[MDEV-6986] Bad results with join comparing INT and VARCHAR columns Created: 2014-10-30  Updated: 2020-10-27

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.14
Fix Version/s: 10.1

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

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

Sergei commented on the email list:

I don't see how you can fix it. The correct fix would be to disable the index in the second query and compare as doubles. But I could only imagine how many applications it will break.

Comment by Ralf Gebhardt [ 2020-10-27 ]

Tried it with 10.5.6, which returns with error
Warning | 1292 | Truncated incorrect INTEGER value: '1.8446744073709552e19'
after adding the primary key

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