CREATE OR REPLACE TEMPORARY TABLE TEST (ID BIGINT, COL VARCHAR);
INSERT INTO TEST (ID, COL) VALUES (1, 's1,s2,s3'), (2, 's1,s2');
SELECT
ID
, F.VALUE::varchar as V
FROM TEST AS T
, LATERAL FLATTEN(input => SPLIT(T.COL, ',')) AS F;
ID
V
1
s1
1
s2
1
s3
2
s1
2
s2
MCVE in Fakesnow:
import fakesnow
import snowflake.connector
def main():
with fakesnow.patch():
conn = snowflake.connector.connect(database="X", schema="Y")
conn.execute_string("CREATE OR REPLACE TEMPORARY TABLE TEST (ID BIGINT, COL VARCHAR)")
conn.execute_string("INSERT INTO TEST (ID, COL) VALUES (1, 's1,s2,s3'), (2, 's1,s2')")
conn.cursor().execute("""
SELECT
ID
, F.VALUE::varchar as V
FROM TEST AS T
, LATERAL FLATTEN(input => SPLIT(T.COL, ',')) AS F
""").fetchall()
if __name__ == "__main__":
main()
Error:
duckdb.duckdb.ConversionException: Conversion Error: Malformed JSON at byte 0 of input: unexpected character. Input: s1
CREATE OR REPLACE TEMPORARY TABLE TEST (ID BIGINT, COL TEXT);
INSERT INTO TEST (ID, COL) VALUES (1, 's1,s2,s3'), (2, 's1,s2');
SELECT ID, CAST(F.VALUE AS TEXT) AS V FROM TEST AS T, LATERAL UNNEST(CAST(STR_SPLIT(T.COL, ',') AS JSON[])) AS F(VALUE);
MCVE in Snowflake:
MCVE in Fakesnow:
Error:
Version:
FAKESNOW_DEBUG=1: