Description:
After executing a sequence of DDL statements on a table, a query returns a different result when executed on a schema-equivalent table reconstructed using SHOW CREATE TABLE.
The original table and the reconstructed table have identical schemas and identical data, but the same SELECT query returns different results.
This indicates that query semantics depend on the history of DDL operations rather than the final schema.
How to repeat:
Step 1: Create and modify the table
DROP DATABASE IF EXISTS bug_repro_db_v2;
CREATE DATABASE bug_repro_db_v2;
USE bug_repro_db_v2;
CREATE TABLE t0 (
c1 NUMERIC UNSIGNED ZEROFILL NOT NULL,
c2 TINYTEXT,
c3 VARCHAR(4)
);
ALTER TABLE t0 ADD UNIQUE (c2(3), c3(1));
ALTER TABLE t0 MODIFY c1 NUMERIC(29,4);
ALTER TABLE t0 ADD PRIMARY KEY (c2(3), c1);
ALTER TABLE t0 DROP INDEX c2;
ALTER TABLE t0 ALTER COLUMN c3 DROP DEFAULT;
ALTER TABLE t0 ALTER c2 SET VISIBLE;
ALTER TABLE t0 RENAME COLUMN c3 TO c4;
ALTER TABLE t0 ADD c3 FLOAT8;
INSERT INTO t0 (c1, c2, c3, c4)
VALUES (60, 'y', 57.0548, 'z');
Step 2: Reconstruct the table using SHOW CREATE TABLE
CREATE TABLE t0_shadow (
`c1` decimal(29,4) NOT NULL,
`c2` tinytext NOT NULL,
`c4` varchar(4),
`c3` double DEFAULT NULL,
PRIMARY KEY (`c2`(3),`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO t0_shadow SELECT * FROM t0;
Step 3: Execute the query
SELECT * FROM t0
WHERE 8 NOT IN (SELECT c2 FROM t0);
SELECT * FROM t0_shadow
WHERE 8 NOT IN (SELECT c2 FROM t0_shadow);
🔹 Expected Result
Both queries should return identical results,
since the two tables have equivalent schemas and identical data.
🔹 Actual Result
The query on t0 returns 1 row,
while the same query on t0_shadow returns 0 rows.
This appears to be a metadata-related logic bug.
Although the final schemas are equivalent, the query result depends on the DDL history.
This may lead to silent data correctness issues in real-world applications.
© 2026, Oracle Corporation and/or its affiliates
Description: After executing a sequence of DDL statements on a table, a query returns a different result when executed on a schema-equivalent table reconstructed using SHOW CREATE TABLE. The original table and the reconstructed table have identical schemas and identical data, but the same SELECT query returns different results. This indicates that query semantics depend on the history of DDL operations rather than the final schema. How to repeat: Step 1: Create and modify the table DROP DATABASE IF EXISTS bug_repro_db_v2; CREATE DATABASE bug_repro_db_v2; USE bug_repro_db_v2; CREATE TABLE t0 ( c1 NUMERIC UNSIGNED ZEROFILL NOT NULL, c2 TINYTEXT, c3 VARCHAR(4) ); ALTER TABLE t0 ADD UNIQUE (c2(3), c3(1)); ALTER TABLE t0 MODIFY c1 NUMERIC(29,4); ALTER TABLE t0 ADD PRIMARY KEY (c2(3), c1); ALTER TABLE t0 DROP INDEX c2; ALTER TABLE t0 ALTER COLUMN c3 DROP DEFAULT; ALTER TABLE t0 ALTER c2 SET VISIBLE; ALTER TABLE t0 RENAME COLUMN c3 TO c4; ALTER TABLE t0 ADD c3 FLOAT8; INSERT INTO t0 (c1, c2, c3, c4) VALUES (60, 'y', 57.0548, 'z'); Step 2: Reconstruct the table using SHOW CREATE TABLE CREATE TABLE t0_shadow ( `c1` decimal(29,4) NOT NULL, `c2` tinytext NOT NULL, `c4` varchar(4), `c3` double DEFAULT NULL, PRIMARY KEY (`c2`(3),`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO t0_shadow SELECT * FROM t0; Step 3: Execute the query SELECT * FROM t0 WHERE 8 NOT IN (SELECT c2 FROM t0); SELECT * FROM t0_shadow WHERE 8 NOT IN (SELECT c2 FROM t0_shadow); 🔹 Expected Result Both queries should return identical results, since the two tables have equivalent schemas and identical data. 🔹 Actual Result The query on t0 returns 1 row, while the same query on t0_shadow returns 0 rows. This appears to be a metadata-related logic bug. Although the final schemas are equivalent, the query result depends on the DDL history. This may lead to silent data correctness issues in real-world applications.