Default column value applied when explicit NULL value specified on insert

[gordthompson]

UCanAccess version 5.1.2-SNAPSHOT

Attempting to insert an explicit NULL value into a column that is nullable and has a default value causes the default value to be inserted instead of the NULL value.

UCanAccess>create table zzz (id text(50) primary key, int_col integer null default 0);
UCanAccess>No rows affected

UCanAccess>insert into zzz (id) values ('int_col omitted');
UCanAccess>1 row(s) affected

UCanAccess>select * from zzz;

·-----------------+---------·
| id              | int_col |
·-----------------+---------·
| int_col omitted |       0 |
·-----------------+---------·

UCanAccess>insert into zzz (id, int_col) values ('int_col explicit NULL', NULL);
UCanAccess>1 row(s) affected

UCanAccess>select * from zzz;

·-----------------------+---------·
| id                    | int_col |
·-----------------------+---------·
| int_col explicit NULL |       0 |
| int_col omitted       |       0 |
·-----------------------+---------·

UCanAccess>insert into zzz (id, int_col) values ('int_col some value', 123);
UCanAccess>1 row(s) affected

UCanAccess>select * from zzz;

·-----------------------+---------·
| id                    | int_col |
·-----------------------+---------·
| int_col explicit NULL |       0 |
| int_col omitted       |       0 |
| int_col some value    |     123 |
·-----------------------+---------·

The same behaviour is observed for Integer, Long Integer, Double, Decimal, and Short Text columns.

[spannm]

Thanks for reporting this issue, @gordthompson
Do you know whether this is a regression or bug that has been existing for longer?

(I am currently travelling so it will be a couple of days before I can look into it.)

cheers,
Markus

[gordthompson]

I just reproduced the behaviour with UCanAccess 4.0.4 so the issue has been around for a while.

[spannm]

Finally got around to investigating this issue.

For columns with default values HSQL creates before_insert triggers which are applied to the insert data (Object[]) before the actual insert is executed (call to Table.insertSingleRow). This trigger updates the data array by replacing null by the default value, not honouring an explicit NULL in the statement.

Such trigger should not be applied when a column is explicitly set to NULL.

This looks like an issue in HSQL.
At the same time, very surprising that it has not been discovered until now!?!
It cannot be solved in UCanAccess I'm afraid.

Update: It is an issue in UCanAccess, see comments below.

Technical Details

The trigger def holds a 'routine' which in turn holds a 'statement' of type StatementTypes.IF which looks something like this:
IF NEW.yourColumn IS NULL THEN SET NEW.yourColumn=columnDefault;END IF

Used org.hsqldb:hsqldb:2.7.3 (classifier debug) for debugging

Added a unit test (ColumnDefaultTest) which is @Disabled for the time being (as it obviously fails).

Stacktrace

    StatementCompound.execute(Session) line: 358
    TriggerDefSQL.pushPair(Session, Object[], Object[]) line: 114
    Table.fireTriggers(Session, int, Object[], Object[], int[]) line: 2587
    StatementInsert(StatementDML).insertSingleRow(Session, PersistentStore, Object[]) line: 1033
    StatementInsert.getResult(Session) line: 166
    StatementInsert(StatementDMQL).execute(Session) line: 173
    Session.executeCompiledStatement(Statement, Object[], int) line: 1463
    Session.executeDirectStatement(Result) line: 1310
    Session.execute(Result) line: 1048
    JDBCStatement.fetchResult(String, int, int, int[], String[]) line: 2124
    JDBCStatement.execute(String) line: 618
    Execute.executeWrapped() line: 40
    Execute(AbstractExecute).executeBase() line: 215
    Execute.execute() line: 30
    [..]
    UcanaccessStatement.execute(String) line: 115
    ColumnDefaultTest(UcanaccessBaseTest).executeStatements(Statement, CharSequence...) line: 437

[gordthompson]

Hi @spannm - Thanks for looking into this.

I just tested with DBeaver using the "HSQLDB / HSQL Embedded" driver (hsqldb-2.7.3.jar) and did not reproduce the issue with vanilla HSQLDB. After setting up

CREATE TABLE t (id varchar(50) PRIMARY KEY, int_col int DEFAULT 0 NULL)

INSERT INTO t (id, int_col) VALUES ('int_col explicit NULL', NULL)

INSERT INTO t (id) VALUES ('int_col omitted')

when I do …

SELECT * FROM t

… I get

ID                   |INT_COL|
---------------------+-------+
int_col explicit NULL|       |
int_col omitted      |      0|

Might this issue be related to the HSQLDB trigger issue discussed here?

https://sourceforge.net/p/hsqldb/bugs/1697/

[gordthompson]

Just a thought. (I haven't worked with Java for quite a few years now.)

updates the data array by replacing null by the default value

Could the problem be that the code is using .setObject(…, null) instead of .setNull(…). In other words, using Java null instead of java.sql.Types.Null ?

[spannm]

Thanks for the input, @gordthompson. But it's none of that

UCanAccess creates a before insert trigger for each column default value that looks something like this:

CREATE TRIGGER TR_TBL_TINYINT_DEFAULT0 BEFORE INSERT ON TBL_TINYINT 
REFERENCING NEW ROW AS NEW FOR EACH ROW IF NEW.COL_TINYINT IS NULL THEN SET NEW.COL_TINYINT = 99; END IF

(see class net.ucanaccess.converters.LoadJet)

At the time HSQLDB applies this logic to the new record, it cannot be differentiated whether a NULL value was explicitly set in the insert sql or is merely the initialized empty record 'template'. As a result the default value overwrites the explicit null in the sql.

[gordthompson]

Since DEFAULT only affects INSERT, a workaround would be to INSERT the rows first, and then UPDATE the affected columns (nullable with default values):

import jaydebeapi

db_path = "my_db.accdb"
cnxn = jaydebeapi.connect(
    jclassname="net.ucanaccess.jdbc.UcanaccessDriver",
    url=f"jdbc:ucanaccess://{db_path};newDatabaseVersion=V2010",
    driver_args=["", ""],
    jars="/home/gord/Downloads/UCanAccess/ucanaccess-5.1.2-SNAPSHOT-uber.jar",
)
crsr = cnxn.cursor()


def dump_table():
    crsr.execute("SELECT * FROM zzz")
    print(crsr.fetchall())


# try:
#     crsr.execute("DROP TABLE zzz")
#     cnxn.commit()
# except:
#     pass
# crsr.execute(
#     "CREATE TABLE zzz (id text(50) PRIMARY KEY, int_col long NULL DEFAULT 0)"
# )
# cnxn.commit()
#
crsr.execute("DELETE FROM zzz")
cnxn.commit()

data = [
    (None, "explicit null 1"),
    (123, "explicit non-null"),
    (None, "explicit null 2"),
]
crsr.executemany("INSERT INTO zzz (int_col, id) VALUES (?, ?)", data)
cnxn.commit()
dump_table()
"""
[('explicit non-null', 123), ('explicit null 1', 0), ('explicit null 2', 0)]
"""

crsr.executemany("UPDATE zzz SET int_col = ? WHERE id = ?", data)
cnxn.commit()
dump_table()
"""
[('explicit non-null', 123), ('explicit null 1', None), ('explicit null 2', None)]
"""