[jira] [Commented] (TRAFODION-1838) Upadate statistics with sampling does not work occasionally when table has nullable key column

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-1838?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15154498#comment-15154498 ] 

David Wayne Birdsall commented on TRAFODION-1838:
-------------------------------------------------

My first thought is that perhaps ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT needs to be added to USTAT_CQDS_ALLOWED_FOR_SPAWNED_COMPILERS. Perhaps that would yield a workaround.

> Upadate statistics with sampling does not work occasionally when table has nullable key column
> ----------------------------------------------------------------------------------------------
>
>                 Key: TRAFODION-1838
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-1838
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>    Affects Versions: 1.2-incubating
>            Reporter: Suresh Subbiah
>            Assignee: David Wayne Birdsall
>
> If a table has a nullable key column then update statistics is not able to create a sample table. 
> This is true even if the cqd allow_nullable_unique_key_constraint 'on' is set before issuing update stats statement.
> To reproduce
> drop table test1 ;
> cqd allow_nullable_unique_key_constraint 'on' ;
> create table test1(a int) store by (a) ;
> insert into test1 values (1),(2),(3),(4),(5),(6),(7),(8) ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> upsert using load into test1 select * from test1 ;
> -- should have about a million rows in table now
> exit;
> cqd ustat_use_is_when_no_stats 'off' ;
> update statistics for table test1 on every column sample ;
> If IS is used then for such narrow tables we don't bother to create a sample table even if we do sample. Its all done in one pass.
> This may have something to do with the secondary compiler gets the cqd 
> cqd allow_nullable_unique_key_constraint 'on' ;
> Even if this cqd is set before update stats command is issued error is seen.
> Though sometimes if there is some DDL before update stats and the cqd is already issued before the DDL, update stats with sampling on such tables does seem to work. I am not certain about this though.
> Error seen
> >>update statistics for table test1 on every column sample ;
> *** ERROR[9214] Object TRAFODION.SEABASE.SQLMX_7303240222670804273_1455851118_723941 could not be created.
> *** ERROR[1135] Clustering key column A must be assigned a NOT NULL NOT DROPPABLE constraint.
> *** ERROR[9201] Unable to DROP object TRAFODION.SEABASE.SQLMX_7303240222670804273_1455851118_723941.
> *** ERROR[1389] Object TRAFODION.SEABASE.SQLMX_7303240222670804273_1455851118_723941 does not exist in Trafodion.
> --- SQL operation failed with errors.
> Ustat code is setting the necessary cqd though it does not always seem to get to where it is needed.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)