[MCOL-2096] IN clause not properly evaluated in where Created: 2019-01-21  Updated: 2020-11-12  Resolved: 2020-04-30

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.1.6
Fix Version/s: 1.4.4, 1.5.1

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

debian9

Sprint: 2020-2, 2020-3, 2020-4, 2020-5, 2020-6, 2020-7

 Description   

IN() clause does not evaluate properly on some columns with a window function

in the example below, the column r is computed with the function DENSE_RANK in a subselect, and there is a where r in (...). The where clause does not filter rows as expected.

Note that rows are properly filtered if the where clause is rewritten as (r=1 or r=2)

create table t(a int, b int) engine=ColumnStore;
insert into t(a,b) values(1,4),(2,3),(3,2),(4,1);

select
a,
r
from (
select
a,
DENSE_RANK() OVER `w_0` as `r`
from t
WINDOW `w_0` as ( ORDER BY `b` DESC)
) t0
where r in (1,2)

=> returns
1,1
2,2
3,3
4,4

instead of just the first 2 rows

 Comments   
Comment by Jose Rojas (Inactive) [ 2020-03-19 ]

After a large amount of investigation the culprit was the IN clause was not filtering for the combination of window function and IN clause. It worked with 1 value inside IN clause because that is treated as "= value"

Comment by Roman [ 2020-04-07 ]

4QA. There was a relevant test added: working_tpch1/misc/MCOL-2096.sql

Comment by Daniel Lee (Inactive) [ 2020-04-30 ]

1.4.4-1

/root/ColumnStore/buildColumnstoreFromGithubSource/server
commit 00abe03ad1da3719e06f7112000a331ee2b6786a
Author: Patrick LeBlanc <43503225+pleblanc1976@users.noreply.github.com>
Date: Wed Apr 29 10:00:54 2020 -0500

/root/ColumnStore/buildColumnstoreFromGithubSource/server/engine
commit 2b67ac7f3537bd4b4d132c8a6c3a53e4cc63f4a1
Merge: beaac49 23d65dc
Author: benthompson15 <ben.thompson.015@gmail.com>
Date: Tue Apr 28 15:40:45 2020 -0500

1.5.0-1

/root/ColumnStore/buildColumnstoreFromGithubSource/server
commit 25eb50d6c002e987e2d240402391549d408c18d9
Author: Alexey Bychko <abychko@gmail.com>
Date: Thu Apr 23 12:36:13 2020 +0700

commit 6ad38ccc28d31a099d052e5de827543808843a3c
Merge: 658abae bb3e76b
Author: benthompson15 <ben.thompson.015@gmail.com>
Date: Fri Apr 24 14:31:09 2020 -0500

MariaDB [mytest]> insert into t(a,b) values(1,4),(2,3),(3,2),(4,1);
Query OK, 4 rows affected (1.120 sec)
Records: 4 Duplicates: 0 Warnings: 0

MariaDB [mytest]> select
-> a,
-> r
-> from (
-> select
-> a,
-> DENSE_RANK() OVER `w_0` as `r`
-> from t
-> WINDOW `w_0` as ( ORDER BY `b` DESC)
-> ) t0
-> where r in (1,2)
-> ;
-------+

a r

-------+

1 1
2 2

-------+
2 rows in set (0.249 sec)

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