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
|
from tests.dialects.test_dialect import Validator
class TestTSQL(Validator):
dialect = "tsql"
def test_tsql(self):
self.validate_identity('SELECT "x"."y" FROM foo')
self.validate_identity(
"SELECT DISTINCT DepartmentName, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY BaseRate) OVER (PARTITION BY DepartmentName) AS MedianCont FROM dbo.DimEmployee"
)
self.validate_all(
"SELECT CAST([a].[b] AS SMALLINT) FROM foo",
write={
"tsql": 'SELECT CAST("a"."b" AS SMALLINT) FROM foo',
"spark": "SELECT CAST(`a`.`b` AS SHORT) FROM foo",
},
)
self.validate_all(
"CONVERT(INT, CONVERT(NUMERIC, '444.75'))",
write={
"mysql": "CAST(CAST('444.75' AS DECIMAL) AS INT)",
"tsql": "CAST(CAST('444.75' AS NUMERIC) AS INTEGER)",
},
)
def test_types(self):
self.validate_identity("CAST(x AS XML)")
self.validate_identity("CAST(x AS UNIQUEIDENTIFIER)")
self.validate_identity("CAST(x AS MONEY)")
self.validate_identity("CAST(x AS SMALLMONEY)")
self.validate_identity("CAST(x AS ROWVERSION)")
self.validate_identity("CAST(x AS IMAGE)")
self.validate_identity("CAST(x AS SQL_VARIANT)")
self.validate_identity("CAST(x AS BIT)")
self.validate_all(
"CAST(x AS DATETIME2)",
read={
"": "CAST(x AS DATETIME)",
},
write={
"mysql": "CAST(x AS DATETIME)",
"tsql": "CAST(x AS DATETIME2)",
},
)
def test_charindex(self):
self.validate_all(
"CHARINDEX(x, y, 9)",
write={
"spark": "LOCATE(x, y, 9)",
},
)
self.validate_all(
"CHARINDEX(x, y)",
write={
"spark": "LOCATE(x, y)",
},
)
self.validate_all(
"CHARINDEX('sub', 'testsubstring', 3)",
write={
"spark": "LOCATE('sub', 'testsubstring', 3)",
},
)
self.validate_all(
"CHARINDEX('sub', 'testsubstring')",
write={
"spark": "LOCATE('sub', 'testsubstring')",
},
)
|
