Issue when using `Nullable` SQL type

[DenisCarriere]

Having issues using Nullable field type https://clickhouse.com/docs/en/sql-reference/data-types/nullable

CREATE TABLE IF NOT EXISTS blocks
(
    `id` String,
    `difficulty` Int64
)
ENGINE = ReplacingMergeTree
PRIMARY KEY id
ORDER BY id

Substreams test code logic

match header.difficulty {
    Some(difficulty) => row.change("difficulty", ("", "123")),
    None => row,
};

❌ Errors

• when difficulty=123

  2024-07-26T23:47:02.098+0200 ERRO (sink-sql) handle BlockScopedData message at block #10 (4ff4a38b278ab49f7739d3a4ed4e12714386a9fdf72192f2e8f7da7822f10b4d): failed to flush at block #10 (4ff4a38b278ab49f7739d3a4ed4e12714386a9fdf72192f2e8f7da7822f10b4d): dialect flush: failed to get values: converting value "123" to type "int64" in column "difficulty": unsupported pointer type int64

• when difficulty=null (empty)

  2024-07-26T23:47:51.667+0200 ERRO (sink-sql) handle BlockScopedData message at block #10 (4ff4a38b278ab49f7739d3a4ed4e12714386a9fdf72192f2e8f7da7822f10b4d): failed to flush at block #10 (4ff4a38b278ab49f7739d3a4ed4e12714386a9fdf72192f2e8f7da7822f10b4d): dialect flush: executing for entry ["0x476574682f76312e302e302f6c696e75782f676f312e342e32" '\x00' 'ᎈ' '\x00' "0x88e96d4537bea4d9c05d12549907b32561d3bf31f45aae734cdc119f13406cb6" "0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" "0x05a56e2d52c817161883f50c441c3228cfe54d9f" "0x969b900de27b6ac6a67742365dd65f55a0526c41fd18e1b16f1a1215c2e66f59" '�' '\x01' "0x1dcc4de8dec75d7aab85b567b6ccd41ad312451b948a7413f0a142fd40d49347" "0xd4e56740f876aef8c010b86a40d5f56745a118d0906a34e69aec8c0db1cb8fa3" "0x56e81f171bcc55a6ff8345e692c0f86e5b48e01b996cadc001622fb5e363b421" "537" "0xd67e4d450343046425ae4271474353857ab860dbc0a1dde64b41b5cd3a532bf3" '\x00' '�' '\x00' "0x56e81f171bcc55a6ff8345e692c0f86e5b48e01b996cadc001622fb5e363b421"]: clickhouse [Append]: clickhouse: expected 20 arguments, got 19

✅ work around solution

Set null values as 0

[DenisCarriere]

Clickhouse best practices recommend avoiding using Nullable fields https://clickhouse.com/docs/en/cloud/bestpractices/avoid-nullable-columns

This issue isn't fixed in the substreams sink sql, however Nullable types shouldn't be used

Alternative setting integer values to 0 and String values to "" empty string