SET @@optimizer_switch='no_bka'; DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; ****************** ** Join Test #1 ** ****************** ** Create Table t1 ** 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=ndbcluster; ** Create Table t2 ** 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=ndbcluster; ** Create Table t3 ** 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=ndbcluster; ** Create Table t4 ** 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=ndbcluster; ** Insert Data ** ** The query that fails... ** 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; project_id project_name client_ptr comments total_budget 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; project_id project_name client_ptr comments total_budget DROP TABLE t1,t2,t3,t4; ***************** ** End Test #1 ** ***************** ****************** ** Join Test #2 ** ** Left Joins ** ****************** ** Create Tables ** CREATE TABLE t0 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b))ENGINE=ndbcluster; CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b))ENGINE=ndbcluster; CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b))ENGINE=ndbcluster; CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b))ENGINE=ndbcluster; CREATE TABLE t4 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b))ENGINE=ndbcluster; CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b))ENGINE=ndbcluster; CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b))ENGINE=ndbcluster; CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b))ENGINE=ndbcluster; CREATE TABLE t8 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b))ENGINE=ndbcluster; CREATE TABLE t9 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b))ENGINE=ndbcluster; ** Insert Data ** ** Run selects ** SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b FROM t2 LEFT JOIN (t3, t4) ON t2.b=t4.b; a b a b a b 3 3 NULL NULL NULL NULL 4 2 1 2 3 2 4 2 1 2 4 2 4 2 2 2 3 2 4 2 2 2 4 2 5 3 NULL NULL NULL NULL 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; a b a b a b 3 3 NULL NULL NULL NULL 4 2 1 2 3 2 4 2 1 2 4 2 5 3 NULL NULL NULL NULL 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; a b a b a b 3 3 NULL NULL NULL NULL 4 2 1 2 3 2 4 2 1 2 4 2 5 3 NULL NULL NULL NULL 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; a b a b a b a b 3 3 NULL NULL NULL NULL NULL NULL 4 2 1 2 3 2 1 1 4 2 1 2 3 2 2 2 4 2 1 2 3 2 3 3 4 2 1 2 4 2 1 1 4 2 1 2 4 2 2 2 4 2 1 2 4 2 3 3 4 2 2 2 3 2 1 1 4 2 2 2 3 2 2 2 4 2 2 2 3 2 3 3 4 2 2 2 4 2 1 1 4 2 2 2 4 2 2 2 4 2 2 2 4 2 3 3 5 3 NULL NULL NULL NULL NULL NULL 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; a b a b a b a b 3 3 NULL NULL NULL NULL NULL NULL 4 2 2 2 3 2 1 1 4 2 2 2 3 2 2 2 4 2 2 2 3 2 3 3 4 2 2 2 4 2 1 1 4 2 2 2 4 2 2 2 4 2 2 2 4 2 3 3 5 3 NULL NULL NULL NULL NULL NULL 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); a b a b a b a b 3 3 NULL NULL NULL NULL NULL NULL 4 2 2 2 3 2 1 1 4 2 2 2 3 2 2 2 4 2 2 2 4 2 1 1 4 2 2 2 4 2 2 2 5 3 NULL NULL NULL NULL NULL NULL 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; a b a b a b 1 2 1 1 NULL NULL 1 2 2 2 0 2 1 2 2 2 1 2 3 2 1 1 NULL NULL 3 2 2 2 0 2 3 2 2 2 1 2 6 1 1 1 NULL NULL 6 1 2 2 0 2 6 1 2 2 1 2 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; a b a b a b a b 1 1 1 2 1 1 NULL NULL 1 1 3 2 1 1 NULL NULL 2 2 1 2 2 2 0 2 2 2 1 2 2 2 1 2 2 2 3 2 2 2 0 2 2 2 3 2 2 2 1 2 3 3 NULL NULL NULL NULL NULL NULL 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); a b a b a b a b 1 1 1 2 1 1 NULL NULL 1 1 3 2 1 1 NULL NULL 2 2 1 2 2 2 0 2 2 2 3 2 2 2 0 2 3 3 NULL NULL NULL NULL NULL NULL 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; a b a b a b 3 3 NULL NULL NULL NULL 4 2 1 2 3 2 4 2 1 2 4 2 5 3 NULL NULL NULL NULL 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; a b a b a b a b a b a b a b 3 3 NULL NULL NULL NULL 1 1 1 2 1 1 NULL NULL 3 3 NULL NULL NULL NULL 1 1 3 2 1 1 NULL NULL 3 3 NULL NULL NULL NULL 2 2 1 2 2 2 0 2 3 3 NULL NULL NULL NULL 2 2 1 2 2 2 1 2 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 4 2 1 2 3 2 1 1 1 2 1 1 NULL NULL 4 2 1 2 3 2 1 1 3 2 1 1 NULL NULL 4 2 1 2 3 2 2 2 1 2 2 2 0 2 4 2 1 2 3 2 2 2 1 2 2 2 1 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 4 2 1 2 4 2 1 1 1 2 1 1 NULL NULL 4 2 1 2 4 2 1 1 3 2 1 1 NULL NULL 4 2 1 2 4 2 2 2 1 2 2 2 0 2 4 2 1 2 4 2 2 2 1 2 2 2 1 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 5 3 NULL NULL NULL NULL 1 1 1 2 1 1 NULL NULL 5 3 NULL NULL NULL NULL 1 1 3 2 1 1 NULL NULL 5 3 NULL NULL NULL NULL 2 2 1 2 2 2 0 2 5 3 NULL NULL NULL NULL 2 2 1 2 2 2 1 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 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); a b a b a b a b a b a b a b 4 2 1 2 3 2 1 1 1 2 1 1 NULL NULL 4 2 1 2 3 2 1 1 3 2 1 1 NULL NULL 4 2 1 2 3 2 2 2 1 2 2 2 0 2 4 2 1 2 3 2 2 2 1 2 2 2 1 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 4 2 1 2 4 2 1 1 1 2 1 1 NULL NULL 4 2 1 2 4 2 1 1 3 2 1 1 NULL NULL 4 2 1 2 4 2 2 2 1 2 2 2 0 2 4 2 1 2 4 2 2 2 1 2 2 2 1 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 5 3 NULL NULL NULL NULL 1 1 1 2 1 1 NULL NULL 5 3 NULL NULL NULL NULL 1 1 3 2 1 1 NULL NULL 5 3 NULL NULL NULL NULL 2 2 1 2 2 2 0 2 5 3 NULL NULL NULL NULL 2 2 1 2 2 2 1 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 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); a b a b a b a b a b a b a b a b 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); a b a b a b a b a b a b a b a b 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); a b a b a b a b a b a b a b a b a b 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); a b a b a b a b a b a b a b a b a b a b 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); a b a b a b a b a b a b a b a b a b a b DROP TABLE t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; ***************** ** End Test #2 ** ***************** ****************** ** Join Test #3 ** ** Nested Joins ** ****************** ** Create Tables ** CREATE TABLE t1 (a int, PRIMARY KEY(a))ENGINE=ndbcluster; CREATE TABLE t2 (a int, PRIMARY KEY(a))ENGINE=ndbcluster; CREATE TABLE t3 (a int, PRIMARY KEY(a))ENGINE=ndbcluster; 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); SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a=t3.a) ON t1.a=t3.a; a a a 1 NULL NULL 2 2 2 3 3 3 SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a; a a a 1 NULL NULL 2 2 2 3 3 3 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; a a a 2 2 2 DELETE FROM t2; SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a; a a a 2 NULL NULL DROP TABLE t1,t2,t3; ***************** ** End Test #3 ** ***************** ****************** ** Join Test #4 ** ** On Joins ** ****************** ** Create Tables ** CREATE TABLE t1(a int, PRIMARY KEY(a))ENGINE=ndbcluster; CREATE TABLE t2(b int, PRIMARY KEY(b))ENGINE=ndbcluster; CREATE TABLE t3(c int, PRIMARY KEY(c))ENGINE=ndbcluster; 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); SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON c < 3 and b = c; a b c 0 0 0 0 1 1 0 2 2 1 0 0 1 1 1 1 2 2 10 0 0 10 1 1 10 2 2 11 0 0 11 1 1 11 2 2 12 0 0 12 1 1 12 2 2 13 0 0 13 1 1 13 2 2 14 0 0 14 1 1 14 2 2 15 0 0 15 1 1 15 2 2 16 0 0 16 1 1 16 2 2 17 0 0 17 1 1 17 2 2 18 0 0 18 1 1 18 2 2 19 0 0 19 1 1 19 2 2 2 0 0 2 1 1 2 2 2 3 0 0 3 1 1 3 2 2 4 0 0 4 1 1 4 2 2 5 0 0 5 1 1 5 2 2 6 0 0 6 1 1 6 2 2 7 0 0 7 1 1 7 2 2 8 0 0 8 1 1 8 2 2 9 0 0 9 1 1 9 2 2 SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c 0 0 0 0 1 1 0 2 2 1 0 0 1 1 1 1 2 2 10 0 0 10 1 1 10 2 2 11 0 0 11 1 1 11 2 2 12 0 0 12 1 1 12 2 2 13 0 0 13 1 1 13 2 2 14 0 0 14 1 1 14 2 2 15 0 0 15 1 1 15 2 2 16 0 0 16 1 1 16 2 2 17 0 0 17 1 1 17 2 2 18 0 0 18 1 1 18 2 2 19 0 0 19 1 1 19 2 2 2 0 0 2 1 1 2 2 2 3 0 0 3 1 1 3 2 2 4 0 0 4 1 1 4 2 2 5 0 0 5 1 1 5 2 2 6 0 0 6 1 1 6 2 2 7 0 0 7 1 1 7 2 2 8 0 0 8 1 1 8 2 2 9 0 0 9 1 1 9 2 2 DELETE FROM t3; SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c DROP TABLE t1,t2,t3; ***************** ** End Test #4 ** ***************** ****************** ** Join Test #5 ** ** Empty Table ** ****************** ** Create Tables ** CREATE TABLE t1 (c11 int, PRIMARY KEY(c11))ENGINE=ndbcluster; CREATE TABLE t2 (c21 int, PRIMARY KEY(c21))ENGINE=ndbcluster; CREATE TABLE t3 (c31 int, PRIMARY KEY(c31))ENGINE=ndbcluster; INSERT INTO t1 VALUES (4), (5); SELECT * FROM t1 LEFT JOIN t2 ON c11=c21; c11 c21 4 NULL 5 NULL SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21; c11 c21 c31 4 NULL NULL 5 NULL NULL DROP TABLE t1,t2,t3; ***************** ** End Test #5 ** ***************** ****************** ** Join Test #6 ** ** Empty Table ** ****************** ** Create Tables ** CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL)ENGINE=ndbcluster; CREATE TABLE t2 (goods int(12), name varchar(50), shop char(2))ENGINE=ndbcluster; CREATE TABLE t3 (groupid int(12) NOT NULL, goodsid int(12) NOT NULL)ENGINE=ndbcluster; CREATE TABLE t4 (groupid int(12))ENGINE=ndbcluster; 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); 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; groupid price 1 NULL 2 NULL 3 2340 4 NULL 5 NULL 6 9900 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'; SELECT * FROM v2; groupid price 1 NULL 2 NULL 3 2340 4 NULL 5 NULL 6 9900 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; groupid price 1 NULL 2 NULL 3 2340 4 NULL 5 NULL 6 9900 DROP VIEW v1,v2; DROP TABLE t1,t2,t3,t4; ***************** ** End Test #6 ** ***************** ****************** ** Join Test #7 ** ** Natural ** ****************** ** Create Tables ** create table t1 (c int, b int)ENGINE=ndbcluster; create table t2 (a int, b int)ENGINE=ndbcluster; create table t3 (b int, c int)ENGINE=ndbcluster; create table t4 (y int, c int)ENGINE=ndbcluster; create table t5 (y int, z int)ENGINE=ndbcluster; create table t6 (a int, c int)ENGINE=ndbcluster; 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); create algorithm=merge view v1a as select * from t1 natural join t2; create algorithm=merge view v1b(a,b,c) as select * from t1 natural join t2; create algorithm=merge view v1c as select b as a, c as b, a as c from t1 natural join t2; create algorithm=merge view v1d(b, a, c) as select a as c, c as b, b as a from t1 natural join t2; 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; 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); create algorithm=merge view v4 as select * from v2a natural join v3a; select * from (t1 natural join t2) natural join (t3 natural join t4); b c a y 1 3 2 11 1 3 2 2 select * from (t1 natural join t2) natural left join (t3 natural join t4); b c a y 1 10 2 NULL 1 3 2 11 1 3 2 2 select * from (t3 natural join t4) natural right join (t1 natural join t2); b c a y 1 10 2 NULL 1 3 2 11 1 3 2 2 select * from (t1 natural left join t2) natural left join (t3 natural left join t4); b c a y 1 10 2 NULL 1 3 2 11 1 3 2 2 2 3 NULL NULL select * from (t4 natural right join t3) natural right join (t2 natural right join t1); b c a y 1 10 2 NULL 1 3 2 11 1 3 2 2 2 3 NULL NULL select * from t1 natural join t2 natural join t3 natural join t4; c b a y 3 1 2 11 3 1 2 2 select * from ((t1 natural join t2) natural join t3) natural join t4; c b a y 3 1 2 11 3 1 2 2 select * from t1 natural join (t2 natural join (t3 natural join t4)); c b a y 3 1 2 11 3 1 2 2 select * from t5 natural right join (t4 natural right join ((t2 natural right join t1) natural right join t3)); y c b a z 11 3 1 2 4 2 3 1 2 NULL NULL 10 1 2 NULL select * from (t1 natural join t2), (t3 natural join t4); b c a c b y 1 10 2 3 1 11 1 10 2 3 1 2 1 3 2 3 1 11 1 3 2 3 1 2 select * from t5 natural join ((t1 natural join t2), (t3 natural join t4)); y z b c a c b 11 4 1 10 2 3 1 11 4 1 3 2 3 1 select * from ((t1 natural join t2), (t3 natural join t4)) natural join t5; y b c a c b z 11 1 10 2 3 1 4 11 1 3 2 3 1 4 select * from t5 natural join ((t1 natural join t2) cross join (t3 natural join t4)); y z b c a c b 11 4 1 10 2 3 1 11 4 1 3 2 3 1 select * from ((t1 natural join t2) cross join (t3 natural join t4)) natural join t5; y b c a c b z 11 1 10 2 3 1 4 11 1 3 2 3 1 4 select * from (t1 join t2 using (b)) join (t3 join t4 using (c)) using (c); c b a b y 3 1 2 1 11 3 1 2 1 2 select * from (t1 join t2 using (b)) natural join (t3 join t4 using (c)); b c a y 1 3 2 11 1 3 2 2 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; a b c 2 1 3 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; b c a y 1 3 2 11 1 3 2 2 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; b c a y 1 3 2 11 1 3 2 2 select * from t1 natural join t2 where t1.c > t2.a; b c a 1 10 2 1 3 2 select * from t1 natural join t2 where t1.b > t2.b; b c a select * from t1 natural left join (t4 natural join t5) where t5.z is not NULL; c b y z 3 1 11 4 3 2 11 4 select * from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c; c b a b y c 3 1 2 1 2 3 3 2 2 1 2 3 select * from (t2 join t4 on b + 1 = y) join t1 on t1.c = t4.c; a b y c c b 2 1 2 3 3 1 2 1 2 3 3 2 select * from t1 natural join (t2 join t4 on b + 1 = y); c b a y 3 1 2 2 select * from (t1 cross join t2) join (t3 cross join t4) on (a < y and t2.b < t3.c); c b a b b c y c 10 1 2 1 1 10 11 3 10 1 2 1 1 3 11 3 3 1 2 1 1 10 11 3 3 1 2 1 1 3 11 3 3 2 2 1 1 10 11 3 3 2 2 1 1 3 11 3 select * from (t1, t2) join (t3, t4) on (a < y and t2.b < t3.c); c b a b b c y c 10 1 2 1 1 10 11 3 10 1 2 1 1 3 11 3 3 1 2 1 1 10 11 3 3 1 2 1 1 3 11 3 3 2 2 1 1 10 11 3 3 2 2 1 1 3 11 3 select * from (t1 natural join t2) join (t3 natural join t4) on a = y; b c a c b y 1 10 2 3 1 2 1 3 2 3 1 2 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; b c c b a b y c y z 1 10 10 1 2 1 11 3 11 4 1 10 3 1 2 1 11 3 11 4 1 3 10 1 2 1 11 3 11 4 1 3 3 1 2 1 11 3 11 4 select * from t1 natural join t2 where t1.b > 0; b c a 1 10 2 1 3 2 select * from t1 natural join (t4 natural join t5) where t4.y > 7; c b y z 3 1 11 4 3 2 11 4 select * from (t4 natural join t5) natural join t1 where t4.y > 7; c y z b 3 11 4 1 3 11 4 2 select * from t1 natural left join (t4 natural join t5) where t4.y > 7; c b y z 3 1 11 4 3 2 11 4 select * from (t4 natural join t5) natural right join t1 where t4.y > 7; c b y z 3 1 11 4 3 2 11 4 select * from (t1 natural join t2) join (t3 natural join t4) on t1.b = t3.b; b c a c b y 1 10 2 3 1 11 1 10 2 3 1 2 1 3 2 3 1 11 1 3 2 3 1 2 select t1.*, t2.* from t1 natural join t2; c b a b 10 1 2 1 3 1 2 1 select t1.*, t2.*, t3.*, t4.* from (t1 natural join t2) natural join (t3 natural join t4); c b a b b c y c 3 1 2 1 1 3 11 3 3 1 2 1 1 3 2 3 select * from (select * from t1 natural join t2) as t12 natural join (select * from t3 natural join t4) as t34; b c a y 1 3 2 11 1 3 2 2 select * from (select * from t1 natural join t2) as t12 natural left join (select * from t3 natural join t4) as t34; b c a y 1 10 2 NULL 1 3 2 11 1 3 2 2 select * from (select * from t3 natural join t4) as t34 natural right join (select * from t1 natural join t2) as t12; b c a y 1 10 2 NULL 1 3 2 11 1 3 2 2 select * from t1 natural join (t3 cross join t4); ERROR 23000: Column 'c' in from clause is ambiguous select * from (t3 cross join t4) natural join t1; ERROR 23000: Column 'c' in from clause is ambiguous select * from t1 join (t2, t3) using (b); ERROR 23000: Column 'b' in from clause is ambiguous select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6; ERROR 23000: Column 'c' in from clause is ambiguous select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6; ERROR 23000: Column 'c' in from clause is ambiguous select * from t6 natural join ((t1 natural join t2), (t3 natural join t4)); ERROR 23000: Column 'c' in from clause is ambiguous select * from (t1 join t2 on t1.b=t2.b) natural join (t3 natural join t4); ERROR 23000: Column 'b' in from clause is ambiguous select * from (t3 natural join t4) natural join (t1 join t2 on t1.b=t2.b); ERROR 23000: Column 'b' in from clause is ambiguous select * from (t3 join (t4 natural join t5) on (b < z)) natural join (t1 natural join t2); ERROR 23000: Column 'c' in from clause is ambiguous select * from (t1 natural join t2) natural join (t3 join (t4 natural join t5) on (b < z)); ERROR 23000: Column 'c' in from clause is ambiguous select t1.b from v1a; ERROR 42S22: Unknown column 't1.b' in 'field list' select * from v1a join v1b on t1.b = t2.b; ERROR 42S22: Unknown column 't1.b' in 'on clause' select * from information_schema.statistics join information_schema.columns using(table_name,column_name) where table_name='user'; TABLE_NAME user COLUMN_NAME Host TABLE_CATALOG NULL TABLE_SCHEMA mysql NON_UNIQUE 0 INDEX_SCHEMA mysql INDEX_NAME PRIMARY SEQ_IN_INDEX 1 COLLATION A CARDINALITY NULL SUB_PART NULL PACKED NULL NULLABLE INDEX_TYPE BTREE COMMENT INDEX_COMMENT TABLE_CATALOG NULL TABLE_SCHEMA mysql ORDINAL_POSITION 1 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE char CHARACTER_MAXIMUM_LENGTH 60 CHARACTER_OCTET_LENGTH 240 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_bin COLUMN_TYPE char(60) COLUMN_KEY PRI EXTRA PRIVILEGES # COLUMN_COMMENT STORAGE Default FORMAT Default TABLE_NAME user COLUMN_NAME User TABLE_CATALOG NULL TABLE_SCHEMA mysql NON_UNIQUE 0 INDEX_SCHEMA mysql INDEX_NAME PRIMARY SEQ_IN_INDEX 2 COLLATION A CARDINALITY 3 SUB_PART NULL PACKED NULL NULLABLE INDEX_TYPE BTREE COMMENT INDEX_COMMENT TABLE_CATALOG NULL TABLE_SCHEMA mysql ORDINAL_POSITION 2 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE char CHARACTER_MAXIMUM_LENGTH 16 CHARACTER_OCTET_LENGTH 64 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_bin COLUMN_TYPE char(16) COLUMN_KEY PRI EXTRA PRIVILEGES # COLUMN_COMMENT STORAGE Default FORMAT Default 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; create table t1 (a1 int, a2 int)ENGINE=ndbcluster; create table t2 (a1 int, b int)ENGINE=ndbcluster; create table t3 (c1 int, c2 int)ENGINE=ndbcluster; create table t4 (c2 int)ENGINE=ndbcluster; insert into t1 values (1,1); insert into t2 values (1,1); insert into t3 values (1,1); insert into t4 values (1); select * from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2); c2 a1 a2 b c1 1 1 1 1 1 select * from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2); c2 c1 a1 a2 b 1 1 1 1 1 select a2 from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2); a2 1 select a2 from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2); a2 1 select a2 from ((t1 join t2 using (a1)) join t3 on b=c1) join t4 using (c2); a2 1 select a2 from ((t1 natural join t2) join t3 on b=c1) natural join t4; a2 1 drop table t1,t2,t3,t4; CREATE TABLE t1 (ID INTEGER, Name VARCHAR(50))ENGINE=ndbcluster; CREATE TABLE t2 (Test_ID INTEGER)ENGINE=ndbcluster; 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; Field Type Null Key Default Extra Name varchar(50) YES NULL CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2 ON v1.Test_ID = t2.Test_ID; DESCRIBE tv2; Field Type Null Key Default Extra Name varchar(50) YES NULL DROP VIEW v1; DROP TABLE t1,t2,tv1,tv2; create table t1 (a int, b int)ENGINE=ndbcluster; insert into t1 values (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4); create table t2 (a int not null, primary key(a))ENGINE=ndbcluster; insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t3 (a int not null, primary key(a))ENGINE=ndbcluster; insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); flush status; select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b; a b a a We expect rnd_next=5, and read_key must be 0 because of short-cutting: show status like 'Handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 5 drop table t1, t2, t3; create table t1 (a int)ENGINE=ndbcluster; insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, b int, filler char(100), key(a), key(b))ENGINE=ndbcluster; create table t3 (a int, b int, filler char(100), key(a), key(b))ENGINE=ndbcluster; 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; explain select * from t2,t3 where t2.a < 200 and t2.b=t3.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range a,b a 5 NULL 10 Using where with pushed condition; Using MRR 1 SIMPLE t3 ref b b 5 test.t2.b 1 drop table t1, t2, t3;