can't solve sql:count_if(xxx='')

[ctdcj]

sql: select count_if(app_code='xxx') as aa from tablename

note : count_if -> presto grammar

[manticore-projects]

Greetings.

Thank for reporting.

The issue is NOT about COUNT_IF but about Functions having logical Expressions as arguments.
This example works (because there is no function involved):

SELECT a = '3'
FROM dual
;

But this example fails, because there is a Function involved:

SELECT to_char(a = '3')
FROM dual
;

We will need to look into this eventually.

[manticore-projects]

The correction is actually simple: Parse a ComplexExpressionList instead of a SimpleExpressionList:

LOOKAHEAD(3) (expressionList=ComplexExpressionList() [ orderByList = OrderByElements() { retval.setOrderByElements(orderByList); } ])

However, this yields in a massive Performance Degradation of the Nested Brackets Performance Test, I could not even finish these and had to deactivate 4 of them to complete the Test Suite.

@wumpz : Maybe we could parse for ComplexExpressionList() on the highest Level only and then parse SimpleExpressionList() on the nested level? Still not ideal of course, but maybe better than nothing?

Alternatively we could just limit the nesting, e. g. to 5 levels will still seems to work fine.
In my opinion, whoever writes statements with deeper nesting does not deserve to get parsed correctly.

[manticore-projects]

@wumpz : Pardon my naive question (as I am just an accountant): are we not affected here by this particular problem about "nested syntactic lookahead" which have been fixed in JavaCC 21

If so, should we not try to adopt JavaCC 21?

[wumpz]

Since it has a massive performance impact, I splittet the functions productions in one with normal parameters and one with complex expressions allowed. Only a hand full of functions are configured here by name. Therefore to add count_if should not be a problem.

About the nested syntactic lookahead I should take a look again.

[manticore-projects]

Since it has a massive performance impact, I splittet the functions productions in one with normal parameters and one with complex expressions allowed. Only a hand full of functions are configured here by name. Therefore to add count_if should not be a problem.

I feel like this jumps too short, for example:

SELECT to_char(a = '3')
FROM dual
;

is a valid statement which I would even use in order to show "true" or "false" in a report. And exactly this to_char function is prone to recursion. Why would we not consider to allow complex arguments on the first level of occurrence and then switch to simple expression when going deeper?

[wumpz]

This split is some kind of a hack. With this at least the most common used methods like iif wored. Sure it would be better to have a general approach. I tried this already using a production like ComplexExpression (I think that was the name).

Your proposed approach is used in some occurrences and made already a huge performance gain. To be more precise I first accept a SimpleExpression and then look for a more complex one, like a relation or a condition.

[manticore-projects]

On Mon, 2021-05-17 at 01:14 -0700, Tobias wrote: This split is some kind of a hack. With this at least the most common used methods like iif wored. Sure it would be better to have a general approach. I tried this already using a production like ComplexExpression (I think that was the name). Your proposed approach is used in some occurrences and made already a huge performance gain. To be more precise I first accept a SimpleExpression and then look for a more complex one, like a relation or a condition. — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

Btw, I had the pleasure of a discussion with  Jonathan Revusky, who is behind the JavaCC21 project. He pointed out that we should never face any performance issues, even with very deep nesting. While I am not as educated as him, I actually agree with him for a simple practical consideration: The RDBMS also parses the deeply nested statement, often with finer granularity. And I have never seen any RDBMS which gave up because of nested expressions. So I think, we do something not right. So with a lot of help from Jonathan, I have started porting JSQLParser to JavaCC21 and almost got it working! I have already generated a valid Parser file in Java. My hope is, that the better LOOKAHEAD/LOOKBEHIND features will allow us to solve the observed performance problems. I will keep you posted on the progress, also in order to avoid another anwarranted large PR. Cheers Andreas

[manticore-projects]

Can't we close this now?