You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by hz...@apache.org on 2017/12/12 18:16:31 UTC
[4/6] incubator-trafodion git commit: add regression test for new sub
limit clauses
add regression test for new sub limit clauses
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/23f578dc
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/23f578dc
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/23f578dc
Branch: refs/heads/master
Commit: 23f578dc803e1a44820e117294947987a137aba2
Parents: 2299f3d
Author: EEDY <cq...@gmail.com>
Authored: Mon Dec 11 18:01:36 2017 +0800
Committer: EEDY <cq...@gmail.com>
Committed: Mon Dec 11 18:01:36 2017 +0800
----------------------------------------------------------------------
core/sql/regress/compGeneral/EXPECTED045 | 121 +++++++++++++++++++-
core/sql/regress/compGeneral/FILTER045 | 17 +++
core/sql/regress/compGeneral/TEST045 | 46 +++++++-
core/sql/regress/tools/runregr_compGeneral.ksh | 3 +
4 files changed, 184 insertions(+), 3 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/23f578dc/core/sql/regress/compGeneral/EXPECTED045
----------------------------------------------------------------------
diff --git a/core/sql/regress/compGeneral/EXPECTED045 b/core/sql/regress/compGeneral/EXPECTED045
index 87ead12..ab607a0 100644
--- a/core/sql/regress/compGeneral/EXPECTED045
+++ b/core/sql/regress/compGeneral/EXPECTED045
@@ -34,6 +34,23 @@
>>obey TEST045(ddl);
>>--------------------------------------------------------------------
>>
+>>create table sub_limit_01 ( a int, b char(30), c decimal(8,2) ) ;
+
+--- SQL operation complete.
+>>
+>>insert into sub_limit_01 values
++>(1, 'Limit_str_01', 11.23),(2, 'Limit_str_02', 12.23),(3, 'Limit_str_03', 13.23),
++>(4, 'Limit_str_04', 14.23),(5, 'Limit_str_05', 15.23),(6, 'Limit_str_06', 16.23),
++>(7, 'Limit_str_07', 17.23),(8, 'Limit_str_08', 18.23),(9, 'Limit_str_09', 19.23),
++>(10, 'Limit_str_10', 20.23),(11, 'Limit_str_11', 21.23),(12, 'Limit_str_12', 22.23),
++>(13, 'Limit_str_13', 23.23),(14, 'Limit_str_14', 24.23),(15, 'Limit_str_15', 25.23),
++>(16, 'Limit_str_16', 26.23),(17, 'Limit_str_17', 27.23),(18, 'Limit_str_18', 28.23),
++>(19, 'Limit_str_19', 29.23),(20, 'Limit_str_20', 30.23),(21, 'Limit_str_21', 31.23);
+
+--- 21 row(s) inserted.
+>>
+>>
+>>
>>create table store_sales
+>(
+> ss_sold_date_sk int,
@@ -490,8 +507,110 @@
--- SQL operation complete.
>>
+>>
+>>
+>>--------------------------------------------------------------------
+>>obey TEST045(common_subexpr);
+>>--------------------------------------------------------------------
+>>
+>>--------------------------------------------------------------------
+>>-- test sub-expressions with limit clause
+>>--------------------------------------------------------------------
+>>
+>>create table sub_limit_02 as select * from sub_limit_01 limit 1;
+
+--- 1 row(s) inserted.
+>>create table sub_limit_03 (a int , b char(30), c decimal(8,2)) as select * from sub_limit_01 limit 2;
+
+--- 2 row(s) inserted.
+>>
+>>insert into sub_limit_02 select * from sub_limit_01 order by a,c limit 2;
+
+--- 2 row(s) inserted.
+>>insert no check into sub_limit_02 select * from sub_limit_01 order by a,c limit 2;
+
+--- 2 row(s) inserted.
+>>insert with no rollback into table sub_limit_02 select * from sub_limit_01 order by a,c limit 2;
+
+--- 2 row(s) inserted.
+>>insert overwrite table sub_limit_02 select * from sub_limit_01 order by a,c limit 2;
+
+--- 2 row(s) inserted.
+>>insert with no rollback into sub_limit_02 (*) select * from sub_limit_01 order by a,c limit 2;
+
+--- 2 row(s) inserted.
+>>insert NOMVLOG into sub_limit_02 (a, b) select a, b from sub_limit_01 order by a,b limit 2;
+
+--- 2 row(s) inserted.
+>>
+>>load into sub_limit_03 select * from sub_limit_01 limit 16;
+Task: LOAD Status: Started Object: TRAFODION.T045_CSES.SUB_LIMIT_03
+Task: CLEANUP Status: Started Time:
+Task: CLEANUP Status: Ended Time:
+Task: CLEANUP Status: Ended Elapsed Time:
+Task: LOADING DATA Status: Started Time:
+ Rows Processed: 16
+ Error Rows: 0
+Task: LOADING DATA Status: Ended Time:
+Task: LOADING DATA Status: Ended Elapsed Time:
+Task: COMPLETION Status: Started Time:
+ Rows Loaded: 16
+Task: COMPLETION Status: Ended Time:
+Task: COMPLETION Status: Ended Elapsed Time:
+
+--- 16 row(s) loaded.
+>>
+>>select * from sub_limit_02 order by a,b,c ;
+
+A B C
+----------- ------------------------------ ----------
+
+ 1 Limit_str_01 11.23
+ 1 Limit_str_01 11.23
+ 1 Limit_str_01 11.23
+ 1 Limit_str_01 11.23
+ 1 Limit_str_01 11.23
+ 1 Limit_str_01 11.23
+ 1 Limit_str_01 ?
+ 2 Limit_str_02 12.23
+ 2 Limit_str_02 12.23
+ 2 Limit_str_02 12.23
+ 2 Limit_str_02 12.23
+ 2 Limit_str_02 12.23
+ 2 Limit_str_02 ?
+
+--- 13 row(s) selected.
+>>
+>>select * from sub_limit_03 order by a,b,c ;
+
+A B C
+----------- ------------------------------ ----------
+
+ 1 Limit_str_01 11.23
+ 1 Limit_str_01 11.23
+ 2 Limit_str_02 12.23
+ 2 Limit_str_02 12.23
+ 3 Limit_str_03 13.23
+ 4 Limit_str_04 14.23
+ 5 Limit_str_05 15.23
+ 6 Limit_str_06 16.23
+ 7 Limit_str_07 17.23
+ 8 Limit_str_08 18.23
+ 9 Limit_str_09 19.23
+ 10 Limit_str_10 20.23
+ 11 Limit_str_11 21.23
+ 12 Limit_str_12 22.23
+ 13 Limit_str_13 23.23
+ 14 Limit_str_14 24.23
+ 15 Limit_str_15 25.23
+ 16 Limit_str_16 26.23
+
+--- 18 row(s) selected.
+>>
+>>
+>>
>>--------------------------------------------------------------------
->>obey TEST045(queries);
+>>obey TEST045(with_queries);
>>--------------------------------------------------------------------
>>
>>obey TEST045(enable_cses);
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/23f578dc/core/sql/regress/compGeneral/FILTER045
----------------------------------------------------------------------
diff --git a/core/sql/regress/compGeneral/FILTER045 b/core/sql/regress/compGeneral/FILTER045
new file mode 100755
index 0000000..b75741f
--- /dev/null
+++ b/core/sql/regress/compGeneral/FILTER045
@@ -0,0 +1,17 @@
+#! /bin/sh
+
+# Specialized filter for project to filter out
+# 1. Syskey values
+# 2. "Funny names" for index columns appearing multiple times
+# (done twice, since it may appear multiple times)
+# 3. refreshed elapsed time
+
+fil=$1
+if [ "$fil" = "" ]; then
+ echo "Usage: $0 filename"
+ exit 1
+fi
+
+sed "
+s/\(Time:\) .*$/\1/g
+" $fil
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/23f578dc/core/sql/regress/compGeneral/TEST045
----------------------------------------------------------------------
diff --git a/core/sql/regress/compGeneral/TEST045 b/core/sql/regress/compGeneral/TEST045
index 533f747..605051c 100644
--- a/core/sql/regress/compGeneral/TEST045
+++ b/core/sql/regress/compGeneral/TEST045
@@ -28,7 +28,8 @@ obey TEST045(clnup);
log LOG045 clear;
obey TEST045(setup);
obey TEST045(ddl);
-obey TEST045(queries);
+obey TEST045(common_subexpr);
+obey TEST045(with_queries);
obey TEST045(clnup);
log;
exit;
@@ -76,6 +77,19 @@ cqd cse_debug_warnings 'off';
?section ddl
--------------------------------------------------------------------
+create table sub_limit_01 ( a int, b char(30), c decimal(8,2) ) ;
+
+insert into sub_limit_01 values
+(1, 'Limit_str_01', 11.23),(2, 'Limit_str_02', 12.23),(3, 'Limit_str_03', 13.23),
+(4, 'Limit_str_04', 14.23),(5, 'Limit_str_05', 15.23),(6, 'Limit_str_06', 16.23),
+(7, 'Limit_str_07', 17.23),(8, 'Limit_str_08', 18.23),(9, 'Limit_str_09', 19.23),
+(10, 'Limit_str_10', 20.23),(11, 'Limit_str_11', 21.23),(12, 'Limit_str_12', 22.23),
+(13, 'Limit_str_13', 23.23),(14, 'Limit_str_14', 24.23),(15, 'Limit_str_15', 25.23),
+(16, 'Limit_str_16', 26.23),(17, 'Limit_str_17', 27.23),(18, 'Limit_str_18', 28.23),
+(19, 'Limit_str_19', 29.23),(20, 'Limit_str_20', 30.23),(21, 'Limit_str_21', 31.23);
+
+
+
create table store_sales
(
ss_sold_date_sk int,
@@ -498,8 +512,36 @@ update statistics for table date_dim on every column;
update statistics for table date_dim on (d_qoy, d_year);
update statistics for table store_sales on every column;
+
+
+--------------------------------------------------------------------
+?section common_subexpr
+--------------------------------------------------------------------
+
+--------------------------------------------------------------------
+-- test sub-expressions with limit clause
+--------------------------------------------------------------------
+
+create table sub_limit_02 as select * from sub_limit_01 limit 1;
+create table sub_limit_03 (a int , b char(30), c decimal(8,2)) as select * from sub_limit_01 limit 2;
+
+insert into sub_limit_02 select * from sub_limit_01 order by a,c limit 2;
+insert no check into sub_limit_02 select * from sub_limit_01 order by a,c limit 2;
+insert with no rollback into table sub_limit_02 select * from sub_limit_01 order by a,c limit 2;
+insert overwrite table sub_limit_02 select * from sub_limit_01 order by a,c limit 2;
+insert with no rollback into sub_limit_02 (*) select * from sub_limit_01 order by a,c limit 2;
+insert NOMVLOG into sub_limit_02 (a, b) select a, b from sub_limit_01 order by a,b limit 2;
+
+load into sub_limit_03 select * from sub_limit_01 limit 16;
+
+select * from sub_limit_02 order by a,b,c ;
+
+select * from sub_limit_03 order by a,b,c ;
+
+
+
--------------------------------------------------------------------
-?section queries
+?section with_queries
--------------------------------------------------------------------
obey TEST045(enable_cses);
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/23f578dc/core/sql/regress/tools/runregr_compGeneral.ksh
----------------------------------------------------------------------
diff --git a/core/sql/regress/tools/runregr_compGeneral.ksh b/core/sql/regress/tools/runregr_compGeneral.ksh
index c854766..d151f51 100755
--- a/core/sql/regress/tools/runregr_compGeneral.ksh
+++ b/core/sql/regress/tools/runregr_compGeneral.ksh
@@ -379,6 +379,9 @@ cp $scriptsdir/tools/runmxci.ksh $REGRRUNDIR 2>$NULL
echo "copying FILTER042 to $REGRRUNDIR"
cp $REGRTSTDIR/FILTER042 $REGRRUNDIR 2>$NULL
+echo "copying FILTER045 to $REGRRUNDIR"
+cp $REGRTSTDIR/FILTER045 $REGRRUNDIR 2>$NULL
+
if [ $diffOnly -eq 0 ]; then
if [ "$REGRTSTDIR" != "$REGRRUNDIR" ]; then
echo "copying FILTER_TIME.AWK to $REGRRUNDIR"