Details
Major Description
This example demonstrates a query optimization bug where two identical queries produce different results depending on whether the table has a PRIMARY KEY. In the first query, where the table t0 has a PRIMARY KEY on c0, the query returns a result (1). In the second query, after the table is recreated without the PRIMARY KEY, the query returns an empty set. This discrepancy is likely due to how the optimizer handles the combination of the SQRT() function and the IN clause, especially when the PRIMARY KEY is present. The presence of the primary key seems to affect how the subquery results are evaluated, leading to inconsistent outcomes.
DROP DATABASE IF EXISTS test1; |
CREATE DATABASE test1; |
USE test1; |
-- query1
|
|
|
CREATE TABLE t0(c0 INT , PRIMARY KEY(c0)); |
INSERT INTO t0 VALUES (1363842356); |
|
|
|
|
SELECT 1 FROM t0 AS tom8 WHERE |
( SELECT SQRT( tom11.c0 ) AS c8 FROM t0 AS tom11 LIMIT 1)IN ( SELECT ATAN( MAX( b'101010' IS FALSE ) ) AS c9 FROM t0 AS tom12 ) ; |
|
|
|
|
|
|
-- query2
|
|
|
DROP TABLE t0; |
CREATE TABLE t0(c0 INT ); |
INSERT INTO t0 VALUES (1363842356); |
|
|
|
|
|
|
SELECT 1 FROM t0 AS tom8 WHERE |
( SELECT SQRT( tom11.c0 ) AS c8 FROM t0 AS tom11 LIMIT 1)IN ( SELECT ATAN( MAX( b'101010' IS FALSE ) ) AS c9 FROM t0 AS tom12 ) ; |
|
|
|
|
|
-- output1
|
|
|
+---+ |
| 1 |
|
+---+ |
| 1 |
|
+---+ |
1 row in set (0.00 sec) |
|
|
|
|
|
|
-- output2
|
Empty set (0.00 sec) |
|
|
|
|
|