WITH...AS parse errors

[ibennetch]

With the sql-parser version 5.5.0 that's included with my phpMyAdmin, I'm looking at the example WITH...AS statement:

with foo as ( select * from products where bar in ('foo', 'bar'));

This passes through the linter without errors.

Which when run through the highlighter provides this:

WITH
    xm_gl AS(
    SELECT
        *
    FROM
        products
    WHERE
        pname IN('foo', 'bar')
);

And that now gives an error through the linter:

#1: Unexpected token. (near ")" at position 107)

It seems this is probably incorrect or improperly handled.

[iifawzi]

Hi, I'm not able to reproduce the same results in both 5.5.0 and 6.0.x-dev which's weird! I might be doing something wrong though.

Linting and highlighting using 5.5.0

Linting and highlighting using 6.0.x-dev I've refactored and introduced a lot of fixes at https://github.com/phpmyadmin/sql-parser/pull/363, linting will now output these errors:

which are correct, because the statement is not a valid with clause, a subsequent query that reference the expression defined, is missing, that's why an expression was expected, and unexpected end of with cte are shown.

a valid statement based on the changes introduced and discussed at #363, should be similar to:

with foo as ( select * from products where bar in ('foo', 'bar')) SELECT * from foo;

[ivanavguston-oviva]

Hi folks, are you going to make any progress there?

[iifawzi]

Hi @ivanavguston-oviva, I'd say that it's already fixed by #363 (not released yet), could you test with 6.0.x-dev, with taking care of that the query should be a valid and complete WITH statement (https://github.com/phpmyadmin/sql-parser/pull/334#issuecomment-986011386)

Aside from that the query mentioned here got tested on an old version that didn't include the patch #363, it is also not a valid WITH clause, as described in my previous comment, and https://github.com/phpmyadmin/sql-parser/pull/334#issuecomment-986011386

[AnrDaemon]

While parsing a rather simple query WITH tm (`data`) AS (SELECT 1 FROM DUAL) SELECT * FROM tm; I get 272 errors in the stack. How can I know which of these are real? Setting strict parsing mode just throws entire code away while request is perfectly executed in console.

[williamdes]

What phpMyAdmin version are you using? Can you provide the full query?

[AnrDaemon]

This is THE FULL query. The parser is 5.8 (last release). Remote is MariaDB 10.4.

[AnrDaemon]

Ok, sorry for the noise, I've found an error in the unexpected place not related to your library.

For those interested:

eval set -- $( getopt --shell=sh … "$@" )

was producing an unquoted result, which, for the case of shell wildcard characters, grabbed a lot from the project directory listing. Given less than explanatory exception messages, and the sheer number of them, I was unable to concentrate on the input data passed to the parser. Which, in the hindsight, I should've checked sooner.

The correct solution is to

eval "set -- $( getopt --shell=sh … "$@" )"

(Note the quotes.)