1309
1309
1 NULL 3 NULL NULL
1310
1310
DROP TABLE t1, t2;
1312
# Bug#52357: Assertion failed: join->best_read in greedy_search
1313
# optimizer_search_depth=0
1315
CREATE TABLE t1( a INT );
1316
INSERT INTO t1 VALUES (1),(2);
1317
SET optimizer_search_depth = 0;
1318
# Should not core dump on query preparation
1321
FROM t1 tt3 LEFT OUTER JOIN t1 tt4 ON 1
1322
LEFT OUTER JOIN t1 tt5 ON 1
1323
LEFT OUTER JOIN t1 tt6 ON 1
1324
LEFT OUTER JOIN t1 tt7 ON 1
1325
LEFT OUTER JOIN t1 tt8 ON 1
1326
RIGHT OUTER JOIN t1 tt2 ON 1
1327
RIGHT OUTER JOIN t1 tt1 ON 1
1328
STRAIGHT_JOIN t1 tt9 ON 1;
1329
id select_type table type possible_keys key key_len ref rows Extra
1330
1 SIMPLE tt1 ALL NULL NULL NULL NULL 2
1331
1 SIMPLE tt2 ALL NULL NULL NULL NULL 2
1332
1 SIMPLE tt3 ALL NULL NULL NULL NULL 2
1333
1 SIMPLE tt4 ALL NULL NULL NULL NULL 2
1334
1 SIMPLE tt5 ALL NULL NULL NULL NULL 2
1335
1 SIMPLE tt6 ALL NULL NULL NULL NULL 2
1336
1 SIMPLE tt7 ALL NULL NULL NULL NULL 2
1337
1 SIMPLE tt8 ALL NULL NULL NULL NULL 2
1338
1 SIMPLE tt9 ALL NULL NULL NULL NULL 2 Using join buffer
1339
SET optimizer_search_depth = DEFAULT;
1342
# Bug#46091 STRAIGHT_JOIN + RIGHT JOIN returns different result
1344
CREATE TABLE t1 (f1 INT NOT NULL);
1345
INSERT INTO t1 VALUES (9),(0);
1346
CREATE TABLE t2 (f1 INT NOT NULL);
1347
INSERT INTO t2 VALUES
1348
(5),(3),(0),(3),(1),(0),(1),(7),(1),(0),(0),(8),(4),(9),(0),(2),(0),(8),(5),(1);
1349
SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1
1350
RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1;
1353
EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1
1354
RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1;
1355
id select_type table type possible_keys key key_len ref rows Extra
1356
1 SIMPLE TA2 ALL NULL NULL NULL NULL 20 Using where
1357
1 SIMPLE TA3 ALL NULL NULL NULL NULL 20 Using join buffer
1358
1 SIMPLE TA1 ALL NULL NULL NULL NULL 2
1361
# Bug#48971 Segfault in add_found_match_trig_cond () at sql_select.cc:5990
1363
CREATE TABLE t1(f1 INT, PRIMARY KEY (f1));
1364
INSERT INTO t1 VALUES (1),(2);
1365
EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1
1367
RIGHT JOIN t1 AS jt3
1369
LEFT JOIN t1 AS jt5 ON 1
1371
RIGHT JOIN t1 AS jt6 ON jt6.f1
1373
id select_type table type possible_keys key key_len ref rows filtered Extra
1374
1 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using index
1375
1 SIMPLE jt6 index NULL PRIMARY 4 NULL 2 100.00 Using index
1376
1 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using index
1377
1 SIMPLE jt4 index NULL PRIMARY 4 NULL 2 100.00 Using index
1378
1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using index
1379
1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using index
1381
Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt1` left join (`test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1))) on(1) where 1
1382
EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1
1383
RIGHT JOIN t1 AS jt2
1384
RIGHT JOIN t1 AS jt3
1386
LEFT JOIN t1 AS jt5 ON 1
1388
RIGHT JOIN t1 AS jt6 ON jt6.f1
1390
id select_type table type possible_keys key key_len ref rows filtered Extra
1391
1 SIMPLE jt6 index NULL PRIMARY 4 NULL 2 100.00 Using index
1392
1 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using index
1393
1 SIMPLE jt4 index NULL PRIMARY 4 NULL 2 100.00 Using index
1394
1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using index
1395
1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using index
1396
1 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using index
1398
Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1)) left join `test`.`t1` `jt1` on(1) where 1
1311
1400
End of 5.1 tests