| Bug #101848 | Potentially wrong results | ||
|---|---|---|---|
| Submitted: | 3 Dec 2020 7:41 | Modified: | 11 Apr 2022 9:08 |
| Reporter: | xiaoyang chen | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[3 Dec 2020 13:29]
MySQL Verification Team
Hi Mr. chen, Thank you for your bug report. However, this is not a bug. One query just processes select list, while the other has filtering conditions. Hence, results can not be the same. Not a bug.
[1 Jun 2021 6:48]
Erik Frøseth
Hello,
This is indeed a bug. Here is a simpler test case:
CREATE TABLE a (col1 DECIMAL(10, 0), UNIQUE INDEX i (col1));
INSERT INTO a VALUES (0);
SELECT * FROM a WHERE NOT CONCAT("a") != col1;
SELECT * FROM a IGNORE INDEX (i) WHERE NOT CONCAT("a") != col1;
Notice that you get different result from the last two queries. For some reason, the EXPLAIN from the first query will say "Zero rows":
mysql> EXPLAIN FORMAT=tree SELECT * FROM a WHERE NOT CONCAT("a") != col1;
+--------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------+
| -> Zero rows (no matching row in const table) (cost=0.00..0.00 rows=0)
|
+--------------------------------------------------------------------------+
1 row in set, 1 warning (0,00 sec)
Tested on 8.0.25
[1 Jun 2021 13:10]
MySQL Verification Team
Hi Mr. Froseth, Thank you for your test case. We repeated the exact behaviour that you reported , with your test case. Thanks a lot. Verified as reported.
[11 Apr 2022 9:08]
Erlend Dahl
Duplicate of Bug#102151 select result is mismatch when use COALESCE and not. fixed in 8.0.27.
[11 Apr 2022 13:15]
MySQL Verification Team
Hi Erlend, Thank you very much for your information. Technically speaking, this report should have been the original one, since it preceded #102151. That is however, irrelevant. What is relevant is that bug is fixed.
Description: The following two queries may produce inconsistent result. ``` The first query: SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (NOT ((COALESCE(NULL, "FWlPp", 'TO')) != (t0.c1))) SELECT (NOT ((COALESCE(NULL, "FWlPp", 'TO')) != (t0.c1))) from t0; +----------------------------------------------------+ | (NOT ((COALESCE(NULL, "FWlPp", 'TO')) != (t0.c1))) | +----------------------------------------------------+ | NULL | | NULL | | NULL | | NULL | | NULL | | 1 | | 0 | +----------------------------------------------------+ ```` The second query: SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (NOT ((COALESCE(NULL, "FWlPp", 'TO')) != (t0.c1))); Empty set, 1 warning (0.00 sec) ``` From the first query, the second query will produce at least one row. How to repeat: Following SQL queries will prodce the above scenario: DROP DATABASE IF EXISTS database86; CREATE DATABASE database86; USE database86; CREATE TABLE t0(c0 MEDIUMINT ZEROFILL UNIQUE COLUMN_FORMAT DEFAULT, c1 DECIMAL ZEROFILL COLUMN_FORMAT DYNAMIC UNIQUE NULL) DELAY_KEY_WRITE = 0; REPLACE INTO t0(c1, c0) VALUES(NULL, NULL); CREATE INDEX i0 ON t0(c0 ASC) VISIBLE ALGORITHM COPY; REPLACE INTO t0(c0, c1) VALUES(NULL, -982707540), (NULL, NULL); INSERT IGNORE INTO t0(c0) VALUES("h+h"); INSERT INTO t0(c0) VALUES(1.09976033E8), (-710330027); REPLACE INTO t0(c0) VALUES(NULL); select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database86'; REPLACE INTO t0(c0) VALUES("9+Y횖Hg]"), (-119610350); REPLACE DELAYED INTO t0(c0) VALUES(-119610350), (NULL), (NULL); INSERT HIGH_PRIORITY IGNORE INTO t0(c1, c0) VALUES(-1997233864, 756247533), (-601897409, -1563963709), (-1997233864, -7.91161194E8); REPLACE INTO t0(c1, c0) VALUES(NULL, 1643502365); INSERT IGNORE INTO t0(c1, c0) VALUES(-791161194, 1.643502365E9); select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database86'; INSERT INTO t0(c0) VALUES(NULL); DELETE LOW_PRIORITY FROM t0 WHERE t0.c0; TRUNCATE TABLE t0; INSERT IGNORE INTO t0(c1) VALUES(968599958); SET SESSION big_tables = OFF; REPLACE INTO t0(c0) VALUES("m"); INSERT HIGH_PRIORITY IGNORE INTO t0(c1, c0) VALUES("", -1.1961035E8); SET SESSION tmp_table_size = 5951427079732767674; INSERT DELAYED IGNORE INTO t0(c1) VALUES(NULL); REPLACE INTO t0(c1, c0) VALUES(NULL, -2117075416); SET SESSION rbr_exec_mode = IDEMPOTENT; REPLACE INTO t0(c0) VALUES(1790433641); INSERT INTO t0(c0) VALUES(NULL); REPLACE INTO t0(c1) VALUES("5to"); REPLACE LOW_PRIORITY INTO t0(c1) VALUES(NULL); INSERT DELAYED IGNORE INTO t0(c1) VALUES(NULL), (NULL); SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (NOT ((COALESCE(NULL, "FWlPp", 'TO')) != (t0.c1)))