UNION clause throws cryptic error in SQLContext

[csubhodeep]

Polars version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of Polars.

Issue description

The below SQL snippet should behave similar to concat but it throws some weird exception. Please correct me if I am misunderstanding the usage here. Thanks !

Reproducible example

import polars as pl

df_test = pl.DataFrame(
    {
        "group": ['a'],
        "attr": [1]
    }
)

df_test2 = pl.DataFrame(
    {
        "group": ['b'],
        "attr": [2]
    }
)

sql_ctx = pl.SQLContext()
sql_ctx.register("test", df_test.lazy())
sql_ctx.register("test2", df_test2.lazy())
result = sql_ctx.query("""
SELECT * FROM test
UNION
SELECT * FROM test2
""")
print(result)

# Output
---------------------------------------------------------------------------
ComputeError                              Traceback (most recent call last)
Cell In[17], line 20
     18 sql_ctx.register("test", df_test.lazy())
     19 sql_ctx.register("test2", df_test2.lazy())
---> 20 result = sql_ctx.query("""
     21 SELECT * FROM test
     22 UNION
     23 SELECT * FROM test2
     24 """)
     25 # result = pl.concat((df_test, df_test2))
     26 print(result)

File ~/Library/Caches/pypoetry/virtualenvs/cseg-UL-1613v-py3.9/lib/python3.9/site-packages/polars/sql/context.py:56, in SQLContext.query(self, query)
     55 def query(self, query: str) -> DataFrame:
---> 56     return self.execute(query).collect()

File ~/Library/Caches/pypoetry/virtualenvs/cseg-UL-1613v-py3.9/lib/python3.9/site-packages/polars/sql/context.py:53, in SQLContext.execute(self, query)
     43 def execute(self, query: str) -> LazyFrame:
     44     """
     45     Parse the givens SQL query and transform that to a ``LazyFrame``.
     46 
   (...)
     51 
     52     """
---> 53     return wrap_ldf(self._ctxt.execute(query))

ComputeError: INSERT, UPDATE is not supported

Expected behavior

shape: (2, 2)
┌───────┬──────┐
│ group ┆ attr │
│ ---   ┆ ---  │
│ str   ┆ i64  │
╞═══════╪══════╡
│ a     ┆ 1    │
│ b     ┆ 2    │
└───────┴──────┘

Installed versions

--------Version info---------
Polars:      0.17.12
Index type:  UInt32
Platform:    macOS-13.3.1-x86_64-i386-64bit
Python:      3.9.15 (main, Apr 14 2023, 14:20:58) 
[Clang 14.0.3 (clang-1403.0.22.14.1)]

----Optional dependencies----
numpy:       1.24.3
pandas:      2.0.1
pyarrow:     10.0.1
connectorx:  <not installed>
deltalake:   <not installed>
fsspec:      <not installed>
matplotlib:  <not installed>
xlsx2csv:    <not installed>
xlsxwriter:  <not installed>

[alexander-beedie]

Set operations (eg: UNION, EXCEPT, INTERSECT) aren't supported yet; for now I'll fix the error so it's more accurate ;)

[csubhodeep]

I see. Does it then make sense to only support UNION ALL for now because UNION is more of - "only add rows which are new in the latter"?

[alexander-beedie]

UNION and UNION ALL are (more or less) equivalent operations as far as the SQL parsing/translation is concerned; we don't support either yet, though I may take a look at UNION to see how much work it is...🤔