Views with CTEs parsed incorrectly

リンクされている test.php スクリプトから始め、phpmyadmin/sql-parser 5.11.1 を使って出力を再現します。CTEs を含む複数の CREATE VIEW 文がどのようにパースされるかを追跡し、次に括弧なしの形式に対するカバレッジを追加して、括弧を必要とせず各ビューが個別かつ正しく維持されることを確認します。

Hi, parser has been working great for the most part but I ran into an issue today where I have multiple views with CTEs that get mixed together by the parser. I found that if I put parentheses around each view body definition the issue goes away, but MySQL does not require this.

Here is a minimal test script that demonstrates the issue:
test.php

Output from the test script on my machine follows. I am using phpmyadmin/sql-parser version 5.11.1 installed via composer.

-- SQL A:
CREATE VIEW view1 AS
WITH foo AS (SELECT abc FROM foo_table),
bar AS (SELECT id, xyz FROM bar_table)
SELECT foo.id AS id, foo.abc AS abc, bar.xyz AS xyz
FROM foo LEFT JOIN bar USING (id)
ORDER BY foo.abc, bar.xyz;

CREATE VIEW view2 AS
WITH foo AS (SELECT id, abc FROM foo_table),
bar AS (SELECT id, xyz FROM bar_table)
SELECT foo.id AS id, foo.abc AS abc, bar.xyz AS xyz
FROM foo LEFT JOIN bar USING (id)
ORDER BY foo.abc, bar.xyz;

-- Result A:
-- create VIEW view1
CREATE VIEW view1  AS WITH foo AS (SELECT abc FROM foo_table), bar AS (SELECT id, xyz FROM bar_table) SELECT foo.id AS `id`, foo.abc AS `abc`, bar.xyz AS `xyz` FROM foo LEFT JOIN bar USING (id) ORDER BY foo.abc ASC, bar.xyz ASCCREATE VIEW view2  AS WITH foo AS (SELECT id, abc FROM foo_table), bar AS (SELECT id, xyz FROM bar_table) SELECT foo.id AS `id`, foo.abc AS `abc`, bar.xyz AS `xyz` FROM foo LEFT JOIN bar USING (id) ORDER BY foo.abc ASC, bar.xyz ASC


-- SQL B:
CREATE VIEW view1 AS (
WITH foo AS (SELECT abc FROM foo_table),
bar AS (SELECT id, xyz FROM bar_table)
SELECT foo.id AS id, foo.abc AS abc, bar.xyz AS xyz
FROM foo LEFT JOIN bar USING (id)
ORDER BY foo.abc, bar.xyz);

CREATE VIEW view2 AS (
WITH foo AS (SELECT id, abc FROM foo_table),
bar AS (SELECT id, xyz FROM bar_table)
SELECT foo.id AS id, foo.abc AS abc, bar.xyz AS xyz
FROM foo LEFT JOIN bar USING (id)
ORDER BY foo.abc, bar.xyz);

-- Result B:
-- create VIEW view1
CREATE VIEW view1  AS  (
WITH foo AS (SELECT abc FROM foo_table),
bar AS (SELECT id, xyz FROM bar_table)
SELECT foo.id AS id, foo.abc AS abc, bar.xyz AS xyz
FROM foo LEFT JOIN bar USING (id)
ORDER BY foo.abc, bar.xyz)
-- create VIEW view2
CREATE VIEW view2  AS  (
WITH foo AS (SELECT id, abc FROM foo_table),
bar AS (SELECT id, xyz FROM bar_table)
SELECT foo.id AS id, foo.abc AS abc, bar.xyz AS xyz
FROM foo LEFT JOIN bar USING (id)
ORDER BY foo.abc, bar.xyz)