With with values list doesn't parse.

[Intektor]

Describe the bug
Using as (values ...) in a with doesn't parse.

To Reproduce

           with sample_data(day, value) as (values (0, 13), (1, 12), (2, 15), (3, 4), (4, 8), (5, 16))
            
           select day, value from sample_data

Exception in thread "main" net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "," ","
    at line 1, column 49.

Was expecting one of:

    ")"
    "EXCEPT"
    "INTERSECT"
    "MINUS"
    "ORDER"
    "UNION"

	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:165)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:59)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:37)`

I tested the code on hyperdb, postgresql and sqlite and it works there.

System

• java: 8
• version: 4.0

[wumpz]

Thats right. This feature is not supported. PRs are welcome.

At the moment a values construct is only allowed as a from part like:

SELECT * FROM (VALUES (1, 2), (3, 4)) AS test

Within the grammar file at SingleStatement() there a new statement type for values has to be integrated.

[milnev]

WITH supports only SELECT now, whats about INSERT/UPDATE/DELETE?
https://www.postgresql.org/docs/9.1/queries-with.html
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "UPDATE" "UPDATE"

[manticore-projects]

No promise, but I will try to look into that.

[manticore-projects]

WITH supports only SELECT now, whats about INSERT/UPDATE/DELETE?
https://www.postgresql.org/docs/9.1/queries-with.html
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "UPDATE" "UPDATE"

ONLY this is fixed in #1178.
WITH ... (value_list) is still open.

[manticore-projects]

This is just a not to myself on a H2 supported example. Please more careful about column names next time.

CREATE TABLE sample_data (
    "DAY"        INT
    , "VALUE"    INT
)
;

with sample_data("DAY", "VALUE") as (values ((0, 13), (1, 12), (2, 15), (3, 4), (4, 8), (5, 16)))
select "DAY", "VALUE" from sample_data;

with sample_data("DAY", "VALUE") as (values (0, 13), (1, 12), (2, 15), (3, 4), (4, 8), (5, 16))
select "DAY", "VALUE" from sample_data;

with sample_data("DAY") as (values 0, 1, 2)
           select "DAY" from sample_data;

[manticore-projects]

I got it partially working when VALUES() is followed by brackets:

@Test
  public void testWithWithValueList1135_SimpleExpressions() throws JSQLParserException {
    assertSqlCanBeParsedAndDeparsed(
        "with sample_data(\"DAY\") as (values (0, 1, 2))\n" +
"           select \"DAY\" from sample_data",
        true);
  }

  @Test
  public void testWithWithValueList1135_MultiExpressions() throws JSQLParserException {
    assertSqlCanBeParsedAndDeparsed(
        "with sample_data(\"DAY\", \"VALUE\") as (values ((0, 13), (1, 12), (2, 15), (3, 4), (4, 8), (5, 16)))\n" +
"select \"DAY\", \"VALUE\" from sample_data",
        true);
  }

Unfortunately H2 also accepts the version without the Brackets after VALUES and I am at loss how to make that happen.

[manticore-projects]

There is a also the problem, that ValuesStatement does not recognize, if there were Brackets or not. Both versions deparse into "without brackets".

So I will leave it as a Working PoC for the moment.

Any opinion on the formatting? I feel like it looks just ugly and am not happy with it.

As soon as my other PR is accepted, I can commit these changes and then maybe @wumpz will help me to sort these Brackets.

[manticore-projects]

This issue is much more complex than I thought because the following is a valid statement:

-- testComplexWithQueryIssue561()
WITH split (    word
                , str
                , hascomma ) AS (
        VALUES ( '', 'Auto,A,1234444', 1 )
        UNION ALL
        SELECT  Substr( str, 0, CASE
                        WHEN Instr( str, ',' )
                            THEN Instr( str, ',' )
                        ELSE Length( str ) + 1
                    END )
                , Ltrim( Substr( str, Instr( str, ',' ) ), ',' )
                , Instr( str, ',' )
        FROM split
        WHERE hascomma )
SELECT Trim( word )
FROM split
WHERE word != ''
;

For this statement, the Parser catches a SubSelect consisting of a ValuesStatement and a Select.
It appears to me that ValuesStatement supports only SimpleExpressionList:

ValuesStatement Values(): {
    List<Expression> expList = new ArrayList<Expression>();
    Expression exp;
} {
    <K_VALUES>
    "("
        exp=PrimaryExpression() { expList.add(exp); }
                        ("," exp=PrimaryExpression()  { expList.add(exp); } )*
    ")"
    {
        return new ValuesStatement(expList);
    }
}

This is a challenge because (without a major rewrite) we need to distinguish now at least 4 use cases:

1. SimpleExpressionList using Brackets (VALUES ('a', 'b', 'c')) parsed as SubQuery already (works)
2. SimpleExpressionList without Brackets (VALUES 'a', 'b', 'c') parsed as MultiExpressionList (works)
3. MultiExpressionList using Brackets (VALUES (('a', 'b', 'c'), ('a', 'b', 'c'))) parsed as MultiExpressionList (works)
4. MultiExpressionList without Brackets (VALUES ('a', 'b', 'c'), ('a', 'b', 'c')) parsed as MultiExpressionList (fails, because it competes with case 1 parsed as SubQuery

Case 4 is never caught because it will trigger Case 1 before (and fail there because ValuesStatement does not support MultiExpressionList). Unfortunately we need to check for Case 1 first or else we break the UNION statement shown above.

In my limited understanding, there are 2 possible solutions:
a) write a Grammar, which triggers case 1 ONLY IF case 4 did not apply. (I tried that but did not succeed to write a valid IF NOT construct in JavaCC)
b) OR refactor ValuesStatement so it supports MultiExpressionList and then refactor also the WithItem so it has an ItemsList (implemented by SubSelect or SimpleExpressionList or MultiExpressionList) instead of a SubSelect

I am scared that b) would break too much stuff and introduces to many unwarranted changes.
But I would need a bit of help to get a) working (with a IF NOT MultiExpressionList without Brackets logic)

[manticore-projects]

Well, after a lot of back and forth, I have solved it.
Formatted SQL Preview is available including patched JSQLParser-4.1 Snapshot Binary.

I have implemented Solution a) checking first, if there is a SimpleExpressionList or MultExpressionList WITHOUT brackets. If so, parse as ExpressionList, else parse as SubQuery.

<K_AS>
    // if the next block looks alike an ExpressionList without Brackets, then parse as List
    ( LOOKAHEAD( "(" <K_VALUES> (SimpleExpressionList() | MultiExpressionList()) ")" ) 
        "(" <K_VALUES>
            
        // decide what kind of List to parse
        ( LOOKAHEAD( MultiExpressionList() ) 
            ( multExpressionList =  MultiExpressionList() { with.withUseBracketsForValues(false).setItemsList(multExpressionList); } )
            | ( simpleExpressionList = SimpleExpressionList() { with.withUseBracketsForValues(false).setItemsList(simpleExpressionList); } ))
            
        ")"

    // Otherwise parse it as a SubSelect
        | "(" select = SubSelect() { with.setSubSelect(select.withUseBrackets(false)); with.setUseValues(false); } ")"
            
    )

[wumpz]

Without any changes, this one parses using the original sql

WITH split (    word
                , str
                , hascomma ) AS (
        VALUES ( '', 'Auto,A,1234444', 1 )
        UNION ALL
        SELECT  Substr( str, 0, CASE
                        WHEN Instr( str, ',' )
                            THEN Instr( str, ',' )
                        ELSE Length( str ) + 1
                    END )
                , Ltrim( Substr( str, Instr( str, ',' ) ), ',' )
                , Instr( str, ',' )
        FROM split
        WHERE hascomma )
SELECT Trim( word )
FROM split
WHERE word != ''

[wumpz]

So the values thing is already a statement. Had totally forgotten this. So this statement works as well:

with sample_data(day, value) as (values ((0, 13), (1, 12), (2, 15), (3, 4), (4, 8), (5, 16))) 
select day, value from sample_data

[wumpz]

I look into it. The modification of ValueStatement should not be such a problem.

[manticore-projects]

Without any changes, this one parses using the original sql

WITH split (    word
                , str
                , hascomma ) AS (
        VALUES ( '', 'Auto,A,1234444', 1 )
        UNION ALL
        SELECT  Substr( str, 0, CASE
                        WHEN Instr( str, ',' )
                            THEN Instr( str, ',' )
                        ELSE Length( str ) + 1
                    END )
                , Ltrim( Substr( str, Instr( str, ',' ) ), ',' )
                , Instr( str, ',' )
        FROM split
        WHERE hascomma )
SELECT Trim( word )
FROM split
WHERE word != ''

Yes. I only mentioned it because I did not know that this was a valid statement and I have had to keep it working together with the changes to Value Lists.

However, it is solved and works fine. All 5 mentioned cases work when you apply the PR, which also has tests for it.