core/sql/regress/compGeneral/EXPECTED013.SB

>>
>>create table t40( a int not null, b int, c int, d int, e int, f int, g int, h int, i int, j int, primary key (a)) ;

--- SQL operation complete.
>>create table t41 like t40 ;

--- SQL operation complete.
>>create table t44( a int not null, b int, primary key (a)) ;

--- SQL operation complete.
>>
>>-- t44 is eliminated
>>-- 2 table outer join query
>>prepare XX from select t40.b from t40 left join t44 on t40.a = t44.a ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
SCAN            

--- 2 row(s) selected.
>>
>>-- t44 is eliminated
>>-- 3 table outer join query
>>prepare XX from select t40.b, t41.b from t40 left join t41 on t40.a = t41.a left join t44 on t40.a = t44.a ;

--- SQL command prepared.
>>execute showOperatorsOnly;

OPERATOR        
----------------

ROOT            
LEFT_HYBRID_HASH
SCAN            
SCAN            

--- 4 row(s) selected.
>>
>>
>>-- NOT ENFORCED RI CONSTRAINT
>>--------------------------------------------------------------------------------
>>
>>
>>create table customer (c_custkey int not null, primary key(c_custkey));

--- SQL operation complete.
>>create table orders (o_orderkey int not null, o_custkey int, constraint cons11 foreign key(o_custkey) references customer(c_custkey) not enforced, primary key(o_orderkey)) no partitions;

*** WARNING[1313] The referential integrity constraint TRAFODION.SCH.CONS11 has been created with the NOT ENFORCED attribute and will not be enforced during INSERT, UPDATE, OR DELETE statements.

--- SQL operation complete.
>>create table orders1 (o_orderkey int not null, o_custkey int, foreign key(o_custkey) references customer(c_custkey) enforced, primary key(o_orderkey)) no partitions;

--- SQL operation complete.
>>create table orders2 (o_orderkey int not null, o_custkey int, foreign key(o_custkey) references customer(c_custkey) enforced not enforced, primary key(o_orderkey)) no partitions;

*** ERROR[3243] Duplicate ENFORCED clauses were specified.

*** ERROR[8822] The statement was not prepared.

>>create table orders2 (o_orderkey int not null, o_custkey int, foreign key(o_custkey) references customer(c_custkey) enforced, primary key(o_orderkey) not enforced) no partitions;

*** ERROR[3244] The NOT ENFORCED constraint attribute is allowed only in a Referential Integrity constraint.

*** ERROR[8822] The statement was not prepared.

>>create table orders2 (o_orderkey int not null, o_custkey int, foreign key(o_custkey) references customer(c_custkey) enforced, primary key(o_orderkey) enforced) no partitions;

--- SQL operation complete.
>>
>>showddl orders;

CREATE TABLE TRAFODION.SCH.ORDERS
  (
    O_ORDERKEY                       INT NO DEFAULT NOT NULL NOT DROPPABLE
  , O_CUSTKEY                        INT DEFAULT NULL
  , PRIMARY KEY (O_ORDERKEY ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

ALTER TABLE TRAFODION.SCH.ORDERS ADD CONSTRAINT TRAFODION.SCH.CONS11 FOREIGN
  KEY
  (
    O_CUSTKEY
  )
 REFERENCES TRAFODION.SCH.CUSTOMER
  (
    C_CUSTKEY
  )
 NOT ENFORCED
;

--- SQL operation complete.
>>showddl orders1;

CREATE TABLE TRAFODION.SCH.ORDERS1
  (
    O_ORDERKEY                       INT NO DEFAULT NOT NULL NOT DROPPABLE
  , O_CUSTKEY                        INT DEFAULT NULL
  , PRIMARY KEY (O_ORDERKEY ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- The following index is a system created index --
CREATE INDEX ORDERS1_277948757_4279 ON TRAFODION.SCH.ORDERS1
  (
    O_CUSTKEY ASC
  )
;

ALTER TABLE TRAFODION.SCH.ORDERS1 ADD CONSTRAINT
  TRAFODION.SCH.ORDERS1_277948757_4279 FOREIGN KEY
  (
    O_CUSTKEY
  )
 REFERENCES TRAFODION.SCH.CUSTOMER
  (
    C_CUSTKEY
  )
;

--- SQL operation complete.
>>showddl orders2;

CREATE TABLE TRAFODION.SCH.ORDERS2
  (
    O_ORDERKEY                       INT NO DEFAULT NOT NULL NOT DROPPABLE
  , O_CUSTKEY                        INT DEFAULT NULL
  , PRIMARY KEY (O_ORDERKEY ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- The following index is a system created index --
CREATE INDEX ORDERS2_417358757_4279 ON TRAFODION.SCH.ORDERS2
  (
    O_CUSTKEY ASC
  )
;

ALTER TABLE TRAFODION.SCH.ORDERS2 ADD CONSTRAINT
  TRAFODION.SCH.ORDERS2_417358757_4279 FOREIGN KEY
  (
    O_CUSTKEY
  )
 REFERENCES TRAFODION.SCH.CUSTOMER
  (
    C_CUSTKEY
  )
;

--- SQL operation complete.
>>
>>prepare XX from insert into orders values(1,1);

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
TRAFODION_INSERT

--- 2 row(s) selected.
>>
>>prepare XX from insert into orders1 values(1,1);

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
NESTED_JOIN     
MERGE_UNION     
SORT_SCALAR_AGGR
SCAN            
TRAFODION_INSERT
TRAFODION_INSERT

--- 7 row(s) selected.
>>
>>prepare XX from update orders1 set o_custkey = 1 ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
NESTED_JOIN     
MERGE_UNION     
SORT_SCALAR_AGGR
SCAN            
BLOCKED_UNION   
TRAFODION_VSBB_U
TRAFODION_DELETE
TRAFODION_UPDATE

--- 9 row(s) selected.
>>
>>prepare XX from delete from customer ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
NESTED_JOIN     
MERGE_UNION     
SORT_SCALAR_AGGR
SCAN            
SORT_SCALAR_AGGR
SCAN            
NESTED_JOIN     
TRAFODION_DELETE
SCAN            

--- 10 row(s) selected.
>>
>>drop table orders1;

--- SQL operation complete.
>>drop table orders2;

--- SQL operation complete.
>>prepare XX from delete from customer ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
TUPLE_FLOW      
TRAFODION_VSBB_D
SCAN            

--- 4 row(s) selected.
>>
>>
>>-----------------------------------------------------------------------------------------------
>>
>>create table lineitem (l_linekey int not null, l_orderkey int, l_partkey int, l_quantity int,  
+>foreign key(l_orderkey) references orders(o_orderkey), primary key(l_linekey));

--- SQL operation complete.
>>
>>
>>
>>
>>prepare XX from 
+>Select c_custkey, l_orderkey,l_partkey, l_quantity
+>From lineitem, orders, customer
+>Where l_orderkey = o_orderkey
+>And o_custkey = c_custkey
+>And o_orderkey >= 500 ;

--- SQL command prepared.
>>
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
HYBRID_HASH_JOIN
SCAN            
SCAN            

--- 4 row(s) selected.
>>
>>
>>
>>prepare XX from
+>Select c_custkey, l_orderkey,l_partkey, l_quantity
+>From customer, lineitem, orders
+>Where l_orderkey = o_orderkey
+>And o_custkey = c_custkey
+>And o_orderkey >= 500 ;

--- SQL command prepared.
>>
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
HYBRID_HASH_JOIN
SCAN            
SCAN            

--- 4 row(s) selected.
>>
>>
>>
>>
>>create table uk0 (a int not null primary key, b int not null);

--- SQL operation complete.
>>create table uk1 (a int not null primary key, b int not null unique);

--- SQL operation complete.
>>create table uk2 (a int not null, b int not null, c int, primary key(a,b));

--- SQL operation complete.
>>create table uk3 (a int not null, b int not null, c int not null, primary key(a,b,c));

--- SQL operation complete.
>>
>>create table fk0 ( a int not null primary key, b int, c int, constraint cons2 foreign key (b) references uk0(a) not enforced) ;

*** WARNING[1313] The referential integrity constraint TRAFODION.SCH.CONS2 has been created with the NOT ENFORCED attribute and will not be enforced during INSERT, UPDATE, OR DELETE statements.

--- SQL operation complete.
>>create table fk1 ( a int not null primary key, b int, c int, constraint cons3 foreign key (b) references uk1(a) not enforced) ;

*** WARNING[1313] The referential integrity constraint TRAFODION.SCH.CONS3 has been created with the NOT ENFORCED attribute and will not be enforced during INSERT, UPDATE, OR DELETE statements.

--- SQL operation complete.
>>create table fk2 ( a int not null primary key, b int, c int, constraint cons4 foreign key (a,b) references uk2(a,b) not enforced) ;

*** WARNING[1313] The referential integrity constraint TRAFODION.SCH.CONS4 has been created with the NOT ENFORCED attribute and will not be enforced during INSERT, UPDATE, OR DELETE statements.

--- SQL operation complete.
>>create table fk3 ( a int not null primary key, b int, c int, constraint cons5 foreign key (a,b,c) references uk3(a,b,c) not enforced) ;

*** WARNING[1313] The referential integrity constraint TRAFODION.SCH.CONS5 has been created with the NOT ENFORCED attribute and will not be enforced during INSERT, UPDATE, OR DELETE statements.

--- SQL operation complete.
>>
>>create table ffk0 ( a int not null primary key, b int, c int, constraint cons6 foreign key (b) references fk0(a) not enforced) ;

*** WARNING[1313] The referential integrity constraint TRAFODION.SCH.CONS6 has been created with the NOT ENFORCED attribute and will not be enforced during INSERT, UPDATE, OR DELETE statements.

--- SQL operation complete.
>>
>>-- tables for sol: 10-100722-1962 test
>>create table t_013 ( a int not null, 
+>                  b char(32) not null, 
+>                  c char(32) , 
+>                  primary key (a,b));

--- SQL operation complete.
>>
>>insert into t_013 values (1, 'abc', 'def'), (2, 'ghi', 'jkl');

--- 2 row(s) inserted.
>>
>>create table fksalta ( id int not null, PRIMARY KEY (id))SALT USING 4 PARTITIONS;

--- SQL operation complete.
>>
>>CREATE TABLE fksaltb ( id int not null, val int,
+>  PRIMARY KEY (id),
+>  CONSTRAINT FKsalt FOREIGN KEY (val) REFERENCES fksalta (id))
+>  SALT USING 4 PARTITIONS;

--- SQL operation complete.
>>
>>INSERT INTO fksalta VALUES ( 1 );

--- 1 row(s) inserted.
>>
>>INSERT INTO fksaltb VALUES ( 1, 1);

--- 1 row(s) inserted.
>>
>>INSERT INTO fksaltb VALUES ( 2, 2);

*** ERROR[8103] The operation is prevented by referential integrity constraint TRAFODION.SCH.FKSALT on table TRAFODION.SCH.FKSALTB.

--- 0 row(s) inserted.
>>
>>CREATE TABLE fkdiva( store_id integer not null,
+>                   item_id  integer not null,
+>                sale_date date default date '2000-01-01' not null,
+>                  sale_amt numeric(10,2),
+>                   primary key (store_id, item_id, sale_date))
+> division by (date_part('YEARMONTH', sale_date));

--- SQL operation complete.
>>
>>create table fkdivb(c1 int not null, c2 int , c3 int, c4  date, primary key(c1), CONSTRAINT FKDIV FOREIGN KEY (c2,c3, c4) REFERENCES fkdiva (store_id,item_id, sale_date));

--- SQL operation complete.
>>
>>INSERT INTO fkdiva VALUES ( 1 , 1, date'2016-03-05', 10.2 );

--- 1 row(s) inserted.
>>INSERT INTO fkdivb VALUES ( 1 , 1, 1, date'2016-03-05');

--- 1 row(s) inserted.
>>INSERT INTO fkdivb VALUES ( 2 , 2, 1, date'2016-01-25');

*** ERROR[8103] The operation is prevented by referential integrity constraint TRAFODION.SCH.FKDIV on table TRAFODION.SCH.FKDIVB.

--- 0 row(s) inserted.
>>
>>--- uk0 should be eliminated
>>-- two table RI constraint join
>>prepare XX from
+>select fk0.a from fk0, uk0 where fk0.b = uk0.a ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
SCAN            

--- 2 row(s) selected.
>>
>>
>>--- uk0 should be eliminated
>>-- two table RI constraint join on an indexed column
>>prepare XX from
+>select fk1.a from fk1, uk1 where fk1.b = uk1.a ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
SCAN            

--- 2 row(s) selected.
>>
>>--- uk0 should be eliminated
>>-- two table RI constraint join with two colum FK
>>prepare XX from
+>select fk2.a from fk2, uk2 where fk2.a = uk2.a and fk2.b = uk2.b;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
SCAN            

--- 2 row(s) selected.
>>
>>--- uk0 should be eliminated
>>-- two table RI constraint join with three colum FK
>>prepare XX from
+>select fk3.a from fk3, uk3 where fk3.a = uk3.a and fk3.b = uk3.b and fk3.c = uk3.c;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
SCAN            

--- 2 row(s) selected.
>>
>>--- uk0 should be eliminated
>>-- three table RI constraint join
>>-- order 1
>>prepare XX from
+>select fk0.a from fk0, uk0, uk2 where fk0.b = uk0.a ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
HYBRID_HASH_JOIN
SCAN            
SCAN            

--- 4 row(s) selected.
>>
>>--- uk0 should be eliminated
>>-- three table RI constraint join
>>-- order 2
>>prepare XX from
+>select fk0.a from fk0, uk2, uk0 where fk0.b = uk0.a ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
HYBRID_HASH_JOIN
SCAN            
SCAN            

--- 4 row(s) selected.
>>
>>--- uk0 should be eliminated
>>-- three table RI constraint join
>>-- order 3
>>prepare XX from
+>select fk0.a from uk0, fk0, uk2 where fk0.b = uk0.a ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
HYBRID_HASH_JOIN
SCAN            
SCAN            

--- 4 row(s) selected.
>>
>>--- uk0 should be eliminated
>>-- three table RI constraint join
>>-- order 4
>>prepare XX from
+>select fk0.a from uk0, uk2, fk0 where fk0.b = uk0.a ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
HYBRID_HASH_JOIN
SCAN            
SCAN            

--- 4 row(s) selected.
>>
>>--- uk0 should be eliminated
>>-- three table RI constraint join
>>-- order 5
>>prepare XX from
+>select fk0.a from uk2, fk0, uk0 where fk0.b = uk0.a ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
HYBRID_HASH_JOIN
SCAN            
SCAN            

--- 4 row(s) selected.
>>
>>--- uk0 should be eliminated
>>-- three table RI constraint join
>>-- order 6
>>prepare XX from
+>select fk0.a from uk2, uk0, fk0 where fk0.b = uk0.a ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
HYBRID_HASH_JOIN
SCAN            
SCAN            

--- 4 row(s) selected.
>>
>>--- uk0 should be eliminated
>>-- three table RI constraint join
>>-- non RI predicate on FK table and third table
>>prepare XX from
+>select fk0.a from uk0, uk2, fk0 where fk0.b = uk0.a and fk0.a = uk2.b;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
HYBRID_HASH_JOIN
SCAN            
SCAN            

--- 4 row(s) selected.
>>
>>-- nothing should be eliminated
>>prepare XX from
+>select fk1.a from fk1, uk1 where fk1.a = uk1.b ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
HYBRID_HASH_JOIN
SCAN            
SCAN            

--- 4 row(s) selected.
>>prepare XX from
+>select fk2.a from fk2, uk2 where fk2.a = uk2.a ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
HYBRID_HASH_JOIN
SCAN            
SCAN            

--- 4 row(s) selected.
>>prepare XX from
+>select fk3.a from fk3, uk3 where fk3.a = uk3.a and fk3.b = uk3.b and uk3.c > 100;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
HYBRID_HASH_JOIN
SCAN            
SCAN            

--- 4 row(s) selected.
>>
>>-- veg from uk table in select list
>>prepare XX from
+>select fk0.a, uk0.a from fk0, uk0 where fk0.b = uk0.a ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
SCAN            

--- 2 row(s) selected.
>>
>>-- extra-hub
>>prepare XX from
+>select fk0.a, uk0.b from fk0, uk0 where fk0.b = uk0.a ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
HYBRID_HASH_JOIN
SCAN            
SCAN            

--- 4 row(s) selected.
>>
>>-- subquery table has UK
>>-- should not be unnested
>>prepare XX from
+>select fk0.a from fk0 where fk0.b = (select uk0.a from uk0);

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
HYBRID_HASH_JOIN
SCAN            
SORT_SCALAR_AGGR
SCAN            

--- 5 row(s) selected.
>>
>>--- uk0 should be eliminated
>>-- two table RI constraint join
>>-- needs OR constant folding
>>prepare XX from
+>select fk0.a from fk0, uk0 where fk0.b = uk0.a AND ((uk0.b = 1) OR (2 = 2));

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
SCAN            

--- 2 row(s) selected.
>>
>>--- uk0 should be eliminated
>>-- two table RI constraint join
>>-- group by on top of uk0
>>prepare XX from
+>select fk0.a from fk0, (select uk0.a from uk0 group by uk0.a) tt
+>where fk0.b = tt.a ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
SCAN            

--- 2 row(s) selected.
>>
>>
>>--- uk0 and fk0 should be eliminated in 2 passes
>>-- ffk0->fk0->uk0
>>
>>prepare XX from
+>select ffk0.a from ffk0, fk0, uk0
+>where ffk0.b = fk0.a and fk0.b = uk0.a ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
SCAN            

--- 2 row(s) selected.
>>
>>-- uk0 should not be eliminated. Direct parent of potentially eliminatable 
>>-- join causes a column from uk0 to be essential
>>prepare XX from
+>select fk0.a from fk0, uk0 where fk0.b = uk0.a order by uk0.b ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
SORT            
HYBRID_HASH_JOIN
SCAN            
SCAN            

--- 5 row(s) selected.
>>
>>-- uk0 should not be eliminated. Some parent of potentially eliminatable 
>>-- join causes a column from uk0 to be essential
>>prepare XX from
+>select fk0.a from fk0, uk0, uk1 where fk0.b = uk0.a order by uk0.b ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
SORT            
HYBRID_HASH_JOIN
HYBRID_HASH_JOIN
SCAN            
SCAN            
SCAN            

--- 7 row(s) selected.
>>
>>-- uk0 can be eliminated.
>>prepare XX from
+>select fk0.a from fk0, uk0, uk1 where fk0.b = uk0.a order by uk0.a ;

--- SQL command prepared.
>>execute showOperatorsOnly ;

OPERATOR        
----------------

ROOT            
SORT            
HYBRID_HASH_JOIN
SCAN            
SCAN            

--- 5 row(s) selected.
>>
>>-- test to verify solution 10-100722-1962..
>>-- should produce a row with a valid string, not a null instantiated string.
>>-- output should be 'ghi'
>>cqd hash_joins 'on';

--- SQL operation complete.
>>cqd nested_joins 'off';

--- SQL operation complete.
>>cqd merge_joins 'off';

--- SQL operation complete.
>>select a16.b
+>from t_013 a11
+>left outer join t_013 a16
+>on ( substring(a11.c,4,1) = substring(a16.c,4,1)
+>and a11.c = a16.c)
+>where a11.a =2;

B                               
--------------------------------

ghi                             

--- 1 row(s) selected.
>>cqd hash_joins 'system';

--- SQL operation complete.
>>cqd nested_joins 'system';

--- SQL operation complete.
>>cqd merge_joins 'system';

--- SQL operation complete.
>>
>>log ;