Bug #118555 Inconsistant results in tables containing NULL values

• Submitted: 29 Jun 18:55
• Modified: 30 Jun 8:09
• 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:55] Jiyuan Li

Description:
Two SELECT should return same results but not

How to repeat:
DROP DATABASE IF EXISTS database152;
CREATE DATABASE database152;
USE database152;
CREATE TABLE t0(c1 DECIMAL  STORAGE DISK PRIMARY KEY COLUMN_FORMAT FIXED NOT NULL UNIQUE KEY) ;

INSERT LOW_PRIORITY IGNORE INTO t0(c1) VALUES(NULL);

SELECT ALL t0.c1 FROM t0 WHERE (COALESCE("0Qfb9%*9쪟", -1129121149)) IN ((+ (t0.c1)));
-- return empty set 

DROP DATABASE IF EXISTS database152_raw;
CREATE DATABASE database152_raw;
USE database152_raw;
CREATE TABLE t0(c1 decimal(10,0));
INSERT INTO t0 SELECT * FROM database152.t0;

SELECT ALL t0.c1 FROM t0 WHERE (COALESCE("0Qfb9%*9쪟", -1129121149)) IN ((+ (t0.c1)));
-- return 1 record

[30 Jun 8:09] MySQL Verification Team

Hello Jiyuan Li,

Thank you for the report and test case.

-- non-ndb SE

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

mysql> CREATE DATABASE database152;
Query OK, 1 row affected (0.059 sec)

mysql> USE database152;
Database changed
mysql> CREATE TABLE t0(c1 DECIMAL  STORAGE DISK PRIMARY KEY COLUMN_FORMAT FIXED NOT NULL UNIQUE KEY) ;
Query OK, 0 rows affected (0.011 sec)

mysql>
mysql> INSERT LOW_PRIORITY IGNORE INTO t0(c1) VALUES(NULL);
Query OK, 1 row affected, 1 warning (0.002 sec)

mysql>
mysql> SELECT ALL t0.c1 FROM t0 WHERE (COALESCE("0Qfb9%*9쪟", -1129121149)) IN ((+ (t0.c1)));
Empty set, 1 warning (0.000 sec)

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

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

mysql> CREATE DATABASE database152_raw;
Query OK, 1 row affected (0.061 sec)

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

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

mysql>
mysql> SELECT ALL t0.c1 FROM t0 WHERE (COALESCE("0Qfb9%*9쪟", -1129121149)) IN ((+ (t0.c1)));
+------+
| c1   |
+------+
|    0 |
+------+
1 row in set (0.000 sec)

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

mysql> show variables like 'version%';
+-------------------------+--------------------------------------+
| Variable_name           | Value                                |
+-------------------------+--------------------------------------+
| version                 | 9.3.0-cluster                        |
| version_comment         | MySQL Cluster Community Server - GPL |
| version_compile_machine | x86_64                               |
| version_compile_os      | Linux                                |
| version_compile_zlib    | 1.3.1                                |
+-------------------------+--------------------------------------+
5 rows in set (0.001 sec)

regards,
Umesh