Escape mechanism doesn't work in like/ilike functions

[bziobrowski]

Describe the bug

Like and ilike functions don't recognize default escape character '\' nor allow using a different one via ESCAPE clause .

To reproduce

SELECT '\quest' LIKE '\_uest'; 
--true but should be false

SELECT 'quest_' LIKE 'quest\_'
--false but should be true 

select 'quest' like 'quest' escape 'Z'
--found [tok=''Z'', len=3] ',', 'from' or 'over' expected

Expected Behavior

No response

Environment

- **QuestDB version**:6.5.4/master
- **OS**:
- **Browser**:

Additional context

No response

[sharmadhiraj86]

Hi. I would like to contribute to this issue. Can you assign this issue to me? @bziobrowski

[Ln11211]

Yeah @bziobrowski , I can confirm it. I just tested the escape sequences and they don't seem to work, I think the LIKE and ILIKE don't support escape and \ yet.

[onlydevelop]

@sharmadhiraj86 did you make any progress in this?

[kachida]

Hi @bziobrowski , I'm interested to work on the above issue, can you please assign me this task

[bziobrowski]

I've cleared the assignee field because I haven't heard from the person . If there's no linked PR then it usually means that no one is working on the issue .

[onlydevelop]

@bziobrowski Can you give some starting point for this?

[bziobrowski]

LIKE is implemented with io.questdb.griffin.engine.functions.regex.ILikeStrFunctionFactory and io.questdb.griffin.engine.functions.regex.LikeStrFunctionFactory classes . To support ESCAPE clause you'd need to change io.questdb.griffin.ExpressionParser (see how cast or between operators are implemented ) and then pass the optional escape character to functions mentioned above .

[SiyaoIsHiding]

Our team is working on QuestDB in our Testing & Debugging coursework and we wanna fix this issue. May u assign this issue to me?

[bziobrowski]

@SiyaoIsHiding AFAIK no on is working on the issue so feel free to submit PR .

[AdalyatNazirov]

@bziobrowski is this issue still open? I can see the PR have been merged years ago, but issue still open

[SiyaoIsHiding]

@AdalyatNazirov The first two cases work now but the third one does not. The escape keyword is not implemented yet.

SELECT '\quest' LIKE '\_uest'; 
--true but should be false

SELECT 'quest_' LIKE 'quest\_'
--false but should be true 

select 'quest' like 'quest' escape 'Z'
--found [tok=''Z'', len=3] ',', 'from' or 'over' expected

[maneeshchamakura]

Hi @SiyaoIsHiding this looks interesting. If the issue is open, can I work on this? If it is open, could you please assign this to me?

[SiyaoIsHiding]

@maneeshchamakura Yes, sure! I have to admit I didn't work on it for a long time. I can also attach Bolek's response about the implementation of the ESCAPE clause. I hope he does not mind.

As for implementing escape clause of like - a good place to start would be in ExpressionParser. If you take a look at how multi-word clauses are implemented (e.g. CASE or CAST, search for SqlKeywords.isCaseKeyword ),
then you should be able to do something similar for x LIKE y ESCAPE c clause  .
The way to add sql keyword depends on its type :
regular functions, e.g. length(string), require writing factory, function and adding the former to module-info.java and io.questdb.griffin.FunctionFactory .  
unusual functions with complex call signature, e.g. CASE or CAST - these need handling in ExpressionParser
'simple'  sql keywords like SELECT, GROUP BY - need to be implemented in SqlParser
When applicable , it's good to add helper function for each new keyword to - surprise, surprise - SqlKeywords class.
It seems that PostgreSQL accepts arbitrary expression but I reckon that a accepting just a constant character should be good enough for 99.99% of users .
If you then pass the character to Factory via parameters (signature you should look like "like(SSS)"), you can pass it to BindLike/ConstLike... Function and use it in function logic .
If it's a contant supplied via args then you can keep those function classes as UnaryFunctions because the number of args passed to them during evaluation won't change .

Hope it helps!