[MDEV-30710] Incorrect operator when comparing large unsigned integers. Created: 2023-02-22  Updated: 2023-07-25  Resolved: 2023-07-20

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.5.16
Fix Version/s: 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3, 11.1.2, 11.2.1

Type: Bug Priority: Major
Reporter: guozhentang Assignee: Rex Johnston
Resolution: Fixed Votes: 0
Labels: None
Environment:

centos 7.6

Attachments: PNG File screenshot-1.png    

 Description    

DROP DATABASE IF EXISTS database98;
 
CREATE DATABASE database98;
 
USE database98;
 
CREATE TABLE t0(c0 BOOLEAN  UNIQUE NOT NULL, c1 MEDIUMINT  UNIQUE NOT NULL, c2 BOOLEAN  UNIQUE NOT NULL) engine=MyISAM;
 
CREATE OR REPLACE TABLE t1(c0 INT  UNIQUE NOT NULL, c1 VARCHAR(100)  UNIQUE NOT NULL, PRIMARY KEY(c1, c0));
 
INSERT INTO t0 VALUES (127885159, -441428384, 540566410);
 
INSERT INTO t0 VALUES (-70694447, 228215695, -294915985);
 
INSERT INTO t1 VALUES (-1668852992, 'rklt');
 
INSERT INTO t1 VALUES (97930192, '');
 
SELECT COUNT(*) FROM t0 WHERE ((1363502665 | -277659069) > c0);
 
SELECT SUM(count) FROM (SELECT (((1363502665 | -277659069) > c0) IS TRUE) as count FROM t0) as asdf;

The expected result is that the two query statements have the same query result, but in fact, the first query result is 1, and the second query result is 2

 Comments   
Comment by guozhentang [ 2023-02-22 ]

Comment by Sergei Golubchik [ 2023-03-25 ]

smaller test case:

create table t1(c0 boolean unique);
 
insert into t1 values (127);
 
insert into t1 values (-128);
 
select * from t1 where 18446744073700599371 > c0;
 
select * from t1 ignore index (c0) where 18446744073700599371 > c0;
 
drop table t1;

Comment by Rex Johnston [ 2023-07-18 ]

This started with commit eb483c5181ab430877c135c16224284cfc517b3d, range scan updates exposed an inconsistency.

Comment by Alexander Barkov [ 2023-07-19 ]

Hello Johnston,

I have small suggestions for the patch:
https://github.com/MariaDB/server/commit/f17a865c395a5c81401d0cd39c4c81a2de93b7bb

This condition:

if (op == SCALAR_CMP_LT && ((item_val > 0)
 
                   || (value->unsigned_flag && (ulonglong)item_val > 0 )))

seems to be redundant from a glance.

Shouldn't this be enough:

if (op == SCALAR_CMP_LT && (item_val > 0 || value->unsigned_flag))

?

Also, please put both queries into the test:

select * from t1 where 18446744073700599371 > c0;
 
select * from t1 ignore index (c0) where 18446744073700599371 > c0;

to demonstrate that the result is equal with and without the index.

Comment by Alexander Barkov [ 2023-07-19 ]

Hello Johnston,
The patch
https://github.com/MariaDB/server/commit/f282d61014db1a8fbfc43bb6750116f768b9611a
looks ok for me.

Generated at Thu Feb 08 10:18:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.