[
https://issues.apache.org/jira/browse/TRAFODION-879?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Subbiah resolved TRAFODION-879.
--------------------------------------
Resolution: Fixed
> LP Bug: 1409863 - Hybrid Query Cache causes POSITON to return wrong results
> ---------------------------------------------------------------------------
>
> Key: TRAFODION-879
> URL: https://issues.apache.org/jira/browse/TRAFODION-879
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: Weishiun Tsai
> Assignee: Howard Qin
> Priority: Blocker
> Fix For: 1.1 (pre-incubation)
>
>
> There are still issues on how Hybrid Query Cache handles an empty string in
> the 2nd query when 2 queries are executed consecutively. As shown in the
> following example. The 2nd SELECT(‘’ in ‘April rain’) is supposed to return
> 1 (the SQL Reference Manual
> http://docs.trafodion.org/Trafodion_SQL_Reference_Manual_0.9.0.pdf P326 says
> “if the length of substring-expression is zero, Trafodion SQL returns 1.”)
> But it returns 0 instead. This problem no longer exists if only the 2nd
> query is executed by itself. This problem also does not exist with 2 queries
> executed together if the CQD HYBRID_QUERY_CACHE is turned off.
> This is seen on the v0110 daily build which includes the fix for
> https://bugs.launchpad.net/trafodion/+bug/1408148 ‘Select literal causes the
> next select empty string to return junk’. But that fix did not address all
> issues. Instead of keeping adding new scenarios to that case, this new one
> is created separately for a new reproducible scenario.
> ----------------------------------------------------
> Here is the entire script to reproduce this problem:
> create schema mytest4;
> set schema mytest4;
> create table t (a int);
> insert into t values (1),(2);
> SELECT POSITION('April spring time' IN 'April rain') from t;
> SELECT POSITION('' IN 'April rain') from t;
> drop schema mytest4 cascade;
> ----------------------------------------------------
> Here is the execution output when HYBRID_QUERY_CACHE is on (it’s on by
> default). The 2nd SELECT returns wrong results.
> >>create schema mytest4;
> --- SQL operation complete.
> >>set schema mytest4;
> --- SQL operation complete.
> >>create table t (a int);
> --- SQL operation complete.
> >>insert into t values (1),(2);
> --- 2 row(s) inserted.
> >>SELECT POSITION('April spring time' IN 'April rain') from t;
> (EXPR)
> ----------
> 0
> 0
> --- 2 row(s) selected.
> >>SELECT POSITION('' IN 'April rain') from t;
> (EXPR)
> ----------
> 0
> 0
> --- 2 row(s) selected.
> >>drop schema mytest4 cascade;
> --- SQL operation complete.
> ----------------------------------------------------
> Here is the execution output when HYBRID_QUERY_CACHE is on (it’s on by
> default) and the 1st SELECT is commented out. The 2nd SELECT by itself no
> longer returns wrong results:
> >>create schema mytest4;
> --- SQL operation complete.
> >>set schema mytest4;
> --- SQL operation complete.
> >>create table t (a int);
> --- SQL operation complete.
> >>insert into t values (1),(2);
> --- 2 row(s) inserted.
> >>-- SELECT POSITION('April spring time' IN 'April rain') from t;
> >>SELECT POSITION('' IN 'April rain') from t;
> (EXPR)
> ----------
> 1
> 1
> --- 2 row(s) selected.
> >>drop schema mytest4 cascade;
> --- SQL operation complete.
> ----------------------------------------------------
> Here is the execution output when HYBRID_QUERY_CACHE is turned off and both
> SELECTS are executed. The 2nd SELECT returns the correct results:
> >>cqd HYBRID_QUERY_CACHE 'off';
> --- SQL operation complete.
> >>create schema mytest4;
> --- SQL operation complete.
> >>set schema mytest4;
> --- SQL operation complete.
> >>create table t (a int);
> --- SQL operation complete.
> >>insert into t values (1),(2);
> --- 2 row(s) inserted.
> >>SELECT POSITION('April spring time' IN 'April rain') from t;
> (EXPR)
> ----------
> 0
> 0
> --- 2 row(s) selected.
> >>SELECT POSITION('' IN 'April rain') from t;
> (EXPR)
> ----------
> 1
> 1
> --- 2 row(s) selected.
> >>drop schema mytest4 cascade;
> --- SQL operation complete.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
