From f2981e8e4d28233864f1ca06ecec45ab80bf9eae Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 19 Nov 2022 15:50:39 +0100 Subject: Merging upstream version 10.0.8. Signed-off-by: Daniel Baumann --- tests/fixtures/identity.sql | 21 ++++++++----- tests/fixtures/optimizer/canonicalize.sql | 5 ++++ tests/fixtures/optimizer/optimizer.sql | 4 ++- tests/fixtures/optimizer/tpc-h/tpc-h.sql | 50 +++++++++++++++---------------- tests/fixtures/pretty.sql | 7 +++++ 5 files changed, 54 insertions(+), 33 deletions(-) create mode 100644 tests/fixtures/optimizer/canonicalize.sql (limited to 'tests/fixtures') diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index 836ab28..75bd25d 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -122,13 +122,6 @@ x AT TIME ZONE 'UTC' CAST('2025-11-20 00:00:00+00' AS TIMESTAMP) AT TIME ZONE 'Africa/Cairo' SET x = 1 SET -v -ADD JAR s3://bucket -ADD JARS s3://bucket, c -ADD FILE s3://file -ADD FILES s3://file, s3://a -ADD ARCHIVE s3://file -ADD ARCHIVES s3://file, s3://a -BEGIN IMMEDIATE TRANSACTION COMMIT USE db NOT 1 @@ -278,6 +271,7 @@ SELECT CEIL(a, b) FROM test SELECT COUNT(a) FROM test SELECT COUNT(1) FROM test SELECT COUNT(*) FROM test +SELECT COUNT() FROM test SELECT COUNT(DISTINCT a) FROM test SELECT EXP(a) FROM test SELECT FLOOR(a) FROM test @@ -372,6 +366,8 @@ WITH a AS (SELECT 1) SELECT 1 UNION SELECT 2 WITH a AS (SELECT 1) SELECT 1 INTERSECT SELECT 2 WITH a AS (SELECT 1) SELECT 1 EXCEPT SELECT 2 WITH a AS (SELECT 1) SELECT 1 EXCEPT SELECT 2 +WITH sub_query AS (SELECT a FROM table) (SELECT a FROM sub_query) +WITH sub_query AS (SELECT a FROM table) ((((SELECT a FROM sub_query)))) (SELECT 1) UNION (SELECT 2) (SELECT 1) UNION SELECT 2 SELECT 1 UNION (SELECT 2) @@ -463,6 +459,7 @@ CREATE TABLE z (a INT, b VARCHAR COMMENT 'z', c VARCHAR(100) COMMENT 'z', d DECI CREATE TABLE z (a INT(11) DEFAULT UUID()) 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 WITH (FORMAT='parquet') AS SELECT 1 @@ -476,6 +473,9 @@ CREATE TABLE z AS ((WITH cte AS (SELECT 1) SELECT * FROM cte)) CREATE TABLE z (a INT UNIQUE) CREATE TABLE z (a INT AUTO_INCREMENT) CREATE TABLE z (a INT UNIQUE AUTO_INCREMENT) +CREATE TABLE z (a INT REFERENCES parent(b, c)) +CREATE TABLE z (a INT PRIMARY KEY, b INT REFERENCES foo(id)) +CREATE TABLE z (a INT, FOREIGN KEY (a) REFERENCES parent(b, c)) CREATE TEMPORARY FUNCTION f CREATE TEMPORARY FUNCTION f AS 'g' CREATE FUNCTION f @@ -514,17 +514,23 @@ DELETE FROM x WHERE y > 1 DELETE FROM y DELETE FROM event USING sales WHERE event.eventid = sales.eventid DELETE FROM event USING sales, USING bla WHERE event.eventid = sales.eventid +DELETE FROM event USING sales AS s WHERE event.eventid = s.eventid +PREPARE statement +EXECUTE statement DROP TABLE a DROP TABLE a.b DROP TABLE IF EXISTS a DROP TABLE IF EXISTS a.b +DROP TABLE a CASCADE DROP VIEW a DROP VIEW a.b DROP VIEW IF EXISTS a DROP VIEW IF EXISTS a.b SHOW TABLES USE db +BEGIN ROLLBACK +ROLLBACK TO b EXPLAIN SELECT * FROM x INSERT INTO x SELECT * FROM y INSERT INTO x (SELECT * FROM y) @@ -581,3 +587,4 @@ SELECT 1 /* c1 */ + 2 /* c2 */, 3 /* c3 */ SELECT x FROM a.b.c /* x */, e.f.g /* x */ SELECT FOO(x /* c */) /* FOO */, b /* b */ SELECT FOO(x /* c1 */ + y /* c2 */ + BLA(5 /* c3 */)) FROM VALUES (1 /* c4 */, "test" /* c5 */) /* c6 */ +SELECT a FROM x WHERE a COLLATE 'utf8_general_ci' = 'b' diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql new file mode 100644 index 0000000..7fcdbb8 --- /dev/null +++ b/tests/fixtures/optimizer/canonicalize.sql @@ -0,0 +1,5 @@ +SELECT w.d + w.e AS c FROM w AS w; +SELECT CONCAT(w.d, w.e) AS c FROM w AS w; + +SELECT CAST(w.d AS DATE) > w.e AS a FROM w AS w; +SELECT CAST(w.d AS DATE) > CAST(w.e AS DATE) AS a FROM w AS w; diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index eb7e9cb..a1e531b 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -119,7 +119,7 @@ GROUP BY LIMIT 1; # title: Root subquery is union -(SELECT b FROM x UNION SELECT b FROM y) LIMIT 1; +(SELECT b FROM x UNION SELECT b FROM y ORDER BY b) LIMIT 1; ( SELECT "x"."b" AS "b" @@ -128,6 +128,8 @@ LIMIT 1; SELECT "y"."b" AS "b" FROM "y" AS "y" + ORDER BY + "b" ) LIMIT 1; diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql index b91205c..8138b11 100644 --- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql +++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql @@ -15,7 +15,7 @@ select from lineitem where - CAST(l_shipdate AS DATE) <= date '1998-12-01' - interval '90' day + l_shipdate <= date '1998-12-01' - interval '90' day group by l_returnflag, l_linestatus @@ -250,8 +250,8 @@ FROM "orders" AS "orders" LEFT JOIN "_u_0" AS "_u_0" ON "_u_0"."l_orderkey" = "orders"."o_orderkey" WHERE - "orders"."o_orderdate" < CAST('1993-10-01' AS DATE) - AND "orders"."o_orderdate" >= CAST('1993-07-01' AS DATE) + CAST("orders"."o_orderdate" AS DATE) < CAST('1993-10-01' AS DATE) + AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1993-07-01' AS DATE) AND NOT "_u_0"."l_orderkey" IS NULL GROUP BY "orders"."o_orderpriority" @@ -293,8 +293,8 @@ SELECT FROM "customer" AS "customer" JOIN "orders" AS "orders" ON "customer"."c_custkey" = "orders"."o_custkey" - AND "orders"."o_orderdate" < CAST('1995-01-01' AS DATE) - AND "orders"."o_orderdate" >= CAST('1994-01-01' AS DATE) + AND CAST("orders"."o_orderdate" AS DATE) < CAST('1995-01-01' AS DATE) + AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1994-01-01' AS DATE) JOIN "region" AS "region" ON "region"."r_name" = 'ASIA' JOIN "nation" AS "nation" @@ -328,8 +328,8 @@ FROM "lineitem" AS "lineitem" WHERE "lineitem"."l_discount" BETWEEN 0.05 AND 0.07 AND "lineitem"."l_quantity" < 24 - AND "lineitem"."l_shipdate" < CAST('1995-01-01' AS DATE) - AND "lineitem"."l_shipdate" >= CAST('1994-01-01' AS DATE); + AND CAST("lineitem"."l_shipdate" AS DATE) < CAST('1995-01-01' AS DATE) + AND CAST("lineitem"."l_shipdate" AS DATE) >= CAST('1994-01-01' AS DATE); -------------------------------------- -- TPC-H 7 @@ -384,13 +384,13 @@ WITH "n1" AS ( SELECT "n1"."n_name" AS "supp_nation", "n2"."n_name" AS "cust_nation", - EXTRACT(year FROM "lineitem"."l_shipdate") AS "l_year", + EXTRACT(year FROM CAST("lineitem"."l_shipdate" AS DATETIME)) AS "l_year", SUM("lineitem"."l_extendedprice" * ( 1 - "lineitem"."l_discount" )) AS "revenue" FROM "supplier" AS "supplier" JOIN "lineitem" AS "lineitem" - ON "lineitem"."l_shipdate" BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE) + ON CAST("lineitem"."l_shipdate" AS DATE) BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE) AND "supplier"."s_suppkey" = "lineitem"."l_suppkey" JOIN "orders" AS "orders" ON "orders"."o_orderkey" = "lineitem"."l_orderkey" @@ -409,7 +409,7 @@ JOIN "n1" AS "n2" GROUP BY "n1"."n_name", "n2"."n_name", - EXTRACT(year FROM "lineitem"."l_shipdate") + EXTRACT(year FROM CAST("lineitem"."l_shipdate" AS DATETIME)) ORDER BY "supp_nation", "cust_nation", @@ -456,7 +456,7 @@ group by order by o_year; SELECT - EXTRACT(year FROM "orders"."o_orderdate") AS "o_year", + EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATETIME)) AS "o_year", SUM( CASE WHEN "nation_2"."n_name" = 'BRAZIL' @@ -477,7 +477,7 @@ JOIN "customer" AS "customer" ON "customer"."c_nationkey" = "nation"."n_nationkey" JOIN "orders" AS "orders" ON "orders"."o_custkey" = "customer"."c_custkey" - AND "orders"."o_orderdate" BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE) + AND CAST("orders"."o_orderdate" AS DATE) BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE) JOIN "lineitem" AS "lineitem" ON "lineitem"."l_orderkey" = "orders"."o_orderkey" AND "part"."p_partkey" = "lineitem"."l_partkey" @@ -488,7 +488,7 @@ JOIN "nation" AS "nation_2" WHERE "part"."p_type" = 'ECONOMY ANODIZED STEEL' GROUP BY - EXTRACT(year FROM "orders"."o_orderdate") + EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATETIME)) ORDER BY "o_year"; @@ -529,7 +529,7 @@ order by o_year desc; SELECT "nation"."n_name" AS "nation", - EXTRACT(year FROM "orders"."o_orderdate") AS "o_year", + EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATETIME)) AS "o_year", SUM( "lineitem"."l_extendedprice" * ( 1 - "lineitem"."l_discount" @@ -551,7 +551,7 @@ WHERE "part"."p_name" LIKE '%green%' GROUP BY "nation"."n_name", - EXTRACT(year FROM "orders"."o_orderdate") + EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATETIME)) ORDER BY "nation", "o_year" DESC; @@ -606,8 +606,8 @@ SELECT FROM "customer" AS "customer" JOIN "orders" AS "orders" ON "customer"."c_custkey" = "orders"."o_custkey" - AND "orders"."o_orderdate" < CAST('1994-01-01' AS DATE) - AND "orders"."o_orderdate" >= CAST('1993-10-01' AS DATE) + AND CAST("orders"."o_orderdate" AS DATE) < CAST('1994-01-01' AS DATE) + AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1993-10-01' AS DATE) JOIN "lineitem" AS "lineitem" ON "lineitem"."l_orderkey" = "orders"."o_orderkey" AND "lineitem"."l_returnflag" = 'R' JOIN "nation" AS "nation" @@ -740,8 +740,8 @@ SELECT FROM "orders" AS "orders" JOIN "lineitem" AS "lineitem" ON "lineitem"."l_commitdate" < "lineitem"."l_receiptdate" - AND "lineitem"."l_receiptdate" < CAST('1995-01-01' AS DATE) - AND "lineitem"."l_receiptdate" >= CAST('1994-01-01' AS DATE) + AND CAST("lineitem"."l_receiptdate" AS DATE) < CAST('1995-01-01' AS DATE) + AND CAST("lineitem"."l_receiptdate" AS DATE) >= CAST('1994-01-01' AS DATE) AND "lineitem"."l_shipdate" < "lineitem"."l_commitdate" AND "lineitem"."l_shipmode" IN ('MAIL', 'SHIP') AND "orders"."o_orderkey" = "lineitem"."l_orderkey" @@ -832,8 +832,8 @@ FROM "lineitem" AS "lineitem" JOIN "part" AS "part" ON "lineitem"."l_partkey" = "part"."p_partkey" WHERE - "lineitem"."l_shipdate" < CAST('1995-10-01' AS DATE) - AND "lineitem"."l_shipdate" >= CAST('1995-09-01' AS DATE); + CAST("lineitem"."l_shipdate" AS DATE) < CAST('1995-10-01' AS DATE) + AND CAST("lineitem"."l_shipdate" AS DATE) >= CAST('1995-09-01' AS DATE); -------------------------------------- -- TPC-H 15 @@ -876,8 +876,8 @@ WITH "revenue" AS ( )) AS "total_revenue" FROM "lineitem" AS "lineitem" WHERE - "lineitem"."l_shipdate" < CAST('1996-04-01' AS DATE) - AND "lineitem"."l_shipdate" >= CAST('1996-01-01' AS DATE) + CAST("lineitem"."l_shipdate" AS DATE) < CAST('1996-04-01' AS DATE) + AND CAST("lineitem"."l_shipdate" AS DATE) >= CAST('1996-01-01' AS DATE) GROUP BY "lineitem"."l_suppkey" ) @@ -1220,8 +1220,8 @@ WITH "_u_0" AS ( "lineitem"."l_suppkey" AS "_u_2" FROM "lineitem" AS "lineitem" WHERE - "lineitem"."l_shipdate" < CAST('1995-01-01' AS DATE) - AND "lineitem"."l_shipdate" >= CAST('1994-01-01' AS DATE) + CAST("lineitem"."l_shipdate" AS DATE) < CAST('1995-01-01' AS DATE) + AND CAST("lineitem"."l_shipdate" AS DATE) >= CAST('1994-01-01' AS DATE) GROUP BY "lineitem"."l_partkey", "lineitem"."l_suppkey" diff --git a/tests/fixtures/pretty.sql b/tests/fixtures/pretty.sql index 5e27b5e..067fe77 100644 --- a/tests/fixtures/pretty.sql +++ b/tests/fixtures/pretty.sql @@ -315,3 +315,10 @@ FROM ( WHERE id = 1 ) /* x */; +SELECT * /* multi + line + comment */; +SELECT + * /* multi + line + comment */; -- cgit v1.2.3