Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.14
-
None
Description
Run this script:
DROP TABLE IF EXISTS t1,t2; |
CREATE TABLE t1 (a DATE PRIMARY KEY); |
INSERT INTO t1 VALUES ('2001-01-01'); |
CREATE TABLE t2 (a ENUM('2001-01-01','2001/01/01')); |
INSERT INTO t2 VALUES ('2001-01-01'); |
INSERT INTO t2 VALUES ('2001/01/01'); |
SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; |
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; |
It produces two rows for both SELECT queries:
+------------+
|
| a |
|
+------------+
|
| 2001-01-01 |
|
| 2001-01-01 |
|
+------------+
|
2 rows in set (0.00 sec)
|
This is correct:
The columns are compared as DATE.
Therefore, '2001-01-01' in t1 matches both '2001-01-01' and '2001/01/01' in t2. Hence two rows.
Now add a primary key on t2 and run the two SELECTs again:
ALTER TABLE t2 ADD PRIMARY KEY(a); |
SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; |
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; |
Now both SELECT queries return only one row:
mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
|
+------------+
|
| a |
|
+------------+
|
| 2001-01-01 |
|
+------------+
|
1 row in set (0.01 sec)
|
This is wrong.
EXPLAIN for the first join tells that it's using eq_ref, which looks wrong:
mysql> EXPLAIN SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
|
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+
|
| 1 | SIMPLE | t1 | index | PRIMARY | PRIMARY | 3 | NULL | 1 | Using index |
|
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 1 | test.t1.a | 1 | Using where; Using index |
|
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+
|
EXPLAIN for the second SELECT with LEFT JOIN tells that the table "t2" was erroneously eliminated:
mysql> EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | index | NULL | PRIMARY | 3 | NULL | 1 | Using index |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
1 row in set (0.00 sec)
|
It seems that the underlying code uses result_type() instead of cmp_type() in some places.
If I use a similar scenario with DATE and VARCHAR columns:
DROP TABLE IF EXISTS t1,t2; |
CREATE TABLE t1 (a DATE PRIMARY KEY); |
INSERT INTO t1 VALUES ('2001-01-01'); |
CREATE TABLE t2 (a VARCHAR(20)); |
INSERT INTO t2 VALUES ('2001-01-01'); |
INSERT INTO t2 VALUES ('2001/01/01'); |
SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; |
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; |
 |
ALTER TABLE t2 ADD PRIMARY KEY(a); |
SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; |
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; |
EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; |
It correctly returns two rows after ALTER TABLE for the natural join SELECT,
but still erroneously returns only one row for the LEFT JOIN query, using table elimination.
The same problem is repeatable with DATE+INT combination:
DROP TABLE IF EXISTS t1,t2; |
CREATE TABLE t1 (a DATE PRIMARY KEY); |
INSERT INTO t1 VALUES ('1999-01-01'); |
CREATE TABLE t2 (a INT); |
INSERT INTO t2 VALUES (19990101); |
INSERT INTO t2 VALUES (990101); |
SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; |
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; |
 |
ALTER TABLE t2 ADD PRIMARY KEY(a); |
SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; |
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; |
EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; |
Both SELECT queries correctly returns two rows before adding a primary key,
but only one row after adding.
A similar problem is observed with a DATETIME+DECIMAL column combination:
DROP TABLE IF EXISTS t1,t2; |
CREATE TABLE t1 (a DATETIME PRIMARY KEY); |
INSERT INTO t1 VALUES ('1999-01-01 00:00:00'); |
CREATE TABLE t2 (a DECIMAL(30,1)); |
INSERT INTO t2 VALUES (19990101000000); |
INSERT INTO t2 VALUES (990101000000); |
SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; |
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; |
ALTER TABLE t2 ADD PRIMARY KEY(a); |
SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; |
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; |
EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; |
DROP TABLE t1,t2; |
It correctly returns two rows before adding a primary key on t2:
+---------------------+
|
| a |
|
+---------------------+
|
| 1999-01-01 00:00:00 |
|
| 1999-01-01 00:00:00 |
|
+---------------------+
|
but only one row after adding.
Explain says that t2 is eliminated:
mysql> EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | index | NULL | PRIMARY | 8 | NULL | 1 | Using index |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
1 row in set (0.00 sec)
|
which is wrong.