Bug: Can't insert option as None

[alanxoc3]

Describe the bug

Here is an example:

result = await db.query(f'''
  INSERT INTO list {{
    name: $name,
    desc: $desc,
  }}
''', {"name": "hello", "desc": None})

Where the desc column is type option.

I'd expect "None" in python to evaluate to the option empty value, but I get this error message:

[{'result': 'Found NULL for field `description`, with record `...`, but expected a option<string>', 'status': 'ERR', 'time': '234.712µs'}]

Steps to reproduce

See "describe bug".

Expected behaviour

See "describe bug".

SurrealDB version

surreal 1.0

surrealdb.py version

python 3.11.5 - surrealdb lib 0.3.1

Contact Details

No response

Is there an existing issue for this?

• [X] I have searched the existing issues

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[maxwellflitton]

Hey @alanxoc3 sorry for the late reply, can you provide the schema that you have for the table you're trying to insert into as right now you're expecting a option<string>.

[AlexFrid]

Also not exactly sure what the bug is here @alanxoc3 None in Python gets translated to NULL in SurrealDB, not sure why you're expecting option<string> 🤔

[CosmKiwi]

Trying to create a record with a null field:

async with Surreal("ws://localhost:8000/rpc") as db:
    await db.signin({"user": "root", "pass": "root"})
    await db.use("test", "test")

    rec = {}
    rec['address'] = None

    await db.create(
        'my_table',
        rec
    )

It works fine if the table type is schemaless:

DEFINE TABLE my_table SCHEMALESS PERMISSIONS NONE; But fails if the table is schemafull, for both string and option<string>. I've also tried datetime, same issue with that.

DEFINE TABLE my_table SCHEMAFULL PERMISSIONS NONE;
DEFINE FIELD address ON my_table TYPE string DEFAULT NULL PERMISSIONS FULL;

DEFINE TABLE my_table SCHEMAFULL PERMISSIONS NONE;
DEFINE FIELD address ON my_table TYPE option<string> DEFAULT NULL PERMISSIONS FULL;

The error returned from the database is:

SurrealPermissionException: There was a problem with the database: Found NULL for fieldaddress, with recordmy_table:iymgrdeb0bwsd7y7win3, but expected a option<string>

Python is converting the None to a database NULL as expected, but the database isn't accepting it.

[CosmKiwi]

Looks like a database bug/limitation, because if I run this query in Surrealist, I get the same error:

create my_table set address = null;

Looks like you can't set fields as null in a schemafull table? SurrealDB version 1.1.1

[AlexFrid]

hey @CosmKiwi, doing this will however give you the expected results:

define field address on my_table type option<string | null> default null;

[AlexFrid]

@alanxoc3 @CosmKiwi closing this as its not an issue with Python but a SurrealQL thing. Currently our null is a value whereas none is not.

Doing just option<string> is saying its string field or no field. Whereas option<string | null> is saying its a string field or field with a null value