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

Bad results with join comparing DECIMAL and ENUM/SET columns

        Details

        • Type: Bug
        • Status: Closed
        • 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.

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                bar Alexander Barkov added a comment - - edited

                A related problem:

                DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DECIMAL(10,1), b ENUM('1','2'));
INSERT INTO t1 (a) VALUES (1),(2);
UPDATE t1 SET b=a;
SELECT * FROM t1;

                returns:

                +------+------+
| a    | b    |
+------+------+
|  1.0 | 1    |
|  2.0 | 2    |
+------+------+

                That is the DECIMAL column values correctly converted to ENUM values by the assignment operator.

                But now if I change the data type of the column "a" from DECIMAL to ENUM using ALTER TABLE, the values get lost:

                ALTER TABLE t1 MODIFY a ENUM('1','2');
SELECT * FROM t1;

                returns

                +------+------+
| a    | b    |
+------+------+
|      | 1    |
|      | 2    |
+------+------+
                Show
                bar Alexander Barkov added a comment - - edited A related problem: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a DECIMAL(10,1), b ENUM('1','2')); INSERT INTO t1 (a) VALUES (1),(2); UPDATE t1 SET b=a; SELECT * FROM t1; returns: +------+------+ | a | b | +------+------+ | 1.0 | 1 | | 2.0 | 2 | +------+------+ That is the DECIMAL column values correctly converted to ENUM values by the assignment operator. But now if I change the data type of the column "a" from DECIMAL to ENUM using ALTER TABLE, the values get lost: ALTER TABLE t1 MODIFY a ENUM('1','2'); SELECT * FROM t1; returns +------+------+ | a | b | +------+------+ | | 1 | | | 2 | +------+------+

                  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: