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.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Summary | LEFT JOIN table elimination erroneously used when it should not be | Bad results with joins compating DATE and ENUM columns |
| Description |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} The first SELECT still returns two rows. But the second SELECT with LEFT JOIN returns only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. According to the EXPLAIN output, the table "t2" was erroneously eliminated: {noformat} 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) {noformat} |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} Now both SELECT queries return only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. According to the EXPLAIN output, the table "t2" was erroneously eliminated in the LEFT JOIN query: {noformat} 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) {noformat} |
| Description |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} Now both SELECT queries return only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. According to the EXPLAIN output, the table "t2" was erroneously eliminated in the LEFT JOIN query: {noformat} 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) {noformat} |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} Now both SELECT queries return only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. EXPLAIN for the first join tells that it's using eq_ref, which looks wrong: {noformat} 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 | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+ {noformat} EXPLAIN for the second SELECT with LEFT JOIN tells that the table "t2" was erroneously eliminated: {noformat} 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) {noformat} |
| Description |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} Now both SELECT queries return only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. EXPLAIN for the first join tells that it's using eq_ref, which looks wrong: {noformat} 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 | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+ {noformat} EXPLAIN for the second SELECT with LEFT JOIN tells that the table "t2" was erroneously eliminated: {noformat} 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) {noformat} |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} Now both SELECT queries return only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. EXPLAIN for the first join tells that it's using eq_ref, which looks wrong: {noformat} 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 | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+ {noformat} EXPLAIN for the second SELECT with LEFT JOIN tells that the table "t2" was erroneously eliminated: {noformat} 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) {noformat} It seems that the underlying code uses result_type() instead of cmp_type() in some places. |
| Description |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} Now both SELECT queries return only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. EXPLAIN for the first join tells that it's using eq_ref, which looks wrong: {noformat} 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 | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+ {noformat} EXPLAIN for the second SELECT with LEFT JOIN tells that the table "t2" was erroneously eliminated: {noformat} 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) {noformat} It seems that the underlying code uses result_type() instead of cmp_type() in some places. |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} Now both SELECT queries return only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. EXPLAIN for the first join tells that it's using eq_ref, which looks wrong: {noformat} 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 | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+ {noformat} EXPLAIN for the second SELECT with LEFT JOIN tells that the table "t2" was erroneously eliminated: {noformat} 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) {noformat} 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: {code:sql} 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; {code} It correctly returns two rows after ALTER TABLE for the natural join SELECT, but still erroneously returns only row for the LEFT JOIN query, using table elimination. |
| Description |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} Now both SELECT queries return only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. EXPLAIN for the first join tells that it's using eq_ref, which looks wrong: {noformat} 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 | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+ {noformat} EXPLAIN for the second SELECT with LEFT JOIN tells that the table "t2" was erroneously eliminated: {noformat} 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) {noformat} 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: {code:sql} 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; {code} It correctly returns two rows after ALTER TABLE for the natural join SELECT, but still erroneously returns only row for the LEFT JOIN query, using table elimination. |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} Now both SELECT queries return only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. EXPLAIN for the first join tells that it's using eq_ref, which looks wrong: {noformat} 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 | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+ {noformat} EXPLAIN for the second SELECT with LEFT JOIN tells that the table "t2" was erroneously eliminated: {noformat} 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) {noformat} 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: {code:sql} 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; {code} It correctly returns two rows after ALTER TABLE for the natural join SELECT, but still erroneously returns only row for the LEFT JOIN query, using table elimination. The same problem is repeatable with DATE+INT combination: {code:sql} SET storage_engine=MyISAM; 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; {code} Both SELECT queries correctly returns two rows before adding a primary key, but only one row after adding. |
| Description |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} Now both SELECT queries return only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. EXPLAIN for the first join tells that it's using eq_ref, which looks wrong: {noformat} 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 | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+ {noformat} EXPLAIN for the second SELECT with LEFT JOIN tells that the table "t2" was erroneously eliminated: {noformat} 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) {noformat} 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: {code:sql} 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; {code} It correctly returns two rows after ALTER TABLE for the natural join SELECT, but still erroneously returns only row for the LEFT JOIN query, using table elimination. The same problem is repeatable with DATE+INT combination: {code:sql} SET storage_engine=MyISAM; 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; {code} Both SELECT queries correctly returns two rows before adding a primary key, but only one row after adding. |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} Now both SELECT queries return only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. EXPLAIN for the first join tells that it's using eq_ref, which looks wrong: {noformat} 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 | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+ {noformat} EXPLAIN for the second SELECT with LEFT JOIN tells that the table "t2" was erroneously eliminated: {noformat} 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) {noformat} 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: {code:sql} 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; {code} It correctly returns two rows after ALTER TABLE for the natural join SELECT, but still erroneously returns only row for the LEFT JOIN query, using table elimination. The same problem is repeatable with DATE+INT combination: {code:sql} 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; {code} Both SELECT queries correctly returns two rows before adding a primary key, but only one row after adding. |
| Summary | Bad results with joins compating DATE and ENUM columns | Bad results with joins comparing DATE and ENUM columns |
| Description |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} Now both SELECT queries return only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. EXPLAIN for the first join tells that it's using eq_ref, which looks wrong: {noformat} 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 | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+ {noformat} EXPLAIN for the second SELECT with LEFT JOIN tells that the table "t2" was erroneously eliminated: {noformat} 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) {noformat} 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: {code:sql} 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; {code} It correctly returns two rows after ALTER TABLE for the natural join SELECT, but still erroneously returns only row for the LEFT JOIN query, using table elimination. The same problem is repeatable with DATE+INT combination: {code:sql} 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; {code} Both SELECT queries correctly returns two rows before adding a primary key, but only one row after adding. |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} Now both SELECT queries return only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. EXPLAIN for the first join tells that it's using eq_ref, which looks wrong: {noformat} 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 | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+ {noformat} EXPLAIN for the second SELECT with LEFT JOIN tells that the table "t2" was erroneously eliminated: {noformat} 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) {noformat} 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: {code:sql} 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; {code} 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: {code:sql} 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; {code} Both SELECT queries correctly returns two rows before adding a primary key, but only one row after adding. |
| Summary | Bad results with joins comparing DATE and ENUM columns | Bad results with joins comparing DATE and INT/ENUM/VARCHAR columns |
| Description |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} Now both SELECT queries return only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. EXPLAIN for the first join tells that it's using eq_ref, which looks wrong: {noformat} 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 | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+ {noformat} EXPLAIN for the second SELECT with LEFT JOIN tells that the table "t2" was erroneously eliminated: {noformat} 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) {noformat} 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: {code:sql} 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; {code} 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: {code:sql} 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; {code} Both SELECT queries correctly returns two rows before adding a primary key, but only one row after adding. |
Run this script: {code:sql} 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; {code} It produces two rows for both SELECT queries: {noformat} +------------+ | a | +------------+ | 2001-01-01 | | 2001-01-01 | +------------+ 2 rows in set (0.00 sec) {noformat} 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: {code:sql} 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; {code} Now both SELECT queries return only one row: {noformat} mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +------------+ | a | +------------+ | 2001-01-01 | +------------+ 1 row in set (0.01 sec) {noformat} This is wrong. EXPLAIN for the first join tells that it's using eq_ref, which looks wrong: {noformat} 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 | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+ {noformat} EXPLAIN for the second SELECT with LEFT JOIN tells that the table "t2" was erroneously eliminated: {noformat} 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) {noformat} 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: {code:sql} 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; {code} 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: {code:sql} 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; {code} 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: {code:sql} 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; {code} It correctly returns two rows before adding a primary key on t2: {noformat} +---------------------+ | a | +---------------------+ | 1999-01-01 00:00:00 | | 1999-01-01 00:00:00 | +---------------------+ {noformat} but only one row after adding. Explain says that t2 is eliminated: {code} 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) {code} which is wrong. |
| Summary | Bad results with joins comparing DATE and INT/ENUM/VARCHAR columns | Bad results with joins comparing DATE/DATETIME and INT/ENUM/VARCHAR columns |
| Summary | Bad results with joins comparing DATE/DATETIME and INT/ENUM/VARCHAR columns | Bad results with joins comparing DATE/DATETIME and INT/DECIMAL/DOUBLE/ENUM/VARCHAR columns |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | MariaDB v2 [ 56702 ] | MariaDB v3 [ 65192 ] |
| Workflow | MariaDB v3 [ 65192 ] | MariaDB v4 [ 148358 ] |