problem "WITH" on INSERT

[ivanavguston-oviva]

it legitimate construction but linter does not allow it

 Line 2, col 1 at "WITH": Unexpected keyword.
 Line 2, col 1 at "WITH": Unrecognized statement type.

INSERT ... WITH ... SELECT ... https://dev.mysql.com/doc/refman/8.0/en/with.html

[williamdes]

Thanks for reporting this !

[ivanavguston-oviva]

Hello folks, do you have any update for me?

[williamdes]

Hello folks, do you have any update for me?

Hi !
Actually we recently added the WITH keyword, see #165 and #331

Could you test the dev-master version of the library in case it already improved this ?

Hi @iifawzi
Would you mind having a look if this is now covered ?

[iifawzi]

Hi, I can confirm that it's not covered. Also such form of statements are not covered too.

CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...

Examples:

INSERT INTO table_name WITH cte (col1) AS ( SELECT 1 UNION ALL SELECT 2 ) SELECT col1 FROM cte
INSERT INTO table_name WITH cte (col1) AS ( SELECT 1 ) SELECT col1 FROM cte ON DUPLICATE KEY UPDATE col_name = 3
CREATE table table_name AS WITH cte (col1) AS ( SELECT 1 UNION ALL SELECT 2 ) SELECT col1 FROM cte

I couldn't think of a test case for the REPLACE statement, and for the CREATE VIEW ... WITH ... SELECT ... case, it will not output any errors even if the WITH statement is incorrect. (because view stm is not aware of the WITH parser) that's why I didn't add add a test cases for them. I've added the tests at iifawzi/sql-parser/support-with-statements.

We need to make other statements aware of the keyword. I will find some time to work on it.