Details
Minor Description
As already reported in mysql bug http://bugs.mysql.com/bug.php?id=54429 , using low values for optimizer_search_depth causes an easy crash on multiple-table joins.
The RQG sometimes generates joins involving many tables. In order to restrict the total optimization time per query, and thus run more queries per test run, optimizer_search_depth must be safe to use and not assert.
assertion:
mysqld: sql_select.cc:5498: bool greedy_search(JOIN*, table_map, uint, uint): Assertion `!is_interleave_error' failed.
|
backtrace:
#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
|
#9 0x0831785c in greedy_search (join=0xae688eb0, remaining_tables=55, search_depth=4, prune_level=1) at sql_select.cc:5498
|
#10 0x08316ebc in choose_plan (join=0xae688eb0, join_tables=55) at sql_select.cc:5120
|
#11 0x0831205f in make_join_statistics (join=0xae688eb0, tables_arg=0xae631c80, conds=0x0, keyuse_array=0xae68dbec) at sql_select.cc:3216
|
#12 0x08309e93 in JOIN::optimize (this=0xae688eb0) at sql_select.cc:956
|
#13 0x0830fa81 in mysql_select (thd=0xb1fda70, rref_pointer_array=0xb1ff50c, tables=0xae631c80, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae64b8b0,
|
unit=0xb1ff16c, select_lex=0xb1ff408) at sql_select.cc:2607
|
#14 0x0830800d in handle_select (thd=0xb1fda70, lex=0xb1ff110, result=0xae64b8b0, setup_tables_done_option=0) at sql_select.cc:286
|
#15 0x082a3400 in execute_sqlcom_select (thd=0xb1fda70, all_tables=0xae631c80) at sql_parse.cc:5070
|
#16 0x08299e1b in mysql_execute_command (thd=0xb1fda70) at sql_parse.cc:2234
|
#17 0x082a599e in mysql_parse (thd=0xb1fda70,
|
rawbuf=0xae631940 "SELECT table2.f4 FROM t1 AS table1 LEFT JOIN t1 AS table2 LEFT JOIN t2 AS table3 LEFT JOIN t3 AS table4 ON table3 .f1 = table4.f3 ON table2 .f1 LEFT JOIN t4 AS table5 JOIN t5 ON tab"...,
|
length=262, found_semicolon=0xae983228) at sql_parse.cc:6077
|
#18 0x08297904 in dispatch_command (command=COM_QUERY, thd=0xb1fda70,
|
packet=0xb217c31 "SELECT table2.f4 FROM t1 AS table1 LEFT JOIN t1 AS table2 LEFT JOIN t2 AS table3 LEFT JOIN t3 AS table4 ON table3 .f1 = table4.f3 ON table2 .f1 LEFT JOIN t4 AS table5 JOIN t5 ON tab"...,
|
packet_length=265) at sql_parse.cc:1210
|
#19 0x08296d6e in do_command (thd=0xb1fda70) at sql_parse.cc:903
|
#20 0x08293e4a in handle_one_connection (arg=0xb1fda70) at sql_connect.cc:1154
|
#21 0x00821919 in start_thread () from /lib/libpthread.so.0
|
#22 0x0076acce in clone () from /lib/libc.so.6
|
test case:
SET SESSION optimizer_search_depth = 4; |
|
|
CREATE TABLE t1 (f1 int,f2 int,f3 int,f4 int) ; |
INSERT IGNORE INTO t1 VALUES (0,0,2,0),(NULL,0,2,0); |
|
|
CREATE TABLE t2 (f1 int) ; |
CREATE TABLE t3 (f3 int,PRIMARY KEY (f3)) ; |
CREATE TABLE t4 (f5 int) ; |
CREATE TABLE t5 (f2 int) ; |
|
|
SELECT alias2.f4 FROM t1 AS alias1 |
LEFT JOIN t1 AS alias2 |
LEFT JOIN t2 AS alias3 |
LEFT JOIN t3 AS alias4 ON alias3.f1 = alias4.f3 |
ON alias2.f1 |
LEFT JOIN t4 AS alias5 |
JOIN t5 ON alias5.f5 |
ON alias2.f3 ON alias1.f2 ; |
Attachments
Issue Links
Activity
As pointed out by Elena: The testcase posted by Philip at 2011-04-06 13:49 still fails (the other one wasn't repeatable for me). Re-opening.
The 'upstream' tag has been removed because the upstream bug was fixed quite long time ago. I don't know if the upstream bugfix was correct, but at least the original test case does not cause a crash on mysql-5.5 or mysql-5.6 any longer.
I've listed all major versions in the 'Fix version' field, please feel free to adjust if you think it isn't worth fixing in old versions (5.1, 5.2).
See also MDEV-4270.
The testcase from the report is repeatable on mariadb-5.5 when one has optimizer_switch='table_elimination=on', and not repeatable when table_elimination is off.
table_elimination will eliminate one table, alias4.
The fix from MySQL has been merged+adjusted for table elimination long ago: psergey@askmonty.org-20110111104216-3fyifys406a1mvgf