Repository navigation
Unexpected result when querying with boolean comparisons #16227
Description
CrateDB version
CrateDB 5.8.0-SNAPSHOT built da0993c
CrateDB setup information
Manual build following the instructions here: https://github.com/crate/crate/blob/master/devs/docs/basics.rst
Problem description
Consider the following test case. A discrepancy is observed when filtering results using a boolean comparison in a WHERE clause. The boolean condition (((t1.c0 != t1.c0)>(t0.c0 = t0.c0)) = t1.c0) evaluates to TRUE when selected directly, but fails to return any rows when used in the WHERE clause.
Might be related with #16173
Steps to Reproduce
DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t1;
CREATE TABLE IF NOT EXISTS t0 (c0 INTEGER DEFAULT 1);
CREATE TABLE IF NOT EXISTS t1 (c0 INTEGER);
INSERT INTO t0( c0) VALUES (1);
INSERT INTO t1(c0) VALUES (0);
REFRESH TABLE t0;
REFRESH TABLE t1;
SELECT * FROM t0, t1; -- 1 0
SELECT (((t1.c0 != t1.c0)>(t0.c0 = t0.c0)) = t1.c0) FROM t0, t1; -- TRUE
SELECT * FROM t0, t1 WHERE (((t1.c0 != t1.c0)>(t0.c0 = t0.c0)) = t1.c0);
-- Expected: 1 0
-- Actual: empty tableActual Result
empty table
Expected Result
1 0
Activity
BaurzhanSakhariev commentedon Jun 25, 2024
Thanks for reporting @suyZhong!
It's indeed related to #16173 (same root cause).
Workaround: disable hash join by
SET enable_hashjoin = false.https://www.db-fiddle.com/f/iTmq9KnYViJiQdG6yGWEEb/0
PG uses NestedLoopJoin (note casts of int to boolean, CrateDB does that implicitly).
Another one false positive HashJoin detection, #16200 needs a follow up (probably can use the same change entry but rephrase it).