Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.0.14
-
None
-
None
Description
DROP TABLE IF EXISTS t1,t2; |
CREATE TABLE t1 (str varchar(21) PRIMARY KEY); |
CREATE TABLE t2 (num bigint unsigned); |
INSERT INTO t1 VALUES ('1.8446744073709552e19'); |
INSERT INTO t2 VALUES (18446744073709551615), (18446744073709551614); |
SELECT * FROM t1, t2 WHERE num=str; |
returns two rows:
+-----------------------+----------------------+
|
| str | num |
|
+-----------------------+----------------------+
|
| 1.8446744073709552e19 | 18446744073709551615 |
|
| 1.8446744073709552e19 | 18446744073709551614 |
|
+-----------------------+----------------------+
|
2 rows in set (0.01 sec)
|
INT and VARCHAR column are compared as double.
There is no enough double precision to cover 20 significat digits, so both 18446744073709551615 and 18446744073709551614 are compared as equal to '1.8446744073709552e19'.
If I add a primary key on t2 and re-run the query:
ALTER TABLE t2 ADD PRIMARY KEY(num); |
SELECT * FROM t1, t2 WHERE num=str; |
it returns only one row:
+-----------------------+----------------------+
|
| str | num |
|
+-----------------------+----------------------+
|
| 1.8446744073709552e19 | 18446744073709551615 |
|
+-----------------------+----------------------+
|
This is wrong. It should return the same row set with and without the primary key.