Bug #108098 double unsigned use key select result error
Submitted: 9 Aug 15:49 Modified: 10 Aug 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

[9 Aug 15:49] haizhen xue
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.
[9 Aug 17:19] MySQL Verification Team
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.
[10 Aug 0:50] haizhen xue
if type double do not use unsgined attribute, select sql return empty.
[10 Aug 1:41] haizhen xue
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.