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
|
from tests.dialects.test_dialect import Validator
class TestDoris(Validator):
dialect = "doris"
def test_doris(self):
self.validate_all(
"SELECT TO_DATE('2020-02-02 00:00:00')",
write={
"doris": "SELECT TO_DATE('2020-02-02 00:00:00')",
"oracle": "SELECT CAST('2020-02-02 00:00:00' AS DATE)",
},
)
self.validate_all(
"SELECT MAX_BY(a, b), MIN_BY(c, d)",
read={
"clickhouse": "SELECT argMax(a, b), argMin(c, d)",
},
)
self.validate_all(
"SELECT ARRAY_SUM(x -> x * x, ARRAY(2, 3))",
read={
"clickhouse": "SELECT arraySum(x -> x*x, [2, 3])",
},
write={
"clickhouse": "SELECT arraySum(x -> x * x, [2, 3])",
"doris": "SELECT ARRAY_SUM(x -> x * x, ARRAY(2, 3))",
},
)
self.validate_all(
"MONTHS_ADD(d, n)",
read={
"oracle": "ADD_MONTHS(d, n)",
},
write={
"doris": "MONTHS_ADD(d, n)",
"oracle": "ADD_MONTHS(d, n)",
},
)
self.validate_all(
"""SELECT JSON_EXTRACT(CAST('{"key": 1}' AS JSONB), '$.key')""",
read={
"postgres": """SELECT '{"key": 1}'::jsonb ->> 'key'""",
},
write={
"doris": """SELECT JSON_EXTRACT(CAST('{"key": 1}' AS JSONB), '$.key')""",
"postgres": """SELECT JSON_EXTRACT_PATH(CAST('{"key": 1}' AS JSONB), 'key')""",
},
)
self.validate_all(
"SELECT GROUP_CONCAT('aa', ',')",
read={
"doris": "SELECT GROUP_CONCAT('aa', ',')",
"mysql": "SELECT GROUP_CONCAT('aa' SEPARATOR ',')",
"postgres": "SELECT STRING_AGG('aa', ',')",
},
)
self.validate_all(
"SELECT LAG(1, 1, NULL) OVER (ORDER BY 1)",
read={
"doris": "SELECT LAG(1, 1, NULL) OVER (ORDER BY 1)",
"postgres": "SELECT LAG(1) OVER (ORDER BY 1)",
},
)
self.validate_all(
"SELECT LAG(1, 2, NULL) OVER (ORDER BY 1)",
read={
"doris": "SELECT LAG(1, 2, NULL) OVER (ORDER BY 1)",
"postgres": "SELECT LAG(1, 2) OVER (ORDER BY 1)",
},
)
self.validate_all(
"SELECT LEAD(1, 1, NULL) OVER (ORDER BY 1)",
read={
"doris": "SELECT LEAD(1, 1, NULL) OVER (ORDER BY 1)",
"postgres": "SELECT LEAD(1) OVER (ORDER BY 1)",
},
)
self.validate_all(
"SELECT LEAD(1, 2, NULL) OVER (ORDER BY 1)",
read={
"doris": "SELECT LEAD(1, 2, NULL) OVER (ORDER BY 1)",
"postgres": "SELECT LEAD(1, 2) OVER (ORDER BY 1)",
},
)
def test_identity(self):
self.validate_identity("COALECSE(a, b, c, d)")
self.validate_identity("SELECT CAST(`a`.`b` AS INT) FROM foo")
self.validate_identity("SELECT APPROX_COUNT_DISTINCT(a) FROM x")
def test_time(self):
self.validate_identity("TIMESTAMP('2022-01-01')")
def test_regex(self):
self.validate_all(
"SELECT REGEXP_LIKE(abc, '%foo%')",
write={
"doris": "SELECT REGEXP(abc, '%foo%')",
},
)
|
