diff options
Diffstat (limited to '')
| -rw-r--r-- | tests/fixtures/optimizer/annotate_functions.sql | 317 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/merge_subqueries.sql | 18 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/qualify_columns.sql | 11 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/qualify_tables.sql | 23 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/simplify.sql | 6 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 68 |
6 files changed, 409 insertions, 34 deletions
diff --git a/tests/fixtures/optimizer/annotate_functions.sql b/tests/fixtures/optimizer/annotate_functions.sql new file mode 100644 index 0000000..1dd7c2d --- /dev/null +++ b/tests/fixtures/optimizer/annotate_functions.sql @@ -0,0 +1,317 @@ +-------------------------------------- +-- Dialect +-------------------------------------- +ABS(1); +INT; + +ABS(1.5); +DOUBLE; + +GREATEST(1, 2, 3); +INT; + +GREATEST(1, 2.5, 3); +DOUBLE; + +LEAST(1, 2, 3); +INT; + +LEAST(1, 2.5, 3); +DOUBLE; + +-------------------------------------- +-- Spark2 / Spark3 / Databricks +-------------------------------------- + +# dialect: spark2, spark, databricks +SUBSTRING(tbl.str_col, 0, 0); +STRING; + +# dialect: spark2, spark, databricks +SUBSTRING(tbl.bin_col, 0, 0); +BINARY; + +# dialect: spark2, spark, databricks +CONCAT(tbl.bin_col, tbl.bin_col); +BINARY; + +# dialect: spark2, spark, databricks +CONCAT(tbl.bin_col, tbl.str_col); +STRING; + +# dialect: spark2, spark, databricks +CONCAT(tbl.str_col, tbl.bin_col); +STRING; + +# dialect: spark2, spark, databricks +CONCAT(tbl.str_col, tbl.str_col); +STRING; + +# dialect: spark2, spark, databricks +CONCAT(tbl.str_col, unknown); +STRING; + +# dialect: spark2, spark, databricks +CONCAT(tbl.bin_col, unknown); +UNKNOWN; + +# dialect: spark2, spark, databricks +CONCAT(unknown, unknown); +UNKNOWN; + +# dialect: spark2, spark, databricks +LPAD(tbl.bin_col, 1, tbl.bin_col); +BINARY; + +# dialect: spark2, spark, databricks +RPAD(tbl.bin_col, 1, tbl.bin_col); +BINARY; + +# dialect: spark2, spark, databricks +LPAD(tbl.bin_col, 1, tbl.str_col); +STRING; + +# dialect: spark2, spark, databricks +RPAD(tbl.bin_col, 1, tbl.str_col); +STRING; + +# dialect: spark2, spark, databricks +LPAD(tbl.str_col, 1, tbl.bin_col); +STRING; + +# dialect: spark2, spark, databricks +RPAD(tbl.str_col, 1, tbl.bin_col); +STRING; + +# dialect: spark2, spark, databricks +LPAD(tbl.str_col, 1, tbl.str_col); +STRING; + +# dialect: spark2, spark, databricks +RPAD(tbl.str_col, 1, tbl.str_col); +STRING; + + +-------------------------------------- +-- BigQuery +-------------------------------------- + +# dialect: bigquery +SIGN(1); +INT; + +# dialect: bigquery +SIGN(1.5); +DOUBLE; + +# dialect: bigquery +CEIL(1); +DOUBLE; + +# dialect: bigquery +CEIL(5.5); +DOUBLE; + +# dialect: bigquery +CEIL(tbl.bignum_col); +BIGDECIMAL; + +# dialect: bigquery +FLOOR(1); +DOUBLE; + +# dialect: bigquery +FLOOR(5.5); +DOUBLE; + +# dialect: bigquery +FLOOR(tbl.bignum_col); +BIGDECIMAL; + +# dialect: bigquery +SQRT(1); +DOUBLE; + +# dialect: bigquery +SQRT(5.5); +DOUBLE; + +# dialect: bigquery +SQRT(tbl.bignum_col); +BIGDECIMAL; + +# dialect: bigquery +LN(1); +DOUBLE; + +# dialect: bigquery +LN(5.5); +DOUBLE; + +# dialect: bigquery +LN(tbl.bignum_col); +BIGDECIMAL; + +# dialect: bigquery +LOG(1); +DOUBLE; + +# dialect: bigquery +LOG(5.5); +DOUBLE; + +# dialect: bigquery +LOG(tbl.bignum_col); +BIGDECIMAL; + +# dialect: bigquery +ROUND(1); +DOUBLE; + +# dialect: bigquery +ROUND(5.5); +DOUBLE; + +# dialect: bigquery +ROUND(tbl.bignum_col); +BIGDECIMAL; + +# dialect: bigquery +EXP(1); +DOUBLE; + +# dialect: bigquery +EXP(5.5); +DOUBLE; + +# dialect: bigquery +EXP(tbl.bignum_col); +BIGDECIMAL; + +# dialect: bigquery +CONCAT(tbl.str_col, tbl.str_col); +STRING; + +# dialect: bigquery +CONCAT(tbl.bin_col, tbl.bin_col); +BINARY; + +# dialect: bigquery +LEFT(tbl.str_col, 1); +STRING; + +# dialect: bigquery +LEFT(tbl.bin_col, 1); +BINARY; + +# dialect: bigquery +RIGHT(tbl.str_col, 1); +STRING; + +# dialect: bigquery +RIGHT(tbl.bin_col, 1); +BINARY; + +# dialect: bigquery +LOWER(tbl.str_col); +STRING; + +# dialect: bigquery +LOWER(tbl.bin_col); +BINARY; + +# dialect: bigquery +UPPER(tbl.str_col); +STRING; + +# dialect: bigquery +UPPER(tbl.bin_col); +BINARY; + +# dialect: bigquery +LPAD(tbl.str_col, 1, tbl.str_col); +STRING; + +# dialect: bigquery +LPAD(tbl.bin_col, 1, tbl.bin_col); +BINARY; + +# dialect: bigquery +RPAD(tbl.str_col, 1, tbl.str_col); +STRING; + +# dialect: bigquery +RPAD(tbl.bin_col, 1, tbl.bin_col); +BINARY; + +# dialect: bigquery +LTRIM(tbl.str_col); +STRING; + +# dialect: bigquery +LTRIM(tbl.bin_col, tbl.bin_col); +BINARY; + +# dialect: bigquery +RTRIM(tbl.str_col); +STRING; + +# dialect: bigquery +RTRIM(tbl.bin_col, tbl.bin_col); +BINARY; + +# dialect: bigquery +TRIM(tbl.str_col); +STRING; + +# dialect: bigquery +TRIM(tbl.bin_col, tbl.bin_col); +BINARY; + +# dialect: bigquery +REGEXP_EXTRACT(tbl.str_col, pattern); +STRING; + +# dialect: bigquery +REGEXP_EXTRACT(tbl.bin_col, pattern); +BINARY; + +# dialect: bigquery +REGEXP_REPLACE(tbl.str_col, pattern, replacement); +STRING; + +# dialect: bigquery +REGEXP_REPLACE(tbl.bin_col, pattern, replacement); +BINARY; + +# dialect: bigquery +REPEAT(tbl.str_col, 1); +STRING; + +# dialect: bigquery +REPEAT(tbl.bin_col, 1); +BINARY; + +# dialect: bigquery +SUBSTRING(tbl.str_col, 1); +STRING; + +# dialect: bigquery +SUBSTRING(tbl.bin_col, 1); +BINARY; + +# dialect: bigquery +SPLIT(tbl.str_col, delim); +ARRAY<STRING>; + +# dialect: bigquery +SPLIT(tbl.bin_col, delim); +ARRAY<BINARY>; + +# dialect: bigquery +STRING(json_expr); +STRING; + +# dialect: bigquery +STRING(timestamp_expr, timezone); +STRING;
\ No newline at end of file diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql index ce5a435..e39e7d1 100644 --- a/tests/fixtures/optimizer/merge_subqueries.sql +++ b/tests/fixtures/optimizer/merge_subqueries.sql @@ -446,3 +446,21 @@ SELECT 1 AS a; WITH q AS (SELECT x.a AS a FROM x AS x ORDER BY x.a) SELECT q.a AS a FROM q AS q UNION ALL SELECT 1 AS a; +# title: Consecutive inner - outer conflicting names +WITH tbl AS (select 1 as id) +SELECT + id +FROM ( + SELECT OTBL.id + FROM ( + SELECT OTBL.id + FROM ( + SELECT OTBL.id + FROM tbl AS OTBL + LEFT OUTER JOIN tbl AS ITBL ON OTBL.id = ITBL.id + ) AS OTBL + LEFT OUTER JOIN tbl AS ITBL ON OTBL.id = ITBL.id + ) AS OTBL + LEFT OUTER JOIN tbl AS ITBL ON OTBL.id = ITBL.id +) AS ITBL; +WITH tbl AS (SELECT 1 AS id) SELECT OTBL.id AS id FROM tbl AS OTBL LEFT OUTER JOIN tbl AS ITBL_2 ON OTBL.id = ITBL_2.id LEFT OUTER JOIN tbl AS ITBL_3 ON OTBL.id = ITBL_3.id LEFT OUTER JOIN tbl AS ITBL ON OTBL.id = ITBL.id; diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index 68c0caa..7c901ce 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -190,6 +190,17 @@ SELECT x._col_0 AS _col_0, x._col_1 AS _col_1 FROM (VALUES (1, 2)) AS x(_col_0, SELECT SOME_UDF(data).* FROM t; SELECT SOME_UDF(t.data).* FROM t AS t; +# execute: false +# allow_partial_qualification: true +# validate_qualify_columns: false +SELECT a + 1 AS i, missing_column FROM x; +SELECT x.a + 1 AS i, missing_column AS missing_column FROM x AS x; + +# execute: false +# dialect: clickhouse +SELECT s, arr1, arr2 FROM arrays_test LEFT ARRAY JOIN arr1, arrays_test.arr2; +SELECT arrays_test.s AS s, arrays_test.arr1 AS arr1, arrays_test.arr2 AS arr2 FROM arrays_test AS arrays_test LEFT ARRAY JOIN arrays_test.arr1, arrays_test.arr2; + -------------------------------------- -- Derived tables -------------------------------------- diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql index 61d0b96..03e8dbe 100644 --- a/tests/fixtures/optimizer/qualify_tables.sql +++ b/tests/fixtures/optimizer/qualify_tables.sql @@ -14,6 +14,26 @@ SELECT 1 FROM x.y.z AS z; SELECT 1 FROM x.y.z AS z; SELECT 1 FROM x.y.z AS z; +# title: only information schema +# dialect: bigquery +SELECT * FROM information_schema.tables; +SELECT * FROM c.db.`information_schema.tables` AS tables; + +# title: information schema with db +# dialect: bigquery +SELECT * FROM y.information_schema.tables; +SELECT * FROM c.y.`information_schema.tables` AS tables; + +# title: information schema with db, catalog +# dialect: bigquery +SELECT * FROM x.y.information_schema.tables; +SELECT * FROM x.y.`information_schema.tables` AS tables; + +# title: information schema with db, catalog, alias +# dialect: bigquery +SELECT * FROM x.y.information_schema.tables AS z; +SELECT * FROM x.y.`information_schema.tables` AS z; + # title: redshift unnest syntax, z.a should be a column, not a table # dialect: redshift SELECT 1 FROM y.z AS z, z.a; @@ -184,3 +204,6 @@ COPY INTO (SELECT * FROM c.db.x AS x) TO 'data' WITH (FORMAT 'CSV'); # title: tablesample SELECT 1 FROM x TABLESAMPLE SYSTEM (10 PERCENT) CROSS JOIN y TABLESAMPLE SYSTEM (10 PERCENT); SELECT 1 FROM c.db.x AS x TABLESAMPLE SYSTEM (10 PERCENT) CROSS JOIN c.db.y AS y TABLESAMPLE SYSTEM (10 PERCENT); + +WITH cte_tbl AS (SELECT 1 AS col2) UPDATE y SET col1 = (SELECT * FROM x) WHERE EXISTS(SELECT 1 FROM cte_tbl); +WITH cte_tbl AS (SELECT 1 AS col2) UPDATE c.db.y SET col1 = (SELECT * FROM c.db.x AS x) WHERE EXISTS(SELECT 1 FROM cte_tbl AS cte_tbl); diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index 1f60741..1842e55 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -137,6 +137,12 @@ FALSE; TRUE AND TRUE OR TRUE AND FALSE; TRUE; +COALESCE(x, y) <> ALL (SELECT z FROM w); +COALESCE(x, y) <> ALL (SELECT z FROM w); + +SELECT NOT (2 <> ALL (SELECT 2 UNION ALL SELECT 3)); +SELECT 2 = ANY(SELECT 2 UNION ALL SELECT 3); + -------------------------------------- -- Absorption -------------------------------------- diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index 7bbeb27..290d276 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -991,9 +991,9 @@ FROM store_sales, date_dim, store, (SELECT ca_zip - FROM (SELECT Substr(ca_zip, 1, 5) ca_zip + FROM (SELECT SUBSTRING(ca_zip, 1, 5) ca_zip FROM customer_address - WHERE Substr(ca_zip, 1, 5) IN ( '67436', '26121', '38443', + WHERE SUBSTRING(ca_zip, 1, 5) IN ( '67436', '26121', '38443', '63157', '68856', '19485', '86425', '26741', @@ -1195,7 +1195,7 @@ FROM store_sales, '92564' ) INTERSECT SELECT ca_zip - FROM (SELECT Substr(ca_zip, 1, 5) ca_zip, + FROM (SELECT SUBSTRING(ca_zip, 1, 5) ca_zip, Count(*) cnt FROM customer_address, customer @@ -1207,13 +1207,13 @@ WHERE ss_store_sk = s_store_sk AND ss_sold_date_sk = d_date_sk AND d_qoy = 2 AND d_year = 2000 - AND ( Substr(s_zip, 1, 2) = Substr(V1.ca_zip, 1, 2) ) + AND ( SUBSTRING(s_zip, 1, 2) = SUBSTRING(V1.ca_zip, 1, 2) ) GROUP BY s_store_name ORDER BY s_store_name LIMIT 100; WITH "a1" AS ( SELECT - SUBSTR("customer_address"."ca_zip", 1, 5) AS "ca_zip" + SUBSTRING("customer_address"."ca_zip", 1, 5) AS "ca_zip" FROM "customer_address" AS "customer_address" JOIN "customer" AS "customer" ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" @@ -1224,10 +1224,10 @@ WITH "a1" AS ( COUNT(*) > 10 ), "a2" AS ( SELECT - SUBSTR("customer_address"."ca_zip", 1, 5) AS "ca_zip" + SUBSTRING("customer_address"."ca_zip", 1, 5) AS "ca_zip" FROM "customer_address" AS "customer_address" WHERE - SUBSTR("customer_address"."ca_zip", 1, 5) IN ('67436', '26121', '38443', '63157', '68856', '19485', '86425', '26741', '70991', '60899', '63573', '47556', '56193', '93314', '87827', '62017', '85067', '95390', '48091', '10261', '81845', '41790', '42853', '24675', '12840', '60065', '84430', '57451', '24021', '91735', '75335', '71935', '34482', '56943', '70695', '52147', '56251', '28411', '86653', '23005', '22478', '29031', '34398', '15365', '42460', '33337', '59433', '73943', '72477', '74081', '74430', '64605', '39006', '11226', '49057', '97308', '42663', '18187', '19768', '43454', '32147', '76637', '51975', '11181', '45630', '33129', '45995', '64386', '55522', '26697', '20963', '35154', '64587', '49752', '66386', '30586', '59286', '13177', '66646', '84195', '74316', '36853', '32927', '12469', '11904', '36269', '17724', '55346', '12595', '53988', '65439', '28015', '63268', '73590', '29216', '82575', '69267', '13805', '91678', '79460', '94152', '14961', '15419', '48277', '62588', '55493', '28360', '14152', '55225', '18007', '53705', '56573', '80245', '71769', '57348', '36845', '13039', '17270', '22363', '83474', '25294', '43269', '77666', '15488', '99146', '64441', '43338', '38736', '62754', '48556', '86057', '23090', '38114', '66061', '18910', '84385', '23600', '19975', '27883', '65719', '19933', '32085', '49731', '40473', '27190', '46192', '23949', '44738', '12436', '64794', '68741', '15333', '24282', '49085', '31844', '71156', '48441', '17100', '98207', '44982', '20277', '71496', '96299', '37583', '22206', '89174', '30589', '61924', '53079', '10976', '13104', '42794', '54772', '15809', '56434', '39975', '13874', '30753', '77598', '78229', '59478', '12345', '55547', '57422', '42600', '79444', '29074', '29752', '21676', '32096', '43044', '39383', '37296', '36295', '63077', '16572', '31275', '18701', '40197', '48242', '27219', '49865', '84175', '30446', '25165', '13807', '72142', '70499', '70464', '71429', '18111', '70857', '29545', '36425', '52706', '36194', '42963', '75068', '47921', '74763', '90990', '89456', '62073', '88397', '73963', '75885', '62657', '12530', '81146', '57434', '25099', '41429', '98441', '48713', '52552', '31667', '14072', '13903', '44709', '85429', '58017', '38295', '44875', '73541', '30091', '12707', '23762', '62258', '33247', '78722', '77431', '14510', '35656', '72428', '92082', '35267', '43759', '24354', '90952', '11512', '21242', '22579', '56114', '32339', '52282', '41791', '24484', '95020', '28408', '99710', '11899', '43344', '72915', '27644', '62708', '74479', '17177', '32619', '12351', '91339', '31169', '57081', '53522', '16712', '34419', '71779', '44187', '46206', '96099', '61910', '53664', '12295', '31837', '33096', '10813', '63048', '31732', '79118', '73084', '72783', '84952', '46965', '77956', '39815', '32311', '75329', '48156', '30826', '49661', '13736', '92076', '74865', '88149', '92397', '52777', '68453', '32012', '21222', '52721', '24626', '18210', '42177', '91791', '75251', '82075', '44372', '45542', '20609', '60115', '17362', '22750', '90434', '31852', '54071', '33762', '14705', '40718', '56433', '30996', '40657', '49056', '23585', '66455', '41021', '74736', '72151', '37007', '21729', '60177', '84558', '59027', '93855', '60022', '86443', '19541', '86886', '30532', '39062', '48532', '34713', '52077', '22564', '64638', '15273', '31677', '36138', '62367', '60261', '80213', '42818', '25113', '72378', '69802', '69096', '55443', '28820', '13848', '78258', '37490', '30556', '77380', '28447', '44550', '26791', '70609', '82182', '33306', '43224', '22322', '86959', '68519', '14308', '46501', '81131', '34056', '61991', '19896', '87804', '65774', '92564') + SUBSTRING("customer_address"."ca_zip", 1, 5) IN ('67436', '26121', '38443', '63157', '68856', '19485', '86425', '26741', '70991', '60899', '63573', '47556', '56193', '93314', '87827', '62017', '85067', '95390', '48091', '10261', '81845', '41790', '42853', '24675', '12840', '60065', '84430', '57451', '24021', '91735', '75335', '71935', '34482', '56943', '70695', '52147', '56251', '28411', '86653', '23005', '22478', '29031', '34398', '15365', '42460', '33337', '59433', '73943', '72477', '74081', '74430', '64605', '39006', '11226', '49057', '97308', '42663', '18187', '19768', '43454', '32147', '76637', '51975', '11181', '45630', '33129', '45995', '64386', '55522', '26697', '20963', '35154', '64587', '49752', '66386', '30586', '59286', '13177', '66646', '84195', '74316', '36853', '32927', '12469', '11904', '36269', '17724', '55346', '12595', '53988', '65439', '28015', '63268', '73590', '29216', '82575', '69267', '13805', '91678', '79460', '94152', '14961', '15419', '48277', '62588', '55493', '28360', '14152', '55225', '18007', '53705', '56573', '80245', '71769', '57348', '36845', '13039', '17270', '22363', '83474', '25294', '43269', '77666', '15488', '99146', '64441', '43338', '38736', '62754', '48556', '86057', '23090', '38114', '66061', '18910', '84385', '23600', '19975', '27883', '65719', '19933', '32085', '49731', '40473', '27190', '46192', '23949', '44738', '12436', '64794', '68741', '15333', '24282', '49085', '31844', '71156', '48441', '17100', '98207', '44982', '20277', '71496', '96299', '37583', '22206', '89174', '30589', '61924', '53079', '10976', '13104', '42794', '54772', '15809', '56434', '39975', '13874', '30753', '77598', '78229', '59478', '12345', '55547', '57422', '42600', '79444', '29074', '29752', '21676', '32096', '43044', '39383', '37296', '36295', '63077', '16572', '31275', '18701', '40197', '48242', '27219', '49865', '84175', '30446', '25165', '13807', '72142', '70499', '70464', '71429', '18111', '70857', '29545', '36425', '52706', '36194', '42963', '75068', '47921', '74763', '90990', '89456', '62073', '88397', '73963', '75885', '62657', '12530', '81146', '57434', '25099', '41429', '98441', '48713', '52552', '31667', '14072', '13903', '44709', '85429', '58017', '38295', '44875', '73541', '30091', '12707', '23762', '62258', '33247', '78722', '77431', '14510', '35656', '72428', '92082', '35267', '43759', '24354', '90952', '11512', '21242', '22579', '56114', '32339', '52282', '41791', '24484', '95020', '28408', '99710', '11899', '43344', '72915', '27644', '62708', '74479', '17177', '32619', '12351', '91339', '31169', '57081', '53522', '16712', '34419', '71779', '44187', '46206', '96099', '61910', '53664', '12295', '31837', '33096', '10813', '63048', '31732', '79118', '73084', '72783', '84952', '46965', '77956', '39815', '32311', '75329', '48156', '30826', '49661', '13736', '92076', '74865', '88149', '92397', '52777', '68453', '32012', '21222', '52721', '24626', '18210', '42177', '91791', '75251', '82075', '44372', '45542', '20609', '60115', '17362', '22750', '90434', '31852', '54071', '33762', '14705', '40718', '56433', '30996', '40657', '49056', '23585', '66455', '41021', '74736', '72151', '37007', '21729', '60177', '84558', '59027', '93855', '60022', '86443', '19541', '86886', '30532', '39062', '48532', '34713', '52077', '22564', '64638', '15273', '31677', '36138', '62367', '60261', '80213', '42818', '25113', '72378', '69802', '69096', '55443', '28820', '13848', '78258', '37490', '30556', '77380', '28447', '44550', '26791', '70609', '82182', '33306', '43224', '22322', '86959', '68519', '14308', '46501', '81131', '34056', '61991', '19896', '87804', '65774', '92564') INTERSECT SELECT "a1"."ca_zip" AS "ca_zip" @@ -1244,7 +1244,7 @@ JOIN "date_dim" AS "date_dim" JOIN "store" AS "store" ON "store"."s_store_sk" = "store_sales"."ss_store_sk" JOIN "a2" AS "a2" - ON SUBSTR("a2"."ca_zip", 1, 2) = SUBSTR("store"."s_zip", 1, 2) + ON SUBSTRING("a2"."ca_zip", 1, 2) = SUBSTRING("store"."s_zip", 1, 2) GROUP BY "store"."s_store_name" ORDER BY @@ -2319,7 +2319,7 @@ FROM catalog_sales, date_dim WHERE cs_bill_customer_sk = c_customer_sk AND c_current_addr_sk = ca_address_sk - AND ( Substr(ca_zip, 1, 5) IN ( '85669', '86197', '88274', '83405', + AND ( SUBSTRING(ca_zip, 1, 5) IN ( '85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792' ) OR ca_state IN ( 'CA', 'WA', 'GA' ) @@ -2344,7 +2344,7 @@ JOIN "customer_address" AS "customer_address" ON ( "catalog_sales"."cs_sales_price" > 500 OR "customer_address"."ca_state" IN ('CA', 'WA', 'GA') - OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792') + OR SUBSTRING("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792') ) AND "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" GROUP BY @@ -2643,7 +2643,7 @@ WHERE d_date_sk = ss_sold_date_sk AND d_year = 1998 AND ss_customer_sk = c_customer_sk AND c_current_addr_sk = ca_address_sk - AND Substr(ca_zip, 1, 5) <> Substr(s_zip, 1, 5) + AND SUBSTRING(ca_zip, 1, 5) <> SUBSTRING(s_zip, 1, 5) AND ss_store_sk = s_store_sk GROUP BY i_brand, i_brand_id, @@ -2672,7 +2672,7 @@ JOIN "customer_address" AS "customer_address" ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" JOIN "store" AS "store" ON "store"."s_store_sk" = "store_sales"."ss_store_sk" - AND SUBSTR("customer_address"."ca_zip", 1, 5) <> SUBSTR("store"."s_zip", 1, 5) + AND SUBSTRING("customer_address"."ca_zip", 1, 5) <> SUBSTRING("store"."s_zip", 1, 5) WHERE "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 1998 GROUP BY @@ -2895,7 +2895,7 @@ LIMIT 100; -------------------------------------- # execute: true WITH frequent_ss_items - AS (SELECT Substr(i_item_desc, 1, 30) itemdesc, + AS (SELECT SUBSTRING(i_item_desc, 1, 30) itemdesc, i_item_sk item_sk, d_date solddate, Count(*) cnt @@ -2905,7 +2905,7 @@ WITH frequent_ss_items WHERE ss_sold_date_sk = d_date_sk AND ss_item_sk = i_item_sk AND d_year IN ( 1998, 1998 + 1, 1998 + 2, 1998 + 3 ) - GROUP BY Substr(i_item_desc, 1, 30), + GROUP BY SUBSTRING(i_item_desc, 1, 30), i_item_sk, d_date HAVING Count(*) > 4), @@ -2962,7 +2962,7 @@ WITH "frequent_ss_items" AS ( JOIN "item" AS "item" ON "item"."i_item_sk" = "store_sales"."ss_item_sk" GROUP BY - SUBSTR("item"."i_item_desc", 1, 30), + SUBSTRING("item"."i_item_desc", 1, 30), "item"."i_item_sk", "date_dim"."d_date" HAVING @@ -5296,7 +5296,7 @@ FROM web_sales, WHERE ws_bill_customer_sk = c_customer_sk AND c_current_addr_sk = ca_address_sk AND ws_item_sk = i_item_sk - AND ( Substr(ca_zip, 1, 5) IN ( '85669', '86197', '88274', '83405', + AND ( SUBSTRING(ca_zip, 1, 5) IN ( '85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792' ) OR i_item_id IN (SELECT i_item_id @@ -5340,7 +5340,7 @@ JOIN "customer_address" AS "customer_address" ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" WHERE NOT "_u_0"."i_item_id" IS NULL - OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792') + OR SUBSTRING("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792') GROUP BY "customer_address"."ca_zip", "customer_address"."ca_state" @@ -7585,7 +7585,7 @@ LIMIT 100; -- TPC-DS 62 -------------------------------------- # execute: true -SELECT Substr(w_warehouse_name, 1, 20) AS "_col_0", +SELECT SUBSTRING(w_warehouse_name, 1, 20) AS "_col_0", sm_type, web_name, Sum(CASE @@ -7622,15 +7622,15 @@ WHERE d_month_seq BETWEEN 1222 AND 1222 + 11 AND ws_warehouse_sk = w_warehouse_sk AND ws_ship_mode_sk = sm_ship_mode_sk AND ws_web_site_sk = web_site_sk -GROUP BY Substr(w_warehouse_name, 1, 20), +GROUP BY SUBSTRING(w_warehouse_name, 1, 20), sm_type, web_name -ORDER BY Substr(w_warehouse_name, 1, 20), +ORDER BY SUBSTRING(w_warehouse_name, 1, 20), sm_type, web_name LIMIT 100; SELECT - SUBSTR("warehouse"."w_warehouse_name", 1, 20) AS "_col_0", + SUBSTRING("warehouse"."w_warehouse_name", 1, 20) AS "_col_0", "ship_mode"."sm_type" AS "sm_type", "web_site"."web_name" AS "web_name", SUM( @@ -7683,7 +7683,7 @@ JOIN "warehouse" AS "warehouse" JOIN "web_site" AS "web_site" ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk" GROUP BY - SUBSTR("warehouse"."w_warehouse_name", 1, 20), + SUBSTRING("warehouse"."w_warehouse_name", 1, 20), "ship_mode"."sm_type", "web_site"."web_name" ORDER BY @@ -10638,7 +10638,7 @@ LIMIT 100; # execute: true SELECT c_last_name, c_first_name, - Substr(s_city, 1, 30) AS "_col_2", + SUBSTRING(s_city, 1, 30) AS "_col_2", ss_ticket_number, amt, profit @@ -10667,7 +10667,7 @@ FROM (SELECT ss_ticket_number, WHERE ss_customer_sk = c_customer_sk ORDER BY c_last_name, c_first_name, - Substr(s_city, 1, 30), + SUBSTRING(s_city, 1, 30), profit LIMIT 100; WITH "ms" AS ( @@ -10701,7 +10701,7 @@ WITH "ms" AS ( SELECT "customer"."c_last_name" AS "c_last_name", "customer"."c_first_name" AS "c_first_name", - SUBSTR("ms"."s_city", 1, 30) AS "_col_2", + SUBSTRING("ms"."s_city", 1, 30) AS "_col_2", "ms"."ss_ticket_number" AS "ss_ticket_number", "ms"."amt" AS "amt", "ms"."profit" AS "profit" @@ -10711,7 +10711,7 @@ JOIN "customer" AS "customer" ORDER BY "c_last_name", "c_first_name", - SUBSTR("ms"."s_city", 1, 30), + SUBSTRING("ms"."s_city", 1, 30), "profit" LIMIT 100; @@ -11371,7 +11371,7 @@ LIMIT 100; -- TPC-DS 85 -------------------------------------- # execute: true -SELECT Substr(r_reason_desc, 1, 20) AS "_col_0", +SELECT SUBSTRING(r_reason_desc, 1, 20) AS "_col_0", Avg(ws_quantity) AS "_col_1", Avg(wr_refunded_cash) AS "_col_2", Avg(wr_fee) AS "_col_3" @@ -11417,13 +11417,13 @@ WHERE ws_web_page_sk = wp_web_page_sk AND ca_state IN ( 'FL', 'WI', 'KS' ) AND ws_net_profit BETWEEN 50 AND 250 ) ) GROUP BY r_reason_desc -ORDER BY Substr(r_reason_desc, 1, 20), +ORDER BY SUBSTRING(r_reason_desc, 1, 20), Avg(ws_quantity), Avg(wr_refunded_cash), Avg(wr_fee) LIMIT 100; SELECT - SUBSTR("reason"."r_reason_desc", 1, 20) AS "_col_0", + SUBSTRING("reason"."r_reason_desc", 1, 20) AS "_col_0", AVG("web_sales"."ws_quantity") AS "_col_1", AVG("web_returns"."wr_refunded_cash") AS "_col_2", AVG("web_returns"."wr_fee") AS "_col_3" @@ -12617,7 +12617,7 @@ ORDER BY -- TPC-DS 99 -------------------------------------- # execute: true -SELECT Substr(w_warehouse_name, 1, 20) AS "_col_0", +SELECT SUBSTRING(w_warehouse_name, 1, 20) AS "_col_0", sm_type, cc_name, Sum(CASE @@ -12654,15 +12654,15 @@ WHERE d_month_seq BETWEEN 1200 AND 1200 + 11 AND cs_warehouse_sk = w_warehouse_sk AND cs_ship_mode_sk = sm_ship_mode_sk AND cs_call_center_sk = cc_call_center_sk -GROUP BY Substr(w_warehouse_name, 1, 20), +GROUP BY SUBSTRING(w_warehouse_name, 1, 20), sm_type, cc_name -ORDER BY Substr(w_warehouse_name, 1, 20), +ORDER BY SUBSTRING(w_warehouse_name, 1, 20), sm_type, cc_name LIMIT 100; SELECT - SUBSTR("warehouse"."w_warehouse_name", 1, 20) AS "_col_0", + SUBSTRING("warehouse"."w_warehouse_name", 1, 20) AS "_col_0", "ship_mode"."sm_type" AS "sm_type", "call_center"."cc_name" AS "cc_name", SUM( @@ -12715,7 +12715,7 @@ JOIN "ship_mode" AS "ship_mode" JOIN "warehouse" AS "warehouse" ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk" GROUP BY - SUBSTR("warehouse"."w_warehouse_name", 1, 20), + SUBSTRING("warehouse"."w_warehouse_name", 1, 20), "ship_mode"."sm_type", "call_center"."cc_name" ORDER BY |