Bug in mysql-4.1 with exists

Hi,

How-to-repeat :

CREATE TABLE `forumconthardwarefr7` (
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`maxnumrep` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`numeropost`),
UNIQUE KEY `maxnumrep` (`maxnumrep`)
) TYPE=MyISAM ROW_FORMAT=FIXED;

INSERT INTO forumconthardwarefr7 (numeropost,maxnumrep) VALUES
(40143,1),(43506,2);

CREATE TABLE `searchconthardwarefr7` (
`mot` varchar(30) NOT NULL default '',
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) NOT NULL default '',
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`)
) TYPE=MyISAM ROW_FORMAT=DYNAMIC;

INSERT INTO searchconthardwarefr7 (mot,topic,date,pseudo) VALUES
('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');

SELECT numeropost,maxnumrep FROM forumconthardwarefr7 WHERE exists (SELECT 1
FROM searchconthardwarefr7 WHERE (mot='joce') AND date >= '2002-10-21' AND
forumconthardwarefr7.numeropost = searchconthardwarefr7.topic) ORDER BY
maxnumrep DESC LIMIT 0, 20;
ERROR 2013: Lost connection to MySQL server during query

If I remove the PRIMARY KEY on searchconthardwarefr7, MySQL doesn't crash
anymore.

Stack trace :

0x80a1971 handle_segfault(int) + 481
0x8286668 pthread_sighandler + 176
0x80d4e61 join_read_next_same(st_read_record*) + 17
0x80d4659 sub_select(JOIN*, st_join_table*, bool) + 281
0x80cfd55 do_select(JOIN*, List*, st_table*, Procedure*) + 261
0x80ca1ac JOIN::exec() + 2940
0x8083a6f subselect_single_select_engine::exec() + 79
0x80834a7 Item_exists_subselect::val_int() + 23
0x810404d find_all_keys(st_sort_param*, SQL_SELECT*, unsigned char**,
st_io_cache*, st_io_cache*, st_io_cache*) + 733
0x8102f20 filesort(THD*, st_table*, st_sort_field*, unsigned, SQL_SELECT*,
unsigned long, unsigned long*) + 1056
0x80d0580 create_sort_index(THD*, st_join_table*, st_order*, unsigned long,
unsigned long) + 288
0x80ca167 JOIN::exec() + 2871
0x80ca79f mysql_select(THD*, st_table_list*, List&, Item*, st_order*,
st_order*, Item*, st_order*, unsigned long, select_result*,
st_select_lex_unit*, st_select_lex*) + 111
0x80c86f7 handle_select(THD*, st_lex*, select_result*) + 247
0x80abbe9 mysql_execute_command(THD*) + 585
0x80b0585 mysql_parse(THD*, char*, unsigned) + 149
0x80aab27 dispatch_command(enum_server_command, THD*, char*, unsigned) + 855
0x80aa7b6 do_command(THD*) + 118
0x80aa08f handle_one_connection(void*) + 895
0x8283efa pthread_start_thread + 218
0x82b83aa 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-thread12938@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in mysql-4.1 with exists

Hi!

On Sun, Nov 10, 2002 at 11:05:21AM -0000, Jocelyn Fournier wrote:
> Hi,
>
> How-to-repeat :
>
> CREATE TABLE `forumconthardwarefr7` (
> `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
> `maxnumrep` int(10) unsigned NOT NULL default '0',
> PRIMARY KEY (`numeropost`),
> UNIQUE KEY `maxnumrep` (`maxnumrep`)
> ) TYPE=MyISAM ROW_FORMAT=FIXED;
>
> INSERT INTO forumconthardwarefr7 (numeropost,maxnumrep) VALUES
> (40143,1),(43506,2);
>
> CREATE TABLE `searchconthardwarefr7` (
> `mot` varchar(30) NOT NULL default '',
> `topic` mediumint(8) unsigned NOT NULL default '0',
> `date` date NOT NULL default '0000-00-00',
> `pseudo` varchar(35) NOT NULL default '',
> PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`)
> ) TYPE=MyISAM ROW_FORMAT=DYNAMIC;
>
> INSERT INTO searchconthardwarefr7 (mot,topic,date,pseudo) VALUES
> ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
>
> SELECT numeropost,maxnumrep FROM forumconthardwarefr7 WHERE exists (SELECT 1
> FROM searchconthardwarefr7 WHERE (mot='joce') AND date >= '2002-10-21' AND
> forumconthardwarefr7.numeropost = searchconthardwarefr7.topic) ORDER BY
> maxnumrep DESC LIMIT 0, 20;
> ERROR 2013: Lost connection to MySQL server during query

Thank you for bugreport! Here is fix of this bug:

diff -Nrc a/sql/sql_select.cc b/sql/sql_select.cc
*** a/sql/sql_select.cc Mon Nov 11 00:19:45 2002
--- b/sql/sql_select.cc Mon Nov 11 00:19:45 2002
***************
*** 2911,2937 ****
*/
if (join->tables > join->const_tables) // Test for not-const tables
free_io_cache(join->table[join->const_tables]);
! for (tab=join->join_tab,end=tab+join->tables ; tab != end ; tab++)
{
! delete tab->select;
! delete tab->quick;
! x_free(tab->cache.buff);
! if (tab->table)
{
! if (tab->table->key_read)
{
! tab->table->key_read=0;
! tab->table->file->extra(HA_EXTRA_NO_KEYREAD);
}
! /* Don't free index if we are using read_record */
! if (!tab->read_record.table)
! tab->table->file->index_end();
}
! end_read_record(&tab->read_record);
}
- //TODO: is enough join_free at the end of mysql_select?
- if (!join->select_lex->dependent)
- join->table=0;
}
/*
We are not using tables anymore
--- 2911,2953 ----
*/
if (join->tables > join->const_tables) // Test for not-const tables
free_io_cache(join->table[join->const_tables]);
! if (join->select_lex->dependent)
! for (tab=join->join_tab,end=tab+join->tables ; tab != end ; tab++)
! {
! if (tab->table)
! {
! if (tab->table->key_read)
! {
! tab->table->key_read= 0;
! tab->table->file->extra(HA_EXTRA_NO_KEYREAD);
! }
! /* Don't free index if we are using read_record */
! if (!tab->read_record.table)
! tab->table->file->index_end();
! }
! }
! else
{
! for (tab=join->join_tab,end=tab+join->tables ; tab != end ; tab++)
{
! delete tab->select;
! delete tab->quick;
! x_free(tab->cache.buff);
! if (tab->table)
{
! if (tab->table->key_read)
! {
! tab->table->key_read= 0;
! tab->table->file->extra(HA_EXTRA_NO_KEYREAD);
! }
! /* Don't free index if we are using read_record */
! if (!tab->read_record.table)
! tab->table->file->index_end();
}
! end_read_record(&tab->read_record);
}
! join->table= 0;
}
}
/*
We are not using tables anymore


[skip]

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

------------------------------------------------------------ ---------
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-thread12939@lists.mysql.com
To unsubscribe, e-mail