Description:
The same SQL query, which is logically identical except for the presence of a primary key on the c0 column, produces different results. The behavior appears to be related to how the TRUNCATE function interacts with the primary key column. This discrepancy should not occur, and it suggests that there might be a bug related to the TRUNCATE function’s handling in queries involving EXCEPT.
How to repeat:
---sql1
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE t1(c0 DOUBLE PRIMARY KEY);
insert INTO t1(c0) VALUES(1);
WITH
tom30 AS (
SELECT TRUNCATE( t1.c0 , 1 ) FROM t1
EXCEPT SELECT MIN( 3.14159 ) FROM ( SELECT b'1' AS c13 LIMIT 0 ) AS tom29
)
SELECT * FROM tom30 ;
--return
Empty set (0.00 sec)
---sql2.The same table as sql1
SELECT TRUNCATE( t1.c0 , 1 ) FROM t1
EXCEPT SELECT MIN( 3.14159 ) FROM ( SELECT b'1' AS c13 LIMIT 0 ) AS tom29;
--return
+-----------------------+
| TRUNCATE( t1.c0 , 1 ) |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
---sql3
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE t1(c0 DOUBLE );
insert INTO t1(c0) VALUES(1);
WITH
tom30 AS (
SELECT TRUNCATE( t1.c0 , 1 ) FROM t1
EXCEPT SELECT MIN( 3.14159 ) FROM ( SELECT b'1' AS c13 LIMIT 0 ) AS tom29
)
SELECT * FROM tom30 ;
+-----------------------+
| TRUNCATE( t1.c0 , 1 ) |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
Description: The same SQL query, which is logically identical except for the presence of a primary key on the c0 column, produces different results. The behavior appears to be related to how the TRUNCATE function interacts with the primary key column. This discrepancy should not occur, and it suggests that there might be a bug related to the TRUNCATE function’s handling in queries involving EXCEPT. How to repeat: ---sql1 DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE t1(c0 DOUBLE PRIMARY KEY); insert INTO t1(c0) VALUES(1); WITH tom30 AS ( SELECT TRUNCATE( t1.c0 , 1 ) FROM t1 EXCEPT SELECT MIN( 3.14159 ) FROM ( SELECT b'1' AS c13 LIMIT 0 ) AS tom29 ) SELECT * FROM tom30 ; --return Empty set (0.00 sec) ---sql2.The same table as sql1 SELECT TRUNCATE( t1.c0 , 1 ) FROM t1 EXCEPT SELECT MIN( 3.14159 ) FROM ( SELECT b'1' AS c13 LIMIT 0 ) AS tom29; --return +-----------------------+ | TRUNCATE( t1.c0 , 1 ) | +-----------------------+ | 1 | +-----------------------+ 1 row in set (0.00 sec) ---sql3 DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE t1(c0 DOUBLE ); insert INTO t1(c0) VALUES(1); WITH tom30 AS ( SELECT TRUNCATE( t1.c0 , 1 ) FROM t1 EXCEPT SELECT MIN( 3.14159 ) FROM ( SELECT b'1' AS c13 LIMIT 0 ) AS tom29 ) SELECT * FROM tom30 ; +-----------------------+ | TRUNCATE( t1.c0 , 1 ) | +-----------------------+ | 1 | +-----------------------+ 1 row in set (0.00 sec)