Repository navigation
Unexpected result when using ARRAY(TEXT) in IN #16652
Description
CrateDB version
CrateDB 5.9.0-SNAPSHOT built 23be7d1
CrateDB setup information
Manual build following the instructions here: https://github.com/crate/crate/blob/master/devs/docs/basics.rst
Problem description
Consider the test case below. It is unexpected that the last query returns an empty result since the second query shows that the expression should be evaluated as TRUE, and thus the third query should return the row in the table.
Steps to Reproduce
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c1 ARRAY(TEXT));
INSERT INTO t0(c1) VALUES (ARRAY['hello','world']);
REFRESH TABLE t0;
SELECT t0.c1 FROM t0; -- ["hello", "world"]
SELECT (t0.c1 IN (ARRAY['hello','world'])) FROM t0; -- TRUE
SELECT t0.c1 FROM t0 WHERE (t0.c1 IN (ARRAY['hello','world']));
-- Expected: ["hello", "world"]
-- Actual: empty result setActual Result
empty table
Expected Result
["hello", "world"]
Activity
BaurzhanSakhariev commentedon Sep 23, 2024
Checked whether it's a recent regression but it's not: doesn't work in 5.6.5 and 5.7.5 as well
Test: Add test_in_operator_with_arrays_on_both_lhs_and_rhs
Test: Add test_in_operator_with_arrays_on_both_lhs_and_rhs
Test: Add test_in_operator_with_arrays_on_both_lhs_and_rhs
Test: Add test_in_operator_with_arrays_on_both_lhs_and_rhs
Test: Add test_in_operator_with_arrays_on_both_lhs_and_rhs
jeeminso commentedon Sep 24, 2024
Thank you for reporting @suyZhong . The fix will be available with the next hotfix relase.
Test: Add test_in_operator_with_arrays_on_both_lhs_and_rhs
Test: Add test_in_operator_with_arrays_on_both_lhs_and_rhs
Test: Add test_in_operator_with_arrays_on_both_lhs_and_rhs
Test: Add test_in_operator_with_arrays_on_both_lhs_and_rhs