Type mismatch when inserting Bool values as UInt into nested columns

[allencchu]

When inserting a Bool value to a non-nested column, a UInt is an accepted format.
However, this does not seem to be true for some nested columns, as it shows a Type mismatch error.

Example:

set flatten_nested=0;
create table testbool
(
	name String,
	tf1 Bool,
	sub1 Nested
	(
		tf2 Bool
	)
)
ENGINE=MergeTree
ORDER BY name

insert into testbool values ('test1',1,[(true)])
Produces: test1 | true | [[true]]

insert into testbool values ('test2',1,[(1)])
Produces: Code: 53. DB::Exception: Type mismatch in IN or VALUES section. Expected: Tuple(tf2 Bool). Got: UInt64: While executing ValuesBlockInputFormat. (TYPE_MISMATCH) (version 22.5.1.2079 (official build))

This can be avoided when manually inserting values. Unfortunately, it seems like clickhouse-jdbc converts Bool values to UInt when using parameterized insertions.

[zhicwu]

Hi @allencchu, Nested data type is mapped to Object[][] in Java. Below works for me on 22.5. You may try that using latest release of JDBC driver or nightly build.

try (ClickHouseConnection conn = newConnection(props);
        ClickHouseStatement stmt = conn.createStatement()) {
    stmt.execute("set flatten_nested = 0; drop table if exists test_nested_bool; "
            + "create table test_nested_bool(name String, tf1 Bool, sub1 Nested(tf2 Bool))engine=Memory; "
            + "insert into test_nested_bool values ('test1',1,[(true)])");
    try (PreparedStatement ps = conn.prepareStatement("insert into test_nested_bool")) {
        ps.setString(1, "test2");
        ps.setObject(2, 0);
        ps.setObject(3, new Object[][] { { false } });
        Assert.assertEquals(ps.executeUpdate(), 1);

        ResultSet rs = stmt.executeQuery("select * from test_nested_bool order by name");
        Assert.assertTrue(rs.next());
        Assert.assertEquals(rs.getString(1), "test1");
        Assert.assertEquals(rs.getInt(2), 1);
        Assert.assertEquals(rs.getBoolean(2), true);
        Assert.assertEquals(rs.getObject(3), new Object[][] { { true } });
        Assert.assertTrue(rs.next());
        Assert.assertEquals(rs.getString(1), "test2");
        Assert.assertEquals(rs.getInt(2), 0);
        Assert.assertEquals(rs.getBoolean(2), false);
        Assert.assertEquals(rs.getObject(3), new Object[][] { { false } });
        Assert.assertFalse(rs.next());
    }
}

[vdimir]

@CurtizJ Could we support such case to insert UInt to nested Bool, what do you think?

[CurtizJ]

The problem is that tuples of one element are removed by parser:

select (1) as c, toTypeName(c);

┌─c─┬─toTypeName(1)─┐
│ 1 │ UInt8         │
└───┴───────────────┘

1 row in set. Elapsed: 0.001 sec. 

select (1, 2) as c, toTypeName(c);

┌─c─────┬─toTypeName((1, 2))──┐
│ (1,2) │ Tuple(UInt8, UInt8) │
└───────┴─────────────────────┘

With 2 or more elements in Nested it works as expected:

SET flatten nested = 0;

CREATE TABLE testbool
(
    `n` Nested(a Bool, b Bool)
)
ENGINE = Memory;

INSERT INTO testbool VALUES (1, 0);

SELECT * FROM testbool;

┌─n──────────────┐
│ [(true,false)] │
└────────────────┘

1 row in set. Elapsed: 0.001 sec. 

Also you can explicitly call tuple function:

select tuple(1) as c, toTypeName(c);

┌─c───┬─toTypeName(tuple(1))─┐
│ (1) │ Tuple(UInt8)         │
└─────┴──────────────────────┘