I want to set null to non-nullable column, and the column set should use the default value in DDL(jdbc version 0.4.1)

[xlvchao]

table ddl:

create table aiops_local_dev.aiops_collect_1
(
    product                         String,
    service                         String,
    itf                             String,
    latency                     Float64 default inf
)
    engine = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/aiops_local_dev/aiops_collect_1', '{replica}')
        PARTITION BY toYYYYMMDD(time)
        PRIMARY KEY (product, service, itf)
        ORDER BY (product, service, itf)
        TTL time + toIntervalMonth(3)
        SETTINGS index_granularity = 8192;

java:

INSERT INTO EXAMPLE (product, service, itf, latency) VALUES ('p1', 's1', 'i1', null);

expected result:

product	service	itf	latency
p1	s1	i1	infinity

but i got:

product	service	itf	latency
p1	s1	i1	0

I try to set the connection property nullAsDefault to 0 or 1 or 2 , they all doesn't work! （jdbc version 0.4.1 doesn't work, but 0.3.1 works without any connection property!）

[zhicwu]

Hi @xlvchao, sorry the driver does not support default value at this point. nullAsDefault is a workaround for choosing a value for non-nullable column, instead of passing null as it's not supported by RowBinary data format. It's a bit inconvenient but you probably have to set the value explicitly for now.

[xlvchao]

Hi @xlvchao, sorry the driver does not support default value at this point. nullAsDefault is a workaround for choosing a value for non-nullable column, instead of passing null as it's not supported by RowBinary data format. It's a bit inconvenient but you probably have to set the value explicitly for now.

Why the JDBC version 0.3.1 works without any connection property of jdbc-url?

table ddl:

create table aiops_local_dev.aiops_collect_1
(
    product                         String,
    service                         String,
    itf                             String,
    latency                     Float64 default inf
)
    engine = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/aiops_local_dev/aiops_collect_1', '{replica}')
        PARTITION BY toYYYYMMDD(time)
        PRIMARY KEY (product, service, itf)
        ORDER BY (product, service, itf)
        TTL time + toIntervalMonth(3)
        SETTINGS index_granularity = 8192;

java:

INSERT INTO EXAMPLE (product, service, itf, latency) VALUES ('p1', 's1', 'i1', null);

expected result:

product	service	itf	latency
p1	s1	i1	infinity

and i got what i expect:

product	service	itf	latency
p1	s1	i1	infinity

[zhicwu]

Because 0.3.1 uses TabSeparated data format for query and insert.

I'll try to an option to disable automatic type inferring so that the driver will send the subsituted query to ClickHouse without binary data - slower as before but should work well for small queries.

[xlvchao]

Because 0.3.1 uses TabSeparated data format for query and insert.

I'll try to an option to disable automatic type inferring so that the driver will send the subsituted query to ClickHouse without binary data - slower as before but should work well for small queries.

How’s going? @zhicwu

[zhicwu]

No I haven't made the change yet. Meanwhile, there's a workaround using input function, for example:

Properties props = new Properties();
try (ClickHouseConnection conn = newConnection(props);
        Statement s = conn.createStatement();
        PreparedStatement ps = conn.prepareStatement(
                "insert into test_insert_default_value select id, name from input('id UInt32, name Nullable(String)')")) {
    s.execute("drop table if exists test_insert_default_value;"
            + "create table test_insert_default_value(n Int32, s String DEFAULT 'secret') engine=Memory");
    ps.setInt(1, 1);
    ps.setString(2, null);
    ps.addBatch();
    ps.setInt(1, -1);
    ps.setNull(2, Types.ARRAY);
    ps.addBatch();
    ps.executeBatch();
    try (ResultSet rs = s.executeQuery("select * from test_insert_default_value order by n")) {
        Assert.assertTrue(rs.next());
        Assert.assertEquals(rs.getInt(1), -1);
        Assert.assertEquals(rs.getString(2), "secret");
        Assert.assertTrue(rs.next());
        Assert.assertEquals(rs.getInt(1), 1);
        Assert.assertEquals(rs.getString(2), "secret");
        Assert.assertFalse(rs.next());
    }
}

Ideally, the driver should be smart enough to build the boring query automatically based on inferred table structure.

[zhicwu]

Thank you for confirming. Since the workaround is inconvenient and no progress has been made, let's continue to keep the issue open.

[mzitnik]

A new format that support defaults https://clickhouse.com/docs/en/interfaces/formats#rowbinarywithdefaults

[zhicwu]

A new format that support defaults https://clickhouse.com/docs/en/interfaces/formats#rowbinarywithdefaults

If it's not back ported to previous releases, we may still have to support both. Perhaps it's better to use the input function workaround.

[dmitrybugakov]

Hello @zhicwu,

Could you please update on any plans to address Issue #1296

Thank you