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

Wrong results for (col1, col2) in (val1, val2) queries

    XMLWordPrintable

    Details

    • Sprint:
      2019-06

      Description

      Consider the following simple table:

      openxs@ao756:~$ mcsmysql -uroot test
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 19
      Server version: 10.3.16-MariaDB-log Columnstore 1.2.5-1
       
      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 [test]> create table dim_date(semaine varchar(10), date_jour date, val int) engine=columnstore;
      Query OK, 0 rows affected (5,804 sec)
       
      MariaDB [test]> insert into dim_date values ('2019-S08','2019-02-18',1);        
      Query OK, 1 row affected (4,658 sec)
       
      MariaDB [test]> insert into dim_date values ('2019-S08','2019-02-19',2);
      Query OK, 1 row affected (0,845 sec)
       
      MariaDB [test]> insert into dim_date values ('2019-S08','2019-02-20',3);
      Query OK, 1 row affected (0,770 sec)
       
      MariaDB [test]> insert into dim_date values ('2019-S07','2019-02-15',0);
      Query OK, 1 row affected (1,028 sec)
       
      MariaDB [test]> insert into dim_date values ('2019-S07','2019-02-14',5);
      Query OK, 1 row affected (0,926 sec)
       
      MariaDB [test]> insert into dim_date values ('2019-S01','2019-01-01',5);
      Query OK, 1 row affected (1,056 sec)
       
      MariaDB [test]> SELECT *
          -> FROM dim_date
          -> WHERE
          -> SEMAINE IN ('2019-S07')
          -> OR
          -> (
          -> SEMAINE = '2019-S08'
          -> AND DATE_JOUR IN ('2019-02-18','2019-02-19')
          -> ) ;
      +----------+------------+------+
      | semaine  | date_jour  | val  |
      +----------+------------+------+
      | 2019-S08 | 2019-02-18 |    1 |
      | 2019-S08 | 2019-02-19 |    2 |
      | 2019-S07 | 2019-02-15 |    0 |
      | 2019-S07 | 2019-02-14 |    5 |
      +----------+------------+------+
      4 rows in set (3,374 sec)
      

      The query returns expected results. Now, consider this semantically equivalent (supposedly) query that compares tuples:

      MariaDB [test]> SELECT *
          -> FROM dim_date
          -> WHERE
          -> SEMAINE IN ('2019-S07')
          -> OR
          -> (SEMAINE,DATE_JOUR) IN
          -> (
          -> ('2019-S08','2019-02-18'),
          -> ('2019-S08','2019-02-19')
          -> )
          -> ORDER BY 1;
      +----------+------------+------+
      | semaine  | date_jour  | val  |
      +----------+------------+------+
      | 2019-S07 | 2019-02-15 |    0 |
      | 2019-S07 | 2019-02-14 |    5 |
      +----------+------------+------+
      2 rows in set (0,658 sec)
       
      MariaDB [test]> SELECT * FROM dim_date WHERE SEMAINE IN ('2019-S07') OR (SEMAINE,DATE_JOUR) IN ( ('2019-S08','2019-02-18'), ('2019-S08','2019-02-19') );
      +----------+------------+------+
      | semaine  | date_jour  | val  |
      +----------+------------+------+
      | 2019-S07 | 2019-02-15 |    0 |
      | 2019-S07 | 2019-02-14 |    5 |
      +----------+------------+------+
      2 rows in set (0,432 sec)
       
      MariaDB [test]> show variables like '%vtable%';
      +----------------------+-------+
      | Variable_name        | Value |
      +----------------------+-------+
      | infinidb_vtable_mode | 1     |
      +----------------------+-------+
      1 row in set (0,002 sec)
       
      MariaDB [test]> set infinidb_vtable_mode = 0;
      Query OK, 0 rows affected (0,000 sec)
       
      MariaDB [test]> SELECT * FROM dim_date WHERE SEMAINE IN ('2019-S07') OR (SEMAINE,DATE_JOUR) IN ( ('2019-S08','2019-02-18'), ('2019-S08','2019-02-19') );
      +----------+------------+------+
      | semaine  | date_jour  | val  |
      +----------+------------+------+
      | 2019-S07 | 2019-02-15 |    0 |
      | 2019-S07 | 2019-02-14 |    5 |
      +----------+------------+------+
      2 rows in set (0,106 sec)
      

      As you can see removing ORDER BY or changing infinidb_vtable_mode does not help.

      For InnoDB table with the same data we get correct, expected result:

      MariaDB [test]> create table dim_date_inno(semaine varchar(10), date_jour date, val int) engine=innodb;
      Query OK, 0 rows affected (1,802 sec)
       
      MariaDB [test]> insert into dim_date_inno select * from dim_date;
      Query OK, 6 rows affected (1,367 sec)
      Records: 6  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> SELECT * FROM dim_date_inno WHERE SEMAINE IN ('2019-S07') OR (SEMAINE,DATE_JOUR) IN ( ('2019-S08','2019-02-18'), ('2019-S08','2019-02-19') );
      +----------+------------+------+
      | semaine  | date_jour  | val  |
      +----------+------------+------+
      | 2019-S08 | 2019-02-18 |    1 |
      | 2019-S08 | 2019-02-19 |    2 |
      | 2019-S07 | 2019-02-15 |    0 |
      | 2019-S07 | 2019-02-14 |    5 |
      +----------+------------+------+
      4 rows in set (0,001 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bharath.bokka Bharath Bokka
              Reporter:
              valerii Valerii Kravchuk
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: