Repository navigation
Unexpected results when using OBJECT nulls #16556
Description
CrateDB version
CrateDB 5.9.0-SNAPSHOT built 443096a
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. As the second query returns NULL, the expression of the WHERE filter should be evaluated to NULL, and thus the third query should return an empty table. However, a row is fetched. Either the result of the second query or the third query should be wrong.
Might be related to #16476
Steps to Reproduce
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(c0 OBJECT NULL);
INSERT INTO t1(c0) VALUES (NULL);
REFRESH TABLE t1;
SELECT * FROM t1; -- NULL
SELECT (NOT ((CAST(t1.c0 AS VARCHAR))LIKE(''))) FROM t1; -- NULL
SELECT * FROM t1 WHERE (NOT ((CAST(t1.c0 AS VARCHAR))LIKE('')));
-- Expected: empty set
-- Actual: NULL
-- Another similar test case
SELECT * FROM t1; -- NULL
SELECT (((CAST(t1.c0 AS VARCHAR))<>(''))) FROM t1; -- NULL
SELECT * FROM t1 WHERE (((CAST(t1.c0 AS VARCHAR))<>('')));
-- Expected: empty set
-- Actual: NULL
Actual Result
One row is fetched
Expected Result
empty result set
Activity
Fix nullability issue for CAST on object columns
Fix nullability issue for CAST on object columns
matriv commentedon Sep 3, 2024
thank you for reporting this issue @suyZhong. The fix will be available with the next hotfix release.
Fix nullability issue for CAST on object columns
Fix nullability issue for CAST on object columns