Bug #118554 Inconsistent results on same tables with different PRIMARY KEY constraint
| Submitted: | 29 Jun 2025 18:42 | Modified: | 30 Jun 2025 8:04 |
|---|---|---|---|
| Reporter: | Jiyuan Li | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 9.3.0 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | x86 |
[30 Jun 2025 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
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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