Column names in CTE names are not preserved during model parse

[Bilbottom]

Summary

In many SQL dialects, column names can be specified as part of the table alias.

SQLMesh does not preserve the column aliases when specified in CTE names, so model parsing fails when the column names are defined here.

Note that SQLMesh does preserve column aliases in subquery and table aliases.

MWE (DuckDB)

The examples below are run with the following environment:

• OS: Windows 10
• Python: 3.11.5
• SQLMesh: 0.141.1
• DuckDB: 1.1.3

A failing example

Here's an example where the column names are lost during the parse, so model evaluation fails:

model (name test.model);
with

l(id, val_l) as (values (1, 'a')),
r(id, val_r) as (values (1, 'X'))

from l inner join r using (id)

The CTE names specify the column names, and DuckDB would return the following table for this query:

id	val_l	val_r
1	a	X

However, running sqlmesh evaluate test.model throw the following error:

...

duckdb.duckdb.BinderException: Binder Error: Values list "l" does not have a column named "id"
LINE 1: ...OM "l" AS "l" INNER JOIN "r" AS "r" ON "l"."id" = "r"."id" LIMIT 1000

This is because the rendered version of the model does not have the column names in the CTE names; the output of sqlmesh render test.model is:

WITH "l" AS (
  VALUES
    (1, 'a')
), "r" AS (
  VALUES
    (1, 'X')
)
SELECT
  *
FROM "l" AS "l"
INNER JOIN "r" AS "r"
  ON "l"."id" = "r"."id"

Two passing examples

As mentioned above, SQLMesh does preserve column names when specified as a subquery/table alias. Here are two examples that work as expected (included just for comparison/completeness):

from (values (1, 'a')) as l(id, val_l)
    inner join (values (1, 'X')) as r(id, val_r)
        using (id)

with

l as (values (1, 'a')),
r as (values (1, 'X'))

from l as l(id, val_l)
    inner join r as r(id, val_r)
        using (id)

[treysp]

Hello - thanks for reporting this, looks like we need to update this behavior in sqlglot. cc @georgesittas