| -- Test: TEST002 (Executor) |
| -- @@@ 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: Union all with joins and aggregates, various joins, |
| -- subqueries with aggregates, correlated subqueries. |
| -- Expected files: EXPECTED002, EXPECTED002.MP |
| -- Table created: t002t1, t002t2, t002t3, t002temp, t002tmp2, t002t5, |
| -- t002t6, t002t7, t002t8, t002t9, t002t10, t002tab2, |
| -- t002vt, t002ZZ, t002ZZI, t002FU, t002FUI, |
| -- t002ut1, t002ut2, t002ut3, t002utemp, t002utmp2 |
| -- View Created: t002v |
| -- Limitations: |
| -- To Do: - Table t002vt is not created in ifMX case, but is to be dropped. |
| -- - Remove DIFF002.KNOWN.NSK when Unicode is supported on MX tables |
| -- while Unicode may never be supported on MP tables |
| -- (Done 5/19/02) |
| -- Revision history: |
| -- (1/28/02) - Copied from fullstack/TEST002 |
| -- (2/18/02) - Renamed tables ZZ, ZZI, FU, FUI to t002ZZ, t002ZZI, |
| -- t002FU, t002FUI |
| -- (2/19/02) - Merged in fullstack/TEST002U |
| -- (2/20/02) - Moved most subquery tests to TEST002 (Core) |
| -- (3/04/02) - Comment off Unicode tests for MP tables |
| -- (5/19/02) - Removed DIFF002.KNOWN.NSK as unicode is supported. |
| control query default POS 'OFF'; |
| control query default ATTEMPT_ESP_PARALLELISM 'OFF'; |
| |
| ?section ddl |
| |
| obey TEST002(clnup); |
| |
| log LOG002 clear; |
| |
| create table t002t1 (a int, b char(9), c int, d char(4)); |
| create table t002t2 (a int not null, b char(9), c int, d char(4), primary key (a)); |
| create table t002t3 (a int not null, b char(9) not null, c int, d char(4), primary key (a, b)); |
| create table t002temp (a int, b char(9), c int, d char(4)); |
| create table t002tmp2 (a int); |
| |
| create table t002t5(a int not null, b int); |
| create table t002t6(c int not null, d int, primary key(c)); |
| create table t002t7 ( col1 int ); |
| create table t002t8 ( col2 int ); |
| create table t002t9 ( col3 int ); |
| create table t002t10 ( col4 int ); |
| create table t002tab2 (char_1 CHAR(1), |
| numeric_1 NUMERIC(4, 0)); |
| |
| -- added for JIRA TRAFODION-2843 |
| Create table D03s |
| ( |
| pk int not null not droppable primary key |
| , val01 int |
| , val02 int |
| ); |
| |
| Create table F01s |
| ( |
| pk int not null not droppable primary key |
| , fk_d01 int not null |
| , fk_d02 int not null |
| , fk_d03 int not null |
| , fk_d04 int not null |
| , fk_d05 int not null |
| , fk_d06 int not null |
| , fk_d07 int not null |
| , fk_d08 int not null |
| , fk_d09 int not null |
| , fk_d10 int not null |
| , val01 int |
| , val02 int |
| , val01_d01 int |
| , val02_d01 int |
| , val01_d02 int |
| , val02_d02 int |
| , val01_d03 int |
| , val02_d03 int |
| ) salt using 8 partitions; |
| |
| ?section Genesis_10_970911_6859 |
| ?ifMX |
| create view t002v(w,x) as values(1,11); -- should work |
| ?ifMX |
| ?ifMP |
| create table t002vt (w smallint not null, x smallint not null); |
| insert into t002vt values (1,11); |
| create view t002v(w,x) as select * from t002vt; |
| ?ifMP |
| table t002v; -- should work |
| update t002v set w=0; -- better fail (error 4028)! |
| |
| #ifMX |
| create table t002ut1 (a int, b nchar(9), c int, d nchar(4)); |
| create table t002ut2 (a int not null, b nchar(9), c int, d nchar(4), primary key (a)); |
| create table t002ut3 (a int not null, b nchar(9) not null, c int, d nchar(4), primary key (a, b)); |
| create table t002utemp (a int, b nchar(9), c int, d nchar(4)); |
| create table t002utmp2 (a int); |
| #ifMX |
| |
| -- To test solution 10-090107-8249 |
| create table t002_oja(a int NOT NULL, PRIMARY key (a)); |
| create table t002_ojb(b int NOT NULL, PRIMARY key (b)); |
| create table t002_ojc(c int NOT NULL, PRIMARY key (c)); |
| create table t002_ojd(e int NOT NULL, PRIMARY key (e)); |
| create view t002_vex(x) as |
| select * from t002_ojd where e = 1 ; |
| create view t002_valb(p,q) as |
| select a,b from t002_oja left join t002_ojb on a = b ; |
| create view t002_valbic(p,q) as |
| select c,V.q from t002_valb V inner join t002_ojc on c = V.p ; |
| |
| create table t002_oj1 (a int) ; |
| create table t002_oj2 (b int) ; |
| |
| -- For Genesis Soln 10-100706-1612 |
| create table t002_outer_nlj (i int not null, j int not null); |
| |
| create table t002_outer_lower_hj like t002_outer_nlj; |
| |
| create table t002_inner_lower_hj like t002_outer_nlj; |
| |
| create table t002_inner_upper_hj like t002_outer_nlj; |
| -- the table t002_inner_upper_hj should be empty to cause the upper HJ |
| -- to issue a cancel to its left HJ child |
| |
| |
| ?section dml |
| -- INSERT queries |
| insert into t002t1 values (10, 'abc', 20, 'xy'); |
| insert into t002t1(b,d,a,c) values ('defg', 'wx', 10+10, 30); |
| insert into t002t2 select * from t002t1; |
| insert into t002t3(a,b,c,d) select a,b,c,d from t002t2; |
| insert into t002t5 values (1, null), (2,0); |
| insert into t002t6 values (-1,2), (3,4), (5,6), (7, null); |
| insert into t002t7 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); |
| insert into t002t8 values(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21); |
| insert into t002t9 values(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31); |
| insert into t002t10 values(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41); |
| insert into t002tab2 values('A', 1000), ('B',2000); |
| |
| #ifMX |
| insert into t002ut1 values (10, N'abc', 20, N'xy'); |
| insert into t002ut1(b,d,a,c) values (N'defg', N'wx', 10+10, 30); |
| insert into t002ut2 select * from t002ut1; |
| insert into t002ut3(a,b,c,d) select a,b,c,d from t002ut2; |
| #ifMX |
| |
| -- UNION ALL |
| select * from t002t1 union all select * from t002t1; |
| |
| select * from t002t1 union all |
| select * from t002t1 union all |
| select * from t002t2; |
| |
| select a+1 from t002t1 union all select a from t002t1; |
| |
| select * from (select * from t002t1 union all select * from t002t1) x; |
| |
| select * from (values (1,2), (3,4)) x; |
| |
| #ifMX |
| select * from t002ut1 union all select * from t002ut1; |
| |
| select * from t002ut1 union all |
| select * from t002ut1 union all |
| select * from t002ut2; |
| |
| select * from (select * from t002ut1 union all select * from t002ut1) x; |
| #ifMX |
| |
| -- INSERT with multiple tuples |
| insert into t002t3 values (30, 'abc', 20, 'xy'), (30+10, 'defg', 30, 'wx'); |
| select * from t002t3; |
| |
| select * from t002t1 union all |
| select * from t002t2 union all |
| select * from t002t3; |
| |
| select * from t002t1 union all (select * from t002t2 union all select * from t002t3); |
| |
| #ifMX |
| insert into t002ut3 values (30, N'abc', 20, N'xy'), (30+10, N'defg', 30, N'wx'); |
| select * from t002ut3; |
| |
| select * from t002ut1 union all |
| select * from t002ut2 union all |
| select * from t002ut3; |
| |
| select * from t002ut1 union all (select * from t002ut2 union all select * from t002ut3); |
| #ifMX |
| |
| -- INSERT with UNION |
| delete from t002temp; |
| insert into t002temp |
| select * from t002t1 union all |
| select * from t002t2 union all |
| select * from t002t3; |
| select * from t002temp; |
| |
| delete from t002tmp2; |
| insert into t002tmp2 |
| select count(*) from |
| (select * from t002t1 join t002t2 on t002t1.a = t002t2.a |
| union all |
| select * from t002t1 join t002t2 on t002t1.a = t002t2.a) x; |
| select * from t002tmp2; |
| |
| #ifMX |
| delete from t002utemp; |
| insert into t002utemp |
| select * from t002ut1 union all |
| select * from t002ut2 union all |
| select * from t002ut3; |
| select * from t002utemp; |
| |
| delete from t002utmp2; |
| insert into t002utmp2 |
| select count(*) from |
| (select * from t002ut1 join t002ut2 on t002ut1.a = t002ut2.a |
| union all |
| select * from t002ut1 join t002ut2 on t002ut1.a = t002ut2.a) x; |
| select * from t002utmp2; |
| #ifMX |
| |
| -- AGGREGATEs with UNION |
| select count(*) from |
| (select * from t002t1 union all |
| select * from t002t2 union all |
| select * from t002t3) x; |
| |
| select count(*) from t002t1 union all select count(*) from t002t2; |
| |
| select count(*) from (select count(*) from t002t1 union all select count(*) from t002t2) x; |
| |
| select count(*) from t002t1 union all |
| select count(*) from (select count(*) from t002t1 union all select count(*) from t002t2) x; |
| |
| #ifMX |
| select count(*) from |
| (select * from t002ut1 union all |
| select * from t002ut2 union all |
| select * from t002ut3) x; |
| |
| select count(*) from t002ut1 union all select count(*) from t002ut2; |
| |
| select count(*) from (select count(*) from t002ut1 union all select count(*) from t002ut2) x; |
| |
| select count(*) from t002ut1 union all |
| select count(*) from (select count(*) from t002ut1 union all select count(*) from t002ut2) x; |
| #ifMX |
| |
| -- JOINs and UNIONs |
| select * from t002t1 join t002t2 on t002t1.a = t002t2.a |
| union all |
| select * from t002t1 join t002t2 on t002t1.a = t002t2.a; |
| |
| -- All together now |
| select count(*) from |
| (select * from t002t1 join t002t2 on t002t1.a = t002t2.a |
| union all |
| select * from t002t1 join t002t2 on t002t1.a = t002t2.a) x; |
| |
| select 1 + count(*) from |
| (select count(*) from (select * from t002t1) x natural join (select * from t002t2) y |
| union all |
| select count(*) from t002t1 join t002t2 on t002t1.a = t002t2.a) z; |
| |
| delete from t002temp; |
| |
| |
| select * from t002temp; |
| |
| #ifMX |
| select 1 + count(*) from |
| (select count(*) from (select * from t002ut1) x natural join (select * from t002ut2) y |
| union all |
| select count(*) from t002ut1 join t002ut2 on t002ut1.a = t002ut2.a) z; |
| |
| delete from t002utemp; |
| |
| |
| select * from t002utemp; |
| #ifMX |
| |
| |
| -- UNION |
| select a from t002t1 union select a from t002t1; |
| select a from t002t1 union select a from t002t1 union select a from t002t1; |
| select a from t002t1 union select a from t002t1 union select a from t002t1 |
| union select a from t002t1; |
| |
| -- UNION and UNION ALL which returns a relatively large number of rows. |
| select * from t002t7 |
| UNION |
| ((select * |
| from t002t8 |
| UNION all |
| Select * |
| from t002t9 |
| ) |
| UNION all |
| select * |
| from t002t10) |
| order by 1; |
| |
| |
| -- Join a null column (b) with a null (d) or not null column (c). |
| select * from t002t6 where c is null; |
| select * from t002t5 left join t002t6 on b < c; |
| select * from t002t5 inner join t002t6 on b < c; |
| select * from t002t5 left join t002t6 on c < b; |
| select * from t002t5 inner join t002t6 on c < b; |
| |
| -- Same join's but null instantiation works differently. |
| select * from t002t5 left join t002t6 on b > c; |
| select * from t002t5 inner join t002t6 on b > c; |
| select * from t002t5 left join t002t6 on c > b; |
| select * from t002t5 inner join t002t6 on c > b; |
| |
| select * from t002t5 left join t002t6 on b+1 <= d; |
| select * from t002t5 inner join t002t6 on b+1 <= d; |
| |
| select * from |
| (select * from (select * from (values (1)) x)y |
| natural join |
| (select * from (values (1)) xx)yy) z; |
| |
| select * from (values (1), (2)) x natural join (select a from t002t1) y; |
| |
| select a,sum(b) from (select * from (values (1), (2)) x |
| natural join (select a from t002t1)z) y(a,b) group by a; |
| |
| -- CR 10-010308-1591 |
| select char_1, numeric_1 |
| from t002tab2 |
| group by char_1, numeric_1 |
| having (char_1, numeric_1) in ( values ('A', 1000), ('F', 3000), (char_1, |
| 2000)); |
| |
| -- Error 15001 expected: |
| select /*this is here to return a syntax error*/ sum(a),sum(b) from t002t1; |
| |
| -- Error 15001 expected: |
| select a,sum(b) from |
| ( |
| (select * from |
| (select * from |
| (select a,sum(a) from t002t1 group by a) x(a,b) |
| union |
| select * from (select a,sum(a) from t002t1 group by a) x(a,b) |
| ) w(a,b) |
| join |
| (select a,sum(a) from t002t1 group by a) y(a,b) |
| ) |
| on w.a = y.a |
| ) z(a,b,c,d) |
| group by a having a > 1 and sum(b) = 20; |
| |
| -- Legal syntax, expect 1 row (20,20): |
| select a,sum(b) from |
| ( |
| (select * from |
| (select * from |
| (select a,sum(a) from t002t1 group by a) x(a,b) |
| union |
| select * from (select a,sum(a) from t002t1 group by a) x(a,b) |
| ) w(a,b) |
| join |
| (select a,sum(a) from t002t1 group by a) y(a,b) |
| on w.a = y.a |
| ) |
| ) z(a,b,c,d) |
| group by a having a > 1 and sum(b) = 20; |
| |
| -- Column B not found |
| select a,sum(b) from |
| (select a,b from |
| (select a,sum(a) from t002t1 group by a) x natural join |
| (select a,sum(a) from t002t1 group by a) y) z(a,b,c,d) |
| group by a; |
| |
| -- Number of columns mismatch |
| select a,sum(b) from |
| (select * from |
| (select a,sum(a) from t002t1 group by a) x natural join |
| (select a,sum(a) from t002t1 group by a) y) z(a,b,c,d) |
| group by a; |
| |
| -- This should work |
| select a,sum(b) from |
| (select * from |
| (select a,sum(a) from t002t1 group by a) x natural join |
| (select a,sum(a) from t002t1 group by a) y) z(a,b,c) |
| group by a; |
| |
| -- This should work |
| #ifMX |
| select a,sum(b) from |
| (select * from |
| (select a,sum(a) from t002ut1 group by a) x natural join |
| (select a,sum(a) from t002ut1 group by a) y) z(a,b,c) |
| group by a; |
| #ifMX |
| |
| |
| -- This should work because x.* expands to unnamed column sum(a) |
| select x.* from |
| (select a,sum(a) from t002t1 group by a) x natural join |
| (select a,sum(a) from t002t1 group by a) y; |
| |
| -- This should NOT work because x.* expands to zero columns |
| select x.* from |
| (select a from t002t1 group by a) x natural join |
| (select a from t002t1 group by a) y; |
| |
| select *, sum(a) from t002t1; -- err 4012 |
| select sum(a), * from t002t1; -- err 4012 |
| select *, sum(a) from t002t1 having 1=1; -- err 4012 |
| select *, sum(a) from t002t1 group by b; -- err 4012 |
| select *, sum(a) from t002t1 group by b,c,a,d; -- ok |
| select sum(a) from t002t1; -- ok |
| select sum(a) from t002t1 having 1=1; -- ok |
| select sum(a) from t002t1 group by b; -- ok |
| -- |
| -- |
| |
| -- The select list contains a non-grouping non-aggregated column. |
| SELECT A,MAX(A) FROM T002T1; |
| -- Ok |
| SELECT A,MAX(A) FROM T002T1 GROUP BY A; |
| -- The column ref A must be a grouping column or be in an aggregate. |
| SELECT A,MAX(A) FROM T002T1 GROUP BY B; |
| -- |
| -- |
| |
| -- The select list contains a non-grouping non-aggregated column. |
| SELECT A,COUNT(*) FROM T002T1; |
| -- Ok |
| SELECT A,COUNT(*) FROM T002T1 GROUP BY A; |
| -- The column ref A must be a grouping column or be in an aggregate. |
| SELECT A,COUNT(*) FROM T002T1 GROUP BY B; |
| -- |
| -- |
| |
| -- The select list contains a non-grouping non-aggregated column. |
| SELECT (SELECT A FROM T002T2 WHERE OUTT.A > A AND MAX(OUTT.B)>'C') FROM T002T1 OUTT; |
| -- The column ref OUTT.A must be a grouping column or be in an aggregate. |
| SELECT (SELECT A FROM T002T2 WHERE OUTT.A > A AND MAX(OUTT.B)>'C') FROM T002T1 OUTT |
| GROUP BY OUTT.B; |
| -- |
| -- |
| |
| -- Soln: 10-040609-6809 -- A MapValueId node on right of a merge join where |
| -- MVI introduces Cast for one of the Join Preds. |
| |
| #ifdef SEABASE_REGRESS |
| control query shape |
| merge_join(cut,sort(sort_groupby(cut))); |
| #else |
| control query shape |
| merge_join(cut, |
| mvi(sort(sort_groupby(pa(groupby(cut)))))); |
| #endif |
| |
| |
| select * |
| from t002t3 t1, |
| (select count(*) from t002t3 group by a) t2(a) |
| where t1.a = t2.a; |
| |
| control query shape cut; |
| |
| -- Soln: 10-040317-4281 -- A MapValueId node on right of a merge join where |
| -- one of the projected values is a count that has been replaced with Cast(1). |
| |
| control query shape merge_join(cut,cut); |
| |
| select cnt |
| from (select a from t002t2) t1, |
| (select a, count(*) as cnt from t002t2 group by a) t2 |
| where t1.a = t2.a |
| ; |
| |
| control query shape cut; |
| |
| -- Three queries to test solution 10-090107-8249 |
| -- all have left outer join that returns nothing from the inner |
| insert into t002_oja values(2); |
| insert into t002_ojb values(2); |
| insert into t002_ojc values(2); |
| insert into t002_ojd values(1); |
| |
| control query shape hybrid_hash_join(hybrid_hash_join(hybrid_hash_join(cut, |
| cut),cut),cut); |
| |
| select * from t002_valbic X left join t002_vex Y on X.p = Y.x ; |
| |
| control query shape cut; |
| |
| -- |
| insert into t002_oj1 values (1); |
| insert into t002_oj2 values (2); |
| |
| control query shape hybrid_hash_join(cut,cut); |
| |
| select * from t002_oj1 |
| left join t002_oj2 on b = 2; |
| |
| control query shape cut; |
| |
| -- |
| delete from t002_oj1; |
| delete from t002_oj2; |
| insert into t002_oj1 values (2) ; |
| insert into t002_oj2 select * from t002_oj1 where a < 3 ; |
| |
| control query shape hybrid_hash_join(cut, |
| sort_groupby(hybrid_hash_join(cut,cut))); |
| |
| select b, |
| ( select a from t002_oj1 |
| right outer join t002_oj2 |
| on a = b |
| where b = 2 |
| ) from t002_oj2 ; |
| |
| -- also try w/o pcode |
| control query default PCODE_OPT_LEVEL 'OFF'; |
| |
| select b, |
| ( select a from t002_oj1 |
| right outer join t002_oj2 |
| on a = b |
| where b = 2 |
| ) from t002_oj2 ; |
| |
| control query default PCODE_OPT_LEVEL reset; |
| |
| control query shape cut; |
| |
| -- Test Genesis Case 10-090127-0322 |
| -- Cancel Hash Join during Phase1, Reuse on, DelayedLeftRequest. |
| |
| control query default HJ_TYPE 'ORDERED'; |
| control query shape nested_join(cut,cut); |
| |
| -- Used to hang |
| SELECT T1.a FROM t002t1 AS t1 |
| WHERE EXISTS ( |
| SELECT -74 FROM t002t1 t2 |
| UNION all |
| SELECT -84 FROM t002t2 t2 |
| WHERE NOT ( ( -51 = T1.c ) |
| OR ( EXISTS (SELECT 1 FROM t002t2 T4, t002t3 t5 |
| ) ) ) |
| GROUP BY T2.b |
| ); |
| |
| -- Used to hang |
| SELECT T1.a FROM t002t1 AS t1 |
| WHERE EXISTS ( |
| SELECT -74 FROM t002t1 t2 where t1.c = 30 |
| UNION all |
| SELECT -84 FROM t002t2 t2 |
| WHERE NOT ( ( -51 = T1.c ) |
| OR ( EXISTS (SELECT 1 FROM t002t2 T4, t002t3 t5 |
| ) ) ) |
| GROUP BY T2.b |
| ); |
| |
| control query shape cut; |
| insert into t002t1 values(30, 'cdef', 40, 'yy'); |
| insert into t002t1 values(40, 'dst', 50, 'zz'); |
| control query shape nested_join(cut,cut); |
| |
| -- Used to hang |
| SELECT T1.a FROM t002t1 AS t1 |
| WHERE EXISTS ( |
| SELECT -74 FROM t002t1 t2 where t1.c = 40 |
| UNION all |
| SELECT -84 FROM t002t2 t2 |
| WHERE NOT ( ( -51 = T1.c ) |
| OR ( EXISTS (SELECT 1 FROM t002t2 T4, t002t3 t5 |
| ) ) ) |
| GROUP BY T2.b |
| ); |
| |
| control query default HJ_TYPE reset; |
| control query shape cut; |
| delete from t002t1 where a >= 30; |
| |
| -- END Test Genesis Case 10-090127-0322 |
| |
| |
| -- Test Genesis Soln 10-100706-1612 |
| -- Cancel of Hash-Join during phase1, with reuse, causes a hang |
| |
| -- repeat same values |
| insert into t002_outer_nlj values (1,1),(1,1),(1,1),(1,1),(1,1); |
| |
| insert into t002_outer_lower_hj values (2,2),(2,2),(2,2),(2,2); -- just values |
| |
| -- t002_inner_upper_hj should be empty to cause the upper HJ to issue a cancel to its left child |
| |
| -- Make t002_inner_lower_hj big (to keep the lower HJ busy in phase 1, till |
| -- cancel comes). Insert 100,000 rows into t002_inner_lower_hj |
| #ifdef SEABASE_REGRESS |
| upsert using load into |
| #else |
| insert into |
| #endif |
| t002_inner_lower_hj |
| select x1 + x2*10 + x3*100 + x4*1000 + x5*10000, |
| case when x5 < 5 then 333 else 444 end |
| from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) T(x5) |
| transpose 0,1,2,3,4,5,6,7,8,9 as x4 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x3 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x2 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1 |
| ; |
| |
| cqd HJ_TYPE 'ORDERED'; |
| cqd NESTED_JOIN_CACHE 'OFF'; |
| cqd JOIN_ORDER_BY_USER 'ON'; |
| |
| control query shape nested_join(cut,ordered_hash_join( |
| ordered_hash_join(cut,cut), cut)); |
| |
| -- used to hang |
| select * from t002_outer_nlj where t002_outer_nlj.i in |
| (select t002_inner_lower_hj.i from |
| t002_outer_lower_hj,t002_inner_lower_hj,t002_inner_upper_hj |
| where t002_outer_lower_hj.i=t002_inner_lower_hj.i |
| and t002_inner_lower_hj.j=t002_inner_upper_hj.j); |
| |
| control query shape cut; |
| cqd HJ_TYPE reset; |
| cqd NESTED_JOIN_CACHE reset; |
| cqd JOIN_ORDER_BY_USER reset; |
| |
| -- END Test Genesis Soln 10-100706-1612 |
| |
| |
| -- NULL Instantiation |
| -- Instantiate a large (> 4K) row. |
| -- See Genesis Solution: 10-090922-4766 |
| |
| insert into t002t6 values (10,20), (20,30); |
| |
| control query shape hhj(cut,cut); |
| select t002t6.c, character_length(T.b), T.a+10 |
| from t002t6 |
| left outer join ( |
| select b||b||b as b,a from ( |
| select b||b||b||b as b,a from ( |
| select b||b||b||b as b,a from ( |
| select b||b||b||b as b,a from ( |
| select b||b||b||b as b,a from ( |
| select b||b||b||b as b,a as a from t002t2 |
| ) T) T) T) T) T) T |
| on (t002t6.c = T.a) |
| ; |
| control query shape nested_join(cut,cut); |
| select t002t6.c, character_length(T.b), T.a+10 |
| from t002t6 |
| left outer join ( |
| select b||b||b as b,a from ( |
| select b||b||b||b as b,a from ( |
| select b||b||b||b as b,a from ( |
| select b||b||b||b as b,a from ( |
| select b||b||b||b as b,a from ( |
| select b||b||b||b as b,a as a from t002t2 |
| ) T) T) T) T) T) T |
| on (t002t6.c = T.a) |
| ; |
| |
| control query shape merge_join(cut,cut); |
| cqd PCODE_OPT_LEVEL '0'; |
| select t002t6.c, character_length(T.b), T.a+10 |
| from t002t6 |
| left outer join ( |
| select b||b||b as b,a from ( |
| select b||b||b||b as b,a from ( |
| select b||b||b||b as b,a from ( |
| select b||b||b||b as b,a from ( |
| select b||b||b||b as b,a from ( |
| select b||b||b||b as b,a as a from t002t2 |
| ) T) T) T) T) T) T |
| on (t002t6.c = T.a) |
| ; |
| cqd PCODE_OPT_LEVEL reset; |
| control query shape cut; |
| |
| -- Test Outer Join with many (all) rows are null instanitated. |
| -- Test for Hash, Nested and Merge |
| control query shape groupby(hhj(cut,cut)); |
| select sum(t.x1), count(t002t2.b), sum(t002t2.c) |
| from (select x1 |
| from (values(0)) T |
| transpose 0,1,2,3,4,5,6,7,8,9 as x5 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x4 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x3 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x2 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1) T |
| left outer join t002t2 |
| on (t.x1 = t002t2.a); |
| |
| select count(*), count(t.x1), sum(t.x1), count(t002t2.b), sum(t002t2.c) |
| from (select x1 |
| from (values(0)) T |
| transpose 0,1,2,3,4,5,6,7,8,9 as x5 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x4 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x3 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x2 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1) T |
| full outer join t002t2 |
| on (t.x1 = t002t2.a); |
| |
| control query shape groupby(nested_join(cut,cut)); |
| select sum(t.x1), count(t002t2.b), sum(t002t2.c) |
| from (select x1 |
| from (values(0)) T |
| transpose 0,1,2,3,4,5,6,7,8,9 as x5 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x4 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x3 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x2 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1) T |
| left outer join t002t2 |
| on (t.x1 = t002t2.a); |
| |
| control query shape groupby(merge_join(cut,cut)); |
| select sum(t.x1), count(t002t2.b), sum(t002t2.c) |
| from (select x1 |
| from (values(0)) T |
| transpose 0,1,2,3,4,5,6,7,8,9 as x5 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x4 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x3 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x2 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1) T |
| left outer join t002t2 |
| on (t.x1 = t002t2.a); |
| |
| -- Test Outer Join with many (most) rows are null instanitated. |
| -- Test for Hash, Nested and Merge |
| |
| control query shape groupby(hhj(cut,cut)); |
| select sum(t.x1), count(t002t2.b), sum(t002t2.c) |
| from (select x1 |
| from (values(0)) T |
| transpose 0,1,2,3,4,5,6,7,8,9 as x5 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x4 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x3 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x2 |
| transpose 10,1,20,3,4,5,6,7,8,9 as x1) T |
| left outer join t002t2 |
| on (t.x1 = t002t2.a); |
| |
| control query shape groupby(nested_join(cut,cut)); |
| select sum(t.x1), count(t002t2.b), sum(t002t2.c) |
| from (select x1 |
| from (values(0)) T |
| transpose 0,1,2,3,4,5,6,7,8,9 as x5 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x4 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x3 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x2 |
| transpose 10,1,20,3,4,5,6,7,8,9 as x1) T |
| left outer join t002t2 |
| on (t.x1 = t002t2.a); |
| |
| control query shape groupby(merge_join(cut,cut)); |
| select sum(t.x1), count(t002t2.b), sum(t002t2.c) |
| from (select x1 |
| from (values(0)) T |
| transpose 0,1,2,3,4,5,6,7,8,9 as x5 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x4 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x3 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x2 |
| transpose 10,1,20,3,4,5,6,7,8,9 as x1) T |
| left outer join t002t2 |
| on (t.x1 = t002t2.a); |
| |
| control query shape cut; |
| |
| |
| -- Test for multi outer joins. |
| -- Populate tables with regular data. |
| -- |
| insert into t002_oja |
| select x1 + x2*10 + x3*100 + 10 |
| from (values(0)) T |
| transpose 0,1,2,3,4,5,6,7,8,9 as x3 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x2 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1; |
| |
| insert into t002_ojb |
| select (x1 + x2*10 + x3*100 + 10) * 2 |
| from (values(0)) T |
| transpose 0,1,2,3,4,5,6,7,8,9 as x3 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x2 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1; |
| |
| insert into t002_ojc |
| select (x1 + x2*10 + x3*100 + 10) * 3 |
| from (values(0)) T |
| transpose 0,1,2,3,4,5,6,7,8,9 as x3 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x2 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1; |
| |
| insert into t002_ojd |
| select (x1 + x2*10 + x3*100 + 10) * 4 |
| from (values(0)) T |
| transpose 0,1,2,3,4,5,6,7,8,9 as x3 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x2 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1; |
| |
| -- Test with Hash, Nested and Merge. |
| control query default hash_joins reset; |
| control query default nested_joins 'off'; |
| control query default merge_joins 'off'; |
| select * from t002_oja A |
| left join t002_ojb B |
| on A.a = B.b |
| left join t002_ojc C |
| on (A.a = C.c) |
| left join t002_ojd D |
| on A.a = D.e ; |
| |
| -- With an AfterJoinPred |
| select * from t002_oja A |
| left join t002_ojb B |
| on A.a = B.b |
| left join t002_ojc C |
| on (A.a = C.c) |
| left join t002_ojd D |
| on A.a = D.e |
| where A.a < 100 and B.b is null and C.c is null and D.e is null; |
| |
| -- Full Outer, With an AfterJoinPred |
| select * from (select * from t002_oja where a < 100) A |
| full outer join (select * from t002_ojb where b < 100) B |
| on A.a = B.b |
| full outer join (select * from t002_ojc where c < 100) C |
| on (A.a = C.c) |
| full outer join (select * from t002_ojd where e < 100) D |
| on A.a = D.e |
| where A.a is null or (B.b is null and C.c is null and D.e is null); |
| |
| -- FOJ with beforepred, at least one outer row w/o a match |
| select count(*) from ( |
| select T1.* |
| from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1 |
| full outer join |
| (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2 |
| on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < T1.c) |
| ) C |
| ; |
| |
| -- FOJ with afterjoinpred, at least one outer row w/o a match |
| select count(*) from ( |
| select T1.* |
| from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1 |
| full outer join |
| (select *, cast('Hello' as char(512)) b from t002_oja) T2 |
| on (T1.a-10 = T2.a and T1.b = T2.b) |
| where T1.c < 5) C |
| ; |
| |
| -- FOJ with afterjoinpred and beforepred, at least one outer row w/o a match |
| select count(*) from ( |
| select T1.* |
| from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1 |
| full outer join |
| (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2 |
| on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < 10) |
| where T1.c < 5) C |
| ; |
| |
| -- FOJ with constant projected from outer side. |
| Select T1.a, T1.c |
| from ( |
| Select 79 c, a |
| from t002_oja |
| where a < 20 |
| ) T1 |
| full join ( |
| Select * |
| from t002_oja |
| where a < 20 |
| ) T2 |
| on (T1.a = T2.a) |
| ; |
| |
| -- A simple FOJ with empty outer clusters |
| Select * |
| from ( |
| Select a - 3 a |
| from t002_oja |
| where a < 20 and a > 9 |
| ) T1 |
| full join ( |
| Select a - 10 a |
| from t002_oja |
| where a < 20 and a > 9 |
| ) T2 |
| on (T1.a = T2.a) |
| ; |
| |
| -- LOJ with beforepred, at least one outer row w/o a match |
| select count(T2.c) |
| from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1 |
| left outer join |
| (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2 |
| on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < T1.c) |
| ; |
| |
| -- LOJ with afterjoinpred, at least one outer row w/o a match |
| select count(*) |
| from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1 |
| left outer join |
| (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2 |
| on (T1.a-10 = T2.a and T1.b = T2.b) |
| where T2.c < 5 or T2.c is null |
| ; |
| |
| -- LOJ with afterjoinpred and beforepred, at least one outer row w/o a match |
| select count(*) |
| from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1 |
| left outer join |
| (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2 |
| on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < T1.c) |
| where T2.c < 5 or T2.c is null |
| ; |
| |
| -- Test Hash with forced overflow. |
| control query default EXE_TEST_HASH_FORCE_OVERFLOW_EVERY '2'; |
| select * from t002_oja A |
| left join t002_ojb B |
| on A.a = B.b |
| left join t002_ojc C |
| on (A.a = C.c) |
| left join t002_ojd D |
| on A.a = D.e ; |
| |
| -- With an AfterJoinPred |
| select * from t002_oja A |
| left join t002_ojb B |
| on A.a = B.b |
| left join t002_ojc C |
| on (A.a = C.c) |
| left join t002_ojd D |
| on A.a = D.e |
| where A.a < 100 and B.b is null and C.c is null and D.e is null; |
| |
| -- Full Outer, With an AfterJoinPred |
| select * from (select * from t002_oja where a < 100) A |
| full outer join (select * from t002_ojb where b < 100) B |
| on A.a = B.b |
| full outer join (select * from t002_ojc where c < 100) C |
| on (A.a = C.c) |
| full outer join (select * from t002_ojd where e < 100) D |
| on A.a = D.e |
| where A.a is null or (B.b is null and C.c is null and D.e is null); |
| |
| -- FOJ with beforepred, at least one outer row w/o a match and forced overflow. |
| select count(*) from ( |
| select T1.* |
| from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1 |
| full outer join |
| (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2 |
| on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < T1.c) |
| ) C |
| ; |
| |
| -- FOJ with afterjoinpred, at least one outer row w/o a match and forced overflow. |
| select count(*) from ( |
| select T1.* |
| from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1 |
| full outer join |
| (select *, cast('Hello' as char(512)) b from t002_oja) T2 |
| on (T1.a-10 = T2.a and T1.b = T2.b) |
| where T1.c < 5) C |
| ; |
| |
| -- FOJ with afterjoinpred and beforepred, at least one outer row w/o a match and forced overflow. |
| select count(*) from ( |
| select T1.* |
| from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1 |
| full outer join |
| (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2 |
| on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < 10) |
| where T1.c < 5) C |
| ; |
| |
| -- FOJ with constant projected from outer side. |
| Select T1.a, T1.c |
| from ( |
| Select 79 c, a |
| from t002_oja |
| where a < 20 |
| ) T1 |
| full join ( |
| Select * |
| from t002_oja |
| where a < 20 |
| ) T2 |
| on (T1.a = T2.a) |
| ; |
| |
| -- A simple FOJ with empty outer clusters |
| Select * |
| from ( |
| Select a - 3 a |
| from t002_oja |
| where a < 20 and a > 9 |
| ) T1 |
| full join ( |
| Select a - 10 a |
| from t002_oja |
| where a < 20 and a > 9 |
| ) T2 |
| on (T1.a = T2.a) |
| ; |
| |
| -- LOJ with beforepred, at least one outer row w/o a match and forced overflow. |
| select count(T2.c) |
| from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1 |
| left outer join |
| (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2 |
| on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < T1.c) |
| ; |
| |
| -- LOJ with afterjoinpred, at least one outer row w/o a match and forced overflow. |
| select count(*) |
| from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1 |
| left outer join |
| (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2 |
| on (T1.a-10 = T2.a and T1.b = T2.b) |
| where T2.c < 5 or T2.c is null |
| ; |
| |
| -- LOJ with afterjoinpred and beforepred, at least one outer row w/o a match and forced overflow. |
| select count(*) |
| from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1 |
| left outer join |
| (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2 |
| on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < T1.c) |
| where T2.c < 5 or T2.c is null |
| ; |
| |
| control query default EXE_TEST_HASH_FORCE_OVERFLOW_EVERY reset; |
| |
| |
| control query default hash_joins 'off'; |
| control query default nested_joins reset; |
| control query default merge_joins 'off'; |
| -- Does not find the nested_join plan with default opt level. |
| control query default optimization_level '5'; |
| select * from t002_oja A |
| left join t002_ojb B |
| on A.a = B.b |
| left join t002_ojc C |
| on (A.a = C.c) |
| left join t002_ojd D |
| on A.a = D.e ; |
| |
| -- With an AfterJoinPred |
| select * from t002_oja A |
| left join t002_ojb B |
| on A.a = B.b |
| left join t002_ojc C |
| on (A.a = C.c) |
| left join t002_ojd D |
| on A.a = D.e |
| where A.a < 100 and B.b is null and C.c is null and D.e is null; |
| control query default optimization_level reset; |
| |
| control query default hash_joins 'off'; |
| control query default nested_joins 'off'; |
| control query default merge_joins reset; |
| select * from t002_oja A |
| left join t002_ojb B |
| on A.a = B.b |
| left join t002_ojc C |
| on (A.a = C.c) |
| left join t002_ojd D |
| on A.a = D.e ; |
| |
| -- With an AfterJoinPred |
| select * from t002_oja A |
| left join t002_ojb B |
| on A.a = B.b |
| left join t002_ojc C |
| on (A.a = C.c) |
| left join t002_ojd D |
| on A.a = D.e |
| where A.a < 100 and B.b is null and C.c is null and D.e is null; |
| |
| control query default hash_joins reset; |
| control query default nested_joins reset; |
| control query default merge_joins reset; |
| |
| drop table if exists student; |
| create table student( student_name char(10), test_score int ); |
| insert into student values('s1', 88); |
| insert into student values('s1', 79); |
| insert into student values('s2', 88); |
| insert into student values('s3', 90); |
| insert into student values('s1', 88); |
| insert into student values('s1', 60); |
| insert into student values('s1', 100); |
| insert into student values('s1', 50); |
| insert into student values('s1', 101); |
| insert into student values('s1', 40); |
| insert into student values('s3', 40); |
| insert into student values('s3', 40); |
| insert into student values('s3', 100); |
| insert into student values('s2', 88); |
| insert into student values('s2', 188); |
| insert into student values('s2', 18); |
| insert into student values('s2', 58); |
| insert into student values('学生三',79); |
| |
| SELECT student_name, |
| GROUP_CONCAT(DISTINCT test_score |
| ORDER BY test_score SEPARATOR '-') |
| FROM student |
| GROUP BY student_name order by student_name; |
| |
| SELECT student_name, |
| GROUP_CONCAT(test_score |
| ORDER BY test_score SEPARATOR '-') |
| FROM student |
| GROUP BY student_name order by student_name; |
| |
| SELECT GROUP_CONCAT(student_name) from student; |
| |
| cqd ATTEMPT_ESP_PARALLELISM 'ON'; |
| control query shape exchange(cut); |
| prepare s1 from SELECT student_name, |
| GROUP_CONCAT(DISTINCT test_score |
| ORDER BY test_score desc SEPARATOR '-') |
| FROM student |
| GROUP BY student_name order by student_name; |
| explain options 'f' s1; |
| execute s1; |
| control query shape cut; |
| cqd ATTEMPT_ESP_PARALLELISM 'OFF'; |
| drop table student; |
| drop table if exists regexp_test; |
| create table regexp_test (c1 char(32) ); |
| insert into regexp_test values( '123' ); |
| insert into regexp_test values( '123a' ); |
| insert into regexp_test values( 'english' ); |
| insert into regexp_test values( 'dev@trafodion.org' ); |
| insert into regexp_test values( '127.0.0.1' ); |
| insert into regexp_test values( '127.0.0.300' ); |
| insert into regexp_test values('中文测试'); |
| |
| -- only number |
| select * from regexp_test where c1 regexp '^[0-9]*\s*$'; |
| select * from regexp_test where c1 regexp '^[[:digit:]]*\s*$'; |
| -- only english |
| select * from regexp_test where c1 regexp '^.[A-Za-z]+\s*$'; |
| -- valid email address |
| select * from regexp_test where c1 regexp '\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*\s*'; |
| -- valid ip address |
| select * from regexp_test where c1 regexp '^(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9])\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9]|0)\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9]|0)\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[0-9])\s*$'; |
| -- utf-8 code |
| select * from regexp_test where c1 regexp '(中文测试)'; |
| select * from regexp_test where c1 regexp '[^\'; |
| drop table regexp_test; |
| |
| --create table have 1K rows |
| create table T002T1K (uniq int not null, |
| c1K int, c100 int, |
| c10 int, c1 int, c0 int ) |
| STORE BY (uniq) |
| ATTRIBUTES ALIGNED FORMAT |
| SALT USING 8 PARTITIONS |
| ; |
| |
| upsert using load into T002T1K select |
| 0 + (1000 * x10) + (100 * x1) + (10 * x1) + (1 * x01), |
| 0 + (100 * x10) + (10 * x1) + (1 * x01), |
| 0 + (10 * x1) + (1 * x01), |
| 0 + (1 * x01), |
| 0, |
| X01 |
| from (values(0)) t |
| transpose 0,1,2,3,4,5,6,7,8,9 as x10 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1 |
| transpose 0,1,2,3,4,5,6,7,8,9 as X01; |
| |
| create table t002timert (c0 int, c1 int, c2 largeint); |
| create table t002tmp1 (c1 int); |
| insert into t002tmp1 values(1),(2),(3); |
| |
| insert into t002timert select 1, sleep(5) , unix_timestamp() from t002tmp1; |
| insert into t002timert select 2, sleep(5) , unix_timestamp() from t002tmp1; |
| select 'sleeptimetest002', di from (select ( max(c2) - min(c2)) as di from t002timert ) where di between 5 and 9; |
| select distinct(count(uuid())) from T002T1K; |
| select distinct(count(sys_guid())) from T002T1K; |
| drop table t002timert; |
| drop table t002tmp1; |
| ------------------------------------------------------------------------ |
| -- added for JIRA TRAFODION-2843 |
| |
| insert into D03s |
| select c1+c2*10+c3*100, c1, c1+c2*10 |
| from (values(1)) T |
| transpose 0,1 as c1 |
| transpose 0,1 as c2 |
| transpose 0,1 as c3; |
| |
| insert with no rollback into F01s |
| select c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 |
| ,c1 |
| ,c1+c2*10 |
| ,c1+c2*10+c3*100 |
| ,c1 |
| ,c1+c2*10 |
| ,c1+c2*10+c3*100 |
| ,c1 |
| ,c1+c2*10 |
| ,c1+c2*10+c3*100 |
| ,c1 |
| ,c1+c2*10 |
| ,mod(c1+c2*100+c3*100,200) |
| ,mod(c1,3) |
| ,mod(c1,6) |
| ,mod(c1+c2*10,5) |
| ,c1 |
| ,c1 |
| ,c1+c2*10 |
| from (values(1)) T |
| transpose 0,1 as c1 |
| transpose 0,1 as c2 |
| transpose 0,1 as c3 |
| transpose 0,1 as c4 |
| transpose 0 as c5 |
| transpose 0 as c6 |
| ; |
| |
| prepare x1 from |
| select F01s.val01, TD03.val01 |
| From F01s |
| full outer join |
| (select D03s.val01,count(D03s.pk) |
| from D03s |
| group by D03s.val01) as TD03(val01,pk) |
| on (TD03.pk=F01s.fk_d03 |
| AND TD03.pk>0 ); |
| |
| execute x1; |
| |
| prepare x1v from |
| select F01s.val01, F01s.fk_d03, TD03.pk, TD03.val01 |
| From F01s |
| full outer join |
| (select D03s.val01,count(D03s.pk) |
| from D03s |
| group by D03s.val01) as TD03(val01,pk) |
| on (TD03.pk=F01s.fk_d03); |
| |
| execute x1v; |
| |
| obey TEST002(BR0198_MULTI); |
| obey TEST002(BR0198_EMPTY); |
| |
| log; |
| obey TEST002(clnup); |
| exit; |
| |
| |
| ?section clnup |
| -- CLEANUP database |
| drop table t002t1; |
| drop table t002t2; |
| drop table t002t3; |
| drop table t002temp; |
| drop table t002tmp2; |
| drop view t002v; |
| drop table t002vt; |
| drop table t002t5; |
| drop table t002t6; |
| drop table t002t7; |
| drop table t002t8; |
| drop table t002t9; |
| drop table t002t10; |
| drop table t002tab2; |
| drop table t002t1k; |
| -- * drop table t002ZZ; |
| -- * drop table t002ZZI; |
| -- * drop table t002FU; |
| -- * drop table t002FUI; |
| |
| #ifMX |
| drop table t002ut1; |
| drop table t002ut2; |
| drop table t002ut3; |
| drop table t002utemp; |
| drop table t002utmp2; |
| #ifMX |
| |
| -- To test solution 10-090107-8249 |
| drop view t002_valbic; |
| drop view t002_valb; |
| drop view t002_vex; |
| |
| drop table t002_oja; |
| drop table t002_ojb; |
| drop table t002_ojc; |
| drop table t002_ojd; |
| |
| drop table t002_oj1 ; |
| drop table t002_oj2 ; |
| |
| -- For Genesis Soln 10-100706-1612 |
| drop table t002_outer_nlj ; |
| drop table t002_outer_lower_hj ; |
| drop table t002_inner_lower_hj ; |
| drop table t002_inner_upper_hj ; |
| |
| drop table F01s; |
| drop table D03s; |
| |
| ?section clnup_end |
| |
| |
| -------------------------------- |
| ?section BR0198_MULTI |
| select count(*) from T002T1; -- must be >1 for this test to be valid |
| obey TEST002(BR0198_BASIC); |
| -------------------------------- |
| |
| -------------------------------- |
| ?section BR0198_EMPTY |
| delete from T002T1; |
| select count(*) from T002T2; -- must be >1 for one case below to be valid |
| obey TEST002(BR0198_BASIC); |
| -------------------------------- |
| |
| ?section BR0198_BASIC |
| |
| SELECT 54 FROM T002T1 T |
| HAVING ( EXISTS ( |
| SELECT STDDEV ( T.A ) FROM T002T1 )); -- 0 rows EMPTY, 1 MULTI |
| |
| SELECT 54 FROM T002T1 T |
| HAVING ( EXISTS ( |
| SELECT SUM ( T.A ) FROM T002T1 )); -- 0 rows EMPTY, 1 MULTI |
| |
| SELECT 54 FROM T002T1 T |
| HAVING ( EXISTS ( |
| SELECT COUNT ( T.A ) FROM T002T1 )); -- 0 rows EMPTY, 1 MULTI |
| |
| SELECT 54 FROM T002T1 T |
| HAVING ( EXISTS ( |
| SELECT STDDEV ( T002T1.A ) FROM T002T1 )); -- 0 rows EMPTY, 1 MULTI |
| |
| SELECT 54 FROM T002T1 T |
| HAVING ( EXISTS ( |
| SELECT SUM ( T002T1.A ) FROM T002T1 )); -- 0 rows EMPTY, 1 MULTI |
| |
| SELECT 54 FROM T002T2 T |
| HAVING ( EXISTS ( |
| SELECT SUM ( T002T1.A ) FROM T002T1 )); -- 1 row ALWAYS |
| |
| SELECT 54 FROM (values(1)) T |
| HAVING ( EXISTS ( |
| SELECT SUM ( T002T1.A ) FROM T002T1 )); -- 1 row ALWAYS |
| |
| SELECT 54 FROM (values(1)) T |
| HAVING ( EXISTS ( |
| SELECT 27 FROM T002T1 )); -- 0 rows EMPTY, 1 MULTI |
| |
| SELECT foo FROM (values(54)) T(foo) |
| WHERE ( EXISTS ( |
| SELECT 27 FROM T002T1 )); -- 0 rows EMPTY, 1 MULTI |
| |
| SELECT 54 FROM (values(1)) T |
| WHERE |
| (SELECT 27 FROM (values(2))u)=27; -- 1 row ALWAYS |
| |
| SELECT 54 FROM (values(1)) TbadCorr |
| WHERE |
| (SELECT 27 FROM TbadCorr) = 27; -- ok: cat.sch.tbadCorr not found |
| |
| SELECT 54 FROM T002T2 T |
| WHERE ( EXISTS ( |
| SELECT 27 FROM T002T1 )); -- 0 rows EMPTY, >0 MULTI |
| |
| SELECT 54 FROM T002T1 T |
| HAVING (SELECT SUM ( T.A ) FROM T002T1 )>0; -- 0 rows EMPTY, err 8401 MULTI |
| |
| SELECT 54 FROM T002T1 T |
| HAVING (SELECT SUM ( T002T1.A ) FROM T002T1 )>0;-- 0 rows EMPTY, 1 MULTI |
| |
| SELECT 54 FROM T002T1 T |
| HAVING (SELECT A FROM T002T1 )>0; -- 0 rows EMPTY, err 8401 MULTI |
| |
| ?section BR0198_end |
| |