sqlmesh: CTE tests throw error with recursive tests

sqlmesh --version
0.131.0

When the test of the following model includes cte tests, an error is thrown. The model itself works perfectly well on duckdb, to which the error is related, suggesting that the error is not directly related to duckdb.

Model:

MODEL (
  name sandbox.recursive_cte_bug,
  kind FULL
);

WITH
    RECURSIVE
    chained_contacts AS (
        -- Start with the initial set of contacts and their immediate nodes
        SELECT
            id_contact_a,
            id_contact_b
        FROM
            sandbox.x420_source

        UNION ALL

        -- Recursive step to find further connected nodes
        SELECT
            chained_contacts.id_contact_a,
            unfactorized_duplicates.id_contact_b
        FROM
            chained_contacts
                JOIN sandbox.x420_source AS unfactorized_duplicates
                     ON chained_contacts.id_contact_b = unfactorized_duplicates.id_contact_a
    ),
    id_contact_a_with_aggregated_id_contact_bs AS (
        SELECT
            id_contact_a,
            ARRAY_AGG(DISTINCT id_contact_b ORDER BY id_contact_b) AS aggregated_id_contact_bs
        FROM
            chained_contacts
        GROUP BY
            id_contact_a
    )
SELECT
    ARRAY_CONCAT([id_contact_a], aggregated_id_contact_bs) AS aggregated_duplicates
FROM
    id_contact_a_with_aggregated_id_contact_bs
WHERE
    id_contact_a NOT IN (
        SELECT DISTINCT
            id_contact_b
        FROM
            sandbox.x420_source
    )
ORDER BY
    id_contact_a

When running the following test:

reproduce_bug:
  model: sandbox.recursive_cte_bug
  inputs:
    sandbox.x420_source:
      rows:
        - id_contact_a: "a"
          id_contact_b: "b"
        - id_contact_a: "b"
          id_contact_b: "c"
        - id_contact_a: "c"
          id_contact_b: "d"
        - id_contact_a: "a"
          id_contact_b: "g"
        - id_contact_a: "b"
          id_contact_b: "e"
        - id_contact_a: "c"
          id_contact_b: "f"
        - id_contact_a: "x"
          id_contact_b: "y"
  outputs:
    ctes:
      id_contact_a_with_aggregated_id_contact_bs:
        - id_contact_a: a
          aggregated_id_contact_bs:
            - b
            - c
            - d
            - e
            - f
            - g
        - id_contact_a: x
          aggregated_id_contact_bs:
            - y
    query:
      rows:
        - aggregated_duplicates:
          - a
          - b
          - c
          - d
          - e
          - f
          - g
        - aggregated_duplicates:
          - x
          - y

An error is thrown when running test:

sqlmesh test
.......E..
======================================================================
ERROR: reproduce_bug (/project-dir/tests/test_recursive_cte_bug.yaml) (cte='"id_contact_a_with_aggregated_id_contact_bs"')
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/project-dir/.venv/lib/python3.12/site-packages/sqlmesh/core/test/definition.py", line 544, in test_ctes
    actual = self._execute(cte_query)
             ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/project-dir/.venv/lib/python3.12/site-packages/sqlmesh/core/test/definition.py", line 491, in _execute
    return self.engine_adapter.fetchdf(query)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/project-dir/.venv/lib/python3.12/site-packages/sqlmesh/core/engine_adapter/base.py", line 1932, in fetchdf
    df = self._fetch_native_df(query, quote_identifiers=quote_identifiers)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/project-dir/.venv/lib/python3.12/site-packages/sqlmesh/core/engine_adapter/base.py", line 1925, in _fetch_native_df
    self.execute(query, quote_identifiers=quote_identifiers)
  File "/project-dir/.venv/lib/python3.12/site-packages/sqlmesh/core/engine_adapter/base.py", line 2059, in execute
    self._execute(sql, **kwargs)
  File "/project-dir/.venv/lib/python3.12/site-packages/sqlmesh/core/engine_adapter/base.py", line 2065, in _execute
    self.cursor.execute(sql, **kwargs)
duckdb.duckdb.BinderException: Binder Error: Circular reference to CTE "chained_contacts", There are two possible solutions. 
1. use WITH RECURSIVE to use recursive CTEs. 
2. If you want to use the TABLE name "chained_contacts" the same as the CTE name, please explicitly add "SCHEMA" before table name. You can try "main.chained_contacts" (main is the duckdb default schema)

----------------------------------------------------------------------
Ran 10 tests in 0.168s

FAILED (errors=1)

The error does not occur when removing the CTE test:

reproduce_bug:
  model: sandbox.recursive_cte_bug
  inputs:
    sandbox.x420_source:
      rows:
        - id_contact_a: "a"
          id_contact_b: "b"
        - id_contact_a: "b"
          id_contact_b: "c"
        - id_contact_a: "c"
          id_contact_b: "d"
        - id_contact_a: "a"
          id_contact_b: "g"
        - id_contact_a: "b"
          id_contact_b: "e"
        - id_contact_a: "c"
          id_contact_b: "f"
        - id_contact_a: "x"
          id_contact_b: "y"
  outputs:
    query:
      rows:
        - aggregated_duplicates:
          - a
          - b
          - c
          - d
          - e
          - f
          - g
        - aggregated_duplicates:
          - x
          - y

test run output:

sqlmesh test
..........
----------------------------------------------------------------------
Ran 10 tests in 0.168s

OK

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Comments: 17 (17 by maintainers)

Most upvoted comments

It’s the same bug

Actually I take this back, they’re not quite the same. Will include a fix for that as well, should be easy.

It’s the same bug, I have a fix coming for that as well.

Yes indeed, my test was wrong… And since it was not working at all, I did not see it was wrong (which is what tests are for 😄 ). it should be:

reproduce_bug:
  model: sandbox.recursive_cte_bug
  inputs:
    sandbox.x420_source:
      rows:
        - id_contact_a: "a"
          id_contact_b: "b"
        - id_contact_a: "b"
          id_contact_b: "c"
        - id_contact_a: "c"
          id_contact_b: "d"
        - id_contact_a: "a"
          id_contact_b: "g"
        - id_contact_a: "b"
          id_contact_b: "e"
        - id_contact_a: "c"
          id_contact_b: "f"
        - id_contact_a: "x"
          id_contact_b: "y"
  outputs:
    ctes:
      id_contact_a_with_aggregated_id_contact_bs:
        - id_contact_a: a
          aggregated_id_contact_bs:
            - b
            - c
            - d
            - e
            - f
            - g
        - id_contact_a: b
          aggregated_id_contact_bs:
            - c
            - d
            - e
            - f
        - id_contact_a: c
          aggregated_id_contact_bs:
            - d
            - f
        - id_contact_a: x
          aggregated_id_contact_bs:
            - y
    query:
      rows:
        - aggregated_duplicates:
          - a
          - b
          - c
          - d
          - e
          - f
          - g
        - aggregated_duplicates:
          - x
          - y

I just double-checked it too, I know what the issue is. I’ll refactor, thanks.