| >> |
| >>create table t008t1(a1 int, c1 int, b1 char(6)); |
| |
| --- SQL operation complete. |
| >>create table t008t2(a2 int, c2 int, b2 char(6)); |
| |
| --- SQL operation complete. |
| >>create table t008t3(a3 int, c3 int, b3 char(6)); |
| |
| --- SQL operation complete. |
| >>create table t008t4(a4 int, c4 int, b4 char(6)); |
| |
| --- SQL operation complete. |
| >>create table t008t20(a20 int, c20 int, b20 char(6)); |
| |
| --- SQL operation complete. |
| >>create table t008t30(a30 int, c30 int, b30 char(6)); |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>?section dml |
| >>insert into t008t1 values(10,10, 't1r1'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t1 values(10,20, 't1r2'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t1 values(10,30, 't1r3'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t1 values(20,10, 't1r4'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t1 values(20,20, 't1r5'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t1 values(20,30, 't1r6'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t1 values(30,10, 't1r7'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t1 values(30,20, 't1r8'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t1 values(30,30, 't1r9'); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>insert into t008t2 values(10,10, 't2r1'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t2 values(10,20, 't2r2'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t2 values(20,10, 't2r3'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t2 values(20,20, 't2r4'); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>insert into t008t3 values(10,10, 't3r1'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t3 values(10,30, 't3r2'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t3 values(30,10, 't3r3'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t3 values(30,30, 't3r4'); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>insert into t008t4 values(20,20, 't4r1'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t4 values(20,30, 't4r2'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t4 values(30,20, 't4r3'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t4 values(30,30, 't4r4'); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>insert into t008t20 values(10,10, 't20r1'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t20 values(10,20, 't20r2'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t20 values(20,10, 't20r3'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t20 values(20,20, 't20r4'); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>insert into t008t30 values(10,10, 't30r1'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t30 values(10,30, 't30r2'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t30 values(30,10, 't30r3'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t008t30 values(30,30, 't30r4'); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>?section q0 |
| >>-- Check: "=" Predicate not lost when it contains an expression |
| >>select a2, c2 from t008t2 where c2 = 2 * a2; |
| |
| A2 C2 |
| ----------- ----------- |
| |
| 10 20 |
| |
| --- 1 row(s) selected. |
| >> |
| >>?section q1 |
| >>-- Special case: Equivalence class contains only one element. |
| >>select * from t008t2 where a2 = a2; |
| |
| A2 C2 B2 |
| ----------- ----------- ------ |
| |
| 10 10 t2r1 |
| 10 20 t2r2 |
| 20 10 t2r3 |
| 20 20 t2r4 |
| |
| --- 4 row(s) selected. |
| >>-- should get 4 rows |
| >> |
| >>?section q2 |
| >>-- Special case: Equivalence class contains only one element. |
| >>select count(*) from t008t2 where a2 = a2; |
| |
| (EXPR) |
| -------------------- |
| |
| 4 |
| |
| --- 1 row(s) selected. |
| >>-- should get 4 |
| >> |
| >>?section q3 |
| >>-- Should not see c2 < 20 rewritten as c2 < a2 in display |
| >>select * from t008t2 where a2 = 20 and c2 < 20; |
| |
| A2 C2 B2 |
| ----------- ----------- ------ |
| |
| 20 10 t2r3 |
| |
| --- 1 row(s) selected. |
| >>-- should get 1 row |
| >> |
| >>?section q4 |
| >>select count(*) from t008t2 where a2 = 20 and c2 < 20; |
| |
| (EXPR) |
| -------------------- |
| |
| 1 |
| |
| --- 1 row(s) selected. |
| >>-- should get 1 |
| >> |
| >>?section q5 |
| >>select a2,b2,b4 from t008t2 join t008t4 on a2 = 20 and a4 = a2; |
| |
| A2 B2 B4 |
| ----------- ------ ------ |
| |
| 20 t2r3 t4r1 |
| 20 t2r3 t4r2 |
| 20 t2r4 t4r1 |
| 20 t2r4 t4r2 |
| |
| --- 4 row(s) selected. |
| >>-- should get 4 rows |
| >> |
| >>?section q6 |
| >>select count(*) from t008t2 join t008t4 on a2 = 20 and a4 = a2; |
| |
| (EXPR) |
| -------------------- |
| |
| 4 |
| |
| --- 1 row(s) selected. |
| >>-- should get 4 |
| >> |
| >>?section q7 |
| >>select a2,b2,b4 from t008t2 join t008t4 on a2 = 20 and a4 = 20; |
| |
| A2 B2 B4 |
| ----------- ------ ------ |
| |
| 20 t2r3 t4r1 |
| 20 t2r3 t4r2 |
| 20 t2r4 t4r1 |
| 20 t2r4 t4r2 |
| |
| --- 4 row(s) selected. |
| >>-- should get 4 rows |
| >> |
| >>?section q8 |
| >>select count(*) from t008t2 join t008t4 on a2 = 20 and a4 = 20; |
| |
| (EXPR) |
| -------------------- |
| |
| 4 |
| |
| --- 1 row(s) selected. |
| >>-- should get 4 |
| >> |
| >>?section q9 |
| >>select a2,b2,b4 from t008t2 join t008t4 on a2 < 30 and a4 = a2; |
| |
| A2 B2 B4 |
| ----------- ------ ------ |
| |
| 20 t2r3 t4r1 |
| 20 t2r3 t4r2 |
| 20 t2r4 t4r1 |
| 20 t2r4 t4r2 |
| |
| --- 4 row(s) selected. |
| >>-- should get 4 rows |
| >> |
| >>?section q10 |
| >>select count(*) from t008t2 join t008t4 on a2 < 30 and a4 = a2; |
| |
| (EXPR) |
| -------------------- |
| |
| 4 |
| |
| --- 1 row(s) selected. |
| >>-- should get 4 |
| >> |
| >>?section q11 |
| >>-- A cross product between t008t2 and t4 |
| >>select a2,b2,b4 from t008t2 join t008t4 on a2 = a2; |
| |
| A2 B2 B4 |
| ----------- ------ ------ |
| |
| 10 t2r1 t4r1 |
| 10 t2r2 t4r1 |
| 20 t2r3 t4r1 |
| 20 t2r4 t4r1 |
| 10 t2r1 t4r2 |
| 10 t2r2 t4r2 |
| 20 t2r3 t4r2 |
| 20 t2r4 t4r2 |
| 10 t2r1 t4r3 |
| 10 t2r2 t4r3 |
| 20 t2r3 t4r3 |
| 20 t2r4 t4r3 |
| 10 t2r1 t4r4 |
| 10 t2r2 t4r4 |
| 20 t2r3 t4r4 |
| 20 t2r4 t4r4 |
| |
| --- 16 row(s) selected. |
| >>-- should get 16 rows { (10,t2r1,t4r*),(20,t2r4,t4r*) } |
| >> |
| >>?section q12 |
| >>select count(*) from t008t2 join t008t4 on a2 = a2; |
| |
| (EXPR) |
| -------------------- |
| |
| 16 |
| |
| --- 1 row(s) selected. |
| >>-- should get 16 |
| >> |
| >>?section q13 |
| >> select a1, b1, b2, b3 |
| +> from t008t1 join t008t2 on a1 = a2 and c2 = 20 |
| +> join t008t3 on a2 = a3 and c3 = 30 |
| +>; |
| |
| A1 B1 B2 B3 |
| ----------- ------ ------ ------ |
| |
| 10 t1r1 t2r2 t3r2 |
| 10 t1r3 t2r2 t3r2 |
| 10 t1r2 t2r2 t3r2 |
| |
| --- 3 row(s) selected. |
| >>-- should get 3 rows {(t1r1,t2r2,t3r2),(t1r2,t2r2,t3r2),(t1r3,t2r2,t3r2) } |
| >> |
| >>?section q14 |
| >> select count(*) |
| +> from t008t1 join t008t2 on a1 = a2 and c2 = 20 |
| +> join t008t3 on a2 = a3 and c3 = 30 |
| +>; |
| |
| (EXPR) |
| -------------------- |
| |
| 3 |
| |
| --- 1 row(s) selected. |
| >>-- should get 3 |
| >> |
| >>?section q15 |
| >> select a1, b1, b2, b3 |
| +> from t008t1 join t008t2 on a1 = a2 and c2 = 20 and a1 = c1 |
| +> join t008t3 on a2 = a3 and c3 = 30 |
| +>; |
| |
| A1 B1 B2 B3 |
| ----------- ------ ------ ------ |
| |
| 10 t1r1 t2r2 t3r2 |
| |
| --- 1 row(s) selected. |
| >>-- should get 1 row {(t1r1,t2r2,t3r2) } where a1 = c1 = 10 |
| >> |
| >>?section q16 |
| >> select count(*) |
| +> from t008t1 join t008t2 on a1 = a2 and c2 = 20 and a1 = c1 |
| +> join t008t3 on a2 = a3 and c3 = 30 |
| +>; |
| |
| (EXPR) |
| -------------------- |
| |
| 1 |
| |
| --- 1 row(s) selected. |
| >>-- should get 1 |
| >> |
| >>?section q17 |
| >> select a1, b1, b2, b3 |
| +> from t008t1 join t008t2 on a1 = a2 and a1 = c1 and a2 = c2 |
| +> join t008t3 on c2 = c3 and a2 = a3 |
| +>; |
| |
| A1 B1 B2 B3 |
| ----------- ------ ------ ------ |
| |
| 10 t1r1 t2r1 t3r1 |
| |
| --- 1 row(s) selected. |
| >>-- should get 1 row {(t1r1,t2r1,t3r1) } where a1 = c1 = 10 |
| >> |
| >>?section q18 |
| >> select count(*) |
| +> from t008t1 join t008t2 on a1 = a2 and a1 = c1 and a2 = c2 |
| +> join t008t3 on c2 = c3 and a2 = a3 |
| +>; |
| |
| (EXPR) |
| -------------------- |
| |
| 1 |
| |
| --- 1 row(s) selected. |
| >>-- should get 1 |
| >> |
| >>?section q19 |
| >> select X.xa2, X.xb2, X.xb3, Y.yc2, Y.yb2, Y.yb3 |
| +> from |
| +>(select a2, b2, b3 from t008t2 join t008t3 on a2 = a3) as X(xa2, xb2, xb3) |
| +>-- ({t2r1,t3r1},{t2r1,t3r2},{t2r2,t3r1},{t2r2,t3r2}) |
| +> join |
| +>(select c20, b20, b30 from t008t20 join t008t30 on c20 = c30) as Y(yc2, yb2, yb3) |
| +>-- ({t20r1,t30r1},{t20r1,t30r2},{t20r2,t30r1},{t20r2,t30r2}) |
| +> on xa2 = yc2 |
| +>; |
| |
| XA2 XB2 XB3 YC2 YB2 YB3 |
| ----------- ------ ------ ----------- ------ ------ |
| |
| 10 t2r1 t3r1 10 t20r1 t30r1 |
| 10 t2r1 t3r1 10 t20r1 t30r3 |
| 10 t2r1 t3r1 10 t20r3 t30r1 |
| 10 t2r1 t3r1 10 t20r3 t30r3 |
| 10 t2r1 t3r2 10 t20r1 t30r1 |
| 10 t2r1 t3r2 10 t20r1 t30r3 |
| 10 t2r1 t3r2 10 t20r3 t30r1 |
| 10 t2r1 t3r2 10 t20r3 t30r3 |
| 10 t2r2 t3r1 10 t20r1 t30r1 |
| 10 t2r2 t3r1 10 t20r1 t30r3 |
| 10 t2r2 t3r1 10 t20r3 t30r1 |
| 10 t2r2 t3r1 10 t20r3 t30r3 |
| 10 t2r2 t3r2 10 t20r1 t30r1 |
| 10 t2r2 t3r2 10 t20r1 t30r3 |
| 10 t2r2 t3r2 10 t20r3 t30r1 |
| 10 t2r2 t3r2 10 t20r3 t30r3 |
| |
| --- 16 row(s) selected. |
| >>-- should get 16 rows |
| >> |
| >>?section q20 |
| >> select count(*) |
| +> from |
| +>(select a2, b2, b3 from t008t2 join t008t3 on a2 = a3) as X(xa2, xb2, xb3) |
| +>-- ({t2r1,t3r1},{t2r1,t3r2},{t2r2,t3r1},{t2r2,t3r2}) |
| +> join |
| +>(select c20, b20, b30 from t008t20 join t008t30 on c20 = c30) as Y(yc2, yb2, yb3) |
| +>-- ({t20r1,t30r1},{t20r1,t30r2},{t20r2,t30r1},{t20r2,t30r2}) |
| +> on xa2 = yc2 |
| +>; |
| |
| (EXPR) |
| -------------------- |
| |
| 16 |
| |
| --- 1 row(s) selected. |
| >>-- should get 16 |
| >> |
| >>?section q21 |
| >>-- TBF: Syntax error |
| >>?ignore |
| >> select za2,zb2,zb3 |
| +> from |
| +>( (select a2, a3, b2, b3 from t008t2 join t008t3 on a2 = a3) as P(xa2, xa3, xb2, xb3) |
| +> union |
| +> (select c20, c30, b20, b30 from t008t20 join t008t30 on c20 = c30) as Q(yc2, yc3, yb2, yb3) |
| +>) as Z(za2, za3, zb2, zb3) |
| +>where za2 = za3 |
| +>; |
| >>?ignore |
| >> |
| >>?section q22 |
| >>-- TBF: Syntax error |
| >> select count(*) |
| +> from |
| +>( (select a2, a3, b2, b3 from t008t2 join t008t3 on a2 = a3) as P(xa2, xa3, xb2, xb3) |
| +> union |
| +> (select c20, c30, b20, b30 from t008t20 join t008t30 on c20 = c30) as Q(yc2, yc3, yb2, yb3) |
| +>) as Z(za2, za3, zb2, zb3) |
| +>where za2 = za3 |
| +>; |
| |
| *** ERROR[15001] A syntax error occurred at or before: |
| select count(*) from ( (select a2, a3, b2, b3 from t008t2 join t008t3 on a2 |
| = a3) as P(xa2, xa3, xb2, xb3) union (select c20, c30, b20, b30 from t008 |
| ^ (122 characters from start of SQL statement) |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>?section q23 |
| >>select b2,b4,c4,a4 |
| +> from t008t2 left join t008t4 on c4 = 20 |
| +> where a4 is not null |
| +>; |
| |
| B2 B4 C4 A4 |
| ------ ------ ----------- ----------- |
| |
| t2r1 t4r1 20 20 |
| t2r1 t4r3 20 30 |
| t2r2 t4r1 20 20 |
| t2r2 t4r3 20 30 |
| t2r3 t4r1 20 20 |
| t2r3 t4r3 20 30 |
| t2r4 t4r1 20 20 |
| t2r4 t4r3 20 30 |
| |
| --- 8 row(s) selected. |
| >>-- should get 8 rows |
| >>-- { (t2r1,t4r1),(t2r1,t4r3), ..., (t2r4,t4r1),(t2r4,t4r3) } |
| >> |
| >>?section q24 |
| >>select b2,b3,b4 |
| +> from t008t2 left join t008t3 on c2 = c3 |
| +> left join t008t4 on c2 = c4 |
| +>; |
| |
| B2 B3 B4 |
| ------ ------ ------ |
| |
| t2r1 t3r1 ? |
| t2r1 t3r3 ? |
| t2r2 ? t4r1 |
| t2r2 ? t4r3 |
| t2r3 t3r1 ? |
| t2r3 t3r3 ? |
| t2r4 ? t4r1 |
| t2r4 ? t4r3 |
| |
| --- 8 row(s) selected. |
| >>-- should get 8 rows |
| >>-- {(t2r1,t3r1,null),(t2r1,t3r3,null),(t2r2,null,t4r1),(t2r2,null,t4r3), |
| >>-- (t2r3,t3r1,null),(t2r3,t3r3,null),(t2r4,null,t4r1),(t2r4,null,t4r3) } |
| >> |
| >>?section q25 |
| >>select count(*) |
| +> from t008t2 left join t008t3 on c2 = c3 |
| +> left join t008t4 on c2 = c4 |
| +>; |
| |
| (EXPR) |
| -------------------- |
| |
| 8 |
| |
| --- 1 row(s) selected. |
| >>-- should get 8 |
| >> |
| >>?section q26 |
| >>select b2,b3,b4 |
| +> from t008t2 left join t008t3 on c2 = c3 |
| +> left join t008t4 on c3 = c4 |
| +>; |
| |
| B2 B3 B4 |
| ------ ------ ------ |
| |
| t2r1 t3r1 ? |
| t2r1 t3r3 ? |
| t2r2 ? ? |
| t2r3 t3r1 ? |
| t2r3 t3r3 ? |
| t2r4 ? ? |
| |
| --- 6 row(s) selected. |
| >>-- should get 6 rows |
| >>-- {(t2r1,t3r1,null),(t2r1,t3r3,null),(t2r2,null,null) |
| >>-- (t2r3,t3r1,null),(t2r3,t3r3,null),(t2r4,null,null) } |
| >> |
| >>?section q27 |
| >>select count(*) |
| +> from t008t2 left join t008t3 on c2 = c3 |
| +> left join t008t4 on c3 = c4 |
| +>; |
| |
| (EXPR) |
| -------------------- |
| |
| 6 |
| |
| --- 1 row(s) selected. |
| >>-- should get 6 |
| >> |
| >>?section q28 |
| >>select b2,b3,b4 |
| +> from t008t2 left join t008t3 on c2 = c3 |
| +> left join t008t4 on c2 = c4 |
| +> where a3 = 30 |
| +>; |
| |
| B2 B3 B4 |
| ------ ------ ------ |
| |
| t2r1 t3r3 ? |
| t2r3 t3r3 ? |
| |
| --- 2 row(s) selected. |
| >>-- should get 2 rows |
| >>-- {(t2r1,t3r3,null),(t2r3,t3r1,null) } |
| >> |
| >>-- Run the equivalent inner join query for verifying the answer |
| >>select b2,b3,b4 |
| +> from t008t2 join t008t3 on c2 = c3 and a3 = 30 |
| +> left join t008t4 on c2 = c4 |
| +>; |
| |
| B2 B3 B4 |
| ------ ------ ------ |
| |
| t2r1 t3r3 ? |
| t2r3 t3r3 ? |
| |
| --- 2 row(s) selected. |
| >>-- should get 2 rows |
| >>-- {(t2r1,t3r3,null),(t2r3,t3r1,null) } |
| >> |
| >>?section q29 |
| >>-- Transform the LJ at the root into an IJ, force a VEG merge |
| >>select count(*) |
| +> from t008t2 left join t008t3 on c2 = c3 |
| +> left join t008t4 on c2 = c4 |
| +> where a3 = 30 |
| +>; |
| |
| (EXPR) |
| -------------------- |
| |
| 2 |
| |
| --- 1 row(s) selected. |
| >>-- should get 2 rows |
| >>-- {(t2r1,t3r3,null),(t2r3,t3r3,null) } |
| >> |
| >>select count(*) |
| +> from t008t2 join t008t3 on c2 = c3 and a3 = 30 |
| +> left join t008t4 on c2 = c4 |
| +>; |
| |
| (EXPR) |
| -------------------- |
| |
| 2 |
| |
| --- 1 row(s) selected. |
| >>-- should get 2 rows |
| >>-- {(t2r1,t3r3,null),(t2r3,t3r3,null) } |
| >> |
| >>?section q30 |
| >>-- Test the MapValueId transformation performed by the |
| >>-- normalizer |
| >>select b3, count(*) |
| +> from t008t2 left join t008t3 on c2 = c3 |
| +> left join t008t4 on c2 = c4 |
| +> where a3 = 30 |
| +> group by b3 |
| +>; |
| |
| B3 (EXPR) |
| ------ -------------------- |
| |
| t3r3 2 |
| |
| --- 1 row(s) selected. |
| >>-- should get 1 group |
| >>-- {(t3r3,2)} |
| >> |
| >>?section q31 |
| >>-- Transform the LJ at the leaf into an IJ, force a VEG merge |
| >>select b2,b3,b4 |
| +> from t008t2 left join t008t3 on c2 = c3 |
| +> left join t008t4 on c3 = c4 |
| +> where a4 = 30 |
| +>; |
| |
| --- 0 row(s) selected. |
| >>-- should get 0 rows |
| >> |
| >>-- Transform the LJ at the leaf into an IJ, force a VEG merge |
| >>?section q32 |
| >>select count(*) |
| +> from t008t2 left join t008t3 on c2 = c3 |
| +> left join t008t4 on c3 = c4 |
| +> where a4 = 30 |
| +>; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>-- should get 0 |
| >> |
| >>?section clnup |
| >>-- cleanup the database |
| >>drop table t008t1; |
| |
| --- SQL operation complete. |
| >>drop table t008t2; |
| |
| --- SQL operation complete. |
| >>drop table t008t3; |
| |
| --- SQL operation complete. |
| >>drop table t008t4; |
| |
| --- SQL operation complete. |
| >>drop table t008t20; |
| |
| --- SQL operation complete. |
| >>drop table t008t30; |
| |
| --- SQL operation complete. |
| >> |
| >>log; |
