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

Wrong result on CREATE TABLE .. AS SELECT .. <TokuDB table>

    XMLWordPrintable

    Details

      Description

      The issue is also reproducible on TokuDB builds of MySQL-5.5.36 and MariaDB 5.5.36 (but not reproducible without TokuDB).

      The result of SELECT with NOT IN ( .. NULL .. ) clause differs from the result of CREATE TABLE .. SELECT (with the same query).

      Test case:

      CREATE TABLE t1 (a CHAR(3), INDEX(a)) ENGINE=TokuDB;
      INSERT INTO t1 VALUES ('foo'),( NULL);
       
      SELECT * FROM t1 WHERE 'bar' NOT IN ( SELECT t1_1.a FROM t1 AS t1_1, t1 AS t1_2 );
       
      CREATE TABLE t2 AS
        SELECT * FROM t1 WHERE 'bar' NOT IN ( SELECT t1_1.a FROM t1 AS t1_1, t1 AS t1_2 );
       
      SELECT * FROM t2;

      Results (for MySQL-5.5.36-TokuDB):

      MySQL [test]> SELECT @@version;
      +---------------------+
      | @@version           |
      +---------------------+
      | 5.5.36-tokudb-7.1.5 |
      +---------------------+

      MySQL [test]> SELECT * FROM t1 WHERE 'bar' NOT IN ( SELECT t1_1.a FROM t1 AS t1_1, t1 AS t1_2 );
      Empty set (0.00 sec)
       
      MySQL [test]> CREATE TABLE t2 AS
          ->   SELECT * FROM t1 WHERE 'bar' NOT IN ( SELECT t1_1.a FROM t1 AS t1_1, t1 AS t1_2 );
      Query OK, 2 rows affected (0.04 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MySQL [test]> SELECT * FROM t2;
      +------+
      | a    |
      +------+
      | NULL |
      | foo  |
      +------+
      2 rows in set (0.00 sec)

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration