Unexpected result when using LTRIM with NULL as second argument

[suyZhong]

CrateDB version

CrateDB 5.7.0-SNAPSHOT 4d4d160

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 second query returns TRUE, because there's one NULL value in the function argument. Still, if it is expected, the third query should return NULL, as the result of the WHERE predicate is TRUE.

RTRIM also shares the same issue. Have tested #15498 on this version and it worked as expected.

Steps to Reproduce

DROP TABLE IF EXISTS t1;

CREATE TABLE t1(c1 INT);
INSERT INTO t1 (c1) VALUES (null);
REFRESH TABLE t1;


SELECT t1.c1 FROM t1; -- NULL
SELECT LTRIM(11, t1.c1)!=1 FROM t1; -- TRUE (unexpected?)
SELECT t1.c1 FROM t1 WHERE ((LTRIM(11, t1.c1))!=1);
-- Expected (if second query is expected): NULL
-- Actual: empty result

Actual Result

As mentioned above

Expected Result

The second query should return NULL, otherwise the thrid query should return NULL

[jeeminso]

Thanks for reporting @suyZhong. As you mentioned, I think the bug here may be ltrim:

cr> select ltrim('abc', null);                                                                                                   
+-------+
| 'abc' |
+-------+
| abc   | -- Postgres would return `null`
+-------+
SELECT 1 row in set (0.033 sec)

since we try to achieve Postgres compatibility if possible.

[matriv]

Thx for reporting @suyZhong ! The issue has been fixed and will be available with the next hotfix release.