[ https://issues.apache.org/jira/browse/TRAFODION-3290?focusedWorklogId=222659&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-222659 ]
ASF GitHub Bot logged work on TRAFODION-3290: --------------------------------------------- Author: ASF GitHub Bot Created on: 03/Apr/19 22:05 Start Date: 03/Apr/19 22:05 Worklog Time Spent: 10m Work Description: DaveBirdsall commented on pull request #1822: [TRAFODION-3290] Give better errors for case where table is all LOB columns URL: https://github.com/apache/trafodion/pull/1822 Formerly, certain UPDATE STATISTICS commands would fail with an odd syntax error if one were attempting to create a persistent sample table on a base table having all LOB columns. With the changes here, we will now get a helpful 9233 error message instead. In a few other circumstances, the 9220 warning message text was not accurate. The text for this message has now been revised. ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking ------------------- Worklog Id: (was: 222659) Time Spent: 10m Remaining Estimate: 0h > Creating a sample table on an all-LOB table fails with syntax error > ------------------------------------------------------------------- > > Key: TRAFODION-3290 > URL: https://issues.apache.org/jira/browse/TRAFODION-3290 > Project: Apache Trafodion > Issue Type: Bug > Components: sql-cmp > Affects Versions: 2.4 > Reporter: David Wayne Birdsall > Assignee: David Wayne Birdsall > Priority: Major > Time Spent: 10m > Remaining Estimate: 0h > > The following script reproduces the problem: > {quote}control query default TRAF_BLOB_AS_VARCHAR 'OFF'; > control query default TRAF_CLOB_AS_VARCHAR 'OFF'; > drop table if exists t1; > create table t1 (c_clob clob, c_blob blob); > insert into t1 values (stringtolob('clob_1'), stringtolob('blob_1')); > update statistics for table t1 on (c_clob, c_blob) sample random 50 percent > persistent; > update statistics for table t1 create sample random 100 percent; > update statistics for table t1 on every column sample random 100 percent > persistent; > update statistics for table t1 on every key sample random 100 percent > persistent; > update statistics for table t1 on existing column sample random 100 percent > persistent; > update statistics for table t1 on necessary column sample random 100 percent > persistent; > {quote} > When run, attempts to create a sample table fail with a syntax error. Instead > we should fail with error 9246, as shown below: > {quote}>>obey repro.sql; > >>control query default TRAF_BLOB_AS_VARCHAR 'OFF'; > --- SQL operation complete. > >>control query default TRAF_CLOB_AS_VARCHAR 'OFF'; > --- SQL operation complete. > >> > >>drop table if exists t1; > --- SQL operation complete. > >> > >>create table t1 (c_clob clob, c_blob blob); > --- SQL operation complete. > >>insert into t1 values (stringtolob('clob_1'), stringtolob('blob_1')); > --- 1 row(s) inserted. > >> > >>update statistics for table t1 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. > >> > >>update statistics for table t1 create sample random 100 percent; > *** ERROR[9214] Object > TRAFODION.SCH.TRAF_SAMPLE_00469125659506823465_1553720877_834150 could not be > created. > *** ERROR[15001] A syntax error occurred at or before: > create table TRAFODION.SCH.TRAF_SAMPLE_00469125659506823465_1553720877_834150 > ( ) ATTRIBUTES ALIGNED FORMAT; > ^ (86 characters from start of SQL statement) > *** ERROR[8822] The statement was not prepared. > *** ERROR[8839] Transaction was aborted. > --- SQL operation failed with errors. > >>update statistics for table t1 on every column sample random 100 percent > >>persistent; > *** ERROR[9214] Object > TRAFODION.SCH.TRAF_SAMPLE_00469125659506823465_1553720916_953637 could not be > created. > *** ERROR[15001] A syntax error occurred at or before: > create table TRAFODION.SCH.TRAF_SAMPLE_00469125659506823465_1553720916_953637 > ( ) ATTRIBUTES ALIGNED FORMAT; > ^ (86 characters from start of SQL statement) > *** ERROR[8822] The statement was not prepared. > *** ERROR[8839] Transaction was aborted. > --- SQL operation failed with errors. > >>update statistics for table t1 on every key sample random 100 percent > >>persistent; > *** ERROR[9214] Object > TRAFODION.SCH.TRAF_SAMPLE_00469125659506823465_1553720950_701762 could not be > created. > *** ERROR[15001] A syntax error occurred at or before: > create table TRAFODION.SCH.TRAF_SAMPLE_00469125659506823465_1553720950_701762 > ( ) ATTRIBUTES ALIGNED FORMAT; > ^ (86 characters from start of SQL statement) > *** ERROR[8822] The statement was not prepared. > *** ERROR[8839] Transaction was aborted. > --- SQL operation failed with errors. > >>update statistics for table t1 on existing column sample random 100 percent > >>persistent; > *** WARNING[9220] The table is empty, so no persistent sample table was > created. > *** WARNING[9217] The statement will have no effect because no histograms are > currently maintained for the table. > --- SQL operation completed with warnings. > >>update statistics for table t1 on necessary column sample random 100 > >>percent persistent; > *** WARNING[9220] The table is empty, so no persistent sample table was > created. > *** WARNING[9218] The statement will have no effect because no histograms > need to be updated. > --- SQL operation completed with warnings. > >>exit; > End of MXCI Session > {quote} -- This message was sent by Atlassian JIRA (v7.6.3#76005)