diff options
Diffstat (limited to 'tests/fixtures')
| -rw-r--r-- | tests/fixtures/identity.sql | 25 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/isolate_table_selects.sql | 3 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/pushdown_projections.sql | 2 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/qualify_columns.sql | 16 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/qualify_columns__invalid.sql | 2 |
5 files changed, 42 insertions, 6 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index 4e21d2b..d52b417 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -94,8 +94,8 @@ CONCAT_WS('-', 'a', 'b') CONCAT_WS('-', 'a', 'b', 'c') POSEXPLODE("x") AS ("a", "b") POSEXPLODE("x") AS ("a", "b", "c") -STR_POSITION(x, 'a') -STR_POSITION(x, 'a', 3) +STR_POSITION(haystack, needle) +STR_POSITION(haystack, needle, pos) LEVENSHTEIN('gumbo', 'gambol', 2, 1, 1) SPLIT(SPLIT(referrer, 'utm_source=')[OFFSET(1)], "&")[OFFSET(0)] x[ORDINAL(1)][SAFE_OFFSET(2)] @@ -375,12 +375,16 @@ SELECT * FROM (SELECT 1 UNION ALL SELECT 2) AS x SELECT * FROM (SELECT 1 UNION ALL SELECT 2) SELECT * FROM ((SELECT 1) AS a UNION ALL (SELECT 2) AS b) SELECT * FROM ((SELECT 1) AS a(b)) +SELECT * FROM ((SELECT 1) UNION (SELECT 2) UNION (SELECT 3)) SELECT * FROM x AS y(a, b) SELECT * EXCEPT (a, b) +SELECT * EXCEPT (a, b) FROM y SELECT * REPLACE (a AS b, b AS C) SELECT * REPLACE (a + 1 AS b, b AS C) SELECT * EXCEPT (a, b) REPLACE (a AS b, b AS C) +SELECT * EXCEPT (a, b) REPLACE (a AS b, b AS C) FROM y SELECT a.* EXCEPT (a, b), b.* REPLACE (a AS b, b AS C) +SELECT a.* EXCEPT (a, b), b.* REPLACE (a AS b, b AS C) FROM x SELECT zoo, animals FROM (VALUES ('oakland', ARRAY('a', 'b')), ('sf', ARRAY('b', 'c'))) AS t(zoo, animals) SELECT zoo, animals FROM UNNEST(ARRAY(STRUCT('oakland' AS zoo, ARRAY('a', 'b') AS animals), STRUCT('sf' AS zoo, ARRAY('b', 'c') AS animals))) AS t(zoo, animals) WITH a AS (SELECT 1) SELECT 1 UNION ALL SELECT 2 @@ -438,6 +442,8 @@ SELECT SUM(x) OVER (PARTITION BY a RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLO SELECT AVG(x) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t SELECT LISTAGG(x) WITHIN GROUP (ORDER BY x) AS y SELECT LISTAGG(x) WITHIN GROUP (ORDER BY x DESC) +SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) +SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY x) SELECT SUM(x) FILTER(WHERE x > 1) SELECT SUM(x) FILTER(WHERE x > 1) OVER (ORDER BY y) SELECT COUNT(DISTINCT a) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) @@ -611,6 +617,7 @@ WITH a AS (SELECT * FROM b) DELETE FROM a WITH a AS (SELECT * FROM b) CACHE TABLE a SELECT ? AS ? FROM x WHERE b BETWEEN ? AND ? GROUP BY ?, 1 LIMIT ? SELECT :hello, ? FROM x LIMIT :my_limit +SELECT * FROM x FETCH NEXT @take ROWS ONLY OFFSET @skip WITH a AS ((SELECT b.foo AS foo, b.bar AS bar FROM b) UNION ALL (SELECT c.foo AS foo, c.bar AS bar FROM c)) SELECT * FROM a WITH a AS ((SELECT 1 AS b) UNION ALL (SELECT 1 AS b)) SELECT * FROM a SELECT (WITH x AS (SELECT 1 AS y) SELECT * FROM x) AS z @@ -670,3 +677,17 @@ 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)) +ALTER TABLE "schema"."tablename" ADD CONSTRAINT "CHK_Name" CHECK (NOT "IdDwh" IS NULL AND "IdDwh" <> (0)) +ALTER TABLE persons ADD CONSTRAINT persons_pk PRIMARY KEY (first_name, last_name) +ALTER TABLE pets ADD CONSTRAINT pets_persons_fk FOREIGN KEY (owner_first_name, owner_last_name) REFERENCES persons +ALTER TABLE pets ADD CONSTRAINT pets_name_not_cute_chk CHECK (LENGTH(name) < 20) +ALTER TABLE people10m ADD CONSTRAINT dateWithinRange CHECK (birthDate > '1900-01-01') +ALTER TABLE people10m ADD CONSTRAINT validIds CHECK (id > 1 AND id < 99999999) ENFORCED +ALTER TABLE baa ADD CONSTRAINT boo PRIMARY KEY (x, y) NOT ENFORCED DEFERRABLE INITIALLY DEFERRED NORELY +ALTER TABLE baa ADD CONSTRAINT boo PRIMARY KEY (x, y) NOT ENFORCED DEFERRABLE INITIALLY DEFERRED NORELY +ALTER TABLE baa ADD CONSTRAINT boo FOREIGN KEY (x, y) REFERENCES persons ON UPDATE NO ACTION ON DELETE NO ACTION MATCH FULL +ALTER TABLE a ADD PRIMARY KEY (x, y) NOT ENFORCED +ALTER TABLE a ADD FOREIGN KEY (x, y) REFERENCES bla +CREATE TABLE foo (baz_id INT REFERENCES baz(id) DEFERRABLE) +SELECT end FROM a +SELECT id FROM b.a AS a QUALIFY ROW_NUMBER() OVER (PARTITION BY br ORDER BY sadf DESC) = 1 diff --git a/tests/fixtures/optimizer/isolate_table_selects.sql b/tests/fixtures/optimizer/isolate_table_selects.sql index 3b9a938..93c0f7c 100644 --- a/tests/fixtures/optimizer/isolate_table_selects.sql +++ b/tests/fixtures/optimizer/isolate_table_selects.sql @@ -18,3 +18,6 @@ WITH y AS (SELECT *) SELECT * FROM x AS x; WITH y AS (SELECT * FROM y AS y2 JOIN x AS z2) SELECT * FROM x AS x JOIN y as y; WITH y AS (SELECT * FROM (SELECT * FROM y AS y) AS y2 JOIN (SELECT * FROM x AS x) AS z2) SELECT * FROM (SELECT * FROM x AS x) AS x JOIN y AS y; + +SELECT * FROM x AS x JOIN xx AS y; +SELECT * FROM (SELECT * FROM x AS x) AS x JOIN xx AS y; diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql index b9f6c3f..03ecf16 100644 --- a/tests/fixtures/optimizer/pushdown_projections.sql +++ b/tests/fixtures/optimizer/pushdown_projections.sql @@ -2,7 +2,7 @@ SELECT a FROM (SELECT * FROM x); SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0; SELECT 1 FROM (SELECT * FROM x) WHERE b = 2; -SELECT 1 AS _col_0 FROM (SELECT x.b AS b FROM x AS x) AS _q_0 WHERE _q_0.b = 2; +SELECT 1 AS "1" FROM (SELECT x.b AS b FROM x AS x) AS _q_0 WHERE _q_0.b = 2; SELECT (SELECT c FROM y WHERE q.b = y.b) FROM (SELECT * FROM x) AS q; SELECT (SELECT y.c AS c FROM y AS y WHERE q.b = y.b) AS _col_0 FROM (SELECT x.b AS b FROM x AS x) AS q; diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index 9c5a0be..ee041e2 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -4,6 +4,14 @@ SELECT a FROM x; SELECT x.a AS a FROM x AS x; +# execute: false +SELECT a FROM zz GROUP BY a ORDER BY a; +SELECT zz.a AS a FROM zz AS zz GROUP BY zz.a ORDER BY a; + +# execute: false +SELECT x, p FROM (SELECT x from xx) xx CROSS JOIN yy; +SELECT xx.x AS x, yy.p AS p FROM (SELECT xx.x AS x FROM xx AS xx) AS xx CROSS JOIN yy AS yy; + SELECT a FROM x AS z; SELECT z.a AS a FROM x AS z; @@ -20,8 +28,8 @@ SELECT a AS b FROM x; SELECT x.a AS b FROM x AS x; # execute: false -SELECT 1, 2 FROM x; -SELECT 1 AS _col_0, 2 AS _col_1 FROM x AS x; +SELECT 1, 2 + 3 FROM x; +SELECT 1 AS "1", 2 + 3 AS _col_1 FROM x AS x; # execute: false SELECT a + b FROM x; @@ -58,6 +66,10 @@ SELECT SUM(a) AS c, SUM(b) AS d FROM x ORDER BY 1, 2; SELECT SUM(x.a) AS c, SUM(x.b) AS d FROM x AS x ORDER BY SUM(x.a), SUM(x.b); # execute: false +SELECT CAST(a AS INT) FROM x ORDER BY a; +SELECT CAST(x.a AS INT) AS a FROM x AS x ORDER BY a; + +# execute: false SELECT SUM(a), SUM(b) AS c FROM x ORDER BY 1, 2; SELECT SUM(x.a) AS _col_0, SUM(x.b) AS c FROM x AS x ORDER BY SUM(x.a), SUM(x.b); diff --git a/tests/fixtures/optimizer/qualify_columns__invalid.sql b/tests/fixtures/optimizer/qualify_columns__invalid.sql index 1104b6e..2a3ccfb 100644 --- a/tests/fixtures/optimizer/qualify_columns__invalid.sql +++ b/tests/fixtures/optimizer/qualify_columns__invalid.sql @@ -1,4 +1,3 @@ -SELECT a FROM zz; SELECT * FROM zz; SELECT z.a FROM x; SELECT z.* FROM x; @@ -11,3 +10,4 @@ SELECT q.a FROM (SELECT x.b FROM x) AS z JOIN (SELECT a FROM z) AS q ON z.b = q. 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 |