Bug with DELETE and IN subselect in MySQL-4.1
Bug with DELETE and IN subselect in MySQL-4.1
am 01.12.2002 17:08:35 von Jocelyn Fournier
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 |
259 rows in set (0.00 sec)
So the first query should delete 259 rows. (6 min for this query seems to be
huge ?)
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-thread13172@lists.mysql.com
To unsubscribe, e-mail
Re: Bug with DELETE and IN subselect in MySQL-4.1
am 01.12.2002 18:13:03 von Sanja Byelkin
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 |
>
> 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.
--
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-thread13173@lists.mysql.com
To unsubscribe, e-mail
Re: Bug with DELETE and IN subselect in MySQL-4.1
am 01.12.2002 18:17:06 von Jocelyn Fournier
Hi uploaded the testcase into
ftp://support.mysql.com/pub/mysql/secret/deletebug.tar.gz :)
----- Original Message -----
From: "Sanja Byelkin"
To: "jocelyn fournier"
Cc:
Sent: Sunday, December 01, 2002 6:13 PM
Subject: Re: Bug with DELETE and IN subselect in MySQL-4.1
> 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 |
> >
> > 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.
>
> --
> 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-thread13173@lists.mysql.com
> To unsubscribe, e-mail
>
>
>
>
------------------------------------------------------------ ---------
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-thread13175@lists.mysql.com
To unsubscribe, e-mail
Re: Bug with DELETE and IN subselect in MySQL-4.1
am 01.12.2002 23:59:24 von Jocelyn Fournier
Hi,
Ok, here is a real bug report with testcase, with a repeatable crash in
bonus ;) :
CREATE TABLE `t1` (
`mot` varchar(30) character set latin1 NOT NULL default '',
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
KEY `pseudo` (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
CREATE TABLE `t2` (
`mot` varchar(30) character set latin1 NOT NULL default '',
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
KEY `pseudo` (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
CREATE TABLE `t3` (
`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 CHARSET=latin1;
INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test');
INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
INSERT INTO t3 VALUES (1,1);
SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
numeropost=topic);
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
numeropost=topic);
SELECT DISTINCT topic FROM t1 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
numeropost=topic);
(it seems those two SELECT queries trigger the crash below)
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
Query OK, 0 rows affected (0.00 sec)
=> this should return 1 row
DELETE FROM t1 WHERE topic IN ((SELECT DISTINCT topic FROM t2 WHERE NOT
EXISTS(SELECT * FROM t3 WHERE numeropost=topic)));
Query OK, 1 row affected (0.00 sec)
=> this is ok
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
ERROR 2013: Lost connection to MySQL server during query
Stack trace :
0x80a2d11 handle_segfault + 481
0x828de98 pthread_sighandler + 176
0x80daba0 part_of_refkey(st_table*, Field*) + 80
0x80d9499 test_if_ref(Item_field*, Item*) + 73
0x80d32cd make_cond_for_table(Item*, unsigned long, unsigned long) + 189
0x80d34be make_cond_for_table(Item*, unsigned long, unsigned long) + 686
0x80d1510 make_join_select(JOIN*, SQL_SELECT*, Item*) + 224
0x80cc086 JOIN::optimize() + 790
0x808710e subselect_single_select_engine::exec() + 270
0x8086317 Item_exists_subselect::val_int() + 23
0x80e4794 mysql_delete(THD*, st_table_list*, Item*, st_order*, unsigned
long, unsigned long) + 2068
0x80b0064 mysql_execute_command(THD*) + 3748
0x80b41d8 mysql_parse(THD*, char*, unsigned) + 248
0x80ae314 dispatch_command(enum_server_command, THD*, char*, unsigned) + 852
0x80adfa6 do_command(THD*) + 118
0x80ad88e handle_one_connection + 910
0x828b8ba pthread_start_thread + 218
0x82bfbda thread_start + 4
Regards,
Jocelyn
----- Original Message -----
From: "Sanja Byelkin"
To: "jocelyn fournier"
Cc:
Sent: Sunday, December 01, 2002 6:13 PM
Subject: Re: Bug with DELETE and IN subselect in MySQL-4.1
> 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 |
> >
> > 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.
>
> --
> 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-thread13173@lists.mysql.com
> To unsubscribe, e-mail
>
>
>
>
------------------------------------------------------------ ---------
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-thread13177@lists.mysql.com
To unsubscribe, e-mail
Re: Bug with DELETE and IN subselect in MySQL-4.1
am 30.12.2002 15:39:00 von Jocelyn Fournier
Hi,
I just want to know what is the status of this bug report :)
How-to-repeat :
CREATE TABLE `t1` (
`mot` varchar(30) character set latin1 NOT NULL default '',
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
KEY `pseudo` (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
CREATE TABLE `t2` (
`mot` varchar(30) character set latin1 NOT NULL default '',
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
KEY `pseudo` (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
CREATE TABLE `t3` (
`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 CHARSET=latin1;
INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test');
INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
INSERT INTO t3 VALUES (1,1);
SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
numeropost=topic);
+-------+
| topic |
+-------+
| 2 |
+-------+
1 row in set (0.00 sec)
SELECT topic FROM t1;
+-------+
| topic |
+-------+
| 1 |
| 2 |
+-------+
2 rows in set (0.00 sec)
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
Query OK, 0 rows affected (0.00 sec)
=> this should delete 1 row.
Happy new year !
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-thread13381@lists.mysql.com
To unsubscribe, e-mail
Re: Bug with DELETE and IN subselect in MySQL-4.1
am 30.12.2002 20:41:24 von Sinisa Milivojevic
Jocelyn Fournier writes:
> Hi,
>
> I just want to know what is the status of this bug report :)
>
> How-to-repeat :
>
> Happy new year !
> Jocelyn
>
As you were just inquiring on it's status, a short answer is that we
are working on it.
Happy New Year to you too ...
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / 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-thread13382@lists.mysql.com
To unsubscribe, e-mail
Re: Bug with DELETE and IN subselect in MySQL-4.1
am 28.01.2003 13:29:54 von Sanja Byelkin
Hi!
On Mon, Dec 30, 2002 at 03:39:00PM +0100, Jocelyn Fournier wrote:
> Hi,
>
> I just want to know what is the status of this bug report :)
>
> How-to-repeat :
>
> CREATE TABLE `t1` (
> `mot` varchar(30) character set latin1 NOT NULL default '',
> `topic` mediumint(8) unsigned NOT NULL default '0',
> `date` date NOT NULL default '0000-00-00',
> `pseudo` varchar(35) character set latin1 NOT NULL default '',
> PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
> KEY `pseudo` (`pseudo`,`date`,`topic`),
> KEY `topic` (`topic`)
> ) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
>
> CREATE TABLE `t2` (
> `mot` varchar(30) character set latin1 NOT NULL default '',
> `topic` mediumint(8) unsigned NOT NULL default '0',
> `date` date NOT NULL default '0000-00-00',
> `pseudo` varchar(35) character set latin1 NOT NULL default '',
> PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
> KEY `pseudo` (`pseudo`,`date`,`topic`),
> KEY `topic` (`topic`)
> ) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
>
> CREATE TABLE `t3` (
> `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 CHARSET=latin1;
>
>
> INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test');
>
> INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
>
> INSERT INTO t3 VALUES (1,1);
>
> SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
> numeropost=topic);
>
> +-------+
> | topic |
> +-------+
> | 2 |
> +-------+
> 1 row in set (0.00 sec)
>
> SELECT topic FROM t1;
>
> +-------+
> | topic |
> +-------+
> | 1 |
> | 2 |
> +-------+
> 2 rows in set (0.00 sec)
>
> DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
> EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
>
> Query OK, 0 rows affected (0.00 sec)
>
> => this should delete 1 row.
Thank you for bugreport! This bug is gone in my local repository, but I
added above to mysql-test.
--
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-thread13612@lists.mysql.com
To unsubscribe, e-mail
Re: Bug with DELETE and IN subselect in MySQL-4.1
am 29.01.2003 10:37:53 von Sanja Byelkin
Hi!
On Sun, Dec 01, 2002 at 11:59:24PM +0100, jocelyn fournier wrote:
> Hi,
>
> Ok, here is a real bug report with testcase, with a repeatable crash in
> bonus ;) :
>
> CREATE TABLE `t1` (
> `mot` varchar(30) character set latin1 NOT NULL default '',
> `topic` mediumint(8) unsigned NOT NULL default '0',
> `date` date NOT NULL default '0000-00-00',
> `pseudo` varchar(35) character set latin1 NOT NULL default '',
> PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
> KEY `pseudo` (`pseudo`,`date`,`topic`),
> KEY `topic` (`topic`)
> ) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
>
> CREATE TABLE `t2` (
> `mot` varchar(30) character set latin1 NOT NULL default '',
> `topic` mediumint(8) unsigned NOT NULL default '0',
> `date` date NOT NULL default '0000-00-00',
> `pseudo` varchar(35) character set latin1 NOT NULL default '',
> PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
> KEY `pseudo` (`pseudo`,`date`,`topic`),
> KEY `topic` (`topic`)
> ) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
>
> CREATE TABLE `t3` (
> `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 CHARSET=latin1;
>
>
> INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test');
>
> INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
>
> INSERT INTO t3 VALUES (1,1);
>
> SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
> numeropost=topic);
>
> DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
> EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
>
> SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
> numeropost=topic);
>
> SELECT DISTINCT topic FROM t1 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
> numeropost=topic);
>
> (it seems those two SELECT queries trigger the crash below)
>
> DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
> EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
>
> Query OK, 0 rows affected (0.00 sec)
>
> => this should return 1 row
>
>
> DELETE FROM t1 WHERE topic IN ((SELECT DISTINCT topic FROM t2 WHERE NOT
> EXISTS(SELECT * FROM t3 WHERE numeropost=topic)));
>
> Query OK, 1 row affected (0.00 sec)
>
> => this is ok
>
> DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
> EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
>
> ERROR 2013: Lost connection to MySQL server during query
Thank you for bugreport. This bug is gone now (in my tree, committed, but not
pushed). I tested above queries all together and separately: they works as
expected.
[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-thread13624@lists.mysql.com
To unsubscribe, e-mail