Repository navigation
Views with CTEs parsed incorrectly #639
Description
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)
Activity
williamdes commentedon Feb 6, 2026
Thank you for reporting this !
Would you want to try to fix this and make a pull-request ?
You can add your examples to our test data folder
hiSandog commentedon Jul 11, 2026
The parser needs to terminate a CTE at the owning view's query boundary rather than carrying its
WITHstate into the nextCREATE VIEW. Regression fixtures should include two consecutive unparenthesized views, multiple/nested CTEs,WITH RECURSIVE, optional semicolons, and the parenthesized form to ensure existing behavior remains. Asserting each parsed statement's tokens/tables independently will catch both statement splitting and CTE ownership errors.