Blank line after final CTE causes false syntax error

[KvGroove]

Blank line after final CTE causes false syntax error

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

A blank line between the closing parenthesis of the final CTE and the main statement causes postgres-language-server to report a syntax error for otherwise valid PostgreSQL.

This appears related to #372, which was closed by #380. However, the issue still reproduces on postgres-language-server 0.25.7 when the blank line occurs after the final CTE, before the main statement.

For example, this is reported as invalid:

WITH failed_ingestion_run AS (
    INSERT INTO ingestion_runs (
        source_name
    )
    VALUES (
        'test'
    )
    RETURNING id
)

INSERT INTO raw_fred_mortgage_rates (
    ingestion_run_id
)
SELECT id
FROM failed_ingestion_run;

The diagnostic is:

Invalid statement: syntax error at end of input

Removing only the blank line makes the diagnostic disappear:

WITH failed_ingestion_run AS (
    INSERT INTO ingestion_runs (
        source_name
    )
    VALUES (
        'test'
    )
    RETURNING id
)
INSERT INTO raw_fred_mortgage_rates (
    ingestion_run_id
)
SELECT id
FROM failed_ingestion_run;

Both forms are valid PostgreSQL.

To Reproduce

1. Use postgres-language-server 0.25.7.
2. Open a SQL file containing:

WITH foo AS (
    SELECT 1 AS id
)

SELECT id
FROM foo;

3. Observe the syntax diagnostic around the end of the CTE.
4. Delete the blank line so that SELECT immediately follows the closing ).
5. Observe that the diagnostic disappears.

The same behavior occurs when the main statement is an INSERT, for example with a data-modifying CTE using INSERT ... RETURNING.

Expected behavior

Whitespace between the final CTE and its main statement should not change how the statement is parsed.

Both of these should be accepted without diagnostics:

WITH foo AS (
    SELECT 1 AS id
)

SELECT id
FROM foo;

and:

WITH foo AS (
    SELECT 1 AS id
)
SELECT id
FROM foo;

Screenshots

N/A

System information

• OS: Linux
• Editor: Neovim
• postgres-language-server: 0.25.7

Additional context

This is particularly noticeable when using SQLFluff as a formatter. SQLFluff's LT08 layout rule intentionally inserts a blank line after a CTE, which turns otherwise diagnostic-free SQL into SQL that postgres-language-server reports as invalid.

This looks closely related to #372 / #380. The fix in #380 appears to address blank lines following commas between CTEs, while a blank line after the final CTE before the main statement still seems to be interpreted as a statement boundary.

[psteinroe]

Hey, thanks for the issue!

Unfortunately, this is expected behaviour. I added it to the docs to be more visible in #781. You can find more context in #135.