Support basic types

[AShiou]

以下 type 會拋出 exception

• Decimal：support big decimal which value great than Long.MAX_VALUE
  Parse decimal literal error #43 (comment)
• Bytea：Support VARBINARY type #41
• Json
• Timestamp
• Interval
• Array：Rewrite the array type syntax in Calcite for BigQuery or others #69
• Record

[AShiou]

Bytea type

Input the followed sql,

SELECT CAST('\x68656c6c6f' as BYTEA) col_0;

Will get the wrong value

java.lang.AssertionError: 
Expecting:
 <[104, 54, 53, 54, 99, 54, 99, 54, 102]>
to be equal to:
 <[104, 101, 108, 108, 111]>
but was not.

Possible solution

Maybe we can use add \x every 2 hex digits for BigQuery, to become

SELECT CAST('\x68\x65\x6c\x6c\x6f' as BYTEA) col_0;

https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#escape_sequences

[AShiou]

Interval type

Input the followed sql,

canner-cml=> SELECT INTERVAL '3 year 5 month' AS col;
 interval_ym 
-------------
 3-0 0 0:0:0

Actual should be

cannerflow-sql-engine=# SELECT INTERVAL '3 year 5 month' AS col;
      col       
----------------
 3 years 5 mons
(1 row)

Will get some problems

1. wrong format
2. wrong value
   Because we send wrong statement to BigQuery.

   SELECT INTERVAL  '3' YEAR col
   

   Also, BigQuery only support single datetime part, so we should throw exception if the user insert multiple datetime part.
   https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#interval_literals

[AShiou]

Array Type

Input the followed sql,

canner-cml=> select cast('{true, false}' as boolean[]) col_0;
ERROR:  line 1:39: mismatched input '['. Expecting: ')', '.'

Will get the exception when parsing.

Possible solution

We should rewrite sql for BigQuery

select cast([true, false] as array<boolean>) col_0

Notice: BigQuery array don't support array of array
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#array_type

[AShiou]

Row type

Input the followed sql,

canner-cml=> select row('abc', 'cde');
ERROR:  com.google.cloud.bigquery.BigQueryException: Function not found: `ROW`; Did you mean pow? at [1:8]

Will get error because BigQuery don't support row.

Possible solution

We should find another BigQuery type to map.