# Strange behavior with ILIKE operator

**URL:** <https://community.cratedb.com/t/strange-behavior-with-ilike-operator/1070>\
**Category:** SQL\
**Created:** [March 30, 2022, 2:49pm UTC](https://community.cratedb.com/t/strange-behavior-with-ilike-operator/1070 "2022-03-30T14:49:06Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Amod](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amod/32/523_2.png) [@Amod](https://community.cratedb.com/u/Amod)\
**Post date:** [March 30, 2022, 2:49pm UTC](https://community.cratedb.com/t/strange-behavior-with-ilike-operator/1070/1 "2022-03-30T14:49:06Z")

</div>

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

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

```

But if use ILIKE operator for the same where condition:

```sql
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.

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [March 30, 2022, 3:43pm UTC](https://community.cratedb.com/t/strange-behavior-with-ilike-operator/1070/2 "2022-03-30T15:43:40Z")

</div>

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

```sql
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

---

<div class="post-metadata">

**Author:** ![Amod](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amod/32/523_2.png) [@Amod](https://community.cratedb.com/u/Amod)\
**Post date:** [March 31, 2022, 6:17am UTC](https://community.cratedb.com/t/strange-behavior-with-ilike-operator/1070/4 "2022-03-31T06:17:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [March 31, 2022, 6:30am UTC](https://community.cratedb.com/t/strange-behavior-with-ilike-operator/1070/5 "2022-03-31T06:30:12Z")

</div>

I was about to file a bug issue

> <https://github.com/crate/crate/issues/12284>
>
> \### CrateDB version
> 
> 4.7.1
> 
> \### CrateDB setup information
> 
> on-prem / cloud…
> 
> \### Steps to Reproduce
> 
> \`\`\`sql
> CREATE TABLE table1 (
> event TEXT
> );
> 
> INSERT INTO TABLE1 (event) VALUES ('Hello');
> 
> select \* from table1 where event ilike 'abc{';
> \`\`\`
> 
> \### Expected Result
> 
> rows are returned
> 
> \### Actual Result
> 
> \`\`\`
> io.crate.exceptions.SQLParseException: Illegal repetition near index 5
> ^abc{.\*$
> ^
> at io.crate.exceptions.SQLExceptions.esToCrateException(SQLExceptions.java:137)
> at io.crate.exceptions.SQLExceptions.prepareForClientTransmission(SQLExceptions.java:126)
> at io.crate.rest.action.SqlHttpHandler.sendResponse(SqlHttpHandler.java:162)
> at io.crate.rest.action.SqlHttpHandler.lambda$channelRead0$0(SqlHttpHandler.java:118)
> at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:859)
> at java.base/java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:837)
> at java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:506)
> at java.base/java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:2158)
> at io.crate.rest.action.RestResultSetReceiver.fail(RestResultSetReceiver.java:89)
> at io.crate.protocols.postgres.RetryOnFailureResultReceiver.fail(RetryOnFailureResultReceiver.java:115)
> at io.crate.action.sql.RowConsumerToResultReceiver.consumeIt(RowConsumerToResultReceiver.java:99)
> at io.crate.action.sql.RowConsumerToResultReceiver.accept(RowConsumerToResultReceiver.java:53)
> at io.crate.execution.engine.InterceptingRowConsumer.lambda$tryForwardResult$1(InterceptingRowConsumer.java:93)
> at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
> at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
> at java.base/java.lang.Thread.run(Thread.java:831)
> Caused by: java.util.regex.PatternSyntaxException: Illegal repetition near index 5
> ^abc{.\*$
> ^
> at java.base/java.util.regex.Pattern.error(Pattern.java:2028)
> at java.base/java.util.regex.Pattern.closure(Pattern.java:3321)
> at java.base/java.util.regex.Pattern.sequence(Pattern.java:2214)
> at java.base/java.util.regex.Pattern.expr(Pattern.java:2069)
> at java.base/java.util.regex.Pattern.compile(Pattern.java:1783)
> at java.base/java.util.regex.Pattern.\<init\>(Pattern.java:1430)
> at java.base/java.util.regex.Pattern.compile(Pattern.java:1095)
> at io.crate.lucene.match.CrateRegexCapabilities$JavaUtilRegexMatcher.\<init\>(CrateRegexCapabilities.java:49)
> at io.crate.lucene.match.CrateRegexCapabilities.compile(CrateRegexCapabilities.java:40)
> at io.crate.lucene.match.CrateRegexTermsEnum.\<init\>(CrateRegexTermsEnum.java:45)
> at io.crate.lucene.match.CrateRegexQuery.getTermsEnum(CrateRegexQuery.java:62)
> at io.crate.lucene.match.CrateRegexQuery.getTermsEnum(CrateRegexQuery.java:41)
> at org.apache.lucene.search.MultiTermQuery.getTermsEnum(MultiTermQuery.java:303)
> at org.apache.lucene.search.MultiTermQueryConstantScoreWrapper$1.rewrite(MultiTermQueryConstantScoreWrapper.java:141)
> at org.apache.lucene.search.MultiTermQueryConstantScoreWrapper$1.scorer(MultiTermQueryConstantScoreWrapper.java:219)
> at org.apache.lucene.search.Weight.scorerSupplier(Weight.java:148)
> at org.apache.lucene.search.LRUQueryCache$CachingWrapperWeight.scorerSupplier(LRUQueryCache.java:727)
> at org.apache.lucene.search.LRUQueryCache$CachingWrapperWeight.scorer(LRUQueryCache.java:812)
> at org.elasticsearch.indices.IndicesQueryCache$CachingWeightWrapper.scorer(IndicesQueryCache.java:150)
> at io.crate.execution.engine.collect.collectors.LuceneBatchIterator.tryAdvanceDocIdSetIterator(LuceneBatchIterator.java:150)
> at io.crate.execution.engine.collect.collectors.LuceneBatchIterator.innerMoveNext(LuceneBatchIterator.java:123)
> at io.crate.execution.engine.collect.collectors.LuceneBatchIterator.moveNext(LuceneBatchIterator.java:116)
> at io.crate.data.CompositeBatchIterator$SeqCompositeBI.moveNext(CompositeBatchIterator.java:145)
> at io.crate.data.MappedForwardingBatchIterator.moveNext(MappedForwardingBatchIterator.java:42)
> at io.crate.data.LimitingBatchIterator.moveNext(LimitingBatchIterator.java:58)
> at io.crate.data.BatchIterators$1.moveNext(BatchIterators.java:120)
> at io.crate.data.AsyncFlatMapBatchIterator.moveNext(AsyncFlatMapBatchIterator.java:68)
> at io.crate.action.sql.RowConsumerToResultReceiver.consumeIt(RowConsumerToResultReceiver.java:70)
> ... 5 more
> \`\`\`

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

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [April 5, 2022, 6:21pm UTC](https://community.cratedb.com/t/strange-behavior-with-ilike-operator/1070/6 "2022-04-05T18:21:38Z")

</div>

@Amod

This will be fixed with the next version 🙂  
PR is merged [fix curly braces within `ILIKE` operands to be treated as a normal character by jeeminso · Pull Request #12303 · crate/crate · GitHub](https://github.com/crate/crate/pull/12303)

Thanks for reporting this.