Description:
Inserting into a float value exceeding the precision, using IF function and using the column return different result.
mysql> SELECT ALL t2.c1 AS ref0, t2.c2 AS ref1, t2.c3 AS ref2, t2.c4 AS ref3, t0.c0 AS ref4, t0.c1 AS ref5, t0.c2 AS ref6 FROM t2, t0 WHERE (NOT ((IF(0.07911034159919772, t2.c4, t2.c3)) NOT IN (t0.c2)));
Empty set (0.00 sec)
mysql> SELECT ALL t2.c1 AS ref0, t2.c2 AS ref1, t2.c3 AS ref2, t2.c4 AS ref3, t0.c0 AS ref4, t0.c1 AS ref5, t0.c2 AS ref6,(NOT ((IF(0.07911034159919772, t2.c4, t2.c3)) NOT IN (t0.c2))) FROM t2, t0 where NOT (t2.c4 NOT IN (t0.c2));
+------------------------+------+------+----------+------+------+----------+----------------------------------------------------------------+
| ref0 | ref1 | ref2 | ref3 | ref4 | ref5 | ref6 | (NOT ((IF(0.07911034159919772, t2.c4, t2.c3)) NOT IN (t0.c2))) |
+------------------------+------+------+----------+------+------+----------+----------------------------------------------------------------+
| 0000.12313180818503089 | NULL | NULL | 0.182441 | NULL | NULL | 0.182441 | 1 |
+------------------------+------+------+----------+------+------+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
How to repeat:
CREATE TABLE `t2` (
`c1` double unsigned zerofill /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL COMMENT 'asdf',
`c2` float /*!50606 STORAGE DISK */ DEFAULT NULL,
`c3` varchar(500) DEFAULT NULL,
`c4` float DEFAULT NULL,
UNIQUE KEY `c1` (`c1`),
UNIQUE KEY `c2` (`c2`),
UNIQUE KEY `i0` (`c1`,`c4`,`c3`(3)),
KEY `i1` (`c3`)
) ENGINE=Dstore DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci PACK_KEYS=0 STATS_AUTO_RECALC=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMPRESSION='NONE';
CREATE TABLE `t0` (
`c0` float /*!50606 STORAGE MEMORY */ DEFAULT NULL COMMENT 'asdf',
`c1` double unsigned zerofill /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL COMMENT 'asdf',
`c2` float /*!50606 STORAGE DISK */ DEFAULT NULL,
UNIQUE KEY `c1` (`c1`),
UNIQUE KEY `c2` (`c2`),
UNIQUE KEY `i2` (`c1`) USING BTREE
) ENGINE=Dstore DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci PACK_KEYS=0 STATS_AUTO_RECALC=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=REDUNDANT COMPRESSION='LZ4';
-- 向t2表插入数据
INSERT INTO t2 (c1, c2, c3, c4) VALUES
(0000.12313180818503089, NULL, NULL, 0.18244101107120514);
-- 向t0表插入数据
INSERT INTO t0 (c0, c1, c2) VALUES
(NULL, NULL, 0.18244101107120514);
mysql> SELECT ALL t2.c1 AS ref0, t2.c2 AS ref1, t2.c3 AS ref2, t2.c4 AS ref3, t0.c0 AS ref4, t0.c1 AS ref5, t0.c2 AS ref6 FROM t2, t0 WHERE (NOT ((IF(0.07911034159919772, t2.c4, t2.c3)) NOT IN (t0.c2)));
Empty set (0.00 sec)
mysql> SELECT ALL t2.c1 AS ref0, t2.c2 AS ref1, t2.c3 AS ref2, t2.c4 AS ref3, t0.c0 AS ref4, t0.c1 AS ref5, t0.c2 AS ref6,(NOT ((IF(0.07911034159919772, t2.c4, t2.c3)) NOT IN (t0.c2))) FROM t2, t0 where NOT (t2.c4 NOT IN (t0.c2));
+------------------------+------+------+----------+------+------+----------+----------------------------------------------------------------+
| ref0 | ref1 | ref2 | ref3 | ref4 | ref5 | ref6 | (NOT ((IF(0.07911034159919772, t2.c4, t2.c3)) NOT IN (t0.c2))) |
+------------------------+------+------+----------+------+------+----------+----------------------------------------------------------------+
| 0000.12313180818503089 | NULL | NULL | 0.182441 | NULL | NULL | 0.182441 | 1 |
+------------------------+------+------+----------+------+------+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
Suggested fix:
the 2 queries shall return the same result set.
