Databend will eventually become a weak type like mysql, or a strong type like postgresql

[hanyisong]

Databend will eventually become a weak type like mysql, or a strong type like postgresql？

Missing some implicit type conversions
e.g

select (((+12.3123))OR(((1036270507)LIKE(1.123))));

ERROR 1105 (HY000): Code: 1007, displayText = Expected a string type, but got UInt32 (while in select before projection).

when t0.c0 is of type Boolean

t0.c0 BETWEEN 1517580277 AND 1356855360

Can not compare Boolean(Boolean) with UInt32(UInt32)

when t0.c1 is of type varchar

true < t0.c1

Code: 1007, displayText = Can not compare Boolean(Boolean) with String(String).

when t1.c0 is of type varchar

NOT t1.c0
 
Code: 1010, displayText = Cast error happens in casting from String to Boolean (while in processor thread 0).

when t13.c1 is Nullable(String)

-1257182980 IN (false, t13.c1)

Code: 1010, displayText = Cast error happens in casting from Nullable(String) to Int64 (while in processor thread 0).

when t0.c0 is of type int

select * from t0 where c0 IN(0,10,1.1);

ERROR 1105 (HY000): Code: 1104, displayText = called `Result::unwrap()` on an `Err` value: Code: 1010, displayText = Can't construct type from Int64(Int64) and Float64(Float64).
   0: common_exception::exception_code::<impl common_exception::exception::ErrorCode>::BadDataValueType
             at /workspace/common/exception/src/exception_code.rs:31:71
   1: common_datavalues::types::type_coercion::numerical_coercion
             at /workspace/common/datavalues/src/types/type_coercion.rs:141:32
   2: <common_functions::scalar

when boolean type participate in arithmetic operations

select ((TIMESTAMP '1969-12-22 22:36:52' IS NOT NULL)-(12.123));
select (- (true NOT IN (0.12312, 1358368073)));
select (COT((- (true NOT IN (1.23, 1358368073)))));
---------------------error-info---------------------        
> Code: 1010, displayText = Ops is not support on datatype: Boolean (while in select before projection).

[Xuanwo]

Cc @leiysky for comments.

[leiysky]

In short, Databend SQL is strong typing.

We are working on a brand new expression system, which involves a formal type system. It will enforce the user to use an explicit type cast for incompatible data types(e.g. from string to integer), and can implicitly cast between compatible types(e.g. int32 and int64).

You can get more details from:

• Tracking issue of migrating to the new expression: Tracking issue for new expression framework #6547
• RFC of the type system: RFC: Formal Type System #5438