[
https://issues.apache.org/jira/browse/TRAFODION-1221?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Subbiah reassigned TRAFODION-1221:
-----------------------------------------
Assignee: Suresh Subbiah (was: Howard Qin)
> LP Bug: 1450853 - Hybrid Query Cache: query with equals predicate on INTERVAL
> datatype should not have a non-parameterized literal.
> -----------------------------------------------------------------------------------------------------------------------------------
>
> Key: TRAFODION-1221
> URL: https://issues.apache.org/jira/browse/TRAFODION-1221
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: Julie Thai
> Assignee: Suresh Subbiah
> Priority: Critical
> Fix For: 2.3
>
>
> 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;
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
