######################################
# Author: Jeb
# Date: 2008-04-02
# Purpose: Test NDB's abilities to handle
#          different types of joins
######################################

let $engine_type=ndbcluster;

SET @@optimizer_switch='no_bka';
--disable_warnings
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
--enable_warnings

##############################
# This test gave a core dump #
# on the MyISAM Engine       #
##############################

--echo ******************
--echo ** Join Test #1 **
--echo ******************

--echo
--echo ** Create Table t1 **  
--echo
eval CREATE TABLE t1 (
        project_id int(11) NOT NULL auto_increment,
        project_row_lock int(11) NOT NULL default '0',
        project_name varchar(80) NOT NULL default '',
        client_ptr int(11) NOT NULL default '0',
        project_contact_ptr int(11) default NULL,
        client_contact_ptr int(11) default NULL,
        billing_contact_ptr int(11) default NULL,
        comments mediumtext,
        PRIMARY KEY  (project_id),
        UNIQUE KEY project (client_ptr,project_name)
        ) ENGINE=$engine_type;


--echo
--echo ** Create Table t2 **
--echo
eval CREATE TABLE t2 (
        period_id int(11) NOT NULL auto_increment,
        period_type enum('user_table','client_table',
                         'role_table','member_table',
                         'project_table') default NULL,
        period_key int(11) default NULL,
        start_date datetime default NULL,
        end_date datetime default NULL,
        work_load int(11) default NULL,
        PRIMARY KEY  (period_id),
        KEY period_index (period_type,period_key),
        KEY date_index (start_date,end_date)
        ) ENGINE=$engine_type;

--echo
--echo ** Create Table t3 **
--echo
eval CREATE TABLE t3 (
         budget_id int(11) NOT NULL auto_increment,
         project_ptr int(11) NOT NULL default '0',
         po_number varchar(20) NOT NULL default '',
         status enum('open','closed') default NULL,
         date_received datetime default NULL,
         amount_received float(10,2) default NULL,
         adjustment float(10,2) default NULL,
         PRIMARY KEY  (budget_id),
         UNIQUE KEY po (project_ptr,po_number)
         ) ENGINE=$engine_type;


--echo
--echo ** Create Table t4 **
--echo
eval CREATE TABLE t4 (
        client_id int(11) NOT NULL auto_increment,
        client_row_lock int(11) NOT NULL default '0',
        client_name varchar(80) NOT NULL default '',
        contact_ptr int(11) default NULL,
        comments mediumtext,
        PRIMARY KEY  (client_id),
        UNIQUE KEY client_name (client_name)
        ) ENGINE=$engine_type;

--echo
--echo ** Insert Data **
--echo
--disable_query_log
INSERT INTO t1 VALUES (1,0,'Rejected Time',1,NULL,NULL,NULL,NULL);
INSERT INTO t1 VALUES (209,0,'MDGRAD Proposal/Investigation',97,NULL,NULL,NULL,'');
INSERT INTO t1 VALUES (208,0,'Font 9 Design',84,NULL,NULL,NULL,'');
INSERT INTO t1 VALUES (207,0,'Web Based Order Processing',95,NULL,NULL,NULL,'');
INSERT INTO t1 VALUES (205,0,'Mac Screen Saver',95,NULL,NULL,NULL,'');
INSERT INTO t1 VALUES (206,2,'Web Site',96,NULL,NULL,NULL,'');
INSERT INTO t1 VALUES (204,0,'Magnafire Glue',94,NULL,NULL,NULL,'');
INSERT INTO t1 VALUES (203,0,'Print Bid',93,NULL,NULL,NULL,'');
INSERT INTO t1 VALUES (202,0,'EPOC Port',92,NULL,NULL,NULL,'');
INSERT INTO t1 VALUES (201,0,'TravelMate',88,NULL,NULL,NULL,'');
INSERT INTO t2 VALUES (1,'user_table',98,'2000-01-01 00:00:00',NULL,NULL);
INSERT INTO t2 VALUES (2,'user_table',99,'2000-01-01 00:00:00',NULL,NULL);
INSERT INTO t2 VALUES (3,'user_table',100,'2000-01-01 00:00:00',NULL,NULL);
INSERT INTO t2 VALUES (49,'project_table',148,'2000-01-01 00:00:00',NULL,NULL);
INSERT INTO t2 VALUES (50,'client_table',68,'2000-01-01 00:00:00',NULL,NULL);
INSERT INTO t2 VALUES (51,'project_table',149,'2000-01-01 00:00:00',NULL,NULL);
INSERT INTO t2 VALUES (52,'project_table',150,'2000-01-01 00:00:00',NULL,NULL);
INSERT INTO t2 VALUES (53,'client_table',69,'2000-01-01 00:00:00',NULL,NULL);
INSERT INTO t2 VALUES (54,'project_table',151,'2000-01-01 00:00:00',NULL,NULL);
INSERT INTO t2 VALUES (55,'client_table',70,'2000-01-01 00:00:00',NULL,NULL);
INSERT INTO t2 VALUES (155,'role_table',1,'2000-01-01 00:00:00',NULL,NULL);
INSERT INTO t2 VALUES (156,'role_table',2,'2000-01-01 00:00:00',NULL,NULL);
INSERT INTO t2 VALUES (160,'member_table',1,'2000-01-01 00:00:00',NULL,1);
INSERT INTO t2 VALUES (161,'member_table',2,'2000-01-01 00:00:00',NULL,1);
INSERT INTO t2 VALUES (162,'member_table',3,'2000-01-01 00:00:00',NULL,1);
INSERT INTO t4 VALUES (1,0,'CPS',NULL,NULL);
--enable_query_log

--echo
--echo ** The query that fails... **
--echo
select distinct
    t1.project_id as project_id,
    t1.project_name as project_name,
    t1.client_ptr as client_ptr,
    t1.comments as comments,
    sum( t3.amount_received ) + sum( t3.adjustment ) as total_budget
from
    t2 as client_period ,
    t2 as project_period,
    t3 left join t1 on (t3.project_ptr = t1.project_id and
                        t3.date_received <= '2001-03-22 14:15:09')
       left join t4 on t4.client_id = t1.client_ptr
   where
        1
        and ( client_period.period_type = 'client_table'
            and client_period.period_key = t4.client_id
            and ( client_period.start_date <= '2001-03-22 14:15:09' or isnull( client_period.start_date ))
            and ( client_period.end_date > '2001-03-21 14:15:09' or isnull( client_period.end_date ))
            )
        and ( project_period.period_type = 'project_table'
        and project_period.period_key = t1.project_id
        and ( project_period.start_date <= '2001-03-22 14:15:09' or isnull( project_period.start_date ))
        and ( project_period.end_date > '2001-03-21 14:15:09' or isnull( project_period.end_date )) )
    group by
        client_id,
        project_id ,
        client_period.period_id ,
        project_period.period_id
    order by
        client_name asc,
        project_name asc;

INSERT INTO t3 VALUES (NULL, 111,2,'open','2001-03-22 14:15:09',100.00,20.00);
select distinct
    t1.project_id as project_id,
    t1.project_name as project_name,
    t1.client_ptr as client_ptr,
    t1.comments as comments,
    sum( t3.amount_received ) + sum( t3.adjustment ) as total_budget
from
    t2 as client_period ,
    t2 as project_period,
    t3 left join t1 on (t3.project_ptr = t1.project_id and
                        t3.date_received <= '2001-03-22 14:15:09')
       left join t4 on t4.client_id = t1.client_ptr
   where
        1
        and ( client_period.period_type = 'client_table'
            and client_period.period_key = t4.client_id
            and ( client_period.start_date <= '2001-03-22 14:15:09' or isnull( client_period.start_date ))
            and ( client_period.end_date > '2001-03-21 14:15:09' or isnull( client_period.end_date ))
            )
        and ( project_period.period_type = 'project_table'
        and project_period.period_key = t1.project_id
        and ( project_period.start_date <= '2001-03-22 14:15:09' or isnull( project_period.start_date ))
        and ( project_period.end_date > '2001-03-21 14:15:09' or isnull( project_period.end_date )) )
    group by
        client_id,
        project_id ,
        client_period.period_id ,
        project_period.period_id
    order by
        client_name asc,
        project_name asc;

DROP TABLE t1,t2,t3,t4;

--echo *****************
--echo ** End Test #1 **
--echo *****************
--echo
--echo ******************
--echo ** Join Test #2 **
--echo **  Left Joins  **
--echo ******************
--echo
--echo ** Create Tables **
--echo

eval CREATE TABLE t0 (a int, b int, c int, 
         PRIMARY KEY(a), KEY b_i (b))ENGINE=$engine_type;
eval CREATE TABLE t1 (a int, b int, c int, 
         PRIMARY KEY(a), KEY b_i (b))ENGINE=$engine_type;
eval CREATE TABLE t2 (a int, b int, c int, 
         PRIMARY KEY(a), KEY b_i (b))ENGINE=$engine_type;
eval CREATE TABLE t3 (a int, b int, c int, 
         PRIMARY KEY(a), KEY b_i (b))ENGINE=$engine_type;
eval CREATE TABLE t4 (a int, b int, c int, 
         PRIMARY KEY(a), KEY b_i (b))ENGINE=$engine_type;
eval CREATE TABLE t5 (a int, b int, c int, 
         PRIMARY KEY(a), KEY b_i (b))ENGINE=$engine_type;
eval CREATE TABLE t6 (a int, b int, c int, 
         PRIMARY KEY(a), KEY b_i (b))ENGINE=$engine_type;
eval CREATE TABLE t7 (a int, b int, c int, 
         PRIMARY KEY(a), KEY b_i (b))ENGINE=$engine_type;
eval CREATE TABLE t8 (a int, b int, c int, 
         PRIMARY KEY(a), KEY b_i (b))ENGINE=$engine_type;
eval CREATE TABLE t9 (a int, b int, c int, 
         PRIMARY KEY(a), KEY b_i (b))ENGINE=$engine_type;

--echo
--echo ** Insert Data **
--echo
--disable_query_log
INSERT INTO t0 VALUES (1,1,0), (2,2,0), (3,2,0);
INSERT INTO t1 VALUES (1,3,0), (2,2,0), (3,2,0);
INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0);
INSERT INTO t3 VALUES (1,2,0), (2,2,0);
INSERT INTO t4 VALUES (3,2,0), (4,2,0);
INSERT INTO t5 VALUES (1,1,0), (2,2,0), (3,3,0);
INSERT INTO t6 VALUES (1,2,0), (3,2,0), (6,1,0);
INSERT INTO t7 VALUES (1,1,0), (2,2,0);
INSERT INTO t8 VALUES (0,2,0), (1,2,0);
INSERT INTO t9 VALUES (1,1,0), (2,2,0), (3,3,0);
--enable_query_log

--echo
--echo ** Run selects **
--echo
--sorted_result
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
  FROM t2
       LEFT JOIN              
       (t3, t4)
       ON t2.b=t4.b;

--sorted_result
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
  FROM t2
       LEFT JOIN              
       (t3, t4)
       ON t3.a=1 AND t2.b=t4.b;

--sorted_result
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
  FROM t2
       LEFT JOIN              
       (t3, t4)
       ON t2.b=t4.b
    WHERE t3.a=1 OR t3.c IS NULL;

--sorted_result
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
  FROM t2
       LEFT JOIN              
       (t3, t4, t5)
       ON t2.b=t4.b;

--sorted_result
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
  FROM t2
       LEFT JOIN              
       (t3, t4, t5)
       ON t2.b=t4.b
    WHERE t3.a>1 OR t3.c IS NULL;

--sorted_result
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
  FROM t2
       LEFT JOIN              
       (t3, t4, t5)
       ON t2.b=t4.b
    WHERE (t3.a>1 OR t3.c IS NULL) AND 
          (t5.a<3 OR t5.c IS NULL);

--sorted_result
SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
  FROM (t6, t7)
       LEFT JOIN 
       t8
       ON t7.b=t8.b AND t6.b < 10;

--sorted_result
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
  FROM t5 
       LEFT JOIN 
       ( 
         (t6, t7)
         LEFT JOIN 
         t8
         ON t7.b=t8.b AND t6.b < 10
       )
       ON t6.b >= 2 AND t5.b=t7.b;

--sorted_result
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
  FROM t5 
       LEFT JOIN 
       ( 
         (t6, t7)
         LEFT JOIN 
         t8
         ON t7.b=t8.b AND t6.b < 10
       )
       ON t6.b >= 2 AND t5.b=t7.b AND
          (t8.a < 1 OR t8.c IS NULL);

--sorted_result
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
  FROM t2
       LEFT JOIN              
       (t3, t4)
       ON t3.a=1 AND t2.b=t4.b;

--sorted_result
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
       t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
  FROM t2
       LEFT JOIN              
       (t3, t4)
       ON t3.a=1 AND t2.b=t4.b,
       t5 
       LEFT JOIN 
       ( 
         (t6, t7)
         LEFT JOIN 
         t8
         ON t7.b=t8.b AND t6.b < 10
       )
       ON t6.b >= 2 AND t5.b=t7.b; 

--sorted_result
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
       t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
  FROM t2
       LEFT JOIN              
       (t3, t4)
       ON t3.a=1 AND t2.b=t4.b,
       t5 
       LEFT JOIN 
       ( 
         (t6, t7)
         LEFT JOIN 
         t8
         ON t7.b=t8.b AND t6.b < 10
       )
       ON t6.b >= 2 AND t5.b=t7.b
    WHERE t2.a > 3 AND
          (t6.a < 6 OR t6.c IS NULL);

--sorted_result
SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
       t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
  FROM t1
       LEFT JOIN                
       ( 
         t2
         LEFT JOIN              
         (t3, t4)
         ON t3.a=1 AND t2.b=t4.b,
         t5 
         LEFT JOIN 
         ( 
           (t6, t7)
           LEFT JOIN 
           t8
           ON t7.b=t8.b AND t6.b < 10
         )
         ON t6.b >= 2 AND t5.b=t7.b 
       )
       ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
          (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
          (t1.a != 2);

--sorted_result
SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
       t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
  FROM t1
       LEFT JOIN                
       ( 
         t2
         LEFT JOIN              
         (t3, t4)
         ON t3.a=1 AND t2.b=t4.b,
         t5 
         LEFT JOIN 
         ( 
           (t6, t7)
           LEFT JOIN 
           t8
           ON t7.b=t8.b AND t6.b < 10
         )
         ON t6.b >= 2 AND t5.b=t7.b 
       )
       ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
          (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
          (t1.a != 2)
    WHERE (t2.a >= 4 OR t2.c IS NULL);
   
--sorted_result
SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
       t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
  FROM t0,t1
       LEFT JOIN                
       ( 
         t2
         LEFT JOIN              
         (t3, t4)
         ON t3.a=1 AND t2.b=t4.b,
         t5 
         LEFT JOIN 
         ( 
           (t6, t7)
           LEFT JOIN 
           t8
           ON t7.b=t8.b AND t6.b < 10
         )
         ON t6.b >= 2 AND t5.b=t7.b 
       )
       ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
          (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
          (t1.a != 2)
    WHERE t0.a=1 AND
          t0.b=t1.b AND          
          (t2.a >= 4 OR t2.c IS NULL); 

--sorted_result
SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
       t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
  FROM t0,t1
       LEFT JOIN                
       ( 
         t2
         LEFT JOIN              
         (t3, t4)
         ON t3.a=1 AND t2.b=t4.b,
         t5 
         LEFT JOIN 
         ( 
           (t6, t7)
           LEFT JOIN 
           t8
           ON t7.b=t8.b AND t6.b < 10
         )
         ON t6.b >= 2 AND t5.b=t7.b 
       )
       ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
          (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
          (t1.a != 2),
       t9
     WHERE t0.a=1 AND
           t0.b=t1.b AND          
           (t2.a >= 4 OR t2.c IS NULL) AND
           (t3.a < 5 OR t3.c IS NULL) AND
           (t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
           (t5.a >=2 OR t5.c IS NULL) AND
           (t6.a >=4 OR t6.c IS NULL) AND
           (t7.a <= 2 OR t7.c IS NULL) AND
           (t8.a < 1 OR t8.c IS NULL) AND
           (t8.b=t9.b OR t8.c IS NULL) AND
           (t9.a=1); 

--sorted_result
SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
       t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
  FROM t0,t1
       LEFT JOIN                
       ( 
         t2
         LEFT JOIN              
         (t3, t4)
         ON t3.a=1 AND t2.b=t4.b,
         t5 
         LEFT JOIN 
         ( 
           (t6, t7)
           LEFT JOIN 
           t8
           ON t7.b=t8.b AND t6.b < 10
         )
         ON t6.b >= 2 AND t5.b=t7.b 
       )
       ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
          (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
          (t1.a != 2),
       t9
     WHERE t0.a=1 AND
           t0.b=t1.b AND          
           (t2.a >= 4 OR t2.c IS NULL) AND
           (t3.a < 5 OR t3.c IS NULL) AND
           (t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
           (t5.a >=2 OR t5.c IS NULL) AND
           (t6.a >=4 OR t6.c IS NULL) AND
           (t7.a <= 2 OR t7.c IS NULL) AND
           (t8.a < 1 OR t8.c IS NULL) AND
           (t8.b=t9.b OR t8.c IS NULL) AND
           (t9.a=1); 


DROP TABLE t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;

--echo *****************
--echo ** End Test #2 **
--echo *****************
--echo
--echo ******************
--echo ** Join Test #3 **
--echo ** Nested Joins **
--echo ******************
--echo
--echo ** Create Tables **
--echo


eval CREATE TABLE t1 (a int, PRIMARY KEY(a))ENGINE=$engine_type;
eval CREATE TABLE t2 (a int, PRIMARY KEY(a))ENGINE=$engine_type;
eval CREATE TABLE t3 (a int, PRIMARY KEY(a))ENGINE=$engine_type;

INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
INSERT INTO t2 VALUES (2);
INSERT INTO t2 VALUES (3);
INSERT INTO t2 VALUES (4);
INSERT INTO t3 VALUES (2);
INSERT INTO t3 VALUES (3);

#check proper syntax for nested outer joins

--sorted_result 
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a=t3.a) ON t1.a=t3.a;

#must be equivalent to:

--sorted_result 
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;

#check that everything is all right when all tables contain not more than 1 row
#(bug #4922)

DELETE FROM t1 WHERE a=1;
DELETE FROM t1 WHERE a=3;
DELETE FROM t2 WHERE a=3;
DELETE FROM t1 WHERE a=4;
DELETE FROM t3 WHERE a=3;
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;
DELETE FROM t2;
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;

DROP TABLE t1,t2,t3;

--echo *****************
--echo ** End Test #3 **
--echo *****************
--echo
--echo ******************
--echo ** Join Test #4 **
--echo **   On Joins   **
--echo ******************
--echo
--echo ** Create Tables **
--echo


#on expression for a nested outer join does not depend on the outer table
#bug #4976

eval CREATE TABLE t1(a int, PRIMARY KEY(a))ENGINE=$engine_type;
eval CREATE TABLE t2(b int, PRIMARY KEY(b))ENGINE=$engine_type;                              
eval CREATE TABLE t3(c int, PRIMARY KEY(c))ENGINE=$engine_type;                              

INSERT INTO t1 VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
INSERT INTO t2 VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
INSERT INTO t3 VALUES (0), (1), (2), (3), (4), (5);

--sorted_result 
SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON c < 3 and b = c;
--sorted_result
SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;

DELETE FROM t3;
--sorted_result
SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;

DROP TABLE t1,t2,t3;

--echo *****************
--echo ** End Test #4 **
--echo *****************
--echo
--echo ******************
--echo ** Join Test #5 **
--echo ** Empty Table  **
--echo ******************
--echo
--echo ** Create Tables **
--echo

#
# Test for bug #11284: empty table in a nested left join
# 

eval CREATE TABLE t1 (c11 int, PRIMARY KEY(c11))ENGINE=$engine_type;
eval CREATE TABLE t2 (c21 int, PRIMARY KEY(c21))ENGINE=$engine_type;
eval CREATE TABLE t3 (c31 int, PRIMARY KEY(c31))ENGINE=$engine_type;

INSERT INTO t1 VALUES (4), (5);

--sorted_result
SELECT * FROM t1 LEFT JOIN t2 ON c11=c21;

--sorted_result
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;

DROP TABLE t1,t2,t3;

--echo *****************
--echo ** End Test #5 **
--echo *****************
--echo
--echo ******************
--echo ** Join Test #6 **
--echo ** Empty Table  **
--echo ******************
--echo
--echo ** Create Tables **
--echo


#
# Bug #12154: creation of temp table for a query with nested outer join
# 

eval CREATE TABLE t1 (goods int(12) NOT NULL, 
         price varchar(128) NOT NULL)ENGINE=$engine_type;
eval CREATE TABLE t2 (goods int(12), name varchar(50), 
         shop char(2))ENGINE=$engine_type;
eval CREATE TABLE t3 (groupid int(12) NOT NULL, 
         goodsid int(12) NOT NULL)ENGINE=$engine_type;
eval CREATE TABLE t4 (groupid int(12))ENGINE=$engine_type;

INSERT INTO t1 VALUES (23, 2340), (26, 9900);
INSERT INTO t2 VALUES (23, 'as300', 'fr'), (26, 'as600', 'fr');
INSERT INTO t3 VALUES (3,23), (6,26);
INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6);

--sorted_result
SELECT * FROM
(SELECT DISTINCT gl.groupid, gp.price
  FROM t4 gl 
       LEFT JOIN
       (t3 g INNER JOIN t2 p ON g.goodsid = p.goods 
             INNER JOIN t1 gp ON p.goods = gp.goods)
       ON gl.groupid = g.groupid and p.shop = 'fr') t;

CREATE VIEW v1 AS
SELECT g.groupid groupid, p.goods goods,  
       p.name name, p.shop shop, 
       gp.price price
  FROM t3 g INNER JOIN t2 p ON g.goodsid = p.goods
            INNER JOIN t1 gp on p.goods = gp.goods;

CREATE VIEW v2 AS
SELECT DISTINCT g.groupid, fr.price
  FROM t4 g
       LEFT JOIN
       v1 fr on g.groupid = fr.groupid and fr.shop = 'fr';

--sorted_result
SELECT * FROM v2;

--sorted_result
SELECT * FROM 
(SELECT DISTINCT g.groupid, fr.price
  FROM t4 g
       LEFT JOIN
       v1 fr on g.groupid = fr.groupid and fr.shop = 'fr') t;

DROP VIEW v1,v2;
DROP TABLE t1,t2,t3,t4;

--echo *****************
--echo ** End Test #6 **
--echo *****************
--echo
--echo ******************
--echo ** Join Test #7 **
--echo **   Natural    **
--echo ******************
--echo
--echo ** Create Tables **
--echo

#
#  NOTICE:
#  - The tests are designed so that all statements, except MySQL
#    extensions run on any SQL server. Please do no change.
#  - Tests marked with TODO will be submitted as bugs.
#

eval create table t1 (c int, b int)ENGINE=$engine_type;
eval create table t2 (a int, b int)ENGINE=$engine_type;
eval create table t3 (b int, c int)ENGINE=$engine_type;
eval create table t4 (y int, c int)ENGINE=$engine_type;
eval create table t5 (y int, z int)ENGINE=$engine_type;
eval create table t6 (a int, c int)ENGINE=$engine_type;

insert into t1 values (10,1);
insert into t1 values (3 ,1);
insert into t1 values (3 ,2);
insert into t2 values (2, 1);
insert into t3 values (1, 3);
insert into t3 values (1,10);
insert into t4 values (11,3);
insert into t4 values (2, 3);
insert into t5 values (11,4);
insert into t6 values (2, 3);

# Views with simple natural join.
create algorithm=merge view v1a as
select * from t1 natural join t2;
# as above, but column names are cross-renamed: a->c, c->b, b->a
create algorithm=merge view v1b(a,b,c) as
select * from t1 natural join t2;
# as above, but column names are aliased: a->c, c->b, b->a
create algorithm=merge view v1c as
select b as a, c as b, a as c from t1 natural join t2;
#  as above, but column names are cross-renamed, and aliased
#  a->c->b, c->b->a, b->a->c
create algorithm=merge view v1d(b, a, c) as
select a as c, c as b, b as a from t1 natural join t2;

# Views with JOIN ... ON
create algorithm=merge view v2a as
select t1.c, t1.b, t2.a from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
create algorithm=merge view v2b as
select t1.c as b, t1.b as a, t2.a as c
from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;

# Views with bigger natural join
create algorithm=merge view v3a as
select * from t1 natural join t2 natural join t3;
create algorithm=merge view v3b as
select * from t1 natural join (t2 natural join t3);

# View over views with mixed natural join and join ... on
create algorithm=merge view v4 as
select * from v2a natural join v3a;

# Nested natural/using joins.
--sorted_result
select * from (t1 natural join t2) natural join (t3 natural join t4);
--sorted_result
select * from (t1 natural join t2) natural left join (t3 natural join t4);
--sorted_result
select * from (t3 natural join t4) natural right join (t1 natural join t2);
--sorted_result
select * from (t1 natural left join t2) natural left join (t3 natural left join t4);
--sorted_result
select * from (t4 natural right join t3) natural right join (t2 natural right join t1);
--sorted_result
select * from t1 natural join t2 natural join t3 natural join t4;
--sorted_result
select * from ((t1 natural join t2) natural join t3) natural join t4;
--sorted_result
select * from t1 natural join (t2 natural join (t3 natural join t4));
--sorted_result
select * from t5 natural right join (t4 natural right join ((t2 natural right join t1) natural right join t3));
--sorted_result
select * from (t1 natural join t2), (t3 natural join t4);

# MySQL extension - nested comma ',' operator instead of cross join.
--sorted_result
select * from t5 natural join ((t1 natural join t2), (t3 natural join t4));
--sorted_result
select * from  ((t1 natural join t2),  (t3 natural join t4)) natural join t5;
--sorted_result
select * from t5 natural join ((t1 natural join t2) cross join (t3 natural join t4));
--sorted_result
select * from  ((t1 natural join t2) cross join (t3 natural join t4)) natural join t5;

--sorted_result
select * from (t1 join t2 using (b)) join (t3 join t4 using (c)) using (c);
--sorted_result
select * from (t1 join t2 using (b)) natural join (t3 join t4 using (c));


# Other clauses refer to NJ columns.
--sorted_result
select a,b,c from (t1 natural join t2) natural join (t3 natural join t4)
where b + 1 = y or b + 10 = y group by b,c,a having min(b) < max(y) order by a;
--sorted_result
select * from (t1 natural join t2) natural left join (t3 natural join t4)
where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y;
--sorted_result
select * from (t3 natural join t4) natural right join (t1 natural join t2)
where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y;

# Qualified column references to NJ columns.
--sorted_result
select * from t1 natural join t2 where t1.c > t2.a;
--sorted_result
select * from t1 natural join t2 where t1.b > t2.b;
--sorted_result
select * from t1 natural left join (t4 natural join t5) where t5.z is not NULL;

# Nested 'join ... on' - name resolution of ON conditions
--sorted_result
select * from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
--sorted_result
select * from (t2 join t4 on b + 1 = y) join t1 on t1.c = t4.c;
--sorted_result
select * from t1 natural join (t2 join t4 on b + 1 = y);
--sorted_result
select * from (t1 cross join t2) join (t3 cross join t4) on (a < y and t2.b < t3.c);

# MySQL extension - 'join ... on' over nested comma operator
--sorted_result
select * from (t1, t2) join (t3, t4) on (a < y and t2.b < t3.c);
--sorted_result
select * from (t1 natural join t2) join (t3 natural join t4) on a = y;
--sorted_result
select * from ((t3 join (t1 join t2 on c > a) on t3.b < t2.a) join t4 on y > t1.c) join t5 on z = t1.b + 3;

# MySQL extension - refererence qualified coalesced columns
--sorted_result
select * from t1 natural join t2 where t1.b > 0;
--sorted_result
select * from t1 natural join (t4 natural join t5) where t4.y > 7;
--sorted_result
select * from (t4 natural join t5) natural join t1 where t4.y > 7;
--sorted_result
select * from t1 natural left join (t4 natural join t5) where t4.y > 7;
--sorted_result
select * from (t4 natural join t5) natural right join t1 where t4.y > 7;
--sorted_result
select * from (t1 natural join t2) join (t3 natural join t4) on t1.b = t3.b;

# MySQL extension - select qualified columns of NJ columns
--sorted_result
select t1.*, t2.* from t1 natural join t2;
--sorted_result
select t1.*, t2.*, t3.*, t4.* from (t1 natural join t2) natural join (t3 natural join t4);

# Queries over subselects in the FROM clause
--sorted_result
select * from (select * from t1 natural join t2) as t12
              natural join
              (select * from t3 natural join t4) as t34;
--sorted_result
select * from (select * from t1 natural join t2) as t12
              natural left join
              (select * from t3 natural join t4) as t34;
--sorted_result
select * from (select * from t3 natural join t4) as t34
              natural right join
              (select * from t1 natural join t2) as t12;


#--------------------------------------------------------------------
# Negative tests (tests for errors)
#--------------------------------------------------------------------
# works in Oracle - bug
-- error 1052
select * from t1 natural join (t3 cross join t4);
# works in Oracle - bug
-- error 1052
select * from (t3 cross join t4) natural join t1;
-- error 1052
select * from t1 join (t2, t3) using (b);
-- error 1052
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;
-- error 1052
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;
-- error 1052
select * from t6 natural join ((t1 natural join t2),  (t3 natural join t4));
-- error 1052
select * from (t1 join t2 on t1.b=t2.b) natural join (t3 natural join t4);
-- error 1052
select * from  (t3 natural join t4) natural join (t1 join t2 on t1.b=t2.b);
# this one is OK, the next equivalent one is incorrect (bug in Oracle)
-- error 1052
select * from (t3 join (t4 natural join t5) on (b < z))
              natural join
              (t1 natural join t2);
-- error 1052
select * from (t1 natural join t2) natural join (t3 join (t4 natural join t5) on (b < z));

-- error 1054
select t1.b from v1a;
-- error 1054
select * from v1a join v1b on t1.b = t2.b;

#
# Bug #17523 natural join and information_schema
#
# We mask out the Privileges column because it differs with embedded server
--replace_column 32 #
query_vertical 
select * from information_schema.statistics join information_schema.columns
              using(table_name,column_name) where table_name='user';

drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop table t5;
drop table t6;

drop view v1a;
drop view v1b;
drop view v1c;
drop view v1d;
drop view v2a;
drop view v2b;
drop view v3a;
drop view v3b;
drop view v4;

#
# BUG#15229 - columns of nested joins that are not natural joins incorrectly
# materialized
#
eval create table t1 (a1 int, a2 int)ENGINE=$engine_type;
eval create table t2 (a1 int, b int)ENGINE=$engine_type;
eval create table t3 (c1 int, c2 int)ENGINE=$engine_type;
eval create table t4 (c2 int)ENGINE=$engine_type;

insert into t1 values (1,1);
insert into t2 values (1,1);
insert into t3 values (1,1);
insert into t4 values (1);

--sorted_result
select * from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2);
--sorted_result
select * from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2);
--sorted_result
select a2 from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2);
--sorted_result
select a2 from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2);
--sorted_result
select a2 from ((t1 join t2 using (a1)) join t3 on b=c1) join t4 using (c2);
--sorted_result
select a2 from ((t1 natural join t2) join t3 on b=c1) natural join t4;

drop table t1,t2,t3,t4;


#
# BUG#25106: A USING clause in combination with a VIEW results in column 
#            aliases ignored
#
eval CREATE TABLE t1 (ID INTEGER, Name VARCHAR(50))ENGINE=$engine_type;
eval CREATE TABLE t2 (Test_ID INTEGER)ENGINE=$engine_type;
CREATE VIEW v1 (Test_ID, Description) AS SELECT ID, Name FROM t1;

CREATE TABLE tv1 SELECT Description AS Name FROM v1 JOIN t2
 USING (Test_ID);
DESCRIBE tv1;
CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2
 ON v1.Test_ID = t2.Test_ID;
DESCRIBE tv2;

DROP VIEW v1;
DROP TABLE t1,t2,tv1,tv2;


# BUG#27939: Early NULLs filtering doesn't work for eq_ref access
eval create table t1 (a int, b int)ENGINE=$engine_type;
insert into t1 values 
  (NULL, 1),
  (NULL, 2),
  (NULL, 3),
  (NULL, 4);

eval create table t2 (a int not null, primary key(a))ENGINE=$engine_type;
insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

eval create table t3 (a int not null, primary key(a))ENGINE=$engine_type;
insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

flush status;
--sorted_result
select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
--echo We expect rnd_next=5, and read_key must be 0 because of short-cutting:
show status like 'Handler_read%'; 
drop table t1, t2, t3;

#
# BUG#14940: Make E(#rows) from "range" access be re-used by range optimizer
#
eval create table t1 (a int)ENGINE=$engine_type;
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

eval create table t2 (a int, b int, filler char(100), key(a), key(b))ENGINE=$engine_type;
eval create table t3 (a int, b int, filler char(100), key(a), key(b))ENGINE=$engine_type;

insert into t2 
  select @a:= A.a + 10*(B.a + 10*C.a), @a, 'filler' from t1 A, t1 B, t1 C;
insert into t3 select * from t2 where a < 800;

# The order of tables must be t2,t3:
explain select * from t2,t3 where t2.a < 200 and t2.b=t3.b;

drop table t1, t2, t3;