[numeric_operators] athena = """ WITH values0 AS (SELECT * FROM (VALUES (1, 2), (3, 4), (6, 6), (9, 8), (12, 10)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", ("a" + "b") AS "add", ("a" - "b") AS "sub", ("a" * "b") AS "mul", ("a" / 2) AS "div", ("a" % 4) AS "mod", ("a" = "b") AS "eq", ("a" <> "b") AS "neq", ("a" > 5) AS "gt", ("a" >= 5) AS "gte", ("b" < 6) AS "lt", ("b" <= 6) AS "lte", (-"a") AS "neg" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ bigquery = """ WITH values0 AS (SELECT 1 AS `a`, 2 AS `b` UNION ALL SELECT 3 AS `a`, 4 AS `b` UNION ALL SELECT 6 AS `a`, 6 AS `b` UNION ALL SELECT 9 AS `a`, 8 AS `b` UNION ALL SELECT 12 AS `a`, 10 AS `b`), values1 AS (SELECT `a`, `b`, (`a` + `b`) AS `add`, (`a` - `b`) AS `sub`, (`a` * `b`) AS `mul`, (`a` / 2) AS `div`, MOD(`a`, 4) AS `mod`, (`a` = `b`) AS `eq`, (`a` <> `b`) AS `neq`, (`a` > 5) AS `gt`, (`a` >= 5) AS `gte`, (`b` < 6) AS `lt`, (`b` <= 6) AS `lte`, (-`a`) AS `neg` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ clickhouse = """ WITH values0 AS (SELECT 1 AS "a", 2 AS "b" UNION ALL SELECT 3 AS "a", 4 AS "b" UNION ALL SELECT 6 AS "a", 6 AS "b" UNION ALL SELECT 9 AS "a", 8 AS "b" UNION ALL SELECT 12 AS "a", 10 AS "b"), values1 AS (SELECT "a", "b", ("a" + "b") AS "add", ("a" - "b") AS "sub", ("a" * "b") AS "mul", ("a" / 2) AS "div", ("a" % 4) AS "mod", ("a" = "b") AS "eq", ("a" <> "b") AS "neq", ("a" > 5) AS "gt", ("a" >= 5) AS "gte", ("b" < 6) AS "lt", ("b" <= 6) AS "lte", (-"a") AS "neg" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ databricks = """ WITH values0 AS (SELECT * FROM (VALUES (1, 2), (3, 4), (6, 6), (9, 8), (12, 10)) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, (`a` + `b`) AS `add`, (`a` - `b`) AS `sub`, (`a` * `b`) AS `mul`, (`a` / 2) AS `div`, (`a` % 4) AS `mod`, (`a` = `b`) AS `eq`, (`a` <> `b`) AS `neq`, (`a` > 5) AS `gt`, (`a` >= 5) AS `gte`, (`b` < 6) AS `lt`, (`b` <= 6) AS `lte`, (-`a`) AS `neg` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (1, 2), (3, 4), (6, 6), (9, 8), (12, 10)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", ("a" + "b") AS "add", ("a" - "b") AS "sub", ("a" * "b") AS "mul", ("a" / 2) AS "div", ("a" % 4) AS "mod", ("a" = "b") AS "eq", ("a" <> "b") AS "neq", ("a" > 5) AS "gt", ("a" >= 5) AS "gte", ("b" < 6) AS "lt", ("b" <= 6) AS "lte", (-"a") AS "neg" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (1, 2), (3, 4), (6, 6), (9, 8), (12, 10)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", ("a" + "b") AS "add", ("a" - "b") AS "sub", ("a" * "b") AS "mul", ("a" / 2) AS "div", ("a" % 4) AS "mod", ("a" = "b") AS "eq", ("a" <> "b") AS "neq", ("a" > 5) AS "gt", ("a" >= 5) AS "gte", ("b" < 6) AS "lt", ("b" <= 6) AS "lte", (-"a") AS "neg" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ mysql = """ WITH values0 AS (SELECT * FROM (VALUES ROW(1, 2), ROW(3, 4), ROW(6, 6), ROW(9, 8), ROW(12, 10)) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, (`a` + `b`) AS `add`, (`a` - `b`) AS `sub`, (`a` * `b`) AS `mul`, (`a` / 2) AS `div`, (`a` % 4) AS `mod`, (`a` = `b`) AS `eq`, (`a` <> `b`) AS `neq`, (`a` > 5) AS `gt`, (`a` >= 5) AS `gte`, (`b` < 6) AS `lt`, (`b` <= 6) AS `lte`, (-`a`) AS `neg` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC """ postgres = """ WITH values0 AS (SELECT * FROM (VALUES (1, 2), (3, 4), (6, 6), (9, 8), (12, 10)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", ("a" + "b") AS "add", ("a" - "b") AS "sub", ("a" * "b") AS "mul", ("a" / 2) AS "div", ("a" % 4) AS "mod", ("a" = "b") AS "eq", ("a" <> "b") AS "neq", ("a" > 5) AS "gt", ("a" >= 5) AS "gte", ("b" < 6) AS "lt", ("b" <= 6) AS "lte", (-"a") AS "neg" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ redshift = """ WITH values0 AS (SELECT 1 AS "a", 2 AS "b" UNION ALL SELECT 3 AS "a", 4 AS "b" UNION ALL SELECT 6 AS "a", 6 AS "b" UNION ALL SELECT 9 AS "a", 8 AS "b" UNION ALL SELECT 12 AS "a", 10 AS "b"), values1 AS (SELECT "a", "b", ("a" + "b") AS "add", ("a" - "b") AS "sub", ("a" * "b") AS "mul", ("a" / 2) AS "div", ("a" % 4) AS "mod", ("a" = "b") AS "eq", ("a" <> "b") AS "neq", ("a" > 5) AS "gt", ("a" >= 5) AS "gte", ("b" < 6) AS "lt", ("b" <= 6) AS "lte", (-"a") AS "neg" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b" FROM (VALUES (1, 2), (3, 4), (6, 6), (9, 8), (12, 10))), values1 AS (SELECT "a", "b", ("a" + "b") AS "add", ("a" - "b") AS "sub", ("a" * "b") AS "mul", ("a" / 2) AS "div", MOD("a", 4) AS "mod", ("a" = "b") AS "eq", ("a" <> "b") AS "neq", ("a" > 5) AS "gt", ("a" >= 5) AS "gte", ("b" < 6) AS "lt", ("b" <= 6) AS "lte", (-"a") AS "neg" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ result = ''' +----+----+-----+-----+-----+-----+-----+-------+-------+-------+-------+-------+-------+-----+ | a | b | add | sub | mul | div | mod | eq | neq | gt | gte | lt | lte | neg | +----+----+-----+-----+-----+-----+-----+-------+-------+-------+-------+-------+-------+-----+ | 1 | 2 | 3 | -1 | 2 | 0 | 1 | false | true | false | false | true | true | -1 | | 3 | 4 | 7 | -1 | 12 | 1 | 3 | false | true | false | false | true | true | -3 | | 6 | 6 | 12 | 0 | 36 | 3 | 2 | true | false | true | true | false | true | -6 | | 9 | 8 | 17 | 1 | 72 | 4 | 1 | false | true | true | true | false | false | -9 | | 12 | 10 | 22 | 2 | 120 | 6 | 0 | false | true | true | true | false | false | -12 | +----+----+-----+-----+-----+-----+-----+-------+-------+-------+-------+-------+-------+-----+ ''' [logical_operators] athena = """ WITH values0 AS (SELECT * FROM (VALUES (1, true, true), (2, true, false), (3, false, true), (4, false, false), (5, false, true)) AS "_values" ("i", "a", "b")), values1 AS (SELECT "i", "a", "b", ("a" OR "b") AS "or", ("a" OR true) AS "or2", ("a" AND "b") AS "and", ("a" AND true) AS "and2", (NOT "a") AS "not", ("a" = "b") AS "eq", ("a" <> "b") AS "neq" FROM values0) SELECT * FROM values1 ORDER BY "i" ASC NULLS FIRST """ bigquery = """ WITH values0 AS (SELECT 1 AS `i`, true AS `a`, true AS `b` UNION ALL SELECT 2 AS `i`, true AS `a`, false AS `b` UNION ALL SELECT 3 AS `i`, false AS `a`, true AS `b` UNION ALL SELECT 4 AS `i`, false AS `a`, false AS `b` UNION ALL SELECT 5 AS `i`, false AS `a`, true AS `b`), values1 AS (SELECT `i`, `a`, `b`, (`a` OR `b`) AS `or`, (`a` OR true) AS `or2`, (`a` AND `b`) AS `and`, (`a` AND true) AS `and2`, (NOT `a`) AS `not`, (`a` = `b`) AS `eq`, (`a` <> `b`) AS `neq` FROM values0) SELECT * FROM values1 ORDER BY `i` ASC NULLS FIRST """ clickhouse = """ WITH values0 AS (SELECT 1 AS "i", true AS "a", true AS "b" UNION ALL SELECT 2 AS "i", true AS "a", false AS "b" UNION ALL SELECT 3 AS "i", false AS "a", true AS "b" UNION ALL SELECT 4 AS "i", false AS "a", false AS "b" UNION ALL SELECT 5 AS "i", false AS "a", true AS "b"), values1 AS (SELECT "i", "a", "b", ("a" OR "b") AS "or", ("a" OR true) AS "or2", ("a" AND "b") AS "and", ("a" AND true) AS "and2", (NOT "a") AS "not", ("a" = "b") AS "eq", ("a" <> "b") AS "neq" FROM values0) SELECT * FROM values1 ORDER BY "i" ASC NULLS FIRST """ databricks = """ WITH values0 AS (SELECT * FROM (VALUES (1, true, true), (2, true, false), (3, false, true), (4, false, false), (5, false, true)) AS `_values` (`i`, `a`, `b`)), values1 AS (SELECT `i`, `a`, `b`, (`a` OR `b`) AS `or`, (`a` OR true) AS `or2`, (`a` AND `b`) AS `and`, (`a` AND true) AS `and2`, (NOT `a`) AS `not`, (`a` = `b`) AS `eq`, (`a` <> `b`) AS `neq` FROM values0) SELECT * FROM values1 ORDER BY `i` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (1, true, true), (2, true, false), (3, false, true), (4, false, false), (5, false, true)) AS "_values" ("i", "a", "b")), values1 AS (SELECT "i", "a", "b", ("a" OR "b") AS "or", ("a" OR true) AS "or2", ("a" AND "b") AS "and", ("a" AND true) AS "and2", (NOT "a") AS "not", ("a" = "b") AS "eq", ("a" <> "b") AS "neq" FROM values0) SELECT * FROM values1 ORDER BY "i" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (1, true, true), (2, true, false), (3, false, true), (4, false, false), (5, false, true)) AS "_values" ("i", "a", "b")), values1 AS (SELECT "i", "a", "b", ("a" OR "b") AS "or", ("a" OR true) AS "or2", ("a" AND "b") AS "and", ("a" AND true) AS "and2", (NOT "a") AS "not", ("a" = "b") AS "eq", ("a" <> "b") AS "neq" FROM values0) SELECT * FROM values1 ORDER BY "i" ASC NULLS FIRST """ mysql = """ WITH values0 AS (SELECT * FROM (VALUES ROW(1, true, true), ROW(2, true, false), ROW(3, false, true), ROW(4, false, false), ROW(5, false, true)) AS `_values` (`i`, `a`, `b`)), values1 AS (SELECT `i`, `a`, `b`, (`a` OR `b`) AS `or`, (`a` OR true) AS `or2`, (`a` AND `b`) AS `and`, (`a` AND true) AS `and2`, (NOT `a`) AS `not`, (`a` = `b`) AS `eq`, (`a` <> `b`) AS `neq` FROM values0) SELECT * FROM values1 ORDER BY `i` ASC """ postgres = """ WITH values0 AS (SELECT * FROM (VALUES (1, true, true), (2, true, false), (3, false, true), (4, false, false), (5, false, true)) AS "_values" ("i", "a", "b")), values1 AS (SELECT "i", "a", "b", ("a" OR "b") AS "or", ("a" OR true) AS "or2", ("a" AND "b") AS "and", ("a" AND true) AS "and2", (NOT "a") AS "not", ("a" = "b") AS "eq", ("a" <> "b") AS "neq" FROM values0) SELECT * FROM values1 ORDER BY "i" ASC NULLS FIRST """ redshift = """ WITH values0 AS (SELECT 1 AS "i", true AS "a", true AS "b" UNION ALL SELECT 2 AS "i", true AS "a", false AS "b" UNION ALL SELECT 3 AS "i", false AS "a", true AS "b" UNION ALL SELECT 4 AS "i", false AS "a", false AS "b" UNION ALL SELECT 5 AS "i", false AS "a", true AS "b"), values1 AS (SELECT "i", "a", "b", ("a" OR "b") AS "or", ("a" OR true) AS "or2", ("a" AND "b") AS "and", ("a" AND true) AS "and2", (NOT "a") AS "not", ("a" = "b") AS "eq", ("a" <> "b") AS "neq" FROM values0) SELECT * FROM values1 ORDER BY "i" ASC NULLS FIRST """ snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "i", "COLUMN2" AS "a", "COLUMN3" AS "b" FROM (VALUES (1, true, true), (2, true, false), (3, false, true), (4, false, false), (5, false, true))), values1 AS (SELECT "i", "a", "b", ("a" OR "b") AS "or", ("a" OR true) AS "or2", ("a" AND "b") AS "and", ("a" AND true) AS "and2", (NOT "a") AS "not", ("a" = "b") AS "eq", ("a" <> "b") AS "neq" FROM values0) SELECT * FROM values1 ORDER BY "i" ASC NULLS FIRST """ result = ''' +---+-------+-------+-------+------+-------+-------+-------+-------+-------+ | i | a | b | or | or2 | and | and2 | not | eq | neq | +---+-------+-------+-------+------+-------+-------+-------+-------+-------+ | 1 | true | true | true | true | true | true | false | true | false | | 2 | true | false | true | true | false | true | false | false | true | | 3 | false | true | true | true | false | false | true | false | true | | 4 | false | false | false | true | false | false | true | true | false | | 5 | false | true | true | true | false | false | true | false | true | +---+-------+-------+-------+------+-------+-------+-------+-------+-------+ ''' [between] athena = """ WITH values0 AS (SELECT * FROM (VALUES (1, 2), (3, 4), (6, 6), (9, 8), (12, 10)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", "a" BETWEEN 0 AND "b" AS "bet1", "a" NOT BETWEEN 0 AND "b" AS "nbet1" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ bigquery = """ WITH values0 AS (SELECT 1 AS `a`, 2 AS `b` UNION ALL SELECT 3 AS `a`, 4 AS `b` UNION ALL SELECT 6 AS `a`, 6 AS `b` UNION ALL SELECT 9 AS `a`, 8 AS `b` UNION ALL SELECT 12 AS `a`, 10 AS `b`), values1 AS (SELECT `a`, `b`, `a` BETWEEN 0 AND `b` AS `bet1`, `a` NOT BETWEEN 0 AND `b` AS `nbet1` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ clickhouse = """ WITH values0 AS (SELECT 1 AS "a", 2 AS "b" UNION ALL SELECT 3 AS "a", 4 AS "b" UNION ALL SELECT 6 AS "a", 6 AS "b" UNION ALL SELECT 9 AS "a", 8 AS "b" UNION ALL SELECT 12 AS "a", 10 AS "b"), values1 AS (SELECT "a", "b", "a" BETWEEN 0 AND "b" AS "bet1", "a" NOT BETWEEN 0 AND "b" AS "nbet1" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ databricks = """ WITH values0 AS (SELECT * FROM (VALUES (1, 2), (3, 4), (6, 6), (9, 8), (12, 10)) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, `a` BETWEEN 0 AND `b` AS `bet1`, `a` NOT BETWEEN 0 AND `b` AS `nbet1` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (1, 2), (3, 4), (6, 6), (9, 8), (12, 10)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", "a" BETWEEN 0 AND "b" AS "bet1", "a" NOT BETWEEN 0 AND "b" AS "nbet1" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (1, 2), (3, 4), (6, 6), (9, 8), (12, 10)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", "a" BETWEEN 0 AND "b" AS "bet1", "a" NOT BETWEEN 0 AND "b" AS "nbet1" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ mysql = """ WITH values0 AS (SELECT * FROM (VALUES ROW(1, 2), ROW(3, 4), ROW(6, 6), ROW(9, 8), ROW(12, 10)) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, `a` BETWEEN 0 AND `b` AS `bet1`, `a` NOT BETWEEN 0 AND `b` AS `nbet1` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC """ postgres = """ WITH values0 AS (SELECT * FROM (VALUES (1, 2), (3, 4), (6, 6), (9, 8), (12, 10)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", "a" BETWEEN 0 AND "b" AS "bet1", "a" NOT BETWEEN 0 AND "b" AS "nbet1" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ redshift = """ WITH values0 AS (SELECT 1 AS "a", 2 AS "b" UNION ALL SELECT 3 AS "a", 4 AS "b" UNION ALL SELECT 6 AS "a", 6 AS "b" UNION ALL SELECT 9 AS "a", 8 AS "b" UNION ALL SELECT 12 AS "a", 10 AS "b"), values1 AS (SELECT "a", "b", "a" BETWEEN 0 AND "b" AS "bet1", "a" NOT BETWEEN 0 AND "b" AS "nbet1" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b" FROM (VALUES (1, 2), (3, 4), (6, 6), (9, 8), (12, 10))), values1 AS (SELECT "a", "b", "a" BETWEEN 0 AND "b" AS "bet1", "a" NOT BETWEEN 0 AND "b" AS "nbet1" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ result = ''' +----+----+-------+-------+ | a | b | bet1 | nbet1 | +----+----+-------+-------+ | 1 | 2 | true | false | | 3 | 4 | true | false | | 6 | 6 | true | false | | 9 | 8 | false | true | | 12 | 10 | false | true | +----+----+-------+-------+ ''' [cast_numeric] athena = """ WITH values0 AS (SELECT * FROM (VALUES ('0'), ('1')) AS "_values" ("a")), values1 AS (SELECT "a", CAST("a" AS TINYINT) AS "i8", CAST("a" AS SMALLINT) AS "u8", CAST("a" AS SMALLINT) AS "i16", CAST("a" AS INT) AS "u16", CAST("a" AS INT) AS "i32", CAST("a" AS BIGINT) AS "u32", CAST("a" AS BIGINT) AS "i64", CAST("a" AS DOUBLE) AS "f32", CAST("a" AS DOUBLE) AS "f64" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ bigquery = """ WITH values0 AS (SELECT '0' AS `a` UNION ALL SELECT '1' AS `a`), values1 AS (SELECT `a`, CAST(`a` AS INT) AS `i8`, CAST(`a` AS INT) AS `u8`, CAST(`a` AS INT) AS `i16`, CAST(`a` AS INT) AS `u16`, CAST(`a` AS INT) AS `i32`, CAST(`a` AS INT) AS `u32`, CAST(`a` AS INT) AS `i64`, CAST(`a` AS FLOAT64) AS `f32`, CAST(`a` AS FLOAT64) AS `f64` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ clickhouse = """ WITH values0 AS (SELECT '0' AS "a" UNION ALL SELECT '1' AS "a"), values1 AS (SELECT "a", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS TINYINT) ELSE NULL END AS "i8", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS SMALLINT) ELSE NULL END AS "u8", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS SMALLINT) ELSE NULL END AS "i16", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS INT) ELSE NULL END AS "u16", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS INT) ELSE NULL END AS "i32", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS BIGINT) ELSE NULL END AS "u32", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS BIGINT) ELSE NULL END AS "i64", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS FLOAT) ELSE NULL END AS "f32", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS DOUBLE) ELSE NULL END AS "f64" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ databricks = """ WITH values0 AS (SELECT * FROM (VALUES ('0'), ('1')) AS `_values` (`a`)), values1 AS (SELECT `a`, CAST(`a` AS TINYINT) AS `i8`, CAST(`a` AS SMALLINT) AS `u8`, CAST(`a` AS SMALLINT) AS `i16`, CAST(`a` AS INT) AS `u16`, CAST(`a` AS INT) AS `i32`, CAST(`a` AS BIGINT) AS `u32`, CAST(`a` AS BIGINT) AS `i64`, CAST(`a` AS FLOAT) AS `f32`, CAST(`a` AS DOUBLE) AS `f64` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES ('0'), ('1')) AS "_values" ("a")), values1 AS (SELECT "a", CAST("a" AS TINYINT) AS "i8", CAST("a" AS SMALLINT) AS "u8", CAST("a" AS SMALLINT) AS "i16", CAST("a" AS INT) AS "u16", CAST("a" AS INT) AS "i32", CAST("a" AS BIGINT) AS "u32", CAST("a" AS BIGINT) AS "i64", CAST("a" AS FLOAT) AS "f32", CAST("a" AS DOUBLE) AS "f64" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES ('0'), ('1')) AS "_values" ("a")), values1 AS (SELECT "a", CAST("a" AS TINYINT) AS "i8", CAST("a" AS SMALLINT) AS "u8", CAST("a" AS SMALLINT) AS "i16", CAST("a" AS INT) AS "u16", CAST("a" AS INT) AS "i32", CAST("a" AS BIGINT) AS "u32", CAST("a" AS BIGINT) AS "i64", CAST("a" AS FLOAT) AS "f32", CAST("a" AS DOUBLE) AS "f64" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ mysql = """ WITH values0 AS (SELECT * FROM (VALUES ROW('0'), ROW('1')) AS `_values` (`a`)), values1 AS (SELECT `a`, CAST(`a` AS SIGNED) AS `i8`, CAST(`a` AS UNSIGNED) AS `u8`, CAST(`a` AS SIGNED) AS `i16`, CAST(`a` AS UNSIGNED) AS `u16`, CAST(`a` AS SIGNED) AS `i32`, CAST(`a` AS UNSIGNED) AS `u32`, CAST(`a` AS SIGNED) AS `i64`, CAST(`a` AS FLOAT) AS `f32`, CAST(`a` AS DOUBLE) AS `f64` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC """ postgres = """ WITH values0 AS (SELECT * FROM (VALUES ('0'), ('1')) AS "_values" ("a")), values1 AS (SELECT "a", CAST("a" AS SMALLINT) AS "i8", CAST("a" AS SMALLINT) AS "u8", CAST("a" AS SMALLINT) AS "i16", CAST("a" AS INTEGER) AS "u16", CAST("a" AS INTEGER) AS "i32", CAST("a" AS BIGINT) AS "u32", CAST("a" AS BIGINT) AS "i64", CAST("a" AS REAL) AS "f32", CAST("a" AS DOUBLE PRECISION) AS "f64" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ redshift = """ WITH values0 AS (SELECT '0' AS "a" UNION ALL SELECT '1' AS "a"), values1 AS (SELECT "a", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS SMALLINT) ELSE NULL END AS "i8", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS SMALLINT) ELSE NULL END AS "u8", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS SMALLINT) ELSE NULL END AS "i16", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS INTEGER) ELSE NULL END AS "u16", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS INTEGER) ELSE NULL END AS "i32", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS BIGINT) ELSE NULL END AS "u32", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS BIGINT) ELSE NULL END AS "i64", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS REAL) ELSE NULL END AS "f32", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS DOUBLE PRECISION) ELSE NULL END AS "f64" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a" FROM (VALUES ('0'), ('1'))), values1 AS (SELECT "a", CAST("a" AS TINYINT) AS "i8", CAST("a" AS SMALLINT) AS "u8", CAST("a" AS SMALLINT) AS "i16", CAST("a" AS INTEGER) AS "u16", CAST("a" AS INTEGER) AS "i32", CAST("a" AS BIGINT) AS "u32", CAST("a" AS BIGINT) AS "i64", CAST("a" AS FLOAT) AS "f32", CAST("a" AS DOUBLE) AS "f64" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+----+----+-----+-----+-----+-----+-----+-----+-----+ | a | i8 | u8 | i16 | u16 | i32 | u32 | i64 | f32 | f64 | +---+----+----+-----+-----+-----+-----+-----+-----+-----+ | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.0 | 0.0 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1.0 | 1.0 | +---+----+----+-----+-----+-----+-----+-----+-----+-----+ ''' [try_cast_numeric] athena = """ WITH values0 AS (SELECT * FROM (VALUES ('0'), ('1')) AS "_values" ("a")), values1 AS (SELECT "a", TRY_CAST("a" AS TINYINT) AS "i8", TRY_CAST("a" AS SMALLINT) AS "u8", TRY_CAST("a" AS SMALLINT) AS "i16", TRY_CAST("a" AS INT) AS "u16", TRY_CAST("a" AS INT) AS "i32", TRY_CAST("a" AS BIGINT) AS "u32", TRY_CAST("a" AS BIGINT) AS "i64", TRY_CAST("a" AS DOUBLE) AS "f32", TRY_CAST("a" AS DOUBLE) AS "f64" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ bigquery = """ WITH values0 AS (SELECT '0' AS `a` UNION ALL SELECT '1' AS `a`), values1 AS (SELECT `a`, SAFE_CAST(`a` AS INT) AS `i8`, SAFE_CAST(`a` AS INT) AS `u8`, SAFE_CAST(`a` AS INT) AS `i16`, SAFE_CAST(`a` AS INT) AS `u16`, SAFE_CAST(`a` AS INT) AS `i32`, SAFE_CAST(`a` AS INT) AS `u32`, SAFE_CAST(`a` AS INT) AS `i64`, SAFE_CAST(`a` AS FLOAT64) AS `f32`, SAFE_CAST(`a` AS FLOAT64) AS `f64` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ clickhouse = """ WITH values0 AS (SELECT '0' AS "a" UNION ALL SELECT '1' AS "a"), values1 AS (SELECT "a", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS TINYINT) ELSE NULL END AS "i8", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS SMALLINT) ELSE NULL END AS "u8", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS SMALLINT) ELSE NULL END AS "i16", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS INT) ELSE NULL END AS "u16", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS INT) ELSE NULL END AS "i32", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS BIGINT) ELSE NULL END AS "u32", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS BIGINT) ELSE NULL END AS "i64", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS FLOAT) ELSE NULL END AS "f32", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS DOUBLE) ELSE NULL END AS "f64" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ databricks = """ WITH values0 AS (SELECT * FROM (VALUES ('0'), ('1')) AS `_values` (`a`)), values1 AS (SELECT `a`, TRY_CAST(`a` AS TINYINT) AS `i8`, TRY_CAST(`a` AS SMALLINT) AS `u8`, TRY_CAST(`a` AS SMALLINT) AS `i16`, TRY_CAST(`a` AS INT) AS `u16`, TRY_CAST(`a` AS INT) AS `i32`, TRY_CAST(`a` AS BIGINT) AS `u32`, TRY_CAST(`a` AS BIGINT) AS `i64`, TRY_CAST(`a` AS FLOAT) AS `f32`, TRY_CAST(`a` AS DOUBLE) AS `f64` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES ('0'), ('1')) AS "_values" ("a")), values1 AS (SELECT "a", TRY_CAST("a" AS TINYINT) AS "i8", TRY_CAST("a" AS SMALLINT) AS "u8", TRY_CAST("a" AS SMALLINT) AS "i16", TRY_CAST("a" AS INT) AS "u16", TRY_CAST("a" AS INT) AS "i32", TRY_CAST("a" AS BIGINT) AS "u32", TRY_CAST("a" AS BIGINT) AS "i64", TRY_CAST("a" AS FLOAT) AS "f32", TRY_CAST("a" AS DOUBLE) AS "f64" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES ('0'), ('1')) AS "_values" ("a")), values1 AS (SELECT "a", TRY_CAST("a" AS TINYINT) AS "i8", TRY_CAST("a" AS SMALLINT) AS "u8", TRY_CAST("a" AS SMALLINT) AS "i16", TRY_CAST("a" AS INT) AS "u16", TRY_CAST("a" AS INT) AS "i32", TRY_CAST("a" AS BIGINT) AS "u32", TRY_CAST("a" AS BIGINT) AS "i64", TRY_CAST("a" AS FLOAT) AS "f32", TRY_CAST("a" AS DOUBLE) AS "f64" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ mysql = """ WITH values0 AS (SELECT * FROM (VALUES ROW('0'), ROW('1')) AS `_values` (`a`)), values1 AS (SELECT `a`, CAST(`a` AS SIGNED) AS `i8`, CAST(`a` AS UNSIGNED) AS `u8`, CAST(`a` AS SIGNED) AS `i16`, CAST(`a` AS UNSIGNED) AS `u16`, CAST(`a` AS SIGNED) AS `i32`, CAST(`a` AS UNSIGNED) AS `u32`, CAST(`a` AS SIGNED) AS `i64`, CAST(`a` AS FLOAT) AS `f32`, CAST(`a` AS DOUBLE) AS `f64` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC """ postgres = """ WITH values0 AS (SELECT * FROM (VALUES ('0'), ('1')) AS "_values" ("a")), values1 AS (SELECT "a", CAST("a" AS SMALLINT) AS "i8", CAST("a" AS SMALLINT) AS "u8", CAST("a" AS SMALLINT) AS "i16", CAST("a" AS INTEGER) AS "u16", CAST("a" AS INTEGER) AS "i32", CAST("a" AS BIGINT) AS "u32", CAST("a" AS BIGINT) AS "i64", CAST("a" AS REAL) AS "f32", CAST("a" AS DOUBLE PRECISION) AS "f64" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ redshift = """ WITH values0 AS (SELECT '0' AS "a" UNION ALL SELECT '1' AS "a"), values1 AS (SELECT "a", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS SMALLINT) ELSE NULL END AS "i8", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS SMALLINT) ELSE NULL END AS "u8", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS SMALLINT) ELSE NULL END AS "i16", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS INTEGER) ELSE NULL END AS "u16", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS INTEGER) ELSE NULL END AS "i32", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS BIGINT) ELSE NULL END AS "u32", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS BIGINT) ELSE NULL END AS "i64", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS REAL) ELSE NULL END AS "f32", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS DOUBLE PRECISION) ELSE NULL END AS "f64" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a" FROM (VALUES ('0'), ('1'))), values1 AS (SELECT "a", TRY_CAST("a" AS TINYINT) AS "i8", TRY_CAST("a" AS SMALLINT) AS "u8", TRY_CAST("a" AS SMALLINT) AS "i16", TRY_CAST("a" AS INTEGER) AS "u16", TRY_CAST("a" AS INTEGER) AS "i32", TRY_CAST("a" AS BIGINT) AS "u32", TRY_CAST("a" AS BIGINT) AS "i64", TRY_CAST("a" AS FLOAT) AS "f32", TRY_CAST("a" AS DOUBLE) AS "f64" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+----+----+-----+-----+-----+-----+-----+-----+-----+ | a | i8 | u8 | i16 | u16 | i32 | u32 | i64 | f32 | f64 | +---+----+----+-----+-----+-----+-----+-----+-----+-----+ | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.0 | 0.0 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1.0 | 1.0 | +---+----+----+-----+-----+-----+-----+-----+-----+-----+ ''' [cast_string] athena = """ WITH values0 AS (SELECT * FROM (VALUES (0, NULL, true, 'A'), (1, 1.5, false, 'BB'), (NULL, 2.25, NULL, 'CCC')) AS "_values" ("a", "b", "c", "d")), values1 AS (SELECT CAST("a" AS VARCHAR) AS "a", CAST("b" AS VARCHAR) AS "b", CAST("c" AS VARCHAR) AS "c", CAST("d" AS VARCHAR) AS "d" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ bigquery = """ WITH values0 AS (SELECT 0 AS `a`, NULL AS `b`, true AS `c`, 'A' AS `d` UNION ALL SELECT 1 AS `a`, 1.5 AS `b`, false AS `c`, 'BB' AS `d` UNION ALL SELECT NULL AS `a`, 2.25 AS `b`, NULL AS `c`, 'CCC' AS `d`), values1 AS (SELECT CAST(`a` AS STRING) AS `a`, CAST(`b` AS STRING) AS `b`, CAST(`c` AS STRING) AS `c`, CAST(`d` AS STRING) AS `d` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ clickhouse = """ WITH values0 AS (SELECT 0 AS "a", NULL AS "b", true AS "c", 'A' AS "d" UNION ALL SELECT 1 AS "a", 1.5 AS "b", false AS "c", 'BB' AS "d" UNION ALL SELECT NULL AS "a", 2.25 AS "b", NULL AS "c", 'CCC' AS "d"), values1 AS (SELECT CASE WHEN "a" IS NOT NULL THEN CAST("a" AS VARCHAR) ELSE NULL END AS "a", CASE WHEN "b" IS NOT NULL THEN CAST("b" AS VARCHAR) ELSE NULL END AS "b", CASE WHEN "c" IS NOT NULL THEN CAST("c" AS VARCHAR) ELSE NULL END AS "c", CASE WHEN "d" IS NOT NULL THEN CAST("d" AS VARCHAR) ELSE NULL END AS "d" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ databricks = """ WITH values0 AS (SELECT * FROM (VALUES (0, NULL, true, 'A'), (1, 1.5, false, 'BB'), (NULL, 2.25, NULL, 'CCC')) AS `_values` (`a`, `b`, `c`, `d`)), values1 AS (SELECT CAST(`a` AS STRING) AS `a`, CAST(`b` AS STRING) AS `b`, CAST(`c` AS STRING) AS `c`, CAST(`d` AS STRING) AS `d` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (0, NULL, true, 'A'), (1, 1.5, false, 'BB'), (NULL, 2.25, NULL, 'CCC')) AS "_values" ("a", "b", "c", "d")), values1 AS (SELECT CAST("a" AS STRING) AS "a", CAST("b" AS STRING) AS "b", CASE WHEN ("c" = true) THEN 'true' WHEN ("c" = false) THEN 'false' ELSE NULL END AS "c", CAST("d" AS STRING) AS "d" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (0, NULL, true, 'A'), (1, 1.5, false, 'BB'), (NULL, 2.25, NULL, 'CCC')) AS "_values" ("a", "b", "c", "d")), values1 AS (SELECT CAST("a" AS VARCHAR) AS "a", CAST("b" AS VARCHAR) AS "b", CAST("c" AS VARCHAR) AS "c", CAST("d" AS VARCHAR) AS "d" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ mysql = """ WITH values0 AS (SELECT * FROM (VALUES ROW(0, NULL, true, 'A'), ROW(1, 1.5, false, 'BB'), ROW(NULL, 2.25, NULL, 'CCC')) AS `_values` (`a`, `b`, `c`, `d`)), values1 AS (SELECT CAST(`a` AS CHAR) AS `a`, CAST(`b` AS CHAR) AS `b`, CASE WHEN (`c` = true) THEN 'true' WHEN (`c` = false) THEN 'false' ELSE NULL END AS `c`, CAST(`d` AS CHAR) AS `d` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC """ postgres = """ WITH values0 AS (SELECT * FROM (VALUES (0, NULL, true, 'A'), (1, 1.5, false, 'BB'), (NULL, 2.25, NULL, 'CCC')) AS "_values" ("a", "b", "c", "d")), values1 AS (SELECT CAST("a" AS TEXT) AS "a", CAST("b" AS TEXT) AS "b", CAST("c" AS TEXT) AS "c", CAST("d" AS TEXT) AS "d" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ redshift = """ WITH values0 AS (SELECT 0 AS "a", NULL AS "b", true AS "c", 'A' AS "d" UNION ALL SELECT 1 AS "a", 1.5 AS "b", false AS "c", 'BB' AS "d" UNION ALL SELECT NULL AS "a", 2.25 AS "b", NULL AS "c", 'CCC' AS "d"), values1 AS (SELECT CASE WHEN "a" IS NOT NULL THEN CAST("a" AS TEXT) ELSE NULL END AS "a", CASE WHEN "b" IS NOT NULL THEN CAST("b" AS TEXT) ELSE NULL END AS "b", CASE WHEN "c" IS NOT NULL THEN CASE WHEN ("c" = true) THEN 'true' WHEN ("c" = false) THEN 'false' ELSE NULL END ELSE NULL END AS "c", CASE WHEN "d" IS NOT NULL THEN CAST("d" AS TEXT) ELSE NULL END AS "d" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b", "COLUMN3" AS "c", "COLUMN4" AS "d" FROM (VALUES (0, NULL, true, 'A'), (1, 1.5, false, 'BB'), (NULL, 2.25, NULL, 'CCC'))), values1 AS (SELECT CAST("a" AS VARCHAR) AS "a", CAST("b" AS VARCHAR) AS "b", CAST("c" AS VARCHAR) AS "c", CAST("d" AS VARCHAR) AS "d" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+------+-------+-----+ | a | b | c | d | +---+------+-------+-----+ | | 2.25 | | CCC | | 0 | | true | A | | 1 | 1.5 | false | BB | +---+------+-------+-----+ ''' [try_cast_string] athena = """ WITH values0 AS (SELECT * FROM (VALUES (0, NULL, true, 'A'), (1, 1.5, false, 'BB'), (NULL, 2.25, NULL, 'CCC')) AS "_values" ("a", "b", "c", "d")), values1 AS (SELECT TRY_CAST("a" AS VARCHAR) AS "a", TRY_CAST("b" AS VARCHAR) AS "b", TRY_CAST("c" AS VARCHAR) AS "c", TRY_CAST("d" AS VARCHAR) AS "d" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ bigquery = """ WITH values0 AS (SELECT 0 AS `a`, NULL AS `b`, true AS `c`, 'A' AS `d` UNION ALL SELECT 1 AS `a`, 1.5 AS `b`, false AS `c`, 'BB' AS `d` UNION ALL SELECT NULL AS `a`, 2.25 AS `b`, NULL AS `c`, 'CCC' AS `d`), values1 AS (SELECT SAFE_CAST(`a` AS STRING) AS `a`, SAFE_CAST(`b` AS STRING) AS `b`, SAFE_CAST(`c` AS STRING) AS `c`, SAFE_CAST(`d` AS STRING) AS `d` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ clickhouse = """ WITH values0 AS (SELECT 0 AS "a", NULL AS "b", true AS "c", 'A' AS "d" UNION ALL SELECT 1 AS "a", 1.5 AS "b", false AS "c", 'BB' AS "d" UNION ALL SELECT NULL AS "a", 2.25 AS "b", NULL AS "c", 'CCC' AS "d"), values1 AS (SELECT CASE WHEN "a" IS NOT NULL THEN CAST("a" AS VARCHAR) ELSE NULL END AS "a", CASE WHEN "b" IS NOT NULL THEN CAST("b" AS VARCHAR) ELSE NULL END AS "b", CASE WHEN "c" IS NOT NULL THEN CAST("c" AS VARCHAR) ELSE NULL END AS "c", CASE WHEN "d" IS NOT NULL THEN CAST("d" AS VARCHAR) ELSE NULL END AS "d" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ databricks = """ WITH values0 AS (SELECT * FROM (VALUES (0, NULL, true, 'A'), (1, 1.5, false, 'BB'), (NULL, 2.25, NULL, 'CCC')) AS `_values` (`a`, `b`, `c`, `d`)), values1 AS (SELECT TRY_CAST(`a` AS STRING) AS `a`, TRY_CAST(`b` AS STRING) AS `b`, TRY_CAST(`c` AS STRING) AS `c`, TRY_CAST(`d` AS STRING) AS `d` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (0, NULL, true, 'A'), (1, 1.5, false, 'BB'), (NULL, 2.25, NULL, 'CCC')) AS "_values" ("a", "b", "c", "d")), values1 AS (SELECT TRY_CAST("a" AS STRING) AS "a", TRY_CAST("b" AS STRING) AS "b", CASE WHEN ("c" = true) THEN 'true' WHEN ("c" = false) THEN 'false' ELSE NULL END AS "c", TRY_CAST("d" AS STRING) AS "d" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (0, NULL, true, 'A'), (1, 1.5, false, 'BB'), (NULL, 2.25, NULL, 'CCC')) AS "_values" ("a", "b", "c", "d")), values1 AS (SELECT TRY_CAST("a" AS VARCHAR) AS "a", TRY_CAST("b" AS VARCHAR) AS "b", TRY_CAST("c" AS VARCHAR) AS "c", TRY_CAST("d" AS VARCHAR) AS "d" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ mysql = """ WITH values0 AS (SELECT * FROM (VALUES ROW(0, NULL, true, 'A'), ROW(1, 1.5, false, 'BB'), ROW(NULL, 2.25, NULL, 'CCC')) AS `_values` (`a`, `b`, `c`, `d`)), values1 AS (SELECT CAST(`a` AS CHAR) AS `a`, CAST(`b` AS CHAR) AS `b`, CASE WHEN (`c` = true) THEN 'true' WHEN (`c` = false) THEN 'false' ELSE NULL END AS `c`, CAST(`d` AS CHAR) AS `d` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC """ postgres = """ WITH values0 AS (SELECT * FROM (VALUES (0, NULL, true, 'A'), (1, 1.5, false, 'BB'), (NULL, 2.25, NULL, 'CCC')) AS "_values" ("a", "b", "c", "d")), values1 AS (SELECT CAST("a" AS TEXT) AS "a", CAST("b" AS TEXT) AS "b", CAST("c" AS TEXT) AS "c", CAST("d" AS TEXT) AS "d" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ redshift = """ WITH values0 AS (SELECT 0 AS "a", NULL AS "b", true AS "c", 'A' AS "d" UNION ALL SELECT 1 AS "a", 1.5 AS "b", false AS "c", 'BB' AS "d" UNION ALL SELECT NULL AS "a", 2.25 AS "b", NULL AS "c", 'CCC' AS "d"), values1 AS (SELECT CASE WHEN "a" IS NOT NULL THEN CAST("a" AS TEXT) ELSE NULL END AS "a", CASE WHEN "b" IS NOT NULL THEN CAST("b" AS TEXT) ELSE NULL END AS "b", CASE WHEN "c" IS NOT NULL THEN CASE WHEN ("c" = true) THEN 'true' WHEN ("c" = false) THEN 'false' ELSE NULL END ELSE NULL END AS "c", CASE WHEN "d" IS NOT NULL THEN CAST("d" AS TEXT) ELSE NULL END AS "d" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b", "COLUMN3" AS "c", "COLUMN4" AS "d" FROM (VALUES (0, NULL, true, 'A'), (1, 1.5, false, 'BB'), (NULL, 2.25, NULL, 'CCC'))), values1 AS (SELECT CAST("a" AS VARCHAR) AS "a", CAST("b" AS VARCHAR) AS "b", CAST("c" AS VARCHAR) AS "c", TRY_CAST("d" AS VARCHAR) AS "d" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+------+-------+-----+ | a | b | c | d | +---+------+-------+-----+ | | 2.25 | | CCC | | 0 | | true | A | | 1 | 1.5 | false | BB | +---+------+-------+-----+ ''' [non_finite_numbers] athena = """ WITH values0 AS (SELECT * FROM (VALUES (0)) AS "_values" ("a")), values1 AS (SELECT "a", NULL AS "ninf", NULL AS "nan", NULL AS "inf" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ bigquery = """ WITH values0 AS (SELECT 0 AS `a`), values1 AS (SELECT `a`, CAST('-inf' AS FLOAT64) AS `ninf`, CAST('NaN' AS FLOAT64) AS `nan`, CAST('inf' AS FLOAT64) AS `inf` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ clickhouse = """ WITH values0 AS (SELECT 0 AS "a"), values1 AS (SELECT "a", CAST('-inf' AS DOUBLE) AS "ninf", CAST('NaN' AS DOUBLE) AS "nan", CAST('inf' AS DOUBLE) AS "inf" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ databricks = """ WITH values0 AS (SELECT * FROM (VALUES (0)) AS `_values` (`a`)), values1 AS (SELECT `a`, CAST('-inf' AS DOUBLE) AS `ninf`, CAST('NaN' AS DOUBLE) AS `nan`, CAST('inf' AS DOUBLE) AS `inf` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (0)) AS "_values" ("a")), values1 AS (SELECT "a", CAST('-inf' AS DOUBLE) AS "ninf", CAST('NaN' AS DOUBLE) AS "nan", CAST('inf' AS DOUBLE) AS "inf" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (0)) AS "_values" ("a")), values1 AS (SELECT "a", CAST('-inf' AS DOUBLE) AS "ninf", CAST('NaN' AS DOUBLE) AS "nan", CAST('inf' AS DOUBLE) AS "inf" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ mysql = """ WITH values0 AS (SELECT * FROM (VALUES ROW(0)) AS `_values` (`a`)), values1 AS (SELECT `a`, NULL AS `ninf`, NULL AS `nan`, NULL AS `inf` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC """ postgres = """ WITH values0 AS (SELECT * FROM (VALUES (0)) AS "_values" ("a")), values1 AS (SELECT "a", CAST('-inf' AS DOUBLE PRECISION) AS "ninf", CAST('NaN' AS DOUBLE PRECISION) AS "nan", CAST('inf' AS DOUBLE PRECISION) AS "inf" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ redshift = """ WITH values0 AS (SELECT 0 AS "a"), values1 AS (SELECT "a", CAST('-inf' AS DOUBLE PRECISION) AS "ninf", CAST('NaN' AS DOUBLE PRECISION) AS "nan", CAST('inf' AS DOUBLE PRECISION) AS "inf" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a" FROM (VALUES (0))), values1 AS (SELECT "a", CAST('-inf' AS DOUBLE) AS "ninf", CAST('NaN' AS DOUBLE) AS "nan", CAST('inf' AS DOUBLE) AS "inf" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+------+-----+-----+ | a | ninf | nan | inf | +---+------+-----+-----+ | 0 | -inf | NaN | inf | +---+------+-----+-----+ ''' [scalar_math_functions] athena = """ WITH values0 AS (SELECT * FROM (VALUES (0, -1.8, 0.1), (1, -1.0, 0.2), (2, 0.0, 0.4), (3, 1.0, 0.6), (4, 1.8, 0.8), (5, NULL, NULL)) AS "_values" ("a", "b", "c")), values1 AS (SELECT "a", abs("b") AS "abs", acos("c") AS "acos", asin("c") AS "asin", atan("c") AS "atan", atan2("c", "a") AS "atan2", CEIL("b") AS "ceil", cos("b") AS "cos", exp("b") AS "exp", FLOOR("b") AS "floor", ln("c") AS "ln", log10("c") AS "log", log10("c") AS "log10", log2("c") AS "log2", pow("b", "a") AS "power", round("b") AS "round", sin("b") AS "sin", sqrt("c") AS "sqrt", tan("b") AS "tan" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ bigquery = """ WITH values0 AS (SELECT 0 AS `a`, -1.8 AS `b`, 0.1 AS `c` UNION ALL SELECT 1 AS `a`, -1.0 AS `b`, 0.2 AS `c` UNION ALL SELECT 2 AS `a`, 0.0 AS `b`, 0.4 AS `c` UNION ALL SELECT 3 AS `a`, 1.0 AS `b`, 0.6 AS `c` UNION ALL SELECT 4 AS `a`, 1.8 AS `b`, 0.8 AS `c` UNION ALL SELECT 5 AS `a`, NULL AS `b`, NULL AS `c`), values1 AS (SELECT `a`, abs(`b`) AS `abs`, acos(`c`) AS `acos`, asin(`c`) AS `asin`, atan(`c`) AS `atan`, atan2(`c`, `a`) AS `atan2`, CEIL(`b`) AS `ceil`, cos(`b`) AS `cos`, exp(`b`) AS `exp`, FLOOR(`b`) AS `floor`, ln(`c`) AS `ln`, log10(`c`) AS `log`, log10(`c`) AS `log10`, log(`c`, 2) AS `log2`, pow(`b`, `a`) AS `power`, round(`b`) AS `round`, sin(`b`) AS `sin`, sqrt(`c`) AS `sqrt`, tan(`b`) AS `tan` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ clickhouse = """ WITH values0 AS (SELECT 0 AS "a", -1.8 AS "b", 0.1 AS "c" UNION ALL SELECT 1 AS "a", -1.0 AS "b", 0.2 AS "c" UNION ALL SELECT 2 AS "a", 0.0 AS "b", 0.4 AS "c" UNION ALL SELECT 3 AS "a", 1.0 AS "b", 0.6 AS "c" UNION ALL SELECT 4 AS "a", 1.8 AS "b", 0.8 AS "c" UNION ALL SELECT 5 AS "a", NULL AS "b", NULL AS "c"), values1 AS (SELECT "a", abs("b") AS "abs", acos("c") AS "acos", asin("c") AS "asin", atan("c") AS "atan", atan2("c", "a") AS "atan2", CEIL("b") AS "ceil", cos("b") AS "cos", exp("b") AS "exp", FLOOR("b") AS "floor", ln("c") AS "ln", log10("c") AS "log", log10("c") AS "log10", log2("c") AS "log2", pow("b", "a") AS "power", round("b") AS "round", sin("b") AS "sin", sqrt("c") AS "sqrt", tan("b") AS "tan" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ databricks = """ WITH values0 AS (SELECT * FROM (VALUES (0, -1.8, 0.1), (1, -1.0, 0.2), (2, 0.0, 0.4), (3, 1.0, 0.6), (4, 1.8, 0.8), (5, NULL, NULL)) AS `_values` (`a`, `b`, `c`)), values1 AS (SELECT `a`, abs(`b`) AS `abs`, acos(`c`) AS `acos`, asin(`c`) AS `asin`, atan(`c`) AS `atan`, atan2(`c`, `a`) AS `atan2`, CEIL(`b`) AS `ceil`, cos(`b`) AS `cos`, exp(`b`) AS `exp`, FLOOR(`b`) AS `floor`, ln(`c`) AS `ln`, log10(`c`) AS `log`, log10(`c`) AS `log10`, log2(`c`) AS `log2`, pow(`b`, `a`) AS `power`, round(`b`) AS `round`, sin(`b`) AS `sin`, sqrt(`c`) AS `sqrt`, tan(`b`) AS `tan` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (0, -1.8, 0.1), (1, -1.0, 0.2), (2, 0.0, 0.4), (3, 1.0, 0.6), (4, 1.8, 0.8), (5, NULL, NULL)) AS "_values" ("a", "b", "c")), values1 AS (SELECT "a", abs("b") AS "abs", acos("c") AS "acos", asin("c") AS "asin", atan("c") AS "atan", atan2("c", "a") AS "atan2", CEIL("b") AS "ceil", cos("b") AS "cos", exp("b") AS "exp", FLOOR("b") AS "floor", ln("c") AS "ln", log("c") AS "log", log10("c") AS "log10", log2("c") AS "log2", pow("b", "a") AS "power", round("b") AS "round", sin("b") AS "sin", sqrt("c") AS "sqrt", tan("b") AS "tan" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (0, -1.8, 0.1), (1, -1.0, 0.2), (2, 0.0, 0.4), (3, 1.0, 0.6), (4, 1.8, 0.8), (5, NULL, NULL)) AS "_values" ("a", "b", "c")), values1 AS (SELECT "a", abs("b") AS "abs", acos("c") AS "acos", asin("c") AS "asin", atan("c") AS "atan", atan2("c", "a") AS "atan2", CEIL("b") AS "ceil", cos("b") AS "cos", pow(2.718281828459045, "b") AS "exp", FLOOR("b") AS "floor", ln("c") AS "ln", log("c") AS "log", log10("c") AS "log10", log2("c") AS "log2", pow("b", "a") AS "power", round("b") AS "round", sin("b") AS "sin", sqrt("c") AS "sqrt", tan("b") AS "tan" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ mysql = """ WITH values0 AS (SELECT * FROM (VALUES ROW(0, -1.8, 0.1), ROW(1, -1.0, 0.2), ROW(2, 0.0, 0.4), ROW(3, 1.0, 0.6), ROW(4, 1.8, 0.8), ROW(5, NULL, NULL)) AS `_values` (`a`, `b`, `c`)), values1 AS (SELECT `a`, abs(`b`) AS `abs`, acos(`c`) AS `acos`, asin(`c`) AS `asin`, atan(`c`) AS `atan`, atan2(`c`, `a`) AS `atan2`, CEIL(`b`) AS `ceil`, cos(`b`) AS `cos`, exp(`b`) AS `exp`, FLOOR(`b`) AS `floor`, ln(`c`) AS `ln`, log10(`c`) AS `log`, log10(`c`) AS `log10`, log2(`c`) AS `log2`, pow(`b`, `a`) AS `power`, round(`b`) AS `round`, sin(`b`) AS `sin`, sqrt(`c`) AS `sqrt`, tan(`b`) AS `tan` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC """ postgres = """ WITH values0 AS (SELECT * FROM (VALUES (0, -1.8, 0.1), (1, -1.0, 0.2), (2, 0.0, 0.4), (3, 1.0, 0.6), (4, 1.8, 0.8), (5, NULL, NULL)) AS "_values" ("a", "b", "c")), values1 AS (SELECT "a", abs("b") AS "abs", acos("c") AS "acos", asin("c") AS "asin", atan("c") AS "atan", atan2("c", "a") AS "atan2", CEIL("b") AS "ceil", cos("b") AS "cos", exp("b") AS "exp", FLOOR("b") AS "floor", ln("c") AS "ln", log("c") AS "log", log(10, "c") AS "log10", log(2, "c") AS "log2", pow("b", "a") AS "power", round("b") AS "round", sin("b") AS "sin", sqrt("c") AS "sqrt", tan("b") AS "tan" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ redshift = """ WITH values0 AS (SELECT 0 AS "a", -1.8 AS "b", 0.1 AS "c" UNION ALL SELECT 1 AS "a", -1.0 AS "b", 0.2 AS "c" UNION ALL SELECT 2 AS "a", 0.0 AS "b", 0.4 AS "c" UNION ALL SELECT 3 AS "a", 1.0 AS "b", 0.6 AS "c" UNION ALL SELECT 4 AS "a", 1.8 AS "b", 0.8 AS "c" UNION ALL SELECT 5 AS "a", NULL AS "b", NULL AS "c"), values1 AS (SELECT "a", abs("b") AS "abs", acos("c") AS "acos", asin("c") AS "asin", atan("c") AS "atan", atan2("c", "a") AS "atan2", CEIL("b") AS "ceil", cos("b") AS "cos", exp("b") AS "exp", FLOOR("b") AS "floor", ln(CAST("c" AS DOUBLE PRECISION)) AS "ln", log(CAST("c" AS DOUBLE PRECISION)) AS "log", log(CAST("c" AS DOUBLE PRECISION)) AS "log10", ln(CAST("c" AS DOUBLE PRECISION)) / ln(2) AS "log2", pow("b", "a") AS "power", round("b") AS "round", sin("b") AS "sin", sqrt("c") AS "sqrt", tan("b") AS "tan" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b", "COLUMN3" AS "c" FROM (VALUES (0, -1.8, 0.1), (1, -1.0, 0.2), (2, 0.0, 0.4), (3, 1.0, 0.6), (4, 1.8, 0.8), (5, NULL, NULL))), values1 AS (SELECT "a", abs("b") AS "abs", acos("c") AS "acos", asin("c") AS "asin", atan("c") AS "atan", atan2("c", "a") AS "atan2", CEIL("b") AS "ceil", cos("b") AS "cos", exp("b") AS "exp", FLOOR("b") AS "floor", ln("c") AS "ln", log(10, "c") AS "log", log(10, "c") AS "log10", log(2, "c") AS "log2", pow("b", "a") AS "power", round("b") AS "round", sin("b") AS "sin", sqrt("c") AS "sqrt", tan("b") AS "tan" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+-----+--------------------+---------------------+---------------------+---------------------+------+---------------------+---------------------+-------+---------------------+----------------------+----------------------+---------------------+---------+-------+---------------------+---------------------+--------------------+ | a | abs | acos | asin | atan | atan2 | ceil | cos | exp | floor | ln | log | log10 | log2 | power | round | sin | sqrt | tan | +---+-----+--------------------+---------------------+---------------------+---------------------+------+---------------------+---------------------+-------+---------------------+----------------------+----------------------+---------------------+---------+-------+---------------------+---------------------+--------------------+ | 0 | 1.8 | 1.4706289056333368 | 0.1001674211615598 | 0.09966865249116204 | 1.5707963267948966 | -1 | -0.2272020946930871 | 0.16529888822158653 | -2 | -2.3025850929940455 | -1 | -1 | -3.321928094887362 | 1 | -2 | -0.9738476308781951 | 0.31622776601683794 | 4.286261674628062 | | 1 | 1 | 1.3694384060045657 | 0.2013579207903308 | 0.19739555984988078 | 0.19739555984988078 | -1 | 0.5403023058681398 | 0.36787944117144233 | -1 | -1.6094379124341003 | -0.6989700043360187 | -0.6989700043360187 | -2.321928094887362 | -1 | -1 | -0.8414709848078965 | 0.4472135954999579 | -1.557407724654902 | | 2 | 0 | 1.1592794807274085 | 0.41151684606748806 | 0.3805063771123649 | 0.19739555984988078 | 0 | 1 | 1 | 0 | -0.916290731874155 | -0.3979400086720376 | -0.3979400086720376 | -1.3219280948873622 | 0 | 0 | 0 | 0.6324555320336759 | 0 | | 3 | 1 | 0.9272952180016123 | 0.6435011087932844 | 0.5404195002705842 | 0.19739555984988075 | 1 | 0.5403023058681398 | 2.718281828459045 | 1 | -0.5108256237659907 | -0.2218487496163564 | -0.2218487496163564 | -0.7369655941662062 | 1 | 1 | 0.8414709848078965 | 0.7745966692414834 | 1.557407724654902 | | 4 | 1.8 | 0.6435011087932843 | 0.9272952180016123 | 0.6747409422235527 | 0.19739555984988078 | 2 | -0.2272020946930871 | 6.0496474644129465 | 1 | -0.2231435513142097 | -0.09691001300805639 | -0.09691001300805639 | -0.3219280948873623 | 10.4976 | 2 | 0.9738476308781951 | 0.8944271909999159 | -4.286261674628062 | | 5 | | | | | | | | | | | | | | | | | | | +---+-----+--------------------+---------------------+---------------------+---------------------+------+---------------------+---------------------+-------+---------------------+----------------------+----------------------+---------------------+---------+-------+---------------------+---------------------+--------------------+ ''' [is_finite] athena = """ WITH values0 AS (SELECT * FROM (VALUES (0, 0.0), (1, -1.5), (2, NULL), (3, NULL), (4, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", "a" IS NOT NULL AS "f1", "b" IS NOT NULL AS "f2" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ bigquery = """ WITH values0 AS (SELECT 0 AS `a`, 0.0 AS `b` UNION ALL SELECT 1 AS `a`, -1.5 AS `b` UNION ALL SELECT 2 AS `a`, CAST('-inf' AS FLOAT64) AS `b` UNION ALL SELECT 3 AS `a`, CAST('inf' AS FLOAT64) AS `b` UNION ALL SELECT 4 AS `a`, CAST('NaN' AS FLOAT64) AS `b`), values1 AS (SELECT `a`, `b`, CAST(`a` AS STRING) NOT IN (CAST(CAST('-inf' AS FLOAT64) AS STRING), CAST(CAST('inf' AS FLOAT64) AS STRING), CAST(CAST('NaN' AS FLOAT64) AS STRING)) AS `f1`, CAST(`b` AS STRING) NOT IN (CAST(CAST('-inf' AS FLOAT64) AS STRING), CAST(CAST('inf' AS FLOAT64) AS STRING), CAST(CAST('NaN' AS FLOAT64) AS STRING)) AS `f2` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ clickhouse = """ WITH values0 AS (SELECT 0 AS "a", 0.0 AS "b" UNION ALL SELECT 1 AS "a", -1.5 AS "b" UNION ALL SELECT 2 AS "a", CAST('-inf' AS DOUBLE) AS "b" UNION ALL SELECT 3 AS "a", CAST('inf' AS DOUBLE) AS "b" UNION ALL SELECT 4 AS "a", CAST('NaN' AS DOUBLE) AS "b"), values1 AS (SELECT "a", "b", isFinite("a") AS "f1", isFinite("b") AS "f2" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ databricks = """ WITH values0 AS (SELECT * FROM (VALUES (0, 0.0), (1, -1.5), (2, CAST('-inf' AS DOUBLE)), (3, CAST('inf' AS DOUBLE)), (4, CAST('NaN' AS DOUBLE))) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, (NOT `a` IN (CAST('-inf' AS DOUBLE), CAST('inf' AS DOUBLE), CAST('NaN' AS DOUBLE))) AS `f1`, (NOT `b` IN (CAST('-inf' AS DOUBLE), CAST('inf' AS DOUBLE), CAST('NaN' AS DOUBLE))) AS `f2` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (0, 0.0), (1, -1.5), (2, CAST('-inf' AS DOUBLE)), (3, CAST('inf' AS DOUBLE)), (4, CAST('NaN' AS DOUBLE))) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", isfinite("a") AS "f1", isfinite("b") AS "f2" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (0, 0.0), (1, -1.5), (2, CAST('-inf' AS DOUBLE)), (3, CAST('inf' AS DOUBLE)), (4, CAST('NaN' AS DOUBLE))) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", isfinite("a") AS "f1", isfinite("b") AS "f2" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ mysql = """ WITH values0 AS (SELECT * FROM (VALUES ROW(0, 0.0), ROW(1, -1.5), ROW(2, NULL), ROW(3, NULL), ROW(4, NULL)) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, `a` IS NOT NULL AS `f1`, `b` IS NOT NULL AS `f2` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC """ postgres = """ WITH values0 AS (SELECT * FROM (VALUES (0, 0.0), (1, -1.5), (2, CAST('-inf' AS DOUBLE PRECISION)), (3, CAST('inf' AS DOUBLE PRECISION)), (4, CAST('NaN' AS DOUBLE PRECISION))) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", (NOT "a" IN (CAST('-inf' AS DOUBLE PRECISION), CAST('inf' AS DOUBLE PRECISION), CAST('NaN' AS DOUBLE PRECISION))) AS "f1", (NOT "b" IN (CAST('-inf' AS DOUBLE PRECISION), CAST('inf' AS DOUBLE PRECISION), CAST('NaN' AS DOUBLE PRECISION))) AS "f2" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ redshift = """ WITH values0 AS (SELECT 0 AS "a", 0.0 AS "b" UNION ALL SELECT 1 AS "a", -1.5 AS "b" UNION ALL SELECT 2 AS "a", CAST('-inf' AS DOUBLE PRECISION) AS "b" UNION ALL SELECT 3 AS "a", CAST('inf' AS DOUBLE PRECISION) AS "b" UNION ALL SELECT 4 AS "a", CAST('NaN' AS DOUBLE PRECISION) AS "b"), values1 AS (SELECT "a", "b", CASE WHEN "a" IS NOT NULL THEN CAST("a" AS TEXT) ELSE NULL END NOT IN (CASE WHEN CAST('-inf' AS DOUBLE PRECISION) IS NOT NULL THEN CAST(CAST('-inf' AS DOUBLE PRECISION) AS TEXT) ELSE NULL END, CASE WHEN CAST('inf' AS DOUBLE PRECISION) IS NOT NULL THEN CAST(CAST('inf' AS DOUBLE PRECISION) AS TEXT) ELSE NULL END, CASE WHEN CAST('NaN' AS DOUBLE PRECISION) IS NOT NULL THEN CAST(CAST('NaN' AS DOUBLE PRECISION) AS TEXT) ELSE NULL END) AS "f1", CASE WHEN "b" IS NOT NULL THEN CAST("b" AS TEXT) ELSE NULL END NOT IN (CASE WHEN CAST('-inf' AS DOUBLE PRECISION) IS NOT NULL THEN CAST(CAST('-inf' AS DOUBLE PRECISION) AS TEXT) ELSE NULL END, CASE WHEN CAST('inf' AS DOUBLE PRECISION) IS NOT NULL THEN CAST(CAST('inf' AS DOUBLE PRECISION) AS TEXT) ELSE NULL END, CASE WHEN CAST('NaN' AS DOUBLE PRECISION) IS NOT NULL THEN CAST(CAST('NaN' AS DOUBLE PRECISION) AS TEXT) ELSE NULL END) AS "f2" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b" FROM (VALUES (0, 0.0), (1, -1.5), (2, CAST('-inf' AS DOUBLE)), (3, CAST('inf' AS DOUBLE)), (4, CAST('NaN' AS DOUBLE)))), values1 AS (SELECT "a", "b", (NOT "a" IN (CAST('-inf' AS DOUBLE), CAST('inf' AS DOUBLE), CAST('NaN' AS DOUBLE))) AS "f1", (NOT "b" IN (CAST('-inf' AS DOUBLE), CAST('inf' AS DOUBLE), CAST('NaN' AS DOUBLE))) AS "f2" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+------+------+-------+ | a | b | f1 | f2 | +---+------+------+-------+ | 0 | 0.0 | true | true | | 1 | -1.5 | true | true | | 2 | -inf | true | false | | 3 | inf | true | false | | 4 | NaN | true | false | +---+------+------+-------+ ''' [str_to_utc_timestamp] athena = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", with_timezone(CAST("b" AS TIMESTAMP), 'America/New_York') AT TIME ZONE 'UTC' AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ bigquery = """ WITH values0 AS (SELECT 0 AS `a`, '2022-01-01 12:34:56' AS `b` UNION ALL SELECT 1 AS `a`, '2022-01-02 02:30:01' AS `b` UNION ALL SELECT 2 AS `a`, '2022-01-03 01:42:21' AS `b` UNION ALL SELECT 3 AS `a`, NULL AS `b`), values1 AS (SELECT `a`, `b`, timestamp(`b`, 'America/New_York') AS `b_utc` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ clickhouse = """ WITH values0 AS (SELECT 0 AS "a", '2022-01-01 12:34:56' AS "b" UNION ALL SELECT 1 AS "a", '2022-01-02 02:30:01' AS "b" UNION ALL SELECT 2 AS "a", '2022-01-03 01:42:21' AS "b" UNION ALL SELECT 3 AS "a", NULL AS "b"), values1 AS (SELECT "a", "b", toTimeZone(toDateTime("b", 'America/New_York'), 'UTC') AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ databricks = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL)) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, to_utc_timestamp(CAST(`b` AS TIMESTAMP), 'America/New_York') AS `b_utc` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", str_to_utc_timestamp("b", 'America/New_York') AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ mysql = """ WITH values0 AS (SELECT * FROM (VALUES ROW(0, '2022-01-01 12:34:56'), ROW(1, '2022-01-02 02:30:01'), ROW(2, '2022-01-03 01:42:21'), ROW(3, NULL)) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, convert_tz(timestamp(`b`), 'America/New_York', 'UTC') AS `b_utc` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC """ postgres = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ redshift = """ WITH values0 AS (SELECT 0 AS "a", '2022-01-01 12:34:56' AS "b" UNION ALL SELECT 1 AS "a", '2022-01-02 02:30:01' AS "b" UNION ALL SELECT 2 AS "a", '2022-01-03 01:42:21' AS "b" UNION ALL SELECT 3 AS "a", NULL AS "b"), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b" FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL))), values1 AS (SELECT "a", "b", convert_timezone('America/New_York', 'UTC', CAST("b" AS timestamp_ntz)) AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+---------------------+---------------------+ | a | b | b_utc | +---+---------------------+---------------------+ | 0 | 2022-01-01 12:34:56 | 2022-01-01T17:34:56 | | 1 | 2022-01-02 02:30:01 | 2022-01-02T07:30:01 | | 2 | 2022-01-03 01:42:21 | 2022-01-03T06:42:21 | | 3 | | | +---+---------------------+---------------------+ ''' [test_date_part_tz] athena = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", with_timezone(CAST("b" AS TIMESTAMP), 'America/New_York') AT TIME ZONE 'UTC' AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", EXTRACT(HOUR FROM "b_utc") AS "hours_utc", EXTRACT(HOUR FROM "b_utc" AT TIME ZONE 'America/Los_Angeles') AS "hours_la", EXTRACT(HOUR FROM "b_utc" AT TIME ZONE 'America/New_York') AS "hours_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ bigquery = """ WITH values0 AS (SELECT 0 AS `a`, '2022-01-01 12:34:56' AS `b` UNION ALL SELECT 1 AS `a`, '2022-01-02 02:30:01' AS `b` UNION ALL SELECT 2 AS `a`, '2022-01-03 01:42:21' AS `b` UNION ALL SELECT 3 AS `a`, NULL AS `b`), values1 AS (SELECT `a`, `b`, timestamp(`b`, 'America/New_York') AS `b_utc` FROM values0), values2 AS (SELECT `a`, `b`, `b_utc`, EXTRACT(HOUR FROM `b_utc`) AS `hours_utc`, EXTRACT(HOUR FROM `b_utc` AT TIME ZONE 'America/Los_Angeles') AS `hours_la`, EXTRACT(HOUR FROM `b_utc` AT TIME ZONE 'America/New_York') AS `hours_nyc` FROM values1) SELECT * FROM values2 ORDER BY `a` ASC NULLS FIRST """ clickhouse = """ WITH values0 AS (SELECT 0 AS "a", '2022-01-01 12:34:56' AS "b" UNION ALL SELECT 1 AS "a", '2022-01-02 02:30:01' AS "b" UNION ALL SELECT 2 AS "a", '2022-01-03 01:42:21' AS "b" UNION ALL SELECT 3 AS "a", NULL AS "b"), values1 AS (SELECT "a", "b", toTimeZone(toDateTime("b", 'America/New_York'), 'UTC') AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", toHour(toTimeZone("b_utc", 'UTC')) AS "hours_utc", toHour(toTimeZone("b_utc", 'America/Los_Angeles')) AS "hours_la", toHour(toTimeZone("b_utc", 'America/New_York')) AS "hours_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ databricks = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL)) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, to_utc_timestamp(CAST(`b` AS TIMESTAMP), 'America/New_York') AS `b_utc` FROM values0), values2 AS (SELECT `a`, `b`, `b_utc`, date_part('hour', `b_utc`) AS `hours_utc`, date_part('hour', from_utc_timestamp(`b_utc`, 'America/Los_Angeles')) AS `hours_la`, date_part('hour', from_utc_timestamp(`b_utc`, 'America/New_York')) AS `hours_nyc` FROM values1) SELECT * FROM values2 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", str_to_utc_timestamp("b", 'America/New_York') AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", date_part_tz('hour', "b_utc", 'UTC') AS "hours_utc", date_part_tz('hour', "b_utc", 'America/Los_Angeles') AS "hours_la", date_part_tz('hour', "b_utc", 'America/New_York') AS "hours_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", date_part('hour', "b_utc") AS "hours_utc", date_part('hour', "b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles') AS "hours_la", date_part('hour', "b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York') AS "hours_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ mysql = """ WITH values0 AS (SELECT * FROM (VALUES ROW(0, '2022-01-01 12:34:56'), ROW(1, '2022-01-02 02:30:01'), ROW(2, '2022-01-03 01:42:21'), ROW(3, NULL)) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, convert_tz(timestamp(`b`), 'America/New_York', 'UTC') AS `b_utc` FROM values0), values2 AS (SELECT `a`, `b`, `b_utc`, EXTRACT(HOUR FROM `b_utc`) AS `hours_utc`, EXTRACT(HOUR FROM convert_tz(`b_utc`, 'UTC', 'America/Los_Angeles')) AS `hours_la`, EXTRACT(HOUR FROM convert_tz(`b_utc`, 'UTC', 'America/New_York')) AS `hours_nyc` FROM values1) SELECT * FROM values2 ORDER BY `a` ASC """ postgres = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", date_part('hour', "b_utc") AS "hours_utc", date_part('hour', "b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles') AS "hours_la", date_part('hour', "b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York') AS "hours_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ redshift = """ WITH values0 AS (SELECT 0 AS "a", '2022-01-01 12:34:56' AS "b" UNION ALL SELECT 1 AS "a", '2022-01-02 02:30:01' AS "b" UNION ALL SELECT 2 AS "a", '2022-01-03 01:42:21' AS "b" UNION ALL SELECT 3 AS "a", NULL AS "b"), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", EXTRACT(HOUR FROM "b_utc") AS "hours_utc", EXTRACT(HOUR FROM "b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles') AS "hours_la", EXTRACT(HOUR FROM "b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York') AS "hours_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b" FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL))), values1 AS (SELECT "a", "b", convert_timezone('America/New_York', 'UTC', CAST("b" AS timestamp_ntz)) AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", date_part('hour', "b_utc") AS "hours_utc", date_part('hour', convert_timezone('UTC', 'America/Los_Angeles', "b_utc")) AS "hours_la", date_part('hour', convert_timezone('UTC', 'America/New_York', "b_utc")) AS "hours_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+---------------------+---------------------+-----------+----------+-----------+ | a | b | b_utc | hours_utc | hours_la | hours_nyc | +---+---------------------+---------------------+-----------+----------+-----------+ | 0 | 2022-01-01 12:34:56 | 2022-01-01T17:34:56 | 17.0 | 9.0 | 12.0 | | 1 | 2022-01-02 02:30:01 | 2022-01-02T07:30:01 | 7.0 | 23.0 | 2.0 | | 2 | 2022-01-03 01:42:21 | 2022-01-03T06:42:21 | 6.0 | 22.0 | 1.0 | | 3 | | | | | | +---+---------------------+---------------------+-----------+----------+-----------+ ''' [test_date_trunc_tz] athena = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", with_timezone(CAST("b" AS TIMESTAMP), 'America/New_York') AT TIME ZONE 'UTC' AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", date_trunc('day', "b_utc") AS "day_utc", date_trunc('day', "b_utc" AT TIME ZONE 'America/Los_Angeles') AT TIME ZONE 'UTC' AS "day_la", date_trunc('day', "b_utc" AT TIME ZONE 'America/New_York') AT TIME ZONE 'UTC' AS "day_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ bigquery = """ WITH values0 AS (SELECT 0 AS `a`, '2022-01-01 12:34:56' AS `b` UNION ALL SELECT 1 AS `a`, '2022-01-02 02:30:01' AS `b` UNION ALL SELECT 2 AS `a`, '2022-01-03 01:42:21' AS `b` UNION ALL SELECT 3 AS `a`, NULL AS `b`), values1 AS (SELECT `a`, `b`, timestamp(`b`, 'America/New_York') AS `b_utc` FROM values0), values2 AS (SELECT `a`, `b`, `b_utc`, timestamp_trunc(`b_utc`, day, 'UTC') AS `day_utc`, timestamp_trunc(`b_utc`, day, 'America/Los_Angeles') AS `day_la`, timestamp_trunc(`b_utc`, day, 'America/New_York') AS `day_nyc` FROM values1) SELECT * FROM values2 ORDER BY `a` ASC NULLS FIRST """ clickhouse = """ WITH values0 AS (SELECT 0 AS "a", '2022-01-01 12:34:56' AS "b" UNION ALL SELECT 1 AS "a", '2022-01-02 02:30:01' AS "b" UNION ALL SELECT 2 AS "a", '2022-01-03 01:42:21' AS "b" UNION ALL SELECT 3 AS "a", NULL AS "b"), values1 AS (SELECT "a", "b", toTimeZone(toDateTime("b", 'America/New_York'), 'UTC') AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", toStartOfDay("b_utc", 'UTC') AS "day_utc", toTimeZone(toStartOfDay("b_utc", 'America/Los_Angeles'), 'UTC') AS "day_la", toTimeZone(toStartOfDay("b_utc", 'America/New_York'), 'UTC') AS "day_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ databricks = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL)) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, to_utc_timestamp(CAST(`b` AS TIMESTAMP), 'America/New_York') AS `b_utc` FROM values0), values2 AS (SELECT `a`, `b`, `b_utc`, date_trunc('day', `b_utc`) AS `day_utc`, to_utc_timestamp(date_trunc('day', from_utc_timestamp(`b_utc`, 'America/Los_Angeles')), 'America/Los_Angeles') AS `day_la`, to_utc_timestamp(date_trunc('day', from_utc_timestamp(`b_utc`, 'America/New_York')), 'America/New_York') AS `day_nyc` FROM values1) SELECT * FROM values2 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", str_to_utc_timestamp("b", 'America/New_York') AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", date_trunc('day', "b_utc") AS "day_utc", to_utc_timestamp(date_trunc('day', from_utc_timestamp("b_utc", 'America/Los_Angeles')), 'America/Los_Angeles') AS "day_la", to_utc_timestamp(date_trunc('day', from_utc_timestamp("b_utc", 'America/New_York')), 'America/New_York') AS "day_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", date_trunc('day', "b_utc") AS "day_utc", date_trunc('day', "b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles') AT TIME ZONE 'America/Los_Angeles' AT TIME ZONE 'UTC' AS "day_la", date_trunc('day', "b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York') AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "day_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ mysql = "UNSUPPORTED" postgres = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", date_trunc('day', "b_utc") AS "day_utc", date_trunc('day', "b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles') AT TIME ZONE 'America/Los_Angeles' AT TIME ZONE 'UTC' AS "day_la", date_trunc('day', "b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York') AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "day_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ redshift = """ WITH values0 AS (SELECT 0 AS "a", '2022-01-01 12:34:56' AS "b" UNION ALL SELECT 1 AS "a", '2022-01-02 02:30:01' AS "b" UNION ALL SELECT 2 AS "a", '2022-01-03 01:42:21' AS "b" UNION ALL SELECT 3 AS "a", NULL AS "b"), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", date_trunc('day', "b_utc") AS "day_utc", date_trunc('day', "b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles') AT TIME ZONE 'America/Los_Angeles' AT TIME ZONE 'UTC' AS "day_la", date_trunc('day', "b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York') AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "day_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b" FROM (VALUES (0, '2022-01-01 12:34:56'), (1, '2022-01-02 02:30:01'), (2, '2022-01-03 01:42:21'), (3, NULL))), values1 AS (SELECT "a", "b", convert_timezone('America/New_York', 'UTC', CAST("b" AS timestamp_ntz)) AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", date_trunc('day', "b_utc") AS "day_utc", convert_timezone('America/Los_Angeles', 'UTC', date_trunc('day', convert_timezone('UTC', 'America/Los_Angeles', "b_utc"))) AS "day_la", convert_timezone('America/New_York', 'UTC', date_trunc('day', convert_timezone('UTC', 'America/New_York', "b_utc"))) AS "day_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+---------------------+---------------------+---------------------+---------------------+---------------------+ | a | b | b_utc | day_utc | day_la | day_nyc | +---+---------------------+---------------------+---------------------+---------------------+---------------------+ | 0 | 2022-01-01 12:34:56 | 2022-01-01T17:34:56 | 2022-01-01T00:00:00 | 2022-01-01T08:00:00 | 2022-01-01T05:00:00 | | 1 | 2022-01-02 02:30:01 | 2022-01-02T07:30:01 | 2022-01-02T00:00:00 | 2022-01-01T08:00:00 | 2022-01-02T05:00:00 | | 2 | 2022-01-03 01:42:21 | 2022-01-03T06:42:21 | 2022-01-03T00:00:00 | 2022-01-02T08:00:00 | 2022-01-03T05:00:00 | | 3 | | | | | | +---+---------------------+---------------------+---------------------+---------------------+---------------------+ ''' [test_make_timestamp_tz] athena = "UNSUPPORTED" bigquery = """ WITH values0 AS (SELECT 1 AS `a`, 2001 AS `Y`, 0 AS `M`, 1 AS `d`, 3 AS `h`, 2 AS `min`, 32 AS `s`, 123 AS `ms` UNION ALL SELECT 2 AS `a`, 1984 AS `Y`, 3 AS `M`, 12 AS `d`, 7 AS `h`, 0 AS `min`, 0 AS `s`, 0 AS `ms` UNION ALL SELECT 3 AS `a`, 1968 AS `Y`, 11 AS `M`, 30 AS `d`, 18 AS `h`, 43 AS `min`, 58 AS `s`, 18 AS `ms` UNION ALL SELECT 4 AS `a`, NULL AS `Y`, NULL AS `M`, NULL AS `d`, NULL AS `h`, NULL AS `min`, NULL AS `s`, NULL AS `ms`), values1 AS (SELECT `a`, timestamp(datetime(date(`Y`, CAST((`M` + 1) AS INT), `d`), time_add(time(`h`, `min`, `s`), INTERVAL `ms` MILLISECOND)), 'UTC') AS `ts_utc`, timestamp(datetime(date(`Y`, CAST((`M` + 1) AS INT), `d`), time_add(time(`h`, `min`, `s`), INTERVAL `ms` MILLISECOND)), 'America/New_York') AS `ts_nyc`, timestamp(datetime(date(`Y`, CAST((`M` + 1) AS INT), `d`), time_add(time(`h`, `min`, `s`), INTERVAL `ms` MILLISECOND)), 'America/Los_Angeles') AS `ts_la` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ clickhouse = "UNSUPPORTED" databricks = """ WITH values0 AS (SELECT * FROM (VALUES (1, 2001, 0, 1, 3, 2, 32, 123), (2, 1984, 3, 12, 7, 0, 0, 0), (3, 1968, 11, 30, 18, 43, 58, 18), (4, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) AS `_values` (`a`, `Y`, `M`, `d`, `h`, `min`, `s`, `ms`)), values1 AS (SELECT `a`, dateadd(millisecond, `ms`, make_timestamp(`Y`, CAST((`M` + 1) AS INT), `d`, `h`, `min`, `s`)) AS `ts_utc`, to_utc_timestamp(dateadd(millisecond, `ms`, make_timestamp(`Y`, CAST((`M` + 1) AS INT), `d`, `h`, `min`, `s`)), 'America/New_York') AS `ts_nyc`, to_utc_timestamp(dateadd(millisecond, `ms`, make_timestamp(`Y`, CAST((`M` + 1) AS INT), `d`, `h`, `min`, `s`)), 'America/Los_Angeles') AS `ts_la` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (1, 2001, 0, 1, 3, 2, 32, 123), (2, 1984, 3, 12, 7, 0, 0, 0), (3, 1968, 11, 30, 18, 43, 58, 18), (4, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) AS "_values" ("a", "Y", "M", "d", "h", "min", "s", "ms")), values1 AS (SELECT "a", make_utc_timestamp("Y", "M", "d", "h", "min", "s", "ms", 'UTC') AS "ts_utc", make_utc_timestamp("Y", "M", "d", "h", "min", "s", "ms", 'America/New_York') AS "ts_nyc", make_utc_timestamp("Y", "M", "d", "h", "min", "s", "ms", 'America/Los_Angeles') AS "ts_la" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (1, 2001, 0, 1, 3, 2, 32, 123), (2, 1984, 3, 12, 7, 0, 0, 0), (3, 1968, 11, 30, 18, 43, 58, 18), (4, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) AS "_values" ("a", "Y", "M", "d", "h", "min", "s", "ms")), values1 AS (SELECT "a", make_timestamp("Y", CAST(("M" + 1) AS INT), "d", "h", "min", ("s" + ("ms" / 1000.0))) AS "ts_utc", make_timestamp("Y", CAST(("M" + 1) AS INT), "d", "h", "min", ("s" + ("ms" / 1000.0))) AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "ts_nyc", make_timestamp("Y", CAST(("M" + 1) AS INT), "d", "h", "min", ("s" + ("ms" / 1000.0))) AT TIME ZONE 'America/Los_Angeles' AT TIME ZONE 'UTC' AS "ts_la" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ mysql = "UNSUPPORTED" postgres = """ WITH values0 AS (SELECT * FROM (VALUES (1, 2001, 0, 1, 3, 2, 32, 123), (2, 1984, 3, 12, 7, 0, 0, 0), (3, 1968, 11, 30, 18, 43, 58, 18), (4, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) AS "_values" ("a", "Y", "M", "d", "h", "min", "s", "ms")), values1 AS (SELECT "a", make_timestamptz("Y", CAST(("M" + 1) AS INTEGER), "d", "h", "min", ("s" + ("ms" / 1000.0)), 'UTC') AT TIME ZONE 'UTC' AS "ts_utc", make_timestamptz("Y", CAST(("M" + 1) AS INTEGER), "d", "h", "min", ("s" + ("ms" / 1000.0)), 'America/New_York') AT TIME ZONE 'UTC' AS "ts_nyc", make_timestamptz("Y", CAST(("M" + 1) AS INTEGER), "d", "h", "min", ("s" + ("ms" / 1000.0)), 'America/Los_Angeles') AT TIME ZONE 'UTC' AS "ts_la" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ redshift = "UNSUPPORTED" snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "Y", "COLUMN3" AS "M", "COLUMN4" AS "d", "COLUMN5" AS "h", "COLUMN6" AS "min", "COLUMN7" AS "s", "COLUMN8" AS "ms" FROM (VALUES (1, 2001, 0, 1, 3, 2, 32, 123), (2, 1984, 3, 12, 7, 0, 0, 0), (3, 1968, 11, 30, 18, 43, 58, 18), (4, NULL, NULL, NULL, NULL, NULL, NULL, NULL))), values1 AS (SELECT "a", timestamp_ntz_from_parts("Y", CAST(("M" + 1) AS INTEGER), "d", "h", "min", "s", "ms" * 1000000) AS "ts_utc", convert_timezone('America/New_York', 'UTC', timestamp_ntz_from_parts("Y", CAST(("M" + 1) AS INTEGER), "d", "h", "min", "s", "ms" * 1000000)) AS "ts_nyc", convert_timezone('America/Los_Angeles', 'UTC', timestamp_ntz_from_parts("Y", CAST(("M" + 1) AS INTEGER), "d", "h", "min", "s", "ms" * 1000000)) AS "ts_la" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+-------------------------+-------------------------+-------------------------+ | a | ts_utc | ts_nyc | ts_la | +---+-------------------------+-------------------------+-------------------------+ | 1 | 2001-01-01T03:02:32.123 | 2001-01-01T08:02:32.123 | 2001-01-01T11:02:32.123 | | 2 | 1984-04-12T07:00:00 | 1984-04-12T12:00:00 | 1984-04-12T15:00:00 | | 3 | 1968-12-30T18:43:58.018 | 1968-12-30T23:43:58.018 | 1968-12-31T02:43:58.018 | | 4 | | | | +---+-------------------------+-------------------------+-------------------------+ ''' [test_epoch_to_utc_timestamp] athena = "UNSUPPORTED" bigquery = """ WITH values0 AS (SELECT 1 AS `a`, 1641058496123 AS `t` UNION ALL SELECT 2 AS `a`, 1641108601321 AS `t` UNION ALL SELECT 3 AS `a`, 1641192141999 AS `t` UNION ALL SELECT 4 AS `a`, NULL AS `t`), values1 AS (SELECT `a`, `t`, timestamp_millis(`t`) AS `t_utc` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ clickhouse = "UNSUPPORTED" databricks = """ WITH values0 AS (SELECT * FROM (VALUES (1, 1641058496123), (2, 1641108601321), (3, 1641192141999), (4, NULL)) AS `_values` (`a`, `t`)), values1 AS (SELECT `a`, `t`, dateadd(millisecond, `t` % 1000, from_unixtime(FLOOR(`t` / 1000))) AS `t_utc` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (1, 1641058496123), (2, 1641108601321), (3, 1641192141999), (4, NULL)) AS "_values" ("a", "t")), values1 AS (SELECT "a", "t", epoch_ms_to_utc_timestamp("t") AS "t_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (1, 1641058496123), (2, 1641108601321), (3, 1641192141999), (4, NULL)) AS "_values" ("a", "t")), values1 AS (SELECT "a", "t", epoch_ms("t") AS "t_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ mysql = "UNSUPPORTED" postgres = """ WITH values0 AS (SELECT * FROM (VALUES (1, 1641058496123), (2, 1641108601321), (3, 1641192141999), (4, NULL)) AS "_values" ("a", "t")), values1 AS (SELECT "a", "t", to_timestamp(FLOOR("t" / 1000)) AT TIME ZONE 'UTC' + INTERVAL '1 millisecond' * ("t" % 1000) AS "t_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ redshift = "UNSUPPORTED" snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "t" FROM (VALUES (1, 1641058496123), (2, 1641108601321), (3, 1641192141999), (4, NULL))), values1 AS (SELECT "a", "t", to_timestamp_ntz("t", 3) AS "t_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+---------------+-------------------------+ | a | t | t_utc | +---+---------------+-------------------------+ | 1 | 1641058496123 | 2022-01-01T17:34:56.123 | | 2 | 1641108601321 | 2022-01-02T07:30:01.321 | | 3 | 1641192141999 | 2022-01-03T06:42:21.999 | | 4 | | | +---+---------------+-------------------------+ ''' [test_utc_timestamp_to_epoch_ms] athena = "UNSUPPORTED" bigquery = """ WITH values0 AS (SELECT 1 AS `a`, 1641058496123 AS `t` UNION ALL SELECT 2 AS `a`, 1641108601321 AS `t` UNION ALL SELECT 3 AS `a`, 1641192141999 AS `t` UNION ALL SELECT 4 AS `a`, NULL AS `t`), values1 AS (SELECT `a`, `t`, timestamp_millis(`t`) AS `t_utc` FROM values0), values2 AS (SELECT `a`, `t`, `t_utc`, unix_millis(`t_utc`) AS `epoch_millis` FROM values1) SELECT * FROM values2 ORDER BY `a` ASC NULLS FIRST """ clickhouse = "UNSUPPORTED" databricks = """ WITH values0 AS (SELECT * FROM (VALUES (1, 1641058496123), (2, 1641108601321), (3, 1641192141999), (4, NULL)) AS `_values` (`a`, `t`)), values1 AS (SELECT `a`, `t`, dateadd(millisecond, `t` % 1000, from_unixtime(FLOOR(`t` / 1000))) AS `t_utc` FROM values0), values2 AS (SELECT `a`, `t`, `t_utc`, unix_timestamp(`t_utc`) * 1000 + (date_part('second', `t_utc`) % 1) * 1000 AS `epoch_millis` FROM values1) SELECT * FROM values2 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (1, 1641058496123), (2, 1641108601321), (3, 1641192141999), (4, NULL)) AS "_values" ("a", "t")), values1 AS (SELECT "a", "t", epoch_ms_to_utc_timestamp("t") AS "t_utc" FROM values0), values2 AS (SELECT "a", "t", "t_utc", utc_timestamp_to_epoch_ms("t_utc") AS "epoch_millis" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (1, 1641058496123), (2, 1641108601321), (3, 1641192141999), (4, NULL)) AS "_values" ("a", "t")), values1 AS (SELECT "a", "t", epoch_ms("t") AS "t_utc" FROM values0), values2 AS (SELECT "a", "t", "t_utc", epoch("t_utc") * 1000 + date_part('millisecond', "t_utc") % 1000 AS "epoch_millis" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ mysql = "UNSUPPORTED" postgres = """ WITH values0 AS (SELECT * FROM (VALUES (1, 1641058496123), (2, 1641108601321), (3, 1641192141999), (4, NULL)) AS "_values" ("a", "t")), values1 AS (SELECT "a", "t", to_timestamp(FLOOR("t" / 1000)) AT TIME ZONE 'UTC' + INTERVAL '1 millisecond' * ("t" % 1000) AS "t_utc" FROM values0), values2 AS (SELECT "a", "t", "t_utc", FLOOR(EXTRACT(EPOCH FROM "t_utc") * 1000) AS "epoch_millis" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ redshift = "UNSUPPORTED" snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "t" FROM (VALUES (1, 1641058496123), (2, 1641108601321), (3, 1641192141999), (4, NULL))), values1 AS (SELECT "a", "t", to_timestamp_ntz("t", 3) AS "t_utc" FROM values0), values2 AS (SELECT "a", "t", "t_utc", date_part(epoch_millisecond, "t_utc") AS "epoch_millis" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+---------------+-------------------------+---------------+ | a | t | t_utc | epoch_millis | +---+---------------+-------------------------+---------------+ | 1 | 1641058496123 | 2022-01-01T17:34:56.123 | 1641058496123 | | 2 | 1641108601321 | 2022-01-02T07:30:01.321 | 1641108601321 | | 3 | 1641192141999 | 2022-01-03T06:42:21.999 | 1641192141999 | | 4 | | | | +---+---------------+-------------------------+---------------+ ''' [test_date_add_tz] athena = "UNSUPPORTED" bigquery = """ WITH values0 AS (SELECT 0 AS `a`, '2022-03-01 03:34:56' AS `b` UNION ALL SELECT 1 AS `a`, '2022-04-02 02:30:01' AS `b` UNION ALL SELECT 2 AS `a`, '2022-05-03 01:42:21' AS `b` UNION ALL SELECT 3 AS `a`, NULL AS `b`), values1 AS (SELECT `a`, `b`, timestamp(`b`, 'UTC') AS `b_utc` FROM values0), values2 AS (SELECT `a`, `b`, `b_utc`, timestamp(datetime_add(datetime(`b_utc`, 'UTC'), INTERVAL 1 MONTH), 'UTC') AS `month_utc`, timestamp(datetime_add(datetime(`b_utc`, 'America/New_York'), INTERVAL 1 MONTH), 'America/New_York') AS `month_nyc` FROM values1) SELECT * FROM values2 ORDER BY `a` ASC NULLS FIRST """ clickhouse = "UNSUPPORTED" databricks = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-03-01 03:34:56'), (1, '2022-04-02 02:30:01'), (2, '2022-05-03 01:42:21'), (3, NULL)) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, to_utc_timestamp(CAST(`b` AS TIMESTAMP), 'UTC') AS `b_utc` FROM values0), values2 AS (SELECT `a`, `b`, `b_utc`, dateadd(month, 1, `b_utc`) AS `month_utc`, to_utc_timestamp(dateadd(month, 1, from_utc_timestamp(`b_utc`, 'America/New_York')), 'America/New_York') AS `month_nyc` FROM values1) SELECT * FROM values2 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-03-01 03:34:56'), (1, '2022-04-02 02:30:01'), (2, '2022-05-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", str_to_utc_timestamp("b", 'UTC') AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", "b_utc" + INTERVAL '1' MONTH AS "month_utc", to_utc_timestamp(from_utc_timestamp("b_utc", 'America/New_York') + INTERVAL '1' MONTH, 'America/New_York') AS "month_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-03-01 03:34:56'), (1, '2022-04-02 02:30:01'), (2, '2022-05-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", ("b_utc") + INTERVAL '1 month' AS "month_utc", (("b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York') + INTERVAL '1 month') AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "month_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ mysql = "UNSUPPORTED" postgres = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-03-01 03:34:56'), (1, '2022-04-02 02:30:01'), (2, '2022-05-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", ("b_utc") + INTERVAL '1 month' AS "month_utc", (("b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York') + INTERVAL '1 month') AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "month_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ redshift = "UNSUPPORTED" snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b" FROM (VALUES (0, '2022-03-01 03:34:56'), (1, '2022-04-02 02:30:01'), (2, '2022-05-03 01:42:21'), (3, NULL))), values1 AS (SELECT "a", "b", CAST("b" AS timestamp_ntz) AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", timestampadd(month, 1, "b_utc") AS "month_utc", convert_timezone('America/New_York', 'UTC', timestampadd(month, 1, convert_timezone('UTC', 'America/New_York', "b_utc"))) AS "month_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+---------------------+---------------------+---------------------+---------------------+ | a | b | b_utc | month_utc | month_nyc | +---+---------------------+---------------------+---------------------+---------------------+ | 0 | 2022-03-01 03:34:56 | 2022-03-01T03:34:56 | 2022-04-01T03:34:56 | 2022-03-29T02:34:56 | | 1 | 2022-04-02 02:30:01 | 2022-04-02T02:30:01 | 2022-05-02T02:30:01 | 2022-05-02T02:30:01 | | 2 | 2022-05-03 01:42:21 | 2022-05-03T01:42:21 | 2022-06-03T01:42:21 | 2022-06-03T01:42:21 | | 3 | | | | | +---+---------------------+---------------------+---------------------+---------------------+ ''' [test_utc_timestamp_to_str] athena = "UNSUPPORTED" bigquery = """ WITH values0 AS (SELECT 0 AS `a`, '2022-03-01 03:34:56.123' AS `b` UNION ALL SELECT 1 AS `a`, '2022-04-02 02:30:01.321' AS `b` UNION ALL SELECT 2 AS `a`, '2022-05-03 01:42:21' AS `b` UNION ALL SELECT 3 AS `a`, NULL AS `b`), values1 AS (SELECT `a`, `b`, timestamp(`b`, 'UTC') AS `b_utc` FROM values0), values2 AS (SELECT `a`, `b`, `b_utc`, format_datetime('%Y-%m-%dT%H:%M:%E3S', datetime(`b_utc`, 'UTC')) AS `str_utc`, format_datetime('%Y-%m-%dT%H:%M:%E3S', datetime(`b_utc`, 'America/New_York')) AS `str_nyc` FROM values1) SELECT * FROM values2 ORDER BY `a` ASC NULLS FIRST """ clickhouse = "UNSUPPORTED" databricks = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-03-01 03:34:56.123'), (1, '2022-04-02 02:30:01.321'), (2, '2022-05-03 01:42:21'), (3, NULL)) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, to_utc_timestamp(CAST(`b` AS TIMESTAMP), 'UTC') AS `b_utc` FROM values0), values2 AS (SELECT `a`, `b`, `b_utc`, replace(date_format(`b_utc`, 'y-MM-dd HH:mm:ss.SSS'), ' ', 'T') AS `str_utc`, replace(date_format(from_utc_timestamp(`b_utc`, 'America/New_York'), 'y-MM-dd HH:mm:ss.SSS'), ' ', 'T') AS `str_nyc` FROM values1) SELECT * FROM values2 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-03-01 03:34:56.123'), (1, '2022-04-02 02:30:01.321'), (2, '2022-05-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", str_to_utc_timestamp("b", 'UTC') AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", utc_timestamp_to_str("b_utc", 'UTC') AS "str_utc", utc_timestamp_to_str("b_utc", 'America/New_York') AS "str_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-03-01 03:34:56.123'), (1, '2022-04-02 02:30:01.321'), (2, '2022-05-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", strftime("b_utc", '%Y-%m-%dT%H:%M:%S.%g') AS "str_utc", strftime("b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York', '%Y-%m-%dT%H:%M:%S.%g') AS "str_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ mysql = "UNSUPPORTED" postgres = """ WITH values0 AS (SELECT * FROM (VALUES (0, '2022-03-01 03:34:56.123'), (1, '2022-04-02 02:30:01.321'), (2, '2022-05-03 01:42:21'), (3, NULL)) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", to_char("b_utc", 'YYYY-MM-DD"T"HH24:MI:SS.MS') AS "str_utc", to_char("b_utc" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York', 'YYYY-MM-DD"T"HH24:MI:SS.MS') AS "str_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ redshift = "UNSUPPORTED" snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b" FROM (VALUES (0, '2022-03-01 03:34:56.123'), (1, '2022-04-02 02:30:01.321'), (2, '2022-05-03 01:42:21'), (3, NULL))), values1 AS (SELECT "a", "b", CAST("b" AS timestamp_ntz) AS "b_utc" FROM values0), values2 AS (SELECT "a", "b", "b_utc", to_varchar("b_utc", 'YYYY-MM-DD"T"HH24:MI:SS.FF3') AS "str_utc", to_varchar(convert_timezone('UTC', 'America/New_York', "b_utc"), 'YYYY-MM-DD"T"HH24:MI:SS.FF3') AS "str_nyc" FROM values1) SELECT * FROM values2 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+-------------------------+-------------------------+-------------------------+-------------------------+ | a | b | b_utc | str_utc | str_nyc | +---+-------------------------+-------------------------+-------------------------+-------------------------+ | 0 | 2022-03-01 03:34:56.123 | 2022-03-01T03:34:56.123 | 2022-03-01T03:34:56.123 | 2022-02-28T22:34:56.123 | | 1 | 2022-04-02 02:30:01.321 | 2022-04-02T02:30:01.321 | 2022-04-02T02:30:01.321 | 2022-04-01T22:30:01.321 | | 2 | 2022-05-03 01:42:21 | 2022-05-03T01:42:21 | 2022-05-03T01:42:21.000 | 2022-05-02T21:42:21.000 | | 3 | | | | | +---+-------------------------+-------------------------+-------------------------+-------------------------+ ''' [date_to_utc_timestamp] athena = """ WITH values0 AS (SELECT * FROM (VALUES (0, CAST('1998-12-20' AS DATE)), (1, CAST('2000-01-24' AS DATE)), (2, CAST('2000-02-13' AS DATE)), (3, CAST('2002-11-09' AS DATE))) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ bigquery = """ WITH values0 AS (SELECT 0 AS `a`, CAST('1998-12-20' AS DATE) AS `b` UNION ALL SELECT 1 AS `a`, CAST('2000-01-24' AS DATE) AS `b` UNION ALL SELECT 2 AS `a`, CAST('2000-02-13' AS DATE) AS `b` UNION ALL SELECT 3 AS `a`, CAST('2002-11-09' AS DATE) AS `b`), values1 AS (SELECT `a`, `b`, timestamp(`b`, 'America/New_York') AS `b_utc` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ clickhouse = """ WITH values0 AS (SELECT 0 AS "a", CAST('1998-12-20' AS DATE) AS "b" UNION ALL SELECT 1 AS "a", CAST('2000-01-24' AS DATE) AS "b" UNION ALL SELECT 2 AS "a", CAST('2000-02-13' AS DATE) AS "b" UNION ALL SELECT 3 AS "a", CAST('2002-11-09' AS DATE) AS "b"), values1 AS (SELECT "a", "b", toTimeZone(toDateTime("b", 'America/New_York'), 'UTC') AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ databricks = """ WITH values0 AS (SELECT * FROM (VALUES (0, CAST('1998-12-20' AS DATE)), (1, CAST('2000-01-24' AS DATE)), (2, CAST('2000-02-13' AS DATE)), (3, CAST('2002-11-09' AS DATE))) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, to_utc_timestamp(CAST(`b` AS TIMESTAMP), 'America/New_York') AS `b_utc` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (0, CAST('1998-12-20' AS DATE)), (1, CAST('2000-01-24' AS DATE)), (2, CAST('2000-02-13' AS DATE)), (3, CAST('2002-11-09' AS DATE))) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", date_to_utc_timestamp("b", 'America/New_York') AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (0, CAST('1998-12-20' AS DATE)), (1, CAST('2000-01-24' AS DATE)), (2, CAST('2000-02-13' AS DATE)), (3, CAST('2002-11-09' AS DATE))) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ mysql = """ WITH values0 AS (SELECT * FROM (VALUES ROW(0, CAST('1998-12-20' AS DATE)), ROW(1, CAST('2000-01-24' AS DATE)), ROW(2, CAST('2000-02-13' AS DATE)), ROW(3, CAST('2002-11-09' AS DATE))) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, convert_tz(timestamp(`b`), 'America/New_York', 'UTC') AS `b_utc` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC """ postgres = """ WITH values0 AS (SELECT * FROM (VALUES (0, CAST('1998-12-20' AS DATE)), (1, CAST('2000-01-24' AS DATE)), (2, CAST('2000-02-13' AS DATE)), (3, CAST('2002-11-09' AS DATE))) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ redshift = """ WITH values0 AS (SELECT 0 AS "a", CAST('1998-12-20' AS DATE) AS "b" UNION ALL SELECT 1 AS "a", CAST('2000-01-24' AS DATE) AS "b" UNION ALL SELECT 2 AS "a", CAST('2000-02-13' AS DATE) AS "b" UNION ALL SELECT 3 AS "a", CAST('2002-11-09' AS DATE) AS "b"), values1 AS (SELECT "a", "b", CAST("b" AS TIMESTAMP) AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b" FROM (VALUES (0, CAST('1998-12-20' AS DATE)), (1, CAST('2000-01-24' AS DATE)), (2, CAST('2000-02-13' AS DATE)), (3, CAST('2002-11-09' AS DATE)))), values1 AS (SELECT "a", "b", convert_timezone('America/New_York', 'UTC', CAST("b" AS timestamp_ntz)) AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+------------+---------------------+ | a | b | b_utc | +---+------------+---------------------+ | 0 | 1998-12-20 | 1998-12-20T05:00:00 | | 1 | 2000-01-24 | 2000-01-24T05:00:00 | | 2 | 2000-02-13 | 2000-02-13T05:00:00 | | 3 | 2002-11-09 | 2002-11-09T05:00:00 | +---+------------+---------------------+ ''' [to_utc_timestamp] athena = "UNSUPPORTED" bigquery = """ WITH values0 AS (SELECT 0 AS `a`, timestamp_millis(1641058496123) AS `b` UNION ALL SELECT 1 AS `a`, timestamp_millis(1641108601321) AS `b` UNION ALL SELECT 2 AS `a`, timestamp_millis(1641192141999) AS `b`), values1 AS (SELECT `a`, `b`, timestamp(datetime(`b`), 'America/New_York') AS `b_utc` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ clickhouse = "UNSUPPORTED" databricks = """ WITH values0 AS (SELECT * FROM (VALUES (0, dateadd(millisecond, 1641058496123 % 1000, from_unixtime(floor(1641058496123 / 1000)))), (1, dateadd(millisecond, 1641108601321 % 1000, from_unixtime(floor(1641108601321 / 1000)))), (2, dateadd(millisecond, 1641192141999 % 1000, from_unixtime(floor(1641192141999 / 1000))))) AS `_values` (`a`, `b`)), values1 AS (SELECT `a`, `b`, to_utc_timestamp(`b`, 'America/New_York') AS `b_utc` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (0, epoch_ms_to_utc_timestamp(1641058496123)), (1, epoch_ms_to_utc_timestamp(1641108601321)), (2, epoch_ms_to_utc_timestamp(1641192141999))) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", to_utc_timestamp("b", 'America/New_York') AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (0, epoch_ms(1641058496123)), (1, epoch_ms(1641108601321)), (2, epoch_ms(1641192141999))) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", "b" AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ mysql = "UNSUPPORTED" postgres = """ WITH values0 AS (SELECT * FROM (VALUES (0, to_timestamp(floor(1641058496123 / 1000)) AT TIME ZONE 'UTC' + INTERVAL '1 millisecond' * (1641058496123 % 1000)), (1, to_timestamp(floor(1641108601321 / 1000)) AT TIME ZONE 'UTC' + INTERVAL '1 millisecond' * (1641108601321 % 1000)), (2, to_timestamp(floor(1641192141999 / 1000)) AT TIME ZONE 'UTC' + INTERVAL '1 millisecond' * (1641192141999 % 1000))) AS "_values" ("a", "b")), values1 AS (SELECT "a", "b", "b" AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC' AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ redshift = "UNSUPPORTED" snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b" FROM (VALUES (0, to_timestamp_ntz(1641058496123, 3)), (1, to_timestamp_ntz(1641108601321, 3)), (2, to_timestamp_ntz(1641192141999, 3)))), values1 AS (SELECT "a", "b", convert_timezone('America/New_York', 'UTC', "b") AS "b_utc" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+-------------------------+-------------------------+ | a | b | b_utc | +---+-------------------------+-------------------------+ | 0 | 2022-01-01T17:34:56.123 | 2022-01-01T22:34:56.123 | | 1 | 2022-01-02T07:30:01.321 | 2022-01-02T12:30:01.321 | | 2 | 2022-01-03T06:42:21.999 | 2022-01-03T11:42:21.999 | +---+-------------------------+-------------------------+ ''' [test_string_ops] athena = """ WITH values0 AS (SELECT * FROM (VALUES (0, '1234', 'efGH'), (1, 'abCD', '5678'), (3, NULL, NULL)) AS "_values" ("a", "b", "c")), values1 AS (SELECT "a", "b", "c", substr("b", 2, 2) AS "b_substr", concat("b", ' ', "c") AS "bc_concat", upper("b") AS "b_upper", lower("b") AS "b_lower" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ bigquery = """ WITH values0 AS (SELECT 0 AS `a`, '1234' AS `b`, 'efGH' AS `c` UNION ALL SELECT 1 AS `a`, 'abCD' AS `b`, '5678' AS `c` UNION ALL SELECT 3 AS `a`, NULL AS `b`, NULL AS `c`), values1 AS (SELECT `a`, `b`, `c`, substr(`b`, 2, 2) AS `b_substr`, concat(`b`, ' ', `c`) AS `bc_concat`, upper(`b`) AS `b_upper`, lower(`b`) AS `b_lower` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ clickhouse = """ WITH values0 AS (SELECT 0 AS "a", '1234' AS "b", 'efGH' AS "c" UNION ALL SELECT 1 AS "a", 'abCD' AS "b", '5678' AS "c" UNION ALL SELECT 3 AS "a", NULL AS "b", NULL AS "c"), values1 AS (SELECT "a", "b", "c", substr("b", 2, 2) AS "b_substr", concat("b", ' ', "c") AS "bc_concat", upper("b") AS "b_upper", lower("b") AS "b_lower" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ databricks = """ WITH values0 AS (SELECT * FROM (VALUES (0, '1234', 'efGH'), (1, 'abCD', '5678'), (3, NULL, NULL)) AS `_values` (`a`, `b`, `c`)), values1 AS (SELECT `a`, `b`, `c`, substr(`b`, 2, 2) AS `b_substr`, concat(`b`, ' ', `c`) AS `bc_concat`, upper(`b`) AS `b_upper`, lower(`b`) AS `b_lower` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC NULLS FIRST """ datafusion = """ WITH values0 AS (SELECT * FROM (VALUES (0, '1234', 'efGH'), (1, 'abCD', '5678'), (3, NULL, NULL)) AS "_values" ("a", "b", "c")), values1 AS (SELECT "a", "b", "c", substr("b", 2, 2) AS "b_substr", concat("b", ' ', "c") AS "bc_concat", upper("b") AS "b_upper", lower("b") AS "b_lower" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ duckdb = """ WITH values0 AS (SELECT * FROM (VALUES (0, '1234', 'efGH'), (1, 'abCD', '5678'), (3, NULL, NULL)) AS "_values" ("a", "b", "c")), values1 AS (SELECT "a", "b", "c", substr("b", 2, 2) AS "b_substr", concat("b", ' ', "c") AS "bc_concat", upper("b") AS "b_upper", lower("b") AS "b_lower" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ mysql = """ WITH values0 AS (SELECT * FROM (VALUES ROW(0, '1234', 'efGH'), ROW(1, 'abCD', '5678'), ROW(3, NULL, NULL)) AS `_values` (`a`, `b`, `c`)), values1 AS (SELECT `a`, `b`, `c`, substr(`b`, 2, 2) AS `b_substr`, concat(`b`, ' ', `c`) AS `bc_concat`, upper(`b`) AS `b_upper`, lower(`b`) AS `b_lower` FROM values0) SELECT * FROM values1 ORDER BY `a` ASC """ postgres = """ WITH values0 AS (SELECT * FROM (VALUES (0, '1234', 'efGH'), (1, 'abCD', '5678'), (3, NULL, NULL)) AS "_values" ("a", "b", "c")), values1 AS (SELECT "a", "b", "c", substr("b", 2, 2) AS "b_substr", concat("b", ' ', "c") AS "bc_concat", upper("b") AS "b_upper", lower("b") AS "b_lower" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ redshift = "UNSUPPORTED" snowflake = """ WITH values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b", "COLUMN3" AS "c" FROM (VALUES (0, '1234', 'efGH'), (1, 'abCD', '5678'), (3, NULL, NULL))), values1 AS (SELECT "a", "b", "c", substr("b", 2, 2) AS "b_substr", concat("b", ' ', "c") AS "bc_concat", upper("b") AS "b_upper", lower("b") AS "b_lower" FROM values0) SELECT * FROM values1 ORDER BY "a" ASC NULLS FIRST """ result = ''' +---+------+------+----------+-----------+---------+---------+ | a | b | c | b_substr | bc_concat | b_upper | b_lower | +---+------+------+----------+-----------+---------+---------+ | 0 | 1234 | efGH | 23 | 1234 efGH | 1234 | 1234 | | 1 | abCD | 5678 | bC | abCD 5678 | ABCD | abcd | | 3 | | | | | | | +---+------+------+----------+-----------+---------+---------+ '''