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 <[email protected]> Authored: Mon Dec 11 18:01:36 2017 +0800 Committer: EEDY <[email protected]> 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"
