[jdbc-v2] Antlr4 parser fails to parse mixed CTEs

[Gerrit-K]

Description

The parser fails to parse queries with CTEs that use both forms, the WITH <expression> AS <identifier> form and the WITH <identifier> AS <subquery expression> form (see syntax). See this example:

SqlParser parser = new SqlParser();
ParsedStatement stmt = parser.parsedStatement("with 1 as a, b as (select 1) select a from b");
assertFalse(stmt.isHasErrors());

The following queries can be parsed without issues:

with 1 as a, 2 as b select a, b; -- two CTEs of the first form
with a as (select 1), b as (select 2) select * from a, b; -- two CTEs of the second form

Steps to reproduce

1. Run the java code above in a test
2. Observe a test failure

Error Log or Exception StackTrace

When debugging this in the test, you can see that inside parsedStatement() the parseTree.children.get(0).exception is org.antlr.v4.runtime.NoViableAltException.

Expected Behaviour

Both forms of CTEs should be parseable if used in a single query.

[Gerrit-K]

Here's another case related to CTEs:

(with a as (select 1) select * from a); -- fails to parse with "<missing SELECT>"
with a as (select 1) select * from a; -- (without parentheses) works
(with 1 as a select a); -- (with parentheses, but other form of CTE) works

I keep stumbling over issues with the parser, and I'll hold off with creating dedicated issues for each one for now. Let me know if you want me to provide all the cases in one umbrella ticket.

[chernser]

Thank you for reporting @Gerrit-K !

[chernser]

@Gerrit-K yes, please list all issue you have. We are ok if one issue describes multiple problems in same area

[Gerrit-K]

@chernser Alright, will do. Thanks a lot for turning to these issues so quickly! And sorry for flooding the inbox 😅 I'll test some more and will add more usecases here if I find any 👍