hawq-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From r...@apache.org
Subject [01/15] incubator-hawq git commit: HAWQ-806. Add feature test for subplan with new framework
Date Mon, 18 Jul 2016 10:12:25 GMT
Repository: incubator-hawq
Updated Branches:
  refs/heads/2.0.0.0-incubating 727117ff4 -> b0d161553


HAWQ-806. Add feature test for subplan with new framework


Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/290e94d5
Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/290e94d5
Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/290e94d5

Branch: refs/heads/2.0.0.0-incubating
Commit: 290e94d59f7e24831c1374c689e8c83a1dad5a87
Parents: b181087
Author: xunzhang <xunzhangthu@gmail.com>
Authored: Thu Jun 16 21:56:56 2016 +0800
Committer: rlei <rlei@pivotal.io>
Committed: Mon Jul 18 18:06:07 2016 +0800

----------------------------------------------------------------------
 src/test/feature/planner/ans/subplan.ans  | 374 +++++++++++++++++++++++++
 src/test/feature/planner/sql/subplan.sql  | 140 +++++++++
 src/test/feature/planner/test_subplan.cpp |  15 +
 src/test/regress/expected/subplan.out     | 337 ----------------------
 src/test/regress/known_good_schedule      |   1 -
 src/test/regress/sql/subplan.sql          | 140 ---------
 6 files changed, 529 insertions(+), 478 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/290e94d5/src/test/feature/planner/ans/subplan.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/planner/ans/subplan.ans b/src/test/feature/planner/ans/subplan.ans
new file mode 100644
index 0000000..7335b90
--- /dev/null
+++ b/src/test/feature/planner/ans/subplan.ans
@@ -0,0 +1,374 @@
+-- start_ignore
+SET SEARCH_PATH=TestSubplan_TestSubplanAll;
+SET
+-- end_ignore
+--
+-- These tests are intended to cover GPSQL-1260.  Which means queries
+-- whose plan contains combinations of InitPlan and SubPlan nodes.
+--
+-- Derived from //cdbfast/main/subquery/mpp8334/
+-- SUITE: hash-vs-nl-not-in
+-- start_ignore
+drop schema if exists subplan_tests cascade;
+psql:/tmp/TestSubplan_TestSubplanAll.sql:12: NOTICE:  schema "subplan_tests" does not exist,
skipping
+DROP SCHEMA
+-- end_ignore
+create schema subplan_tests;
+CREATE SCHEMA
+set search_path=subplan_tests;
+SET
+create table t1(a int, b int) distributed by (a);
+CREATE TABLE
+insert into t1 select i, i+10 from generate_series(-5,5)i;
+INSERT 0 11
+create table i3(a int not null, b int not null) distributed by (a);
+CREATE TABLE
+insert into i3 select i-1, i from generate_series(1,5)i;
+INSERT 0 5
+create table i4(a int, b int) distributed by (a);
+CREATE TABLE
+insert into i4 values(null,null);
+INSERT 0 1
+insert into i4 select i, i-10 from generate_series(-5,0)i;
+INSERT 0 6
+DROP LANGUAGE IF EXISTS plpythonu CASCADE;
+psql:/tmp/TestSubplan_TestSubplanAll.sql:27: NOTICE:  language "plpythonu" does not exist,
skipping
+DROP LANGUAGE
+CREATE LANGUAGE plpythonu;
+CREATE LANGUAGE
+create or replace function twice(int) returns int as $$
+       select 2 * $1;
+$$ language sql;
+CREATE FUNCTION
+create or replace function half(int) returns int as $$
+begin
+	return $1 / 2;
+end;
+$$ language plpgsql;
+CREATE FUNCTION
+create or replace function thrice(x int) returns int as $$
+    if (x is None):
+        return 0
+    else:
+        return x * 3
+$$ language plpythonu;
+CREATE FUNCTION
+select t1.* from t1 where (t1.a, t1.b) not in
+   (select twice(i3.a), i3.b from i3 union select i4.a, thrice(i4.b) from i4);
+ a  | b  
+----+----
+ -2 |  8
+  3 | 13
+  5 | 15
+  4 | 14
+ -1 |  9
+  0 | 10
+  2 | 12
+ -5 |  5
+ -3 |  7
+ -4 |  6
+  1 | 11
+(11 rows)
+
+select t1.* from t1 where (t1.a, half(t1.b)) not in
+   (select twice(i3.a), i3.b from i3 union all select i4.a, i4.b from i4);
+ a | b 
+---+---
+(0 rows)
+
+select t1.a, half(t1.b) from t1 where (t1.a, t1.b) not in
+   (select 1, thrice(2) union select 3, 4);
+ a  | half 
+----+------
+  4 |    7
+ -5 |    2
+ -3 |    3
+ -4 |    3
+  1 |    5
+ -1 |    4
+  0 |    5
+  2 |    6
+ -2 |    4
+  3 |    6
+  5 |    7
+(11 rows)
+
+select t1.* from t1 where (half(t1.a), t1.b) not in
+   (select thrice(i3.a), i3.b from i3 union select i4.a, i4.b from i4);
+ a | b 
+---+---
+(0 rows)
+
+select t1.* from t1 where (t1.a, t1.b) not in
+   (select i3.a, half(i3.b) from i3 union all
+      select i4.a, thrice(i4.b) from i4);
+ a  | b  
+----+----
+  4 | 14
+ -1 |  9
+  0 | 10
+  2 | 12
+ -5 |  5
+ -3 |  7
+ -2 |  8
+  3 | 13
+  5 | 15
+ -4 |  6
+  1 | 11
+(11 rows)
+
+-- Two SubPlan nodes
+select t1.* from t1 where (t1.a, t1.b) not in (select i3.a, i3.b from i3) or
+   (t1.a, t1.b) not in (select i4.a, i4.b from i4);
+ a  | b  
+----+----
+ -2 |  8
+  3 | 13
+  5 | 15
+ -1 |  9
+  0 | 10
+  2 | 12
+ -4 |  6
+  1 | 11
+ -5 |  5
+ -3 |  7
+  4 | 14
+(11 rows)
+
+-- Two SubPlan nodes
+select t1.* from t1 where
+   (t1.a, twice(t1.b)) not in (select thrice(i3.a), i3.b from i3) or
+      (t1.a, half(t1.b)) not in (select i4.a, i4.b from i4);
+ a  | b  
+----+----
+ -4 |  6
+  1 | 11
+  4 | 14
+ -2 |  8
+  3 | 13
+  5 | 15
+ -5 |  5
+ -3 |  7
+ -1 |  9
+  0 | 10
+  2 | 12
+(11 rows)
+
+-- Two SubPlan nodes
+select t1.* from t1 where (t1.a, t1.b) not in (select i3.a,i3.b from i3) or
+   (t1.a, half(t1.b)) not in (select thrice(i4.a), i4.b from i4);
+ a  | b  
+----+----
+ -4 |  6
+  1 | 11
+  4 | 14
+ -1 |  9
+  0 | 10
+  2 | 12
+ -2 |  8
+  3 | 13
+  5 | 15
+ -5 |  5
+ -3 |  7
+(11 rows)
+
+-- SUITE: diff-rel-cols-not-in
+truncate table t1;
+TRUNCATE TABLE
+create table t2(a int, b int) distributed by (a);
+CREATE TABLE
+insert into t1 select i, i-10 from generate_series(-1,3)i;
+INSERT 0 5
+insert into t2 select i, i-10 from generate_series(2,5)i;
+INSERT 0 4
+create table i1(a int, b int) distributed by (a);
+CREATE TABLE
+insert into i1 select i, i-10 from generate_series(3,6)i;
+INSERT 0 4
+create or replace function twice(int) returns int as $$
+       select 2 * $1;
+$$ language sql;
+CREATE FUNCTION
+create or replace function half(int) returns int as $$
+begin
+	return $1 / 2;
+end;
+$$ language plpgsql;
+CREATE FUNCTION
+create or replace function thrice(x int) returns int as $$
+    if x is not None:
+        return x * 3
+    return 0
+$$ language plpythonu;
+CREATE FUNCTION
+select t1.a, twice(t2.b) from t1, t2 where t1.a = half(t2.a) or
+   ((t1.a, t2.b) not in (select i1.a, thrice(i1.b) from i1));
+ a  | twice 
+----+-------
+  3 |   -14
+  3 |   -10
+  1 |   -14
+  1 |   -10
+ -1 |   -14
+ -1 |   -10
+  0 |   -14
+  0 |   -10
+  2 |   -14
+  2 |   -10
+  3 |   -16
+  1 |   -16
+ -1 |   -16
+  0 |   -16
+  2 |   -16
+  3 |   -12
+  1 |   -12
+ -1 |   -12
+  0 |   -12
+  2 |   -12
+(20 rows)
+
+select t1.a, t2.b from t1 left join t2 on
+   (t1.a = t2.a and ((t1.a, half(t2.b)) not in (select i1.a, i1.b from i1)));
+ a  | b  
+----+----
+  1 |   
+ -1 |   
+  0 |   
+  2 | -8
+  3 | -7
+(5 rows)
+
+select t1.a, t2.b from t1, t2 where t1.a = t2.a or
+   ((t1.a, t2.b) not in (select thrice(i1.a), i1.b from i1));
+ a  | b  
+----+----
+  1 | -6
+ -1 | -6
+  0 | -6
+  2 | -6
+  3 | -6
+  1 | -8
+ -1 | -8
+  0 | -8
+  2 | -8
+  3 | -8
+  1 | -7
+  1 | -5
+ -1 | -7
+ -1 | -5
+  0 | -7
+  0 | -5
+  2 | -7
+  2 | -5
+  3 | -7
+  3 | -5
+(20 rows)
+
+select t1.a, t2.b from t1 left join t2 on
+   (thrice(t1.a) = thrice(t2.a) and
+      ((t1.a, t2.b) not in (select i1.a, i1.b from i1)));
+ a  | b  
+----+----
+  1 |   
+  3 |   
+ -1 |   
+  0 |   
+  2 | -8
+(5 rows)
+
+select t1.a, t2.b from t1, t2 where t1.a = t2.a or
+   ((t1.a, t2.b) not in (select i1.a, half(i1.b) from i1));
+ a  | b  
+----+----
+  3 | -6
+  1 | -6
+ -1 | -6
+  0 | -6
+  2 | -6
+  3 | -7
+  3 | -5
+  1 | -7
+  1 | -5
+ -1 | -7
+ -1 | -5
+  0 | -7
+  0 | -5
+  2 | -7
+  2 | -5
+  3 | -8
+  1 | -8
+ -1 | -8
+  0 | -8
+  2 | -8
+(20 rows)
+
+select t1.a, t2.b from t1 left join t2 on
+   (t1.a = t2.a and
+      ((t1.a, twice(t2.b)) not in (select i1.a, thrice(i1.b) from i1)));
+ a  | b  
+----+----
+  1 |   
+ -1 |   
+  0 |   
+  2 | -8
+  3 | -7
+(5 rows)
+
+-- From MPP-2869
+create table bug_data (domain integer, class integer, attr text, value integer)
+   distributed by (domain);
+CREATE TABLE
+insert into bug_data values(1, 1, 'A', 1);
+INSERT 0 1
+insert into bug_data values(2, 1, 'A', 0);
+INSERT 0 1
+insert into bug_data values(3, 0, 'B', 1);
+INSERT 0 1
+-- This one is contains one InitPlan without any SubPlan.
+create table foo as 
+SELECT attr, class, (select thrice(count(distinct class)::int) from bug_data)
+   as dclass FROM bug_data GROUP BY attr, class distributed by (attr);
+SELECT 2
+-- Query from GPSQL-1260, produces InitPlan and a SubPlan.
+create or replace function nop(a int) returns int as $$ return a $$
+language plpythonu;
+CREATE FUNCTION
+create table toy as select generate_series(1, 10) i distributed by (i);
+SELECT 10
+select * from toy; -- only for debugging
+ i  
+----
+  8
+ 10
+  2
+  1
+  9
+  4
+  6
+  3
+  5
+  7
+(10 rows)
+
+select array(select nop(i) from toy order by i);
+        ?column?        
+------------------------
+ {1,2,3,4,5,6,7,8,9,10}
+(1 row)
+
+-- start_ignore
+drop schema subplan_tests cascade;
+psql:/tmp/TestSubplan_TestSubplanAll.sql:142: NOTICE:  drop cascades to append only table
toy
+psql:/tmp/TestSubplan_TestSubplanAll.sql:142: NOTICE:  drop cascades to function nop(integer)
+psql:/tmp/TestSubplan_TestSubplanAll.sql:142: NOTICE:  drop cascades to append only table
foo
+psql:/tmp/TestSubplan_TestSubplanAll.sql:142: NOTICE:  drop cascades to append only table
bug_data
+psql:/tmp/TestSubplan_TestSubplanAll.sql:142: NOTICE:  drop cascades to function thrice(integer)
+psql:/tmp/TestSubplan_TestSubplanAll.sql:142: NOTICE:  drop cascades to function half(integer)
+psql:/tmp/TestSubplan_TestSubplanAll.sql:142: NOTICE:  drop cascades to function twice(integer)
+psql:/tmp/TestSubplan_TestSubplanAll.sql:142: NOTICE:  drop cascades to append only table
i1
+psql:/tmp/TestSubplan_TestSubplanAll.sql:142: NOTICE:  drop cascades to append only table
t2
+psql:/tmp/TestSubplan_TestSubplanAll.sql:142: NOTICE:  drop cascades to append only table
i4
+psql:/tmp/TestSubplan_TestSubplanAll.sql:142: NOTICE:  drop cascades to append only table
i3
+psql:/tmp/TestSubplan_TestSubplanAll.sql:142: NOTICE:  drop cascades to append only table
t1
+DROP SCHEMA
+-- end_ignore

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/290e94d5/src/test/feature/planner/sql/subplan.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/planner/sql/subplan.sql b/src/test/feature/planner/sql/subplan.sql
new file mode 100644
index 0000000..a1ffb6c
--- /dev/null
+++ b/src/test/feature/planner/sql/subplan.sql
@@ -0,0 +1,140 @@
+--
+-- These tests are intended to cover GPSQL-1260.  Which means queries
+-- whose plan contains combinations of InitPlan and SubPlan nodes.
+--
+-- Derived from //cdbfast/main/subquery/mpp8334/
+
+-- SUITE: hash-vs-nl-not-in
+-- start_ignore
+drop schema if exists subplan_tests cascade;
+-- end_ignore
+create schema subplan_tests;
+set search_path=subplan_tests;
+
+create table t1(a int, b int) distributed by (a);
+insert into t1 select i, i+10 from generate_series(-5,5)i;
+
+create table i3(a int not null, b int not null) distributed by (a);
+insert into i3 select i-1, i from generate_series(1,5)i;
+
+create table i4(a int, b int) distributed by (a);
+insert into i4 values(null,null);
+insert into i4 select i, i-10 from generate_series(-5,0)i;
+
+DROP LANGUAGE IF EXISTS plpythonu CASCADE;
+CREATE LANGUAGE plpythonu;
+
+create or replace function twice(int) returns int as $$
+       select 2 * $1;
+$$ language sql;
+
+create or replace function half(int) returns int as $$
+begin
+	return $1 / 2;
+end;
+$$ language plpgsql;
+
+create or replace function thrice(x int) returns int as $$
+    if (x is None):
+        return 0
+    else:
+        return x * 3
+$$ language plpythonu;
+
+select t1.* from t1 where (t1.a, t1.b) not in
+   (select twice(i3.a), i3.b from i3 union select i4.a, thrice(i4.b) from i4);
+
+select t1.* from t1 where (t1.a, half(t1.b)) not in
+   (select twice(i3.a), i3.b from i3 union all select i4.a, i4.b from i4);
+
+select t1.a, half(t1.b) from t1 where (t1.a, t1.b) not in
+   (select 1, thrice(2) union select 3, 4);
+
+select t1.* from t1 where (half(t1.a), t1.b) not in
+   (select thrice(i3.a), i3.b from i3 union select i4.a, i4.b from i4);
+
+select t1.* from t1 where (t1.a, t1.b) not in
+   (select i3.a, half(i3.b) from i3 union all
+      select i4.a, thrice(i4.b) from i4);
+
+-- Two SubPlan nodes
+select t1.* from t1 where (t1.a, t1.b) not in (select i3.a, i3.b from i3) or
+   (t1.a, t1.b) not in (select i4.a, i4.b from i4);
+
+-- Two SubPlan nodes
+select t1.* from t1 where
+   (t1.a, twice(t1.b)) not in (select thrice(i3.a), i3.b from i3) or
+      (t1.a, half(t1.b)) not in (select i4.a, i4.b from i4);
+
+-- Two SubPlan nodes
+select t1.* from t1 where (t1.a, t1.b) not in (select i3.a,i3.b from i3) or
+   (t1.a, half(t1.b)) not in (select thrice(i4.a), i4.b from i4);
+
+-- SUITE: diff-rel-cols-not-in
+truncate table t1;
+create table t2(a int, b int) distributed by (a);
+
+insert into t1 select i, i-10 from generate_series(-1,3)i;
+insert into t2 select i, i-10 from generate_series(2,5)i;
+
+create table i1(a int, b int) distributed by (a);
+insert into i1 select i, i-10 from generate_series(3,6)i;
+
+create or replace function twice(int) returns int as $$
+       select 2 * $1;
+$$ language sql;
+
+create or replace function half(int) returns int as $$
+begin
+	return $1 / 2;
+end;
+$$ language plpgsql;
+
+create or replace function thrice(x int) returns int as $$
+    if x is not None:
+        return x * 3
+    return 0
+$$ language plpythonu;
+
+select t1.a, twice(t2.b) from t1, t2 where t1.a = half(t2.a) or
+   ((t1.a, t2.b) not in (select i1.a, thrice(i1.b) from i1));
+
+select t1.a, t2.b from t1 left join t2 on
+   (t1.a = t2.a and ((t1.a, half(t2.b)) not in (select i1.a, i1.b from i1)));
+
+select t1.a, t2.b from t1, t2 where t1.a = t2.a or
+   ((t1.a, t2.b) not in (select thrice(i1.a), i1.b from i1));
+
+select t1.a, t2.b from t1 left join t2 on
+   (thrice(t1.a) = thrice(t2.a) and
+      ((t1.a, t2.b) not in (select i1.a, i1.b from i1)));
+
+select t1.a, t2.b from t1, t2 where t1.a = t2.a or
+   ((t1.a, t2.b) not in (select i1.a, half(i1.b) from i1));
+
+select t1.a, t2.b from t1 left join t2 on
+   (t1.a = t2.a and
+      ((t1.a, twice(t2.b)) not in (select i1.a, thrice(i1.b) from i1)));
+
+-- From MPP-2869
+create table bug_data (domain integer, class integer, attr text, value integer)
+   distributed by (domain);
+insert into bug_data values(1, 1, 'A', 1);
+insert into bug_data values(2, 1, 'A', 0);
+insert into bug_data values(3, 0, 'B', 1);
+
+-- This one is contains one InitPlan without any SubPlan.
+create table foo as 
+SELECT attr, class, (select thrice(count(distinct class)::int) from bug_data)
+   as dclass FROM bug_data GROUP BY attr, class distributed by (attr);
+
+-- Query from GPSQL-1260, produces InitPlan and a SubPlan.
+create or replace function nop(a int) returns int as $$ return a $$
+language plpythonu;
+create table toy as select generate_series(1, 10) i distributed by (i);
+select * from toy; -- only for debugging
+select array(select nop(i) from toy order by i);
+
+-- start_ignore
+drop schema subplan_tests cascade;
+-- end_ignore

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/290e94d5/src/test/feature/planner/test_subplan.cpp
----------------------------------------------------------------------
diff --git a/src/test/feature/planner/test_subplan.cpp b/src/test/feature/planner/test_subplan.cpp
new file mode 100644
index 0000000..192c10c
--- /dev/null
+++ b/src/test/feature/planner/test_subplan.cpp
@@ -0,0 +1,15 @@
+#include "gtest/gtest.h"
+
+#include "lib/sql_util.h"
+
+class TestSubplan : public ::testing::Test {
+ public:
+  TestSubplan() {}
+  ~TestSubplan() {}
+};
+
+TEST_F(TestSubplan, TestSubplanAll) {
+ hawq::test::SQLUtility util;
+ util.execSQLFile("planner/sql/subplan.sql",
+                  "planner/ans/subplan.ans");
+}

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/290e94d5/src/test/regress/expected/subplan.out
----------------------------------------------------------------------
diff --git a/src/test/regress/expected/subplan.out b/src/test/regress/expected/subplan.out
deleted file mode 100644
index 6854bcd..0000000
--- a/src/test/regress/expected/subplan.out
+++ /dev/null
@@ -1,337 +0,0 @@
---
--- These tests are intended to cover GPSQL-1260.  Which means queries
--- whose plan contains combinations of InitPlan and SubPlan nodes.
---
--- Derived from //cdbfast/main/subquery/mpp8334/
--- SUITE: hash-vs-nl-not-in
--- start_ignore
-drop schema subplan_tests cascade;
-ERROR:  schema "subplan_tests" does not exist
--- end_ignore
-create schema subplan_tests;
-set search_path=subplan_tests;
-create table t1(a int, b int) distributed by (a);
-insert into t1 select i, i+10 from generate_series(-5,5)i;
-create table i3(a int not null, b int not null) distributed by (a);
-insert into i3 select i-1, i from generate_series(1,5)i;
-create table i4(a int, b int) distributed by (a);
-insert into i4 values(null,null);
-insert into i4 select i, i-10 from generate_series(-5,0)i;
-DROP LANGUAGE IF EXISTS plpythonu CASCADE;
-CREATE LANGUAGE plpythonu;
-create or replace function twice(int) returns int as $$
-       select 2 * $1;
-$$ language sql;
-create or replace function half(int) returns int as $$
-begin
-	return $1 / 2;
-end;
-$$ language plpgsql;
-create or replace function thrice(x int) returns int as $$
-    if (x is None):
-        return 0
-    else:
-        return x * 3
-$$ language plpythonu;
-select t1.* from t1 where (t1.a, t1.b) not in
-   (select twice(i3.a), i3.b from i3 union select i4.a, thrice(i4.b) from i4);
- a  | b  
-----+----
- -4 |  6
- -2 |  8
-  1 | 11
-  3 | 13
-  5 | 15
- -5 |  5
- -3 |  7
- -1 |  9
-  0 | 10
-  2 | 12
-  4 | 14
-(11 rows)
-
-select t1.* from t1 where (t1.a, half(t1.b)) not in
-   (select twice(i3.a), i3.b from i3 union all select i4.a, i4.b from i4);
- a | b 
----+---
-(0 rows)
-
-select t1.a, half(t1.b) from t1 where (t1.a, t1.b) not in
-   (select 1, thrice(2) union select 3, 4);
- a  | half 
-----+------
- -5 |    2
- -3 |    3
- -1 |    4
-  0 |    5
-  2 |    6
-  4 |    7
- -4 |    3
- -2 |    4
-  1 |    5
-  3 |    6
-  5 |    7
-(11 rows)
-
-select t1.* from t1 where (half(t1.a), t1.b) not in
-   (select thrice(i3.a), i3.b from i3 union select i4.a, i4.b from i4);
- a | b 
----+---
-(0 rows)
-
-select t1.* from t1 where (t1.a, t1.b) not in
-   (select i3.a, half(i3.b) from i3 union all
-      select i4.a, thrice(i4.b) from i4);
- a  | b  
-----+----
- -5 |  5
- -3 |  7
- -1 |  9
-  0 | 10
-  2 | 12
-  4 | 14
- -4 |  6
- -2 |  8
-  1 | 11
-  3 | 13
-  5 | 15
-(11 rows)
-
--- Two SubPlan nodes
-select t1.* from t1 where (t1.a, t1.b) not in (select i3.a, i3.b from i3) or
-   (t1.a, t1.b) not in (select i4.a, i4.b from i4);
- a  | b  
-----+----
- -5 |  5
- -3 |  7
- -1 |  9
-  0 | 10
-  2 | 12
-  4 | 14
- -4 |  6
- -2 |  8
-  1 | 11
-  3 | 13
-  5 | 15
-(11 rows)
-
--- Two SubPlan nodes
-select t1.* from t1 where
-   (t1.a, twice(t1.b)) not in (select thrice(i3.a), i3.b from i3) or
-      (t1.a, half(t1.b)) not in (select i4.a, i4.b from i4);
- a  | b  
-----+----
- -4 |  6
- -2 |  8
-  1 | 11
-  3 | 13
-  5 | 15
- -5 |  5
- -3 |  7
- -1 |  9
-  0 | 10
-  2 | 12
-  4 | 14
-(11 rows)
-
--- Two SubPlan nodes
-select t1.* from t1 where (t1.a, t1.b) not in (select i3.a,i3.b from i3) or
-   (t1.a, half(t1.b)) not in (select thrice(i4.a), i4.b from i4);
- a  | b  
-----+----
- -5 |  5
- -3 |  7
- -1 |  9
-  0 | 10
-  2 | 12
-  4 | 14
- -4 |  6
- -2 |  8
-  1 | 11
-  3 | 13
-  5 | 15
-(11 rows)
-
--- SUITE: diff-rel-cols-not-in
-truncate table t1;
-create table t2(a int, b int) distributed by (a);
-insert into t1 select i, i-10 from generate_series(-1,3)i;
-insert into t2 select i, i-10 from generate_series(2,5)i;
-create table i1(a int, b int) distributed by (a);
-insert into i1 select i, i-10 from generate_series(3,6)i;
-create or replace function twice(int) returns int as $$
-       select 2 * $1;
-$$ language sql;
-create or replace function half(int) returns int as $$
-begin
-	return $1 / 2;
-end;
-$$ language plpgsql;
-create or replace function thrice(x int) returns int as $$
-    if x is not None:
-        return x * 3
-    return 0
-$$ language plpythonu;
-select t1.a, twice(t2.b) from t1, t2 where t1.a = half(t2.a) or
-   ((t1.a, t2.b) not in (select i1.a, thrice(i1.b) from i1));
- a  | twice 
-----+-------
-  1 |   -16
-  1 |   -12
-  3 |   -16
-  3 |   -12
- -1 |   -16
- -1 |   -12
-  0 |   -16
-  0 |   -12
-  2 |   -16
-  2 |   -12
-  1 |   -14
-  1 |   -10
-  3 |   -14
-  3 |   -10
- -1 |   -14
- -1 |   -10
-  0 |   -14
-  0 |   -10
-  2 |   -14
-  2 |   -10
-(20 rows)
-
-select t1.a, t2.b from t1 left join t2 on
-   (t1.a = t2.a and ((t1.a, half(t2.b)) not in (select i1.a, i1.b from i1)));
- a  | b  
-----+----
- -1 |   
-  0 |   
-  2 | -8
-  1 |   
-  3 | -7
-(5 rows)
-
-select t1.a, t2.b from t1, t2 where t1.a = t2.a or
-   ((t1.a, t2.b) not in (select thrice(i1.a), i1.b from i1));
- a  | b  
-----+----
-  1 | -7
-  1 | -5
-  3 | -7
-  3 | -5
- -1 | -7
- -1 | -5
-  0 | -7
-  0 | -5
-  2 | -7
-  2 | -5
-  1 | -8
-  1 | -6
-  3 | -8
-  3 | -6
- -1 | -8
- -1 | -6
-  0 | -8
-  0 | -6
-  2 | -8
-  2 | -6
-(20 rows)
-
-select t1.a, t2.b from t1 left join t2 on
-   (thrice(t1.a) = thrice(t2.a) and
-      ((t1.a, t2.b) not in (select i1.a, i1.b from i1)));
- a  | b  
-----+----
-  1 |   
-  3 |   
- -1 |   
-  0 |   
-  2 | -8
-(5 rows)
-
-select t1.a, t2.b from t1, t2 where t1.a = t2.a or
-   ((t1.a, t2.b) not in (select i1.a, half(i1.b) from i1));
- a  | b  
-----+----
-  1 | -7
-  1 | -5
-  3 | -7
-  3 | -5
- -1 | -7
- -1 | -5
-  0 | -7
-  0 | -5
-  2 | -7
-  2 | -5
-  1 | -8
-  1 | -6
-  3 | -8
-  3 | -6
- -1 | -8
- -1 | -6
-  0 | -8
-  0 | -6
-  2 | -8
-  2 | -6
-(20 rows)
-
-select t1.a, t2.b from t1 left join t2 on
-   (t1.a = t2.a and
-      ((t1.a, twice(t2.b)) not in (select i1.a, thrice(i1.b) from i1)));
- a  | b  
-----+----
- -1 |   
-  0 |   
-  2 | -8
-  1 |   
-  3 | -7
-(5 rows)
-
--- From MPP-2869
-create table bug_data (domain integer, class integer, attr text, value integer)
-   distributed by (domain);
-insert into bug_data values(1, 1, 'A', 1);
-insert into bug_data values(2, 1, 'A', 0);
-insert into bug_data values(3, 0, 'B', 1);
--- This one is contains one InitPlan without any SubPlan.
-create table foo as 
-SELECT attr, class, (select thrice(count(distinct class)::int) from bug_data)
-   as dclass FROM bug_data GROUP BY attr, class distributed by (attr);
--- Query from GPSQL-1260, produces InitPlan and a SubPlan.
-create or replace function nop(a int) returns int as $$ return a $$
-language plpythonu;
-create table toy as select generate_series(1, 10) i distributed by (i);
-select * from toy; -- only for debugging
- i  
-----
-  2
-  4
-  6
-  8
- 10
-  1
-  3
-  5
-  7
-  9
-(10 rows)
-
-select array(select nop(i) from toy order by i);
-        ?column?        
-------------------------
- {1,2,3,4,5,6,7,8,9,10}
-(1 row)
-
--- start_ignore
-drop schema subplan_tests cascade;
-NOTICE:  drop cascades to append only table toy
-NOTICE:  drop cascades to function nop(integer)
-NOTICE:  drop cascades to append only table foo
-NOTICE:  drop cascades to append only table bug_data
-NOTICE:  drop cascades to function thrice(integer)
-NOTICE:  drop cascades to function half(integer)
-NOTICE:  drop cascades to function twice(integer)
-NOTICE:  drop cascades to append only table i1
-NOTICE:  drop cascades to append only table t2
-NOTICE:  drop cascades to append only table i4
-NOTICE:  drop cascades to append only table i3
-NOTICE:  drop cascades to append only table t1
--- end_ignore

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/290e94d5/src/test/regress/known_good_schedule
----------------------------------------------------------------------
diff --git a/src/test/regress/known_good_schedule b/src/test/regress/known_good_schedule
index d408439..0cd48f3 100755
--- a/src/test/regress/known_good_schedule
+++ b/src/test/regress/known_good_schedule
@@ -57,7 +57,6 @@ ignore: oidjoins
 ignore: opr_sanity
 ignore: geometry
 ignore: horology
-test: subplan
 ignore: create_type
 test: create_table_test
 test: create_table_distribution

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/290e94d5/src/test/regress/sql/subplan.sql
----------------------------------------------------------------------
diff --git a/src/test/regress/sql/subplan.sql b/src/test/regress/sql/subplan.sql
deleted file mode 100644
index 82dda0d..0000000
--- a/src/test/regress/sql/subplan.sql
+++ /dev/null
@@ -1,140 +0,0 @@
---
--- These tests are intended to cover GPSQL-1260.  Which means queries
--- whose plan contains combinations of InitPlan and SubPlan nodes.
---
--- Derived from //cdbfast/main/subquery/mpp8334/
-
--- SUITE: hash-vs-nl-not-in
--- start_ignore
-drop schema subplan_tests cascade;
--- end_ignore
-create schema subplan_tests;
-set search_path=subplan_tests;
-
-create table t1(a int, b int) distributed by (a);
-insert into t1 select i, i+10 from generate_series(-5,5)i;
-
-create table i3(a int not null, b int not null) distributed by (a);
-insert into i3 select i-1, i from generate_series(1,5)i;
-
-create table i4(a int, b int) distributed by (a);
-insert into i4 values(null,null);
-insert into i4 select i, i-10 from generate_series(-5,0)i;
-
-DROP LANGUAGE IF EXISTS plpythonu CASCADE;
-CREATE LANGUAGE plpythonu;
-
-create or replace function twice(int) returns int as $$
-       select 2 * $1;
-$$ language sql;
-
-create or replace function half(int) returns int as $$
-begin
-	return $1 / 2;
-end;
-$$ language plpgsql;
-
-create or replace function thrice(x int) returns int as $$
-    if (x is None):
-        return 0
-    else:
-        return x * 3
-$$ language plpythonu;
-
-select t1.* from t1 where (t1.a, t1.b) not in
-   (select twice(i3.a), i3.b from i3 union select i4.a, thrice(i4.b) from i4);
-
-select t1.* from t1 where (t1.a, half(t1.b)) not in
-   (select twice(i3.a), i3.b from i3 union all select i4.a, i4.b from i4);
-
-select t1.a, half(t1.b) from t1 where (t1.a, t1.b) not in
-   (select 1, thrice(2) union select 3, 4);
-
-select t1.* from t1 where (half(t1.a), t1.b) not in
-   (select thrice(i3.a), i3.b from i3 union select i4.a, i4.b from i4);
-
-select t1.* from t1 where (t1.a, t1.b) not in
-   (select i3.a, half(i3.b) from i3 union all
-      select i4.a, thrice(i4.b) from i4);
-
--- Two SubPlan nodes
-select t1.* from t1 where (t1.a, t1.b) not in (select i3.a, i3.b from i3) or
-   (t1.a, t1.b) not in (select i4.a, i4.b from i4);
-
--- Two SubPlan nodes
-select t1.* from t1 where
-   (t1.a, twice(t1.b)) not in (select thrice(i3.a), i3.b from i3) or
-      (t1.a, half(t1.b)) not in (select i4.a, i4.b from i4);
-
--- Two SubPlan nodes
-select t1.* from t1 where (t1.a, t1.b) not in (select i3.a,i3.b from i3) or
-   (t1.a, half(t1.b)) not in (select thrice(i4.a), i4.b from i4);
-
--- SUITE: diff-rel-cols-not-in
-truncate table t1;
-create table t2(a int, b int) distributed by (a);
-
-insert into t1 select i, i-10 from generate_series(-1,3)i;
-insert into t2 select i, i-10 from generate_series(2,5)i;
-
-create table i1(a int, b int) distributed by (a);
-insert into i1 select i, i-10 from generate_series(3,6)i;
-
-create or replace function twice(int) returns int as $$
-       select 2 * $1;
-$$ language sql;
-
-create or replace function half(int) returns int as $$
-begin
-	return $1 / 2;
-end;
-$$ language plpgsql;
-
-create or replace function thrice(x int) returns int as $$
-    if x is not None:
-        return x * 3
-    return 0
-$$ language plpythonu;
-
-select t1.a, twice(t2.b) from t1, t2 where t1.a = half(t2.a) or
-   ((t1.a, t2.b) not in (select i1.a, thrice(i1.b) from i1));
-
-select t1.a, t2.b from t1 left join t2 on
-   (t1.a = t2.a and ((t1.a, half(t2.b)) not in (select i1.a, i1.b from i1)));
-
-select t1.a, t2.b from t1, t2 where t1.a = t2.a or
-   ((t1.a, t2.b) not in (select thrice(i1.a), i1.b from i1));
-
-select t1.a, t2.b from t1 left join t2 on
-   (thrice(t1.a) = thrice(t2.a) and
-      ((t1.a, t2.b) not in (select i1.a, i1.b from i1)));
-
-select t1.a, t2.b from t1, t2 where t1.a = t2.a or
-   ((t1.a, t2.b) not in (select i1.a, half(i1.b) from i1));
-
-select t1.a, t2.b from t1 left join t2 on
-   (t1.a = t2.a and
-      ((t1.a, twice(t2.b)) not in (select i1.a, thrice(i1.b) from i1)));
-
--- From MPP-2869
-create table bug_data (domain integer, class integer, attr text, value integer)
-   distributed by (domain);
-insert into bug_data values(1, 1, 'A', 1);
-insert into bug_data values(2, 1, 'A', 0);
-insert into bug_data values(3, 0, 'B', 1);
-
--- This one is contains one InitPlan without any SubPlan.
-create table foo as 
-SELECT attr, class, (select thrice(count(distinct class)::int) from bug_data)
-   as dclass FROM bug_data GROUP BY attr, class distributed by (attr);
-
--- Query from GPSQL-1260, produces InitPlan and a SubPlan.
-create or replace function nop(a int) returns int as $$ return a $$
-language plpythonu;
-create table toy as select generate_series(1, 10) i distributed by (i);
-select * from toy; -- only for debugging
-select array(select nop(i) from toy order by i);
-
--- start_ignore
-drop schema subplan_tests cascade;
--- end_ignore


Mime
View raw message