[
https://issues.apache.org/jira/browse/TRAFODION-1005?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14717296#comment-14717296
]
Suresh Subbiah commented on TRAFODION-1005:
-------------------------------------------
Julie Thai (julie-y-thai) wrote on 2015-02-12: #1
SQL script to reproduce issue. Edit (943 bytes, application/x-sql)
Howard Qin (hao-qin) on 2015-03-03
Changed in trafodion:
status: New → In Progress
Howard Qin (hao-qin) wrote on 2015-03-03: #2
SQC does the cache work before bind, and it strictly check NAType on constants,
while HQC check with tolerant, this causes inconsistency.
It is a option that HQC does not cache insert statement as SQC already did this
before bind.
Julie Thai (julie-y-thai) wrote on 2015-03-09: #3
Download full text (3.4 KiB)
verified fix on cluster with hqc_utt_0304:
[trafodion@n009 hqc_utt_0304]$ sqid
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>drop view p9pv;
*** ERROR[1389] Object TRAFODION.SEABASE.P9PV does not exist in Trafodion.
[2015-03-09 20:18:19]
SQL>drop view p9sv;
*** ERROR[1389] Object TRAFODION.SEABASE.P9SV does not exist in Trafodion.
[2015-03-09 20:18:19]
SQL>drop table p9tab;
*** ERROR[1389] Object TRAFODION.SEABASE.P9TAB does not exist in Trafodion.
[2015-03-09 20:18:19]
SQL>create table p9tab (
+>c1 float
+>, c2 float(22)
+>, c3 float(23)
+>, c4 real
+>, c5 double precision not null
+>, primary key (c5)
+>) no partition;
--- SQL operation complete.
SQL>create index p9indx on p9tab(c1);
--- SQL operation complete.
SQL>create view p9pv(c1,c2,c4,c5) as
+>select c1,c2,c4,c5 from p9tab
+>where c2 > 1.0e2 and c5 < 1.0e2;
--- SQL operation complete.
SQL>create view p9sv(c2,c3) as
+>select c2,c3 from p9tab
+>where c3 >= c4;
--- SQL operation complete.
SQL>insert into p9tab
+>values (1.0e1,1.0e2,1.0e3,1.0e4,1.0e5);
--- 1 row(s) inserted.
SQL>insert into p9tab
+>values (2.1e1,2.1e2,2.1e3,2.1e4,2.1e5);
--- 1 row(s) inserted.
SQL>insert into p9tab
+>values (5.5e1,5.5e2,5.5e3,5.5e4,5.5e5);
--- 1 row(s) inserted.
SQL>insert into p9tab
+>values (8.0e1,8.0e2,8.0e3,8.0e4,8.0e5);
--- 1 row(s) inserted.
SQL>insert into p9tab(c1,c2,c3,c4,c5)
+>values (2,333.333,0.4E5,400E-3,100);
--- 1 row(s) inserted.
SQL>insert into p9tab(c1,c2,c3,c4,c5)
+>values (101.0,1.02E2,103,1.4E1,1.5E1);
--- 1 row(s) inserted.
SQL>insert into p9tab(c1,c2,c3,c4,c5)
+>values (61,52,43,4.1234567,5.12345678901);
--- 1 row(s) inserted.
SQL>insert into p9tab (c5) values (0E0);
--- 1 row(s) inserted.
SQL>select * from p9tab;
C1 C2 C3 C4 C5
------------------------ ------------------------ ------------------------
--------------- ------------------------
NULL NULL NULL NULL 0.0
61.0 52.0 43.0 4.123457 5.12345678901
101.0 102.0 103.0 14.0 15.0
2.0 333.333 40000.0 0.4 100.0
10.0 100.0 1000.0 10000.0 100000.0
21.0 210.0 2100.0 21000.0 210000.0
55.0 550.0 5500.0 55000.0 550000.0
80.0 800.0 8000.0 80000.0 800000.0
--- 8 row(s) selected.
SQL>select c4 from p9tab where c1 = 61E0;
C4
---------------
4.123457
--- 1 row(s) selec...
Read more...
Julie Thai (julie-y-thai) wrote on 2015-03-26: #4
verified on cluster with 20150324_0830 build:
SQL>select * from p9tab;
C1 C2 C3 C4 C5
------------------------ ------------------------ ------------------------
--------------- ------------------------
NULL NULL NULL NULL 0.0
61.0 52.0 43.0 4.123457 5.12345678901
101.0 102.0 103.0 14.0 15.0
2.0 333.333 40000.0 0.4 100.0
10.0 100.0 1000.0 10000.0 100000.0
21.0 210.0 2100.0 21000.0 210000.0
55.0 550.0 5500.0 55000.0 550000.0
80.0 800.0 8000.0 80000.0 800000.0
--- 8 row(s) selected.
SQL>select c4 from p9tab where c1 = 61E0;
C4
---------------
4.123457
--- 1 row(s) selected.
Changed in trafodion:
status: In Progress → Fix Released
> LP Bug: 1421374 - Hybrid Query Cache: precision of real differs when HQC is
> ON and OFF.
> ---------------------------------------------------------------------------------------
>
> Key: TRAFODION-1005
> URL: https://issues.apache.org/jira/browse/TRAFODION-1005
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: Julie Thai
> Assignee: Howard Qin
> Priority: Blocker
> Fix For: 1.1 (pre-incubation)
>
>
> Failing testcase is arkcase/arkt0102/test003.
> In TRAF r1.0.0 (HQC off), the following query in trafci, returns:
> SQL>select * from p9tab;
> C1 C2 C3 C4
> C5
> ------------------------ ------------------------ ------------------------
> --------------- ------------------------
> NULL NULL NULL
> NULL 0.0
> 61.0 52.0 43.0
> 4.123457 5.12345678901
> 101.0 102.0 103.0
> 14.0 15.0
> 2.0 333.333 40000.0
> 0.4 100.0
> 10.0 100.0 1000.0
> 10000.0 100000.0
> 21.0 210.0 2100.0
> 21000.0 210000.0
> 55.0 550.0 5500.0
> 55000.0 550000.0
> 80.0 800.0 8000.0
> 80000.0 800000.0
> --- 8 row(s) selected.
> Yet with HQC=ON, this query’s result set diffs. The result set with HQC=ON is:
> SQL>select * from p9tab;
> C1 C2 C3 C4
> C5
> ------------------------ ------------------------ ------------------------
> --------------- ------------------------
> NULL NULL NULL
> NULL 0.0
> 61.0 52.0 43.0
> 4.1234565 5.12345678901
> 101.0 102.0 103.0
> 14.0 15.0
> 2.0 333.333 40000.0
> 0.4 100.0
> 10.0 100.0 1000.0
> 10000.0 100000.0
> 21.0 210.0 2100.0
> 21000.0 210000.0
> 55.0 550.0 5500.0
> 55000.0 550000.0
> 80.0 800.0 8000.0
> 80000.0 800000.0
> --- 8 row(s) selected.
> To reproduce, in trafci:
> 1. specify CQD HYBRID_QUERY_CACHE '[OFF|ON]';
> 2. obey repo.sql (see attached file)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
