Details
-
Type:
Bug
-
Status: Closed
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
The following query:
EXPLAIN
SELECT table1.f5
FROM t2 AS table1 JOIN t1 AS table3 ON table3.f1
WHERE table1.f1 IN (
SELECT SUBQUERY1_t1.f2
FROM t1 AS SUBQUERY1_t1 JOIN t3 AS SUBQUERY1_t3 ON SUBQUERY1_t3.f4
);
asserts in maria-5.3-mwl89 with the following backtrace:
#8 0x00a72d98 in __assert_fail () from /lib/libc.so.6
#9 0x08321c82 in sub_select_cache (join=0xae66d920, join_tab=0xae674068, end_of_records=true) at sql_select.cc:12914
#10 0x08321ecc in sub_select (join=0xae66d920, join_tab=0xae673ea8, end_of_records=true) at sql_select.cc:13082
#11 0x0832147b in do_select (join=0xae66d920, fields=0xae63248c, table=0x0, procedure=0x0) at sql_select.cc:12678
#12 0x083077ac in JOIN::exec (this=0xae66d920) at sql_select.cc:2338
#13 0x08238172 in subselect_single_select_engine::exec (this=0xae6331c0) at item_subselect.cc:2766
#14 0x0823268f in Item_subselect::exec (this=0xae6330c8) at item_subselect.cc:552
#15 0x0823288e in Item_in_subselect::exec (this=0xae6330c8) at item_subselect.cc:649
#16 0x082341e4 in Item_in_subselect::val_bool (this=0xae6330c8) at item_subselect.cc:1327
#17 0x081d18c2 in Item::val_bool_result (this=0xae6330c8) at item.h:783
#18 0x081fd39e in Item_in_optimizer::val_int (this=0xae633828) at item_cmpfunc.cc:1893
#19 0x0830554f in JOIN::exec (this=0xae668aa0) at sql_select.cc:1821
#20 0x08307f17 in mysql_select (thd=0xa8891e0, rref_pointer_array=0xa88ac5c, tables=0xae631bc0, wild_num=0, fields=..., conds=0xae6330c8, og_num=0,
order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0xae633258, unit=0xa88a8bc, select_lex=0xa88ab58)
at sql_select.cc:2548
#21 0x083313dc in mysql_explain_union (thd=0xa8891e0, unit=0xa88a8bc, result=0xae633258) at sql_select.cc:18901
#22 0x0829db0a in execute_sqlcom_select (thd=0xa8891e0, all_tables=0xae631bc0) at sql_parse.cc:5080
#23 0x082947a3 in mysql_execute_command (thd=0xa8891e0) at sql_parse.cc:2281
#24 0x082a022c in mysql_parse (thd=0xa8891e0,
rawbuf=0xae631920 "EXPLAIN\nSELECT table1.f5\nFROM t2 AS table1 JOIN t1 AS table3 ON table3.f1\nWHERE table1.f1 IN\n(\nSELECT SUBQUERY1_t1.f2\nFROM t1 AS SUBQUERY1_t1 JOIN t3 AS SUBQUERY1_t3 ON SUBQUERY1_t3.f4 )", length=186, found_semicolon=0xae7ff228) at sql_parse.cc:6109
#25 0x0829228c in dispatch_command (command=COM_QUERY, thd=0xa8891e0, packet=0xa8a1a71 "", packet_length=186) at sql_parse.cc:1209
#26 0x082916f6 in do_command (thd=0xa8891e0) at sql_parse.cc:902
#27 0x0828e7d4 in handle_one_connection (arg=0xa8891e0) at sql_connect.cc:1154
#28 0x00bea919 in start_thread () from /lib/libpthread.so.0
#29 0x00b2ccbe in clone () from /lib/libc.so.6
Working EXPLAIN is vitally important for automatic testing, so this bug needs to be fixed before testing on mwl-89 can continue.
Test case:
CREATE TABLE t1 (f1 int,f2 int) ;
INSERT IGNORE INTO t1 VALUES ('2','5'),('2',NULL);
CREATE TABLE t2 (f1 int, f5 int) ;
INSERT IGNORE INTO t2 VALUES (1,0);
CREATE TABLE t3 (f4 int) ;
INSERT IGNORE INTO t3 VALUES (0),(0);
EXPLAIN
SELECT table1.f5
FROM t2 AS table1 JOIN t1 AS table3 ON table3.f1
WHERE table1.f1 IN
(
SELECT SUBQUERY1_t1.f2
FROM t1 AS SUBQUERY1_t1 JOIN t3 AS SUBQUERY1_t3 ON SUBQUERY1_t3.f4 );
EXPLAIN from 5.3:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 DEPENDENT SUBQUERY SUBQUERY1_t1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY SUBQUERY1_t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Assertion `cache != __null' failed in sub_select_cache() on EXPLAIN in maria-5.3-mwl89
Even simpler test case (based on bug analysis):
set @@optimizer_switch='semijoin=off';
EXPLAIN
SELECT * FROM t2
WHERE f1 IN (SELECT SUBQUERY1_t1.f2
FROM t1 AS SUBQUERY1_t1 JOIN t3 AS SUBQUERY1_t3 ON SUBQUERY1_t3.f4 );