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.
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Trafodion |
In Progress
|
Medium
|
Howard Qin |
Bug 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>),
end_key ................ (_SALT_ = %(9)), (COLTS = <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*
+>(c1+c2*
+>mod(c1+
+>mod(c1+
+>(c1+c2*
+>cast(
+>cast(
+>cast(
+>cast(
+>cast(
+>cast(
+>(86400000000 * (c1+c2*
+>time'00:00:00' + cast(mod(
+>as interval minute),
+>converttimest
+>(c1+c2*
+>(60000000 * (c1+c2*10)) + (3600000000 * (c1+c2*10))),
+>cast(
+>cast(
+>cast(
+>cast(
+>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.
Task: CLEANUP Status: Started Object: TRAFODION.
Task: CLEANUP Status: Ended Object: TRAFODION.
Task: DISABLE INDEXE Status: Started Object: TRAFODION.
Task: DISABLE INDEXE Status: Ended Object: TRAFODION.
Task: PREPARATION Status: Started Object: TRAFODION.
Rows Processed: 100000
Task: PREPARATION Status: Ended ET: 00:00:10.332
Task: COMPLETION Status: Started Object: TRAFODION.
Task: COMPLETION Status: Ended ET: 00:00:02.941
Task: POPULATE INDEX Status: Started Object: TRAFODION.
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;
-------
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
-------
ROOT =======
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_
total_
xn_access_mode ......... read_only
xn_autoabort_
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 =======
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 =======
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.
object_type ............ Trafodion
cache_size ........... 100
probes ................. 1
rows_accessed ......... 10
key_columns ............ _SALT_, COLTS, COLVCHRUCS2, COLINTS
begin_key .............. (_SALT_ = %(9)), (COLTS = <min>),
end_key ................ (_SALT_ = %(9)), (COLTS = <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+
(c1+c2*
mod(c1+
mod(c1+
(c1+c2*
cast(c1+
cast(c1+
cast(c1+
cast(c1+
cast(c1+
cast(converttim
(86400000000 * (c1+c2*
time'00:00:00' + cast(mod(
as interval minute),
converttimestam
(c1+c2*
(60000000 * (c1+c2*10)) + (3600000000 * (c1+c2*10))),
cast(c1+
cast(c1+
cast(c1+
cast(c1+
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;
Changed in trafodion: | |
assignee: | nobody → Howard Qin (hao-qin) |
status: | New → In Progress |
Changed in trafodion: | |
milestone: | r1.1 → r2.0 |
tags: | added: sql-cmp |