The LIKE/ILIKE keyword in CrateDB does not work when the target string contains newline (\n) or tab (\t) characters. This causes pattern matching to fail for multi-line or formatted text values

Community
1

For example, the query below returns an empty result:
SELECT * FROM table
WHERE message ILIKE ‘%A user account was%’;

But the actual message column value is:

A user account was deleted
accountid    :    amit
1 Like
2

Hello there, what version of CrateDB are you running? I cannot reproduce.

Query ran at 2026-01-07T14:31:00.662Z on CrateDB 6.0.3
SELECT
  *
FROM
  t
WHERE
  a ILIKE '%line%';
1 record(s) returned in 0.0100s, showing 1 row(s).
a
“It’s line one\nIt’s line two”
3

It can be reproduced like this on CrateDB 6.1.1:

CREATE TABLE q1 (a TEXT);

INSERT INTO q1 VALUES (
$$
A user account was deleted
accountid    :    amit
$$
);

SELECT *
FROM q1
WHERE a ILIKE '%A user account was%';
-- returns no result

The same process on PostgreSQL does return a result:

postgres=# CREATE TABLE q1 (a TEXT);
CREATE TABLE
postgres=# INSERT INTO q1 VALUES (
$$
A user account was deleted
accountid    :    amit
$$
);
INSERT 0 1
postgres=# SELECT * FROM q1;
             a              
----------------------------
                           +
 A user account was deleted+
 accountid    :    amit    +
 
(1 row)

postgres=# SELECT *
FROM q1
WHERE a ILIKE '%A user account was%';
             a              
----------------------------
                           +
 A user account was deleted+
 accountid    :    amit    +
 
(1 row)

That makes it look like a bug to me :slight_smile:. @anujjaiswar, since you discovered it, would you like to report it on GitHub so that it can be addressed by the development team?

Thanks!

3 Likes
4

Yes, I have reported this issue, and the reference details are provided below.

2 Likes