diff options
Diffstat (limited to '')
| -rw-r--r-- | tests/fixtures/identity.sql | 6 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/qualify_columns.sql | 7 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/qualify_tables.sql | 23 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 16 |
4 files changed, 52 insertions, 0 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index a08a7a8..ea695c9 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -85,6 +85,7 @@ x IS TRUE x IS FALSE x IS TRUE IS TRUE x LIKE y IS TRUE +TRIM('a' || 'b') MAP() GREATEST(x) LEAST(y) @@ -104,6 +105,7 @@ ARRAY(time, foo) ARRAY(foo, time) ARRAY(LENGTH(waiter_name) > 0) ARRAY_CONTAINS(x, 1) +x.EXTRACT(1) EXTRACT(x FROM y) EXTRACT(DATE FROM y) EXTRACT(WEEK(monday) FROM created_at) @@ -215,6 +217,7 @@ SELECT COUNT(DISTINCT a, b) SELECT COUNT(DISTINCT a, b + 1) SELECT SUM(DISTINCT x) SELECT SUM(x IGNORE NULLS) AS x +SELECT COUNT(x RESPECT NULLS) SELECT TRUNCATE(a, b) SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 10) AS x SELECT ARRAY_AGG(STRUCT(x, x AS y) ORDER BY z DESC) AS x @@ -820,3 +823,6 @@ JSON_OBJECT('x': 1 RETURNING VARBINARY FORMAT JSON ENCODING UTF8) SELECT if.x SELECT NEXT VALUE FOR db.schema.sequence_name SELECT NEXT VALUE FOR db.schema.sequence_name OVER (ORDER BY foo), col +SELECT PERCENTILE_CONT(x, 0.5) OVER () +SELECT PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER () +SELECT PERCENTILE_CONT(x, 0.5 IGNORE NULLS) OVER () diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index 3013bba..f077647 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -4,6 +4,9 @@ SELECT a FROM x; SELECT x.a AS a FROM x AS x; +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; @@ -212,6 +215,10 @@ SELECT x.a AS a FROM x AS x WHERE x.b IN (SELECT x.b AS b FROM y AS x); SELECT a FROM x AS i WHERE b IN (SELECT b FROM y AS j WHERE j.b IN (SELECT c FROM y AS k WHERE k.b = j.b)); SELECT i.a AS a FROM x AS i WHERE i.b IN (SELECT j.b AS b FROM y AS j WHERE j.b IN (SELECT k.c AS c FROM y AS k WHERE k.b = j.b)); +# execute: false +SELECT (SELECT n.a FROM n WHERE n.id = m.id) FROM m AS m; +SELECT (SELECT n.a AS a FROM n AS n WHERE n.id = m.id) AS _col_0 FROM m AS m; + -------------------------------------- -- Expand * -------------------------------------- diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql index 2cea85d..0ad155a 100644 --- a/tests/fixtures/optimizer/qualify_tables.sql +++ b/tests/fixtures/optimizer/qualify_tables.sql @@ -15,3 +15,26 @@ WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a; SELECT (SELECT y.c FROM y AS y) FROM x; SELECT (SELECT y.c FROM c.db.y AS y) FROM c.db.x AS x; + +------------------------- +-- Expand join constructs +------------------------- + +-- This is valid in Trino, so we treat the (tbl AS tbl) as a "join construct" per postgres' terminology. +SELECT * FROM (tbl AS tbl) AS _q_0; +SELECT * FROM (SELECT * FROM c.db.tbl AS tbl) AS _q_0; + +SELECT * FROM ((tbl AS tbl)) AS _q_0; +SELECT * FROM (SELECT * FROM c.db.tbl AS tbl) AS _q_0; + +SELECT * FROM (((tbl AS tbl))) AS _q_0; +SELECT * FROM (SELECT * FROM c.db.tbl AS tbl) AS _q_0; + +SELECT * FROM (tbl1 AS tbl1 JOIN tbl2 AS tbl2 ON id1 = id2 JOIN tbl3 AS tbl3 ON id1 = id3) AS _q_0; +SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN c.db.tbl2 AS tbl2 ON id1 = id2 JOIN c.db.tbl3 AS tbl3 ON id1 = id3) AS _q_0; + +SELECT * FROM ((tbl1 AS tbl1 JOIN tbl2 AS tbl2 ON id1 = id2 JOIN tbl3 AS tbl3 ON id1 = id3)) AS _q_0; +SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN c.db.tbl2 AS tbl2 ON id1 = id2 JOIN c.db.tbl3 AS tbl3 ON id1 = id3) AS _q_0; + +SELECT * FROM (tbl1 AS tbl1 JOIN (tbl2 AS tbl2 JOIN tbl3 AS tbl3 ON id2 = id3) AS _q_0 ON id1 = id3) AS _q_1; +SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN (SELECT * FROM c.db.tbl2 AS tbl2 JOIN c.db.tbl3 AS tbl3 ON id2 = id3) AS _q_0 ON id1 = id3) AS _q_1; diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index 9168508..9908756 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -6386,6 +6386,14 @@ WITH "tmp1" AS ( OR "item"."i_class" IN ('personal', 'portable', 'reference', 'self-help') ) AND ( + "item"."i_brand" IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9') + OR "item"."i_category" IN ('Women', 'Music', 'Men') + ) + AND ( + "item"."i_brand" IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9') + OR "item"."i_class" IN ('accessories', 'classical', 'fragrances', 'pants') + ) + AND ( "item"."i_category" IN ('Books', 'Children', 'Electronics') OR "item"."i_category" IN ('Women', 'Music', 'Men') ) @@ -7590,6 +7598,14 @@ WITH "tmp1" AS ( OR "item"."i_class" IN ('personal', 'portable', 'reference', 'self-help') ) AND ( + "item"."i_brand" IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9') + OR "item"."i_category" IN ('Women', 'Music', 'Men') + ) + AND ( + "item"."i_brand" IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9') + OR "item"."i_class" IN ('accessories', 'classical', 'fragrances', 'pants') + ) + AND ( "item"."i_category" IN ('Books', 'Children', 'Electronics') OR "item"."i_category" IN ('Women', 'Music', 'Men') ) |