click_house icon indicating copy to clipboard operation
click_house copied to clipboard

many parameterized types don't parse properly

Open slotrans opened this issue 3 years ago • 0 comments

create table dt_type_test 
( 
  a DateTime
, b Nullable(DateTime)
, c DateTime64(3)
, d Nullable(DateTime64(3))
, e DateTime64(3, 'UTC')
, f Nullable(DateTime64(3, 'UTC'))
, g Decimal(18,2) 
) 
engine=Log ;

insert into dt_type_test values (now(), now(), now(), now(), now(), now(), 5.99);
[14] pry(main)> rec = ClickHouse.connection.select_one("select * from dt_type_test limit 1")
SQL (Total: 10MS, CH: 4MS) select * from dt_type_test limit 1;
                                                              Read: 1 rows, 51.0B. Written: 0 rows, 0B
=> {"a"=>Fri, 01 Jul 2022 17:48:53 +0000,
 "b"=>Fri, 01 Jul 2022 17:48:53 +0000,
 "c"=>"2022-07-01 17:48:53.000",
 "d"=>"2022-07-01 17:48:53.000",
 "e"=>"2022-07-01 23:48:53.000",
 "f"=>"2022-07-01 23:48:53.000",
 "g"=>5.99}
[15] pry(main)> rec["g"].class
=> Float

My expectation is that all of a, b, c, d, e, and f would be of type DateTime, and g would be of type BigDecimal. Instead we see c-f as strings, and g as a Float.

Poking around, I found at least two problems.

One is that DateTime64(%d) is not a registered type. That's an easy fix.

The bigger problem is that parameterized types with %d placeholders don't seem to work at all. For example, it appears that ClickHouse::Response::ResultSet#extract_type_info parses DateTime64(3, 'UTC') as DateTime64(%s, %s) rather than DateTtime64(%d, %s). And since DateTime64(%s, %s) isn't a registered type, it falls back to treating it as a string. Similarly, Decimal(%d, %d) is a registered type, but gets parsed as Decimal(%s, %s) which doesn't match.

In fact, extract_type_info makes no attempt to insert %d placeholders, always using %s, which is inconsistent with how the type registry works. So if I'm understanding this right, no parameterized type with a %d placeholder will ever work.

slotrans avatar Jul 02 '22 00:07 slotrans