Parser fails if the first group by is a case statement

[salonijuneja]

Hi, I am using parser to find underlying tables in a query.

Parser failing on query:

SELECT (CASE WHEN 'USD' = 'USD' THEN "column1"
ELSE "column2"
END) AS "testColumn1", "testColumn2", "testColumn3", count(*)
FROM test_schema.table_name
group by (CASE WHEN 'USD' = 'USD' THEN "column1" ELSE "column2" END), "testColumn2", "testColumn3"

It works on query, though not entirely correct (able to format sql but removing commas in group by columns)

SELECT (CASE WHEN 'USD' = 'USD' THEN "column1"
ELSE "column2"
END) AS "testColumn1", "testColumn2", "testColumn3", count(*)
FROM test_schema.table_name
group by "testColumn2", (CASE WHEN 'USD' = 'USD' THEN "column1" ELSE "column2" END), "testColumn3"

Both the SQLs are valid.
Tried out here

[wumpz]

So what is your code to find the tables? What is your output and what do you expect?

[manticore-projects]

@salonijuneja: please clarify, are you referring to a) Parsing the SQL via JSQLParser or b) formatting the SQL via JSQLFormatter?

It looks to me like b) and indeed there was a bug in JSQLFormatter regarding missing commas in GROUP expressions (fixed but not published yet).

If I am correct then please close this ticket and lets discuss on the JSQLFormatter page. Otherwise please elaborate.

@wumpz: apologies for dragging JSQLParser into that.

[manticore-projects]

This fails to parse

select * 
from dual 
group by (case when 1=1 then 'X' else 'Y' end), column1

This succeeds:

select * 
from dual 
group by (case when 1=1 then 'X' else 'Y' end)

I will look into it.

[manticore-projects]

We are facing two issues here:
a) GROUP BY shall use a ComplexExpressionList instead of a SimpleExpressionList. This will solve:

        // without extra brackets
        assertSqlCanBeParsedAndDeparsed(
                "select * \n" +
                        "from dual \n" +
                        "group by case when 1=1 then 'X' else 'Y' end, column1", true);

        // with extra  brackets for List
        assertSqlCanBeParsedAndDeparsed(
                "select * \n" +
                        "from dual \n" +
                        "group by (case when 1=1 then 'X' else 'Y' end, column1)", true);

b) Complex Expressions with Extra Brackets within a List, e.g.

        // with extra brackets for Expression
        assertSqlCanBeParsedAndDeparsed(
                "select * \n" +
                        "from dual \n" +
                        "group by (case when 1=1 then 'X' else 'Y' end), column1", true);

This is a tough one, because we need to tell the parser that the brackets belong to the Expression only, but not to the Expression List of the GROUP BY.

Right now, the Parser reads <K_GROUP> <BY> "(" ComplexExpressionList() ")" "," Expression() and fails.
Instead, it should read <K_GROUP> <BY> "(" ComplexExpressionList() ")"

[salonijuneja]

Hi @wumpz @manticore-projects ,

I am using JSQLParser for finding underlying tables in a query.

Input is similar to query in the issue description
Expected Output should be tables list, in this case: [test_schema.table_name]
Currently it fails with error: Encountered unexpected token: ","","

Code:
CCJSqlParserManager parserManager = new CCJSqlParserManager();
Reader reader = new StringReader(query);
Statement statement = parserManager.parse(reader);
TableNamesFinder tableNamesFinder = new TableNamesFinder();
List tables = tableNamesFinder.getTableList(statement);

Thanks for looking into this.

[salonijuneja]

Hi @manticore-projects @wumpz I see that a ticket has been checked in and issue closed. But I wanted to confirm because this query is still failing in parser:

select *
from dual
group by (case when 1=1 then 'X' else 'Y' end), column1

Please let me know the resolution.
Tried here

Thanks,
Saloni

[manticore-projects]

Greetings,

what exactly have you tested please?
The fix as been committed and accepted into GIT Master Branch, but not released yet and (if I understand it correctly), also no Snapshot has been released yet.

You would need to pull from GIT and compile it yourself for using it.

The Online JSQLFormatter also not up-to-date but stuck with JSQLParser-4.1 although I plan to update it next week.

Best regards
Andreas

[wumpz]

Could you confirm, that the snapshot needs to be rebuild again? I thought it was done already.