SET join_cache_level=0; SHOW VARIABLES LIKE "%join_cache%"; Variable_name Value join_cache_level 0 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 1 3 3 3 NULL NULL NULL NULL 1 1 1 2 1 1 NULL NULL 1 3 3 3 NULL NULL NULL NULL 1 1 3 2 1 1 NULL NULL 1 3 3 3 NULL NULL NULL NULL 2 2 1 2 2 2 0 2 1 3 3 3 NULL NULL NULL NULL 2 2 1 2 2 2 1 2 1 3 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 1 3 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 1 3 3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 3 4 2 1 2 3 2 1 1 1 2 1 1 NULL NULL 1 3 4 2 1 2 3 2 1 1 3 2 1 1 NULL NULL 1 3 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 3 4 2 1 2 4 2 1 1 1 2 1 1 NULL NULL 1 3 4 2 1 2 4 2 1 1 3 2 1 1 NULL NULL 1 3 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 3 5 3 NULL NULL NULL NULL 1 1 1 2 1 1 NULL NULL 1 3 5 3 NULL NULL NULL NULL 1 1 3 2 1 1 NULL NULL 1 3 5 3 NULL NULL NULL NULL 2 2 1 2 2 2 0 2 1 3 5 3 NULL NULL NULL NULL 2 2 1 2 2 2 1 2 1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 1 3 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 2 3 3 NULL NULL NULL NULL 1 1 1 2 1 1 NULL NULL 3 2 3 3 NULL NULL NULL NULL 1 1 3 2 1 1 NULL NULL 3 2 3 3 NULL NULL NULL NULL 2 2 1 2 2 2 0 2 3 2 3 3 NULL NULL NULL NULL 2 2 1 2 2 2 1 2 3 2 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 3 2 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 3 2 3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 3 2 4 2 1 2 3 2 1 1 1 2 1 1 NULL NULL 3 2 4 2 1 2 3 2 1 1 3 2 1 1 NULL NULL 3 2 4 2 1 2 3 2 2 2 1 2 2 2 0 2 3 2 4 2 1 2 3 2 2 2 1 2 2 2 1 2 3 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2 3 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 3 2 4 2 1 2 4 2 1 1 1 2 1 1 NULL NULL 3 2 4 2 1 2 4 2 1 1 3 2 1 1 NULL NULL 3 2 4 2 1 2 4 2 2 2 1 2 2 2 0 2 3 2 4 2 1 2 4 2 2 2 1 2 2 2 1 2 3 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2 3 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 3 2 5 3 NULL NULL NULL NULL 1 1 1 2 1 1 NULL NULL 3 2 5 3 NULL NULL NULL NULL 1 1 3 2 1 1 NULL NULL 3 2 5 3 NULL NULL NULL NULL 2 2 1 2 2 2 0 2 3 2 5 3 NULL NULL NULL NULL 2 2 1 2 2 2 1 2 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 3 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) 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 1 3 4 2 1 2 3 2 1 1 1 2 1 1 NULL NULL 1 3 4 2 1 2 3 2 1 1 3 2 1 1 NULL NULL 1 3 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 3 4 2 1 2 4 2 1 1 1 2 1 1 NULL NULL 1 3 4 2 1 2 4 2 1 1 3 2 1 1 NULL NULL 1 3 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 3 5 3 NULL NULL NULL NULL 1 1 1 2 1 1 NULL NULL 1 3 5 3 NULL NULL NULL NULL 1 1 3 2 1 1 NULL NULL 1 3 5 3 NULL NULL NULL NULL 2 2 1 2 2 2 0 2 1 3 5 3 NULL NULL NULL NULL 2 2 1 2 2 2 1 2 1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 1 3 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 2 4 2 1 2 3 2 1 1 1 2 1 1 NULL NULL 3 2 4 2 1 2 3 2 1 1 3 2 1 1 NULL NULL 3 2 4 2 1 2 3 2 2 2 1 2 2 2 0 2 3 2 4 2 1 2 3 2 2 2 1 2 2 2 1 2 3 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2 3 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 3 2 4 2 1 2 4 2 1 1 1 2 1 1 NULL NULL 3 2 4 2 1 2 4 2 1 1 3 2 1 1 NULL NULL 3 2 4 2 1 2 4 2 2 2 1 2 2 2 0 2 3 2 4 2 1 2 4 2 2 2 1 2 2 2 1 2 3 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2 3 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 3 2 5 3 NULL NULL NULL NULL 1 1 1 2 1 1 NULL NULL 3 2 5 3 NULL NULL NULL NULL 1 1 3 2 1 1 NULL NULL 3 2 5 3 NULL NULL NULL NULL 2 2 1 2 2 2 0 2 3 2 5 3 NULL NULL NULL NULL 2 2 1 2 2 2 1 2 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 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 0 NULL NULL 1 NULL NULL 10 NULL NULL 11 NULL NULL 12 NULL NULL 13 NULL NULL 14 NULL NULL 15 NULL NULL 16 NULL NULL 17 NULL NULL 18 NULL NULL 19 NULL NULL 2 NULL NULL 3 NULL NULL 4 NULL NULL 5 NULL NULL 6 NULL NULL 7 NULL NULL 8 NULL NULL 9 NULL NULL 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;