Skip to content

Commit e5866d3

Browse files
gerbenisgerbenis
committed
fix(snowflake): extend Snowflake's cast target types
1 parent 5060891 commit e5866d3

4 files changed

Lines changed: 113 additions & 6 deletions

File tree

‎pegjs/snowflake.pegjs‎

Lines changed: 28 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4485,7 +4485,10 @@ KW_TIME = "TIME"i !ident_start { return 'TIME'; }
44854485
KW_TIMESTAMP= "TIMESTAMP"i!ident_start { return 'TIMESTAMP'; }
44864486
KW_TIMESTAMP_TZ = "TIMESTAMP_TZ"i !ident_start { return 'TIMESTAMP_TZ'; }
44874487
KW_TIMESTAMP_NTZ = "TIMESTAMP_NTZ"i !ident_start { return 'TIMESTAMP_NTZ'; }
4488+
KW_TIMESTAMP_LTZ = "TIMESTAMP_LTZ"i !ident_start { return 'TIMESTAMP_LTZ'; }
44884489
KW_TRUNCATE = "TRUNCATE"i !ident_start { return 'TRUNCATE'; }
4490+
KW_VARIANT = "VARIANT"i !ident_start { return 'VARIANT'; }
4491+
KW_OBJECT = "OBJECT"i !ident_start { return 'OBJECT'; }
44894492
KW_USER = "USER"i !ident_start { return 'USER'; }
44904493
KW_UUID = "UUID"i !ident_start { return 'UUID'; }
44914494
KW_OID = "OID"i !ident_start { return 'OID'; }
@@ -4833,6 +4836,7 @@ mem_chain
48334836

48344837
data_type
48354838
= array_type
4839+
/ snowflake_array_type
48364840
/ character_string_type
48374841
/ numeric_type
48384842
/ datetime_type
@@ -4846,6 +4850,8 @@ data_type
48464850
/ binary_type
48474851
/ geography_type
48484852
/ oid_type
4853+
/ variant_type
4854+
/ object_type
48494855

48504856

48514857
array_type
@@ -4909,8 +4915,8 @@ time_type
49094915
/ t:(KW_TIME / KW_TIMESTAMP) __ tz:timezone? { /* => data_type */ return { dataType: t, suffix: tz }; }
49104916

49114917
datetime_type
4912-
= t:(KW_DATE / KW_DATETIME / KW_TIMESTAMP_TZ / KW_TIMESTAMP_NTZ) __ LPAREN __ l:[0-9]+ __ RPAREN { /* => data_type */ return { dataType: t, length: parseInt(l.join(''), 10), parentheses: true }; }
4913-
/ t:(KW_DATE / KW_DATETIME / KW_TIMESTAMP_TZ / KW_TIMESTAMP_NTZ) { /* => data_type */ return { dataType: t }; }
4918+
= t:(KW_DATE / KW_DATETIME / KW_TIMESTAMP_LTZ / KW_TIMESTAMP_TZ / KW_TIMESTAMP_NTZ) __ LPAREN __ l:[0-9]+ __ RPAREN { /* => data_type */ return { dataType: t, length: parseInt(l.join(''), 10), parentheses: true }; }
4919+
/ t:(KW_DATE / KW_DATETIME / KW_TIMESTAMP_LTZ / KW_TIMESTAMP_TZ / KW_TIMESTAMP_NTZ) { /* => data_type */ return { dataType: t }; }
49144920
/ time_type
49154921

49164922
enum_type
@@ -4941,3 +4947,23 @@ text_type
49414947

49424948
uuid_type
49434949
= t:KW_UUID {/* => data_type */ return { dataType: t }}
4950+
4951+
variant_type
4952+
= t:KW_VARIANT {/* => data_type */ return { dataType: t }}
4953+
4954+
object_type
4955+
= t:KW_OBJECT {/* => data_type */ return { dataType: t }}
4956+
4957+
snowflake_array_type
4958+
= t:KW_ARRAY __ LPAREN __ d:data_type __ RPAREN {
4959+
/* => data_type */
4960+
return {
4961+
dataType: t,
4962+
definition: [{ field_type: d }],
4963+
parentheses: true
4964+
}
4965+
}
4966+
/ t:KW_ARRAY {
4967+
/* => data_type */
4968+
return { dataType: t }
4969+
}

‎src/func.js‎

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
import { arrayIndexToSQL, columnOffsetToSQL } from './column'
22
import { collateToSQL } from './collate'
33
import { exprToSQL, orderOrPartitionByToSQL } from './expr'
4-
import { hasVal, identifierToSql, literalToSQL, toUpper } from './util'
4+
import { arrayStructTypeToSQL, hasVal, identifierToSql, literalToSQL, toUpper } from './util'
55
import { overToSQL } from './over'
66

77
function anyValueFuncToSQL(stmt) {
@@ -34,7 +34,23 @@ function castToSQL(expr) {
3434
const result = []
3535
for (let i = 0, len = targets.length; i < len; ++i) {
3636
const target = targets[i]
37-
const { angle_brackets: angleBrackets, length, dataType, parentheses, quoted, scale, suffix: dataTypeSuffix, expr: targetExpr } = target
37+
const { angle_brackets: angleBrackets, length, dataType, parentheses, quoted, scale, suffix: dataTypeSuffix, expr: targetExpr, definition } = target
38+
// Handle ARRAY(TYPE) and STRUCT types with definition
39+
if (definition) {
40+
const typeStr = arrayStructTypeToSQL(target)
41+
let symbolChar = '::'
42+
let suffix = ''
43+
const targetResult = []
44+
if (symbol === 'as') {
45+
if (i === 0) prefix = `${toUpper(keyword)}(${prefix}`
46+
suffix = ')'
47+
symbolChar = ` ${symbol.toUpperCase()} `
48+
}
49+
if (i === 0) targetResult.push(prefix)
50+
targetResult.push(symbolChar, quoted, typeStr, quoted, suffix)
51+
result.push(targetResult.filter(hasVal).join(''))
52+
continue
53+
}
3854
let str = targetExpr ? exprToSQL(targetExpr) : ''
3955
if (length != null) str = scale ? `${length}, ${scale}` : length
4056
if (parentheses) str = `(${str})`

‎src/util.js‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -303,7 +303,7 @@ function dataTypeToSQL(expr) {
303303

304304
function arrayStructTypeToSQL(expr) {
305305
if (!expr) return
306-
const { dataType, definition, anglebracket } = expr
306+
const { dataType, definition, anglebracket, parentheses } = expr
307307
const dataTypeUpper = toUpper(dataType)
308308
const isNotArrayOrStruct = dataTypeUpper !== 'ARRAY' && dataTypeUpper !== 'STRUCT'
309309
if (isNotArrayOrStruct) return dataTypeUpper
@@ -314,7 +314,9 @@ function arrayStructTypeToSQL(expr) {
314314
const fieldResult = [fieldName, arrayStructTypeToSQL(fieldType)]
315315
return fieldResult.filter(hasVal).join(' ')
316316
}).join(', ')
317-
return anglebracket ? `${dataTypeUpper}<${result}>` : `${dataTypeUpper} ${result}`
317+
if (anglebracket) return `${dataTypeUpper}<${result}>`
318+
if (parentheses) return `${dataTypeUpper}(${result})`
319+
return `${dataTypeUpper} ${result}`
318320
}
319321

320322
function commentToSQL(comment) {

‎test/snowflake.spec.js‎

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -126,6 +126,69 @@ describe('snowflake', () => {
126126
`SELECT SHA512(TRY_CAST(CONCAT("a", "b", "c") AS VARCHAR)) AS "Hashed" FROM v`,
127127
],
128128
},
129+
{
130+
title: 'cast to VARIANT type',
131+
sql: [
132+
`SELECT col::VARIANT AS variant_col FROM t`,
133+
`SELECT "col"::VARIANT AS "variant_col" FROM t`,
134+
],
135+
},
136+
{
137+
title: 'cast to OBJECT type',
138+
sql: [
139+
`SELECT col::OBJECT AS object_col FROM t`,
140+
`SELECT "col"::OBJECT AS "object_col" FROM t`,
141+
],
142+
},
143+
{
144+
title: 'cast to ARRAY type (untyped)',
145+
sql: [
146+
`SELECT col::ARRAY AS array_col FROM t`,
147+
`SELECT "col"::ARRAY AS "array_col" FROM t`,
148+
],
149+
},
150+
{
151+
title: 'cast to ARRAY(TYPE) type (typed)',
152+
sql: [
153+
`SELECT col::ARRAY(VARCHAR) AS typed_array_col FROM t`,
154+
`SELECT "col"::ARRAY(VARCHAR) AS "typed_array_col" FROM t`,
155+
],
156+
},
157+
{
158+
title: 'cast to ARRAY(NUMBER) type',
159+
sql: [
160+
`SELECT col::ARRAY(NUMBER) AS num_array FROM t`,
161+
`SELECT "col"::ARRAY(NUMBER) AS "num_array" FROM t`,
162+
],
163+
},
164+
{
165+
title: 'cast to TIMESTAMP_LTZ type',
166+
sql: [
167+
`SELECT col::TIMESTAMP_LTZ AS ts_ltz FROM t`,
168+
`SELECT "col"::TIMESTAMP_LTZ AS "ts_ltz" FROM t`,
169+
],
170+
},
171+
{
172+
title: 'CAST AS VARIANT',
173+
sql: [
174+
`SELECT CAST(col AS VARIANT) AS variant_col FROM t`,
175+
`SELECT CAST("col" AS VARIANT) AS "variant_col" FROM t`,
176+
],
177+
},
178+
{
179+
title: 'CAST AS ARRAY',
180+
sql: [
181+
`SELECT CAST(col AS ARRAY) AS array_col FROM t`,
182+
`SELECT CAST("col" AS ARRAY) AS "array_col" FROM t`,
183+
],
184+
},
185+
{
186+
title: 'CAST AS ARRAY(INTEGER)',
187+
sql: [
188+
`SELECT CAST(col AS ARRAY(INTEGER)) AS int_array_col FROM t`,
189+
`SELECT CAST("col" AS ARRAY(INTEGER)) AS "int_array_col" FROM t`,
190+
],
191+
},
129192
{
130193
title: 'regexp operator',
131194
sql: [

0 commit comments

Comments
 (0)