Hybrid Query Cache: query with equals predicate on INTERVAL datatype should not have a non-parameterized literal.

Bug #1450853 reported by Julie Thai
6
This bug affects 1 person
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 HybridQueryCacheEntries virtual table. Non-parametrrized literal should be empty.

SQL>prepare XX from select * from F00INTVL where colintvl = interval '39998' day(6);
*** WARNING[6008] Statistics for column (COLKEY) from table TRAFODION.QUERYCACHE_HQC.F00INTVL were not available. As a result, the access path chosen might not be the best possible. [2015-04-30 13:31:48]
--- 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+c3*100+c4*1000+c5*10000+c6*100000, --colkey
cast(cast(mod(c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000,999999)
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(HybridQueryCacheEntries('USER', 'LOCAL'));
prepare XX from select * from F00INTVL where colintvl = interval '39998' day(6);
execute show_entries;

Tags: sql-cmp
Revision history for this message
Julie Thai (julie-y-thai) wrote :

Also, there appears to be an inconsistency between hkey and the parameterized literal.
Currently in HybridQueryCacheEntries virtual table:
Hkey = SELECT * FROM F00INTVL WHERE COLINTVL = INTERVAL #NP# DAY(#NP#);
Parameterized literal = INTERVAL ‘39998’ DAY(6)

Changed in trafodion:
milestone: none → r2.0
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers