Parser fails if the first group by is a case statement

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

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

@salonijuneja

@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.

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.

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() ")"

@wumpz

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.

GROUP BY with Complex Expressions

Fixes #1306 (#1311)

@manticore-projects

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

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

Fix Gradle PMD and Checkstyle (#1318)

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

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

After accepting PR #1318 all the builds should run normal again and Snapshots should reflect the latest Git.

@manticore-projects

Hi @manticore-projects,

Is the JSQLFormatter also updated?

This query is still failing:

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

Tried out here

Is the JSQLFormatter also updated?

Sorry, not yet. But I promise to pick it up soonest.

Thanks

Sorry for being late: http://jsqlformatter.manticore-projects.com/jsqlformatter/demo.html?args=-c%20MoUQMiDCAqAEsApaQIKgFDy9nuDqAEiAHKwDkAqsACJmwC85Vtmub780RpARAMYB7ADYBXALYA7AIw9WHDuFCx+w8RIBMs+bhLVYASlhplAFwCmAZxORVkmXJwAaU5eu2NWts57mrN0ZIAzJ64zjYiEiZIAFQG6ABiAEoA8gCysL4mAPoWfAAWZmIAhgB0JkUARkJmWRJFYmboAOIpFAAKsABCAJrYSKgY2kOwhCRMNHSMlBMOw9pcYyoB0iFzbIogyoLLmrNrunFD3pn+artHLn7uwegA3EA+

Everything has been updated to JSQLParser 4.3-SNAPSHOT.

Cheers!

@manticore-projects

Thanks for all the efforts @manticore-projects

@wumpz When do we plan to release it? I am asking because we use Maven central only in our firm :(

@wumpz

When do we plan to release it? I am asking because we use Maven central only in our firm :(

Only after the Starbucks Vouchers arrive :-D

More seriously: @wumpz has released 4.3 last week with all the patches included.
You should be able to get it from the Maven Central already.

I checked it: https://mvnrepository.com/artifact/com.github.jsqlparser/jsqlparser/4.2. This was the last version I released last week and it is already published. So can we close this or do you have a different problem?

Out of curiosity, where do you store all your own artifacts in your company? Only local?

@wumpz

@wumpz , Yes I verified, failing queries are now being parsed by v4.2. The issue is already closed. Thanks!

Yes, there's firmwide repo parallel to maven central that contains all.

@salonijuneja

@salonijuneja OK. Strange this restriction to only central. Many projects are published at there own repos. But since it is company restricion, who am I to argue? :)

Update sets (JSQLParser#1317)

Navigation Menu

Repository navigation

Parser fails if the first group by is a case statement #1308

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Parser fails if the first group by is a case statement #1308

Description

Activity

wumpz commented on Aug 25, 2021

manticore-projects commented on Aug 26, 2021

manticore-projects commented on Aug 26, 2021

manticore-projects commented on Aug 26, 2021

salonijuneja commented on Aug 26, 2021

salonijuneja commented on Aug 30, 2021

manticore-projects commented on Aug 30, 2021

wumpz commented on Sep 1, 2021

manticore-projects commented on Sep 2, 2021

salonijuneja commented on Sep 2, 2021

manticore-projects commented on Sep 2, 2021

salonijuneja commented on Sep 3, 2021

manticore-projects commented on Sep 11, 2021

salonijuneja commented on Sep 17, 2021

manticore-projects commented on Sep 17, 2021

wumpz commented on Sep 18, 2021

salonijuneja commented on Sep 18, 2021

wumpz commented on Sep 18, 2021

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions