~drizzle-trunk/drizzle/development

drop table if exists t1,t2,t3;
CREATE TABLE t1 (S1 INT);
CREATE TABLE t2 (S1 INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
SELECT * FROM t1 JOIN t2;
S1	S1
1	2
SELECT * FROM t1 INNER JOIN t2;
S1	S1
1	2
SELECT * from t1 JOIN t2 USING (S1);
S1
SELECT * FROM t1 INNER JOIN t2 USING (S1);
S1
SELECT * from t1 CROSS JOIN t2;
S1	S1
1	2
SELECT * from t1 LEFT JOIN t2 USING(S1);
S1
1
SELECT * from t1 LEFT JOIN t2 ON(t2.S1=2);
S1	S1
1	2
SELECT * from t1 RIGHT JOIN t2 USING(S1);
S1
2
SELECT * from t1 RIGHT JOIN t2 ON(t1.S1=1);
S1	S1
1	2
drop table t1,t2;
create table t1 (id int primary key);
create table t2 (id int);
insert into t1 values (75);
insert into t1 values (79);
insert into t1 values (78);
insert into t1 values (77);
replace into t1 values (76);
replace into t1 values (76);
insert into t1 values (104);
insert into t1 values (103);
insert into t1 values (102);
insert into t1 values (101);
insert into t1 values (105);
insert into t1 values (106);
insert into t1 values (107);
insert into t2 values (107),(75),(1000);
select t1.id, t2.id from t1, t2 where t2.id = t1.id;
id	id
107	107
75	75
select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t1.id;
id	count(t2.id)
75	1
107	1
select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t2.id;
id	count(t2.id)
75	1
107	1
select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
id	id
NULL	75
explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	const	PRIMARY	NULL	NULL	NULL	1	Impossible ON condition
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
explain select t1.id, t2.id from t1, t2 where t2.id = t1.id and t1.id <0 and t1.id > 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
drop table t1,t2;
CREATE TABLE t1 (
id int NOT NULL auto_increment,
token varchar(100) DEFAULT '' NOT NULL,
count int DEFAULT '0' NOT NULL,
qty int,
phone char(1) DEFAULT '' NOT NULL,
timestamp datetime,
PRIMARY KEY (id),
KEY token (token(15)),
KEY timestamp (timestamp),
UNIQUE token_2 (token(75),count,phone)
);
INSERT INTO t1 VALUES (21,'e45703b64de71482360de8fec94c3ade',3,7800,'n','1999-12-23 17:22:21');
INSERT INTO t1 VALUES (22,'e45703b64de71482360de8fec94c3ade',4,5000,'y','1999-12-23 17:22:21');
INSERT INTO t1 VALUES (18,'346d1cb63c89285b2351f0ca4de40eda',3,13200,'b','1999-12-23 11:58:04');
INSERT INTO t1 VALUES (17,'ca6ddeb689e1b48a04146b1b5b6f936a',4,15000,'b','1999-12-23 11:36:53');
INSERT INTO t1 VALUES (16,'ca6ddeb689e1b48a04146b1b5b6f936a',3,13200,'b','1999-12-23 11:36:53');
INSERT INTO t1 VALUES (26,'a71250b7ed780f6ef3185bfffe027983',5,1500,'b','1999-12-27 09:44:24');
INSERT INTO t1 VALUES (24,'4d75906f3c37ecff478a1eb56637aa09',3,5400,'y','1999-12-23 17:29:12');
INSERT INTO t1 VALUES (25,'4d75906f3c37ecff478a1eb56637aa09',4,6500,'y','1999-12-23 17:29:12');
INSERT INTO t1 VALUES (27,'a71250b7ed780f6ef3185bfffe027983',3,6200,'b','1999-12-27 09:44:24');
INSERT INTO t1 VALUES (28,'a71250b7ed780f6ef3185bfffe027983',3,5400,'y','1999-12-27 09:44:36');
INSERT INTO t1 VALUES (29,'a71250b7ed780f6ef3185bfffe027983',4,17700,'b','1999-12-27 09:45:05');
CREATE TABLE t2 (
id int NOT NULL auto_increment,
category int DEFAULT '0' NOT NULL,
county int DEFAULT '0' NOT NULL,
state int DEFAULT '0' NOT NULL,
phones int DEFAULT '0' NOT NULL,
nophones int DEFAULT '0' NOT NULL,
PRIMARY KEY (id),
KEY category (category,county,state)
);
INSERT INTO t2 VALUES (3,2,11,12,5400,7800);
INSERT INTO t2 VALUES (4,2,25,12,6500,11200);
INSERT INTO t2 VALUES (5,1,37,6,10000,12000);
select a.id, b.category as catid, b.state as stateid, b.county as countyid from t1 a, t2 b ignore index (primary) where (a.token ='a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id);
id	catid	stateid	countyid
27	2	12	11
28	2	12	11
29	2	12	25
26	1	6	37
select a.id, b.category as catid, b.state as stateid, b.county as
countyid from t1 a, t2 b where (a.token =
'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id) order by a.id;
id	catid	stateid	countyid
26	1	6	37
27	2	12	11
28	2	12	11
29	2	12	25
drop table t1, t2;
create table t1 (a int primary key);
insert into t1 values(1),(2);
select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
a
1
2
select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
ERROR HY000: Too many tables; Drizzle can only use XX tables in a join
select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
a
1
2
select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
ERROR HY000: Too many tables; Drizzle can only use XX tables in a join
drop table t1;
CREATE TEMPORARY TABLE t1 (
a int NOT NULL,
b int NOT NULL,
PRIMARY KEY  (a,b)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(2,3);
CREATE TEMPORARY TABLE t2 (
a int default NULL
) ENGINE=MyISAM;
INSERT INTO t2 VALUES (2),(3);
SELECT t1.a,t2.a,b FROM t1,t2 WHERE t1.a=t2.a AND (t1.a=1 OR t1.a=2) AND b>=1 AND b<=3;
a	a	b
2	2	3
DROP TABLE t1, t2;
CREATE TABLE t1 (d DATE);
CREATE TABLE t2 (d DATE);
INSERT INTO t1 (d) VALUES ('2001-08-01'),(NULL);
SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE t2.d IS NULL;
d
2001-08-01
NULL
SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE d IS NULL;
d
NULL
SELECT * from t1 WHERE t1.d IS NULL;
d
NULL
SELECT * FROM t1 WHERE 1/0 IS NULL;
ERROR 22012: Division by 0
DROP TABLE t1,t2;
CREATE TABLE t1 (
Document_ID varchar(50) NOT NULL default '',
Contractor_ID varchar(6) NOT NULL default '',
Language_ID char(3) NOT NULL default '',
Expiration_Date datetime default NULL,
Publishing_Date datetime default NULL,
Title text,
Column_ID varchar(50) NOT NULL default '',
PRIMARY KEY  (Language_ID,Document_ID,Contractor_ID)
);
INSERT INTO t1 VALUES ('xep80','1','ger','2001-12-31 20:00:00','2001-11-12 10:58:00','Kartenbestellung - jetzt auch online','anle'),('','999998','',NULL,NULL,NULL,'');
CREATE TABLE t2 (
Contractor_ID char(6) NOT NULL default '',
Language_ID char(3) NOT NULL default '',
Document_ID char(50) NOT NULL default '',
CanRead char(1) default NULL,
Customer_ID int NOT NULL default '0',
PRIMARY KEY  (Contractor_ID,Language_ID,Document_ID,Customer_ID)
);
INSERT INTO t2 VALUES ('5','ger','xep80','1',999999),('1','ger','xep80','1',999999);
CREATE TABLE t3 (
Language_ID char(3) NOT NULL default '',
Column_ID char(50) NOT NULL default '',
Contractor_ID char(6) NOT NULL default '',
CanRead char(1) default NULL,
Active char(1) default NULL,
PRIMARY KEY  (Language_ID,Column_ID,Contractor_ID)
);
INSERT INTO t3 VALUES ('ger','home','1','1','1'),('ger','Test','1','0','0'),('ger','derclu','1','0','0'),('ger','clubne','1','0','0'),('ger','philos','1','0','0'),('ger','clubko','1','0','0'),('ger','clubim','1','1','1'),('ger','progra','1','0','0'),('ger','progvo','1','0','0'),('ger','progsp','1','0','0'),('ger','progau','1','0','0'),('ger','progku','1','0','0'),('ger','progss','1','0','0'),('ger','nachl','1','0','0'),('ger','mitgli','1','0','0'),('ger','mitsu','1','0','0'),('ger','mitbus','1','0','0'),('ger','ergmar','1','1','1'),('ger','home','4','1','1'),('ger','derclu','4','1','1'),('ger','clubne','4','0','0'),('ger','philos','4','1','1'),('ger','clubko','4','1','1'),('ger','clubim','4','1','1'),('ger','progra','4','1','1'),('ger','progvo','4','1','1'),('ger','progsp','4','1','1'),('ger','progau','4','0','0'),('ger','progku','4','1','1'),('ger','progss','4','1','1'),('ger','nachl','4','1','1'),('ger','mitgli','4','0','0'),('ger','mitsu','4','0','0'),('ger','mitbus','4','0','0'),('ger','ergmar','4','1','1'),('ger','progra2','1','0','0'),('ger','archiv','4','1','1'),('ger','anmeld','4','1','1'),('ger','thema','4','1','1'),('ger','edito','4','1','1'),('ger','madis','4','1','1'),('ger','enma','4','1','1'),('ger','madis','1','1','1'),('ger','enma','1','1','1'),('ger','vorsch','4','0','0'),('ger','veranst','4','0','0'),('ger','anle','4','1','1'),('ger','redak','4','1','1'),('ger','nele','4','1','1'),('ger','aukt','4','1','1'),('ger','callcenter','4','1','1'),('ger','anle','1','0','0');
delete from t1 where Contractor_ID='999998';
insert into t1 (Contractor_ID) Values ('999998');
SELECT DISTINCT COUNT(t1.Title) FROM t1,
t2, t3 WHERE 
t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND 
t1.Language_ID='ger' AND '2001-12-21 23:14:24' >= 
Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND 
t1.Document_ID = t2.Document_ID AND 
t1.Language_ID = t2.Language_ID AND 
t1.Contractor_ID = t2.Contractor_ID AND ( 
t2.Customer_ID = '4'  OR 
t2.Customer_ID = '999999'  OR 
t2.Customer_ID = '1' )AND t2.CanRead 
= '1'  AND t1.Column_ID=t3.Column_ID AND 
t1.Language_ID=t3.Language_ID AND ( 
t3.Contractor_ID = '4'  OR 
t3.Contractor_ID = '999999'  OR 
t3.Contractor_ID = '1') AND 
t3.CanRead='1' AND t3.Active='1';
COUNT(t1.Title)
1
SELECT DISTINCT COUNT(t1.Title) FROM t1,
t2, t3 WHERE 
t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND 
t1.Language_ID='ger' AND '2001-12-21 23:14:24' >= 
Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND 
t1.Document_ID = t2.Document_ID AND 
t1.Language_ID = t2.Language_ID AND 
t1.Contractor_ID = t2.Contractor_ID AND ( 
t2.Customer_ID = '4'  OR 
t2.Customer_ID = '999999'  OR 
t2.Customer_ID = '1' )AND t2.CanRead 
= '1'  AND t1.Column_ID=t3.Column_ID AND 
t1.Language_ID=t3.Language_ID AND ( 
t3.Contractor_ID = '4'  OR 
t3.Contractor_ID = '999999'  OR 
t3.Contractor_ID = '1') AND 
t3.CanRead='1' AND t3.Active='1';
COUNT(t1.Title)
1
drop table t1,t2,t3;
CREATE TEMPORARY TABLE t1 (
t1_id int default NULL,
t2_id int default NULL,
type enum('Cost','Percent') default NULL,
cost_unit enum('Cost','Unit') default NULL,
min_value double default NULL,
max_value double default NULL,
t3_id int default NULL,
item_id int default NULL
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (12,5,'Percent','Cost',-1,0,-1,-1),(14,4,'Percent','Cost',-1,0,-1,-1),(18,5,'Percent','Cost',-1,0,-1,-1),(19,4,'Percent','Cost',-1,0,-1,-1),(20,5,'Percent','Cost',100,-1,22,291),(21,5,'Percent','Cost',100,-1,18,291),(22,1,'Percent','Cost',100,-1,6,291),(23,1,'Percent','Cost',100,-1,21,291),(24,1,'Percent','Cost',100,-1,9,291),(25,1,'Percent','Cost',100,-1,4,291),(26,1,'Percent','Cost',100,-1,20,291),(27,4,'Percent','Cost',100,-1,7,202),(28,1,'Percent','Cost',50,-1,-1,137),(29,2,'Percent','Cost',100,-1,4,354),(30,2,'Percent','Cost',100,-1,9,137),(93,2,'Cost','Cost',-1,10000000,-1,-1);
CREATE TEMPORARY TABLE t2 (
id int NOT NULL auto_increment,
name varchar(255) default NULL,
PRIMARY KEY  (id)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1,'s1'),(2,'s2'),(3,'s3'),(4,'s4'),(5,'s5');
select t1.*, t2.*  from t1, t2 where t2.id=t1.t2_id limit 2;
t1_id	t2_id	type	cost_unit	min_value	max_value	t3_id	item_id	id	name
22	1	Percent	Cost	100	-1	6	291	1	s1
23	1	Percent	Cost	100	-1	21	291	1	s1
drop table t1,t2;
CREATE TEMPORARY TABLE t1 (
siteid varchar(25) NOT NULL default '',
emp_id varchar(30) NOT NULL default '',
rate_code varchar(10) default NULL,
UNIQUE KEY site_emp (siteid,emp_id),
KEY siteid (siteid)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('rivercats','psmith','cust'), ('rivercats','KWalker','cust');
CREATE TEMPORARY TABLE t2 (
siteid varchar(25) NOT NULL default '',
rate_code varchar(10) NOT NULL default '',
base_rate float NOT NULL default '0',
PRIMARY KEY  (siteid,rate_code)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES ('rivercats','cust',20);
SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND lr.siteid = 'rivercats';
rate_code	base_rate
cust	20
SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE lr.siteid = 'rivercats' AND emp.emp_id = 'psmith';
rate_code	base_rate
cust	20
SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND siteid = 'rivercats';
rate_code	base_rate
cust	20
SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE siteid = 'rivercats' AND emp.emp_id = 'psmith';
rate_code	base_rate
cust	20
drop table t1,t2;
CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255));
CREATE TABLE t2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255));
INSERT INTO t1 VALUES (1, 'A');
INSERT INTO t2 VALUES (1, 'B');
SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND (Value1 = 'A' AND Value2 <> 'B');
ID	Value1	Value2
SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND Value1 = 'A' AND Value2 <> 'B';
ID	Value1	Value2
SELECT * FROM t1 NATURAL JOIN t2 WHERE (Value1 = 'A' AND Value2 <> 'B') AND 1;
ID	Value1	Value2
drop table t1,t2;
CREATE TABLE t1 (a int);
CREATE TABLE t2 (b int);
CREATE TABLE t3 (c int);
SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
a	b	c
DROP TABLE t1, t2, t3;
create table t1 (i int);
create table t2 (i int);
create table t3 (i int);
insert into t1 values(1),(2);
insert into t2 values(2),(3);
insert into t3 values (2),(4);
select * from t1 natural left join t2;
i
1
2
select * from t1 left join t2 on (t1.i=t2.i);
i	i
1	NULL
2	2
select * from t1 natural left join t2 natural left join t3;
i
1
2
select * from t1 left join t2 on (t1.i=t2.i) left join t3 on (t2.i=t3.i);
i	i	i
1	NULL	NULL
2	2	2
select * from t3 natural right join t2;
i
2
3
select * from t3 right join t2 on (t3.i=t2.i);
i	i
2	2
NULL	3
select * from t3 natural right join t2 natural right join t1;
i
1
2
select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i);
i	i	i
NULL	NULL	1
2	2	2
select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
i	i
1	2
1	3
2	2
2	3
select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
i	i	i
1	2	2
1	3	NULL
2	2	2
2	3	NULL
select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i;
i	i	i
1	2	2
1	3	NULL
2	2	2
2	3	NULL
select t1.i,t2.i,t3.i from t2 left join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
i	i	i
1	2	2
1	3	NULL
2	2	2
2	3	NULL
select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i;
i	i
1	4
1	2
2	4
2	2
select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
i	i	i
1	NULL	4
1	2	2
2	NULL	4
2	2	2
select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i;
i	i	i
1	NULL	4
1	2	2
2	NULL	4
2	2	2
select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
i	i	i
1	NULL	4
1	2	2
2	NULL	4
2	2	2
drop table t1,t2,t3;
CREATE TABLE t1 (a int, b int default 0, c int default 1);
INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8);
INSERT INTO t1 (a) SELECT a + 8 FROM t1;
INSERT INTO t1 (a) SELECT a + 16 FROM t1;
CREATE TABLE t2 (a int, d int, e int default 0);
INSERT INTO t2 (a, d) VALUES (1,1),(2,2),(3,3),(4,4);
INSERT INTO t2 (a, d) SELECT a+4, a+4 FROM t2;
INSERT INTO t2 (a, d) SELECT a+8, a+8 FROM t2;
EXPLAIN
SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
ORDER BY t1.b, t1.c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	32	Using temporary; Using filesort
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16	Using where; Using join buffer
SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
ORDER BY t1.b, t1.c;
e
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
DROP TABLE t1,t2;
create table t1 (c int, b int);
create table t2 (a int, b int);
create table t3 (b int, c int);
create table t4 (y int, c int);
create table t5 (y int, z int);
create table t6 (a int, c int);
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);
select * from t1 natural join t2;
b	c	a
1	10	2
1	3	2
select * from t1 natural join t2;
b	c	a
1	10	2
1	3	2
select b as a, c as b, a as c from t1 natural join t2;
a	b	c
1	10	2
1	3	2
select a as c, c as b, b as a from t1 natural join t2;
c	b	a
2	10	1
2	3	1
select t1.c, t1.b, t2.a from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
c	b	a
3	1	2
3	2	2
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;
b	a	c
3	1	2
3	2	2
select * from t1 natural join (t2 natural join t3);
c	b	a
10	1	2
3	1	2
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	2
1	3	2	11
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	2
1	3	2	11
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
drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop table t5;
drop table t6;
create table t1 (a1 int, a2 int);
create table t2 (a1 int, b int);
create table t3 (c1 int, c2 int);
create table t4 (c2 int);
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 (c int, b int);
create table t2 (a int, b int);
create table t3 (b int, c int);
create table t4 (y int, c int);
create table t5 (y int, z int);
insert into t1 values (3,2);
insert into t2 values (1,2);
insert into t3 values (2,3);
insert into t4 values (1,3);
insert into t5 values (1,4);
select * from ((t3 natural join (t1 natural join t2)) natural join t4)
natural join t5;
y	c	b	a	z
1	3	2	1	4
drop table t1, t2, t3, t4, t5;
create table t1 (a int, b int);
insert into t1 values 
(NULL, 1),
(NULL, 2),
(NULL, 3),
(NULL, 4);
create table t2 (a int not null, primary key(a));
insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t3 (a int not null, primary key(a));
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
explain select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	Using index
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
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	#
Handler_read_key	#
Handler_read_next	#
Handler_read_prev	#
Handler_read_rnd	#
Handler_read_rnd_next	#
drop table t1, t2, t3;
create table t1 (a int);
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));
create table t3 (a int, b int, filler char(100), key(a), key(b));
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;
drop table t1, t2, t3;
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (a int, b int, primary key(a));
insert into t2 select @v:=A.a+10*B.a, @v  from t1 A, t1 B;
explain select * from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
show status like '%cost%';
Variable_name	Value
Last_query_cost	#
select 'The cost of accessing t1 (dont care if it changes' '^';
The cost of accessing t1 (dont care if it changes
The cost of accessing t1 (dont care if it changes^
select 'vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv' Z;
Z
vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv
explain select * from t1, t2 A, t2 B where A.a = t1.a and B.a=A.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
1	SIMPLE	A	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	
1	SIMPLE	B	eq_ref	PRIMARY	PRIMARY	4	test.A.b	1	
show status like '%cost%';
Variable_name	Value
Last_query_cost	#
select '^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error' Z;
Z
^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error
drop table t1, t2;
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
CREATE TABLE t2 (c INT PRIMARY KEY, d INT);
INSERT INTO t1 VALUES(1,NULL),(2,NULL),(3,NULL),(4,NULL);
INSERT INTO t1 SELECT a + 4, b FROM t1;
INSERT INTO t1 SELECT a + 8, b FROM t1;
INSERT INTO t1 SELECT a + 16, b FROM t1;
INSERT INTO t1 SELECT a + 32, b FROM t1;
INSERT INTO t1 SELECT a + 64, b FROM t1;
INSERT INTO t2 SELECT a, b FROM t1;
EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	2	
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	
EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	2	
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	
SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
a	b	c	d
SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
a	b	c	d
1	NULL	1	NULL
2	NULL	2	NULL
EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	128	Using filesort
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	
EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	128	Using filesort
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	
SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
a	b	c	d
SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
a	b	c	d
1	NULL	1	NULL
2	NULL	2	NULL
3	NULL	3	NULL
4	NULL	4	NULL
5	NULL	5	NULL
6	NULL	6	NULL
7	NULL	7	NULL
8	NULL	8	NULL
9	NULL	9	NULL
10	NULL	10	NULL
11	NULL	11	NULL
12	NULL	12	NULL
13	NULL	13	NULL
14	NULL	14	NULL
15	NULL	15	NULL
16	NULL	16	NULL
17	NULL	17	NULL
18	NULL	18	NULL
19	NULL	19	NULL
20	NULL	20	NULL
21	NULL	21	NULL
22	NULL	22	NULL
23	NULL	23	NULL
24	NULL	24	NULL
25	NULL	25	NULL
26	NULL	26	NULL
27	NULL	27	NULL
28	NULL	28	NULL
29	NULL	29	NULL
30	NULL	30	NULL
31	NULL	31	NULL
32	NULL	32	NULL
33	NULL	33	NULL
34	NULL	34	NULL
35	NULL	35	NULL
36	NULL	36	NULL
37	NULL	37	NULL
38	NULL	38	NULL
39	NULL	39	NULL
40	NULL	40	NULL
41	NULL	41	NULL
42	NULL	42	NULL
43	NULL	43	NULL
44	NULL	44	NULL
45	NULL	45	NULL
46	NULL	46	NULL
47	NULL	47	NULL
48	NULL	48	NULL
49	NULL	49	NULL
50	NULL	50	NULL
51	NULL	51	NULL
52	NULL	52	NULL
53	NULL	53	NULL
54	NULL	54	NULL
55	NULL	55	NULL
56	NULL	56	NULL
57	NULL	57	NULL
58	NULL	58	NULL
59	NULL	59	NULL
60	NULL	60	NULL
61	NULL	61	NULL
62	NULL	62	NULL
63	NULL	63	NULL
64	NULL	64	NULL
65	NULL	65	NULL
66	NULL	66	NULL
67	NULL	67	NULL
68	NULL	68	NULL
69	NULL	69	NULL
70	NULL	70	NULL
71	NULL	71	NULL
72	NULL	72	NULL
73	NULL	73	NULL
74	NULL	74	NULL
75	NULL	75	NULL
76	NULL	76	NULL
77	NULL	77	NULL
78	NULL	78	NULL
79	NULL	79	NULL
80	NULL	80	NULL
81	NULL	81	NULL
82	NULL	82	NULL
83	NULL	83	NULL
84	NULL	84	NULL
85	NULL	85	NULL
86	NULL	86	NULL
87	NULL	87	NULL
88	NULL	88	NULL
89	NULL	89	NULL
90	NULL	90	NULL
91	NULL	91	NULL
92	NULL	92	NULL
93	NULL	93	NULL
94	NULL	94	NULL
95	NULL	95	NULL
96	NULL	96	NULL
97	NULL	97	NULL
98	NULL	98	NULL
99	NULL	99	NULL
100	NULL	100	NULL
101	NULL	101	NULL
102	NULL	102	NULL
103	NULL	103	NULL
104	NULL	104	NULL
105	NULL	105	NULL
106	NULL	106	NULL
107	NULL	107	NULL
108	NULL	108	NULL
109	NULL	109	NULL
110	NULL	110	NULL
111	NULL	111	NULL
112	NULL	112	NULL
113	NULL	113	NULL
114	NULL	114	NULL
115	NULL	115	NULL
116	NULL	116	NULL
117	NULL	117	NULL
118	NULL	118	NULL
119	NULL	119	NULL
120	NULL	120	NULL
121	NULL	121	NULL
122	NULL	122	NULL
123	NULL	123	NULL
124	NULL	124	NULL
125	NULL	125	NULL
126	NULL	126	NULL
127	NULL	127	NULL
128	NULL	128	NULL
DROP TABLE IF EXISTS t1,t2;
End of 5.0 tests.