1256
1256
DROP TABLE t1,t2;
1258
# Bug#47650: using group by with rollup without indexes returns incorrect
1259
# results with where
1261
CREATE TABLE t1 ( a INT );
1262
INSERT INTO t1 VALUES (1);
1263
CREATE TABLE t2 ( a INT, b INT );
1264
INSERT INTO t2 VALUES (1, 1),(1, 2),(1, 3),(2, 4),(2, 5);
1266
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
1267
FROM t1 LEFT JOIN t2 USING( a )
1268
GROUP BY t1.a WITH ROLLUP;
1269
id select_type table type possible_keys key key_len ref rows Extra
1270
1 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort
1271
1 SIMPLE t2 ALL NULL NULL NULL NULL 5
1272
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
1273
FROM t1 LEFT JOIN t2 USING( a )
1274
GROUP BY t1.a WITH ROLLUP;
1275
a COUNT( t2.b ) SUM( t2.b ) MAX( t2.b )
1279
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
1280
FROM t1 JOIN t2 USING( a )
1281
GROUP BY t1.a WITH ROLLUP;
1282
id select_type table type possible_keys key key_len ref rows Extra
1283
1 SIMPLE t1 system NULL NULL NULL NULL 1 Using filesort
1284
1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where
1285
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
1286
FROM t1 JOIN t2 USING( a )
1287
GROUP BY t1.a WITH ROLLUP;
1288
a COUNT( t2.b ) SUM( t2.b ) MAX( t2.b )
1293
# Bug#51598 Inconsistent behaviour with a COALESCE statement inside an IN comparison
1295
CREATE TABLE t1(f1 INT, f2 INT, f3 INT);
1296
INSERT INTO t1 VALUES (1, NULL, 3);
1297
CREATE TABLE t2(f1 INT, f2 INT);
1298
INSERT INTO t2 VALUES (2, 1);
1299
EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2
1300
WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2));
1301
id select_type table type possible_keys key key_len ref rows filtered Extra
1302
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
1303
1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00
1305
Note 1003 select '1' AS `f1`,NULL AS `f2`,'3' AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t1` left join `test`.`t2` on(multiple equal(NULL)) where ((coalesce('1',NULL),'3') in ((1,3),(2,2)))
1306
SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2
1307
WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2));
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
1401
# Bug#57688 Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field
1403
CREATE TABLE t1 (f1 INT NOT NULL, PRIMARY KEY (f1));
1404
CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY (f1, f2));
1405
INSERT INTO t1 VALUES (4);
1406
INSERT INTO t2 VALUES (3, 3);
1407
INSERT INTO t2 VALUES (7, 7);
1408
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
1410
GROUP BY t2.f1, t2.f2;
1411
id select_type table type possible_keys key key_len ref rows Extra
1412
1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using temporary; Using filesort
1413
1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using index
1414
SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
1416
GROUP BY t2.f1, t2.f2;
1419
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
1420
WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
1421
GROUP BY t2.f1, t2.f2;
1422
id select_type table type possible_keys key key_len ref rows Extra
1423
1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort
1424
1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using where; Using index
1425
SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
1426
WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
1427
GROUP BY t2.f1, t2.f2;