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