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)
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:
I just double-checked it too, I know what the issue is. I’ll refactor, thanks.