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

Bad results with join comparing DECIMAL and ENUM/SET columns

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.40, 10.0.14, 5.3.13
    • 10.0.15
    • Optimizer
    • 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

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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