core/sql/regress/core/TEST163

-- 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;