error when running CTEs

[edublancas]

since we automatically resolve dependencies, a CTE will break if one of them contains a name that's been used already by a given snippet:

In [1]: %load_ext sql

In [2]: %sql duckdb:// --alias myconnection

In [3]: %sql --save penguins select * from penguins.csv
Running query in 'myconnection'
Out[3]:
+---------+-----------+----------------+---------------+-------------------+-------------+--------+
| species |   island  | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g |  sex   |
+---------+-----------+----------------+---------------+-------------------+-------------+--------+
|  Adelie | Torgersen |      39.1      |      18.7     |        181        |     3750    |  MALE  |
|  Adelie | Torgersen |      39.5      |      17.4     |        186        |     3800    | FEMALE |
|  Adelie | Torgersen |      40.3      |      18.0     |        195        |     3250    | FEMALE |
|  Adelie | Torgersen |      None      |      None     |        None       |     None    |  None  |
|  Adelie | Torgersen |      36.7      |      19.3     |        193        |     3450    | FEMALE |
|  Adelie | Torgersen |      39.3      |      20.6     |        190        |     3650    |  MALE  |
|  Adelie | Torgersen |      38.9      |      17.8     |        181        |     3625    | FEMALE |
|  Adelie | Torgersen |      39.2      |      19.6     |        195        |     4675    |  MALE  |
|  Adelie | Torgersen |      34.1      |      18.1     |        193        |     3475    |  None  |
|  Adelie | Torgersen |      42.0      |      20.2     |        190        |     4250    |  None  |
+---------+-----------+----------------+---------------+-------------------+-------------+--------+

In [4]: %sql select * from penguins
Generating CTE with stored snippets: 'penguins'
Running query in 'myconnection'
Out[4]:
+---------+-----------+----------------+---------------+-------------------+-------------+--------+
| species |   island  | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g |  sex   |
+---------+-----------+----------------+---------------+-------------------+-------------+--------+
|  Adelie | Torgersen |      39.1      |      18.7     |        181        |     3750    |  MALE  |
|  Adelie | Torgersen |      39.5      |      17.4     |        186        |     3800    | FEMALE |
|  Adelie | Torgersen |      40.3      |      18.0     |        195        |     3250    | FEMALE |
|  Adelie | Torgersen |      None      |      None     |        None       |     None    |  None  |
|  Adelie | Torgersen |      36.7      |      19.3     |        193        |     3450    | FEMALE |
|  Adelie | Torgersen |      39.3      |      20.6     |        190        |     3650    |  MALE  |
|  Adelie | Torgersen |      38.9      |      17.8     |        181        |     3625    | FEMALE |
|  Adelie | Torgersen |      39.2      |      19.6     |        195        |     4675    |  MALE  |
|  Adelie | Torgersen |      34.1      |      18.1     |        193        |     3475    |  None  |
|  Adelie | Torgersen |      42.0      |      20.2     |        190        |     4250    |  None  |
+---------+-----------+----------------+---------------+-------------------+-------------+--------+

In [5]: %%sql
   ...: with something as(
   ...: select * from penguins
   ...: )
   ...: select * from something
   ...:
   ...:
Generating CTE with stored snippets: 'penguins'
Running query in 'myconnection'
RuntimeError: If using snippets, you may pass the --with argument explicitly.
For more details please refer : https://jupysql.ploomber.io/en/latest/compose.html#with-argument


Original error message from DB driver:
(duckdb.ParserException) Parser Error: syntax error at or near "with"
LINE 2: with something as(
        ^
[SQL: WITH penguins AS (select * from penguins.csv)
with something as(
select * from penguins
)
select * from something]
(Background on this error at: https://sqlalche.me/e/14/f405)

If you need help solving this issue, send us a message: https://ploomber.io/community