| Bug #102151 | select result is mismatch when use COALESCE and not. | ||
|---|---|---|---|
| Submitted: | 5 Jan 2021 13:28 | Modified: | 16 Aug 2021 18:54 |
| Reporter: | tong gao | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[5 Jan 2021 15:20]
MySQL Verification Team
Thank you for the bug report. Please see below error on the test case?
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE database195;
Query OK, 1 row affected (0.03 sec)
mysql> USE database195;
Database changed
mysql> CREATE TABLE t0(c0 DECIMAL ZEROFILL STORAGE MEMORY ) ;
Query OK, 0 rows affected, 1 warning (0.10 sec)
mysql> ALTER TABLE t0 RENAME t0;
Query OK, 0 rows affected (0.02 sec)
mysql> CHECK TABLE t0 FOR UPGRADE FAST EXTENDED CHANGED QUICK MEDIUM;
+----------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| database195.t0 | check | status | OK |
+----------------+-------+----------+----------+
1 row in set (0.01 sec)
mysql> INSERT IGNORE INTO t0(c0) VALUES('r105169326'), (1.250308188E9);
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> ALTER TABLE t0 STATS_AUTO_RECALC DEFAULT, ROW_FORMAT COMPACT, STATS_PERSISTENT 1, PACK_KEYS DEFAULT, CHECKSUM 0, DELAY_KEY_WRITE 1, RENAME t1, ALGORITHM INSTANT, COMPRESSION 'ZLIB', DISABLE KEYS;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY.
mysql>
[5 Jan 2021 15:47]
tong gao
Ignore the error and continue the remaining SQL statements. Eventually, you'll see this： mysql> SELECT t2.c0 FROM t2 WHERE "M" = t2.c0; +------------+ | c0 | +------------+ | 0000000000 | | 0000000000 | | 0000000000 | | 0000000000 | | 0000000000 | +------------+ 5 rows in set, 1 warning (0.00 sec) mysql> mysql> mysql> SELECT t2.c0 FROM t2 WHERE (((COALESCE(NULL, "M", "b", "W", -1775599917)) = (t2.c0))); Empty set, 3 warnings (0.00 sec) mysql> mysql> mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.22 | +-----------+ 1 row in set (0.00 sec) mysql>
[5 Jan 2021 17:30]
MySQL Verification Team
Below the result ignoring errors. Please provide a clean and repeatable test case. Thanks.
miguel@uxenial:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE database195;
ALTER TABLE t0 RENAME t0;
Query OK, 1 row affected (0.02 sec)
mysql> USE database195;
CHECK TABLE t0 FOR UPGRADE FAST EXTENDED CHANGED QUICK MEDIUM;
Database changed
mysql> CREATE TABLE t0(c0 DECIMAL ZEROFILL STORAGE MEMORY ) ;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> ALTER TABLE t0 RENAME t0;
INSERT IGNORE INTO t0(c0) VALUES('r105169326'), (1.250308188E9);
ALTER TABLE t0 STATS_AUTO_RECALC DEFAULT, ROW_FORMAT COMPACT, STATS_PERSISTENT 1, PACK_KEYS DEFAULT, CHECKSUM 0, DELAY_KEY_WRITE 1, RENAME t1, ALGORITHM INSTANT, COMPRESSION 'ZLIB', DISABLE KEYS;
ALTER TABLE t0 FORCE, STATS_AUTO_RECALC 1, RENAME AS t2, STATS_PERSISTENT 1, PACK_KEYS 1, DELAY_KEY_WRITE 1, COMPRESSION 'ZLIB', ROW_FORMAT COMPRESSED, ENABLE KEYS, ALGORITHM COPY;
Query OK, 0 rows affected (0.00 sec)
mysql> CHECK TABLE t0 FOR UPGRADE FAST EXTENDED CHANGED QUICK MEDIUM;
+----------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| database195.t0 | check | status | OK |
+----------------+-------+----------+----------+
1 row in set (0.01 sec)
mysql> INSERT IGNORE INTO t0(c0) VALUES('r105169326'), (1.250308188E9);
SET SESSION max_sort_length = 7531760;
CHECKSUM TABLE t2 EXTENDED;
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> ALTER TABLE t0 STATS_AUTO_RECALC DEFAULT, ROW_FORMAT COMPACT, STATS_PERSISTENT 1, PACK_KEYS DEFAULT, CHECKSUM 0, DELAY_KEY_WRITE 1, RENAME t1, ALGORITHM INSTANT, COMPRESSION 'ZLIB', DISABLE KEYS;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY.
mysql> ALTER TABLE t0 FORCE, STATS_AUTO_RECALC 1, RENAME AS t2, STATS_PERSISTENT 1, PACK_KEYS 1, DELAY_KEY_WRITE 1, COMPRESSION 'ZLIB', ROW_FORMAT COMPRESSED, ENABLE KEYS, ALGORITHM COPY;
ERROR 1031 (HY000): Table storage engine for '#sql-355_9' doesn't have this option
mysql> SET SESSION max_sort_length = 7531760;
Query OK, 0 rows affected (0.00 sec)
mysql> CHECKSUM TABLE t2 EXTENDED;
+----------------+----------+
| Table | Checksum |
+----------------+----------+
| database195.t2 | NULL |
+----------------+----------+
1 row in set, 1 warning (0.00 sec)
mysql> DELETE IGNORE FROM t2;
ERROR 1146 (42S02): Table 'database195.t2' doesn't exist
mysql> DELETE LOW_PRIORITY QUICK IGNORE FROM t2;
ERROR 1146 (42S02): Table 'database195.t2' doesn't exist
mysql> TRUNCATE TABLE t2;
ERROR 1146 (42S02): Table 'database195.t2' doesn't exist
mysql> CREATE INDEX i0 ON t2(c0 ASC);
ERROR 1146 (42S02): Table 'database195.t2' doesn't exist
mysql> INSERT IGNORE INTO t2(c0) VALUES(NULL);
ERROR 1146 (42S02): Table 'database195.t2' doesn't exist
mysql> INSERT IGNORE INTO t2(c0) VALUES('+');
ERROR 1146 (42S02): Table 'database195.t2' doesn't exist
mysql> REPLACE DELAYED INTO t2(c0) VALUES(NULL);
ERROR 1146 (42S02): Table 'database195.t2' doesn't exist
mysql> INSERT IGNORE INTO t2(c0) VALUES("Fc|X4");
ERROR 1146 (42S02): Table 'database195.t2' doesn't exist
mysql> INSERT DELAYED IGNORE INTO t2(c0) VALUES(NULL), (NULL), (-1884484723), ("O'8㿮 ~)"), ("#k)rKf");
ERROR 1146 (42S02): Table 'database195.t2' doesn't exist
mysql> ALTER TABLE t2 FORCE;
ERROR 1146 (42S02): Table 'database195.t2' doesn't exist
mysql> INSERT LOW_PRIORITY IGNORE INTO t2(c0) VALUES(NULL);
ERROR 1146 (42S02): Table 'database195.t2' doesn't exist
mysql> ANALYZE LOCAL TABLE t2;
+----------------+---------+----------+--------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+---------+----------+--------------------------------------+
| database195.t2 | analyze | Error | Table 'database195.t2' doesn't exist |
| database195.t2 | analyze | status | Operation failed |
+----------------+---------+----------+--------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT t2.c0 FROM t2 WHERE "M" = t2.c0;
ERROR 1146 (42S02): Table 'database195.t2' doesn't exist
mysql> SELECT t2.c0 FROM t2 WHERE (((COALESCE(NULL, "M", "b", "W", -1775599917)) = (t2.c0)));
ERROR 1146 (42S02): Table 'database195.t2' doesn't exist
mysql>
[5 Jan 2021 18:05]
MySQL Verification Team
TESTCASE -------------- set sql_mode=default; drop table if exists t; create table t (a decimal(10,0),key(a)) engine=innodb; insert into t values (null),(0); select a from t force index(a) where 'm'=a; select a from t force index(a) where coalesce(null, 'm', 'b', 'w')=a; select a from t ignore index(a) where 'm'=a; select a from t ignore index(a) where coalesce(null, 'm', 'b', 'w')=a; select version(),@@sql_mode;
[5 Jan 2021 19:17]
MySQL Verification Team
Verified with Shane's test case.
[6 Jan 2021 1:32]
tong gao
Shane's test case:
$ /opt/gaotong/mysql/mysql-8.0.22/bin/mysql -uroot -pTaurus_123 -h127.0.0.1 -P8022
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22 Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set sql_mode=default;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table t (a decimal(10,0),key(a)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values (null),(0);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select a from t force index(a) where 'm'=a;
+------+
| a |
+------+
| 0 |
+------+
1 row in set, 1 warning (0.00 sec)
mysql> select a from t force index(a) where coalesce(null, 'm', 'b', 'w')=a;
Empty set, 3 warnings (0.00 sec)
mysql> select a from t ignore index(a) where 'm'=a;
+------+
| a |
+------+
| 0 |
+------+
1 row in set, 1 warning (0.00 sec)
mysql> select a from t ignore index(a) where coalesce(null, 'm', 'b', 'w')=a;
+------+
| a |
+------+
| 0 |
+------+
1 row in set, 1 warning (0.00 sec)
mysql> select version(),@@sql_mode;
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| version() | @@sql_mode |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| 8.0.22 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
===========================================================
my test case：
DROP DATABASE IF EXISTS database195;
CREATE DATABASE database195;
USE database195;
CREATE TABLE t0(c0 DECIMAL ZEROFILL STORAGE MEMORY ) ;
CHECK TABLE t0 FOR UPGRADE FAST EXTENDED CHANGED QUICK MEDIUM;
INSERT IGNORE INTO t0(c0) VALUES('105169326'), (1.250308188E9);
ALTER TABLE t0 FORCE, STATS_AUTO_RECALC 1, RENAME AS t2, STATS_PERSISTENT 1, PACK_KEYS 1, DELAY_KEY_WRITE 1, COMPRESSION 'ZLIB', ROW_FORMAT COMPRESSED, ENABLE KEYS, ALGORITHM COPY;
DELETE LOW_PRIORITY QUICK IGNORE FROM t2;
TRUNCATE TABLE t2;
CREATE INDEX i0 ON t2(c0 ASC);
INSERT IGNORE INTO t2(c0) VALUES(NULL);
INSERT IGNORE INTO t2(c0) VALUES('+');
REPLACE DELAYED INTO t2(c0) VALUES(NULL);
INSERT IGNORE INTO t2(c0) VALUES("Fc|X4");
INSERT DELAYED IGNORE INTO t2(c0) VALUES(NULL), (NULL), (-1884484723), ("O'8㿮 ~)"), ("#k)rKf");
INSERT LOW_PRIORITY IGNORE INTO t2(c0) VALUES(NULL);
ANALYZE LOCAL TABLE t2;
SELECT t2.c0 FROM t2 WHERE "M" = t2.c0;
SELECT t2.c0 FROM t2 WHERE (((COALESCE(NULL, "M", "b", "W", -1775599917)) = (t2.c0)));
result:
$ /opt/gaotong/mysql/mysql-8.0.22/bin/mysql -uroot -pTaurus_123 -h127.0.0.1 -P8022
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22 Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> DROP DATABASE IF EXISTS database195;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE DATABASE database195;
Query OK, 1 row affected (0.00 sec)
mysql> USE database195;
Database changed
mysql> CREATE TABLE t0(c0 DECIMAL ZEROFILL STORAGE MEMORY ) ;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> CHECK TABLE t0 FOR UPGRADE FAST EXTENDED CHANGED QUICK MEDIUM;
+----------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| database195.t0 | check | status | OK |
+----------------+-------+----------+----------+
1 row in set (0.00 sec)
mysql> INSERT IGNORE INTO t0(c0) VALUES('105169326'), (1.250308188E9);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t0 FORCE, STATS_AUTO_RECALC 1, RENAME AS t2, STATS_PERSISTENT 1, PACK_KEYS 1, DELAY_KEY_WRITE 1, COMPRESSION 'ZLIB', ROW_FORMAT COMPRESSED, ENABLE KEYS, ALGORITHM COPY;
Query OK, 2 rows affected, 2 warnings (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 2
mysql> DELETE LOW_PRIORITY QUICK IGNORE FROM t2;
Query OK, 2 rows affected (0.00 sec)
mysql> TRUNCATE TABLE t2;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE INDEX i0 ON t2(c0 ASC);
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> INSERT IGNORE INTO t2(c0) VALUES(NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO t2(c0) VALUES('+');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> REPLACE DELAYED INTO t2(c0) VALUES(NULL);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> INSERT IGNORE INTO t2(c0) VALUES("Fc|X4");
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> INSERT DELAYED IGNORE INTO t2(c0) VALUES(NULL), (NULL), (-1884484723), ("O'8㿮 ~)"), ("#k)rKf");
Query OK, 5 rows affected, 4 warnings (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 4
mysql> INSERT LOW_PRIORITY IGNORE INTO t2(c0) VALUES(NULL);
Query OK, 1 row affected (0.00 sec)
mysql> ANALYZE LOCAL TABLE t2;
+----------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| database195.t2 | analyze | status | OK |
+----------------+---------+----------+----------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> SELECT t2.c0 FROM t2 WHERE "M" = t2.c0;
+------------+
| c0 |
+------------+
| 0000000000 |
| 0000000000 |
| 0000000000 |
| 0000000000 |
| 0000000000 |
+------------+
5 rows in set, 1 warning (0.00 sec)
mysql> SELECT t2.c0 FROM t2 WHERE (((COALESCE(NULL, "M", "b", "W", -1775599917)) = (t2.c0)));
Empty set, 3 warnings (0.00 sec)
mysql>
mysql> select version(),@@sql_mode;
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| version() | @@sql_mode |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| 8.0.22 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
[7 Feb 2021 6:42]
tong gao
Hi： Is there anything about this issue?
[16 Aug 2021 18:54]
Christine Cole
Posted by developer: Fixed as of the upcoming MySQL 8.0.27 release, and here's the proposed changelog entry from the documentation team: Inconsistencies in how certain comparison predicates were evaluated (for example, when part of a WHERE clause) could return different results if a function was used instead of a string literal. Thank you for the bug report.
[11 Apr 2022 9:07]
Erlend Dahl
Bug#101269 using different index may produce different results was marked as a duplicate.
Description: When the following two SQL statements are executed, the same result is expected. However, the query result is not the same. sql: SELECT t2.c0 FROM t2 WHERE "M" = t2.c0; SELECT t2.c0 FROM t2 WHERE (((COALESCE(NULL, "M", "b", "W", -1775599917)) = (t2.c0))); tested in mysql 8.0.18 - 8.0.22 How to repeat: 1、run the following SQL statement: DROP DATABASE IF EXISTS database195; CREATE DATABASE database195; USE database195; CREATE TABLE t0(c0 DECIMAL ZEROFILL STORAGE MEMORY ) ; ALTER TABLE t0 RENAME t0; CHECK TABLE t0 FOR UPGRADE FAST EXTENDED CHANGED QUICK MEDIUM; INSERT IGNORE INTO t0(c0) VALUES('r105169326'), (1.250308188E9); ALTER TABLE t0 STATS_AUTO_RECALC DEFAULT, ROW_FORMAT COMPACT, STATS_PERSISTENT 1, PACK_KEYS DEFAULT, CHECKSUM 0, DELAY_KEY_WRITE 1, RENAME t1, ALGORITHM INSTANT, COMPRESSION 'ZLIB', DISABLE KEYS; ALTER TABLE t0 FORCE, STATS_AUTO_RECALC 1, RENAME AS t2, STATS_PERSISTENT 1, PACK_KEYS 1, DELAY_KEY_WRITE 1, COMPRESSION 'ZLIB', ROW_FORMAT COMPRESSED, ENABLE KEYS, ALGORITHM COPY; SET SESSION max_sort_length = 7531760; CHECKSUM TABLE t2 EXTENDED; DELETE IGNORE FROM t2; DELETE LOW_PRIORITY QUICK IGNORE FROM t2; TRUNCATE TABLE t2; CREATE INDEX i0 ON t2(c0 ASC); INSERT IGNORE INTO t2(c0) VALUES(NULL); INSERT IGNORE INTO t2(c0) VALUES('+'); REPLACE DELAYED INTO t2(c0) VALUES(NULL); INSERT IGNORE INTO t2(c0) VALUES("Fc|X4"); INSERT DELAYED IGNORE INTO t2(c0) VALUES(NULL), (NULL), (-1884484723), ("O'8㿮 ~)"), ("#k)rKf"); ALTER TABLE t2 FORCE; INSERT LOW_PRIORITY IGNORE INTO t2(c0) VALUES(NULL); ANALYZE LOCAL TABLE t2; 2、run the following SQL statements: SELECT t2.c0 FROM t2 WHERE "M" = t2.c0; SELECT t2.c0 FROM t2 WHERE (((COALESCE(NULL, "M", "b", "W", -1775599917)) = (t2.c0))); 3、we expect the same results.