[TRAFODION-1221] LP Bug: 1450853 - Hybrid Query Cache: query with equals predicate on INTERVAL datatype should not have a non-parameterized literal.

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

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;