| -- Test: TEST020 (Core) |
| -- @@@ 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 @@@ |
| -- |
| -- Functionality: Test the Explain Function |
| -- Expected files: EXPECTED020, EXPECTED020.MP |
| -- Tables created: TAB1, TAB2, TAB3 |
| -- Limitations: |
| -- To do: - Test description field of EXPLAIN |
| -- Revision history: |
| -- (06/08/06) Added a few additional tests for problems found with |
| -- new explain syntax. |
| -- (10/05/02) Move test to core from fullstack2, without any change. |
| -- (02/22/99) Added CQS's and order by's to make the test insensitive |
| -- to plan changes |
| -- (05/28/97) Clean up. |
| |
| ?section ddb |
| drop table TAB1; |
| drop table TAB2; |
| drop table TAB3; |
| |
| ?section cdb |
| log LOG020 clear; |
| |
| create table TAB1 (col1 int, col2 int, col3 int); |
| create table TAB2 (col1 int, col2 int, col3 int); |
| create table TAB3 (col1 int, tname char(60), sname char(60)); |
| |
| insert into TAB1 values (1, 2, 3), |
| (2, 3, 4), |
| (3, 4, 5), |
| (4, 5, 6), |
| (5, 6, 7), |
| (6, 7, 8), |
| (7, 8, 9), |
| (8, 9, 0), |
| (9, 0, 1), |
| (0, 1, 2); |
| |
| insert into TAB2 values (1, 2, 3), |
| (4, 5, 6), |
| (7, 8, 9), |
| (0, 1, 2), |
| (3, 4, 5), |
| (6, 7, 8), |
| (9, 0, 1), |
| (2, 3, 4), |
| (5, 6, 7); |
| |
| insert into TAB3 values (1, 'TAB1', 'S1'), |
| (2, 'TAB2', 'S2'), |
| (3, 'TAB3', 'S3'); |
| |
| |
| ?section prepare_queries |
| |
| -- QUERYS to be explained |
| -- Every query to be explained has a CQS in order to make the |
| -- test insensitive to plan changes. |
| |
| #ifndef SEABASE_REGRESS |
| control query shape hybrid_hash_join(partition_access( |
| scan(path 'TAB1', forward, mdam off)),partition_access( |
| scan(path 'TAB2', forward, mdam off))); |
| #else |
| control query shape hybrid_hash_join( |
| scan(path 'TAB1', forward, mdam off), |
| scan(path 'TAB2', forward, mdam off)); |
| #endif |
| |
| prepare S1 from |
| select * |
| from TAB1 , TAB2 |
| where TAB1.col1 = TAB2.col1 |
| and TAB1.col1 < 15 |
| and TAB2.col2 < 20 |
| ; |
| |
| #ifndef SEABASE_REGRESS |
| control query shape hybrid_hash_join(partition_access( |
| scan(path 'TAB1', forward, mdam off)),partition_access( |
| scan(path 'TAB3', forward, mdam off))); |
| #else |
| control query shape hybrid_hash_join( |
| scan(path 'TAB1', forward, mdam off), |
| scan(path 'TAB3', forward, mdam off)); |
| #endif |
| |
| prepare S2 from |
| select * |
| from TAB1 , TAB3 |
| where TAB1.col1 = TAB3.col1 |
| and TAB1.col2 < 10 |
| and TAB3.col1 < 15 |
| and TAB3.tname is not null |
| and TAB3.sname is null |
| and (TAB1.col2 + TAB1.col3 < TAB3.col1 or char_length(TAB3.tname) > 10) |
| and substring(TAB3.tname from 1 for 3) <> 'ABC' |
| ; |
| |
| #ifndef SEABASE_REGRESS |
| control query shape hybrid_hash_join(partition_access( |
| scan(path 'TAB3', forward, mdam off)),partition_access( |
| scan(path 'TAB2', forward, mdam off))); |
| #else |
| control query shape hybrid_hash_join( |
| scan(path 'TAB3', forward, mdam off), |
| scan(path 'TAB2', forward, mdam off)); |
| #endif |
| |
| prepare S3 from |
| select * |
| from TAB2 , TAB3 |
| where TAB2.col1 = TAB3.col1 |
| and TAB2.col2 < 10 |
| and TAB3.col1 < 10 |
| ; |
| |
| #ifndef SEABASE_REGRESS |
| control query shape sort_groupby(nested_join(sort(partition_access( |
| scan(path 'TAB1', forward, mdam off))), |
| partition_access(scan(path 'TAB2', forward, mdam off)))); |
| #else |
| control query shape sort_groupby(nested_join(sort( |
| scan(path 'TAB1', forward, mdam off)), |
| scan(path 'TAB2', forward, mdam off))); |
| #endif |
| |
| prepare S4 from |
| select TAB1.col1, TAB1.col2, sum(TAB2.col2), count(*) |
| from TAB1 , TAB2 |
| where TAB1.col1 = TAB2.col1 |
| and TAB2.col2 < 30 |
| group by TAB1.col1, TAB1.col2 |
| order by TAB1.col2 |
| ; |
| |
| #ifndef SEABASE_REGRESS |
| control query shape partition_access(insert); |
| #else |
| control query shape insert; |
| #endif |
| |
| prepare S5 from |
| insert into TAB1 |
| values (42,42,42) |
| ; |
| |
| #ifndef SEABASE_REGRESS |
| control query shape nested_join(anything,partition_access(insert)); |
| #else |
| control query shape nested_join(anything,insert); |
| #endif |
| |
| prepare S6 from |
| insert into TAB1 |
| values (42,42,42), |
| (52,52,52), |
| (1,2,3) |
| ; |
| |
| #ifndef SEABASE_REGRESS |
| control query shape partition_access(update); |
| #else |
| control query shape update; |
| #endif |
| |
| prepare S7 from |
| update TAB1 |
| set col1 = 12, |
| col2 = col1 + col2, |
| col3 = 12 * 4 |
| where col1 < 10 |
| ; |
| |
| #ifndef SEABASE_REGRESS |
| control query shape hybrid_hash_join(partition_access( |
| scan(path 'TAB1', forward, mdam off)),partition_access( |
| scan(path 'TAB2', forward, mdam off))); |
| #else |
| control query shape hybrid_hash_join( |
| scan(path 'TAB1', forward, mdam off), |
| scan(path 'TAB2', forward, mdam off)); |
| #endif |
| |
| prepare S8 from |
| select * from TAB1 |
| where TAB1.col1 in (select col2 from TAB2 where TAB2.col1 < 10) |
| ; |
| |
| #ifndef SEABASE_REGRESS |
| control query shape hybrid_hash_join(partition_access( |
| scan(path 'TAB1', forward, mdam off)),partition_access( |
| scan(path 'TAB2', forward, mdam off))); |
| #else |
| control query shape hybrid_hash_join( |
| scan(path 'TAB1', forward, mdam off), |
| scan(path 'TAB2', forward, mdam off)); |
| #endif |
| |
| prepare S9 from |
| select * from TAB1 |
| where TAB1.col1 in (select col2 from TAB2 where TAB2.col1 < TAB1.col2) |
| ; |
| |
| -- remove shape: |
| control query shape cut; |
| |
| ?section explain_queries |
| |
| -- QUERY 1 - A simple Explain query with a predicate |
| select |
| #ifMX |
| tname, |
| #ifMX |
| #ifMP |
| substring(tname, 1, 4), |
| #ifMP |
| operator |
| from table (explain(NULL,'S1')) |
| where tname LIKE '%TAB1%' |
| order by |
| operator |
| ; |
| |
| ?ignore |
| -- this query returns different values for cost on different |
| -- platforms with different releases. |
| -- It is not a good query for regressions. |
| -- QUERY 2 - The cost fields of the explain table. |
| select |
| operator, |
| operator_cost, |
| total_cost, |
| detail_cost |
| from table (explain(NULL,'S1')) |
| order by |
| operator |
| ; |
| ?ignore |
| |
| -- QUERY 3 - The explain table is used in a JOIN. |
| select |
| operator, |
| expp.tname |
| from table (explain (NULL, 'S1')) as expp, TAB3 |
| where substring(expp.tname from position('TAB' in expp.tname) for 4) = TAB3.tname |
| order by |
| 1,2--operator |
| ; |
| |
| -- QUERY 4 - The explain table is used in a JOIN. |
| select |
| operator, |
| seq_num |
| from table (explain (NULL, 'S1')) as expp, TAB1 |
| where expp.seq_num = TAB1.col1 |
| order by |
| operator,seq_num |
| ; |
| |
| -- QUERY 5 - The explain function is used within a correlated |
| -- subquery. |
| select |
| * |
| from TAB3 |
| where TAB3.tname in |
| (select substring(tname from position('TAB' in tname) for 4) |
| from table (explain (NULL,TAB3.sname))) |
| order by tname |
| ; |
| |
| -- QUERY 6 - Wildcarding is used in the explain parameters. |
| select |
| module_name, |
| statement_name |
| from table (explain (NULL, 'S%')) |
| order by |
| module_name |
| ,statement_name |
| ; |
| |
| --QUERY 7 - Wildcarding is used in the explain parameters. |
| select |
| operator |
| from table (explain (NULL, 'S%')) as expp |
| where expp.left_child_seq_num is not null |
| and expp.right_child_seq_num is not null |
| order by operator |
| ; |
| |
| -- QUERY 8 |
| |
| select seq_num, |
| left_child_seq_num, |
| operator, |
| right_child_seq_num |
| from table (explain(NULL, 'S%')) |
| order by |
| seq_num |
| ,left_child_seq_num |
| ,operator |
| ,right_child_seq_num |
| ; |
| |
| --QUERY 9 |
| select |
| operator |
| from table (explain(NULL,'S1')) |
| order by |
| operator |
| ; |
| |
| --QUERY 10 |
| select |
| operator |
| from table (explain(NULL,'S2')) |
| order by |
| operator |
| ; |
| |
| --QUERY 11 |
| select |
| operator |
| from table (explain(NULL,'S3')) |
| order by |
| operator |
| ; |
| |
| --QUERY 12 |
| select |
| operator |
| from table (explain(NULL,'S4')) |
| order by |
| operator |
| ; |
| |
| --QUERY 13 |
| select |
| operator |
| from table (explain(NULL,'S5')) |
| order by |
| operator |
| ; |
| |
| --QUERY 14 |
| select |
| operator |
| from table (explain(NULL,'S6')) |
| order by |
| operator |
| ; |
| |
| --QUERY 15 |
| select |
| operator |
| from table (explain(NULL,'S7')) |
| order by |
| operator |
| ; |
| |
| --QUERY 16 |
| select |
| operator |
| from table (explain(NULL,'S8')) |
| order by |
| operator |
| ; |
| |
| --QUERY 17 |
| select |
| operator |
| from table (explain(NULL,'S9')) |
| order by |
| operator |
| ; |
| |
| --QUERY 18 |
| -- Tests description field |
| -- Currently commented out because query gives results that are not repeatable |
| -- (predicates printed contain "funny names" that change from run to run |
| -- in their columns, probably the way to fix it is to change the "getText()" |
| -- method of the column item expr. 05/97) |
| -- select seq_num, |
| -- operator, |
| -- description |
| -- from table (explain(NULL, 'S%')) |
| -- ; |
| |
| --QUERY 19 |
| -- Solution 10-060524-6738. Make sure "explain" with access options |
| -- work. |
| #ifMX |
| explain options 'f' select * from TAB1 where col1 < 15 |
| for read committed access; |
| #ifMX |
| |
| --QUERY 20 |
| -- Solution 10-060601-6896. This should cause a syntax error. |
| explain explain select * |
| from TAB1 , TAB2 |
| where TAB1.col1 = TAB2.col1 |
| and TAB1.col1 < 15 |
| and TAB2.col2 < 20 |
| ; |
| |
| --QUERY 21 |
| -- Solution 10-060606-7000. This should cause a syntax error. |
| explain; |
| |
| |
| obey TEST020(ddb); |
| |
| log; |
