From 258c7df9cab21a4978c100568907ac1cb7fd6ee0 Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Tue, 23 Jan 2024 06:06:10 +0100
Subject: Adding upstream version 20.9.0.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
---
 tests/fixtures/optimizer/simplify.sql | 54 +++++++++++++++++++++++++++--------
 1 file changed, 42 insertions(+), 12 deletions(-)

(limited to 'tests/fixtures/optimizer/simplify.sql')

diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index d3b03fb..a80be17 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -427,7 +427,7 @@ FALSE;
 TRUE;
 
 date '1998-12-01' - interval x day;
-CAST('1998-12-01' AS DATE) - INTERVAL x day;
+CAST('1998-12-01' AS DATE) - INTERVAL x DAY;
 
 date '1998-12-01' - interval '90' day;
 CAST('1998-09-02' AS DATE);
@@ -442,13 +442,13 @@ interval '1' year + date '1998-01-01' + 3 * 7 * 4;
 CAST('1999-01-01' AS DATE) + 84;
 
 date '1998-12-01' - interval '90' foo;
-CAST('1998-12-01' AS DATE) - INTERVAL '90' foo;
+CAST('1998-12-01' AS DATE) - INTERVAL '90' FOO;
 
 date '1998-12-01' + interval '90' foo;
-CAST('1998-12-01' AS DATE) + INTERVAL '90' foo;
+CAST('1998-12-01' AS DATE) + INTERVAL '90' FOO;
 
 CAST(x AS DATE) + interval '1' week;
-CAST(x AS DATE) + INTERVAL '1' week;
+CAST(x AS DATE) + INTERVAL '1' WEEK;
 
 CAST('2008-11-11' AS DATETIME) + INTERVAL '5' MONTH;
 CAST('2009-04-11 00:00:00' AS DATETIME);
@@ -456,8 +456,8 @@ CAST('2009-04-11 00:00:00' AS DATETIME);
 datetime '1998-12-01' - interval '90' day;
 CAST('1998-09-02 00:00:00' AS DATETIME);
 
-CAST(x AS DATETIME) + interval '1' week;
-CAST(x AS DATETIME) + INTERVAL '1' week;
+CAST(x AS DATETIME) + interval '1' WEEK;
+CAST(x AS DATETIME) + INTERVAL '1' WEEK;
 
 TS_OR_DS_TO_DATE('1998-12-01 00:00:01') - interval '90' day;
 CAST('1998-09-02' AS DATE);
@@ -844,14 +844,14 @@ x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE);
 
 -- Always false, except for nulls
 DATE_TRUNC('quarter', x) = CAST('2021-01-02' AS DATE);
-DATE_TRUNC('quarter', x) = CAST('2021-01-02' AS DATE);
+DATE_TRUNC('QUARTER', x) = CAST('2021-01-02' AS DATE);
 
 DATE_TRUNC('year', x) <> CAST('2021-01-01' AS DATE);
 FALSE;
 
 -- Always true, except for nulls
 DATE_TRUNC('year', x) <> CAST('2021-01-02' AS DATE);
-DATE_TRUNC('year', x) <> CAST('2021-01-02' AS DATE);
+DATE_TRUNC('YEAR', x) <> CAST('2021-01-02' AS DATE);
 
 DATE_TRUNC('year', x) <= CAST('2021-01-01' AS DATE);
 x < CAST('2022-01-01' AS DATE);
@@ -884,11 +884,11 @@ DATE_TRUNC('year', x) > TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE('2021-01-02'));
 x >= CAST('2022-01-01' AS DATE);
 
 DATE_TRUNC('year', x) > TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE('2021-01-02', '%Y'));
-DATE_TRUNC('year', x) > TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE('2021-01-02', '%Y'));
+DATE_TRUNC('YEAR', x) > CAST(STR_TO_TIME('2021-01-02', '%Y') AS DATE);
 
 -- right is not a date
 DATE_TRUNC('year', x) <> '2021-01-02';
-DATE_TRUNC('year', x) <> '2021-01-02';
+DATE_TRUNC('YEAR', x) <> '2021-01-02';
 
 DATE_TRUNC('year', x) IN (CAST('2021-01-01' AS DATE), CAST('2023-01-01' AS DATE));
 (x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE)) OR (x < CAST('2024-01-01' AS DATE) AND x >= CAST('2023-01-01' AS DATE));
@@ -906,7 +906,7 @@ x < CAST('2022-01-01 00:00:00' AS DATETIME) AND x >= CAST('2021-01-01 00:00:00'
 
 -- right side is not a date literal
 DATE_TRUNC('day', x) = CAST(y AS DATE);
-CAST(y AS DATE) = DATE_TRUNC('day', x);
+CAST(y AS DATE) = DATE_TRUNC('DAY', x);
 
 -- nested cast
 DATE_TRUNC('day', x) = CAST(CAST('2021-01-01 01:02:03' AS DATETIME) AS DATE);
@@ -973,7 +973,7 @@ DATE_ADD(DATE_ADD(DATE_TRUNC('week', DATE_SUB(x, 1, DAY)), 1, DAY), 1, YEAR) < C
 x < CAST('2020-01-14' AS DATE);
 
 x - INTERVAL '1' day = CAST(y AS DATE);
-CAST(y AS DATE) = x - INTERVAL '1' day;
+CAST(y AS DATE) = x - INTERVAL '1' DAY;
 
 --------------------------------------
 -- Constant Propagation
@@ -1094,3 +1094,33 @@ CASE WHEN x = y THEN z END;
 
 CASE x1 + x2 WHEN x3 THEN x4 WHEN x5 + x6 THEN x7 ELSE x8 END;
 CASE WHEN x3 = (x1 + x2) THEN x4 WHEN (x1 + x2) = (x5 + x6) THEN x7 ELSE x8 END;
+
+--------------------------------------
+-- Simplify STARTSWITH
+--------------------------------------
+STARTS_WITH('foo', 'f');
+TRUE;
+
+STARTS_WITH('foo', 'g');
+FALSE;
+
+STARTS_WITH('', 'f');
+FALSE;
+
+STARTS_WITH('', '');
+TRUE;
+
+STARTS_WITH('foo', '');
+TRUE;
+
+STARTS_WITH(NULL, y);
+STARTS_WITH(NULL, y);
+
+STARTS_WITH(x, y);
+STARTS_WITH(x, y);
+
+STARTS_WITH('x', y);
+STARTS_WITH('x', y);
+
+STARTS_WITH(x, 'y');
+STARTS_WITH(x, 'y');
-- 
cgit v1.2.3

