Hybrid Query Cache: query with equals predicate on INTERVAL datatype should not have a non-parameterized literal.
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Trafodion |
New
|
High
|
Howard Qin |
Bug Description
For query with equal predicate on INTERVAL datatype, both parameterized and non-parameterized literals appear in HybridQueryCach
SQL>prepare XX from select * from F00INTVL where colintvl = interval '39998' day(6);
*** WARNING[6008] Statistics for column (COLKEY) from table TRAFODION.
--- SQL command prepared.
SQL>execute show_entries;
HKEY NUM_HITS NUM_PLITERALS (EXPR) NUM_NPLITERALS (EXPR)
-------
SELECT * FROM F00INTVL WHERE COLINTVL = INTERVAL #NP# DAY ( #NP# ) ; 0 1 INTERVAL '39998' DAY(6)
1 '39998'
--- 1 row(s) selected.
To reproduce:
create table F00INTVL(
colkey int not null primary key,
colintvl interval day(6));
load into F00INTVL select
c1+c2*10+
cast(cast(
as integer) as interval day(6)) --colintvl
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
transpose 0,1,2,3,4,5,6,7,8,9 as c6;
update statistics for table F00INTVL on colintvl;
prepare show_entries from select left(hkey,50), num_pliterals, left(pliterals,15), num_npliterals, left(npliterals,15) from table(HybridQue
prepare XX from select * from F00INTVL where colintvl = interval '39998' day(6);
execute show_entries;
Changed in trafodion: | |
milestone: | none → r2.0 |
Also, there appears to be an inconsistency between hkey and the parameterized literal. eEntries virtual table:
Currently in HybridQueryCach
Hkey = SELECT * FROM F00INTVL WHERE COLINTVL = INTERVAL #NP# DAY(#NP#);
Parameterized literal = INTERVAL ‘39998’ DAY(6)