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 due to a difference in the table structure. In the first case, the table t0 has a PRIMARY KEY on the column c1. In the second case, the table t0 is created without a PRIMARY KEY on the c1 column. Both queries should logically return the same result, as they are structurally identical, but they yield different outcomes. This discrepancy suggests that the bug is related to the handling of the BIT_XOR function, which behaves differently depending on whether the c1 column is defined as a primary key, leading to different results unexpectedly.
DROP DATABASE IF EXISTS test1; |
CREATE DATABASE test1; |
USE test1; |
|
|
|
|
-- query1
|
CREATE TABLE t0(c0 REAL UNIQUE, c1 REAL ZEROFILL PRIMARY KEY); |
INSERT INTO t0 VALUES (-1, 1); |
|
|
|
|
SELECT IF( YEARWEEK( MAX( '2025' ) , 1 ) <= MAX( tom0.c1 ) , POWER( 1 , 2 ) * COALESCE( 42 ) , BIT_XOR( tom0.c0 ) ) AS c0 |
FROM t0 AS tom0 |
WHERE 1 GROUP BY tom0.c1 |
EXCEPT |
SELECT BIT_XOR( 1) | MIN( COALESCE( DATE_FORMAT( '2025' , '%W %M %Y' ) , 1 ) ) << LN( BIT_XOR( 'A' ) ) AS c4 ; |
|
|
|
|
-- query2
|
|
|
drop table t0; |
CREATE TABLE t0(c0 REAL UNIQUE,c1 REAL zerofill); |
INSERT INTO t0 VALUES (-1, 1); |
SELECT IF( YEARWEEK( MAX( '2025' ) , 1 ) <= MAX( tom0.c1 ) , POWER( 1 , 2 ) * COALESCE( 42 ) , BIT_XOR( tom0.c0 ) ) AS c0 |
FROM t0 AS tom0 |
WHERE 1 GROUP BY tom0.c1 |
EXCEPT |
SELECT BIT_XOR( 1 ) | MIN( COALESCE( DATE_FORMAT( '2025' , '%W %M %Y' ) , 1 ) ) << LN( BIT_XOR( 'A' ) ) AS c4 ; |
|
|
-- output1
|
|
|
+------+ |
| c0 |
|
+------+ |
| -1 |
|
+------+ |
1 row in set, 4 warnings (0.00 sec) |
|
|
|
|
-- output2
|
+-----------------------+ |
| c0 |
|
+-----------------------+ |
| 1.8446744073709552e19 |
|
+-----------------------+ |
1 row in set, 4 warnings (0.01 sec) |
|
|
|
|
|