Re: Bug with DELETE and IN subselect in MySQL-4.1 - Sanja Byelkin - com.mysql.lists.bugs

8 messages in com.mysql.lists.bugsRe: Bug with DELETE and IN subselect ...

From	Sent On	Attachments
jocelyn fournier	01 Dec 2002 08:08
Sanja Byelkin	01 Dec 2002 09:12
jocelyn fournier	01 Dec 2002 09:16
jocelyn fournier	01 Dec 2002 14:59
Jocelyn Fournier	30 Dec 2002 06:38
Sinisa Milivojevic	30 Dec 2002 11:41
Sanja Byelkin	28 Jan 2003 04:29
Sanja Byelkin	29 Jan 2003 01:37

Subject:	Re: Bug with DELETE and IN subselect in MySQL-4.1
From:	Sanja Byelkin (san...@mysql.com)
Date:	12/01/2002 09:12:41 AM
List:	com.mysql.lists.bugs

Hi!

On Sun, Dec 01, 2002 at 05:08:35PM +0100, jocelyn fournier wrote:

Hi,

How-to-repeat :

get ftp://support.mysql.com/pub/mysql/secret/deletebug.tar.gz

Then :

DELETE FROM searchconthardwarefr8 WHERE topic IN (SELECT DISTINCT topic FROM searchconthardwarefr9 WHERE NOT EXISTS(SELECT * FROM forumconthardwarefr8 WHERE numeropost=topic));

Query OK, 0 rows affected (6 min 27.15 sec)

searchconthardware9 and searchconthardware8 are exactly the same table.

mysql> SELECT DISTINCT topic FROM searchconthardwarefr9 WHERE NOT EXISTS(SELECT * FROM forumconthardwarefr8 WHERE numeropost=topic); +-------+ | topic | +-------+ | 1033 | | 1100 | <cut> 259 rows in set (0.00 sec)

So the first query should delete 259 rows. (6 min for this query seems to be huge ?)

Hi! 1. I need a test case to check it. 2. Subselects are not optimized yet.