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

Bad results with joins comparing DATE/DATETIME and INT/DECIMAL/DOUBLE/ENUM/VARCHAR columns

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.14
    • Fix Version/s: 10.0.15
    • Component/s: Optimizer
    • Labels:
      None

      Description

      Run this script:

      DROP TABLE IF EXISTS t1,t2;
      CREATE TABLE t1 (a DATE PRIMARY KEY);
      INSERT INTO t1 VALUES ('2001-01-01');
      CREATE TABLE t2 (a ENUM('2001-01-01','2001/01/01'));
      INSERT INTO t2 VALUES ('2001-01-01');
      INSERT INTO t2 VALUES ('2001/01/01');
      SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
      SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;

      It produces two rows for both SELECT queries:

      +------------+
      | a          |
      +------------+
      | 2001-01-01 |
      | 2001-01-01 |
      +------------+
      2 rows in set (0.00 sec)

      This is correct:
      The columns are compared as DATE.
      Therefore, '2001-01-01' in t1 matches both '2001-01-01' and '2001/01/01' in t2. Hence two rows.

      Now add a primary key on t2 and run the two SELECTs again:

      ALTER TABLE t2 ADD PRIMARY KEY(a);
      SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
      SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;

      Now both SELECT queries return only one row:

      mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
      +------------+
      | a          |
      +------------+
      | 2001-01-01 |
      +------------+
      1 row in set (0.01 sec)

      This is wrong.

      EXPLAIN for the first join tells that it's using eq_ref, which looks wrong:

      mysql>  EXPLAIN SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
      +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+
      | id   | select_type | table | type   | possible_keys | key     | key_len | ref       | rows | Extra                    |
      +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+
      |    1 | SIMPLE      | t1    | index  | PRIMARY       | PRIMARY | 3       | NULL      |    1 | Using index              |
      |    1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 1       | test.t1.a |    1 | Using where; Using index |
      +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+

      EXPLAIN for the second SELECT with LEFT JOIN tells that the table "t2" was erroneously eliminated:

      mysql> EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
      +------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
      +------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | index | NULL          | PRIMARY | 3       | NULL |    1 | Using index |
      +------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
      1 row in set (0.00 sec)

      It seems that the underlying code uses result_type() instead of cmp_type() in some places.

      If I use a similar scenario with DATE and VARCHAR columns:

      DROP TABLE IF EXISTS t1,t2;
      CREATE TABLE t1 (a DATE PRIMARY KEY);
      INSERT INTO t1 VALUES ('2001-01-01');
      CREATE TABLE t2 (a VARCHAR(20));
      INSERT INTO t2 VALUES ('2001-01-01');
      INSERT INTO t2 VALUES ('2001/01/01');
      SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
      SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
       
      ALTER TABLE t2 ADD PRIMARY KEY(a);
      SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
      SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
      EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;

      It correctly returns two rows after ALTER TABLE for the natural join SELECT,
      but still erroneously returns only one row for the LEFT JOIN query, using table elimination.

      The same problem is repeatable with DATE+INT combination:

      DROP TABLE IF EXISTS t1,t2;
      CREATE TABLE t1 (a DATE PRIMARY KEY);
      INSERT INTO t1 VALUES ('1999-01-01');
      CREATE TABLE t2 (a INT);
      INSERT INTO t2 VALUES (19990101);
      INSERT INTO t2 VALUES (990101);
      SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
      SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
       
      ALTER TABLE t2 ADD PRIMARY KEY(a);
      SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
      SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
      EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;

      Both SELECT queries correctly returns two rows before adding a primary key,
      but only one row after adding.

      A similar problem is observed with a DATETIME+DECIMAL column combination:

      DROP TABLE IF EXISTS t1,t2;
      CREATE TABLE t1 (a DATETIME PRIMARY KEY);
      INSERT INTO t1 VALUES ('1999-01-01 00:00:00');
      CREATE TABLE t2 (a DECIMAL(30,1));
      INSERT INTO t2 VALUES (19990101000000);
      INSERT INTO t2 VALUES (990101000000);  
      SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
      SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
      ALTER TABLE t2 ADD PRIMARY KEY(a);
      SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
      SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
      EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
      DROP TABLE t1,t2; 

      It correctly returns two rows before adding a primary key on t2:

      +---------------------+
      | a                   |
      +---------------------+
      | 1999-01-01 00:00:00 |
      | 1999-01-01 00:00:00 |
      +---------------------+

      but only one row after adding.

      Explain says that t2 is eliminated:

      mysql> EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
      +------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
      +------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | index | NULL          | PRIMARY | 8       | NULL |    1 | Using index |
      +------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
      1 row in set (0.00 sec)

      which is wrong.

        Attachments

          Activity

            People

            Assignee:
            bar Alexander Barkov
            Reporter:
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration