Nullable issues with RowBinary Format

[clickhouse0987654321]

Question 1> ClickHouse doesn't take Nullable Float64 from RowBinary Format

===================================================

For table without Nullable column, the importer works fine.

DESCRIBE TABLE default.nullNormal

┌─name──┬─type────┬─default_type─┬─default_expression─┐

│ age   │ UInt64  │              │                    │

│ value │ Float64 │              │                    │

└───────┴─────────┴──────────────┴────────────────────┘

clickhouse-client --host=127.0.0.1 --query="insert into default.nullNormal FORMAT RowBinary" < nulltable.ch

:) select * from default.nullNormal;

SELECT *

FROM default.nulltable3

┌─age─┬─value─┐

│ 123 │ 1.223 │

└─────┴───────┘

===================================================

For table with Nullable column, the importer fails to work fine.

DESCRIBE TABLE default.nullTable

┌─name──┬─type──────────────┬─default_type─┬─default_expression─┐

│ age   │ UInt64            │              │                    │

│ value │ Nullable(Float64) │              │                    │

└───────┴───────────────────┴──────────────┴────────────────────┘

clickhouse-client --host=127.0.0.1 --query="insert into default.nullTable FORMAT RowBinary" < nulltable.ch

Code: 33. DB::Exception: Cannot read all data. Bytes read: 7. Bytes expected: 8.

Question 2> What is the value I should use to write a NULL value for Float64 with RowBinary format?

Thank you

[Mikhail Filimonov]

Can't reproduce. Nullable(Float64) in RowBinary format written by Clickhouse are properly interpreted by Clickhouse.

➜ clickhouse-client --query="SELECT arrayJoin([Cast(0.1, 'Nullable(Float64)'), Cast(Null, 'Nullable(Float64)'), Cast(0.0, 'Nullable(Float64)')]) as float_field, 'test' as string_field FORMAT RowBinary" | clickhouse-client --external --file=- --format=RowBinary --name=float_test --structure='float_field Nullable(Float64),string_field String' --query='SELECT * FROM float_test'

0.1 test

\N test

0 test

Use clickhouse output and hexdump to dig the format details, like that 

➜ clickhouse-client --query="SELECT arrayJoin([Cast(1, 'Nullable(Float64)'), Cast(Null, 'Nullable(Float64)'), Cast(2, 'Nullable(Float64)')]) as float_field, 3735928559 as UInt32 FORMAT RowBinary" | hexdump
0000000 0000 0000 0000 f000 ef3f adbe 01de beef
0000010 dead 0000 0000 0000 0000 ef40 adbe 00de
000001f

[clickhouse0987654321]

Hello Mikhail,

Here is how to reproduce the reported issues.

1> Write a UInt64 of 123 to the file of nulltable.ch with RowBinary format

2> Write a Float64 of 1.223 to the file of nulltable.ch with RowBinary format.

Note: one UInt64 and one Float64 have been written into nulltable.ch with RowBinary format.

3> load this binary file into a regular table without problems

┌─name──┬─type────┬─default_type─┬─default_expression─┐

│ age   │ UInt64  │              │                    │

│ value │ Float64 │              │                    │

└───────┴─────────┴──────────────┴────────────────────┘

4> load this binary file into a table with nullable field with problems:

┌─name──┬─type──────────────┬─default_type─┬─default_expression─┐

│ age   │ UInt64            │              │                    │

│ value │ Nullable(Float64) │              │                    │

└───────┴───────────────────┴──────────────┴────────────────────┘

Q1> Can a Nullable filed take values from a regular number(i.e. Float64)?

Q2> What is the method used by ClickHouse to represent a Null Float64 in RowBinary format?

Thank you

[Mikhail Filimonov]

I will just show you few dumps.

Lets see how usual UInt looks like:

➜ clickhouse-client --query="SELECT CAST(123, 'UInt64') FORMAT RowBinary" | hexdump -C -v
00000000  7b 00 00 00 00 00 00 00                           |{.......|
00000008

It's 123 in UInt64. Fair 8 bytes, old-good 64 bit int, little endian. 

Now let's check how it looks with Nullable type

➜ clickhouse-client --query="SELECT CAST(123, 'Nullable(UInt64)') FORMAT RowBinary" | hexdump -C -v
00000000  00 7b 00 00 00 00 00 00  00                       |.{.......|
00000009

Now it's 9 byte instead of 8. One extra byte is needed to store is_null status.

So nullable and not nullable type are not binary compatible / interchangable.

Same with float / Nullable(Float) etc.

➜ clickhouse-client --query="SELECT CAST(123, 'Float64') FORMAT RowBinary" | hexdump -C -v
00000000  00 00 00 00 00 c0 5e 40                           |......^@|
00000008

➜ clickhouse-client --query="SELECT CAST(123, 'Nullable(Float64)') FORMAT RowBinary" | hexdump -C -v
00000000  00 00 00 00 00 00 c0 5e  40                       |.......^@|
00000009

 

And when is_null flag is set up then value is not stored at all,

➜ clickhouse-client --query="SELECT CAST(Null, 'Nullable(Float64)') FORMAT RowBinary" | hexdump -C -v
00000000  01                                                |.|
00000001