[#MCOL-4570] Wrong results for query with filter condition on subquery with window function

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.

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)

[root@sergueyz-devel-1 1]# mysql <t1.sql

*************************** 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

id1     id2     id3     val1    val2

1       1       1       0.5     0.6

2       1       1       0.55    0.6

id1     c1

1       -0.04999999999999993

2       NULL

[root@sergueyz-devel-1 1]# mysql <t3-innodb.sql

*************************** 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=InnoDB DEFAULT CHARSET=latin1

id1     id2     id3     val1    val2

1       1       1       0.5     0.6

2       1       1       0.55    0.6

id1     c1

1       -0.04999999999999993

2       NULL

[root@sergueyz-devel-1 1]# cat t1.sql

use test;

drop table if exists t1;

create table t1(id1 int, id2 int, id3 int, val1 double, val2 double) engine = Columnstore;

show create table t1 \G;

insert into t1 values(1,1,1,0.5,0.6), (2,1,1,0.55,0.6);

select * from t1;

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;

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;

[root@sergueyz-devel-1 1]# cat t3-innodb.sql

use test;

drop table if exists t1;

create table t1(id1 int, id2 int, id3 int, val1 double, val2 double) engine=innodb;

show create table t1 \G;

insert into t1 values(1,1,1,0.5,0.6), (2,1,1,0.55,0.6);

select * from t1;

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;

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;

[MCOL-4570] Wrong results for query with filter condition on subquery with window function Created: 2021-03-02 Updated: 2022-06-02 Resolved: 2022-03-04
Status:	Closed
Project:	MariaDB ColumnStore
Component/s:	ExeMgr, PrimProc
Affects Version/s:	5.5.1, 5.5.2
Fix Version/s:	6.3.1

[MCOL-4570] Wrong results for query with filter condition on subquery with window function Created: 2021-03-02 Updated: 2022-06-02 Resolved: 2022-03-04