Bug #118554 Inconsistent results on same tables with different PRIMARY KEY constraint

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

Description:
Inconsistent results on same tables with different PRIMARY KEY constraint

How to repeat:
DROP DATABASE IF EXISTS database234;
CREATE DATABASE database234;
USE database234;
CREATE TABLE t0(c0 DECIMAL  PRIMARY KEY) ;

INSERT IGNORE INTO t0(c0) VALUES(NULL);

SELECT ALL t0.c0 FROM t0 WHERE (t0.c0) IN (LEAST("558939928", '0TjYj'));
-- return empty set

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

SELECT ALL t0.c0 FROM t0 WHERE (t0.c0) IN (LEAST("558939928", '0TjYj'));
--return 1 record

[30 Jun 8:04] MySQL Verification Team

Hello Jiyuan Li,

Thank you for the report and test case.

-- non-ndb SE
- 9.3.0

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

mysql> CREATE DATABASE database234;
Query OK, 1 row affected (0.043 sec)

mysql> USE database234;
Database changed
mysql> CREATE TABLE t0(c0 DECIMAL  PRIMARY KEY) ;
Query OK, 0 rows affected (0.010 sec)

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

mysql>
mysql> SELECT ALL t0.c0 FROM t0 WHERE (t0.c0) IN (LEAST("558939928", '0TjYj'));
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 database234_raw;
Query OK, 0 rows affected, 1 warning (0.002 sec)

mysql> CREATE DATABASE database234_raw;
Query OK, 1 row affected (0.043 sec)

mysql> USE database234_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 database234.t0;
Query OK, 1 row affected (0.002 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT ALL t0.c0 FROM t0 WHERE (t0.c0) IN (LEAST("558939928", '0TjYj'));
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set (0.000 sec)

regards,
Umesh