Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.5.9, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
FreeBSD/amd64
Description
A "between" clause returns wrong results, compared to the equivalent "<=" and "=>" clause, on bigint fields if the integer is single-quoted. There may be other cases but this is the easiest-to-reproduce variant.
Here is a minimalist test case:
CREATE TABLE betweentest ( |
id int(11) unsigned NOT NULL AUTO_INCREMENT, |
range1 bigint(20) DEFAULT NULL, |
range2 bigint(20) DEFAULT NULL, |
PRIMARY KEY (id)) ENGINE=InnoDB; |
INSERT INTO betweentest VALUES |
(1,2739499741191602369,2739499741191602369),
|
(2,2739499741191602192,2739499741191602192),
|
(3,2739138623713574912,2739138623730352127);
|
And some sample queries. Query #4 is the failure case being reported here; it returns an extra row that does NOT match the query. The extra row does match the first 56 bits of the bigint value, but not the full 64 bits. The other 5 queries are correct.
[localhost:test] mysql> select * from betweentest where 2739138623713706238 between range1 and range2 \G
|
id: 3
|
range1: 2739138623713574912
|
range2: 2739138623730352127
|
1 row in set (0.001 sec)
|
|
|
[localhost:test] mysql> select * from betweentest where '2739138623713706238' between range1 and range2 \G
|
id: 3
|
range1: 2739138623713574912
|
range2: 2739138623730352127
|
1 row in set (0.001 sec)
|
|
|
[localhost:test] mysql> select * from betweentest where 2739499741191602192 between range1 and range2 \G
|
id: 2
|
range1: 2739499741191602192
|
range2: 2739499741191602192
|
1 row in set (0.001 sec)
|
|
|
[localhost:test] mysql> select * from betweentest where '2739499741191602192' between range1 and range2 \G
|
id: 1
|
range1: 2739499741191602369
|
range2: 2739499741191602369
|
id: 2
|
range1: 2739499741191602192
|
range2: 2739499741191602192
|
2 rows in set (0.001 sec)
|
|
|
[localhost:test] mysql> select * from betweentest where 2739499741191602192>=range1 and 2739499741191602192<=range2 \G
|
id: 2
|
range1: 2739499741191602192
|
range2: 2739499741191602192
|
1 row in set (0.001 sec)
|
|
|
[localhost:test] mysql> select * from betweentest where '2739499741191602192'>=range1 and '2739499741191602192'<=range2 \G
|
id: 2
|
range1: 2739499741191602192
|
range2: 2739499741191602192
|
The reason we were using single-quotes is because of Perl's DBD::mysql's handling of bind/placeholder variables, as is common practice for avoiding SQL injection vulnerabilities; it seems to be adding them. The examples above are just from the CLI and don't involve Perl at all, though.
We're working around the issue in our application by switching to the syntax from examples 5 and 6.
Maybe there's a type conversion issue affecting bigints somewhere? I'm not sure why it would affect only "between" though.
Attachments
Issue Links
- duplicates
-
-
- Closed
-
Activity
Yes, you're right. It's a type conversion. This is documented here: https://mariadb.com/kb/en/type-conversion/#rules-for-conversion-on-comparison
Rules for Conversion on Comparison
...
- In other cases, arguments are compared as floating point, or real, numbers.
that is, if you compare a number to a string, both a converted to floating point numbers. For large bigint integers this conversion is lossy:
MariaDB [test]> select cast(2739499741191602192 as double);
|
+-------------------------------------+
|
| cast(2739499741191602192 as double) |
|
+-------------------------------------+
|
| 2.739499741191602e18 |
|
+-------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> select cast(cast(2739499741191602192 as double) as int);
|
+--------------------------------------------------+
|
| cast(cast(2739499741191602192 as double) as int) |
|
+--------------------------------------------------+
|
| 2739499741191602176 |
|
+--------------------------------------------------+
|
1 row in set (0.00 sec)
|
For the case of <= and >= with string literals (also =, and a couple of others) there is a special handling that treats the string literal as integer without precision loss. Exactly, to provide more "intuitive" behavior.
It will be fixed eventually. But meanwhile, I'd think, your best workaround would be to use cast(? as int) as the first argument of between.
The problem is also repeatable with ENGINE=MyISAM:
CREATE OR REPLACE TABLE betweentest ( |
id int(11) unsigned NOT NULL AUTO_INCREMENT, |
range1 bigint(20) DEFAULT NULL, |
range2 bigint(20) DEFAULT NULL, |
PRIMARY KEY (id)) ENGINE=MyISAM; |
|
|
INSERT INTO betweentest VALUES |
(1,2739499741191602369,2739499741191602369),
|
(2,2739499741191602192,2739499741191602192),
|
(3,2739138623713574912,2739138623730352127);
|
|
|
-- This query erroneously returns two rows
|
SELECT * FROM betweentest WHERE '2739499741191602192' BETWEEN range1 AND range2; |
+----+---------------------+---------------------+
|
| id | range1 | range2 |
|
+----+---------------------+---------------------+
|
| 1 | 2739499741191602369 | 2739499741191602369 |
|
| 2 | 2739499741191602192 | 2739499741191602192 |
|
+----+---------------------+---------------------+
|
-- This query correctly returns one row
|
SELECT * FROM betweentest WHERE 2739499741191602192 BETWEEN range1 AND range2; |
+----+---------------------+---------------------+
|
| id | range1 | range2 |
|
+----+---------------------+---------------------+
|
| 2 | 2739499741191602192 | 2739499741191602192 |
|
+----+---------------------+---------------------+
|
MySQL 8.0.23 also has this bug, fyi