lightdb支持cast(expr as unsigned)

cast(expr as unsigned)"/>

lightdb支持cast(expr as unsigned)

LightDB 从23.4开始支持mysql 的cast(expr as unsigned) 语法，下面说明下不兼容的注意点及示例

注意点

• LightDB 还没有实际的unsigned bigint 类型
• 返回类型与mysql不同，LightDB 为 numeric， mysql 为 unsigned bigint.
• 表达式不能为日期/时间类型（mysql 支持）。具体见示例。
• 字符串格式的浮点型不会被截断，这与mysql不同，mysql会截断，如’1.9’ 会被截断为1。具体见示例。
• 负浮点型常量不会被截断，这与mysql5.7不同，mysql5.7会截断（mysql8不会），如-1.9 会被截断为0（但mysql中浮点型字段中存负数，转换时不会被截断）。具体见示例。

示例

lightdb@test_m=# select cast(123 as unsigned) as unsigned;unsigned 
----------123
(1 row)lightdb@test_m=# select cast(-123 as unsigned) as unsigned;unsigned       
----------------------18446744073709551493
(1 row)lightdb@test_m=# select cast(18446744073709551615 as unsigned) as unsigned;unsigned       
----------------------18446744073709551615
(1 row)lightdb@test_m=# select cast(18446744073709551616 as unsigned) as unsigned;
WARNING:  unsigned bigint out of range, Truncatedunsigned       
----------------------18446744073709551615
(1 row)lightdb@test_m=# select cast(-9223372036854775808 as unsigned) as unsigned;unsigned       
---------------------9223372036854775808
(1 row)lightdb@test_m=# select cast(-9223372036854775809 as unsigned) as unsigned;
WARNING:  unsigned bigint out of range, Truncatedunsigned 
----------0
(1 row)
lightdb@test_m=# select cast(1.4 as unsigned) as unsigned;unsigned 
----------1
(1 row)lightdb@test_m=# select cast(1.5 as unsigned) as unsigned;unsigned 
----------2
(1 row)lightdb@test_m=# select cast(1.9 as unsigned) as unsigned;unsigned 
----------2
(1 row)-- mysql5.7为 0 ，mysql8.0: 18446744073709551614
lightdb@test_m=# select cast(-1.9 as unsigned) as unsigned;unsigned       
----------------------18446744073709551614
(1 row)
-- mysql5.7为 0 ，mysql8.0: 18446744073709551614
lightdb@test_m=# select cast(-1.5 as unsigned) as unsigned;unsigned       
----------------------18446744073709551614
(1 row)
-- mysql5.7为 0 ，mysql8.0: 18446744073709551615
lightdb@test_m=# select cast(-1.4 as unsigned) as unsigned;unsigned       
----------------------18446744073709551615
(1 row)-- mysql: 1
lightdb@test_m=# select cast('1.4' as unsigned) as unsigned;unsigned 
----------1
(1 row)-- mysql: 1
lightdb@test_m=# select cast('1.5' as unsigned) as unsigned;unsigned 
----------2
(1 row)-- mysql: 1
lightdb@test_m=# select cast('1.9' as unsigned) as unsigned;unsigned 
----------2
(1 row)-- mysql: 18446744073709551615
lightdb@test_m=# select cast('-1.4' as unsigned) as unsigned;unsigned       
----------------------18446744073709551615
(1 row)-- mysql: 18446744073709551615
lightdb@test_m=# select cast('-1.5' as unsigned) as unsigned;unsigned       
----------------------18446744073709551614
(1 row)-- mysql: 18446744073709551615
lightdb@test_m=# select cast('-1.9' as unsigned) as unsigned;unsigned       
----------------------18446744073709551614
(1 row)lightdb@test_m=# create table test_c(key1 float4);
CREATE TABLE
lightdb@test_m=# insert into test_c values('-1.6');
INSERT 0 1
lightdb@test_m=# insert into test_c values('-1.4');
INSERT 0 1
lightdb@test_m=# insert into test_c values('-1.5');
INSERT 0 1
lightdb@test_m=# select cast(key1 as unsigned) , key1 from test_c;numeric2unsigned   | key1 
----------------------+------18446744073709551614 | -1.618446744073709551615 | -1.418446744073709551614 | -1.5
(3 rows)lightdb@test_m=# select cast(cast('20121212' as date) as unsigned);
ERROR:  cannot cast type date to numeric
LINE 1: select cast(cast('20121212' as date) as unsigned);^
lightdb@test_m=#