Re: Bug with DELETE and IN subselect in MySQL-4.1

[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