Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4570

Wrong results for query with filter condition on subquery with window function

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.1, 5.5.2
    • 6.3.1
    • ExeMgr, PrimProc
    • 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)
      

      Attachments

        Activity

          People

            sergey.zefirov Sergey Zefirov
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.