Datafusion 42 does not raise plan error when UNION branches have different number of columns

[emanueledomingo]

Describe the bug

Since datafusion 41, i got Datafusion Error when some SQL queries contain errors.

SELECT "Product" FROM "my_table" UNION ALL SELECT "Product", "Id" FROM "my_table"

raises

---------------------------------------------------------------------------
Exception                                 Traceback (most recent call last)
Cell In[4], line 4
      1 query = """
      2 SELECT "Product" FROM "my_table" UNION ALL SELECT "Product", "Id" FROM "my_table"
      3 """
----> 4 df = ctx.sql(query)

File ~/.../site-packages/datafusion/context.py:514, in SessionContext.sql(self, query, options)
    499 """Create a :py:class:`~datafusion.DataFrame` from SQL query text.
    500 
    501 Note: This API implements DDL statements such as ``CREATE TABLE`` and
   (...)
    511     DataFrame representation of the SQL query.
    512 """
    513 if options is None:
--> 514     return DataFrame(self.ctx.sql(query))
    515 return DataFrame(self.ctx.sql_with_options(query, options.options_internal))

Exception: DataFusion error: Plan("Union queries must have the same number of columns, (left is 1, right is 2)")

With new datafusion version, this doesn't happens anymore (it crashes later, when i run .collect() after the .sql() result)

To Reproduce

import pyarrow as pa
import datafusion as df
import pyarrow.dataset as pda

t = pa.Table.from_pydict(
    {
        "Id": [0, 0, 0, 0, 0, 1, 1, 1, 1],
        "Product": ["A", "A", "A", "B", "C", "B", "C", "B", "B"],
    },
)

ctx = df.SessionContext()
ctx.register_dataset(name="my_table", dataset=pda.dataset(t))

query = """
SELECT "Product" FROM "my_table" UNION ALL SELECT "Product", "Id" FROM "my_table"
"""
df = ctx.sql(query)  # with DF 41 this riases an error, with 42 doesn't

Expected behavior

DataFusion error exception correctly raised

Additional context

No response

[Omega359]

I think this is related to #11961

[alamb]

I think SQL requires the inputs to have the same number of columns

Here is an example in postgres

postgres=# create table foo (x int, y int) ;
CREATE TABLE
postgres=# insert into foo values (1, 1), (1, 2);
INSERT 0 2
postgres=# select x from foo UNION ALL select y, y FROM foo;
ERROR:  each UNION query must have the same number of columns
LINE 1: select x from foo UNION ALL select y, y FROM foo;
                                           ^
postgres=# select x FROM foo UNION ALL select x, y FROM foo;

Perhaps you can get equivalent behavior in DataFusion 42 using a null constant

SELECT "Product" FROM "my_table" UNION ALL SELECT "Product", "Id" FROM "my_table"

to

SELECT "Product", NULL FROM "my_table" UNION ALL SELECT "Product", "Id" FROM "my_table"

[Omega359]

I think the author's concern is not that the sql is invalid, it's that it's not being detected as invalid as early as it used to be. Is that a correct summation @emanueledomingo ?

[alamb]

Ah, sorry -- that is my mistake

[jonahgao]

I think we can check if the number of columns is the same when building Unions, as it is a common scenario and does not depend on wildcard expansion and type coercion.

pub fn union(left_plan: LogicalPlan, right_plan: LogicalPlan) -> Result<LogicalPlan> {
    if left_plan.schema().fields().len() != right_plan.schema().fields().len() {
        return plan_err!(
            "UNION queries have different number of columns: \
            left has {} columns whereas right has {} columns",
            left_plan.schema().fields().len(),
            right_plan.schema().fields().len()
        );
    }

[emanueledomingo]

I think the author's concern is not that the sql is invalid, it's that it's not being detected as invalid as early as it used to be. Is that a correct summation @emanueledomingo ?

Yes exactly. I realised it because i have a set of unit tests to test a function that handles errors and one of them failed. Not a real bug to me, i just want to report that this behaviour changed!

[Omega359]

take

[findepi]

#11961 is probably related, at least "Union queries must have the same number of columns" error changed there.

@emanueledomingo if this is about UNIONs, can we change the issue title to "Datafusion 42 does not raise plan error when UNION branches have different number of columns"?, or is there more to it?

[emanueledomingo]

#11961 is probably related, at least "Union queries must have the same number of columns" error changed there.

@emanueledomingo if this is about UNIONs, can we change the issue title to "Datafusion 42 does not raise plan error when UNION branches have different number of columns"?, or is there more to it?

I experienced this bug only with this kind of query. i have 5 wrong queries in the unit test and only this gave this error