Nothing columns in DB backends

[radeusgd]

Most DB backends do not allow to create a table with a column that has NULL column type. So we disallow Value_Type.Null in select_into_database_table.

However, we should make it possible to use Nothing in expressions, and perhaps create an all-nothing column. Just some cautious usage of the value-types needs to be done so that we can use the Null type in the temporary column but don't do anything invalid like CAST(x as NULL).

There are existing Expression_Spec tests that just need to be enabled to test this.

[radeusgd]

Check behaviour of SELECT 1, NULL, 'a';

[radeusgd]

It seems that there is no good way to have a NULL type column in Postgres. Creating such column in CREATE TABLE obviously does not make sense, but even a 'virtual' column (e.g. result of SELECT NULL;) gets some type - defaulting to Char if no constraints.

After a discussion we decided that it just doesn't make sense to try creating some weird construction to get Null column type to work in DB.

The only real issue with it is that expressions that have Nothing as left-hand side won't work in DB - e.g. Nothing + [A] or ISNULL(Nothing). But such expressions have little utility in practice. Currently they will fail with Unsupported_Database_Operation - e.g. Postgres tables do not support Null types.

[radeusgd]

Check behaviour of SELECT 1, NULL, 'a';

In Postgres:

> c.query (..Raw_SQL "SELECT 1 AS a, NULL AS b, 'a' AS c") . column_info . print
   | Column | Items Count | Value Type
---+--------+-------------+--------------------------------
 0 | a      | 1           | (Value_Type.Integer 32 bits)
 1 | b      | 0           | (Value_Type.Char Nothing True)
 2 | c      | 1           | (Value_Type.Char Nothing True)

[radeusgd]

After a discussion we decided that it just doesn't make sense to try creating some weird construction to get Null column type to work in DB.

Exception is SQLite which cares little about types so it seems to make sense to have a Nothing column there. We can disable it though if it would be causing any problems.

[enso-bot]

Radosław Waśko reports a new STANDUP for yesterday (2025-01-22):

Progress: Updated tests in the upstream PR according to the discussion. Created a draft for the single-value column intersection type. Debugging interpreter crashes, got it resolved with Jaroslav's help. It should be finished by 2025-01-22.

Next Day: Next day I will be working on the #12095 task. Prepare PR for the intersection type.