is it possible to specify DEFAULT value for PreparedStatements

[PatrickTu]

Table DDL

CREATE TABLE EXAMPLE
(
    id     Int64,
    date   DateTime64,
    number Int64 DEFAULT -1
) ENGINE = MergeTree
      ORDER BY (id);

---

From ClickHouse's documentation, looking to specify DEFAULT when inserting multiple values that should utilize the default value defined for the column.

INSERT INTO EXAMPLE (id, date, number)
VALUES (1, DEFAULT, DEFAULT) (2, DEFAULT, 2) (3, '2022-01-01 00:00:00.000', 3);

Is it possible to specify this using PreparedStatement?

...
preparedStatement.setObject(1, 1);
preparedStatement.setObject(2, DEFAULT);
preparedStatement.setObject(3, DEFAULT);
preparedStatement.addBatch();
...
preparedStatement.executeBatch();
...

---

Expected results

id	date	number
1	1970-01-01 00:00:00.000	-1
2	1970-01-01 00:00:00.000	2
3	2022-01-01 00:00:00.000	3

[zhicwu]

Sorry for the late response. Please use null value.

[PatrickTu]

Hi @zhicwu, I tried to supply null but this error comes up when we run addBatch(). This uses the same table from above.

Exception in thread "main" java.lang.NullPointerException
	at com.clickhouse.client.data.BinaryStreamUtils.writeDateTime64(BinaryStreamUtils.java:1534)
	at com.clickhouse.client.data.ClickHouseRowBinaryProcessor$MappedFunctions.lambda$buildMappingsForDataTypes$57(ClickHouseRowBinaryProcessor.java:319)
	at com.clickhouse.client.data.ClickHouseRowBinaryProcessor$MappedFunctions.serialize(ClickHouseRowBinaryProcessor.java:485)
	at com.clickhouse.jdbc.internal.InputBasedPreparedStatement.addBatch(InputBasedPreparedStatement.java:295)

PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO EXAMPLE (id, date, number) VALUES (?, ?, ?)");

preparedStatement.setObject(1, 1);
preparedStatement.setObject(2, null);
preparedStatement.setObject(3, null);
preparedStatement.addBatch();

[zhicwu]

Thanks @PatrickTu. This is definitely a bug. Will fix the issue and release a new patch in the coming weekend.

[PatrickTu]

Thank you, I just tested this with [DateTime, Date, Date32] and they are fine. Looks like the only one impacted by the error is DateTime64

[zhicwu]

Turns out there's no generic approach to write empty or null value in RowBinary format. Switching TabSeparated format might work though. NPE is ugly and should be avoided in general but I need to do some more testing to see if there's negative impact to performance.

[PatrickTu]

Okay, I'll play around with TabSeparated format when I have more bandwidth.

In the meantime, to get past the issue of associating NULL with a column's default value...

Before I run setObject(int, value), I will check if the value is null and then...

1. Overwrite the value if the column has a defined default value
2. Overwrite the value based on the default value of the corresponding ClickHouseDataType by retrieving com.clickhouse.client.ClickHouseColumn

I just added a static mapper that aligns with the documentation.

I don't really prefer this route as we'll have to ensure that the default mapper is compatible with clickhouse-jdbc and ClickHouse Server going forward.

Thoughts?

[zhicwu]

Sorry I didn't make it clear. You don't have to deal with too many details of the data format , you just need to change connection property format to TabSeparatedWithNamesAndTypes. This way, the driver will use TabSeparatedWithNamesAndTypes for select queries and TabSeparated(without headers) for insert queries. It does not work in latest release(0.3.2-patch7), but I'll fix that in these two days and then release another patch in weekend.

As to the workaround you mentioned above, it's better to offload to the driver, so that nobody has to suffer. I'm thinking of a new connection property like nullAsDefault to control this - when it's set to true, the driver will convert null value to default value based on target column data type(not column definition). Let me give it a shot tonight when I get home to see if it's feasible.

[zhicwu]

Turns out it's not that simple :( First there's no generic way to set null in RowBinary format. On the other hand, switching to TabSeparated works in some cases(see tests at here) but not all.

I think the best way as of now is to set default value by yourself, and you have to update application each time default value in DDL is changed... I'll leave this issue open, and try again later by enhancing the parser to extract default value(constant only) from column/table definition.

[ShortFinger]

Is possible jdbc do not check null just use BinaryStreamUtils.writeNull(output); to write null directly? I saw #935 and insert_null_as_default , i think it use to tell jdbc to deal with null value but not to deal with null column. Or there is other way that we can get DEFAULT VALUE before write,like java.sql.ResultSetMetaData?

[zhicwu]

Hi @ShortFinger, thanks for sharing your thoughts. It's more of a limitation/restriction of the RowBinary data format we're using. IMO, people may prefer to manage default value in database especially in database-centric design, but if ClickHouse is not the only database you're using, you probably should consider to let service/external metadata store/scheme registry etc. to manage, which might be a better design. As to those fancy, dynamic default value generated runtime, I think they're mainly for convenience but not mandatory.

Is possible jdbc do not check null just use BinaryStreamUtils.writeNull(output); to write null directly?

Unfortunately it won't work. Maybe I should rename writeNull to writeNullFlag, as it's just about writing a flag as part of nullable column value.

Or there is other way that we can get DEFAULT VALUE before write,like java.sql.ResultSetMetaData?

This means we need to enhance the parser to get default value, and we know it might not be a constant but something has to be calculated in runtime :)

[ShortFinger]

After i discuss with my workmate, We thought is not a bug. Because is a constraint，Column in DDL constraint to not nullable. When column set to not null default xxx it means this column can not insert null value but when it not to insert value it will be set default value xxx ,if it is nullable that means this column can insert null value. That is different meaning between write null and not insert this column. So jdbc is not allow null value write into not null column is right, even this column set default.