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

-- source include/have_ndb.inc
let $engine_type=ndbcluster;

SET join_cache_level=6;
SHOW VARIABLES LIKE "%join_cache%";
--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 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 (201,0,'TravelMate',88,NULL,NULL,NULL,'');
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

DROP TABLE t1,t2,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 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;

--echo
--echo ** Insert Data **
--echo
--disable_query_log
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);
--enable_query_log

--echo
--echo ** Run selects **
--echo
--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);



DROP TABLE t5,t6,t7,t8;