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
153
154
155
156
157
158
159
160
161
|
from tests.dialects.test_dialect import Validator
class TestTrino(Validator):
dialect = "trino"
def test_trino(self):
self.validate_identity("REFRESH MATERIALIZED VIEW mynamespace.test_view")
self.validate_identity("JSON_QUERY(m.properties, 'lax $.area' OMIT QUOTES NULL ON ERROR)")
self.validate_identity("JSON_EXTRACT(content, json_path)")
self.validate_identity("JSON_QUERY(content, 'lax $.HY.*')")
self.validate_identity("JSON_QUERY(content, 'strict $.HY.*' WITH WRAPPER)")
self.validate_identity("JSON_QUERY(content, 'strict $.HY.*' WITH ARRAY WRAPPER)")
self.validate_identity("JSON_QUERY(content, 'strict $.HY.*' WITH UNCONDITIONAL WRAPPER)")
self.validate_identity("JSON_QUERY(content, 'strict $.HY.*' WITHOUT CONDITIONAL WRAPPER)")
self.validate_identity("JSON_QUERY(description, 'strict $.comment' KEEP QUOTES)")
self.validate_identity(
"JSON_QUERY(description, 'strict $.comment' OMIT QUOTES ON SCALAR STRING)"
)
self.validate_identity(
"JSON_QUERY(content, 'strict $.HY.*' WITH UNCONDITIONAL WRAPPER KEEP QUOTES)"
)
self.validate_identity(
"SELECT TIMESTAMP '2012-10-31 01:00 -2'",
"SELECT CAST('2012-10-31 01:00 -2' AS TIMESTAMP WITH TIME ZONE)",
)
self.validate_identity(
"SELECT TIMESTAMP '2012-10-31 01:00 +2'",
"SELECT CAST('2012-10-31 01:00 +2' AS TIMESTAMP WITH TIME ZONE)",
)
self.validate_all(
"SELECT TIMESTAMP '2012-10-31 01:00:00 +02:00'",
write={
"duckdb": "SELECT CAST('2012-10-31 01:00:00 +02:00' AS TIMESTAMPTZ)",
"trino": "SELECT CAST('2012-10-31 01:00:00 +02:00' AS TIMESTAMP WITH TIME ZONE)",
},
)
self.validate_all(
"SELECT FORMAT('%s', 123)",
write={
"duckdb": "SELECT FORMAT('{}', 123)",
"snowflake": "SELECT TO_CHAR(123)",
"trino": "SELECT FORMAT('%s', 123)",
},
)
self.validate_identity(
"SELECT * FROM tbl MATCH_RECOGNIZE (PARTITION BY id ORDER BY col MEASURES FIRST(col, 2) AS col1, LAST(col, 2) AS col2 PATTERN (B* A) DEFINE A AS col = 1)"
)
def test_listagg(self):
self.validate_identity(
"SELECT LISTAGG(DISTINCT col, ',') WITHIN GROUP (ORDER BY col ASC) FROM tbl"
)
self.validate_identity(
"SELECT LISTAGG(col, '; ' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY col ASC) FROM tbl"
)
self.validate_identity(
"SELECT LISTAGG(col, '; ' ON OVERFLOW TRUNCATE WITH COUNT) WITHIN GROUP (ORDER BY col ASC) FROM tbl"
)
self.validate_identity(
"SELECT LISTAGG(col, '; ' ON OVERFLOW TRUNCATE WITHOUT COUNT) WITHIN GROUP (ORDER BY col ASC) FROM tbl"
)
self.validate_identity(
"SELECT LISTAGG(col, '; ' ON OVERFLOW TRUNCATE '...' WITH COUNT) WITHIN GROUP (ORDER BY col ASC) FROM tbl"
)
self.validate_identity(
"SELECT LISTAGG(col, '; ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP (ORDER BY col ASC) FROM tbl"
)
self.validate_identity(
"SELECT LISTAGG(col) WITHIN GROUP (ORDER BY col DESC) FROM tbl",
"SELECT LISTAGG(col, ',') WITHIN GROUP (ORDER BY col DESC) FROM tbl",
)
def test_trim(self):
self.validate_identity("SELECT TRIM('!' FROM '!foo!')")
self.validate_identity("SELECT TRIM(BOTH '$' FROM '$var$')")
self.validate_identity("SELECT TRIM(TRAILING 'ER' FROM UPPER('worker'))")
self.validate_identity(
"SELECT TRIM(LEADING FROM ' abcd')",
"SELECT LTRIM(' abcd')",
)
self.validate_identity(
"SELECT TRIM('!foo!', '!')",
"SELECT TRIM('!' FROM '!foo!')",
)
def test_ddl(self):
self.validate_identity("ALTER TABLE users RENAME TO people")
self.validate_identity("ALTER TABLE IF EXISTS users RENAME TO people")
self.validate_identity("ALTER TABLE users ADD COLUMN zip VARCHAR")
self.validate_identity("ALTER TABLE IF EXISTS users ADD COLUMN IF NOT EXISTS zip VARCHAR")
self.validate_identity("ALTER TABLE users DROP COLUMN zip")
self.validate_identity("ALTER TABLE IF EXISTS users DROP COLUMN IF EXISTS zip")
self.validate_identity("ALTER TABLE users RENAME COLUMN id TO user_id")
self.validate_identity("ALTER TABLE IF EXISTS users RENAME COLUMN IF EXISTS id TO user_id")
self.validate_identity("ALTER TABLE users ALTER COLUMN id SET DATA TYPE BIGINT")
self.validate_identity("ALTER TABLE users ALTER COLUMN id DROP NOT NULL")
self.validate_identity(
"ALTER TABLE people SET AUTHORIZATION alice", check_command_warning=True
)
self.validate_identity(
"ALTER TABLE people SET AUTHORIZATION ROLE PUBLIC", check_command_warning=True
)
self.validate_identity(
"ALTER TABLE people SET PROPERTIES x = 'y'", check_command_warning=True
)
self.validate_identity(
"ALTER TABLE people SET PROPERTIES foo = 123, 'foo bar' = 456",
check_command_warning=True,
)
self.validate_identity(
"ALTER TABLE people SET PROPERTIES x = DEFAULT", check_command_warning=True
)
self.validate_identity("ALTER VIEW people RENAME TO users")
self.validate_identity(
"ALTER VIEW people SET AUTHORIZATION alice", check_command_warning=True
)
self.validate_identity("CREATE SCHEMA foo WITH (LOCATION='s3://bucket/foo')")
self.validate_identity(
"CREATE TABLE foo.bar WITH (LOCATION='s3://bucket/foo/bar') AS SELECT 1"
)
# Hive connector syntax (partitioned_by)
self.validate_identity(
"CREATE TABLE foo (a VARCHAR, b INTEGER, c DATE) WITH (PARTITIONED_BY=ARRAY['a', 'b'])"
)
self.validate_identity(
'CREATE TABLE "foo" ("a" VARCHAR, "b" INTEGER, "c" DATE) WITH (PARTITIONED_BY=ARRAY[\'a\', \'b\'])',
identify=True,
)
# Iceberg connector syntax (partitioning, can contain Iceberg transform expressions)
self.validate_identity(
"CREATE TABLE foo (a VARCHAR, b INTEGER, c DATE) WITH (PARTITIONING=ARRAY['a', 'bucket(4, b)', 'month(c)'])",
)
self.validate_identity(
'CREATE TABLE "foo" ("a" VARCHAR, "b" INTEGER, "c" DATE) WITH (PARTITIONING=ARRAY[\'a\', \'bucket(4, b)\', \'month(c)\'])',
identify=True,
)
def test_analyze(self):
self.validate_identity("ANALYZE tbl")
self.validate_identity("ANALYZE tbl WITH (prop1=val1, prop2=val2)")
def test_json_value(self):
self.validate_identity(
"JSON_VALUE(jl.extra_attributes, 'lax $.amount_source' RETURNING VARCHAR)"
)
json_doc = """'{"item": "shoes", "price": "49.95"}'"""
self.validate_identity(f"""SELECT JSON_VALUE({json_doc}, 'strict $.price')""")
self.validate_identity(
f"""SELECT JSON_VALUE({json_doc}, 'lax $.price' RETURNING DECIMAL(4, 2))"""
)
for on_option in ("NULL", "ERROR", "DEFAULT 1"):
self.validate_identity(
f"""SELECT JSON_VALUE({json_doc}, 'lax $.price' RETURNING DECIMAL(4, 2) {on_option} ON EMPTY {on_option} ON ERROR) AS price"""
)
|
