Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.1, 5.5.2
-
None
-
2021-17
Description
If we put some SELECT from the Columnstore table with a Window function in it into a subquery, filtering the results of that subquery based on column with Window function in the outer query does not work.
Consider the following primitive test case (I've used https://hub.docker.com/r/mariadb/columnstore/ docker image to test):
openxs@ao756:~$ sudo docker exec -it mcs_container2 bash
|
[root@c804c1e80cc4 /]# mariadb
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 4
|
Server version: 10.5.9-MariaDB MariaDB Server
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [(none)]> create database test;
|
Query OK, 1 row affected (0.000 sec)
|
|
|
MariaDB [(none)]> use test
|
Database changed
|
|
|
MariaDB [test]> create table t1(id1 int, id2 int, id3 int, val1 double, val2 double) engine = Columnstore;
|
Query OK, 0 rows affected (7.329 sec)
|
|
|
MariaDB [test]> show create table t1\G
|
*************************** 1. row ***************************
|
Table: t1
|
Create Table: CREATE TABLE `t1` (
|
`id1` int(11) DEFAULT NULL,
|
`id2` int(11) DEFAULT NULL,
|
`id3` int(11) DEFAULT NULL,
|
`val1` double DEFAULT NULL,
|
`val2` double DEFAULT NULL
|
) ENGINE=Columnstore DEFAULT CHARSET=latin1
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> insert into t1 values(1,1,1,0.5,0.6), (2,1,1,0.55,0.6);
|
Query OK, 2 rows affected (1.577 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select * from t1;
|
+------+------+------+------+------+
|
| id1 | id2 | id3 | val1 | val2 |
|
+------+------+------+------+------+
|
| 1 | 1 | 1 | 0.5 | 0.6 |
|
| 2 | 1 | 1 | 0.55 | 0.6 |
|
+------+------+------+------+------+
|
2 rows in set (0.143 sec)
|
|
|
MariaDB [test]> with sub as (select id1, (lead(val1) over(partition by id2 order by val1) - val2) as c1 from t1 where val2 > val1 and id3 in (1)) select * from sub;
|
+------+----------------------+
|
| id1 | c1 |
|
+------+----------------------+
|
| 1 | -0.04999999999999993 |
|
| 2 | NULL |
|
+------+----------------------+
|
2 rows in set (0.040 sec)
|
|
|
MariaDB [test]> with sub as (select id1, (lead(val1) over(partition by id2 order by val1) - val2) as c1 from t1 where val2 > val1 and id3 in (1)) select * from sub where c1 > 0;
|
+------+----------------------+
|
| id1 | c1 |
|
+------+----------------------+
|
| 1 | -0.04999999999999993 |
|
| 2 | NULL |
|
+------+----------------------+
|
2 rows in set (0.039 sec)
|
The result above is obviously wrong, we asked for c1 > 0, but got negative value and NULL. This is NOT the case with InnoDB, for example:
MariaDB [test]> alter table t1 engine=InnoDB;
|
Query OK, 2 rows affected (3.501 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> with sub as (select id1, (lead(val1) over(partition by id2 order by val1) - val2) as c1 from t1 where val2 > val1 and id3 in (1)) select * from sub;
|
+------+----------------------+
|
| id1 | c1 |
|
+------+----------------------+
|
| 1 | -0.04999999999999993 |
|
| 2 | NULL |
|
+------+----------------------+
|
2 rows in set (0.002 sec)
|
|
|
MariaDB [test]> with sub as (select id1, (lead(val1) over(partition by id2 order by val1) - val2) as c1 from t1 where val2 > val1 and id3 in (1)) select * from sub where c1 > 0;
|
Empty set (0.001 sec)
|