Strange behavior with ILIKE operator

CrateDB SQL
1

Hi,

I am trying to use ILIKE operator for case insensitive search. Here I am facing issue with specific character ‘{’ only when I use ILIKE operator. Same where condition works with LIKE operator.

e.g. Following query is executed successfully

select *  from table1 where event like 'abc{%'

But if use ILIKE operator for the same where condition:

select *  from table1 where event ilike 'abc{%'

I get following error
SQLParseException[Illegal repetition near index 6 ^aabc{.*$ ^]

Not sure why same where condition works for LIKE operator , but doesn’t work with ILIKE operator.

Could you please provide any update on this?
I am testing this on 4.5.1 version.

Thanks for any help/update.

2 Likes
2

Are you sure that this is not just a copy/paste issue?

CREATE TABLE table1 (
	event TEXT
);
select * from table1 where event like 'abc{%';
select * from table1 where event ilike 'abc{%';

Works fine with 4.7.1 and 4.5.1. Can you maybe share more information about the schema and sample data?

Edit: Just tested again with some data, this indeed seems to be a bug also in 4.7.1

1 Like
4

Thanks for the info. Will this get logged as bug? I mean, any idea about whether this will get fixed in future version?

5

I was about to file a bug issue

I would expect it to get fixed with one of the next releases.

1 Like
6

@Amod

This will be fixed with the next version :slight_smile:
PR is merged fix curly braces within `ILIKE` operands to be treated as a normal character by jeeminso · Pull Request #12303 · crate/crate · GitHub

Thanks for reporting this.

1 Like