1drop table if exists t1,t2,t3; 2CREATE TABLE t1 (S1 INT); 3CREATE TABLE t2 (S1 INT); 4INSERT INTO t1 VALUES (1); 5INSERT INTO t2 VALUES (2); 6SELECT * FROM t1 JOIN t2; 7S1 S1 81 2 9SELECT * FROM t1 INNER JOIN t2; 10S1 S1 111 2 12SELECT * from t1 JOIN t2 USING (S1); 13S1 14SELECT * FROM t1 INNER JOIN t2 USING (S1); 15S1 16SELECT * from t1 CROSS JOIN t2; 17S1 S1 181 2 19SELECT * from t1 LEFT JOIN t2 USING(S1); 20S1 211 22SELECT * from t1 LEFT JOIN t2 ON(t2.S1=2); 23S1 S1 241 2 25SELECT * from t1 RIGHT JOIN t2 USING(S1); 26S1 272 28SELECT * from t1 RIGHT JOIN t2 ON(t1.S1=1); 29S1 S1 301 2 31drop table t1,t2; 32create table t1 (id int primary key); 33create table t2 (id int); 34insert into t1 values (75); 35insert into t1 values (79); 36insert into t1 values (78); 37insert into t1 values (77); 38replace into t1 values (76); 39replace into t1 values (76); 40insert into t1 values (104); 41insert into t1 values (103); 42insert into t1 values (102); 43insert into t1 values (101); 44insert into t1 values (105); 45insert into t1 values (106); 46insert into t1 values (107); 47insert into t2 values (107),(75),(1000); 48select t1.id, t2.id from t1, t2 where t2.id = t1.id; 49id id 50107 107 5175 75 52select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t1.id; 53id count(t2.id) 5475 1 55107 1 56select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t2.id; 57id count(t2.id) 5875 1 59107 1 60select 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; 61id id 62NULL 75 63explain 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; 64id select_type table type possible_keys key key_len ref rows Extra 651 SIMPLE t1 const PRIMARY NULL NULL NULL 1 Impossible ON condition 661 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where 67explain select t1.id, t2.id from t1, t2 where t2.id = t1.id and t1.id <0 and t1.id > 0; 68id select_type table type possible_keys key key_len ref rows Extra 691 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 70drop table t1,t2; 71CREATE TABLE t1 ( 72id int(11) NOT NULL auto_increment, 73token varchar(100) DEFAULT '' NOT NULL, 74count int(11) DEFAULT '0' NOT NULL, 75qty int(11), 76phone char(1) DEFAULT '' NOT NULL, 77timestamp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, 78PRIMARY KEY (id), 79KEY token (token(15)), 80KEY timestamp (timestamp), 81UNIQUE token_2 (token(75),count,phone) 82); 83INSERT INTO t1 VALUES (21,'e45703b64de71482360de8fec94c3ade',3,7800,'n','1999-12-23 17:22:21'); 84INSERT INTO t1 VALUES (22,'e45703b64de71482360de8fec94c3ade',4,5000,'y','1999-12-23 17:22:21'); 85INSERT INTO t1 VALUES (18,'346d1cb63c89285b2351f0ca4de40eda',3,13200,'b','1999-12-23 11:58:04'); 86INSERT INTO t1 VALUES (17,'ca6ddeb689e1b48a04146b1b5b6f936a',4,15000,'b','1999-12-23 11:36:53'); 87INSERT INTO t1 VALUES (16,'ca6ddeb689e1b48a04146b1b5b6f936a',3,13200,'b','1999-12-23 11:36:53'); 88INSERT INTO t1 VALUES (26,'a71250b7ed780f6ef3185bfffe027983',5,1500,'b','1999-12-27 09:44:24'); 89INSERT INTO t1 VALUES (24,'4d75906f3c37ecff478a1eb56637aa09',3,5400,'y','1999-12-23 17:29:12'); 90INSERT INTO t1 VALUES (25,'4d75906f3c37ecff478a1eb56637aa09',4,6500,'y','1999-12-23 17:29:12'); 91INSERT INTO t1 VALUES (27,'a71250b7ed780f6ef3185bfffe027983',3,6200,'b','1999-12-27 09:44:24'); 92INSERT INTO t1 VALUES (28,'a71250b7ed780f6ef3185bfffe027983',3,5400,'y','1999-12-27 09:44:36'); 93INSERT INTO t1 VALUES (29,'a71250b7ed780f6ef3185bfffe027983',4,17700,'b','1999-12-27 09:45:05'); 94CREATE TABLE t2 ( 95id int(11) NOT NULL auto_increment, 96category int(11) DEFAULT '0' NOT NULL, 97county int(11) DEFAULT '0' NOT NULL, 98state int(11) DEFAULT '0' NOT NULL, 99phones int(11) DEFAULT '0' NOT NULL, 100nophones int(11) DEFAULT '0' NOT NULL, 101PRIMARY KEY (id), 102KEY category (category,county,state) 103); 104INSERT INTO t2 VALUES (3,2,11,12,5400,7800); 105INSERT INTO t2 VALUES (4,2,25,12,6500,11200); 106INSERT INTO t2 VALUES (5,1,37,6,10000,12000); 107select 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); 108id catid stateid countyid 10927 2 12 11 11028 2 12 11 11129 2 12 25 11226 1 6 37 113select a.id, b.category as catid, b.state as stateid, b.county as 114countyid from t1 a, t2 b where (a.token = 115'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id) order by a.id; 116id catid stateid countyid 11726 1 6 37 11827 2 12 11 11928 2 12 11 12029 2 12 25 121drop table t1, t2; 122create table t1 (a int primary key); 123insert into t1 values(1),(2); 124select 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); 125a 1261 1272 128select 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); 129ERROR HY000: Too many tables; MySQL can only use XX tables in a join 130select 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); 131a 1321 1332 134select 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); 135ERROR HY000: Too many tables; MySQL can only use XX tables in a join 136drop table t1; 137CREATE TABLE t1 ( 138a int(11) NOT NULL, 139b int(11) NOT NULL, 140PRIMARY KEY (a,b) 141) ENGINE=MyISAM; 142INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(2,3); 143CREATE TABLE t2 ( 144a int(11) default NULL 145) ENGINE=MyISAM; 146INSERT INTO t2 VALUES (2),(3); 147SELECT 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; 148a a b 1492 2 3 150DROP TABLE t1, t2; 151CREATE TABLE t1 (d DATE NOT NULL); 152CREATE TABLE t2 (d DATE NOT NULL); 153INSERT INTO t1 (d) VALUES ('2001-08-01'),('0000-00-00'); 154SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE t2.d IS NULL; 155d 1562001-08-01 1570000-00-00 158SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE d IS NULL; 159d 1600000-00-00 161SELECT * from t1 WHERE t1.d IS NULL; 162d 1630000-00-00 164SELECT * FROM t1 WHERE 1/0 IS NULL; 165d 1662001-08-01 1670000-00-00 168DROP TABLE t1,t2; 169CREATE TABLE t1 ( 170Document_ID varchar(50) NOT NULL default '', 171Contractor_ID varchar(6) NOT NULL default '', 172Language_ID char(3) NOT NULL default '', 173Expiration_Date datetime default NULL, 174Publishing_Date datetime default NULL, 175Title text, 176Column_ID varchar(50) NOT NULL default '', 177PRIMARY KEY (Language_ID,Document_ID,Contractor_ID) 178); 179INSERT 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,''); 180CREATE TABLE t2 ( 181Contractor_ID char(6) NOT NULL default '', 182Language_ID char(3) NOT NULL default '', 183Document_ID char(50) NOT NULL default '', 184CanRead char(1) default NULL, 185Customer_ID int(11) NOT NULL default '0', 186PRIMARY KEY (Contractor_ID,Language_ID,Document_ID,Customer_ID) 187); 188INSERT INTO t2 VALUES ('5','ger','xep80','1',999999),('1','ger','xep80','1',999999); 189CREATE TABLE t3 ( 190Language_ID char(3) NOT NULL default '', 191Column_ID char(50) NOT NULL default '', 192Contractor_ID char(6) NOT NULL default '', 193CanRead char(1) default NULL, 194Active char(1) default NULL, 195PRIMARY KEY (Language_ID,Column_ID,Contractor_ID) 196); 197INSERT 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'); 198delete from t1 where Contractor_ID='999998'; 199insert into t1 (Contractor_ID) Values ('999998'); 200SELECT DISTINCT COUNT(t1.Title) FROM t1, 201t2, t3 WHERE 202t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND 203t1.Language_ID='ger' AND '2001-12-21 23:14:24' >= 204Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND 205t1.Document_ID = t2.Document_ID AND 206t1.Language_ID = t2.Language_ID AND 207t1.Contractor_ID = t2.Contractor_ID AND ( 208t2.Customer_ID = '4' OR 209t2.Customer_ID = '999999' OR 210t2.Customer_ID = '1' )AND t2.CanRead 211= '1' AND t1.Column_ID=t3.Column_ID AND 212t1.Language_ID=t3.Language_ID AND ( 213t3.Contractor_ID = '4' OR 214t3.Contractor_ID = '999999' OR 215t3.Contractor_ID = '1') AND 216t3.CanRead='1' AND t3.Active='1'; 217COUNT(t1.Title) 2181 219SELECT DISTINCT COUNT(t1.Title) FROM t1, 220t2, t3 WHERE 221t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND 222t1.Language_ID='ger' AND '2001-12-21 23:14:24' >= 223Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND 224t1.Document_ID = t2.Document_ID AND 225t1.Language_ID = t2.Language_ID AND 226t1.Contractor_ID = t2.Contractor_ID AND ( 227t2.Customer_ID = '4' OR 228t2.Customer_ID = '999999' OR 229t2.Customer_ID = '1' )AND t2.CanRead 230= '1' AND t1.Column_ID=t3.Column_ID AND 231t1.Language_ID=t3.Language_ID AND ( 232t3.Contractor_ID = '4' OR 233t3.Contractor_ID = '999999' OR 234t3.Contractor_ID = '1') AND 235t3.CanRead='1' AND t3.Active='1'; 236COUNT(t1.Title) 2371 238drop table t1,t2,t3; 239CREATE TABLE t1 ( 240t1_id int(11) default NULL, 241t2_id int(11) default NULL, 242type enum('Cost','Percent') default NULL, 243cost_unit enum('Cost','Unit') default NULL, 244min_value double default NULL, 245max_value double default NULL, 246t3_id int(11) default NULL, 247item_id int(11) default NULL 248) ENGINE=MyISAM; 249INSERT 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); 250CREATE TABLE t2 ( 251id int(10) unsigned NOT NULL auto_increment, 252name varchar(255) default NULL, 253PRIMARY KEY (id) 254) ENGINE=MyISAM; 255INSERT INTO t2 VALUES (1,'s1'),(2,'s2'),(3,'s3'),(4,'s4'),(5,'s5'); 256select t1.*, t2.* from t1, t2 where t2.id=t1.t2_id limit 2; 257t1_id t2_id type cost_unit min_value max_value t3_id item_id id name 25822 1 Percent Cost 100 -1 6 291 1 s1 25923 1 Percent Cost 100 -1 21 291 1 s1 260drop table t1,t2; 261CREATE TABLE t1 ( 262siteid varchar(25) NOT NULL default '', 263emp_id varchar(30) NOT NULL default '', 264rate_code varchar(10) default NULL, 265UNIQUE KEY site_emp (siteid,emp_id), 266KEY siteid (siteid) 267) ENGINE=MyISAM; 268INSERT INTO t1 VALUES ('rivercats','psmith','cust'), ('rivercats','KWalker','cust'); 269CREATE TABLE t2 ( 270siteid varchar(25) NOT NULL default '', 271rate_code varchar(10) NOT NULL default '', 272base_rate float NOT NULL default '0', 273PRIMARY KEY (siteid,rate_code), 274FULLTEXT KEY rate_code (rate_code) 275) ENGINE=MyISAM; 276INSERT INTO t2 VALUES ('rivercats','cust',20); 277SELECT 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'; 278rate_code base_rate 279cust 20 280SELECT 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'; 281rate_code base_rate 282cust 20 283SELECT 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'; 284rate_code base_rate 285cust 20 286SELECT 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'; 287rate_code base_rate 288cust 20 289drop table t1,t2; 290CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255)); 291CREATE TABLE t2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255)); 292INSERT INTO t1 VALUES (1, 'A'); 293INSERT INTO t2 VALUES (1, 'B'); 294SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND (Value1 = 'A' AND Value2 <> 'B'); 295ID Value1 Value2 296SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND Value1 = 'A' AND Value2 <> 'B'; 297ID Value1 Value2 298SELECT * FROM t1 NATURAL JOIN t2 WHERE (Value1 = 'A' AND Value2 <> 'B') AND 1; 299ID Value1 Value2 300drop table t1,t2; 301CREATE TABLE t1 (a int); 302CREATE TABLE t2 (b int); 303CREATE TABLE t3 (c int); 304SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3; 305a b c 306DROP TABLE t1, t2, t3; 307create table t1 (i int); 308create table t2 (i int); 309create table t3 (i int); 310insert into t1 values(1),(2); 311insert into t2 values(2),(3); 312insert into t3 values (2),(4); 313select * from t1 natural left join t2; 314i 3151 3162 317select * from t1 left join t2 on (t1.i=t2.i); 318i i 3191 NULL 3202 2 321select * from t1 natural left join t2 natural left join t3; 322i 3231 3242 325select * from t1 left join t2 on (t1.i=t2.i) left join t3 on (t2.i=t3.i); 326i i i 3271 NULL NULL 3282 2 2 329select * from t3 natural right join t2; 330i 3312 3323 333select * from t3 right join t2 on (t3.i=t2.i); 334i i 3352 2 336NULL 3 337select * from t3 natural right join t2 natural right join t1; 338i 3391 3402 341select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i); 342i i i 3432 2 2 344NULL NULL 1 345select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i; 346i i 3471 2 3481 3 3492 2 3502 3 351select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i; 352i i i 3531 2 2 3541 3 NULL 3552 2 2 3562 3 NULL 357select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i; 358i i i 3591 2 2 3601 3 NULL 3612 2 2 3622 3 NULL 363select 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; 364i i i 3651 2 2 3661 3 NULL 3672 2 2 3682 3 NULL 369select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i; 370i i 3711 4 3721 2 3732 4 3742 2 375select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i; 376i i i 3771 NULL 4 3781 2 2 3792 NULL 4 3802 2 2 381select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i; 382i i i 3831 NULL 4 3841 2 2 3852 NULL 4 3862 2 2 387select 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; 388i i i 3891 NULL 4 3901 2 2 3912 NULL 4 3922 2 2 393drop table t1,t2,t3; 394CREATE TABLE t1 (a int, b int default 0, c int default 1); 395INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8); 396INSERT INTO t1 (a) SELECT a + 8 FROM t1; 397INSERT INTO t1 (a) SELECT a + 16 FROM t1; 398CREATE TABLE t2 (a int, d int, e int default 0); 399INSERT INTO t2 (a, d) VALUES (1,1),(2,2),(3,3),(4,4); 400INSERT INTO t2 (a, d) SELECT a+4, a+4 FROM t2; 401INSERT INTO t2 (a, d) SELECT a+8, a+8 FROM t2; 402EXPLAIN 403SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e 404ORDER BY t1.b, t1.c; 405id select_type table type possible_keys key key_len ref rows Extra 4061 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort 4071 SIMPLE t2 ALL NULL NULL NULL NULL 16 Using where; Using join buffer (Block Nested Loop) 408SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e 409ORDER BY t1.b, t1.c; 410e 4110 4120 4130 4140 4150 4160 4170 4180 4190 4200 4210 4220 4230 4240 4250 4260 4270 4280 4290 4300 4310 4320 4330 4340 4350 4360 4370 4380 4390 4400 4410 4420 443DROP TABLE t1,t2; 444create table t1 (c int, b int); 445create table t2 (a int, b int); 446create table t3 (b int, c int); 447create table t4 (y int, c int); 448create table t5 (y int, z int); 449create table t6 (a int, c int); 450insert into t1 values (10,1); 451insert into t1 values (3 ,1); 452insert into t1 values (3 ,2); 453insert into t2 values (2, 1); 454insert into t3 values (1, 3); 455insert into t3 values (1,10); 456insert into t4 values (11,3); 457insert into t4 values (2, 3); 458insert into t5 values (11,4); 459insert into t6 values (2, 3); 460create algorithm=merge view v1a as 461select * from t1 natural join t2; 462create algorithm=merge view v1b(a,b,c) as 463select * from t1 natural join t2; 464create algorithm=merge view v1c as 465select b as a, c as b, a as c from t1 natural join t2; 466create algorithm=merge view v1d(b, a, c) as 467select a as c, c as b, b as a from t1 natural join t2; 468create algorithm=merge view v2a as 469select t1.c, t1.b, t2.a from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c; 470create algorithm=merge view v2b as 471select t1.c as b, t1.b as a, t2.a as c 472from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c; 473create algorithm=merge view v3a as 474select * from t1 natural join t2 natural join t3; 475create algorithm=merge view v3b as 476select * from t1 natural join (t2 natural join t3); 477create algorithm=merge view v4 as 478select * from v2a natural join v3a; 479select * from (t1 natural join t2) natural join (t3 natural join t4); 480b c a y 4811 3 2 11 4821 3 2 2 483select * from (t1 natural join t2) natural left join (t3 natural join t4); 484b c a y 4851 10 2 NULL 4861 3 2 11 4871 3 2 2 488select * from (t3 natural join t4) natural right join (t1 natural join t2); 489b c a y 4901 10 2 NULL 4911 3 2 11 4921 3 2 2 493select * from (t1 natural left join t2) natural left join (t3 natural left join t4); 494b c a y 4951 10 2 NULL 4961 3 2 11 4971 3 2 2 4982 3 NULL NULL 499select * from (t4 natural right join t3) natural right join (t2 natural right join t1); 500b c a y 5011 10 2 NULL 5021 3 2 11 5031 3 2 2 5042 3 NULL NULL 505select * from t1 natural join t2 natural join t3 natural join t4; 506c b a y 5073 1 2 11 5083 1 2 2 509select * from ((t1 natural join t2) natural join t3) natural join t4; 510c b a y 5113 1 2 11 5123 1 2 2 513select * from t1 natural join (t2 natural join (t3 natural join t4)); 514c b a y 5153 1 2 11 5163 1 2 2 517select * from t5 natural right join (t4 natural right join ((t2 natural right join t1) natural right join t3)); 518y c b a z 51911 3 1 2 4 5202 3 1 2 NULL 521NULL 10 1 2 NULL 522select * from (t1 natural join t2), (t3 natural join t4); 523b c a c b y 5241 10 2 3 1 11 5251 10 2 3 1 2 5261 3 2 3 1 11 5271 3 2 3 1 2 528select * from t5 natural join ((t1 natural join t2), (t3 natural join t4)); 529y z b c a c b 53011 4 1 10 2 3 1 53111 4 1 3 2 3 1 532select * from ((t1 natural join t2), (t3 natural join t4)) natural join t5; 533y b c a c b z 53411 1 10 2 3 1 4 53511 1 3 2 3 1 4 536select * from t5 natural join ((t1 natural join t2) cross join (t3 natural join t4)); 537y z b c a c b 53811 4 1 10 2 3 1 53911 4 1 3 2 3 1 540select * from ((t1 natural join t2) cross join (t3 natural join t4)) natural join t5; 541y b c a c b z 54211 1 10 2 3 1 4 54311 1 3 2 3 1 4 544select * from (t1 join t2 using (b)) join (t3 join t4 using (c)) using (c); 545c b a b y 5463 1 2 1 11 5473 1 2 1 2 548select * from (t1 join t2 using (b)) natural join (t3 join t4 using (c)); 549b c a y 5501 3 2 11 5511 3 2 2 552select a,b,c from (t1 natural join t2) natural join (t3 natural join t4) 553where b + 1 = y or b + 10 = y group by b,c,a having min(b) < max(y) order by a; 554a b c 5552 1 3 556select * from (t1 natural join t2) natural left join (t3 natural join t4) 557where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y; 558b c a y 5591 3 2 2 5601 3 2 11 561select * from (t3 natural join t4) natural right join (t1 natural join t2) 562where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y; 563b c a y 5641 3 2 2 5651 3 2 11 566select * from t1 natural join t2 where t1.c > t2.a; 567b c a 5681 10 2 5691 3 2 570select * from t1 natural join t2 where t1.b > t2.b; 571b c a 572select * from t1 natural left join (t4 natural join t5) where t5.z is not NULL; 573c b y z 5743 1 11 4 5753 2 11 4 576select * from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c; 577c b a b y c 5783 1 2 1 2 3 5793 2 2 1 2 3 580select * from (t2 join t4 on b + 1 = y) join t1 on t1.c = t4.c; 581a b y c c b 5822 1 2 3 3 1 5832 1 2 3 3 2 584select * from t1 natural join (t2 join t4 on b + 1 = y); 585c b a y 5863 1 2 2 587select * from (t1 cross join t2) join (t3 cross join t4) on (a < y and t2.b < t3.c); 588c b a b b c y c 58910 1 2 1 1 3 11 3 59010 1 2 1 1 10 11 3 5913 1 2 1 1 3 11 3 5923 1 2 1 1 10 11 3 5933 2 2 1 1 3 11 3 5943 2 2 1 1 10 11 3 595select * from (t1, t2) join (t3, t4) on (a < y and t2.b < t3.c); 596c b a b b c y c 59710 1 2 1 1 3 11 3 59810 1 2 1 1 10 11 3 5993 1 2 1 1 3 11 3 6003 1 2 1 1 10 11 3 6013 2 2 1 1 3 11 3 6023 2 2 1 1 10 11 3 603select * from (t1 natural join t2) join (t3 natural join t4) on a = y; 604b c a c b y 6051 10 2 3 1 2 6061 3 2 3 1 2 607select * 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; 608b c c b a b y c y z 6091 3 10 1 2 1 11 3 11 4 6101 10 10 1 2 1 11 3 11 4 6111 3 3 1 2 1 11 3 11 4 6121 10 3 1 2 1 11 3 11 4 613select * from t1 natural join t2 where t1.b > 0; 614b c a 6151 10 2 6161 3 2 617select * from t1 natural join (t4 natural join t5) where t4.y > 7; 618c b y z 6193 1 11 4 6203 2 11 4 621select * from (t4 natural join t5) natural join t1 where t4.y > 7; 622c y z b 6233 11 4 1 6243 11 4 2 625select * from t1 natural left join (t4 natural join t5) where t4.y > 7; 626c b y z 6273 1 11 4 6283 2 11 4 629select * from (t4 natural join t5) natural right join t1 where t4.y > 7; 630c b y z 6313 1 11 4 6323 2 11 4 633select * from (t1 natural join t2) join (t3 natural join t4) on t1.b = t3.b; 634b c a c b y 6351 10 2 3 1 11 6361 10 2 3 1 2 6371 3 2 3 1 11 6381 3 2 3 1 2 639select t1.*, t2.* from t1 natural join t2; 640c b a b 64110 1 2 1 6423 1 2 1 643select t1.*, t2.*, t3.*, t4.* from (t1 natural join t2) natural join (t3 natural join t4); 644c b a b b c y c 6453 1 2 1 1 3 11 3 6463 1 2 1 1 3 2 3 647select * from (select * from t1 natural join t2) as t12 648natural join 649(select * from t3 natural join t4) as t34; 650b c a y 6511 3 2 11 6521 3 2 2 653select * from (select * from t1 natural join t2) as t12 654natural left join 655(select * from t3 natural join t4) as t34; 656b c a y 6571 10 2 NULL 6581 3 2 11 6591 3 2 2 660select * from (select * from t3 natural join t4) as t34 661natural right join 662(select * from t1 natural join t2) as t12; 663b c a y 6641 10 2 NULL 6651 3 2 11 6661 3 2 2 667select * from v1a; 668b c a 6691 10 2 6701 3 2 671select * from v1b; 672a b c 6731 10 2 6741 3 2 675select * from v1c; 676a b c 6771 10 2 6781 3 2 679select * from v1d; 680b a c 6812 10 1 6822 3 1 683select * from v2a; 684c b a 6853 1 2 6863 2 2 687select * from v2b; 688b a c 6893 1 2 6903 2 2 691select * from v3a; 692b c a 6931 10 2 6941 3 2 695select * from v3b; 696c b a 69710 1 2 6983 1 2 699select * from v4; 700c b a 7013 1 2 702select * from v1a natural join v2a; 703b c a 7041 3 2 705select v2a.* from v1a natural join v2a; 706c b a 7073 1 2 708select * from v1b join v2a on v1b.b = v2a.c; 709a b c c b a 7101 3 2 3 1 2 7111 3 2 3 2 2 712select * from v1c join v2a on v1c.b = v2a.c; 713a b c c b a 7141 3 2 3 1 2 7151 3 2 3 2 2 716select * from v1d join v2a on v1d.a = v2a.c; 717b a c c b a 7182 3 1 3 1 2 7192 3 1 3 2 2 720select * from v1a join (t3 natural join t4) on a = y; 721b c a c b y 7221 10 2 3 1 2 7231 3 2 3 1 2 724select * from t1 natural join (t3 cross join t4); 725ERROR 23000: Column 'c' in from clause is ambiguous 726select * from (t3 cross join t4) natural join t1; 727ERROR 23000: Column 'c' in from clause is ambiguous 728select * from t1 join (t2, t3) using (b); 729ERROR 23000: Column 'b' in from clause is ambiguous 730select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6; 731ERROR 23000: Column 'c' in from clause is ambiguous 732select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6; 733ERROR 23000: Column 'c' in from clause is ambiguous 734select * from t6 natural join ((t1 natural join t2), (t3 natural join t4)); 735ERROR 23000: Column 'c' in from clause is ambiguous 736select * from (t1 join t2 on t1.b=t2.b) natural join (t3 natural join t4); 737ERROR 23000: Column 'b' in from clause is ambiguous 738select * from (t3 natural join t4) natural join (t1 join t2 on t1.b=t2.b); 739ERROR 23000: Column 'b' in from clause is ambiguous 740select * from (t3 join (t4 natural join t5) on (b < z)) 741natural join 742(t1 natural join t2); 743ERROR 23000: Column 'c' in from clause is ambiguous 744select * from (t1 natural join t2) natural join (t3 join (t4 natural join t5) on (b < z)); 745ERROR 23000: Column 'c' in from clause is ambiguous 746select t1.b from v1a; 747ERROR 42S22: Unknown column 't1.b' in 'field list' 748select * from v1a join v1b on t1.b = t2.b; 749ERROR 42S22: Unknown column 't1.b' in 'on clause' 750select 751statistics.TABLE_NAME, statistics.COLUMN_NAME, statistics.TABLE_CATALOG, statistics.TABLE_SCHEMA, statistics.NON_UNIQUE, statistics.INDEX_SCHEMA, statistics.INDEX_NAME, statistics.SEQ_IN_INDEX, statistics.COLLATION, statistics.SUB_PART, statistics.PACKED, statistics.NULLABLE, statistics.INDEX_TYPE, statistics.COMMENT, 752columns.TABLE_CATALOG, columns.TABLE_SCHEMA, columns.COLUMN_DEFAULT, columns.IS_NULLABLE, columns.DATA_TYPE, columns.CHARACTER_MAXIMUM_LENGTH, columns.CHARACTER_OCTET_LENGTH, columns.NUMERIC_PRECISION, columns.NUMERIC_SCALE, columns.CHARACTER_SET_NAME, columns.COLLATION_NAME, columns.COLUMN_TYPE, columns.COLUMN_KEY, columns.EXTRA, columns.COLUMN_COMMENT 753from information_schema.statistics join information_schema.columns using(table_name,column_name) where table_name='user'; 754TABLE_NAME COLUMN_NAME TABLE_CATALOG TABLE_SCHEMA NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLLATION SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT TABLE_CATALOG TABLE_SCHEMA COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA COLUMN_COMMENT 755user Host def mysql 0 mysql PRIMARY 1 A NULL NULL BTREE def mysql NO char 60 180 NULL NULL utf8 utf8_bin char(60) PRI 756user User def mysql 0 mysql PRIMARY 2 A NULL NULL BTREE def mysql NO char 16 48 NULL NULL utf8 utf8_bin char(16) PRI 757drop table t1; 758drop table t2; 759drop table t3; 760drop table t4; 761drop table t5; 762drop table t6; 763drop view v1a; 764drop view v1b; 765drop view v1c; 766drop view v1d; 767drop view v2a; 768drop view v2b; 769drop view v3a; 770drop view v3b; 771drop view v4; 772create table t1 (a1 int, a2 int); 773create table t2 (a1 int, b int); 774create table t3 (c1 int, c2 int); 775create table t4 (c2 int); 776insert into t1 values (1,1); 777insert into t2 values (1,1); 778insert into t3 values (1,1); 779insert into t4 values (1); 780select * from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2); 781c2 a1 a2 b c1 7821 1 1 1 1 783select * from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2); 784c2 c1 a1 a2 b 7851 1 1 1 1 786select a2 from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2); 787a2 7881 789select a2 from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2); 790a2 7911 792select a2 from ((t1 join t2 using (a1)) join t3 on b=c1) join t4 using (c2); 793a2 7941 795select a2 from ((t1 natural join t2) join t3 on b=c1) natural join t4; 796a2 7971 798drop table t1,t2,t3,t4; 799create table t1 (c int, b int); 800create table t2 (a int, b int); 801create table t3 (b int, c int); 802create table t4 (y int, c int); 803create table t5 (y int, z int); 804insert into t1 values (3,2); 805insert into t2 values (1,2); 806insert into t3 values (2,3); 807insert into t4 values (1,3); 808insert into t5 values (1,4); 809prepare stmt1 from "select * from ((t3 natural join (t1 natural join t2)) 810natural join t4) natural join t5"; 811execute stmt1; 812y c b a z 8131 3 2 1 4 814select * from ((t3 natural join (t1 natural join t2)) natural join t4) 815natural join t5; 816y c b a z 8171 3 2 1 4 818drop table t1, t2, t3, t4, t5; 819CREATE TABLE t1 (ID INTEGER, Name VARCHAR(50)); 820CREATE TABLE t2 (Test_ID INTEGER); 821CREATE VIEW v1 (Test_ID, Description) AS SELECT ID, Name FROM t1; 822CREATE TABLE tv1 SELECT Description AS Name FROM v1 JOIN t2 823USING (Test_ID); 824DESCRIBE tv1; 825Field Type Null Key Default Extra 826Name varchar(50) YES NULL 827CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2 828ON v1.Test_ID = t2.Test_ID; 829DESCRIBE tv2; 830Field Type Null Key Default Extra 831Name varchar(50) YES NULL 832DROP VIEW v1; 833DROP TABLE t1,t2,tv1,tv2; 834create table t1 (a int, b int); 835insert into t1 values 836(NULL, 1), 837(NULL, 2), 838(NULL, 3), 839(NULL, 4); 840create table t2 (a int not null, primary key(a)); 841insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 842create table t3 (a int not null, primary key(a)); 843insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 844flush status; 845select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b; 846a b a a 847explain select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b; 848id select_type table type possible_keys key key_len ref rows Extra 8491 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where 8501 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index 8511 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index 852We expect rnd_next=5, and read_key must be 0 because of short-cutting: 853show status like 'Handler_read%'; 854Variable_name Value 855Handler_read_first 0 856Handler_read_key 0 857Handler_read_last 0 858Handler_read_next 0 859Handler_read_prev 0 860Handler_read_rnd 0 861Handler_read_rnd_next 5 862drop table t1, t2, t3; 863create table t1 (a int); 864insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 865create table t2 (a int, b int, filler char(100), key(a), key(b)); 866create table t3 (a int, b int, filler char(100), key(a), key(b)); 867insert into t2 868select @a:= A.a + 10*(B.a + 10*C.a), @a, 'filler' from t1 A, t1 B, t1 C; 869insert into t3 select * from t2 where a < 800; 870explain select * from t2,t3 where t2.a < 200 and t2.b=t3.b; 871id select_type table type possible_keys key key_len ref rows Extra 8721 SIMPLE t2 ALL a,b NULL NULL NULL 1000 Using where 8731 SIMPLE t3 ref b b 5 test.t2.b 1 NULL 874drop table t1, t2, t3; 875create table t1 (a int); 876insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 877create table t2 (a int, b int, primary key(a)); 878insert into t2 select @v:=A.a+10*B.a, @v from t1 A, t1 B; 879explain select * from t1; 880id select_type table type possible_keys key key_len ref rows Extra 8811 SIMPLE t1 ALL NULL NULL NULL NULL 10 NULL 882show status like '%cost%'; 883Variable_name Value 884Last_query_cost 4.016090 885select 'The cost of accessing t1 (dont care if it changes' '^'; 886The cost of accessing t1 (dont care if it changes 887The cost of accessing t1 (dont care if it changes^ 888select 'vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv' Z; 889Z 890vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv 891explain select * from t1, t2 A, t2 B where A.a = t1.a and B.a=A.b; 892id select_type table type possible_keys key key_len ref rows Extra 8931 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where 8941 SIMPLE A eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where 8951 SIMPLE B eq_ref PRIMARY PRIMARY 4 test.A.b 1 NULL 896show status like '%cost%'; 897Variable_name Value 898Last_query_cost 28.016090 899select '^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error' Z; 900Z 901^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error 902drop table t1, t2; 903CREATE TABLE t1 (a INT PRIMARY KEY, b INT); 904CREATE TABLE t2 (c INT PRIMARY KEY, d INT); 905INSERT INTO t1 VALUES(1,NULL),(2,NULL),(3,NULL),(4,NULL); 906INSERT INTO t1 SELECT a + 4, b FROM t1; 907INSERT INTO t1 SELECT a + 8, b FROM t1; 908INSERT INTO t1 SELECT a + 16, b FROM t1; 909INSERT INTO t1 SELECT a + 32, b FROM t1; 910INSERT INTO t1 SELECT a + 64, b FROM t1; 911INSERT INTO t2 SELECT a, b FROM t1; 912EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2; 913id select_type table type possible_keys key key_len ref rows Extra 9141 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using where 9151 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 NULL 916EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2; 917id select_type table type possible_keys key key_len ref rows Extra 9181 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 NULL 9191 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 NULL 920SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2; 921a b c d 922SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2; 923a b c d 9241 NULL 1 NULL 9252 NULL 2 NULL 926EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a; 927id select_type table type possible_keys key key_len ref rows Extra 9281 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using where; Using filesort 9291 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 NULL 930EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a; 931id select_type table type possible_keys key key_len ref rows Extra 9321 SIMPLE t1 ALL PRIMARY NULL NULL NULL 128 Using filesort 9331 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 NULL 934SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a; 935a b c d 936SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a; 937a b c d 9381 NULL 1 NULL 9392 NULL 2 NULL 9403 NULL 3 NULL 9414 NULL 4 NULL 9425 NULL 5 NULL 9436 NULL 6 NULL 9447 NULL 7 NULL 9458 NULL 8 NULL 9469 NULL 9 NULL 94710 NULL 10 NULL 94811 NULL 11 NULL 94912 NULL 12 NULL 95013 NULL 13 NULL 95114 NULL 14 NULL 95215 NULL 15 NULL 95316 NULL 16 NULL 95417 NULL 17 NULL 95518 NULL 18 NULL 95619 NULL 19 NULL 95720 NULL 20 NULL 95821 NULL 21 NULL 95922 NULL 22 NULL 96023 NULL 23 NULL 96124 NULL 24 NULL 96225 NULL 25 NULL 96326 NULL 26 NULL 96427 NULL 27 NULL 96528 NULL 28 NULL 96629 NULL 29 NULL 96730 NULL 30 NULL 96831 NULL 31 NULL 96932 NULL 32 NULL 97033 NULL 33 NULL 97134 NULL 34 NULL 97235 NULL 35 NULL 97336 NULL 36 NULL 97437 NULL 37 NULL 97538 NULL 38 NULL 97639 NULL 39 NULL 97740 NULL 40 NULL 97841 NULL 41 NULL 97942 NULL 42 NULL 98043 NULL 43 NULL 98144 NULL 44 NULL 98245 NULL 45 NULL 98346 NULL 46 NULL 98447 NULL 47 NULL 98548 NULL 48 NULL 98649 NULL 49 NULL 98750 NULL 50 NULL 98851 NULL 51 NULL 98952 NULL 52 NULL 99053 NULL 53 NULL 99154 NULL 54 NULL 99255 NULL 55 NULL 99356 NULL 56 NULL 99457 NULL 57 NULL 99558 NULL 58 NULL 99659 NULL 59 NULL 99760 NULL 60 NULL 99861 NULL 61 NULL 99962 NULL 62 NULL 100063 NULL 63 NULL 100164 NULL 64 NULL 100265 NULL 65 NULL 100366 NULL 66 NULL 100467 NULL 67 NULL 100568 NULL 68 NULL 100669 NULL 69 NULL 100770 NULL 70 NULL 100871 NULL 71 NULL 100972 NULL 72 NULL 101073 NULL 73 NULL 101174 NULL 74 NULL 101275 NULL 75 NULL 101376 NULL 76 NULL 101477 NULL 77 NULL 101578 NULL 78 NULL 101679 NULL 79 NULL 101780 NULL 80 NULL 101881 NULL 81 NULL 101982 NULL 82 NULL 102083 NULL 83 NULL 102184 NULL 84 NULL 102285 NULL 85 NULL 102386 NULL 86 NULL 102487 NULL 87 NULL 102588 NULL 88 NULL 102689 NULL 89 NULL 102790 NULL 90 NULL 102891 NULL 91 NULL 102992 NULL 92 NULL 103093 NULL 93 NULL 103194 NULL 94 NULL 103295 NULL 95 NULL 103396 NULL 96 NULL 103497 NULL 97 NULL 103598 NULL 98 NULL 103699 NULL 99 NULL 1037100 NULL 100 NULL 1038101 NULL 101 NULL 1039102 NULL 102 NULL 1040103 NULL 103 NULL 1041104 NULL 104 NULL 1042105 NULL 105 NULL 1043106 NULL 106 NULL 1044107 NULL 107 NULL 1045108 NULL 108 NULL 1046109 NULL 109 NULL 1047110 NULL 110 NULL 1048111 NULL 111 NULL 1049112 NULL 112 NULL 1050113 NULL 113 NULL 1051114 NULL 114 NULL 1052115 NULL 115 NULL 1053116 NULL 116 NULL 1054117 NULL 117 NULL 1055118 NULL 118 NULL 1056119 NULL 119 NULL 1057120 NULL 120 NULL 1058121 NULL 121 NULL 1059122 NULL 122 NULL 1060123 NULL 123 NULL 1061124 NULL 124 NULL 1062125 NULL 125 NULL 1063126 NULL 126 NULL 1064127 NULL 127 NULL 1065128 NULL 128 NULL 1066DROP TABLE IF EXISTS t1,t2; 1067# 1068# Bug #42116: Mysql crash on specific query 1069# 1070CREATE TABLE t1 (a INT); 1071CREATE TABLE t2 (a INT); 1072CREATE TABLE t3 (a INT, INDEX (a)); 1073CREATE TABLE t4 (a INT); 1074CREATE TABLE t5 (a INT); 1075CREATE TABLE t6 (a INT); 1076INSERT INTO t1 VALUES (1), (1), (1); 1077INSERT INTO t2 VALUES 1078(2), (2), (2), (2), (2), (2), (2), (2), (2), (2); 1079INSERT INTO t3 VALUES 1080(3), (3), (3), (3), (3), (3), (3), (3), (3), (3); 1081EXPLAIN 1082SELECT * 1083FROM 1084t1 JOIN t2 ON t1.a = t2.a 1085LEFT JOIN 1086( 1087( 1088t3 LEFT JOIN t4 ON t3.a = t4.a 1089) 1090LEFT JOIN 1091( 1092t5 LEFT JOIN t6 ON t5.a = t6.a 1093) 1094ON t4.a = t5.a 1095) 1096ON t1.a = t3.a; 1097id select_type table type possible_keys key key_len ref rows Extra 10981 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 10991 SIMPLE t3 ref a a 5 test.t1.a 2 Using index 11001 SIMPLE t4 ALL NULL NULL NULL NULL 0 Using where 11011 SIMPLE t5 ALL NULL NULL NULL NULL 0 Using where 11021 SIMPLE t6 ALL NULL NULL NULL NULL 0 Using where 11031 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (Block Nested Loop) 1104SELECT * 1105FROM 1106t1 JOIN t2 ON t1.a = t2.a 1107LEFT JOIN 1108( 1109( 1110t3 LEFT JOIN t4 ON t3.a = t4.a 1111) 1112LEFT JOIN 1113( 1114t5 LEFT JOIN t6 ON t5.a = t6.a 1115) 1116ON t4.a = t5.a 1117) 1118ON t1.a = t3.a; 1119a a a a a a 1120DROP TABLE t1,t2,t3,t4,t5,t6; 1121# 1122# Bug#48483: crash in get_best_combination() 1123# 1124CREATE TABLE t1(f1 INT); 1125INSERT INTO t1 VALUES (1),(2); 1126CREATE VIEW v1 AS SELECT 1 FROM t1 LEFT JOIN t1 AS t2 on 1=1; 1127EXPLAIN EXTENDED 1128SELECT 1 FROM v1 right join v1 AS v2 ON RAND(); 1129id select_type table type possible_keys key key_len ref rows filtered Extra 11301 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 NULL 11311 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 11321 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop) 11331 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 1134Warnings: 1135Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` left join `test`.`t1` `t2` on((1 = 1)) left join (`test`.`t1` left join `test`.`t1` `t2` on((1 = 1))) on(rand()) where 1 1136DROP VIEW v1; 1137DROP TABLE t1; 1138# 1139# Bug#52177 crash with explain, row comparison, join, text field 1140# 1141CREATE TABLE t1 (a TINYINT, b TEXT, KEY (a)); 1142INSERT INTO t1 VALUES (0,''),(0,''); 1143FLUSH TABLES; 1144EXPLAIN SELECT 1 FROM t1 LEFT JOIN t1 a ON 1 1145WHERE ROW(t1.a, 1111.11) = ROW(1111.11, 1111.11) AND 1146ROW(t1.b, 1111.11) <=> ROW('',''); 1147id select_type table type possible_keys key key_len ref rows Extra 11481 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1149DROP TABLE t1; 1150# 1151# Bug #50335: Assertion `!(order->used & map)' in eq_ref_table 1152# 1153CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)); 1154INSERT INTO t1 VALUES (0,0), (1,1); 1155SELECT * FROM t1 STRAIGHT_JOIN t1 t2 ON t1.a=t2.a AND t1.a=t2.b ORDER BY t2.a, t1.a; 1156a b a b 11570 0 0 0 11581 1 1 1 1159DROP TABLE t1; 1160End of 5.0 tests. 1161CREATE TABLE t1 (f1 int); 1162CREATE TABLE t2 (f1 int); 1163INSERT INTO t2 VALUES (1); 1164CREATE VIEW v1 AS SELECT * FROM t2; 1165PREPARE stmt FROM 'UPDATE t2 AS A NATURAL JOIN v1 B SET B.f1 = 1'; 1166EXECUTE stmt; 1167EXECUTE stmt; 1168DEALLOCATE PREPARE stmt; 1169DROP VIEW v1; 1170DROP TABLE t1, t2; 1171CREATE TABLE t1(a CHAR(9),b INT,KEY(b),KEY(a)) ENGINE=MYISAM; 1172CREATE TABLE t2(a CHAR(9),b INT,KEY(b),KEY(a)) ENGINE=MYISAM; 1173INSERT INTO t1 VALUES ('1',null),(null,null); 1174INSERT INTO t2 VALUES ('1',null),(null,null); 1175CREATE TABLE mm1(a CHAR(9),b INT,KEY(b),KEY(a)) 1176ENGINE=MERGE UNION=(t1,t2); 1177SELECT t1.a FROM mm1,t1; 1178a 1179NULL 11801 1181NULL 11821 1183NULL 11841 1185NULL 11861 1187DROP TABLE t1, t2, mm1; 1188# 1189# Bug #54468: crash after item's print() function when ordering/grouping 1190# by subquery 1191# 1192CREATE TABLE t1(a INT, b INT); 1193INSERT INTO t1 VALUES (), (); 1194SELECT 1 FROM t1 1195GROUP BY 1196GREATEST(t1.a, 1197(SELECT 1 FROM 1198(SELECT t1.b FROM t1,t1 t2 1199ORDER BY t1.a, t1.a LIMIT 1) AS d) 1200); 12011 12021 1203DROP TABLE t1; 1204# 1205# Bug #53544: Server hangs during JOIN query in stored procedure called 1206# twice in a row 1207# 1208CREATE TABLE t1(c INT); 1209INSERT INTO t1 VALUES (1), (2); 1210PREPARE stmt FROM "SELECT t2.c AS f1 FROM t1 LEFT JOIN 1211 t1 t2 ON t1.c=t2.c RIGHT JOIN 1212 t1 t3 ON t1.c=t3.c 1213 GROUP BY f1;"; 1214EXECUTE stmt; 1215f1 12161 12172 1218EXECUTE stmt; 1219f1 12201 12212 1222DEALLOCATE PREPARE stmt; 1223DROP TABLE t1; 1224End of 5.1 tests 1225# 1226# Bug #59696 Optimizer fails to move WHERE condition on JOIN column 1227# when joining with a view 1228# 1229CREATE TABLE t1 ( 1230c1 INTEGER NOT NULL 1231); 1232INSERT INTO t1 VALUES (1),(2),(3); 1233CREATE TABLE t2 ( 1234pk INTEGER NOT NULL, 1235c1 INTEGER NOT NULL, 1236PRIMARY KEY (pk) 1237); 1238INSERT INTO t2 VALUES (1,4),(3,5),(2,6); 1239EXPLAIN SELECT t2.pk, t2.c1 FROM t2, t1 1240WHERE t2.pk = t1.c1 AND t2.pk >= 2; 1241id select_type table type possible_keys key key_len ref rows Extra 12421 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where 12431 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c1 1 NULL 1244SELECT t2.pk, t2.c1 FROM t2, t1 1245WHERE t2.pk = t1.c1 AND t2.pk >= 2; 1246pk c1 12472 6 12483 5 1249CREATE VIEW v_t2 AS SELECT * FROM t2; 1250EXPLAIN SELECT v_t2.pk, v_t2.c1 FROM v_t2, t1 1251WHERE v_t2.pk = t1.c1 AND v_t2.pk >= 2; 1252id select_type table type possible_keys key key_len ref rows Extra 12531 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 12541 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c1 1 Using index condition 1255SELECT v_t2.pk, v_t2.c1 FROM v_t2, t1 1256WHERE v_t2.pk = t1.c1 AND v_t2.pk >= 2; 1257pk c1 12582 6 12593 5 1260DROP VIEW v_t2; 1261DROP TABLE t1, t2; 1262# 1263# Bug 13102033 - CRASH IN COPY_FUNCS IN SQL_SELECT.CC ON JOIN + 1264# GROUP BY + ORDER BY 1265# 1266CREATE TABLE t1 ( 1267pk INTEGER NOT NULL, 1268i1 INTEGER NOT NULL, 1269i2 INTEGER NOT NULL, 1270PRIMARY KEY (pk) 1271); 1272INSERT INTO t1 VALUES (7,8,1), (8,2,2); 1273CREATE VIEW v1 AS SELECT * FROM t1; 1274EXPLAIN SELECT t1.pk 1275FROM v1, t1 1276WHERE v1.i2 = 211 1277AND v1.i2 > 7 1278OR t1.i1 < 3 1279GROUP BY t1.pk 1280ORDER BY v1.i2; 1281id select_type table type possible_keys key key_len ref rows Extra 12821 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 12831 SIMPLE t1 ALL PRIMARY NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop) 1284SELECT t1.pk 1285FROM v1, t1 1286WHERE v1.i2 = 211 1287AND v1.i2 > 7 1288OR t1.i1 < 3 1289GROUP BY t1.pk 1290ORDER BY v1.i2; 1291pk 12928 1293EXPLAIN SELECT t1.pk 1294FROM v1, t1 1295WHERE (v1.i2 = 211 AND v1.i2 > 7) 1296OR (t1.i1 < 3 AND v1.i2 < 10); 1297id select_type table type possible_keys key key_len ref rows Extra 12981 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 12991 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop) 1300SELECT t1.pk 1301FROM v1, t1 1302WHERE (v1.i2 = 211 AND v1.i2 > 7) 1303OR (t1.i1 < 3 AND v1.i2 < 10); 1304pk 13058 13068 1307DROP VIEW v1; 1308DROP TABLE t1; 1309# 1310# BUG#11752239 - 43368: STRAIGHT_JOIN DOESN'T WORK FOR NESTED JOINS 1311# 1312create table t1(c1 int primary key, c2 char(10)) engine=myisam; 1313create table t2(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam; 1314create table t3(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam; 1315create table t4(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam; 1316insert into t1 values(1,'a'); 1317insert into t2 values(1,'a', 1); 1318insert into t3 values(1,'a', 1); 1319insert into t3 values(2,'b',2); 1320insert into t4 values(1,'a', 1); 1321insert into t4 values(2,'a', 2); 1322insert into t4 values(3,'a', 3); 1323insert into t4 values(4,'a', 4); 1324insert into t1 values(2,'b'); 1325insert into t1 values(3,'c'); 1326EXPLAIN SELECT * FROM t4 JOIN (t1 join t3 on t3.ref_t1 = 1327t1.c1 join t2 on t2.ref_t1 = t1.c1) on t4.ref_t1 = t1.c1; 1328id select_type table type possible_keys key key_len ref rows Extra 13291 SIMPLE t2 system NULL NULL NULL NULL 1 NULL 13301 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 13311 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where 13321 SIMPLE t4 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) 1333EXPLAIN SELECT STRAIGHT_JOIN * FROM t4 JOIN (t1 join t3 on t3.ref_t1 = 1334t1.c1 join t2 on t2.ref_t1 = t1.c1) on t4.ref_t1 = t1.c1; 1335id select_type table type possible_keys key key_len ref rows Extra 13361 SIMPLE t4 ALL NULL NULL NULL NULL 4 NULL 13371 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) 13381 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop) 13391 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop) 1340EXPLAIN SELECT * FROM t4 STRAIGHT_JOIN (t1 join t3 on t3.ref_t1 = 1341t1.c1 join t2 on t2.ref_t1 = t1.c1) on t4.ref_t1 = t1.c1; 1342id select_type table type possible_keys key key_len ref rows Extra 13431 SIMPLE t4 ALL NULL NULL NULL NULL 4 Using where 13441 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop) 13451 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t4.ref_t1 1 NULL 13461 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop) 1347drop table t1,t2,t3,t4; 1348