Bug #118553 SELECT returns different results on tables with same data

• Submitted: 29 Jun 18:26
• Modified: 30 Jun 8:03
• Reporter: Jiyuan Li
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 9.3.0
• OS: Ubuntu
• CPU Architecture: x86

[29 Jun 18:26] Jiyuan Li

Description:
SELECT returns different results on tables with same data

How to repeat:
DROP DATABASE IF EXISTS database357;
CREATE DATABASE database357;
USE database357;
CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL  NULL UNIQUE KEY STORAGE MEMORY , c1 FLOAT ) ;

ALTER TABLE t0 INSERT_METHOD LAST, ROW_FORMAT REDUNDANT, RENAME t0, COMPRESSION 'LZ4', DELAY_KEY_WRITE 0, DROP c1, ENABLE KEYS, PACK_KEYS 0, STATS_PERSISTENT DEFAULT, CHECKSUM 1, FORCE;

INSERT IGNORE INTO t0(c0) VALUES("~j!n");

SELECT ALL t0.c0 FROM t0 WHERE (COALESCE(NULL, '0Qn>')) = (t0.c0);
-- return empty set

DROP DATABASE IF EXISTS database357_raw;
CREATE DATABASE database357_raw;
USE database357_raw;
CREATE TABLE t0(c0 decimal(10,0));
INSERT INTO t0 SELECT * FROM database357.t0;

SELECT ALL t0.c0 FROM t0 WHERE (COALESCE(NULL, '0Qn>')) = (t0.c0);
-- return 1 record

[30 Jun 8:03] MySQL Verification Team

Hello Jiyuan Li,

Thank you for the report and test case.

-- non-ndb SE

mysql> DROP DATABASE IF EXISTS database357;
Query OK, 0 rows affected, 1 warning (0.002 sec)

mysql> CREATE DATABASE database357;
Query OK, 1 row affected (0.060 sec)

mysql> USE database357;
Database changed
mysql> CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL  NULL UNIQUE KEY STORAGE MEMORY , c1 FLOAT ) ;
Query OK, 0 rows affected (0.012 sec)

mysql>
mysql> ALTER TABLE t0 INSERT_METHOD LAST, ROW_FORMAT REDUNDANT, RENAME t0, COMPRESSION 'LZ4', DELAY_KEY_WRITE 0, DROP c1, ENABLE KEYS, PACK_KEYS 0, STATS_PERSISTENT DEFAULT, CHECKSUM 1, FORCE;
Query OK, 0 rows affected, 1 warning (0.028 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql>
mysql> INSERT IGNORE INTO t0(c0) VALUES("~j!n");
Query OK, 1 row affected, 1 warning (0.001 sec)

mysql>
mysql> SELECT ALL t0.c0 FROM t0 WHERE (COALESCE(NULL, '0Qn>')) = (t0.c0);
Empty set, 1 warning (0.000 sec)

mysql> -- return empty set
Query OK, 0 rows affected (0.000 sec)

mysql> DROP DATABASE IF EXISTS database357_raw;
Query OK, 0 rows affected, 1 warning (0.002 sec)

mysql> CREATE DATABASE database357_raw;
Query OK, 1 row affected (0.046 sec)

mysql> USE database357_raw;
Database changed
mysql> CREATE TABLE t0(c0 decimal(10,0));
Query OK, 0 rows affected (0.010 sec)

mysql> INSERT INTO t0 SELECT * FROM database357.t0;
Query OK, 1 row affected (0.002 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT ALL t0.c0 FROM t0 WHERE (COALESCE(NULL, '0Qn>')) = (t0.c0);
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set (0.000 sec)

mysql> -- return 1 record
Query OK, 0 rows affected (0.000 sec)

regards,
Umesh