This is an automated email from the ASF dual-hosted git repository.
dbirdsall pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/trafodion.git
The following commit(s) were added to refs/heads/master by this push:
new f627fd7 [TRAFODION-3290] Give better errors for case where table is
all LOB columns
new 00df580 Merge pull request #1822 from DaveBirdsall/master
f627fd7 is described below
commit f627fd7e68a2d6d0e4f4e772afd46b2d94b9eae6
Author: Dave Birdsall <[email protected]>
AuthorDate: Wed Apr 3 21:48:30 2019 +0000
[TRAFODION-3290] Give better errors for case where table is all LOB columns
---
core/sql/bin/SqlciErrors.txt | 4 +-
core/sql/regress/compGeneral/EXPECTED023 | 109 ++++++++++++++++-----
core/sql/regress/compGeneral/TEST023 | 15 +++
core/sql/ustat/hs_const.h | 1 +
core/sql/ustat/hs_globals.cpp | 16 +++
core/sql/ustat/hs_la.cpp | 22 +++++
core/sql/ustat/hs_la.h | 2 +
.../src/asciidoc/_chapters/update_stats_msgs.adoc | 25 ++++-
8 files changed, 164 insertions(+), 30 deletions(-)
diff --git a/core/sql/bin/SqlciErrors.txt b/core/sql/bin/SqlciErrors.txt
index ef3c5a5..f8ccd90 100644
--- a/core/sql/bin/SqlciErrors.txt
+++ b/core/sql/bin/SqlciErrors.txt
@@ -1909,7 +1909,7 @@ drop the default context
9217 ZZZZZ 99999 BEGINNER INFRM DBADMIN The statement will have no effect
because no histograms are currently maintained for the table.
9218 ZZZZZ 99999 BEGINNER INFRM DBADMIN The statement will have no effect
because no histograms need to be updated.
9219 ZZZZZ 99999 BEGINNER MAJOR DBADMIN Incremental UPDATE STATISTICS: An
operation failed, possibly due to an invalid WHERE clause.
-9220 ZZZZZ 99999 BEGINNER INFRM DBADMIN The table is empty, so no persistent
sample table was created.
+9220 ZZZZZ 99999 BEGINNER INFRM DBADMIN No persistent sample table was created
because the table was empty or there were no histograms needing update.
9221 ZZZZZ 99999 BEGINNER MAJOR DBADMIN Incremental UPDATE STATISTICS cannot
be performed due to the absence of the IUS persistent sample table for
$0~string0. Use a regular UPDATE STATISTICS command with the sample clause and
PERSISTENT first to create such a persistent sample table.
9222 ZZZZZ 99999 BEGINNER MINOR LOGONLY Incremental UPDATE STATISTICS: the
rowcount in an interval for column $0~String0 changed more than the percentage
specified by CQD USTAT_IUS_INTERVAL_ROWCOUNT_CHANGE_THRESHOLD. A regular UPDATE
STATISTICS is performed instead.
9223 ZZZZZ 99999 BEGINNER MINOR LOGONLY Incremental UPDATE STATISTICS: the
total rowcount for column $0~String0 changed more than the percentage specified
by CQD USTAT_IUS_TOTAL_ROWCOUNT_CHANGE_THRESHOLD. A regular UPDATE STATISTICS
is performed instead.
@@ -1921,7 +1921,7 @@ drop the default context
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 --- unused ---
+9233 ZZZZZ 99999 BEGINNER MAJOR DBADMIN No operation was done since the table
consists only of LOB columns.
9234 ZZZZZ 99999 BEGINNER MINOR LOGONLY Incremental UPDATE STATISTICS: a new
high frequency value is detected for column $0~string0. A regular UPDATE
STATISTICS is performed instead.
9235 ZZZZZ 99999 BEGINNER MINOR LOGONLY --- unused ---
9236 ZZZZZ 99999 BEGINNER MINOR LOGONLY Incremental UPDATE STATISTICS: the
histogram for column $0~string0 is empty. A regular UPDATE STATISTICS is
performed instead.
diff --git a/core/sql/regress/compGeneral/EXPECTED023
b/core/sql/regress/compGeneral/EXPECTED023
index 0ea3503..5defcdc 100644
--- a/core/sql/regress/compGeneral/EXPECTED023
+++ b/core/sql/regress/compGeneral/EXPECTED023
@@ -137,7 +137,7 @@ STEST_EMPTY
OBJECT_NAME
SAMPLE_NAME
[...]
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
-STEST
TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_07158060726899021395_1517441663_444545
[...]
+STEST
TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_08919005064132513331_1554324660_717268
[...]
--- 1 row(s) selected.
>>-- should see one row
@@ -153,7 +153,7 @@ SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
-TRAF_SAMPLE_07158060726899021395_1517441663_444545
+TRAF_SAMPLE_08919005064132513331_1554324660_717268
=======================
7 row(s) returned
@@ -180,7 +180,7 @@ SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
-TRAF_SAMPLE_07158060726899021395_1517441711_141471
+TRAF_SAMPLE_08919005064132513331_1554324765_285140
=======================
7 row(s) returned
@@ -193,7 +193,7 @@ TRAF_SAMPLE_07158060726899021395_1517441711_141471
OBJECT_NAME
SAMPLE_NAME
[...]
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
-STEST
TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_07158060726899021395_1517441711_141471
[...]
+STEST
TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_08919005064132513331_1554324765_285140
[...]
--- 1 row(s) selected.
>>-- should see one row
@@ -243,7 +243,7 @@ SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
-TRAF_SAMPLE_07158060726899021395_1517441748_979037
+TRAF_SAMPLE_08919005064132513331_1554324812_578807
=======================
7 row(s) returned
@@ -256,7 +256,7 @@ TRAF_SAMPLE_07158060726899021395_1517441748_979037
OBJECT_NAME
SAMPLE_NAME
[...]
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
-STEST
TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_07158060726899021395_1517441748_979037
[...]
+STEST
TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_08919005064132513331_1554324812_578807
[...]
--- 1 row(s) selected.
>>-- should see one row
@@ -280,7 +280,7 @@ SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
-TRAF_SAMPLE_07158060726899021395_1517441802_045572
+TRAF_SAMPLE_08919005064132513331_1554324868_301800
=======================
7 row(s) returned
@@ -293,7 +293,7 @@ TRAF_SAMPLE_07158060726899021395_1517441802_045572
OBJECT_NAME
SAMPLE_NAME
[...]
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
-STEST
TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_07158060726899021395_1517441802_045572
[...]
+STEST
TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_08919005064132513331_1554324868_301800
[...]
--- 1 row(s) selected.
>>-- should see one row
@@ -354,7 +354,7 @@ STEST
OBJECT_NAME
SAMPLE_NAME
[...]
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
-STEST
TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_07158060726899021395_1517441802_045572
[...]
+STEST
TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_08919005064132513331_1554324868_301800
[...]
--- 1 row(s) selected.
>>
@@ -411,8 +411,8 @@ SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
-TRAF_SAMPLE_07158060726899021395_1517441802_045572
-TRAF_SAMPLE_07158060726899023220_1517441862_582002
+TRAF_SAMPLE_08919005064132513331_1554324868_301800
+TRAF_SAMPLE_08919005064132515594_1554324936_300860
=======================
8 row(s) returned
@@ -424,7 +424,7 @@ TRAF_SAMPLE_07158060726899023220_1517441862_582002
OBJECT_NAME
SAMPLE_NAME
[...]
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
-STESTC
TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_07158060726899023220_1517441862_582002
[...]
+STESTC
TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_08919005064132515594_1554324936_300860
[...]
--- 1 row(s) selected.
>>
@@ -449,8 +449,8 @@ SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
-TRAF_SAMPLE_07158060726899021395_1517441802_045572
-TRAF_SAMPLE_07158060726899023220_1517441862_582002
+TRAF_SAMPLE_08919005064132513331_1554324868_301800
+TRAF_SAMPLE_08919005064132515594_1554324936_300860
=======================
8 row(s) returned
@@ -465,7 +465,7 @@ TRAF_SAMPLE_07158060726899023220_1517441862_582002
>>
>>update statistics for table stest_empty on every column sample random 10
>>percent persistent;
-*** WARNING[9220] The table is empty, so no persistent sample table was
created.
+*** WARNING[9220] No persistent sample table was created because the table was
empty or there were no histograms needing update.
--- SQL operation completed with warnings.
>>
@@ -480,8 +480,8 @@ SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
-TRAF_SAMPLE_07158060726899021395_1517441802_045572
-TRAF_SAMPLE_07158060726899023220_1517441862_582002
+TRAF_SAMPLE_08919005064132513331_1554324868_301800
+TRAF_SAMPLE_08919005064132515594_1554324936_300860
=======================
8 row(s) returned
@@ -512,8 +512,8 @@ SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
-TRAF_SAMPLE_07158060726899021395_1517441802_045572
-TRAF_SAMPLE_07158060726899023220_1517441862_582002
+TRAF_SAMPLE_08919005064132513331_1554324868_301800
+TRAF_SAMPLE_08919005064132515594_1554324936_300860
=======================
8 row(s) returned
@@ -527,8 +527,8 @@ TRAF_SAMPLE_07158060726899023220_1517441862_582002
*** ERROR[9219] Incremental UPDATE STATISTICS: An operation failed, possibly
due to an invalid WHERE clause.
*** ERROR[15001] A syntax error occurred at or before:
-DELETE WITH NO ROLLBACK FROM TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_07158060
-726899021395_1517441802_045572 WHERE 1;
+DELETE WITH NO ROLLBACK FROM TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_08919005
+064132513331_1554324868_301800 WHERE 1;
^ (119 characters from start of SQL
statement)
*** ERROR[8822] The statement was not prepared.
@@ -546,8 +546,8 @@ SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
-TRAF_SAMPLE_07158060726899021395_1517441802_045572
-TRAF_SAMPLE_07158060726899023220_1517441862_582002
+TRAF_SAMPLE_08919005064132513331_1554324868_301800
+TRAF_SAMPLE_08919005064132515594_1554324936_300860
=======================
8 row(s) returned
@@ -560,7 +560,7 @@ TRAF_SAMPLE_07158060726899023220_1517441862_582002
*** ERROR[9219] Incremental UPDATE STATISTICS: An operation failed, possibly
due to an invalid WHERE clause.
-*** ERROR[4001] Column BADCOL is not found. Tables in scope:
TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_07158060726899021395_1517441802_045572.
Default schema: TRAFODION.SCH.
+*** ERROR[4001] Column BADCOL is not found. Tables in scope:
TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_08919005064132513331_1554324868_301800.
Default schema: TRAFODION.SCH.
*** ERROR[8822] The statement was not prepared.
@@ -601,7 +601,7 @@ SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
-TRAF_SAMPLE_07158060726899021395_1517441802_045572
+TRAF_SAMPLE_08919005064132513331_1554324868_301800
=======================
7 row(s) returned
@@ -609,6 +609,62 @@ TRAF_SAMPLE_07158060726899021395_1517441802_045572
--- SQL operation complete.
>> -- should be the same as previous "get tables" except only one sample table
>>
+>>-- attempt to create persistent sample table on a table of all LOB columns
+>>
+>>control query default TRAF_BLOB_AS_VARCHAR 'OFF';
+
+--- SQL operation complete.
+>>control query default TRAF_CLOB_AS_VARCHAR 'OFF';
+
+--- SQL operation complete.
+>>
+>>create table slobs (c_clob clob, c_blob blob);
+
+--- SQL operation complete.
+>>insert into slobs values (stringtolob('clob_1'), stringtolob('blob_1'));
+
+--- 1 row(s) inserted.
+>>
+>>update statistics for table slobs on (c_clob, c_blob) sample random 50
percent persistent;
+
+*** ERROR[9246] UPDATE STATISTICS is not supported on LOB columns. Column
C_BLOB is a LOB column.
+
+--- SQL operation failed with errors.
+>> -- gets error 9246
+>>update statistics for table slobs create sample random 100 percent;
+
+*** ERROR[9233] No operation was done since the table consists only of LOB
columns.
+
+--- SQL operation failed with errors.
+>> -- gets error 9233
+>>update statistics for table slobs on every column sample random 100 percent
persistent;
+
+*** ERROR[9233] No operation was done since the table consists only of LOB
columns.
+
+--- SQL operation failed with errors.
+>> -- gets error 9233
+>>update statistics for table slobs on every key sample random 100 percent
persistent;
+
+*** ERROR[9233] No operation was done since the table consists only of LOB
columns.
+
+--- SQL operation failed with errors.
+>> -- gets error 9233
+>>update statistics for table slobs on existing column sample random 100
percent persistent;
+
+*** WARNING[9220] No persistent sample table was created because the table was
empty or there were no histograms needing update.
+
+*** WARNING[9217] The statement will have no effect because no histograms are
currently maintained for the table.
+
+--- SQL operation completed with warnings.
+>> -- gets warning 9220
+>>update statistics for table slobs on necessary column sample random 100
percent persistent;
+
+*** WARNING[9220] No persistent sample table was created because the table was
empty or there were no histograms needing update.
+
+*** WARNING[9218] The statement will have no effect because no histograms need
to be updated.
+
+--- SQL operation completed with warnings.
+>> -- gets warning 9220
>>
>>?section clnup
>>
@@ -621,6 +677,9 @@ TRAF_SAMPLE_07158060726899021395_1517441802_045572
>>drop table stest_empty;
--- SQL operation complete.
+>>drop table slobs;
+
+--- SQL operation complete.
>>
>>get tables;
diff --git a/core/sql/regress/compGeneral/TEST023
b/core/sql/regress/compGeneral/TEST023
index c55eeec..7d6f792 100755
--- a/core/sql/regress/compGeneral/TEST023
+++ b/core/sql/regress/compGeneral/TEST023
@@ -285,12 +285,27 @@ update statistics for table stest create sample random 20
percent;
get tables; -- should be the same as previous "get tables" except only one
sample table
+-- attempt to create persistent sample table on a table of all LOB columns
+
+control query default TRAF_BLOB_AS_VARCHAR 'OFF';
+control query default TRAF_CLOB_AS_VARCHAR 'OFF';
+
+create table slobs (c_clob clob, c_blob blob);
+insert into slobs values (stringtolob('clob_1'), stringtolob('blob_1'));
+
+update statistics for table slobs on (c_clob, c_blob) sample random 50 percent
persistent; -- gets error 9246
+update statistics for table slobs create sample random 100 percent; -- gets
error 9233
+update statistics for table slobs on every column sample random 100 percent
persistent; -- gets error 9233
+update statistics for table slobs on every key sample random 100 percent
persistent; -- gets error 9233
+update statistics for table slobs on existing column sample random 100 percent
persistent; -- gets warning 9220
+update statistics for table slobs on necessary column sample random 100
percent persistent; -- gets warning 9220
?section clnup
drop table stest;
drop table stestc;
drop table stest_empty;
+drop table slobs;
get tables; -- sample tables should be gone too
diff --git a/core/sql/ustat/hs_const.h b/core/sql/ustat/hs_const.h
index 6b36c4b..414b670 100644
--- a/core/sql/ustat/hs_const.h
+++ b/core/sql/ustat/hs_const.h
@@ -157,6 +157,7 @@ enum USTAT_ERROR_CODES {UERR_SYNTAX_ERROR
= 15001,
UERR_WARNING_IUS_INSUFFICIENT_MEMORY = 9230,
UERR_IUS_WRONG_RANDOM = 9231,
UERR_IUS_IN_PROGRESS = 9232,
+ UERR_ALL_LOB_COLUMNS = 9233,
UERR_IUS_INSERT_NONMFV_OVERFLOW = 9234,
UERR_IUS_NO_EXISTING_STATS = 9236,
UERR_IUS_WHERE_CLAUSE = 9237,
diff --git a/core/sql/ustat/hs_globals.cpp b/core/sql/ustat/hs_globals.cpp
index a8f52ce..cf3c73a 100644
--- a/core/sql/ustat/hs_globals.cpp
+++ b/core/sql/ustat/hs_globals.cpp
@@ -5470,6 +5470,14 @@ Lng32 HSGlobalsClass::CollectStatistics()
}
else if (optFlags & IUS_PERSIST) // PERSIST keyword given with sample
clause
{
+ // If all the user columns in the table are LOB columns, our sample
table
+ // would have zero columns. So return an error for that case.
+ if (objDef->allUserColumnsAreLOBs())
+ {
+ HSFuncMergeDiags(- UERR_ALL_LOB_COLUMNS);
+ return -1;
+ }
+
// Create a persistent sample table. It will be used for this non-IUS
// execution of Update Stats, and updated incrementally for subsequent
// IUS operations.
@@ -16029,6 +16037,14 @@ Lng32 managePersistentSamples()
return -1;
}
+ // If all the user columns in the table are LOB columns, our sample table
+ // would have zero columns. So return an error for that case.
+ if (hs_globals->objDef->allUserColumnsAreLOBs())
+ {
+ HSFuncMergeDiags(- UERR_ALL_LOB_COLUMNS);
+ return -1;
+ }
+
// tableRows could be zero for a Trafodion or HBase table if the table is
new
// and all the data is still in memstore. So, in the logic below we dance
around
// that, attempting to supply a not unreasonable guess for tableRows in
that case.
diff --git a/core/sql/ustat/hs_la.cpp b/core/sql/ustat/hs_la.cpp
index d188853..54f86e6 100644
--- a/core/sql/ustat/hs_la.cpp
+++ b/core/sql/ustat/hs_la.cpp
@@ -1390,3 +1390,25 @@ void HSTableDef::addTruncatedSelectList(NAString & qry)
}
}
+//
+// METHOD: allUserColumnsAreLOBs()
+//
+// PURPOSE: Determines if all user columns are LOBs. If so,
+// returns TRUE. Otherwise, returns FALSE.
+//
+NABoolean HSTableDef::allUserColumnsAreLOBs()
+ {
+ for (Int32 i = 0; i < numCols_; i++)
+ {
+ if (!DFS2REC::isLOB(colInfo_[i].datatype))
+ {
+ if (strcmp(*colInfo_[i].colname,"SYSKEY") != 0) // SYSKEY is not a
user column
+ return FALSE;
+ // we don't need to check for "_SALT_" or divisioning columns
+ // since those could only be present if there were user key columns
+ }
+ }
+ return TRUE;
+ }
+
+
diff --git a/core/sql/ustat/hs_la.h b/core/sql/ustat/hs_la.h
index e205862..369a840 100644
--- a/core/sql/ustat/hs_la.h
+++ b/core/sql/ustat/hs_la.h
@@ -122,6 +122,8 @@ class HSTableDef : public NABasicObject
void addTruncatedSelectList(NAString & qry);
+ NABoolean allUserColumnsAreLOBs();
+
protected:
NABoolean setObjectUID(NABoolean createExternalObject);
diff --git a/docs/messages_guide/src/asciidoc/_chapters/update_stats_msgs.adoc
b/docs/messages_guide/src/asciidoc/_chapters/update_stats_msgs.adoc
index 51c7e3e..8f6e8bd 100644
--- a/docs/messages_guide/src/asciidoc/_chapters/update_stats_msgs.adoc
+++ b/docs/messages_guide/src/asciidoc/_chapters/update_stats_msgs.adoc
@@ -316,11 +316,12 @@ contact {project-support}.
== SQL 9220
```
-The table is empty, so no persistent sample table was created.
+No persistent sample table was created because the table was empty or there
were no histograms needing update.
```
-*Cause:* UPDATE STATISTICS SAMPLE RANDOM PERSISTENT was specified but the
table was empty. Histograms
-were created, however no persistent sample table was created.
+*Cause:* If UPDATE STATISTICS SAMPLE RANDOM PERSISTENT was specified, the
table was empty. Histograms
+were created, however no persistent sample table was created. If UPDATE
STATISTICS ON EXISTING COLUMNS
+PERSISTENT was specified and there were no existing histograms, no persistent
sample table is created.
*Effect:* The operation completes.
@@ -373,6 +374,24 @@ another UPDATE STATISTICS INCREMENTAL operation was
already in progress for the
*Recovery:* If this was not the intended statement correct and resubmit.
Otherwise no recovery
is necessary.
+[[SQL-9233]]
+== SQL 9233
+
+```
+No operation was done since the table consists only of LOB columns.
+```
+
+*Cause:* An UPDATE STATISTICS command requested creation of a persistent
sample table, there
+were no user columns in the base table to create a sample table from. UPDATE
STATISTICS is
+not supported on LOB columns, so it skips LOB columns when computing which
columns to create
+in the sample table.
+
+*Effect:* {project-name} is unable to continue processing.
+
+*Recovery:* If this was not the intended statement correct and resubmit.
Otherwise no recovery
+is necessary.
+
+<<<
[[SQL-9240]]
== SQL 9240
