[MCOL-1029] Logic issue breaking multiple where conditions Created: 2017-11-13  Updated: 2020-05-04  Resolved: 2017-12-05

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.0.11, 1.1.1
Fix Version/s: 1.0.12, 1.1.3

Type: Bug Priority: Major
Reporter: Andrew Hutchings (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Epic Link: ColumnStore Compatibility Improvements
Sprint: 2017-23, 2017-24

 Description   

An always-true WHERE condition added to a query with at least 2 normal WHERE conditions generates more results. Essentially removing the first WHERE condition.

Example with partsupp table:

select ps_partkey, ps_suppkey from partsupp where ps_availqty<4093 and ps_supplycost < 500;
 
...
 
163216 rows in set (0.73 sec)
 
 
 
select ps_partkey, ps_suppkey from partsupp where ps_availqty<4093 and ps_supplycost < 500 and (TRUE OR FALSE);
 
...
 
563399 rows in set (2.00 sec)

This happens with:

AND (1=1 OR 0=0)
 
AND (TRUE OR FALSE)
 
AND ('test' = 'test' OR 'test' = 'aaa')



 Comments   
Comment by Andrew Hutchings (Inactive) [ 2017-11-13 ]

Execution plan seems to indicate that the (TRUE OR FALSE) turns the two simple filters into an OR instead of an AND.

First (good) query:

---------------- EXECUTION PLAN ----------------
 
>SELECT limit: 0 - 18446744073709551615
 
MAIN
 
>>Returned Columns
 
SimpleColumn test.partsupp.ps_partkey
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: test/partsupp/ps_partkey//3048/int/partsupp/test.partsupp.ps_partkey/0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
SimpleColumn test.partsupp.ps_suppkey
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: test/partsupp/ps_suppkey//3049/int/partsupp/test.partsupp.ps_suppkey/0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
>>From Tables
 
test.partsupp(partsupp/) engineType=Columnstore
 
>>Filters
 
SimpleFilter(indexflag=0 joinFlag= 0 card= 0)
 
  SimpleColumn test.partsupp.ps_supplycost
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: test/partsupp/ps_supplycost//3051/decimal/partsupp//0/-1/0/0/0/InfiniDB/-1
 
 
 
  Operator: < fOp=8 opType=4  ConstantColumn: 500 intVal=500 uintVal=500(n) resultType=bigint/Alias: 500
 
 
 
SimpleFilter(indexflag=0 joinFlag= 0 card= 0)
 
  SimpleColumn test.partsupp.ps_availqty
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: test/partsupp/ps_availqty//3050/int/partsupp//0/-1/0/0/0/InfiniDB/-1
 
 
 
  Operator: < fOp=8 opType=9  ConstantColumn: 4093 intVal=4093 uintVal=4093(n) resultType=bigint/Alias: 4093
 
 
 
Operator: and fOp=12 opType=5
 
SessionID: 11
 
TxnID: 0
 
VerID:   SCN: 17
 
  Txns:
 
TraceFlags: 1
 
StatementID: 0
 
DistUnionNum: 0
 
Limit: 0 - 18446744073709551615
 
String table threshold: 20
 
--- Column Map ---
 
ps_availqty : 0x7fc0700a7710
 
ps_partkey : 0x7fc0700a7bc0
 
ps_suppkey : 0x7fc0700a8070
 
ps_supplycost : 0x7fc0700a8520
 
UUID: 8ec09ffc-0a78-4958-8a08-f220e1675989
 
QueryType: SELECT
 
 
 
-------------- EXECUTION PLAN END --------------

Second (bad) query:

---------------- EXECUTION PLAN ----------------
 
>SELECT limit: 0 - 18446744073709551615
 
MAIN
 
>>Returned Columns
 
SimpleColumn test.partsupp.ps_partkey
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: test/partsupp/ps_partkey//3048/int/partsupp/test.partsupp.ps_partkey/0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
SimpleColumn test.partsupp.ps_suppkey
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: test/partsupp/ps_suppkey//3049/int/partsupp/test.partsupp.ps_suppkey/0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
>>From Tables
 
test.partsupp(partsupp/) engineType=Columnstore
 
>>Filters
 
SimpleFilter(indexflag=0 joinFlag= 0 card= 0)
 
  SimpleColumn test.partsupp.ps_supplycost
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: test/partsupp/ps_supplycost//3051/decimal/partsupp//0/-1/0/0/0/InfiniDB/-1
 
 
 
  Operator: < fOp=8 opType=4  ConstantColumn: 500 intVal=500 uintVal=500(n) resultType=bigint/Alias: 500
 
 
 
SimpleFilter(indexflag=0 joinFlag= 0 card= 0)
 
  SimpleColumn test.partsupp.ps_availqty
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: test/partsupp/ps_availqty//3050/int/partsupp//0/-1/0/0/0/InfiniDB/-1
 
 
 
  Operator: < fOp=8 opType=9  ConstantColumn: 4093 intVal=4093 uintVal=4093(n) resultType=bigint/Alias: 4093
 
 
 
Operator: or fOp=13 opType=5
 
SessionID: 11
 
TxnID: 0
 
VerID:   SCN: 17
 
  Txns:
 
TraceFlags: 1
 
StatementID: 0
 
DistUnionNum: 0
 
Limit: 0 - 18446744073709551615
 
String table threshold: 20
 
--- Column Map ---
 
ps_availqty : 0x7fc0700a7b50
 
ps_partkey : 0x7fc0700a8000
 
ps_suppkey : 0x7fc0700a84b0
 
ps_supplycost : 0x7fc0700a8960
 
UUID: 227e7d82-e5ad-4657-aa1a-da9fe74e7250
 
QueryType: SELECT
 
 
 
-------------- EXECUTION PLAN END --------------

Notice the final operator ("or" instead of "and").

Comment by Andrew Hutchings (Inactive) [ 2017-11-20 ]

OK, cause is a little complex, so stick with me...

The (TRUE OR FALSE) resolves internally to a CACHE_ITEM containing a COND_ITEM with two arguments. Now, usually with the parse the arguments are parsed first and then we get the COND_ITEM afterwords. We use a stack for this and just pop off the stack during COND_ITEM. For the CACHE_ITEM we haven't put any items on the stack and we are still in the middle of trying to put things on the stack for the AND conditions. This means that the two previous conditions are on the stack, the COND_ITEM inside CACHE_ITEM is used and that adds an OR between the other two conditions.

Current working theory being tested is there is no scenario where a CACHE_ITEM containing a COND_ITEM will need to be executed.

Comment by Andrew Hutchings (Inactive) [ 2017-11-20 ]

Pull request for 1.0 and 1.1. Do not merge 1.1 until after 1.1.2 has been released.

For QA: See description, both the queries should return the same results.

Comment by Daniel Lee (Inactive) [ 2017-12-05 ]

Builds verified: GitHub source

1.0.12-1

[root@localhost ~]# cat mariadb-columnstore-1.0.12-1-centos7.x86_64.bin.tar.txt
/root/columnstore/mariadb-columnstore-server
commit 25e9d054cd3d05683fade1b974e1730316d256ed
Merge: 89b2ea1 7c52a83
Author: David.Hall <david.hall@mariadb.com>
Date: Tue Nov 21 10:49:11 2017 -0600

Merge pull request #79 from mariadb-corporation/MCOL-954-1.0

MCOL-954 Init vtable state

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit b112e826a2793228f5f3c1312fec5291fc1d8bf5
Merge: 7c2640f b657938
Author: David.Hall <david.hall@mariadb.com>
Date: Fri Dec 1 16:17:28 2017 -0600

Merge pull request #338 from mariadb-corporation/MCOL-1068

MCOL-1068 Improve compression_ratio() procedure

1.1.3-1

/root/columnstore/mariadb-columnstore-server
commit 632e265687674fb66bd1d704bc18032b00dd6b17
Merge: 5e9fe52 200f5be
Author: david hill <david.hill@mariadb.com>
Date: Tue Nov 21 15:22:06 2017 -0600

Merge branch 'develop-1.1' of https://github.com/mariadb-corporation/mariadb-columnstore-server into develop-1.1

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit 4d8026618cfb5377c9a200170848092ce5660f10
Author: david hill <david.hill@mariadb.com>
Date: Wed Nov 29 09:36:24 2017 -0600

change how the os_detect is run on remote nodes

Verified mentioned queries. Also tried queries with differ expressions that always evaluate to be true, such as 1>0.

Generated at Thu Feb 08 02:25:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.