[MDEV-25492] BETWEEN clause returns incorrect results on quoted 64-bit ints Created: 2021-04-22  Updated: 2022-07-06  Resolved: 2022-07-06

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 5.5, 10.0, 10.1, 10.5.9, 10.2, 10.3, 10.4, 10.5
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Mike Andrews Assignee: Alexander Barkov
Resolution: Duplicate Votes: 0
Labels: upstream
Environment:

FreeBSD/amd64

Issue Links:
Duplicate
duplicates MDEV-21445 Strange/inconsistent behavior of IN c... Closed

 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.

 Comments   
Comment by Mike Andrews [ 2021-04-22 ]

MySQL 8.0.23 also has this bug, fyi

Comment by Sergei Golubchik [ 2021-04-27 ]

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.

Comment by Alexander Barkov [ 2022-07-06 ]

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 |
 
+----+---------------------+---------------------+

Generated at Thu Feb 08 09:38:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.