From 639a208fa57ea674d165c4837e96f3ae4d7e3e61 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 19 Feb 2023 14:45:09 +0100 Subject: Merging upstream version 11.1.3. Signed-off-by: Daniel Baumann --- tests/fixtures/identity.sql | 23 ++++++++++++++++-- tests/fixtures/optimizer/optimizer.sql | 7 +----- tests/fixtures/optimizer/pushdown_projections.sql | 27 ++++++++++++++++++++++ tests/fixtures/optimizer/qualify_columns.sql | 12 ++++++++++ .../optimizer/qualify_columns__invalid.sql | 2 +- tests/fixtures/pretty.sql | 4 ++-- 6 files changed, 64 insertions(+), 11 deletions(-) (limited to 'tests/fixtures') diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index b3f546b..7b9ae6d 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -16,6 +16,18 @@ SUM(CASE WHEN x > 1 THEN 1 ELSE 0 END) / y 'x' '\x' "x" +'\z' +'\\z' +'\\\z' +'\\\\z' +'\\\\\z' +'\\\\\\z' +'\n' +'\\n' +'\\\n' +'\\\\n' +'\\\\\n' +'\\\\\\n' "" """x""" N'abc' @@ -502,7 +514,7 @@ CREATE TABLE z (a INT(11) DEFAULT NULL COMMENT '客户id') CREATE TABLE z (a INT(11) NOT NULL DEFAULT 1) CREATE TABLE z (a INT(11) NOT NULL DEFAULT -1) CREATE TABLE z (a INT(11) NOT NULL COLLATE utf8_bin AUTO_INCREMENT) -CREATE TABLE z (a INT, PRIMARY KEY(a)) +CREATE TABLE z (a INT, PRIMARY KEY (a)) CREATE TABLE z WITH (FORMAT='parquet') AS SELECT 1 CREATE TABLE z WITH (FORMAT='ORC', x='2') AS SELECT 1 CREATE TABLE z WITH (TABLE_FORMAT='iceberg', FORMAT='parquet') AS SELECT 1 @@ -530,9 +542,13 @@ CREATE TABLE asd AS SELECT asd FROM asd WITH DATA CREATE TABLE products (x INT GENERATED BY DEFAULT AS IDENTITY) CREATE TABLE products (x INT GENERATED ALWAYS AS IDENTITY) CREATE TABLE IF NOT EXISTS customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT BY 1)) -CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 1)) +CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 1 MINVALUE -1 MAXVALUE 1 NO CYCLE)) CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10)) +CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (CYCLE)) CREATE TABLE foo (baz_id INT REFERENCES baz(id) DEFERRABLE) +CREATE TABLE foo (baz CHAR(4) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC) +CREATE TABLE foo (baz DATE FORMAT 'YYYY/MM/DD' TITLE 'title') +CREATE TABLE t (title TEXT) CREATE TABLE a, FALLBACK, LOG, JOURNAL, CHECKSUM=DEFAULT, DEFAULT MERGEBLOCKRATIO, BLOCKCOMPRESSION=MANUAL (a INT) CREATE TABLE a, NO FALLBACK PROTECTION, NO LOG, NO JOURNAL, CHECKSUM=ON, NO MERGEBLOCKRATIO, BLOCKCOMPRESSION=ALWAYS (a INT) CREATE TABLE a, WITH JOURNAL TABLE=x.y.z, CHECKSUM=OFF, MERGEBLOCKRATIO=1, DATABLOCKSIZE=10 KBYTES (a INT) @@ -556,6 +572,7 @@ CREATE TEMPORARY VIEW x AS WITH y AS (SELECT 1) SELECT * FROM y CREATE MATERIALIZED VIEW x.y.z AS SELECT a FROM b CREATE VIEW z (a, b) CREATE VIEW z (a, b COMMENT 'b', c COMMENT 'c') +CREATE VIEW z AS LOCKING ROW FOR ACCESS SELECT a FROM b CREATE TEMPORARY FUNCTION f CREATE TEMPORARY FUNCTION f AS 'g' CREATE FUNCTION f @@ -731,3 +748,5 @@ SELECT id FROM b.a AS a QUALIFY ROW_NUMBER() OVER (PARTITION BY br ORDER BY sadf SELECT LEFT.FOO FROM BLA AS LEFT SELECT RIGHT.FOO FROM BLA AS RIGHT SELECT LEFT FROM LEFT LEFT JOIN RIGHT RIGHT JOIN LEFT +SELECT * FROM x WHERE name ILIKE ANY XXX('a', 'b') +SELECT * FROM x WHERE name LIKE ANY XXX('a', 'b') diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index 9c14ec1..6ccf24e 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -1,15 +1,10 @@ # title: lateral # execute: false SELECT a, m FROM z LATERAL VIEW EXPLODE([1, 2]) q AS m; -WITH "z_2" AS ( - SELECT - "z"."a" AS "a" - FROM "z" AS "z" -) SELECT "z"."a" AS "a", "q"."m" AS "m" -FROM "z_2" AS "z" +FROM "z" AS "z" LATERAL VIEW EXPLODE(ARRAY(1, 2)) q AS "m"; diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql index 03ecf16..107e92f 100644 --- a/tests/fixtures/optimizer/pushdown_projections.sql +++ b/tests/fixtures/optimizer/pushdown_projections.sql @@ -57,3 +57,30 @@ SELECT q.x AS x FROM (VALUES (1, 2)) AS q(x, y); SELECT i.a FROM x AS i LEFT JOIN (SELECT a, b FROM (SELECT a, b FROM x)) AS j ON i.a = j.a; SELECT i.a AS a FROM x AS i LEFT JOIN (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) AS j ON i.a = j.a; + +-------------------------------------- +-- Unknown Star Expansion +-------------------------------------- +SELECT a FROM (SELECT * FROM zz) WHERE b = 1; +SELECT _q_0.a AS a FROM (SELECT zz.a AS a, zz.b AS b FROM zz AS zz) AS _q_0 WHERE _q_0.b = 1; + +SELECT a FROM (SELECT * FROM aa UNION ALL SELECT * FROM bb UNION ALL SELECT * from cc); +SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa UNION ALL SELECT bb.a AS a FROM bb AS bb UNION ALL SELECT cc.a AS a FROM cc AS cc) AS _q_0; + +SELECT a FROM (SELECT a FROM aa UNION ALL SELECT * FROM bb UNION ALL SELECT * from cc); +SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa UNION ALL SELECT bb.a AS a FROM bb AS bb UNION ALL SELECT cc.a AS a FROM cc AS cc) AS _q_0; + +SELECT a FROM (SELECT * FROM aa UNION ALL SELECT * FROM bb UNION ALL SELECT * from cc); +SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa UNION ALL SELECT bb.a AS a FROM bb AS bb UNION ALL SELECT cc.a AS a FROM cc AS cc) AS _q_0; + +SELECT a FROM (SELECT * FROM aa CROSS JOIN bb); +SELECT _q_0.a AS a FROM (SELECT a AS a FROM aa AS aa CROSS JOIN bb AS bb) AS _q_0; + +SELECT a FROM (SELECT aa.* FROM aa); +SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa) AS _q_0; + +SELECT a FROM (SELECT * FROM (SELECT * FROM aa)); +SELECT _q_1.a AS a FROM (SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa) AS _q_0) AS _q_1; + +with cte1 as (SELECT cola, colb FROM tb UNION ALL SELECT colc, cold FROM tb2) SELECT cola FROM cte1; +WITH cte1 AS (SELECT tb.cola AS cola FROM tb AS tb UNION ALL SELECT tb2.colc AS colc FROM tb2 AS tb2) SELECT cte1.cola AS cola FROM cte1; diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index 141f028..46c576a 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -215,6 +215,9 @@ SELECT _q_0.a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0; SELECT * FROM (SELECT a FROM x); SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0; +SELECT * FROM x GROUP BY 1, 2; +SELECT x.a AS a, x.b AS b FROM x AS x GROUP BY x.a, x.b; + -------------------------------------- -- CTEs -------------------------------------- @@ -310,6 +313,15 @@ SELECT t.aa AS aa FROM x AS x, UNNEST(x.a) AS t(aa); SELECT aa FROM x, UNNEST(a) AS aa; SELECT aa AS aa FROM x AS x, UNNEST(x.a) AS aa; +# execute: false +# dialect: presto +SELECT x.a, i.b FROM x CROSS JOIN UNNEST(SPLIT(b, ',')) AS i(b); +SELECT x.a AS a, i.b AS b FROM x AS x CROSS JOIN UNNEST(SPLIT(x.b, ',')) AS i(b); + +# execute: false +SELECT c FROM (SELECT 1 a) AS x LATERAL VIEW EXPLODE(a) AS c; +SELECT _q_0.c AS c FROM (SELECT 1 AS a) AS x LATERAL VIEW EXPLODE(x.a) _q_0 AS c; + -------------------------------------- -- Window functions -------------------------------------- diff --git a/tests/fixtures/optimizer/qualify_columns__invalid.sql b/tests/fixtures/optimizer/qualify_columns__invalid.sql index 2a3ccfb..f0f9f87 100644 --- a/tests/fixtures/optimizer/qualify_columns__invalid.sql +++ b/tests/fixtures/optimizer/qualify_columns__invalid.sql @@ -1,4 +1,3 @@ -SELECT * FROM zz; SELECT z.a FROM x; SELECT z.* FROM x; SELECT x FROM x; @@ -11,3 +10,4 @@ SELECT b FROM x AS a CROSS JOIN y AS b CROSS JOIN y AS c; SELECT x.a FROM x JOIN y USING (a); SELECT a, SUM(b) FROM x GROUP BY 3; SELECT p FROM (SELECT x from xx) y CROSS JOIN yy CROSS JOIN zz +select a from (select * from x cross join y); diff --git a/tests/fixtures/pretty.sql b/tests/fixtures/pretty.sql index c67ba5d..a06af88 100644 --- a/tests/fixtures/pretty.sql +++ b/tests/fixtures/pretty.sql @@ -40,7 +40,7 @@ WITH cte1 AS ( FROM (SELECT 1) AS x, y, (SELECT 2) z UNION ALL SELECT MAX(COALESCE(x AND y, a and b and c, d and e)), FOO(CASE WHEN a and b THEN c and d ELSE 3 END) - GROUP BY x, GROUPING SETS (a, (b, c)) CUBE(y, z) + GROUP BY x, GROUPING SETS (a, (b, c)), CUBE(y, z) ) x ) SELECT a, b c FROM ( @@ -95,7 +95,7 @@ WITH cte1 AS ( MAX(COALESCE(x AND y, a AND b AND c, d AND e)), FOO(CASE WHEN a AND b THEN c AND d ELSE 3 END) GROUP BY - x + x, GROUPING SETS ( a, (b, c) -- cgit v1.2.3