Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Incorrect filter results

danila
SQL-Fighter

Hi,

Last weekend we upgraded our Exasol cluster from version 6.2 to 7.1. And simultaneously added more disks per node.
After that we faced with a weird and unpleasant bug.

Here is a table structure:

 

create table MY_TABLE (
    DEAL_CODE decimal(10,0) not null,
    ACCOUNT_CODE decimal(19,0) not null,
    CHANNEL_CODE decimal(5,0) not null,
    ...
);
alter table MY_TABLE add constraint PK_MY_TABLE primary key ("DEAL_CODE","ACCOUNT_CODE","CHANNEL_CODE") ENABLE;
ALTER TABLE MY_TABLE DISTRIBUTE BY ACCOUNT_CODE;

 

 

When I'm running query

 

select * from MY_TABLE where DEAL_CODE = 150

 

Then I get 8517642 records.

When I'm running query

 

select * from MY_TABLE where ACCOUNT_CODE < 0

 

Then I receive 0 records.

BUT! When I run this query

 

select * from MY_TABLE where DEAL_CODE = 150 and ACCOUNT_CODE < 0

 

Then I surprisingly get 919522 records!

ALSO! When I run following query

 

select * from MY_TABLE where DEAL_CODE = 150 and ACCOUNT_CODE > 0

 

Then I got 7598116 records.

 

This shows that Exasol divided records into two groups: 7598116 (ACCOUNT_CODE > 0) + 919522 (ACCOUNT_CODE < 0) = 8517642 
But in reality there are no negative Account codes.

 

I looked on exa_dba_indices for the table:

INDEX_TYPE MEM_OBJECT_SIZE REMARKS

LOCAL0LOCAL INDEX (DEAL_CODE)
LOCAL575285107LOCAL INDEX (CHANNEL_CODE)
LOCAL0LOCAL INDEX (DEAL_CODE,CHANNEL_CODE)
LOCAL1735336895LOCAL INDEX (DEAL_CODE,ACCOUNT_CODE)
LOCAL2827940569LOCAL INDEX (DEAL_CODE,CHANNEL_CODE,ACCOUNT_CODE)


0 is MEM_OBJECT_SIZE is very strange.

 

I tried to enforce reorganize table – it didn't help.
Dropped distribution key and created it back – it didn't help.
Dropped and created back primary key – it didn't help.

This is profile of the query with ACCOUNT_CODE < 0 filter:

PART_IDPART_NAMEPART_INFOOBJECT_NAMEOBJECT_ROWSOUT_ROWSREMARKS
1COMPILE / EXECUTE     
2SCAN MY_TABLE1000270855919522LOCAL INDEX (DEAL_CODE)
3INSERTon TEMPORARY tabletmp_subselect00919522 

 

Following profile stands for query

 

select * from MY_TABLE where TRIM (DEAL_CODE) = 150 and ACCOUNT_CODE < 0;

 

PART_IDPART_NAMEPART_INFOOBJECT_NAMEOBJECT_ROWSOUT_ROWSREMARKS
1COMPILE / EXECUTE     
2SCAN TDBRF_PERSONAL_ACCOUNTS_VER210002708550TDBRF_PERSONAL_ACCOUNTS_VER2(DEAL_CODE,ACCOUNT_CODE)
3INSERTon TEMPORARY tabletmp_subselect000 

 

And it returns correct resultset. Obviously, it doesn't use local index.

Have you faced with such a bug? Which workaround can help here?

Thank you.

3 REPLIES 3

mwellbro
Xpert

Just one quick remark regarding the "0 is MEM_OBJECT_SIZE is very strange." => those are index wrappers, which (if I understand them correctly) are references/pointers to another index which already caters to the same column set or a subset therof ( in your case "LOCAL INDEX (DEAL_CODE,CHANNEL_CODE,ACCOUNT_CODE)" , as both index wrappers show the "leading edge" of this index ).
It can happen that you end up with actual indexes instead of wrappers, that depends on the timing of the index creation ( I´d expect the 3-column index in your system to be the "oldest" out of the 3 indexes in question ).


As for the behavior all in all....fascinating...will be interesting if someone at exa-* can reproduce this.

Cheers,
Malte

exa-Nico
Community Manager
Community Manager

Usually these kinds of problem are very data-dependant and are difficult to reproduce. That being said, I see @danila already opened a support ticket so hopefully you can share the results as the ticket progresses 😀

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

danila
SQL-Fighter

Current status is that we have dropped all indexes in the database.
We were not sure about which of them were causing problems, and we needed fast and working solution, so we decided to drop all of them. It helped, new queries are returning correct and expected results.

In case someone would looks for drop index command, here it is:

DROP [ LOCAL | GLOBAL ] INDEX ON [<schema>.]<table> (<columnlist>);

I have few other thoughts.

  1. While we were upgrading the cluster, we have created and restored 2 backups (level 0 and level 1).
    The problem may be, that in between creation of these 2 backups one of the nodes failed, and Exasol switched to a reserve node.
    So, level 0 backup was using data from nodes 11,12,13,14,15,16, but level 1 backup was based on data from nodes 11,12,14,15,16,17.
  2. Looks like it's a best practice to delete all indexes after restoration of a backup. Especially, when cluster configuration changes.
    Correct me if I'm wrong.