diff options
Diffstat (limited to 'tests/fixtures/optimizer/simplify.sql')
| -rw-r--r-- | tests/fixtures/optimizer/simplify.sql | 54 |
1 files changed, 42 insertions, 12 deletions
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'); |