← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/join_nested.test

  • Committer: Daniel Nichter
  • Date: 2011-10-23 16:01:37 UTC
  • mto: This revision was merged to the branch mainline in revision 2448.
  • Revision ID: daniel@percona.com-20111023160137-7ac3blgz8z4tf8za
Add Administration Getting Started and Logging.  Capitalize SQL clause keywords.

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/join_nested.test
35
35
SELECT t4.a,t4.b
36
36
  FROM t4;
37
37
 
 
38
--error ER_CARTESIAN_JOIN_ATTEMPTED
38
39
SELECT t3.a,t3.b,t4.a,t4.b
39
40
  FROM t3,t4;
40
41
 
75
76
SELECT t5.a,t5.b
76
77
  FROM t5;
77
78
 
 
79
--error ER_CARTESIAN_JOIN_ATTEMPTED
78
80
SELECT t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
79
81
  FROM t3,t4,t5;
80
82
 
122
124
SELECT t7.a,t7.b
123
125
  FROM t7;
124
126
 
 
127
--sorted_result
125
128
SELECT t6.a,t6.b,t7.a,t7.b
126
 
  FROM t6,t7;
 
129
  FROM t6 CROSS JOIN t7;
127
130
 
128
131
SELECT t8.a,t8.b
129
132
  FROM t8;
130
133
 
 
134
--sorted_result
131
135
EXPLAIN EXTENDED
132
136
SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
133
137
  FROM (t6, t7)
155
159
       )
156
160
       ON t6.b >= 2 AND t5.b=t7.b;
157
161
 
 
162
--select_sorted
158
163
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
159
164
  FROM t5 
160
165
       LEFT JOIN 
189
194
       )
190
195
       ON t6.b >= 2 AND t5.b=t7.b; 
191
196
 
 
197
--sorted_result
192
198
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
193
199
       t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
194
200
  FROM t2
313
319
          t0.b=t1.b AND          
314
320
          (t2.a >= 4 OR t2.c IS NULL); 
315
321
 
 
322
--sorted_result
316
323
EXPLAIN EXTENDED
317
324
SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
318
325
       t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
402
409
       t3
403
410
       ON t2.b=t3.b;
404
411
 
 
412
--sorted_result
405
413
SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
406
414
  FROM t1, t2 
407
415
       LEFT JOIN              
409
417
       ON t2.b=t3.b
410
418
    WHERE t1.a <= 2;
411
419
 
 
420
--sorted_result
412
421
SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
413
422
  FROM t1, t3 
414
423
       RIGHT JOIN              
417
426
    WHERE t1.a <= 2;
418
427
 
419
428
SELECT t3.a,t3.b,t4.a,t4.b
420
 
  FROM t3,t4;
 
429
  FROM t3 CROSS JOIN t4;
421
430
 
422
431
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
423
432
  FROM t2 
425
434
       (t3, t4)
426
435
       ON t3.a=1 AND t2.b=t4.b;
427
436
 
 
437
--sorted_result
428
438
SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
429
439
  FROM t1, t2 
430
440
       LEFT JOIN              
432
442
       ON t3.a=1 AND t2.b=t4.b
433
443
    WHERE t1.a <= 2;
434
444
 
 
445
--sorted_result
435
446
SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
436
447
  FROM t1, (t3, t4) 
437
448
       RIGHT JOIN              
456
467
 
457
468
CREATE INDEX idx_b ON t2(b);
458
469
 
 
470
--sorted_result
459
471
EXPLAIN EXTENDED
460
472
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
461
473
  FROM (t3,t4)
469
481
       (t1,t2)
470
482
       ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
471
483
 
 
484
--sorted_result
472
485
EXPLAIN EXTENDED
473
486
SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
474
487
       t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
758
771
create table t1 (a int);
759
772
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
760
773
create table t2 (a int, filler char(100), key(a));
761
 
insert into t2 select A.a + 10*B.a, '' from t1 A, t1 B;
 
774
insert into t2 select A.a + 10*B.a, '' from t1 A CROSS JOIN t1 B;
762
775
create table t3 like t2;
763
776
insert into t3 select * from t2;
764
777