MySQL Bugs: #108098: double unsigned use key select result error

Bug #108098	double unsigned use key select result error
Submitted:	9 Aug 2022 15:49	Modified:	10 Aug 2022 1:41
Reporter:	haizhen xue	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S5 (Performance)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
CREATE TABLE `t1`(
    `c0` double unsigned DEFAULT NULL,
    key `i0` (`c0`) USING BTREE
);
insert into t1 values(0);

mysql> SELECT t1.c0 FROM t1 WHERE IF(1,-1,"") IN (t1.c0);
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

not correct, expected Empty.

How to repeat:
1. create table and insert valuse(0):
CREATE TABLE `t1`(
    `c0` double unsigned DEFAULT NULL,
    key `i0` (`c0`) USING BTREE
);
insert into t1 values(0);

2. execute sql：
SELECT t1.c0 FROM t1 WHERE IF(1,-1,"") IN (t1.c0);

Suggested fix:
As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type FLOAT, DOUBLE, and DECIMAL (and any synonyms); you should expect support for it to be removed in a future version of MySQL. Consider using a simple CHECK constraint instead for such columns.
When type FLOAT, DOUBLE, and DECIMAL with UNSIGNED attribute, I suggest to print warnning hint.

Hi Mr. xue,

Thank you for your bug report.

However, this is not a bug.

These are the reasons why .....

expr1 is true and expr2 is not NULL, hence expr2 is returned. However, since t1.c0 is unsigned double, it is converted to 0 and which matched the only entry in the table .....

Not a bug.

if type double do not use unsgined attribute, select sql return empty.

it is not a bug, As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type FLOAT, DOUBLE, and DECIMAL (and any synonyms); if create table use unsigned, We'd like to report an warnning.