Bug #102151 select result is mismatch when use COALESCE and not.
Submitted: 5 Jan 13:28 Modified: 7 Feb 6:42
Reporter: tong gao Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0 OS:Linux
Assigned to: CPU Architecture:Any

[5 Jan 13:28] tong gao
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.
[5 Jan 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 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 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 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 19:17] MySQL Verification Team
Verified with Shane's test case.
[6 Jan 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 6:42] tong gao
Hi:
Is there anything about this issue?