From bb7e519f74dbddaf174a9606b590c281d4ca4913 Mon Sep 17 00:00:00 2001
From: Pallavi Sontakke <pallavi.sontakke@2ndQuadrant.com>
Date: Fri, 17 Jun 2016 14:39:21 +0530
Subject: [PATCH] Add test for complex-update issue #38

---
 src/test/regress/expected/xl_known_bugs.out | 42 +++++++++++++++++
 src/test/regress/sql/xl_known_bugs.sql      | 51 +++++++++++++++++++++
 2 files changed, 93 insertions(+)

diff --git a/src/test/regress/expected/xl_known_bugs.out b/src/test/regress/expected/xl_known_bugs.out
index bd210881a2..064390e2a0 100644
--- a/src/test/regress/expected/xl_known_bugs.out
+++ b/src/test/regress/expected/xl_known_bugs.out
@@ -751,3 +751,45 @@ FETCH FIRST xl_ins_cur;
 COMMIT;
 drop table xl_PLine;
 ------------------------------------------
+-- Issue #38 - complex update
+-- In complex update using FROM list with explicit join, equation clause ‘=’ for integer is not supported
+-- distributed by default by HASH(tmpunique1)
+CREATE TABLE xl_tmp (
+tmpunique1 int4,
+stringu1 name,
+stringu2 name,
+string4 name
+);
+-- distributed by default by HASH(unique1)
+CREATE TABLE xl_onek (
+unique1 int4,
+unique2 int4,
+two int4,
+four int4,
+stringu1 name,
+stringu2 name,
+string4 name
+);
+UPDATE xl_tmp
+   SET stringu1 = xl_onek.stringu1
+   FROM xl_onek
+   WHERE xl_onek.unique1 <= 3 and
+	  xl_onek.unique1 = xl_tmp.tmpunique1;
+UPDATE xl_tmp
+   SET stringu1 = xl_onek.stringu1
+   FROM xl_onek
+   WHERE xl_onek.unique1 = 3 and
+	  xl_onek.unique1 = xl_tmp.tmpunique1;
+UPDATE xl_tmp
+   SET stringu1 = xl_onek.stringu1
+   FROM xl_onek
+   WHERE xl_tmp.tmpunique1 <= 3 and
+	  xl_onek.unique1 = xl_tmp.tmpunique1;
+UPDATE xl_tmp
+   SET stringu1 = xl_onek.stringu1
+   FROM xl_onek
+   WHERE xl_tmp.tmpunique1 = 3 and
+	  xl_onek.unique1 = xl_tmp.tmpunique1;
+drop table xl_tmp;
+drop table xl_onek;
+------------------------------------------
diff --git a/src/test/regress/sql/xl_known_bugs.sql b/src/test/regress/sql/xl_known_bugs.sql
index 093e0419e9..38803c9886 100644
--- a/src/test/regress/sql/xl_known_bugs.sql
+++ b/src/test/regress/sql/xl_known_bugs.sql
@@ -302,3 +302,54 @@ COMMIT;
 drop table xl_PLine;
 
 ------------------------------------------
+
+-- Issue #38 - complex update
+
+-- In complex update using FROM list with explicit join, equation clause ‘=’ for integer is not supported
+
+-- distributed by default by HASH(tmpunique1)
+CREATE TABLE xl_tmp (
+tmpunique1 int4,
+stringu1 name,
+stringu2 name,
+string4 name
+);
+-- distributed by default by HASH(unique1)
+CREATE TABLE xl_onek (
+unique1 int4,
+unique2 int4,
+two int4,
+four int4,
+stringu1 name,
+stringu2 name,
+string4 name
+);
+
+UPDATE xl_tmp
+   SET stringu1 = xl_onek.stringu1
+   FROM xl_onek
+   WHERE xl_onek.unique1 <= 3 and
+	  xl_onek.unique1 = xl_tmp.tmpunique1;
+
+UPDATE xl_tmp
+   SET stringu1 = xl_onek.stringu1
+   FROM xl_onek
+   WHERE xl_onek.unique1 = 3 and
+	  xl_onek.unique1 = xl_tmp.tmpunique1;
+
+UPDATE xl_tmp
+   SET stringu1 = xl_onek.stringu1
+   FROM xl_onek
+   WHERE xl_tmp.tmpunique1 <= 3 and
+	  xl_onek.unique1 = xl_tmp.tmpunique1;
+
+UPDATE xl_tmp
+   SET stringu1 = xl_onek.stringu1
+   FROM xl_onek
+   WHERE xl_tmp.tmpunique1 = 3 and
+	  xl_onek.unique1 = xl_tmp.tmpunique1;
+
+drop table xl_tmp;
+drop table xl_onek;
+
+------------------------------------------
-- 
2.39.5