Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.2.2
-
None
-
None
Description
This example demonstrates a query optimization bug where two seemingly identical queries produce different results. In the first case, the table t1 has a PRIMARY KEY on column c1, and the query returns 1. In the second case, the table is recreated without the PRIMARY KEY, and the query returns an empty set. Both queries are logically equivalent, but the presence of the PRIMARY KEY in the first query seems to affect the behavior of the STD() function and the IN clause, resulting in inconsistent results. This suggests that the bug is related to how STD() and IN interact with the query optimizer when a primary key is present.
DROP DATABASE IF EXISTS test1; |
CREATE DATABASE test1; |
USE test1; |
-- query1
|
|
|
CREATE OR REPLACE TABLE t1 (c0 REAL , c1 REAL PRIMARY KEY); |
INSERT INTO t1 VALUES (1, 1); |
INSERT INTO t1 VALUES (-1, 1); |
|
|
SELECT 1 |
FROM t1 AS tom5 WHERE |
( SELECT tom6.c1 AS c13 FROM t1 AS tom6 LIMIT 1)IN ( SELECT STD( b'101010' ) AS c14 FROM t1 AS tom7 WHERE 1 ) ; |
|
|
-- query2
|
|
|
CREATE OR REPLACE TABLE t1 (c0 REAL , c1 REAL ); |
INSERT INTO t1 VALUES (1, 1); |
INSERT INTO t1 VALUES (-1, 1); |
|
|
SELECT 1 |
FROM t1 AS tom5 WHERE |
( SELECT tom6.c1 AS c13 FROM t1 AS tom6 LIMIT 1)IN ( SELECT STD( b'101010' ) AS c14 FROM t1 AS tom7 WHERE 1 ) ; |
|
|
|
-- output1
|
+---+ |
| 1 |
|
+---+ |
| 1 |
|
+---+ |
1 row in set (0.00 sec) |
|
|
-- output2
|
Empty set (0.00 sec) |
|