Bug #118552 Inconsistant result on tables with same data but different constraints

• Submitted: 29 Jun 18:14
• Modified: 30 Jun 7:46
• 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:14] Jiyuan Li

Description:
SELECT returns different results on tables with same data and different constraints

How to repeat:
DROP DATABASE IF EXISTS database422;
CREATE DATABASE database422;
USE database422;
CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL  UNIQUE KEY STORAGE MEMORY COLUMN_FORMAT DYNAMIC) ;

ALTER TABLE t0 RENAME TO t2, CHECKSUM 0, STATS_AUTO_RECALC 0, INSERT_METHOD FIRST, DISABLE KEYS, ROW_FORMAT DEFAULT, DELAY_KEY_WRITE 0, COMPRESSION 'NONE', PACK_KEYS 0, FORCE;

REPLACE DELAYED INTO t2(c0) VALUES('7');

SELECT t2.c0 FROM t2 WHERE (t2.c0) = (GREATEST(509492029, "7)+"));
-- return empty set

DROP DATABASE IF EXISTS database422_raw;
CREATE DATABASE database422_raw;
USE database422_raw;
CREATE TABLE t2(c0 decimal);
INSERT INTO t2 SELECT * FROM database422.t2;

SELECT t2.c0 FROM t2 WHERE (t2.c0) = (GREATEST(509492029, "7)+"));
-- return 1 record

[30 Jun 7:46] MySQL Verification Team

Hello Jiyuan Li,

Thank you for the report and test case.

-- non-ndb SE

bin/mysql -uroot -S/tmp/mysql_4.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 9.3.0-cluster MySQL Cluster Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> CREATE DATABASE database422;
Query OK, 1 row affected (0.072 sec)

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

mysql>
mysql> ALTER TABLE t0 RENAME TO t2, CHECKSUM 0, STATS_AUTO_RECALC 0, INSERT_METHOD FIRST, DISABLE KEYS, ROW_FORMAT DEFAULT, DELAY_KEY_WRITE 0, COMPRESSION 'NONE', PACK_KEYS 0, FORCE;
Query OK, 0 rows affected, 1 warning (0.024 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql>
mysql> REPLACE DELAYED INTO t2(c0) VALUES('7');
Query OK, 1 row affected, 1 warning (0.002 sec)

mysql>
mysql> SELECT t2.c0 FROM t2 WHERE (t2.c0) = (GREATEST(509492029, "7)+"));
Empty set, 1 warning (0.000 sec)

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

mysql> CREATE DATABASE database422_raw;
Query OK, 1 row affected (0.069 sec)

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

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

mysql>
mysql> SELECT t2.c0 FROM t2 WHERE (t2.c0) = (GREATEST(509492029, "7)+"));
+------+
| c0   |
+------+
|    7 |
+------+
1 row in set (0.000 sec)

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

regards,
Umesh