Bug/Crash in DELETE with IN subselect with MySQL-4.1

Hi,

How-to-repeat :


CREATE TABLE `forumconthardwarefr8` (
`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;


CREATE TABLE `searchconthardwarefr8` (
`topic` mediumint(8) unsigned NOT NULL default '0',
PRIMARY KEY `topic` (`topic`)
) TYPE=MyISAM ROW_FORMAT=DYNAMIC;


INSERT INTO forumconthardwarefr8 (numeropost,maxnumrep) VALUES
(1,1),(2,2),(3,3)

INSERT INTO searchconthardwarefr8 (topic) VALUES (1),(2),(3),(4),(5),(6);

mysql> SELECT DISTINCT topic FROM searchconthardwarefr8 WHERE NOT
EXISTS(SELECT * FROM forumconthardwarefr8 WHERE numeropost=topic);
+-------+
| topic |
+-------+
| 4 |
| 5 |
| 6 |
+-------+
3 rows in set (0.00 sec)

mysql> DELETE FROM searchconthardwarefr8 WHERE topic IN (SELECT DISTINCT
topic FROM searchconthardwarefr8 WHERE NOT EXISTS(SELECT * FROM
forumconthardwarefr8 WHERE numeropost=topic));
Query OK, 0 rows affected (0.00 sec)

=> 3 rows should be deleted here.

Sometimes it also crashes MySQL with the following stack trace :

0x80a2c91 handle_segfault + 481
0x828dbd8 pthread_sighandler + 176
0x80d9270 test_if_ref(Item_field*, Item*) + 96
0x80d308d make_cond_for_table(Item*, unsigned long, unsigned long) + 189
0x80d327e make_cond_for_table(Item*, unsigned long, unsigned long) + 686
0x80d12d0 make_join_select(JOIN*, SQL_SELECT*, Item*) + 224
0x80cbe46 JOIN::optimize() + 790
0x808708e subselect_single_select_engine::exec() + 270
0x8086297 Item_exists_subselect::val_int() + 23
0x80e4408 mysql_delete(THD*, st_table_list*, Item*, st_order*, unsigned
long, unsigned long) + 1992
0x80b0384 mysql_execute_command(THD*) + 4676
0x80b40a8 mysql_parse(THD*, char*, unsigned) + 248
0x80ae294 dispatch_command(enum_server_command, THD*, char*, unsigned) + 852
0x80adf26 do_command(THD*) + 118
0x80ad80e handle_one_connection + 910
0x828b5fa pthread_start_thread + 218
0x82bf91a 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-thread13157@lists.mysql.com
To unsubscribe, e-mail

Re: Bug/Crash in DELETE with IN subselect with MySQL-4.1

Hi!

On Sat, Nov 30, 2002 at 10:43:46AM -0000, Jocelyn Fournier wrote:
> Hi,
>
> How-to-repeat :
>
>
> CREATE TABLE `forumconthardwarefr8` (
> `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;
>
>
> CREATE TABLE `searchconthardwarefr8` (
> `topic` mediumint(8) unsigned NOT NULL default '0',
> PRIMARY KEY `topic` (`topic`)
> ) TYPE=MyISAM ROW_FORMAT=DYNAMIC;
>
>
> INSERT INTO forumconthardwarefr8 (numeropost,maxnumrep) VALUES
> (1,1),(2,2),(3,3)
>
> INSERT INTO searchconthardwarefr8 (topic) VALUES (1),(2),(3),(4),(5),(6);
>
> mysql> SELECT DISTINCT topic FROM searchconthardwarefr8 WHERE NOT
> EXISTS(SELECT * FROM forumconthardwarefr8 WHERE numeropost=topic);
> +-------+
> | topic |
> +-------+
> | 4 |
> | 5 |
> | 6 |
> +-------+
> 3 rows in set (0.00 sec)
>
> mysql> DELETE FROM searchconthardwarefr8 WHERE topic IN (SELECT DISTINCT
> topic FROM searchconthardwarefr8 WHERE NOT EXISTS(SELECT * FROM
> forumconthardwarefr8 WHERE numeropost=topic));
> Query OK, 0 rows affected (0.00 sec)
>
> => 3 rows should be deleted here.

Now it is impossible:
DELETE FROM searchconthardwarefr8 WHERE topic IN (SELECT DISTINCT topic FROM
searchconthardwarefr8 WHERE NOT EXISTS(SELECT * FROM forumconthardwarefr8
WHERE numeropost=topic))' failed: 1093: INSERT TABLE 'searchconthardwarefr8'
isn't allowed in FROM table list

(text of error message will be fixed)

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