bug(sqlparser): properly handle ambiguity in INSERT INTO table_name (SELECT ...)

[cloudcarver]

Version:

PostgreSQL 13.14.0-RisingWave-2.1.0 (unknown)

Steps to reproduce:

CREATE TABLE src2 (a INT, b INT);
CREATE TABLE dst2 (c INT, d INT);
INSERT INTO dst2 (SELECT * FROM src2);

Postgres supports this syntax. So RisingWave might also need to adapt it.

[xiangjinwu]

Note that RisingWave already support it without parentheses:

INSERT INTO dst2 SELECT * FROM src2;

The problem of parentheses is the ambiguity between the follow two SQLs:

INSERT INTO dst2 (d) SELECT 1;
INSERT INTO dst2 (SELECT * FROM src2);

Right now it seems our sqlparser always tries to parse a column list inside parentheses and would report an error immediately without backtracking and trying the other possibility: parsing a parentheses Query.

Is avoiding the unnecessary parentheses an option in your use case? The sqlparser may (or may not, I have not checked this specific case) need a moderate refactor to support backtracking well.

Of course another workaround also works:

INSERT INTO dst2(c, d) (SELECT * FROM src2);

[cloudcarver]

Is avoiding the unnecessary parentheses an option in your use case?

All good. These workarounds can work. It is just a bit confusing.