Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
1.1.6
-
None
-
debian9
-
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
Attachments
Activity
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)
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"