From commits-return-7485-archive-asf-public=cust-asf.ponee.io@trafodion.apache.org Thu May 10 21:41:19 2018 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id 4020E180674 for ; Thu, 10 May 2018 21:41:18 +0200 (CEST) Received: (qmail 83045 invoked by uid 500); 10 May 2018 19:41:17 -0000 Mailing-List: contact commits-help@trafodion.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: commits@trafodion.apache.org Delivered-To: mailing list commits@trafodion.apache.org Received: (qmail 83015 invoked by uid 99); 10 May 2018 19:41:17 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 10 May 2018 19:41:17 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id 37689F678A; Thu, 10 May 2018 19:41:17 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: dbirdsall@apache.org To: commits@trafodion.apache.org Date: Thu, 10 May 2018 19:41:18 -0000 Message-Id: In-Reply-To: <03d2244592c7400295b76680c30a8945@git.apache.org> References: <03d2244592c7400295b76680c30a8945@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [2/3] trafodion git commit: [TRAFODION 3047] Cannot get right result using prepare statement with dynamic parameters - add test case [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 Authored: Fri Apr 27 18:10:23 2018 +0800 Committer: Andy Yang 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