Cozo 0.7.5 QueryException: required column alias not provided by input

[athanhat]

Hi, in the relation Core with heading

:create Core {id: Uuid default rand_uuid_v4()  => 
    name: String, 
    label: String, 
    alias: String?, 
    notes: String?  default '<NI>', 
    usage: String?  default '<NI>', 
    description: String?  default '<NI>' }

the following row is inserted

row = {'name': 'foonam', 'label': 'foolab', 'notes': 'bar'}

client.put('Core', row)

Notice that alias is of type string? (nullable), the default value has not been specified and in the row dictionary that key is omitted.

In that case, inserting the row above fails and CozoScript produces the following error message

QueryException: required column alias not provided by input

But the alias field is nullable, so one would expect that if this column is not present in the input it will automatically take the null value. Well at least this is the case with SQL insert command. If a column allows NULL values and has no NOT NULL constraint, then it can be omitted from the insert values. Why is that different with CozoScript ?

Now if I try to insert the same row but with the alias key in the dictionary, that succeeds and I am getting an OK status.

row = [{'name': 'foonam', 'label': 'foolab', 'notes': 'bar', 'alias': ''}, 
           {'name': 'foonam', 'label': 'foolab', 'notes': 'bar', 'alias': None}]

client.put('Core', row)

Here is the database after the insertion of these two rows

This screen capture shows that if you specify nullable columns that have a default value, let's say <NI> these can be omitted from the put command.

The question that arises here is how you can omit a nullable column from the put when there is not a default value.

[athanhat]

OK after some experimentation, I found how to omit nullable columns from CozoScript put command.
When you define the relation with the :create command you must specify default values for any nullable column, otherwise it cannot be omitted from the put command, e.g.

{:create Core {id: Uuid default rand_uuid_v4()  => 
    name: String, 
    label: String, 
    alias: String? default default null, 
    notes: String?  default '<NI>', 
    usage: String?  default '<NI>', 
    description: String?  default '<NI>' }

Here the alias is nullable, it has a default value null and it can be omitted from the following put

row = [{'name': 'foonam1', 'label': 'foolab1', 'notes': 'bar1'}, 
           {'name': 'foonam2', 'label': 'foolab2', 'notes': 'bar2'}]

client.put('Core', row)

Here is the important reference from documentation that seems to contradict my case.

For :put, :remove, :ensure and :ensure_not, you do not need to specify all existing columns in the spec if the omitted columns have a default generator, or if the type of the column is nullable, in which case the value defaults to null.

But according to the error I described above you cannot omit any nullable column in the spec, unless it has a default value. Then it continues...

For these operations ( :put, :remove, :ensure and :ensure_not), specifying default values does not have any effect and will not replace existing ones.

In the context of the specific paragraph, that is not clear and a bit confusing, what do you mean ?

Summary

In the current implementation of CozoScript 0.7.5 it should become clear in the documentation when a column is required e.g.:

• A column is required when there is not a default value, nullable columns are not an exception here
• A column is not required when there is a default value, the default null must be specified for nullable columns

if that is not the intended behavior then it should be fixed in the code

PS: It is also confusing that instead of having a required attribute of a column, you have a nullable attribute with a different meaning