Bug #102035 wrong select result with unsigned smallint and smallint types

• Submitted: 20 Dec 2020 3:10
• Modified: 21 Dec 2020 13:38
• Reporter: hel le
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0.22
• OS: Linux
• CPU Architecture: x86

[20 Dec 2020 3:10] hel le

Description:
When comparing unsigned smallint filed with smallint filed, it result wrong 
results.

1. prepare data

CREATE DATABASE test;
USE test;
CREATE TABLE `t2` (
  `c0` smallint(5) unsigned zerofill DEFAULT NULL,
  `c1` double DEFAULT NULL,
  KEY `i0` (`c0`,`c1` DESC)
) ;

CREATE TABLE `t3` (
  `c0` decimal(10,0) DEFAULT NULL,
  `c1` smallint(209) DEFAULT NULL,
  UNIQUE KEY `c1` (`c1`)
) ;
insert into t2(c0) values(32768);
insert into t3(c1) values (6);
insert into t3(c1) values (NULL);

2. exec select statment

mysql> select t2.c0 from t2,t3 where (not ((t2.c0) <= (t3.c1)));
Empty set (0.00 sec)

mysql> select t2.c0, t3.c1, (not ((t2.c0) <= (t3.c1))) from t2,t3;
+-------+------+----------------------------+
| c0    | c1   | (not ((t2.c0) <= (t3.c1))) |
+-------+------+----------------------------+
| 32768 | NULL |                       NULL |
| 32768 |    6 |                          1 |
+-------+------+----------------------------+
2 rows in set (0.00 sec)

The first select statment result empty, it different with the second select statment's result.

How to repeat:
CREATE DATABASE test;
USE test;
CREATE TABLE `t2` (
  `c0` smallint(5) unsigned zerofill DEFAULT NULL,
  `c1` double DEFAULT NULL,
  KEY `i0` (`c0`,`c1` DESC)
) ;

CREATE TABLE `t3` (
  `c0` decimal(10,0) DEFAULT NULL,
  `c1` smallint(209) DEFAULT NULL,
  UNIQUE KEY `c1` (`c1`)
) ;
insert into t2(c0) values(32768);
insert into t3(c1) values (6);
insert into t3(c1) values (NULL);

select t2.c0 from t2,t3 where (not ((t2.c0) <= (t3.c1)));
Empty set (0.00 sec)

select t2.c0, t3.c1, (not ((t2.c0) <= (t3.c1))) from t2,t3;

+-------+------+----------------------------+
| c0    | c1   | (not ((t2.c0) <= (t3.c1))) |
+-------+------+----------------------------+
| 32768 | NULL |                       NULL |
| 32768 |    6 |                          1 |
+-------+------+----------------------------+
2 rows in set (0.00 sec)

[21 Dec 2020 13:38] MySQL Verification Team

Hi Mr. le,

Thank you for your bug report.

However , this is not a bug.

One query does not filter results at all, just process SELECT list, while the other filters results by the condition. Regarding the wrong output in the query with filtering, it is because you are comparing columns where one has a value that is above the maximum value for the signed SMALLINT.

Take a look at our Reference Manual, paragraph 12.1.2.

Not a bug.