Crash with UPDATE and SUBSELECT in MySQL-4.1

Hi,

How-to-repeat :

CREATE TABLE `r` (
`a` int(1) default NULL
) TYPE=MyISAM CHARSET=latin1;

INSERT INTO r VALUES (1),(2),(3);

If we try :

UPDATE r SET a=(SELECT DISTINCT 1 FROM r);
ERROR 1093: You can't specify target table 'r' for update in FROM clause

=> it's ok

However, we can force the update, which leads to a crash :

UPDATE r SET a=(SELECT DISTINCT 1 FROM (SELECT * FROM r) r);
ERROR 2013: Lost connection to MySQL server during query

Stack trace :

0x80aec09 handle_segfault + 681
0x8295bd8 pthread_sighandler + 176
0x8278343 hashcmp + 99
0x8278230 hash_search + 112
0x8139e3a Query_cache::invalidate(THD*, st_table_list*, char) + 186
0x80eec2c mysql_update(THD*, st_table_list*, List&, List&,
Item*, st_order*, unsigned long, enum_duplicates) + 2924
0x80bc2b9 mysql_execute_command(THD*) + 3513
0x80c0028 mysql_parse(THD*, char*, unsigned) + 248
0x80ba4bd dispatch_command(enum_server_command, THD*, char*, unsigned) + 925
0x80ba106 do_command(THD*) + 118
0x80b99d7 handle_one_connection + 855
0x82935fa pthread_start_thread + 218
0x82c74da thread_start + 4

Regards,
Jocelyn


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13336@lists.mysql.com
To unsubscribe, e-mail

Re: Crash with UPDATE and SUBSELECT in MySQL-4.1

Jocelyn Fournier writes:
> Hi,
>
> How-to-repeat :
>
> CREATE TABLE `r` (
> `a` int(1) default NULL
> ) TYPE=MyISAM CHARSET=latin1;
>
> INSERT INTO r VALUES (1),(2),(3);
>
> If we try :
>
> UPDATE r SET a=(SELECT DISTINCT 1 FROM r);
> ERROR 1093: You can't specify target table 'r' for update in FROM clause
>
> => it's ok
>
> However, we can force the update, which leads to a crash :
>
> UPDATE r SET a=(SELECT DISTINCT 1 FROM (SELECT * FROM r) r);
> ERROR 2013: Lost connection to MySQL server during query
>
> Stack trace :
>

[skip]

>
> Regards,
> Jocelyn

Hi!

Thank you very much for posting this bug-report.

Your bug report actually helped us in solving two bugs:

* improper initialization of the lock type when derived table is
called from a command that is not SELECT
* query cache code invalidating a derived table

This is a temporary patch:

===== sql/sql_cache.cc 1.41 vs edited =====
*** /tmp/sql_cache.cc-1.41-679 Fri Nov 22 15:50:45 2002
--- edited/sql/sql_cache.cc Fri Dec 20 20:18:10 2002
***************
*** 1062,1067 ****
--- 1062,1069 ----
for (; tables_used; tables_used=tables_used->next)
{
DBUG_ASSERT(!using_transactions || tables_used->table!=0);
+ if (tables_used->derived)
+ continue;
if (using_transactions &&
tables_used->table->file->has_transactions())
/*
===== sql/sql_derived.cc 1.25 vs edited =====
*** /tmp/sql_derived.cc-1.25-679 Sat Dec 14 15:13:22 2002
--- edited/sql/sql_derived.cc Fri Dec 20 20:17:05 2002
***************
*** 168,181 ****
tables->table_list->table=tables->table; // to fix a problem in EXPLAIN
}
else
! {
! if (is_union)
! unit->exclude();
! else
! sl->exclude();
! }
t->db=(char *)"";
! t->derived=(SELECT_LEX *)0; // just in case ...
table->file->info(HA_STATUS_VARIABLE);
}
}
--- 168,176 ----
tables->table_list->table=tables->table; // to fix a problem in EXPLAIN
}
else
! unit->exclude();
t->db=(char *)"";
! t->derived=(SELECT_LEX *)1; // just in case ...
table->file->info(HA_STATUS_VARIABLE);
}
}
===== sql/sql_parse.cc 1.274 vs edited =====
*** /tmp/sql_parse.cc-1.274-679 Sun Dec 15 21:50:17 2002
--- edited/sql/sql_parse.cc Thu Dec 19 22:59:40 2002
***************
*** 3001,3006 ****
--- 3001,3007 ----
lex->select_lex.link_prev= (st_select_lex_node**)&(lex->all_selects_list);
lex->olap=lex->describe=0;
lex->derived_tables= false;
+ lex->lock_option=TL_UNLOCK;
thd->check_loops_counter= thd->select_number=
lex->select_lex.select_number= 1;
thd->free_list= 0;
===== sql/sql_yacc.yy 1.209 vs edited =====
*** /tmp/sql_yacc.yy-1.209-679 Wed Dec 18 16:26:45 2002
--- edited/sql/sql_yacc.yy Thu Dec 19 22:02:14 2002
***************
*** 2509,2515 ****
lex->current_select= unit->outer_select();
if (!($$= lex->current_select->
add_table_to_list(lex->thd, new Table_ident(unit), $5, 0,
! lex->lock_option)))
YYABORT;
};

--- 2509,2517 ----
lex->current_select= unit->outer_select();
if (!($$= lex->current_select->
add_table_to_list(lex->thd, new Table_ident(unit), $5, 0,
! lex->lock_option,(List *)0,
! (List *)0)))
!
YYABORT;
};



--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13340@lists.mysql.com
To unsubscribe, e-mail