[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.

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Fix Version/s: 2.4
• Component/s: sql-cmp
• External issue URL: https://bugs.launchpad.net/bugs/1430938
• Assignee: Suresh Subbiah
• Reporter: Julie Thai
• Created: 22/Jul/15 18:19
• Updated: 16/Apr/20 02:20

Description

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;