Unexpected Results when Using AND Operator

[suyZhong]

CrateDB version

5.6.0-2023-11-14-02-39-63a8b59

CrateDB setup information

Manual build following the instructions here: https://github.com/crate/crate/blob/master/devs/docs/basics.rst

Also could reproduce in 5.5.0

Problem description

The third SELECT returns an empty result, which is surprising, given that the result of the second query is TRUE. The value of the WHERE condition is TRUE, and thus the third query should return that row.

P.S. I also found that SELECT -1 AND NULL; would return FALSE, while other DBMS (e.g., MySQL, SQLite) usually CAST non-zero values as TRUE (for PostgreSQL SELECT (-1)::boolean; -- true), and then the result of the query would be NULL.

Steps to Reproduce

CREATE TABLE t0(c0 INT, c1 INT);
INSERT INTO t0(c1) VALUES (-1);

SELECT * FROM t0; -- c0: NULL, c1: -1
SELECT NOT (c1 AND c0 ) FROM t0;  -- TRUE
SELECT * FROM t0 WHERE NOT (c1 AND c0); 

Actual Result

empty table

Expected Result

NULL -1