Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29585

Incorrect output of SELECT with CTE

    XMLWordPrintable

    Details

      Description

      I used my fuzzing tool to test MariaDB and found a logic bug that make the server produce incorrect results.

      MariaDB installation
      1) cd mariadb-10.10.1
      2) mkdir build; cd build
      3) cmake .. -DCMAKE_BUILD_TYPE=Debug -DWITH_ASAN=ON
      4) make -j12 && sudo make install

      Setup the environment
      1) /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql &
      2) /usr/local/mysql/bin/mysql -uroot
      3) mysql> create database testdb;

      Trigger the bug
      /usr/local/mysql/bin/mysql --force -uroot -Dtestdb

      CREATE TABLE `t_rry5a` (
        `wkey` int(11) DEFAULT NULL,
        `pkey` int(11) NOT NULL,
        `c_t4jlkc` int(11) DEFAULT NULL,
        `c_a047t` text DEFAULT NULL,
        `c_bhsf6d` double DEFAULT NULL,
        `c_t9_mu` int(11) DEFAULT NULL,
        PRIMARY KEY (`pkey`)
      );
       
      insert into t_rry5a (wkey, pkey, c_t4jlkc, c_bhsf6d) values
      (1052, 5800000, 1, 100);
       
      WITH
      cte_0 AS (select
          ref_0.wkey as c0,
          ref_0.pkey as c1,
          ref_0.c_t4jlkc as c2,
          ref_0.c_a047t as c3,
          ref_0.c_bhsf6d as c4,
          ref_0.c_t9_mu as c5
        from
          t_rry5a as ref_0)
      select
          ref_2.c0 as c0,
          ref_2.c1 as c1,
          ref_2.c2 as c2,
          ref_2.c3 as c3,
          ref_2.c4 as c4,
          ref_2.c5 as c5
        from
          cte_0 as ref_2
        where exists (
            select
                  FIRST_VALUE(ref_2.c4) over (partition by ref_2.c1) as c0
            );
       
      WITH
      cte_0 AS (select
          ref_0.wkey as c0,
          ref_0.pkey as c1,
          ref_0.c_t4jlkc as c2,
          ref_0.c_a047t as c3,
          ref_0.c_bhsf6d as c4,
          ref_0.c_t9_mu as c5
        from
          t_rry5a as ref_0)
      select
          ref_2.c0 as c0,
          ref_2.c1 as c1,
          ref_2.c2 as c2,
          ref_2.c3 as c3,
          ref_2.c4 as c4,
          ref_2.c5 as c5
        from
          cte_0 as ref_2
        where exists (
            select
                  FIRST_VALUE(ref_2.c4) over (partition by ref_2.c1) as c0
            ) or 17 <> 0;
      

      Analysis
      The first SELECT outputs

      +------+---------+------+------+------+------+
      | c0   | c1      | c2   | c3   | c4   | c5   |
      +------+---------+------+------+------+------+
      | 1052 | 5800000 |    1 | NULL |  100 | NULL |
      +------+---------+------+------+------+------+
      1 row in set (0.002 sec)
      

      The second SELECT outputs

      +------+---------+------+------+------+------+
      | c0   | c1      | c2   | c3   | c4   | c5   |
      +------+---------+------+------+------+------+
      | 1052 | 5800000 |    1 | NULL | NULL | NULL |
      +------+---------+------+------+------+------+
      1 row in set (0.002 sec)
      

      The first SELECT outputs a row whose c4 column is 100. The second SELECT only adds a "or 17 <> 0" at the end of the WHERE clause of the first SELECT. Such change should not affect the results of the SELECT, so the second SELECT should output the same results as the first SELECT. However, it outputs a row whose c4 column is NULL.

      Based on the analysis, I think it is a logic bug that makes SELECT output incorrect results.

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            Zuming Jiang Zuming Jiang
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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