[
https://issues.apache.org/jira/browse/TRAFODION-879?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14721166#comment-14721166
]
Suresh Subbiah commented on TRAFODION-879:
------------------------------------------
Howard Qin (hao-qin) on 2015-01-13
Changed in trafodion:
assignee: nobody → Howard Qin (hao-qin)
Howard Qin (hao-qin) wrote on 2015-01-13: #1
The second query is not a match of the first one, according to their SQC Key,
but HQC consider so, this is the root cause for this bug and lp1408148
select '0123456789' from t;
"root kids(scan TRAFODION.MYTEST4.T redef:0) cExpr:% sla: il:10 am:1
ilu:4294967295 ac:0 fl:0 rm:2 ai:4294967295 mc:0"
select '' from t;
"root kids(scan TRAFODION.MYTEST4.T redef:0) cExpr:_ISO88591'' sla: il:10 am:1
ilu:4294967295 ac:0 fl:0 rm:2 ai:4294967295 mc:0"
select position('April spring time' IN 'April rain') from t;
"root kids(scan TRAFODION.MYTEST4.T redef:0) cExpr:(% position %) sla: il:10
am:1 ilu:4294967295 ac:0 fl:0 rm:2 ai:4294967295 mc:0"
select position('' IN 'April rain') from t;
"root kids(scan TRAFODION.MYTEST4.T redef:0) cExpr:(_ISO88591'' position %)
sla: il:10 am:1 ilu:4294967295 ac:0 fl:0 rm:2 ai:4294967295 mc:0"
John Messavussu (john-messavussu) on 2015-01-13
Changed in trafodion:
milestone: r1.0 → r1.1
Howard Qin (hao-qin) on 2015-01-14
Changed in trafodion:
status: New → In Progress
Julie Thai (julie-y-thai) wrote on 2015-02-19: #2
Verified on qinhao_hqc_utt_0124:
>>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.
Also verified on pcec_hqc_utt:
>>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.
>>exit;
Julie Thai (julie-y-thai) wrote on 2015-03-09: #3
verified on hqc_utt_0304:
MY_SQROOT=/opt/home/trafodion/hqc_utt_0304
who@host=trafodion@amethyst-hn1
JAVA_HOME=/usr/java/jdk1.7.0_67
linux=2.6.32-279.el6.x86_64
redhat=6.3
Release 1.1.0 (Build release [qinhao], branch 087e0e4-hqc-bug-fix, date 04Mar15)
from TRAFCI:
SQL>create schema mytest4;
--- SQL operation complete.
SQL>set schema mytest4;
--- SQL operation complete.
SQL>create table t (a int);
--- SQL operation complete.
SQL>insert into t values (1),(2);
--- 2 row(s) inserted.
SQL>SELECT POSITION('April spring time' IN 'April rain') from t;
(EXPR)
----------
0
0
--- 2 row(s) selected.
SQL>SELECT POSITION('' IN 'April rain') from t;
(EXPR)
----------
1
1
--- 2 row(s) selected.
SQL>drop schema mytest4 cascade;
--- SQL operation complete.
Weishiun Tsai (wei-shiun-tsai) wrote on 2015-03-24: #4
Verified on the v0324 build installed on a workstation. This problem has been
fixed:
>>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.
Changed in trafodion:
status: In Progress → Fix Released
> 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)
