Adding upstream version 11.7.1.upstream/11.7.1

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

6 files changed, 53 insertions, 7 deletions

diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 54e5583..a08a7a8 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -100,6 +100,9 @@ CURRENT_DATE AT TIME ZONE zone_column
 CURRENT_DATE AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokio'
 ARRAY()
 ARRAY(1, 2)
+ARRAY(time, foo)
+ARRAY(foo, time)
+ARRAY(LENGTH(waiter_name) > 0)
 ARRAY_CONTAINS(x, 1)
 EXTRACT(x FROM y)
 EXTRACT(DATE FROM y)
@@ -126,12 +129,14 @@ x ILIKE '%y%' ESCAPE '\'
 1 AS escape
 INTERVAL '1' day
 INTERVAL '1' MONTH
-INTERVAL '1 day'
 INTERVAL '-1' CURRENT_DATE
 INTERVAL '-31' CAST(GETDATE() AS DATE)
-INTERVAL 2 months
 INTERVAL (1 + 3) DAYS
+INTERVAL '1' day * 5
+5 * INTERVAL '1' day
+CASE WHEN TRUE THEN INTERVAL '15' days END
 CAST('45' AS INTERVAL DAYS)
+CAST(x AS UUID)
 FILTER(a, x -> x.a.b.c.d.e.f.g)
 FILTER(a, x -> FOO(x.a.b.c.d.e.f.g) + x.a.b.c.d.e.f.g)
 TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), 1, DAY)
@@ -250,6 +255,8 @@ SELECT * FROM test LIMIT 1 + 1
 SELECT * FROM test LIMIT 100 OFFSET 200
 SELECT * FROM test FETCH FIRST ROWS ONLY
 SELECT * FROM test FETCH FIRST 1 ROWS ONLY
+SELECT * FROM test ORDER BY id DESC FETCH FIRST 10 ROWS WITH TIES
+SELECT * FROM test ORDER BY id DESC FETCH FIRST 10 PERCENT ROWS WITH TIES
 SELECT * FROM test FETCH NEXT 1 ROWS ONLY
 SELECT (1 > 2) AS x FROM test
 SELECT NOT (1 > 2) FROM test
@@ -554,6 +561,7 @@ CREATE TABLE asd AS SELECT asd FROM asd WITH NO DATA
 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 konyvszerzo (szerzo_azon INT CONSTRAINT konyvszerzo_szerzo_fk REFERENCES szerzo)
 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 MINVALUE -1 MAXVALUE 1 NO CYCLE))
 CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10))
@@ -640,6 +648,7 @@ 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
+DELETE FROM event AS event USING sales AS s WHERE event.eventid = s.eventid
 PREPARE statement
 EXECUTE statement
 DROP TABLE a
@@ -648,6 +657,7 @@ DROP TABLE IF EXISTS a
 DROP TABLE IF EXISTS a.b
 DROP TABLE a CASCADE
 DROP TABLE s_hajo CASCADE CONSTRAINTS
+DROP TABLE a PURGE
 DROP VIEW a
 DROP VIEW a.b
 DROP VIEW IF EXISTS a
@@ -717,12 +727,14 @@ SELECT a /* x */ /* y */ /* z */, b /* k */ /* m */
 SELECT * FROM foo /* x */, bla /* x */
 SELECT 1 /* comment */ + 1
 SELECT 1 /* c1 */ + 2 /* c2 */
+SELECT 1 /* c1 */ + /* c2 */ 2 /* c3 */
 SELECT 1 /* c1 */ + 2 /* c2 */ + 3 /* c3 */
 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 */
 INSERT INTO foo SELECT * FROM bar /* comment */
+/* c */ WITH x AS (SELECT 1) SELECT * FROM x
 SELECT a FROM x WHERE a COLLATE 'utf8_general_ci' = 'b'
 SELECT x AS INTO FROM bla
 SELECT * INTO newevent FROM event
@@ -736,6 +748,7 @@ ALTER TABLE IF EXISTS integers ADD COLUMN k INT
 ALTER TABLE integers ADD COLUMN l INT DEFAULT 10
 ALTER TABLE measurements ADD COLUMN mtime TIMESTAMPTZ DEFAULT NOW()
 ALTER TABLE integers DROP COLUMN k
+ALTER TABLE integers DROP PRIMARY KEY
 ALTER TABLE integers DROP COLUMN IF EXISTS k
 ALTER TABLE integers DROP COLUMN k CASCADE
 ALTER TABLE integers ALTER COLUMN i TYPE VARCHAR
@@ -760,6 +773,7 @@ STRUCT<VARCHAR(10)>("bla")
 STRUCT<INT>(5)
 STRUCT<DATE>("2011-05-05")
 STRUCT<x INT, y TEXT>(1, t.str_col)
+STRUCT<int INT>
 SELECT CAST(NULL AS ARRAY<INT>) IS NULL AS array_is_null
 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)
@@ -803,3 +817,6 @@ JSON_OBJECT('x': NULL, 'y': 1 WITH UNIQUE KEYS)
 JSON_OBJECT('x': NULL, 'y': 1 ABSENT ON NULL WITH UNIQUE KEYS)
 JSON_OBJECT('x': 1 RETURNING VARCHAR(100))
 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
diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql
index 7582f3a..ccf2f16 100644
--- a/tests/fixtures/optimizer/canonicalize.sql
+++ b/tests/fixtures/optimizer/canonicalize.sql
@@ -11,7 +11,7 @@ SELECT CAST(1 + 3.2 AS DOUBLE) AS a FROM w AS w;
 SELECT 1 + 3.2 AS "a" FROM "w" AS "w";
 
 SELECT CAST("2022-01-01" AS DATE) + INTERVAL '1' day;
-SELECT CAST("2022-01-01" AS DATE) + INTERVAL '1' "day" AS "_col_0";
+SELECT CAST("2022-01-01" AS DATE) + INTERVAL '1' day AS "_col_0";
 
 --------------------------------------
 -- Ensure boolean predicates
diff --git a/tests/fixtures/optimizer/normalize.sql b/tests/fixtures/optimizer/normalize.sql
index a84fadf..803a474 100644
--- a/tests/fixtures/optimizer/normalize.sql
+++ b/tests/fixtures/optimizer/normalize.sql
@@ -39,3 +39,6 @@ A OR ((((B OR C) AND (B OR D)) OR C) AND (((B OR C) AND (B OR D)) OR D));
 
 (A AND B) OR (C OR (D AND E));
 (A OR C OR D) AND (A OR C OR E) AND (B OR C OR D) AND (B OR C OR E);
+
+SELECT * FROM x WHERE (A AND B) OR C;
+SELECT * FROM x WHERE (A OR C) AND (B OR C);
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 74e2d0a..3013bba 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -131,6 +131,14 @@ SELECT DATE_TRUNC(x.a, MONTH) AS a FROM x AS x;
 SELECT x FROM READ_PARQUET('path.parquet', hive_partition=1);
 SELECT _q_0.x AS x FROM READ_PARQUET('path.parquet', hive_partition = 1) AS _q_0;
 
+# execute: false
+select * from (values (1, 2));
+SELECT _q_0._col_0 AS _col_0, _q_0._col_1 AS _col_1 FROM (VALUES (1, 2)) AS _q_0(_col_0, _col_1);
+
+# execute: false
+select * from (values (1, 2)) x;
+SELECT x._col_0 AS _col_0, x._col_1 AS _col_1 FROM (VALUES (1, 2)) AS x(_col_0, _col_1);
+
 --------------------------------------
 -- Derived tables
 --------------------------------------
@@ -317,6 +325,21 @@ SELECT COALESCE(y.b, z.b) AS b, COALESCE(y.c, z.c) AS c FROM y AS y JOIN z AS z
 SELECT * FROM y JOIN z USING(b, c) WHERE b = 2 AND c = 3;
 SELECT COALESCE(y.b, z.b) AS b, COALESCE(y.c, z.c) AS c FROM y AS y JOIN z AS z ON y.b = z.b AND y.c = z.c WHERE COALESCE(y.b, z.b) = 2 AND COALESCE(y.c, z.c) = 3;
 
+-- We can safely convert `b` to `x.b` in the following two queries, because the original queries
+-- would be invalid if `b` also existed in `t`'s schema (which we don't know), due to ambiguity.
+
+# execute: false
+SELECT b FROM x JOIN t USING(a);
+SELECT x.b AS b FROM x AS x JOIN t AS t ON x.a = t.a;
+
+# execute: false
+SELECT b FROM t JOIN x USING(a);
+SELECT x.b AS b FROM t AS t JOIN x AS x ON t.a = x.a;
+
+# execute: false
+SELECT a FROM t1 JOIN t2 USING(a);
+SELECT COALESCE(t1.a, t2.a) AS a FROM t1 AS t1 JOIN t2 AS t2 ON t1.a = t2.a;
+
 --------------------------------------
 -- Hint with table reference
 --------------------------------------
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index 54ec64b..a2cd859 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -572,3 +572,6 @@ x > 3;
 
 'a' < 'b';
 TRUE;
+
+x = 2018 OR x <> 2018;
+x <> 2018 OR x = 2018;
\ No newline at end of file
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index d9a06cc..9168508 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -2500,7 +2500,7 @@ JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_ship_date_sk" = "date_dim"."d_date_sk"
   AND "date_dim"."d_date" >= '2002-3-01'
   AND CAST("date_dim"."d_date" AS DATE) <= (
-    CAST('2002-3-01' AS DATE) + INTERVAL '60' "day"
+    CAST('2002-3-01' AS DATE) + INTERVAL '60' day
   )
 JOIN "customer_address" AS "customer_address"
   ON "catalog_sales"."cs_ship_addr_sk" = "customer_address"."ca_address_sk"
@@ -9420,7 +9420,7 @@ JOIN "date_dim" AS "date_dim_2"
   AND "date_dim_2"."d_year" = 2002
 JOIN "date_dim" AS "date_dim_3"
   ON "catalog_sales"."cs_ship_date_sk" = "date_dim_3"."d_date_sk"
-  AND "date_dim_3"."d_date" > CONCAT("date_dim_2"."d_date", INTERVAL '5' "day")
+  AND "date_dim_3"."d_date" > CONCAT("date_dim_2"."d_date", INTERVAL '5' day)
 LEFT JOIN "promotion" AS "promotion"
   ON "catalog_sales"."cs_promo_sk" = "promotion"."p_promo_sk"
 LEFT JOIN "catalog_returns" AS "catalog_returns"
@@ -12200,7 +12200,7 @@ JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date" >= '2000-3-01'
   AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= (
-    CAST('2000-3-01' AS DATE) + INTERVAL '60' "day"
+    CAST('2000-3-01' AS DATE) + INTERVAL '60' day
   )
 JOIN "customer_address" AS "customer_address"
   ON "customer_address"."ca_state" = 'MT'
@@ -12295,7 +12295,7 @@ JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date" >= '2000-4-01'
   AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= (
-    CAST('2000-4-01' AS DATE) + INTERVAL '60' "day"
+    CAST('2000-4-01' AS DATE) + INTERVAL '60' day
   )
 JOIN "customer_address" AS "customer_address"
   ON "customer_address"."ca_state" = 'IN'