[TRAFODION-2140] Move IUS feature to open source
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/021ebd6c Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/021ebd6c Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/021ebd6c Branch: refs/heads/master Commit: 021ebd6cfec18db6f616fbfc08ee2a4bf3bb4b33 Parents: 37901fb Author: Barry Fritchman <[email protected]> Authored: Tue Aug 9 17:19:07 2016 +0000 Committer: Barry Fritchman <[email protected]> Committed: Tue Aug 9 17:19:07 2016 +0000 ---------------------------------------------------------------------- core/sql/bin/SqlciErrors.txt | 10 +- core/sql/common/ComSmallDefs.h | 4 + core/sql/common/NAString.cpp | 7 + core/sql/optimizer/RelSample.cpp | 1 - core/sql/regress/compGeneral/EXPECTED023 | 559 ++++++ core/sql/regress/compGeneral/FILTER023 | 36 + core/sql/regress/compGeneral/TEST023 | 273 +++ core/sql/regress/core/EXPECTED116 | 3 + core/sql/regress/executor/EXPECTED130 | 1 + core/sql/regress/hive/EXPECTED009 | 1 + core/sql/regress/privs1/EXPECTED132 | 5 + core/sql/regress/privs1/EXPECTED133 | Bin 26390 -> 27095 bytes core/sql/regress/privs1/EXPECTED141 | Bin 113222 -> 123489 bytes core/sql/regress/privs1/TEST132 | 2 +- core/sql/regress/privs2/EXPECTED138 | 24 +- core/sql/regress/privs2/EXPECTED140 | 25 +- core/sql/regress/seabase/EXPECTED002 | 50 +- core/sql/regress/seabase/EXPECTED012 | 1 + core/sql/regress/seabase/EXPECTED026 | 24 +- core/sql/regress/tools/runregr_compGeneral.ksh | 2 +- core/sql/sqlcomp/CmpSeabaseDDLcleanup.cpp | 5 +- core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp | 69 +- core/sql/sqlcomp/CmpSeabaseDDLmd.h | 25 + core/sql/sqlcomp/CmpSeabaseDDLtable.cpp | 8 +- core/sql/ustat/hs_auto.h | 4 +- core/sql/ustat/hs_cli.cpp | 831 +++++---- core/sql/ustat/hs_cli.h | 81 +- core/sql/ustat/hs_const.h | 11 +- core/sql/ustat/hs_globals.cpp | 1822 +++++++++++++------ core/sql/ustat/hs_globals.h | 110 +- core/sql/ustat/hs_la.cpp | 24 + core/sql/ustat/hs_la.h | 24 +- core/sql/ustat/hs_log.h | 21 +- core/sql/ustat/hs_parser.cpp | 4 + core/sql/ustat/hs_update.cpp | 3 - core/sql/ustat/hs_yacc.y | 65 +- 36 files changed, 2987 insertions(+), 1148 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/bin/SqlciErrors.txt ---------------------------------------------------------------------- diff --git a/core/sql/bin/SqlciErrors.txt b/core/sql/bin/SqlciErrors.txt index f21cf09..d39769b 100644 --- a/core/sql/bin/SqlciErrors.txt +++ b/core/sql/bin/SqlciErrors.txt @@ -1870,7 +1870,8 @@ drop the default context 9225 ZZZZZ 99999 BEGINNER MINOR LOGONLY Incremental UPDATE STATISTICS: the total unique count for column $0~String0 changed more than the percentage specified by CQD USTAT_IUS_TOTAL_UEC_CHANGE_THRESHOLD. A regular UPDATE STATISTICS is performed instead. 9226 ZZZZZ 99999 BEGINNER MINOR LOGONLY Incremental UPDATE STATISTICS: one interval for column $0~String0 is reset to zero. A regular UPDATE STATISTICS is performed instead. 9227 ZZZZZ 99999 BEGINNER MINOR LOGONLY Incremental UPDATE STATISTICS: an interval for column $0~String0 is reduced to zero rows using IUS. A regular UPDATE STATISTICS is performed instead. -9230 ZZZZZ 99999 BEGINNER MAJOR DBADMIN Incremental UPDATE STATISTICS: cannot allocate enough memory. A regular UPDATE STATISTICS is performed instead. +9228 ZZZZZ 99999 BEGINNER MINOR LOGONLY There were no sample tables to drop. +9230 ZZZZZ 99999 BEGINNER MAJOR DBADMIN Incremental UPDATE STATISTICS: $0~int0 columns could not be processed due to insufficient memory, and are being processed by regular UPDATE STATISTICS instead. 9231 ZZZZZ 99999 BEGINNER MAJOR DBADMIN Incremental UPDATE STATISTICS: only RANDOM <percentage> PERCENT is allowed as the sampling specification with PERSISTENT. 9232 ZZZZZ 99999 BEGINNER MAJOR DBADMIN Incremental UPDATE STATISTICS: cannot proceed because of the on-going IUS transaction originated at $0~String0. 9233 ZZZZZ 99999 BEGINNER MAJOR DBADMIN Incremental UPDATE STATISTICS: could not store search condition used for IUS because it exceeds $0~int0 characters. @@ -1885,10 +1886,13 @@ drop the default context 9242 ZZZZZ 99999 BEGINNER MAJOR DBADMIN Unable to create schema for Hive statistics. 9243 ZZZZZ 99999 BEGINNER MAJOR DBADMIN This UPDATE STATISTICS command may take too long. It is recommended to use the SAMPLE clause instead. If you wish to do this without a SAMPLE clause, specify NO SAMPLE explicitly. 9244 ZZZZZ 99999 BEGINNER MAJOR DBADMIN UPDATE STATISTICS is not allowed in a user transaction. -9245 ZZZZZ 99999 UUUUUUUU UUUUU UUUUUUU ---- unused ---- +9245 ZZZZZ 99999 BEGINNER INFRM DBADMIN Unexpected out-of-order data encountered during histogram construction on column $0~string0; this might result in later 6004 warnings. 9246 ZZZZZ 99999 BEGINNER MAJOR DBADMIN UPDATE STATISTICS is not supported on LOB columns. Column $0~String0 is a LOB column. 9247 ZZZZZ 99999 BEGINNER MAJOR DBADMIN UPDATE STATISTICS is not supported on volatile tables presently. -9250 ZZZZZ 99999 UUUUUUUU UUUUU UUUUUUU Last UPDATE STATISTICS error. +9248 ZZZZZ 99999 BEGINNER MAJOR DBADMIN UPDATE STATISTICS failed due to a memory allocation failure. +9249 ZZZZZ 99999 BEGINNER MAJOR DBADMIN Incremental UPDATE STATISTICS is disabled. +9250 ZZZZZ 99999 BEGINNER MINOR LOGONLY Incremental UPDATE STATISTICS: non-NULL values added to previously all-NULL histogram for column $0~string0. A regular UPDATE STATISTICS is performed instead. +9259 ZZZZZ 99999 UUUUUUUU UUUUU UUUUUUU Last UPDATE STATISTICS error. 10000 ZZZZZ 99999 UUUUUUUU UUUUU UUUUUUU Sort Error: First Sort error 10001 ZZZZZ 99999 ADVANCED MAJOR DIALOUT Sort Error : No error text defined. Unexpected error. $0~String0 10002 ZZZZZ 99999 UUUUUUUU UUUUU UUUUUUU ---- unused ---- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/common/ComSmallDefs.h ---------------------------------------------------------------------- diff --git a/core/sql/common/ComSmallDefs.h b/core/sql/common/ComSmallDefs.h index 5a25b62..68de7b7 100644 --- a/core/sql/common/ComSmallDefs.h +++ b/core/sql/common/ComSmallDefs.h @@ -104,18 +104,22 @@ typedef NABoolean ComBoolean; #define HIVE_SYSTEM_SCHEMA "HIVE" #define HIVE_STATS_CATALOG "TRAFODION" #define HIVE_STATS_SCHEMA "\"_HIVESTATS_\"" +#define HIVE_STATS_SCHEMA_NO_QUOTES "_HIVESTATS_" #define HIVE_EXT_SCHEMA_PREFIX "_HV_" #define HBASE_SYSTEM_CATALOG "HBASE" #define HBASE_SYSTEM_SCHEMA "HBASE" #define HBASE_HIST_NAME "SB_HISTOGRAMS" #define HBASE_HISTINT_NAME "SB_HISTOGRAM_INTERVALS" +#define HBASE_PERS_SAMP_NAME "SB_PERSISTENT_SAMPLES" #define HBASE_HIST_PK "SB_HISTOGRAMS_PK" #define HBASE_HISTINT_PK "SB_HISTOGRAM_INTERVALS_PK" +#define HBASE_PERS_SAMP_PK "SB_PERSISTENT_SAMPLES_PK" #define HBASE_EXT_SCHEMA_PREFIX "_HB_" #define HBASE_STATS_CATALOG "TRAFODION" #define HBASE_STATS_SCHEMA "\"_HBASESTATS_\"" +#define HBASE_STATS_SCHEMA_NO_QUOTES "_HBASESTATS_" // default null format for data in hive files. #define HIVE_DEFAULT_NULL_STRING "\\N" http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/common/NAString.cpp ---------------------------------------------------------------------- diff --git a/core/sql/common/NAString.cpp b/core/sql/common/NAString.cpp index 3b9830e..0816baa 100644 --- a/core/sql/common/NAString.cpp +++ b/core/sql/common/NAString.cpp @@ -280,6 +280,13 @@ NAString Int64ToNAString(Int64 l) return NAString(resultstr); } +NAString RealToNAString(double d) +{ + char resultstr[200]; + sprintf(resultstr,"%G",d); + return NAString(resultstr); +} + NAString &replaceAll(NAString &source, const NAString &searchFor, const NAString &replaceWith) { http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/optimizer/RelSample.cpp ---------------------------------------------------------------------- diff --git a/core/sql/optimizer/RelSample.cpp b/core/sql/optimizer/RelSample.cpp index 56d88b8..92f318a 100644 --- a/core/sql/optimizer/RelSample.cpp +++ b/core/sql/optimizer/RelSample.cpp @@ -925,7 +925,6 @@ RelExpr *RelSample::bindNode(BindWA *bindWA) RelExpr* myChild = child(0); if (myChild->getOperatorType() == REL_SCAN && (static_cast<Scan*>(myChild))->isHbaseTable() && - myChild->selectionPred().entries() == 0 && isSimpleRandomRelative() && trafSampleRate <= 1.0f) { http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/regress/compGeneral/EXPECTED023 ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/EXPECTED023 b/core/sql/regress/compGeneral/EXPECTED023 new file mode 100644 index 0000000..5fea598 --- /dev/null +++ b/core/sql/regress/compGeneral/EXPECTED023 @@ -0,0 +1,559 @@ +>>-- create the database used for the tests +>> +>>create schema compgeneral_test023; + +--- SQL operation complete. +>> +>>set schema compgeneral_test023; + +--- SQL operation complete. +>> +>>get tables; + +Tables in Schema TRAFODION.COMPGENERAL_TEST023 +============================================== + +SB_HISTOGRAMS +SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES + +--- SQL operation complete. +>> +>>CREATE TABLE stest ++>( ++>c1 integer not null, ++>c2 integer not null, ++>c3 integer not null, ++>PRIMARY KEY (C1 ASC) ++>) ++>SALT USING 4 PARTITIONS ++>ON (C1) ++>; + +--- SQL operation complete. +>> +>> +>>upsert using load into stest ++>select ++> x1+10*x2+100*x3+1000*x4+10000*x5, ++> x2, ++> x3 ++>-- the from clause below creates 100,000 rows, the cross product of ++>-- 5 copies of { 0, ... 9 } ++> from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) T(x1) ++>transpose 0,1,2,3,4,5,6,7,8,9 as x2 ++>transpose 0,1,2,3,4,5,6,7,8,9 as x3 ++>transpose 0,1,2,3,4,5,6,7,8,9 as x4 ++>transpose 0,1,2,3,4,5,6,7,8,9 as x5; + +--- 100000 row(s) inserted. +>> +>> +>>CREATE TABLE stestc ++>( ++>c1 char(12) not null, ++>c2 char(12) not null, ++>c3 char(12) not null, ++>PRIMARY KEY (C1 ASC) ++>) ++>SALT USING 4 PARTITIONS ++>ON (C1) ++>; + +--- SQL operation complete. +>> +>>upsert using load into stestc ++>select ++> x1 || x2 || x3 || x4 || x5, ++> x2 || x4 || x1, ++> x5 || x3 ++>-- the from clause below creates 100,000 rows, the cross product of ++>-- 5 copies of { 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' } ++> from (values('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j')) T(x1) ++>transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x2 ++>transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x3 ++>transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x4 ++>transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x5; + +--- 100000 row(s) inserted. +>> +>> +>>create table stest_empty like stest with partitions; + +--- SQL operation complete. +>> +>>get tables; + +Tables in Schema TRAFODION.COMPGENERAL_TEST023 +============================================== + +SB_HISTOGRAMS +SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES +STEST +STESTC +STEST_EMPTY + +--- SQL operation complete. +>> -- should be just stest, stest_empty, stestc; no sb_* tables yet +>> +>>?section ustat1p +>> +>>-- positive tests +>> +>>-- create a persistent sample +>>update statistics for table stest create sample random 20 percent; + +--- SQL operation complete. +>> +>>prepare s1 from ++>select object_name,sample_name,reason,last_where_predicate ++> from "_MD_".objects o join sb_persistent_samples s ++> on o.object_uid = s.table_uid ++> where object_name = ? and object_type = 'BT'; + +--- SQL command prepared. +>> +>>execute s1 using 'STEST'; + +OBJECT_NAME SAMPLE_NAME REASON LAST_WHERE_PREDICATE +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + +STEST TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_6_1456279355_95736 M + +--- 1 row(s) selected. +>>-- should see one row +>> +>>get tables; + +Tables in Schema TRAFODION.COMPGENERAL_TEST023 +============================================== + +SB_HISTOGRAMS +SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES +STEST +STESTC +STEST_EMPTY +TRAF_SAMPLE_6_1456279355_95736 + +--- SQL operation complete. +>> -- should be stest, stest_empty, stestc, sb_persistent_samples + a sample table +>> +>>-- create another one, showing its replacement +>>update statistics for table stest create sample random 10 percent; + +--- SQL operation complete. +>> +>>get tables; + +Tables in Schema TRAFODION.COMPGENERAL_TEST023 +============================================== + +SB_HISTOGRAMS +SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES +STEST +STESTC +STEST_EMPTY +TRAF_SAMPLE_5_1456279391_83165 + +--- SQL operation complete. +>> -- should be stest, stest_empty, stestc, sb_persistent_samples + a different sample table +>> +>>execute s1 using 'STEST'; + +OBJECT_NAME SAMPLE_NAME REASON LAST_WHERE_PREDICATE +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + +STEST TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_5_1456279391_83165 M + +--- 1 row(s) selected. +>>-- should see one row +>> +>>-- remove a persistent sample table +>>update statistics for table stest remove sample; + +--- SQL operation complete. +>> +>>get tables; + +Tables in Schema TRAFODION.COMPGENERAL_TEST023 +============================================== + +SB_HISTOGRAMS +SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES +STEST +STESTC +STEST_EMPTY + +--- SQL operation complete. +>> -- should be stest, stest_empty, stestc, sb_persistent_samples only +>> +>>execute s1 using 'STEST'; + +--- 0 row(s) selected. +>>-- should see zero rows +>> +>>-- create a persistent sample table using PERSISTENT +>> +>>update statistics for table stest on every column sample random 10 percent persistent; + +--- SQL operation complete. +>> +>>get tables; + +Tables in Schema TRAFODION.COMPGENERAL_TEST023 +============================================== + +SB_HISTOGRAMS +SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES +STEST +STESTC +STEST_EMPTY +TRAF_SAMPLE_72_1456279415_388472 + +--- SQL operation complete. +>> -- should be stest, stest_empty, stestc, sb_persistent_samples, sb_hist* + another sample table +>> +>>execute s1 using 'STEST'; + +OBJECT_NAME SAMPLE_NAME REASON LAST_WHERE_PREDICATE +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + +STEST TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_72_1456279415_388472 I + +--- 1 row(s) selected. +>>-- should see one row +>> +>>-- do it again showing its replacement +>>update statistics for table stest on every column sample random 10 percent persistent; + +--- SQL operation complete. +>> +>>get tables; + +Tables in Schema TRAFODION.COMPGENERAL_TEST023 +============================================== + +SB_HISTOGRAMS +SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES +STEST +STESTC +STEST_EMPTY +TRAF_SAMPLE_88_1456279440_120688 + +--- SQL operation complete. +>> -- should be stest, stest_empty, stestc, sb_persistent_samples, sb_hist* + another sample table +>> +>>execute s1 using 'STEST'; + +OBJECT_NAME SAMPLE_NAME REASON LAST_WHERE_PREDICATE +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + +STEST TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_88_1456279440_120688 I + +--- 1 row(s) selected. +>>-- should see one row +>> +>>-- change the data in the table and do an INCREMENTAL update stats +>>insert into stest values (100000,1,1), ++> (100001,1,1), ++> (100002,1,1), ++> (100003,1,1), ++> (100004,1,1), ++> (100005,1,1), ++> (100006,1,1), ++> (100007,1,1), ++> (100008,1,1), ++> (100009,1,1), ++> (100010,2,1), ++> (100011,2,1), ++> (100012,2,1), ++> (100013,2,1), ++> (100014,2,1), ++> (100015,2,1), ++> (100016,2,1), ++> (100017,2,1), ++> (100018,2,1), ++> (100019,2,1), ++> (100020,3,1), ++> (100021,3,1), ++> (100022,3,1), ++> (100023,3,1), ++> (100024,3,1), ++> (100025,3,1), ++> (100026,3,1), ++> (100027,3,1), ++> (100028,3,1), ++> (100029,3,1); + +--- 30 row(s) inserted. +>> +>>-- These two CQDs are to get around annoying 9222 and 9224 warnings +>>-- that happen non-deterministically due to "gaps" in the sample histograms. +>>cqd USTAT_IUS_INTERVAL_ROWCOUNT_CHANGE_THRESHOLD '0.15'; + +--- SQL operation complete. +>>cqd USTAT_IUS_INTERVAL_UEC_CHANGE_THRESHOLD '0.15'; + +--- SQL operation complete. +>> +>>update statistics for table stest on existing columns incremental where c1 >= 100000; + +--- SQL operation complete. +>> +>>execute s1 using 'STEST'; + +OBJECT_NAME SAMPLE_NAME REASON LAST_WHERE_PREDICATE +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + +STEST TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_88_1456279440_120688 I c1 >= 100000 + +--- 1 row(s) selected. +>> +>>-- similar tests, but using character data types +>>update statistics for table stestc on every column sample random 10 percent persistent; + +--- SQL operation complete. +>> +>>insert into stestc values ('naaaa','abc','ba'), ++> ('naaab','abc','ba'), ++> ('naaac','abc','ba'), ++> ('naaad','abc','ba'), ++> ('naaae','abc','ba'), ++> ('naaaf','abc','ba'), ++> ('naaag','abc','ba'), ++> ('naaah','abc','ba'), ++> ('naaai','abc','ba'), ++> ('naaaj','abc','ba'), ++> ('naaba','bhe','ba'), ++> ('naabb','bhe','ba'), ++> ('naabc','bhe','ba'), ++> ('naabd','bhe','ba'), ++> ('naabe','bhe','ba'), ++> ('naabf','bhe','ba'), ++> ('naabg','bhe','ba'), ++> ('naabh','bhe','ba'), ++> ('naabi','bhe','ba'), ++> ('naabj','bhe','ba'), ++> ('naaca','fib','ba'), ++> ('naacb','fib','ba'), ++> ('naacc','fib','ba'), ++> ('naacd','fib','ba'), ++> ('naace','fib','ba'), ++> ('naacf','fib','ba'), ++> ('naacg','fib','ba'), ++> ('naach','fib','ba'), ++> ('naaci','fib','ba'), ++> ('naacj','fib','ba'); + +--- 30 row(s) inserted. +>> +>>update statistics for table stestc on existing columns incremental where c1 >= 'naaaa'; + +--- SQL operation complete. +>> +>>get tables; + +Tables in Schema TRAFODION.COMPGENERAL_TEST023 +============================================== + +SB_HISTOGRAMS +SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES +STEST +STESTC +STEST_EMPTY +TRAF_SAMPLE_85_1456279493_417385 +TRAF_SAMPLE_88_1456279440_120688 + +--- SQL operation complete. +>> +>>execute s1 using 'STESTC'; + +OBJECT_NAME SAMPLE_NAME REASON LAST_WHERE_PREDICATE +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + +STESTC TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_85_1456279493_417385 I c1 >= 'naaaa' + +--- 1 row(s) selected. +>> +>>?section ustat1n +>>-- negative tests +>> +>>-- attempt to create a sample on an empty table +>>update statistics for table stest_empty create sample random 10 percent; + +*** ERROR[9207] The specified SAMPLE option generated an empty sample set. Modify the SAMPLE option and resubmit. + +--- SQL operation failed with errors. +>> +>>get tables; + +Tables in Schema TRAFODION.COMPGENERAL_TEST023 +============================================== + +SB_HISTOGRAMS +SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES +STEST +STESTC +STEST_EMPTY +TRAF_SAMPLE_85_1456279493_417385 +TRAF_SAMPLE_88_1456279440_120688 + +--- SQL operation complete. +>> -- should be the same as previous "get tables" +>> +>>execute s1 using 'STEST_EMPTY'; + +--- 0 row(s) selected. +>>-- should be zero rows +>> +>>update statistics for table stest_empty on every column sample random 10 percent persistent; + +--- SQL operation complete. +>> +>>get tables; + +Tables in Schema TRAFODION.COMPGENERAL_TEST023 +============================================== + +SB_HISTOGRAMS +SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES +STEST +STESTC +STEST_EMPTY +TRAF_SAMPLE_85_1456279493_417385 +TRAF_SAMPLE_88_1456279440_120688 + +--- SQL operation complete. +>> -- should be the same as previous "get tables" +>> +>>execute s1 using 'STEST_EMPTY'; + +--- 0 row(s) selected. +>>-- should be zero rows +>> +>>-- attempt to remove a non-existant persistent sample +>>update statistics for table stest_empty remove sample; + +*** WARNING[9228] There were no sample tables to drop. + +--- SQL operation completed with warnings. +>> +>>get tables; + +Tables in Schema TRAFODION.COMPGENERAL_TEST023 +============================================== + +SB_HISTOGRAMS +SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES +STEST +STESTC +STEST_EMPTY +TRAF_SAMPLE_85_1456279493_417385 +TRAF_SAMPLE_88_1456279440_120688 + +--- SQL operation complete. +>> -- should be the same as previous "get tables" +>> +>>-- attempt to do incremental with an invalid where clause +>>update statistics for table stest on existing columns incremental where 1; + +*** ERROR[9200] UPDATE STATISTICS for table TRAFODION.COMPGENERAL_TEST023.STEST encountered an error (15001) from statement HSCursor::prepareRowsetInternal(). + +*** ERROR[15001] A syntax error occurred at or before: +SELECT "C1", "C2", "C3", "_SALT_" FROM TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPL +E_88_1456279440_120688 WHERE 1 FOR READ UNCOMMITTED ACCESS; + ^ (120 characters from start of SQL statement) + +*** ERROR[8822] The statement was not prepared. + +*** ERROR[9200] UPDATE STATISTICS for table TRAFODION.COMPGENERAL_TEST023.STEST encountered an error (15001) from statement POPULATE_FROM_QUERY. + +*** ERROR[15001] A syntax error occurred at or before: +SELECT "C1", "C2", "C3", "_SALT_" FROM TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPL +E_88_1456279440_120688 WHERE 1 FOR READ UNCOMMITTED ACCESS; + ^ (120 characters from start of SQL statement) + +*** ERROR[8822] The statement was not prepared. + +--- SQL operation failed with errors. +>> +>>get tables; + +Tables in Schema TRAFODION.COMPGENERAL_TEST023 +============================================== + +SB_HISTOGRAMS +SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES +STEST +STESTC +STEST_EMPTY +TRAF_SAMPLE_85_1456279493_417385 +TRAF_SAMPLE_88_1456279440_120688 + +--- SQL operation complete. +>> -- should be the same as previous "get tables" +>> +>>-- attempt to do incremental when no persistent sample exists +>>update statistics for table stest_empty on existing columns incremental where c1 >= 100000; + +--- SQL operation complete. +>> +>>get tables; + +Tables in Schema TRAFODION.COMPGENERAL_TEST023 +============================================== + +SB_HISTOGRAMS +SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES +STEST +STESTC +STEST_EMPTY +TRAF_SAMPLE_85_1456279493_417385 +TRAF_SAMPLE_88_1456279440_120688 + +--- SQL operation complete. +>> -- should be the same as previous "get tables" +>> +>> +>>?section clnup +>> +>>drop table stest; + +--- SQL operation complete. +>>drop table stestc; + +--- SQL operation complete. +>>drop table stest_empty; + +--- SQL operation complete. +>> +>>get tables; + +Tables in Schema TRAFODION.COMPGENERAL_TEST023 +============================================== + +SB_HISTOGRAMS +SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES + +--- SQL operation complete. +>> -- sample tables should be gone too +>> +>>drop schema compgeneral_test023 cascade; + +--- SQL operation complete. +>> -- cascade needed because of sb_* tables +>> +>>log; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/regress/compGeneral/FILTER023 ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/FILTER023 b/core/sql/regress/compGeneral/FILTER023 new file mode 100755 index 0000000..dc7f367 --- /dev/null +++ b/core/sql/regress/compGeneral/FILTER023 @@ -0,0 +1,36 @@ +#! /bin/sh +# @@@ START COPYRIGHT @@@ +# +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. +# +# @@@ END COPYRIGHT @@@ + +# The names of sample tables are generated from non-deterministic +# criteria; we filter that out. + +fil=$1 +if [ "$fil" = "" ]; then + echo "Usage: $0 filename" + exit 1 +fi + +sed " +s/TRAF_SAMPLE_[0-9]*_[0-9]*_[0-9]*/TRAF_SAMPLE_nn_nnnnnnnnnn_nnnnnn/g +s/E_[0-9]*_[0-9]*_[0-9]* WHERE/E_nn_nnnnnnnnnn_nnnnnn WHERE/g +" $fil + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/regress/compGeneral/TEST023 ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/TEST023 b/core/sql/regress/compGeneral/TEST023 new file mode 100755 index 0000000..01ab49b --- /dev/null +++ b/core/sql/regress/compGeneral/TEST023 @@ -0,0 +1,273 @@ +-- Test: TEST023 (CompGeneral) +-- Functionality: Tests of the incremental UPDATE STATISTICS feature. +-- +-- @@@ START COPYRIGHT @@@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, +-- software distributed under the License is distributed on an +-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +-- KIND, either express or implied. See the License for the +-- specific language governing permissions and limitations +-- under the License. +-- +-- @@@ END COPYRIGHT @@@ + +?section clean_up + +set schema compgeneral_test023; + +drop schema compgeneral_test023 cascade; + +?section create_db + +log LOG023 clear; +-- create the database used for the tests + +create schema compgeneral_test023; + +set schema compgeneral_test023; + +get tables; + +CREATE TABLE stest +( +c1 integer not null, +c2 integer not null, +c3 integer not null, +PRIMARY KEY (C1 ASC) +) +SALT USING 4 PARTITIONS +ON (C1) +; + + +upsert using load into stest +select + x1+10*x2+100*x3+1000*x4+10000*x5, + x2, + x3 +-- the from clause below creates 100,000 rows, the cross product of +-- 5 copies of { 0, ... 9 } + from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) T(x1) +transpose 0,1,2,3,4,5,6,7,8,9 as x2 +transpose 0,1,2,3,4,5,6,7,8,9 as x3 +transpose 0,1,2,3,4,5,6,7,8,9 as x4 +transpose 0,1,2,3,4,5,6,7,8,9 as x5; +; + +CREATE TABLE stestc +( +c1 char(12) not null, +c2 char(12) not null, +c3 char(12) not null, +PRIMARY KEY (C1 ASC) +) +SALT USING 4 PARTITIONS +ON (C1) +; + +upsert using load into stestc +select + x1 || x2 || x3 || x4 || x5, + x2 || x4 || x1, + x5 || x3 +-- the from clause below creates 100,000 rows, the cross product of +-- 5 copies of { 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' } + from (values('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j')) T(x1) +transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x2 +transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x3 +transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x4 +transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x5; +; + +create table stest_empty like stest with partitions; + +get tables; -- should be just stest, stest_empty, stestc; no sb_* tables yet + +?section ustat1p + +-- positive tests + +-- create a persistent sample +update statistics for table stest create sample random 20 percent; + +prepare s1 from +select object_name,sample_name,reason,last_where_predicate + from "_MD_".objects o join sb_persistent_samples s + on o.object_uid = s.table_uid + where object_name = ? and object_type = 'BT'; + +execute s1 using 'STEST'; +-- should see one row + +get tables; -- should be stest, stest_empty, stestc, sb_persistent_samples + a sample table + +-- create another one, showing its replacement +update statistics for table stest create sample random 10 percent; + +get tables; -- should be stest, stest_empty, stestc, sb_persistent_samples + a different sample table + +execute s1 using 'STEST'; +-- should see one row + +-- remove a persistent sample table +update statistics for table stest remove sample; + +get tables; -- should be stest, stest_empty, stestc, sb_persistent_samples only + +execute s1 using 'STEST'; +-- should see zero rows + +-- create a persistent sample table using PERSISTENT + +update statistics for table stest on every column sample random 10 percent persistent; + +get tables; -- should be stest, stest_empty, stestc, sb_persistent_samples, sb_hist* + another sample table + +execute s1 using 'STEST'; +-- should see one row + +-- do it again showing its replacement +update statistics for table stest on every column sample random 10 percent persistent; + +get tables; -- should be stest, stest_empty, stestc, sb_persistent_samples, sb_hist* + another sample table + +execute s1 using 'STEST'; +-- should see one row + +-- change the data in the table and do an INCREMENTAL update stats +insert into stest values (100000,1,1), + (100001,1,1), + (100002,1,1), + (100003,1,1), + (100004,1,1), + (100005,1,1), + (100006,1,1), + (100007,1,1), + (100008,1,1), + (100009,1,1), + (100010,2,1), + (100011,2,1), + (100012,2,1), + (100013,2,1), + (100014,2,1), + (100015,2,1), + (100016,2,1), + (100017,2,1), + (100018,2,1), + (100019,2,1), + (100020,3,1), + (100021,3,1), + (100022,3,1), + (100023,3,1), + (100024,3,1), + (100025,3,1), + (100026,3,1), + (100027,3,1), + (100028,3,1), + (100029,3,1); + +-- These two CQDs are to get around annoying 9222 and 9224 warnings +-- that happen non-deterministically due to "gaps" in the sample histograms. +cqd USTAT_IUS_INTERVAL_ROWCOUNT_CHANGE_THRESHOLD '0.15'; +cqd USTAT_IUS_INTERVAL_UEC_CHANGE_THRESHOLD '0.15'; + +update statistics for table stest on existing columns incremental where c1 >= 100000; + +execute s1 using 'STEST'; + +-- similar tests, but using character data types +update statistics for table stestc on every column sample random 10 percent persistent; + +insert into stestc values ('naaaa','abc','ba'), + ('naaab','abc','ba'), + ('naaac','abc','ba'), + ('naaad','abc','ba'), + ('naaae','abc','ba'), + ('naaaf','abc','ba'), + ('naaag','abc','ba'), + ('naaah','abc','ba'), + ('naaai','abc','ba'), + ('naaaj','abc','ba'), + ('naaba','bhe','ba'), + ('naabb','bhe','ba'), + ('naabc','bhe','ba'), + ('naabd','bhe','ba'), + ('naabe','bhe','ba'), + ('naabf','bhe','ba'), + ('naabg','bhe','ba'), + ('naabh','bhe','ba'), + ('naabi','bhe','ba'), + ('naabj','bhe','ba'), + ('naaca','fib','ba'), + ('naacb','fib','ba'), + ('naacc','fib','ba'), + ('naacd','fib','ba'), + ('naace','fib','ba'), + ('naacf','fib','ba'), + ('naacg','fib','ba'), + ('naach','fib','ba'), + ('naaci','fib','ba'), + ('naacj','fib','ba'); + +update statistics for table stestc on existing columns incremental where c1 >= 'naaaa'; + +get tables; + +execute s1 using 'STESTC'; + +?section ustat1n +-- negative tests + +-- attempt to create a sample on an empty table +update statistics for table stest_empty create sample random 10 percent; + +get tables; -- should be the same as previous "get tables" + +execute s1 using 'STEST_EMPTY'; +-- should be zero rows + +update statistics for table stest_empty on every column sample random 10 percent persistent; + +get tables; -- should be the same as previous "get tables" + +execute s1 using 'STEST_EMPTY'; +-- should be zero rows + +-- attempt to remove a non-existant persistent sample +update statistics for table stest_empty remove sample; + +get tables; -- should be the same as previous "get tables" + +-- attempt to do incremental with an invalid where clause +update statistics for table stest on existing columns incremental where 1; + +get tables; -- should be the same as previous "get tables" + +-- attempt to do incremental when no persistent sample exists +update statistics for table stest_empty on existing columns incremental where c1 >= 100000; + +get tables; -- should be the same as previous "get tables" + + +?section clnup + +drop table stest; +drop table stestc; +drop table stest_empty; + +get tables; -- sample tables should be gone too + +drop schema compgeneral_test023 cascade; -- cascade needed because of sb_* tables + +log; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/regress/core/EXPECTED116 ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/EXPECTED116 b/core/sql/regress/core/EXPECTED116 index 3932ba7..0441135 100644 --- a/core/sql/regress/core/EXPECTED116 +++ b/core/sql/regress/core/EXPECTED116 @@ -645,6 +645,7 @@ Tables in Schema TRAFODION.T116SCH1 SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES --- SQL operation complete. >>create table t116t2 (a int); @@ -657,6 +658,7 @@ Tables in Schema TRAFODION.T116SCH1 SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES T116T2 --- SQL operation complete. @@ -685,6 +687,7 @@ Tables in Schema TRAFODION.T116SCH1 SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES T116T1 --- SQL operation complete. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/regress/executor/EXPECTED130 ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/EXPECTED130 b/core/sql/regress/executor/EXPECTED130 index af65951..bb19a2b 100644 --- a/core/sql/regress/executor/EXPECTED130 +++ b/core/sql/regress/executor/EXPECTED130 @@ -1360,6 +1360,7 @@ LOBDescHandle__05102114855562636318_0001 LOBMD__05102114855562636318 SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES TLOB130TS2 --- SQL operation complete. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/regress/hive/EXPECTED009 ---------------------------------------------------------------------- diff --git a/core/sql/regress/hive/EXPECTED009 b/core/sql/regress/hive/EXPECTED009 index 7d27a24..372d5de 100644 --- a/core/sql/regress/hive/EXPECTED009 +++ b/core/sql/regress/hive/EXPECTED009 @@ -264,6 +264,7 @@ Tables in Schema TRAFODION.HIVE_T009 SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES T009T1 --- SQL operation complete. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/regress/privs1/EXPECTED132 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/EXPECTED132 b/core/sql/regress/privs1/EXPECTED132 index 0e9828c..d84aee8 100644 --- a/core/sql/regress/privs1/EXPECTED132 +++ b/core/sql/regress/privs1/EXPECTED132 @@ -222,6 +222,7 @@ Tables in Schema TRAFODION.T132SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES --- SQL operation complete. >> @@ -263,6 +264,7 @@ Tables in Schema TRAFODION.T132SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES T132T1 T132T2 T132T3 @@ -623,6 +625,7 @@ Tables in Schema TRAFODION.T132SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES --- SQL operation complete. >> @@ -1868,6 +1871,7 @@ Tables in Schema TRAFODION.T132SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES --- SQL operation complete. >> @@ -1891,6 +1895,7 @@ Tables in Schema TRAFODION.T132SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES T132T1 T132T2 http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/regress/privs1/EXPECTED133 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/EXPECTED133 b/core/sql/regress/privs1/EXPECTED133 index d9fb353..a6d6307 100644 Binary files a/core/sql/regress/privs1/EXPECTED133 and b/core/sql/regress/privs1/EXPECTED133 differ http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/regress/privs1/EXPECTED141 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/EXPECTED141 b/core/sql/regress/privs1/EXPECTED141 index 49eca21..308aae9 100644 Binary files a/core/sql/regress/privs1/EXPECTED141 and b/core/sql/regress/privs1/EXPECTED141 differ http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/regress/privs1/TEST132 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/TEST132 b/core/sql/regress/privs1/TEST132 index 41f56f9..30e846f 100755 --- a/core/sql/regress/privs1/TEST132 +++ b/core/sql/regress/privs1/TEST132 @@ -51,7 +51,7 @@ exit; ?section clean_up set schema t132sch; drop sequence t132_team_number_sequence; -drop schema t132sch cascade; +cleanup schema t132sch; ?section set_up create shared schema t132sch; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/regress/privs2/EXPECTED138 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/EXPECTED138 b/core/sql/regress/privs2/EXPECTED138 index 92caae5..ece9b94 100644 --- a/core/sql/regress/privs2/EXPECTED138 +++ b/core/sql/regress/privs2/EXPECTED138 @@ -19,6 +19,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES --- SQL operation complete. >> @@ -34,6 +35,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES --- SQL operation complete. >>create table user1_t1 (c1 int not null primary key, c2 int); @@ -118,6 +120,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES --- SQL operation complete. >>create table user1_t1 (c1 int not null primary key, c2 int); @@ -198,6 +201,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES USER1_T1 USER1_T2 @@ -386,6 +390,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES USER1_T1 USER1_T2 @@ -407,6 +412,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES USER1_T1 USER1_T2 @@ -424,6 +430,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES USER1_T1 USER1_T2 @@ -444,6 +451,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES USER1_T1 USER1_T2 @@ -461,6 +469,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES --- SQL operation complete. >> @@ -472,6 +481,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES --- SQL operation complete. >> @@ -493,6 +503,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES --- SQL operation complete. >> @@ -513,6 +524,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES --- SQL operation complete. >>create table user1_t1 (c1 int not null primary key, c2 int); @@ -597,6 +609,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES USER1_T1 USER1_T2 @@ -790,6 +803,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES USER1_T1 USER1_T2 @@ -999,6 +1013,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES USER1_T1 USER1_T2 @@ -1201,6 +1216,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES USER1_T1 USER1_T2 @@ -1218,6 +1234,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES --- SQL operation complete. >> @@ -1272,6 +1289,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES --- SQL operation complete. >>create table user1_t1 (c1 int not null primary key, c2 int); @@ -1511,6 +1529,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES USER1_T1 USER1_T2 @@ -1528,6 +1547,7 @@ Tables in Schema TRAFODION.T138SCH SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES --- SQL operation complete. >> @@ -1739,7 +1759,7 @@ End of MXCI Session (EXPR) -------------------- - 11 + 13 --- 1 row(s) selected. >> @@ -1753,7 +1773,7 @@ End of MXCI Session (EXPR) -------------------- - 11 + 13 --- 1 row(s) selected. >> http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/021ebd6c/core/sql/regress/privs2/EXPECTED140 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/EXPECTED140 b/core/sql/regress/privs2/EXPECTED140 index 84543e2..b31683f 100644 --- a/core/sql/regress/privs2/EXPECTED140 +++ b/core/sql/regress/privs2/EXPECTED140 @@ -206,6 +206,7 @@ GAMES PLAYERS SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS +SB_PERSISTENT_SAMPLES TEAMS --- SQL operation complete. @@ -318,6 +319,7 @@ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GAMES TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GAMES VI SQL_USER1 SQL_USER2 S------ NONE TRAFODION.T140_SHARED_VIEWS.SB_HISTOGRAMS BT -2 DB__ROOT SIDU-R- SIDU-R- TRAFODION.T140_SHARED_VIEWS.SB_HISTOGRAM_INTERVALS BT -2 DB__ROOT SIDU-R- SIDU-R- +TRAFODION.T140_SHARED_VIEWS.SB_PERSISTENT_SAMPLES BT -2 DB__ROOT SIDU-R- SIDU-R- TRAFODION.T140_SHARED_VIEWS.T140_L1 LB -2 DB__ROOT ---UG-- ---UG-- TRAFODION.T140_SHARED_VIEWS.T140_TRANSLATEPRIVSBIT UR -2 DB__ROOT ------E ------E TRAFODION.T140_SHARED_VIEWS.T140_TRANSLATEPRIVSBIT UR DB__ROOT -1 ------E NONE @@ -327,10 +329,11 @@ TRAFODION.T140_USER1_PRIVATE.PLAYERS TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER1 SQL_USER2 SI----- SI----- TRAFODION.T140_USER1_PRIVATE.SB_HISTOGRAMS BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.SB_HISTOGRAM_INTERVAL BT -2 SQL_USER1 SIDU-R- SIDU-R- +TRAFODION.T140_USER1_PRIVATE.SB_PERSISTENT_SAMPLES BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER1 SQL_USER2 SIDU-R- SIDU-R- ---- 16 row(s) selected. +--- 18 row(s) selected. >> >>exit; @@ -486,6 +489,7 @@ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GAMES TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GAMES VI SQL_USER1 SQL_USER2 S------ NONE TRAFODION.T140_SHARED_VIEWS.SB_HISTOGRAMS BT -2 DB__ROOT SIDU-R- SIDU-R- TRAFODION.T140_SHARED_VIEWS.SB_HISTOGRAM_INTERVALS BT -2 DB__ROOT SIDU-R- SIDU-R- +TRAFODION.T140_SHARED_VIEWS.SB_PERSISTENT_SAMPLES BT -2 DB__ROOT SIDU-R- SIDU-R- TRAFODION.T140_SHARED_VIEWS.T140_L1 LB -2 DB__ROOT ---UG-- ---UG-- TRAFODION.T140_SHARED_VIEWS.T140_TRANSLATEPRIVSBIT UR -2 DB__ROOT ------E ------E TRAFODION.T140_SHARED_VIEWS.T140_TRANSLATEPRIVSBIT UR DB__ROOT -1 ------E NONE @@ -496,11 +500,12 @@ TRAFODION.T140_USER1_PRIVATE.PLAYERS TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER2 SQL_USER3 SI----- -I----- TRAFODION.T140_USER1_PRIVATE.SB_HISTOGRAMS BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.SB_HISTOGRAM_INTERVAL BT -2 SQL_USER1 SIDU-R- SIDU-R- +TRAFODION.T140_USER1_PRIVATE.SB_PERSISTENT_SAMPLES BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER1 SQL_USER2 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER2 SQL_USER3 S-D---- S-D---- ---- 19 row(s) selected. +--- 21 row(s) selected. >> >>exit; @@ -630,6 +635,7 @@ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GAMES TRAFODION.T140_SHARED_VIEWS.PLAYERS_ON_TEAM VI -2 SQL_USER3 S------ NONE TRAFODION.T140_SHARED_VIEWS.SB_HISTOGRAMS BT -2 DB__ROOT SIDU-R- SIDU-R- TRAFODION.T140_SHARED_VIEWS.SB_HISTOGRAM_INTERVALS BT -2 DB__ROOT SIDU-R- SIDU-R- +TRAFODION.T140_SHARED_VIEWS.SB_PERSISTENT_SAMPLES BT -2 DB__ROOT SIDU-R- SIDU-R- TRAFODION.T140_SHARED_VIEWS.T140_L1 LB -2 DB__ROOT ---UG-- ---UG-- TRAFODION.T140_SHARED_VIEWS.T140_TRANSLATEPRIVSBIT UR -2 DB__ROOT ------E ------E TRAFODION.T140_SHARED_VIEWS.T140_TRANSLATEPRIVSBIT UR DB__ROOT -1 ------E NONE @@ -641,12 +647,13 @@ TRAFODION.T140_USER1_PRIVATE.PLAYERS TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER3 SQL_USER4 -I----- NONE TRAFODION.T140_USER1_PRIVATE.SB_HISTOGRAMS BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.SB_HISTOGRAM_INTERVAL BT -2 SQL_USER1 SIDU-R- SIDU-R- +TRAFODION.T140_USER1_PRIVATE.SB_PERSISTENT_SAMPLES BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER1 SQL_USER2 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER2 SQL_USER3 S-D---- S-D---- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER3 SQL_USER4 S------ NONE ---- 22 row(s) selected. +--- 24 row(s) selected. >> >>exit; @@ -736,6 +743,7 @@ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GAMES TRAFODION.T140_SHARED_VIEWS.PLAYERS_ON_TEAM VI -2 SQL_USER3 S------ NONE TRAFODION.T140_SHARED_VIEWS.SB_HISTOGRAMS BT -2 DB__ROOT SIDU-R- SIDU-R- TRAFODION.T140_SHARED_VIEWS.SB_HISTOGRAM_INTERVALS BT -2 DB__ROOT SIDU-R- SIDU-R- +TRAFODION.T140_SHARED_VIEWS.SB_PERSISTENT_SAMPLES BT -2 DB__ROOT SIDU-R- SIDU-R- TRAFODION.T140_SHARED_VIEWS.T140_L1 LB -2 DB__ROOT ---UG-- ---UG-- TRAFODION.T140_SHARED_VIEWS.T140_TRANSLATEPRIVSBIT UR -2 DB__ROOT ------E ------E TRAFODION.T140_SHARED_VIEWS.T140_TRANSLATEPRIVSBIT UR DB__ROOT -1 ------E NONE @@ -748,12 +756,13 @@ TRAFODION.T140_USER1_PRIVATE.PLAYERS TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER3 SQL_USER4 -I----- NONE TRAFODION.T140_USER1_PRIVATE.SB_HISTOGRAMS BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.SB_HISTOGRAM_INTERVAL BT -2 SQL_USER1 SIDU-R- SIDU-R- +TRAFODION.T140_USER1_PRIVATE.SB_PERSISTENT_SAMPLES BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER1 SQL_USER2 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER2 SQL_USER3 S-D---- S-D---- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER3 SQL_USER4 S------ NONE ---- 23 row(s) selected. +--- 25 row(s) selected. >> >>exit; @@ -867,6 +876,7 @@ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GAMES TRAFODION.T140_SHARED_VIEWS.PLAYERS_ON_TEAM VI -2 SQL_USER3 S------ NONE TRAFODION.T140_SHARED_VIEWS.SB_HISTOGRAMS BT -2 DB__ROOT SIDU-R- SIDU-R- TRAFODION.T140_SHARED_VIEWS.SB_HISTOGRAM_INTERVALS BT -2 DB__ROOT SIDU-R- SIDU-R- +TRAFODION.T140_SHARED_VIEWS.SB_PERSISTENT_SAMPLES BT -2 DB__ROOT SIDU-R- SIDU-R- TRAFODION.T140_SHARED_VIEWS.T140_L1 LB -2 DB__ROOT ---UG-- ---UG-- TRAFODION.T140_SHARED_VIEWS.T140_TRANSLATEPRIVSBIT UR -2 DB__ROOT ------E ------E TRAFODION.T140_SHARED_VIEWS.T140_TRANSLATEPRIVSBIT UR DB__ROOT -1 ------E NONE @@ -886,12 +896,13 @@ TRAFODION.T140_USER1_PRIVATE.PLAYERS TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER3 SQL_USER4 -I----- NONE TRAFODION.T140_USER1_PRIVATE.SB_HISTOGRAMS BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.SB_HISTOGRAM_INTERVAL BT -2 SQL_USER1 SIDU-R- SIDU-R- +TRAFODION.T140_USER1_PRIVATE.SB_PERSISTENT_SAMPLES BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER1 SQL_USER2 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER2 SQL_USER3 S-D---- S-D---- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER3 SQL_USER4 S------ NONE ---- 30 row(s) selected. +--- 32 row(s) selected. >> >>-- user6 tries to grant select to user7 >>grant select on games to sql_user7 by sql_user6; @@ -943,6 +954,7 @@ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GAMES TRAFODION.T140_SHARED_VIEWS.PLAYERS_ON_TEAM VI -2 SQL_USER3 S------ NONE TRAFODION.T140_SHARED_VIEWS.SB_HISTOGRAMS BT -2 DB__ROOT SIDU-R- SIDU-R- TRAFODION.T140_SHARED_VIEWS.SB_HISTOGRAM_INTERVALS BT -2 DB__ROOT SIDU-R- SIDU-R- +TRAFODION.T140_SHARED_VIEWS.SB_PERSISTENT_SAMPLES BT -2 DB__ROOT SIDU-R- SIDU-R- TRAFODION.T140_SHARED_VIEWS.T140_L1 LB -2 DB__ROOT ---UG-- ---UG-- TRAFODION.T140_SHARED_VIEWS.T140_TRANSLATEPRIVSBIT UR -2 DB__ROOT ------E ------E TRAFODION.T140_SHARED_VIEWS.T140_TRANSLATEPRIVSBIT UR DB__ROOT -1 ------E NONE @@ -960,11 +972,12 @@ TRAFODION.T140_USER1_PRIVATE.PLAYERS TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER3 SQL_USER4 -I----- NONE TRAFODION.T140_USER1_PRIVATE.SB_HISTOGRAMS BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.SB_HISTOGRAM_INTERVAL BT -2 SQL_USER1 SIDU-R- SIDU-R- +TRAFODION.T140_USER1_PRIVATE.SB_PERSISTENT_SAMPLES BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER1 SQL_USER2 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER2 SQL_USER3 S-D---- S-D---- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER3 SQL_USER4 S------ NONE ---- 28 row(s) selected. +--- 30 row(s) selected. >> >>log;
