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
-