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

Bad results with join comparing DECIMAL and ENUM/SET columns

    XMLWordPrintable

    Details

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

      Description

      The following three scripts comparing VARCHAR, INT and DOUBLS columns to a ENUM column correctly return 2 rows before and after adding a primary key on t2.c1.

      DROP TABLE t1,t2;
      CREATE TABLE t1 (c1 VARCHAR(10) PRIMARY KEY);
      INSERT INTO t1 VALUES ('a'),('b');
      CREATE TABLE t2 (c1 ENUM('a','b'));
      INSERT INTO t2 VALUES ('a'),('b');
      SELECT t1.* FROM t1 NATURAL JOIN t2;
      ALTER TABLE t2 ADD PRIMARY KEY(c1);
      SELECT t1.* FROM t1 NATURAL JOIN t2;

      DROP TABLE t1,t2;
      CREATE TABLE t1 (c1 DOUBLE PRIMARY KEY);
      INSERT INTO t1 VALUES (1),(2);
      CREATE TABLE t2 (c1 ENUM('a','b'));
      INSERT INTO t2 VALUES ('a'),('b');
      SELECT t1.* FROM t1 NATURAL JOIN t2;
      ALTER TABLE t2 ADD PRIMARY KEY(c1);
      SELECT t1.* FROM t1 NATURAL JOIN t2;

      DROP TABLE t1,t2;
      CREATE TABLE t1 (c1 INT PRIMARY KEY);
      INSERT INTO t1 VALUES (1),(2);
      CREATE TABLE t2 (c1 ENUM('a','b'));
      INSERT INTO t2 VALUES ('a'),('b');
      SELECT t1.* FROM t1 NATURAL JOIN t2;
      ALTER TABLE t2 ADD PRIMARY KEY(c1);
      SELECT t1.* FROM t1 NATURAL JOIN t2;

      But a DECIMAL(10,1) column does not work well in the same context:

      DROP TABLE t1,t2;
      CREATE TABLE t1 (c1 DECIMAL(10,1) PRIMARY KEY);
      INSERT INTO t1 VALUES (1),(2);
      CREATE TABLE t2 (c1 ENUM('a','b'));
      INSERT INTO t2 VALUES ('a'),('b');
      SELECT t1.* FROM t1 NATURAL JOIN t2;
      ALTER TABLE t2 ADD PRIMARY KEY(c1);
      SELECT t1.* FROM t1 NATURAL JOIN t2;

      It returns 2 rows before adding the primary key, and "empty set" after adding the key.
      The same problem is repeatable if I change the data type of t2.c1 from ENUM to SET.

      The problem does not seem to exists in MySQL.

        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: