[MDEV-6993] Bad results with join comparing DECIMAL and ENUM/SET columns Created: 2014-10-31  Updated: 2014-12-01  Resolved: 2014-11-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5.40, 10.0.14, 5.3.13
Fix Version/s: 10.0.15

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
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.

 Comments   
Comment by Alexander Barkov [ 2014-11-19 ]

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    |
 
+------+------+

Generated at Thu Feb 08 07:16:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.