You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Hans Zeller (JIRA)" <ji...@apache.org> on 2016/05/20 01:20:14 UTC
[jira] [Updated] (TRAFODION-1053) LP Bug: 1430938 - In full explain
output, begin/end key for char/varchar key column should be min/max if
there is no predicated defined on the key column.
[ https://issues.apache.org/jira/browse/TRAFODION-1053?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Hans Zeller updated TRAFODION-1053:
-----------------------------------
Fix Version/s: (was: 2.0-incubating)
2.1-incubating
> LP Bug: 1430938 - In full explain output, begin/end key for char/varchar key column should be min/max if there is no predicated defined on the key column.
> ----------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: TRAFODION-1053
> URL: https://issues.apache.org/jira/browse/TRAFODION-1053
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: Julie Thai
> Assignee: Howard Qin
> Fix For: 2.1-incubating
>
>
> In full explain output, begin/end key for char/varchar key column should be min/max
> if there is no predicated defined on the key column.
> Snippet from TRAFODION_SCAN below:
> key_columns ............ _SALT_, COLTS, COLVCHRUCS2, COLINTS
> begin_key .............. (_SALT_ = %(9)), (COLTS = <min>),
> (COLVCHRUCS2 = '洼硡'), (COLINTS = <min>)
> end_key ................ (_SALT_ = %(9)), (COLTS = <max>),
> (COLVCHRUCS2 = '洼湩'), (COLINTS = <max>)
> Expected (COLVCHRUCS2 = '<min>') and (COLVCHRUCS2 = '<max>').
> SQL>create table salttbl3 (
> +>colintu int unsigned not null, colints int signed not null,
> +>colsintu smallint unsigned not null, colsints smallint signed not null,
> +>collint largeint not null, colnum numeric(11,3) not null,
> +>colflt float not null, coldec decimal(11,2) not null,
> +>colreal real not null, coldbl double precision not null,
> +>coldate date not null, coltime time not null,
> +>colts timestamp not null,
> +>colchriso char(90) character set iso88591 not null,
> +>colchrucs2 char(111) character set ucs2 not null,
> +>colvchriso varchar(113) character set iso88591 not null,
> +>colvchrucs2 varchar(115) character set ucs2 not null,
> +>PRIMARY KEY (colts ASC, colvchrucs2 DESC, colints ASC))
> +>SALT USING 9 PARTITIONS ON (colints, colvchrucs2, colts);
> --- SQL operation complete.
> SQL>LOAD INTO salttbl3 SELECT
> +>c1+c2*10+c3*100+c4*1000+c5*10000,
> +>(c1+c2*10+c3*100+c4*1000+c5*10000) - 50000,
> +>mod(c1+c2*10+c3*100+c4*1000+c5*10000, 65535),
> +>mod(c1+c2*10+c3*100+c4*1000+c5*10000, 32767),
> +>(c1+c2*10+c3*100+c4*1000+c5*10000) + 549755813888,
> +>cast(c1+c2*10+c3*100+c4*1000+c5*10000 as numeric(11,3)),
> +>cast(c1+c2*10+c3*100+c4*1000+c5*10000 as float),
> +>cast(c1+c2*10+c3*100+c4*1000+c5*10000 as decimal(11,2)),
> +>cast(c1+c2*10+c3*100+c4*1000+c5*10000 as real),
> +>cast(c1+c2*10+c3*100+c4*1000+c5*10000 as double precision),
> +>cast(converttimestamp(210614299200000000 +
> +>(86400000000 * (c1+c2*10+c3*100+c4*1000+c5*10000))) as date),
> +>time'00:00:00' + cast(mod(c1+c2*10+c3*100+c4*1000+c5*10000,3)
> +>as interval minute),
> +>converttimestamp(210614299200000000 + (86400000000 *
> +>(c1+c2*10+c3*100+c4*1000+c5*10000)) + (1000000 * (c1+c2*10+c3*100)) +
> +>(60000000 * (c1+c2*10)) + (3600000000 * (c1+c2*10))),
> +>cast(c1+c2*10+c3*100+c4*1000+c5*10000 as char(90) character set iso88591),
> +>cast(c1+c2*10+c3*100+c4*1000+c5*10000 as char(111) character set ucs2),
> +>cast(c1+c2*10+c3*100+c4*1000+c5*10000 as varchar(113) character set iso88591),
> +>cast(c1+c2*10+c3*100+c4*1000+c5*10000 as varchar(115) character set ucs2)
> +>from (values(1)) t
> +>transpose 0,1,2,3,4,5,6,7,8,9 as c1
> +>transpose 0,1,2,3,4,5,6,7,8,9 as c2
> +>transpose 0,1,2,3,4,5,6,7,8,9 as c3
> +>transpose 0,1,2,3,4,5,6,7,8,9 as c4
> +>transpose 0,1,2,3,4,5,6,7,8,9 as c5;
> UTIL_OUTPUT
> --------------------------------------------------------------------------------------------------------------------------------
> Task: LOAD Status: Started Object: TRAFODION.SEABASE.SALTTBL3
> Task: CLEANUP Status: Started Object: TRAFODION.SEABASE.SALTTBL3
> Task: CLEANUP Status: Ended Object: TRAFODION.SEABASE.SALTTBL3
> Task: DISABLE INDEXE Status: Started Object: TRAFODION.SEABASE.SALTTBL3
> Task: DISABLE INDEXE Status: Ended Object: TRAFODION.SEABASE.SALTTBL3
> Task: PREPARATION Status: Started Object: TRAFODION.SEABASE.SALTTBL3
> Rows Processed: 100000
> Task: PREPARATION Status: Ended ET: 00:00:10.332
> Task: COMPLETION Status: Started Object: TRAFODION.SEABASE.SALTTBL3
> Task: COMPLETION Status: Ended ET: 00:00:02.941
> Task: POPULATE INDEX Status: Started Object: TRAFODION.SEABASE.SALTTBL3
> Task: POPULATE INDEX Status: Ended ET: 00:00:05.357
> --- SQL operation complete.
> SQL>update statistics for table salttbl3 ON EVERY KEY SAMPLE RANDOM 20 PERCENT;
> --- SQL operation complete.
> SQL>prepare XX from select count(*) from salttbl3 where "_SALT_" = 9;
> --- SQL command prepared.
> SQL>explain XX;
> ------------------------------------------------------------------ PLAN SUMMARY
> MODULE_NAME .............. DYNAMICALLY COMPILED
> STATEMENT_NAME ........... XX
> PLAN_ID .................. 212292724720044885
> ROWS_OUT ................. 1
> EST_TOTAL_COST ........... 0.01
> STATEMENT ................ select count(*) from salttbl3 where "_SALT_" = 9
> ------------------------------------------------------------------ NODE LISTING
> ROOT ====================================== SEQ_NO 3 ONLY CHILD 2
> REQUESTS_IN .............. 1
> ROWS_OUT ................. 1
> EST_OPER_COST ............ 0
> EST_TOTAL_COST ........... 0.01
> DESCRIPTION
> max_card_est ........... 1
> fragment_id ............ 0
> parent_frag ............ (none)
> fragment_type .......... master
> statement_index ........ 0
> affinity_value ......... 0
> max_max_cardinality ... 10
> total_overflow_size .... 0.00 KB
> xn_access_mode ......... read_only
> xn_autoabort_interval 0
> auto_query_retry ....... enabled
> plan_version ....... 2,600
> embedded_arkcmp ........ used
> LDAP_USERNAME .......... DONTCARE
> ObjectUIDs ............. 477441946105369718
> select_list ............ count(1 )
> input_variables ........ %(9)
> SORT_SCALAR_AGGR ========================== SEQ_NO 2 ONLY CHILD 1
> REQUESTS_IN .............. 1
> ROWS_OUT ................. 1
> EST_OPER_COST ............ 0.01
> EST_TOTAL_COST ........... 0.01
> DESCRIPTION
> max_card_est ........... 1
> fragment_id ............ 0
> parent_frag ............ (none)
> fragment_type .......... master
> aggregates ............. count(1 )
> TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
> TABLE_NAME ............... SALTTBL3
> REQUESTS_IN .............. 1
> ROWS_OUT ................ 10
> EST_OPER_COST ............ 0.01
> EST_TOTAL_COST ........... 0.01
> DESCRIPTION
> max_card_est .......... 99
> fragment_id ............ 0
> parent_frag ............ (none)
> fragment_type .......... master
> scan_type .............. subset scan of table TRAFODION.SEABASE.SALTTBL3
> object_type ............ Trafodion
> cache_size ........... 100
> probes ................. 1
> rows_accessed ......... 10
> key_columns ............ _SALT_, COLTS, COLVCHRUCS2, COLINTS
> begin_key .............. (_SALT_ = %(9)), (COLTS = <min>),
> (COLVCHRUCS2 = '洼硡'), (COLINTS = <min>)
> end_key ................ (_SALT_ = %(9)), (COLTS = <max>),
> (COLVCHRUCS2 = '洼湩'), (COLINTS = <max>)
> --- SQL operation complete.
> To reproduce:
> drop table salttbl3 cascade;
> create table salttbl3 (
> colintu int unsigned not null, colints int signed not null,
> colsintu smallint unsigned not null, colsints smallint signed not null,
> collint largeint not null, colnum numeric(11,3) not null,
> colflt float not null, coldec decimal(11,2) not null,
> colreal real not null, coldbl double precision not null,
> coldate date not null, coltime time not null,
> colts timestamp not null,
> colchriso char(90) character set iso88591 not null,
> colchrucs2 char(111) character set ucs2 not null,
> colvchriso varchar(113) character set iso88591 not null,
> colvchrucs2 varchar(115) character set ucs2 not null,
> PRIMARY KEY (colts ASC, colvchrucs2 DESC, colints ASC))
> SALT USING 9 PARTITIONS ON (colints, colvchrucs2, colts);
> LOAD INTO salttbl3 SELECT
> c1+c2*10+c3*100+c4*1000+c5*10000,
> (c1+c2*10+c3*100+c4*1000+c5*10000) - 50000,
> mod(c1+c2*10+c3*100+c4*1000+c5*10000, 65535),
> mod(c1+c2*10+c3*100+c4*1000+c5*10000, 32767),
> (c1+c2*10+c3*100+c4*1000+c5*10000) + 549755813888,
> cast(c1+c2*10+c3*100+c4*1000+c5*10000 as numeric(11,3)),
> cast(c1+c2*10+c3*100+c4*1000+c5*10000 as float),
> cast(c1+c2*10+c3*100+c4*1000+c5*10000 as decimal(11,2)),
> cast(c1+c2*10+c3*100+c4*1000+c5*10000 as real),
> cast(c1+c2*10+c3*100+c4*1000+c5*10000 as double precision),
> cast(converttimestamp(210614299200000000 +
> (86400000000 * (c1+c2*10+c3*100+c4*1000+c5*10000))) as date),
> time'00:00:00' + cast(mod(c1+c2*10+c3*100+c4*1000+c5*10000,3)
> as interval minute),
> converttimestamp(210614299200000000 + (86400000000 *
> (c1+c2*10+c3*100+c4*1000+c5*10000)) + (1000000 * (c1+c2*10+c3*100)) +
> (60000000 * (c1+c2*10)) + (3600000000 * (c1+c2*10))),
> cast(c1+c2*10+c3*100+c4*1000+c5*10000 as char(90) character set iso88591),
> cast(c1+c2*10+c3*100+c4*1000+c5*10000 as char(111) character set ucs2),
> cast(c1+c2*10+c3*100+c4*1000+c5*10000 as varchar(113) character set iso88591),
> cast(c1+c2*10+c3*100+c4*1000+c5*10000 as varchar(115) character set ucs2)
> from (values(1)) t
> transpose 0,1,2,3,4,5,6,7,8,9 as c1
> transpose 0,1,2,3,4,5,6,7,8,9 as c2
> transpose 0,1,2,3,4,5,6,7,8,9 as c3
> transpose 0,1,2,3,4,5,6,7,8,9 as c4
> transpose 0,1,2,3,4,5,6,7,8,9 as c5;
> update statistics for table salttbl3 ON EVERY KEY SAMPLE RANDOM 20 PERCENT;
> prepare XX from select count(*) from salttbl3 where "_SALT_" = 9;
> explain XX;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)