MySQL Bugs: #28561: Assertion fails in do_select(): updatable VIEW with subselect and CHECK OPTION

Bug #28561	Assertion fails in do_select(): updatable VIEW with subselect and CHECK OPTION
Submitted:	21 May 2007 14:32	Modified:	19 Jun 2007 0:58
Reporter:	Gleb Shchepa	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0-bk, 5.1-bk	OS:	Any
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
UPDATE of VIEW with subselect and CHECK OPTIOIN may cause assertion failure:

    DBUG_ASSERT(join->tables);

Following source code (in sql_select.cc) uses `join' variable as regular pointer.

How to repeat:
CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT, c INT DEFAULT 0);
INSERT INTO t1 (id) VALUES (1);
INSERT INTO t2 (id) VALUES (1);
CREATE VIEW v AS SELECT t2.c FROM t1, t2 WHERE t1.id=t2.id AND 1 in (SELECT id FROM t1) WITH CHECK OPTION;
UPDATE v SET c=1;

I meant "Following source code (in sql_select.cc) uses `join->tables' as regular pointer" in the previous commentary.

verified:

Version: '5.0.44-debug'  socket: '/tmp/mysql.sock'  port: 3306  yes
mysqld: sql_select.cc:10288: int do_select(JOIN*, List<Item>*, TABLE*, Procedure*): Assertion `join->tables' failed.
070522  0:08:06 - mysqld got signal 6;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=209715200
read_buffer_size=131072
max_used_connections=135
max_connections=1000
threads_connected=102
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2380764 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x4e421878
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbc1fe0d8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80e1705 handle_segfault + 529
0x83dd68d __pthread_sighandler + 173
0x8418611 __kill + 17
0x83daf5b __pthread_raise + 27
0x84189e4 abort + 340
0x8414430 __assert_fail + 224
0x8148404 _Z9do_selectP4JOINP4ListI4ItemEP8st_tableP9Procedure + 1286
0x8137ae6 _ZN4JOIN4execEv + 6934
0x80b489b _ZN30subselect_single_select_engine4execEv + 843
0x80afbd6 _ZN14Item_subselect4execEv + 38
0x80b1205 _ZN17Item_in_subselect8val_boolEv + 67
0x805ee4d _ZN4Item15val_bool_resultEv + 17
0x80821dd _ZN17Item_in_optimizer7val_intEv + 417
0x804e933 _ZN4Item8val_boolEv + 43
0x80883b7 _ZN13Item_cond_and7val_intEv + 85
0x8130a4a _ZN13st_table_list17view_check_optionEP3THDb + 44
0x81684df _ZN12multi_update10do_updatesEb + 1153
0x8168953 _ZN12multi_update8send_eofEv + 55
0x814823a _Z9do_selectP4JOINP4ListI4ItemEP8st_tableP9Procedure + 828
0x8137ae6 _ZN4JOIN4execEv + 6934
0x8137fca _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_sel + 638
0x816691f _Z18mysql_multi_updateP3THDP13st_table_listP4ListI4ItemES6_PS4_y15enum_duplicatesbP18st_select_lex_unitP13st_select_lex + 367
0x80fb2da _Z21mysql_execute_commandP3THD + 10890
0x81013ee _Z11mysql_parseP3THDPKcjPS2_ + 348
0x80f7368 _Z16dispatch_command19enum_server_commandP3THDPcj + 1956
0x80f6bb9 _Z10do_commandP3THD + 529
0x80f5cbe handle_one_connection + 874
0x83d8561 pthread_start_thread + 225
0x8438f4a __clone + 106
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8c404f8 = UPDATE v SET c=1
thd->thread_id=13648
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
mysqld: my_new.cc:50: int __cxa_pure_virtual(): Assertion `"Pure virtual method called." == "Aborted"' failed.
mysqld: my_new.cc:50: int __cxa_pure_virtual(): Assertion `"Pure virtual method called." == "Aborted"' failed.
mysqld: my_new.cc:50: int __cxa_pure_virtual(): Assertion `"Pure virtual method called." == "Aborted"' failed.

Number of processes running now: 13

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27249

ChangeSet@1.2500, 2007-05-23 19:04:12-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #28561: assertion abort for update on multi-table view with
  CHECK OPTION and a subquery in WHERE condition.
  The abort was triggered by setting the value of join->tables for
  subqueries in the function JOIN::cleanup. This function was called
  after an invocation of the JOIN::join_free method for subqueries
  used in WHERE condition.

Pushed into 5.1.20-beta

Pushed into 5.0.44

Noted in 5.0.44, 5.1.20 changelogs.