SQL Compilation Error with Snowflake backend

[sprivite]

It seems that if a table has a column of all NULL values, the ibis snowflake backend is unable to write this table.

Here is a minimal example that shows the issue.

# get a connector to snowflake
con = ibis.snowflake.connect(
        user=SNOWFLAKE_USER,
        authenticator="externalbrowser",
        account=SNOWFLAKE_ACCOUNT,
        warehouse=SNOWFLAKE_WAREHOUSE,
        role=SNOWFLAKE_ROLE,
        database=database,
        schema=schema,
    )

# take literally any table in that database and add a NULL column
table = table.mutate(PERSON_ID=None).select("PERSON_ID")

This compiles to:

SELECT
  NULL AS "PERSON_ID"
FROM "YOURTABLE" AS "t0"

#try to write the table:
con.create_table(
    name=name_table,
    database=database,
    obj=table,
    overwrite=overwrite,
    schema=table.schema(),
    )

Gives error:

ProgrammingError: 001003 (42000): SQL compilation error:
syntax error line 1 at position 63 unexpected 'NULL'.
syntax error line 1 at position 69 unexpected 'AS'.

I am using ibis '9.5.0'. Any suggestions would be appreciated! My tables sometimes have NULL columns.

[sprivite]

I think I have solved my own issue. In the example above, it fails because the column is non-nullable. However I also had a column that was of NULL type. By casting it to some datatype, the compilation error goes away:

>> table.schema()
ibis.Schema {
  VALUE       null
}

--> leads to compilation error when writing to disk

but

>> table = table.cast({"VALUE":"float"})

writes successfully.

[cpcloud]

While there's a way to spell "a column of all nulls" in Ibis, most databases do not allow this. Personally, I still don't understand the value of a NULL-only type. We've had some discussion about it, but never came to consensus, so the current status quo remains.

[sprivite]

This is very interesting to know, thank you. In our use case, we have a function that returns a table with 3 columns. However, sometimes one of the columns is undefined and we chose to fill that columns with NULL in order to have a static "shape" of the data. But maybe it makes more sense to just omit the columns that aren't defined. We will think about it!

[cpcloud]

If the column is only sometimes missing, then it could make sense to give that column the same type it gets when it is present.