lightdb支持cast(expr as unsigned)-mysql兼容

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中浮点型字段中存负数，转换时不会被截断）。具体见示例。
• 负数在溢出的时候会被截断为 9223372036854775808，这与mysql8.0相同（mysql5.7会截断为0）。具体见示例。
• 支持转换科学计数法格式的字符串到unsigned, 如：‘2e2’ 转为200(mysql 不支持，转换后为2)。具体见示例。

示例

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, Truncated
       unsigned       
----------------------
 18446744073709551615
(1 row)

lightdb@test_m=# select cast(-9223372036854775808 as unsigned) as unsigned;
      unsigned       
---------------------
 9223372036854775808
(1 row)

-- mysql5.7为 0 ，mysql8.0: 9223372036854775808
lightdb@test_m=# select cast(-9223372036854775809 as unsigned) as unsigned;
WARNING:  unsigned bigint out of range, Truncated
      unsigned       
---------------------
 9223372036854775808
(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=# select cast(2e2 as unsigned) as unsigned;
 unsigned 
----------
      200
(1 row)

-- mysql 为 2， 被截断
lightdb@test_m=# select cast('2e2' as unsigned) as unsigned;
 unsigned 
----------
      200
(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.6
 18446744073709551615 | -1.4
 18446744073709551614 | -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=# 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152