trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dbirds...@apache.org
Subject [2/3] trafodion git commit: [TRAFODION 3047] Cannot get right result using prepare statement with dynamic parameters - add test case
Date Thu, 10 May 2018 19:41:18 GMT
[TRAFODION 3047] Cannot get right result using prepare statement with dynamic parameters -
add test case


Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/49c4af7f
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/49c4af7f
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/49c4af7f

Branch: refs/heads/master
Commit: 49c4af7f92a055aed647fc2918866fcf18862cba
Parents: db68b6f
Author: Andy Yang <yongfeng.yang@esgyn.cn>
Authored: Fri Apr 27 18:10:23 2018 +0800
Committer: Andy Yang <yongfeng.yang@esgyn.cn>
Committed: Fri Apr 27 18:10:23 2018 +0800

----------------------------------------------------------------------
 core/sql/regress/core/EXPECTED163       |  63 +++++++++++++++
 core/sql/regress/core/TEST163           | 115 +++++++++++++++++++++++++++
 core/sql/regress/tools/runregr_core.ksh |   2 +-
 3 files changed, 179 insertions(+), 1 deletion(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/trafodion/blob/49c4af7f/core/sql/regress/core/EXPECTED163
----------------------------------------------------------------------
diff --git a/core/sql/regress/core/EXPECTED163 b/core/sql/regress/core/EXPECTED163
new file mode 100644
index 0000000..26a91c8
--- /dev/null
+++ b/core/sql/regress/core/EXPECTED163
@@ -0,0 +1,63 @@
+>>obey TEST163(tests);
+>>--------------------------------------------------------------------------
+>>
+>>set param ?id '12345678';
+>>prepare s1 from 
++>SELECT  (
++>         SELECT 'Y'
++>         FROM dual
++>         WHERE EXISTS (
++>                       SELECT 1
++>                       FROM dic
++>                       WHERE area_id = dom.area_id
++>                      )
++>        ) impact_yn
++>      , (
++>         SELECT cd_nm
++>         FROM code
++>         WHERE lang_cd = 'zh_CN'
++>               AND up_cd_id = '6023'
++>               AND cd_id = dom.type_cd
++>        ) dic_gbn_nm
++>FROM dom
++>WHERE dom.area_id = ?id
++>      AND dom.type_cd = '0003';
+
+--- SQL command prepared.
+>>execute s1;
+
+IMPACT_YN  DIC_GBN_NM
+---------  ----------------------------------------------------------------------------------------------------
+
+Y          level code                                                                   
                      
+
+--- 1 row(s) selected.
+>>
+>>SELECT  (
++>         SELECT 'Y'
++>         FROM dual
++>         WHERE EXISTS (
++>                       SELECT 1
++>                       FROM dic
++>                       WHERE area_id = dom.area_id
++>                      )
++>        ) impact_yn
++>      , (
++>         SELECT cd_nm
++>         FROM code
++>         WHERE lang_cd = 'zh_CN'
++>               AND up_cd_id = '6023'
++>               AND cd_id = dom.type_cd
++>        ) dic_gbn_nm
++>FROM dom
++>WHERE dom.area_id = '12345678'
++>      AND dom.type_cd = '0003';
+
+IMPACT_YN  DIC_GBN_NM
+---------  ----------------------------------------------------------------------------------------------------
+
+Y          level code                                                                   
                      
+
+--- 1 row(s) selected.
+>>
+>>log;

http://git-wip-us.apache.org/repos/asf/trafodion/blob/49c4af7f/core/sql/regress/core/TEST163
----------------------------------------------------------------------
diff --git a/core/sql/regress/core/TEST163 b/core/sql/regress/core/TEST163
new file mode 100644
index 0000000..c40e2c0
--- /dev/null
+++ b/core/sql/regress/core/TEST163
@@ -0,0 +1,115 @@
+-- Tests for Nested Join Probe Cache
+-- Added April 2018
+--
+-- @@@ START COPYRIGHT @@@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements.  See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership.  The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License.  You may obtain a copy of the License at
+--
+--   http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied.  See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+-- @@@ END COPYRIGHT @@@
+
+obey TEST163(clean_up);
+obey TEST163(setup);
+log LOG163 clear;
+obey TEST163(tests);
+log;
+obey TEST163(clean_up);
+exit;
+
+?section setup
+--------------------------------------------------------------------------
+
+create table dom
+(
+  area_id varchar(50)
+, type_cd varchar(4)
+);
+
+create table dic
+(
+  area_id varchar(50)
+);
+
+create table code
+(
+  lang_cd  varchar(10)
+, up_cd_id varchar(50)
+, cd_id    varchar(50)
+, cd_nm    varchar(100)
+);
+
+insert into dom values('12345678', '0003');
+
+insert into dic values('12345678');
+
+insert into code values('zh_CN','6023', '0003', 'level code');
+
+?section tests
+--------------------------------------------------------------------------
+
+set param ?id '12345678';
+prepare s1 from 
+SELECT  (
+         SELECT 'Y'
+         FROM dual
+         WHERE EXISTS (
+                       SELECT 1
+                       FROM dic
+                       WHERE area_id = dom.area_id
+                      )
+        ) impact_yn
+      , (
+         SELECT cd_nm
+         FROM code
+         WHERE lang_cd = 'zh_CN'
+               AND up_cd_id = '6023'
+               AND cd_id = dom.type_cd
+        ) dic_gbn_nm
+FROM dom
+WHERE dom.area_id = ?id
+      AND dom.type_cd = '0003';
+execute s1;
+
+SELECT  (
+         SELECT 'Y'
+         FROM dual
+         WHERE EXISTS (
+                       SELECT 1
+                       FROM dic
+                       WHERE area_id = dom.area_id
+                      )
+        ) impact_yn
+      , (
+         SELECT cd_nm
+         FROM code
+         WHERE lang_cd = 'zh_CN'
+               AND up_cd_id = '6023'
+               AND cd_id = dom.type_cd
+        ) dic_gbn_nm
+FROM dom
+WHERE dom.area_id = '12345678'
+      AND dom.type_cd = '0003';
+
+?section clean_up
+--------------------------------------------------------------------------
+
+drop table if exists dom;
+
+drop table if exists dic;
+
+drop table if exists code;
+

http://git-wip-us.apache.org/repos/asf/trafodion/blob/49c4af7f/core/sql/regress/tools/runregr_core.ksh
----------------------------------------------------------------------
diff --git a/core/sql/regress/tools/runregr_core.ksh b/core/sql/regress/tools/runregr_core.ksh
index 1e9d0f5..a7a1ebd 100755
--- a/core/sql/regress/tools/runregr_core.ksh
+++ b/core/sql/regress/tools/runregr_core.ksh
@@ -221,7 +221,7 @@ fi
 
 # sbtestfiles contains the list of tests to be run in seabase mode
 if [ "$seabase" -ne 0 ]; then
-  sbtestfiles="TEST000 TEST001 TEST002 TEST004 TEST005 TEST008 TEST010 TEST018 TEST019 TEST020
TEST027 TEST029 TEST032 TEST037 TEST038 TEST041 TEST056 TEST061 TEST116 TEST131 TEST162 TESTRTS"
+  sbtestfiles="TEST000 TEST001 TEST002 TEST004 TEST005 TEST008 TEST010 TEST018 TEST019 TEST020
TEST027 TEST029 TEST032 TEST037 TEST038 TEST041 TEST056 TEST061 TEST116 TEST131 TEST162 TEST163
TESTRTS"
   sbprettyfiles=
   for i in $prettyfiles; do
     for j in $sbtestfiles; do


Mime
View raw message