Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Won't Fix
-
5.5.40, 10.0.14
-
None
Description
Run this script:
DROP TABLE IF EXISTS t1,t2; |
CREATE TABLE t1 (a DOUBLE PRIMARY KEY); |
INSERT INTO t1 VALUES (1.8446744073709552e19); |
DROP TABLE IF EXISTS t2; |
CREATE TABLE t2 (a BIGINT UNSIGNED); |
INSERT INTO t2 VALUES (18446744073709551614),(18446744073709551615); |
SELECT t1.* FROM t1 JOIN t2 USING(a); |
SELECT t1.* FROM t1 LEFT JOIN t2 USING(a); |
Both SELECT queries return two rows:
+-----------------------+
|
| a |
|
+-----------------------+
|
| 1.8446744073709552e19 |
|
| 1.8446744073709552e19 |
|
+-----------------------+
|
2 rows in set (0.00 sec)
|
This is correct, because comparison is done as DOUBLE, and the two BIGINT values are mapped into the same DOUBLE value:
mysql> SELECT CAST(a AS DOUBLE) FROM t2;
|
+-----------------------+
|
| CAST(a AS DOUBLE) |
|
+-----------------------+
|
| 1.8446744073709552e19 |
|
| 1.8446744073709552e19 |
|
+-----------------------+
|
2 rows in set (0.01 sec)
|
Now add a primary key on t2 and rerun the same SELECT queries:
ALTER TABLE t2 ADD PRIMARY KEY(a); |
SELECT t1.* FROM t1 JOIN t2 USING(a); |
SELECT t1.* FROM t1 LEFT JOIN t2 USING(a); |
Now the first query with a natural join returns 0 rows,
and the second query with a left join return 1 row.
This looks wrong. Both queries should return 2 rows.
EXPLAIN for the natural join reports eq_ref join method:
mysql> EXPLAIN SELECT t1.* FROM t1 JOIN t2 USING(a);
|
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+
|
| 1 | SIMPLE | t1 | index | PRIMARY | PRIMARY | 8 | NULL | 1 | Using index |
|
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 8 | test.t1.a | 1 | Using where; Using index |
|
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+
|
2 rows in set (0.00 sec)
|
which is wrong.
EXPLAIN for the LEFT join says that t2 is eliminated:
mysql> EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(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 also wrong.
The same wrong behaviour is repeatable if I change the data type for t2.a from BIGINT to DECIMAL(30).