Unexpected result when comparing `CHAR` values with newline

[suyZhong]

CrateDB version

CrateDB 6.0.0-SNAPSHOT built f94b10e

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 no row because the result of the second query is evaluated as TRUE. PostgreSQL returns one row for the third query. Note that the data type should be CHAR, and the comparing string contains a newline.

Steps to Reproduce

DROP TABLE IF EXISTS t0;

CREATE  TABLE  t0(c0 CHAR(10));
INSERT INTO t0(c0) VALUES  ('');
REFRESH TABLE t0;

SELECT * FROM t0; -- ''

SELECT NOT (t0.c0 >= '
sN') FROM t0; -- TRUE

SELECT * FROM t0 WHERE NOT (t0.c0 >= '
sN');
-- Expected: ''
-- Actual: 0 row

Actual Result

empty table

Expected Result

a row with empty string

[suyZhong]

Perhaps a simpler reproduction:

DROP TABLE IF EXISTS t0;
CREATE  TABLE  t0(c0 CHAR(10), PRIMARY KEY(c0));
INSERT INTO t0(c0) VALUES ('');
REFRESH TABLE t0;

SELECT * FROM t0; -- ''
SELECT ((CAST('' AS CHAR(10)))=(t0.c0)) FROM t0; -- TRUE
SELECT * FROM t0 WHERE ((CAST('' AS CHAR(10)))=(t0.c0));
-- Expected: ''
-- Actual: empty table

[jeeminso]

Thank you for reporting @suyZhong , the fix will be available with the next hotfix release.