Unexpected result when using escape character in `LIKE`

[suyZhong]

CrateDB version

CrateDB 5.9.0-SNAPSHOT built 4d38a5e

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 FALSE, the expression of the WHERE filter should be evaluated to false, 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. Or an error should be raised.

Steps to Reproduce

DROP TABLE IF EXISTS t0;

CREATE TABLE t0 (c2 VARCHAR(500)) ;
INSERT INTO t0(c2) VALUES  ('');
REFRESH TABLE t0;

SELECT * FROM t0; -- ''
SELECT (NOT ((t0.c2 LIKE '\'))) FROM t0; -- FALSE (unexpected)
SELECT * FROM t0 WHERE (NOT ((t0.c2 LIKE '\')));
-- Expected: empty table
-- Actual: ''

Might be the issue: the result of following query is inconsistent with PG:

SELECT '' LIKE '\';
-- TRUE

Actual Result

As mentioned above

Expected Result

As mentioned above. Or an error thrown.

[matriv]

Since PostgreSQL throws an error in all other cases (where the text we're trying to match is not an empty string), we decided to not differentiate in CrateDB for the case where the text to match is ''.

matriv=> SELECT 'abcd' LIKE '\';
ERROR:  LIKE pattern must not end with escape character

matriv=> SELECT x LIKE '\' from unnest(array['', 'foo', 'bar']) as t(x);
ERROR:  22025: LIKE pattern must not end with escape character