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