LOAD 'chdb_hook'; \set ECHO errors /****************************************************************************/ -- Numbers. CREATE TABLE numbers ( u UUID NOT NULL, i2 INT2 NOT NULL, i4 INT4 NOT NULL, i8 INT8 NULL, num numeric NULL, np numeric(32) NULL, nps numeric(12, 6) NULL, f4 float4 NOT NULL, f8 float8 NULL, b bool NOT NULL, o OID NOT NULL, o8 :oid8 NOT NULL ); INSERT INTO numbers VALUES ('00000000-0000-0000-0000-000000000000', 0, 0, 0, 0, 0, 0, 0, 0, false, 0, '0') , ('6ba7b810-9dad-11d1-80b4-00c04fd430c8', 0, 0, NULL, NULL, NULL, NULL, 0, NULL, false, 0, '0') , ('B82601F4-2FF0-4F05-99E4-2CAAC200BA0F', -32768, -2147483648, -9223372036854775808, -987654321.123456789, -987654321, -123456.545675, -1.1, -98.68, false, 0, '0') , ('A411B3DC-76c7-4D5D-92B2-3ab802504f1f', 32767, 2147483647, 9223372036854775807, 987654321.123456789, 987654321, 123456.545675, 1.1, 98.68, true, 0, '18446744073709551615') , ('00000000-0000-0000-0000-000000000000', 42, 42, 42, NULL, NULL, NULL, 'nan', 'nan', true, 0, '0') , ('00000000-0000-0000-0000-000000000000', -42, -42, -42, NULL, NULL, NULL, '-infinity', '-infinity', true, 0, '0') , ('00000000-0000-0000-0000-000000000000', 42, 42, 42, NULL, NULL, NULL, 'infinity', 'infinity', true, 0, '0') ; -- Execute round-trip to all supported formats. Protobuf reads a Nullable -- field holding zero back as NULL, so the zeroed int8 and float8 do not -- survive: https://github.com/chdb-io/chdb-core/issues/152 CREATE TABLE numbers2 (LIKE numbers INCLUDING ALL); \set from_table numbers \set to_table numbers2 \set output_file numbers.tmp \i test/utils/round-trip-formats.sql \set ECHO errors t: TabSeparated t: TabSeparatedRaw t: TabSeparatedWithNames t: TabSeparatedWithNamesAndTypes t: TabSeparatedRawWithNames t: TabSeparatedRawWithNamesAndTypes t: CSV t: CSVWithNames t: CSVWithNamesAndTypes t: CustomSeparated t: CustomSeparatedWithNames t: CustomSeparatedWithNamesAndTypes t: Values t: JSON t: JSONColumns t: JSONColumnsWithMetadata t: JSONCompact t: JSONCompactColumns t: JSONEachRow t: JSONStringsEachRow t: JSONCompactEachRow t: JSONCompactStringsEachRow t: JSONCompactEachRowWithNames t: JSONCompactEachRowWithNamesAndTypes t: JSONCompactStringsEachRowWithNames t: JSONCompactStringsEachRowWithNamesAndTypes t: JSONObjectEachRow t: TSKV t: Native t: RowBinary t: RowBinaryWithNames t: RowBinaryWithNamesAndTypes t: Parquet t: Arrow t: ArrowStream t: ORC t: Avro f: Protobuf f: ProtobufList t: MsgPack t: BSONEachRow /****************************************************************************/ -- Number Arrays. CREATE TABLE number_arrays ( u UUID[] NOT NULL, i2 INT2[] NOT NULL, i4 INT4[] NOT NULL, i8 INT8[] NOT NULL, num numeric[] NOT NULL, np numeric(32)[] NOT NULL, nps numeric(12, 6)[] NOT NULL, f4 float4[] NOT NULL, f8 float8[] NOT NULL, b bool[] NOT NULL, o OID[] NOT NULL, o8 :oid8[] NOT NULL ); INSERT INTO number_arrays VALUES ('{00000000-0000-0000-0000-000000000000}', '{0}', '{0}', '{0}', '{0}', '{0}', '{0}', '{0}', '{0}', '{false}', '{0}', '{0}') , ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}') , ('{6ba7b810-9dad-11d1-80b4-00c04fd430c8, NULL}', '{-32768,32767}', '{-2147483648,2147483647}', '{-9223372036854775808,9223372036854775807}', '{-987654321.123456789,987654321.123456789}', '{-987654321,987654321}', '{-123456.545675,123456.545675}', '{-1.1,1.1}', '{-98.68,98.68}', '{false,true}', '{0, 0}', '{0,18446744073709551615}') , ('{B82601F4-2FF0-4F05-99E4-2CAAC200BA0F, A411B3DC-76c7-4D5D-92B2-3ab802504f1f}', '{0, NULL}', '{NULL, 0}', '{NULL}', '{0, NULL}', '{NULL, 0}', '{NULL}', '{0,NULL}', '{NULL,0}', '{false,NULL}', '{NULL}', '{0,NULL}') ; -- Execute round-trip to all supported formats. Protobuf has no null in a -- repeated field, so the arrays carrying one come back short. CREATE TABLE number_arrays2 (LIKE number_arrays INCLUDING ALL); \set from_table number_arrays \set to_table number_arrays2 \i test/utils/round-trip-formats.sql \set ECHO errors t: TabSeparated t: TabSeparatedRaw t: TabSeparatedWithNames t: TabSeparatedWithNamesAndTypes t: TabSeparatedRawWithNames t: TabSeparatedRawWithNamesAndTypes t: CSV t: CSVWithNames t: CSVWithNamesAndTypes t: CustomSeparated t: CustomSeparatedWithNames t: CustomSeparatedWithNamesAndTypes t: Values t: JSON t: JSONColumns t: JSONColumnsWithMetadata t: JSONCompact t: JSONCompactColumns t: JSONEachRow t: JSONStringsEachRow t: JSONCompactEachRow t: JSONCompactStringsEachRow t: JSONCompactEachRowWithNames t: JSONCompactEachRowWithNamesAndTypes t: JSONCompactStringsEachRowWithNames t: JSONCompactStringsEachRowWithNamesAndTypes t: JSONObjectEachRow t: TSKV t: Native t: RowBinary t: RowBinaryWithNames t: RowBinaryWithNamesAndTypes t: Parquet t: Arrow t: ArrowStream t: ORC t: Avro f: Protobuf f: ProtobufList t: MsgPack t: BSONEachRow /****************************************************************************/ -- Numeric for integers wider than bigint, UInt64 among them. CREATE TABLE wide_ints ( i128 numeric NOT NULL, u128 numeric NOT NULL, i256 numeric NOT NULL, u256 numeric NOT NULL, u64 numeric NOT NULL ); \set wide_structure 'i128 Int128, u128 UInt128, i256 Int256, u256 UInt256, u64 UInt64' INSERT INTO wide_ints VALUES (0, 0, 0, 0, 0) , (170141183460469231731687303715884105727, 340282366920938463463374607431768211455, 57896044618658097711785492504343953926634992332820282019728792003956564819967, 115792089237316195423570985008687907853269984665640564039457584007913129639935, 18446744073709551615) , (-170141183460469231731687303715884105728, 42, -57896044618658097711785492504343953926634992332820282019728792003956564819968, 42, 42) ; CREATE TABLE wide_ints2 (LIKE wide_ints INCLUDING ALL); COPY wide_ints TO 'file:///tmp/numbers.tmp' (format 'TabSeparated', structure :'wide_structure'); COPY wide_ints2 FROM 'file:///tmp/numbers.tmp' (format 'TabSeparated', structure :'wide_structure'); SELECT * FROM wide_ints2 ORDER BY i128; i128 | u128 | i256 | u256 | u64 ------------------------------------------+-----------------------------------------+--------------------------------------------------------------------------------+--------------------------------------------------------------------------------+---------------------- -170141183460469231731687303715884105728 | 42 | -57896044618658097711785492504343953926634992332820282019728792003956564819968 | 42 | 42 0 | 0 | 0 | 0 | 0 170141183460469231731687303715884105727 | 340282366920938463463374607431768211455 | 57896044618658097711785492504343953926634992332820282019728792003956564819967 | 115792089237316195423570985008687907853269984665640564039457584007913129639935 | 18446744073709551615 (3 rows) /****************************************************************************/ -- BFloat16 keeps the leading 16 bits of a float4, so a write drops the low -- mantissa bits. CREATE TABLE bfloats (f FLOAT4 NOT NULL); INSERT INTO bfloats VALUES (0), (1.5), (-2.25), (1.1), (3.4028235e38), ('infinity'), ('-infinity'), ('nan'); CREATE TABLE bfloats2 (LIKE bfloats INCLUDING ALL); COPY bfloats TO 'file:///tmp/numbers.tmp' (format 'TabSeparated', structure 'f BFloat16'); COPY bfloats2 FROM 'file:///tmp/numbers.tmp' (format 'TabSeparated', structure 'f BFloat16'); SELECT f FROM bfloats2 ORDER BY f; f --------------- -Infinity -2.25 0 1.09375 1.5 3.3895314e+38 Infinity NaN (8 rows) \set ECHO errors