Re: Crash with MySQL-4.1 in IN Subselect + SQL_CALC_FOUND_ROWS

Hi!

On Sat, Oct 26, 2002 at 03:03:59PM +0200, Jocelyn Fournier wrote:
> Hi,
>
> With latest changeset 1.1364 which introduces "IN subselect", MySQL crashes
> if SQL_CALC_FOUND_ROWS is used in the query.
>
> How-to-repeat :
>
> CREATE TABLE `searchconthardwarefr3` (
> `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 (`pseudo`,`date`,`topic`),
> KEY `topic` (`topic`)
> ) TYPE=MyISAM ROW_FORMAT=DYNAMIC
>
> INSERT INTO searchconthardwarefr3 (topic,date,pseudo) VALUES
> ('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
>
> 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);
>
> mysql> SELECT /*!40001 SQL_CALC_FOUND_ROWS */ numeropost,maxnumrep FROM
> forumconthardwarefr7 WHERE numeropost IN (SELECT topic FROM
> searchconthardwarefr3 WHERE pseudo='joce' AND date >= '2002-10-06') ORDER BY
> maxnumrep DESC LIMIT 0,20;

Thank you for bug report, I fixed it, fix will be soon in repository.

[skip]

> BTW, I assume IN subselects are not yet optimised ? :

Yes. Optimization will be started after finishing main subselect
constructions.

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