~drizzle-trunk/drizzle/development

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
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;
ERROR HY000: Implicit cartesian join attempted.
SELECT * FROM t1 INNER JOIN t2;
ERROR HY000: Implicit cartesian join attempted.
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_arg datetime,
PRIMARY KEY (id),
KEY token (token(15)),
KEY timestamp_arg (timestamp_arg),
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;
ERROR HY000: Implicit cartesian join attempted.
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 where B.a >= 0;
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 where B.a >= 0;
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.