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:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:9.3.0 OS:Ubuntu
Assigned to: CPU Architecture:x86

[29 Jun 2025 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 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.