4 files changed, 92 insertions, 13 deletions
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 74572d2..b318a92 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -944,3 +944,9 @@ SELECT
 FROM "m"
 JOIN "n" AS "foo"("a")
   ON "m"."a" = "foo"."a";
+
+# title: reduction of string concatenation that uses CONCAT(..), || and +
+# execute: false
+SELECT CONCAT('a', 'b') || CONCAT(CONCAT('c', 'd'), CONCAT('e', 'f')) + ('g' || 'h' || 'i');
+SELECT
+  'abcdefghi' AS "_col_0";
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 7ba8e54..3224a83 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -431,6 +431,14 @@ SELECT x.a AS a, i.b AS b FROM x AS x CROSS JOIN UNNEST(SPLIT(x.b, ',')) AS i(b)
 SELECT c FROM (SELECT 1 a) AS x LATERAL VIEW EXPLODE(a) AS c;
 SELECT _q_0.c AS c FROM (SELECT 1 AS a) AS x LATERAL VIEW EXPLODE(x.a) _q_0 AS c;
 
+# execute: false
+SELECT * FROM foo(bar) AS t(c1, c2, c3);
+SELECT t.c1 AS c1, t.c2 AS c2, t.c3 AS c3 FROM FOO(bar) AS t(c1, c2, c3);
+
+# execute: false
+SELECT c1, c3 FROM foo(bar) AS t(c1, c2, c3);
+SELECT t.c1 AS c1, t.c3 AS c3 FROM FOO(bar) AS t(c1, c2, c3);
+
 --------------------------------------
 -- Window functions
 --------------------------------------
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index f821575..3ed02cd 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -240,9 +240,18 @@ A AND B AND C;
 SELECT x WHERE TRUE;
 SELECT x;
 
-SELECT x FROM y LEFT JOIN z ON TRUE;
+SELECT x FROM y JOIN z ON TRUE;
+SELECT x FROM y CROSS JOIN z;
+
+SELECT x FROM y RIGHT JOIN z ON TRUE;
 SELECT x FROM y CROSS JOIN z;
 
+SELECT x FROM y LEFT JOIN z ON TRUE;
+SELECT x FROM y LEFT JOIN z ON TRUE;
+
+SELECT x FROM y FULL OUTER JOIN z ON TRUE;
+SELECT x FROM y FULL OUTER JOIN z ON TRUE;
+
 SELECT x FROM y JOIN z USING (x);
 SELECT x FROM y JOIN z USING (x);
 
@@ -602,3 +611,57 @@ TRUE;
 
 x = 2018 OR x <> 2018;
 x <> 2018 OR x = 2018;
+
+--------------------------------------
+-- Coalesce
+--------------------------------------
+COALESCE(x);
+x;
+
+COALESCE(x, 1) = 2;
+x = 2 AND NOT x IS NULL;
+
+2 = COALESCE(x, 1);
+2 = x AND NOT x IS NULL;
+
+COALESCE(x, 1, 1) = 1 + 1;
+x = 2 AND NOT x IS NULL;
+
+COALESCE(x, 1, 2) = 2;
+x = 2 AND NOT x IS NULL;
+
+COALESCE(x, 3) <= 2;
+x <= 2 AND NOT x IS NULL;
+
+COALESCE(x, 1) <> 2;
+x <> 2 OR x IS NULL;
+
+COALESCE(x, 1) <= 2;
+x <= 2 OR x IS NULL;
+
+COALESCE(x, 1) = 1;
+x = 1 OR x IS NULL;
+
+COALESCE(x, 1) IS NULL;
+FALSE;
+
+--------------------------------------
+-- CONCAT
+--------------------------------------
+CONCAT(x, y);
+CONCAT(x, y);
+
+CONCAT(x);
+x;
+
+CONCAT('a', 'b', 'c');
+'abc';
+
+CONCAT('a', x, y, 'b', 'c');
+CONCAT('a', x, y, 'bc');
+
+'a' || 'b';
+'ab';
+
+'a' || 'b' || x;
+CONCAT('ab', x);
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index 1205c33..f50cf0b 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -857,7 +857,7 @@ WITH "salesreturns" AS (
 ), "cte_10" AS (
   SELECT
     'catalog channel' AS "channel",
-    'catalog_page' || "csr"."cp_catalog_page_id" AS "id",
+    CONCAT('catalog_page', "csr"."cp_catalog_page_id") AS "id",
     "csr"."sales" AS "sales",
     "csr"."returns1" AS "returns1",
     "csr"."profit" - "csr"."profit_loss" AS "profit"
@@ -865,7 +865,7 @@ WITH "salesreturns" AS (
   UNION ALL
   SELECT
     'web channel' AS "channel",
-    'web_site' || "wsr"."web_site_id" AS "id",
+    CONCAT('web_site', "wsr"."web_site_id") AS "id",
     "wsr"."sales" AS "sales",
     "wsr"."returns1" AS "returns1",
     "wsr"."profit" - "wsr"."profit_loss" AS "profit"
@@ -873,7 +873,7 @@ WITH "salesreturns" AS (
 ), "x" AS (
   SELECT
     'store channel' AS "channel",
-    'store' || "ssr"."s_store_id" AS "id",
+    CONCAT('store', "ssr"."s_store_id") AS "id",
     "ssr"."sales" AS "sales",
     "ssr"."returns1" AS "returns1",
     "ssr"."profit" - "ssr"."profit_loss" AS "profit"
@@ -8611,7 +8611,7 @@ WITH "date_dim_2" AS (
     "warehouse"."w_county" AS "w_county",
     "warehouse"."w_state" AS "w_state",
     "warehouse"."w_country" AS "w_country",
-    'ZOUROS' || ',' || 'ZHOU' AS "ship_carriers",
+    'ZOUROS,ZHOU' AS "ship_carriers",
     "date_dim"."d_year" AS "year1",
     SUM(
       CASE
@@ -8806,7 +8806,7 @@ WITH "date_dim_2" AS (
     "warehouse"."w_county" AS "w_county",
     "warehouse"."w_state" AS "w_state",
     "warehouse"."w_country" AS "w_country",
-    'ZOUROS' || ',' || 'ZHOU' AS "ship_carriers",
+    'ZOUROS,ZHOU' AS "ship_carriers",
     "date_dim"."d_year" AS "year1",
     SUM(
       CASE
@@ -10833,9 +10833,11 @@ LEFT JOIN "ws"
   AND "ws"."ws_item_sk" = "ss"."ss_item_sk"
   AND "ws"."ws_sold_year" = "ss"."ss_sold_year"
 WHERE
-  "ss"."ss_sold_year" = 1999
-  AND COALESCE("cs"."cs_qty", 0) > 0
-  AND COALESCE("ws"."ws_qty", 0) > 0
+  "cs"."cs_qty" > 0
+  AND "ss"."ss_sold_year" = 1999
+  AND "ws"."ws_qty" > 0
+  AND NOT "cs"."cs_qty" IS NULL
+  AND NOT "ws"."ws_qty" IS NULL
 ORDER BY
   "ss_item_sk",
   "ss"."ss_qty" DESC,
@@ -11121,7 +11123,7 @@ WITH "date_dim_2" AS (
 ), "cte_4" AS (
   SELECT
     'catalog channel' AS "channel",
-    'catalog_page' || "csr"."catalog_page_id" AS "id",
+    CONCAT('catalog_page', "csr"."catalog_page_id") AS "id",
     "csr"."sales" AS "sales",
     "csr"."returns1" AS "returns1",
     "csr"."profit" AS "profit"
@@ -11129,7 +11131,7 @@ WITH "date_dim_2" AS (
   UNION ALL
   SELECT
     'web channel' AS "channel",
-    'web_site' || "wsr"."web_site_id" AS "id",
+    CONCAT('web_site', "wsr"."web_site_id") AS "id",
     "wsr"."sales" AS "sales",
     "wsr"."returns1" AS "returns1",
     "wsr"."profit" AS "profit"
@@ -11137,7 +11139,7 @@ WITH "date_dim_2" AS (
 ), "x" AS (
   SELECT
     'store channel' AS "channel",
-    'store' || "ssr"."store_id" AS "id",
+    CONCAT('store', "ssr"."store_id") AS "id",
     "ssr"."sales" AS "sales",
     "ssr"."returns1" AS "returns1",
     "ssr"."profit" AS "profit"
@@ -11569,7 +11571,7 @@ ORDER  BY c_customer_id
 LIMIT 100;
 SELECT
   "customer"."c_customer_id" AS "customer_id",
-  "customer"."c_last_name" || ', ' || "customer"."c_first_name" AS "customername"
+  CONCAT("customer"."c_last_name", ', ', "customer"."c_first_name") AS "customername"
 FROM "customer" AS "customer"
 JOIN "customer_address" AS "customer_address"
   ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"