Hi!
Thank you for your test case.
However, with latest 4.0.2 code I am getting correct results with
it. Difference in number of rows in select and delete are stemming
from the fact that select reports number of rows in result set, while
delete reports total number of rows deleted.
These are results that I get. If you still think that those are not
correct, please let me know.
I get this :
create database xx;
Query OK, 1 row affected (0.00 sec)
use xx;
Database changed
CREATE TABLE `t1` (
`titre` char(80) NOT NULL default '',
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`ouvert` tinyint(1) unsigned NOT NULL default '1',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`auteur` char(35) NOT NULL default '',
`icone` tinyint(2) unsigned NOT NULL default '0',
`nbrep` mediumint(8) unsigned NOT NULL default '0',
`maxnumrep` int(10) unsigned NOT NULL default '0',
`vue` mediumint(8) unsigned NOT NULL default '0',
`lastauteur` char(35) NOT NULL default '',
`sondage` tinyint(1) NOT NULL default '0',
`next` int(10) NOT NULL default '0',
`prev` int(10) NOT NULL default '0',
`subcat` mediumint(8) unsigned NOT NULL default '0',
PRIMARY KEY (`numeropost`),
KEY `date` (`date`),
KEY `maxnumrep` (`maxnumrep`),
KEY `auteur` (`auteur`),
KEY `sondage` (`sondage`),
KEY `subcat` (`subcat`)
) TYPE=MyISAM;
Query OK, 0 rows affected (0.01 sec)
CREATE TABLE `t2` (
`mot` char(30) NOT NULL default '',
`numreponse` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`mot`,`numreponse`),
KEY `numreponse` (`numreponse`)
) TYPE=MyISAM ROW_FORMAT=FIXED;
Query OK, 0 rows affected (0.00 sec)
CREATE TABLE `t3` (
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`pseudo` char(35) NOT NULL default '',
`numreponse` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`date`,`numreponse`,`topic`),
UNIQUE KEY `pseudo` (`pseudo`,`date`,`numreponse`,`topic`),
UNIQUE KEY `numreponse` (`numreponse`),
KEY `topic` (`topic`)
) TYPE=MyISAM ROW_FORMAT=FIXED;
Query OK, 0 rows affected (0.00 sec)
INSERT INTO t1 (titre) VALUES ('test');
Query OK, 1 row affected (0.00 sec)
INSERT INTO t3 (topic,pseudo) VALUES (1,'joce');
Query OK, 1 row affected (0.00 sec)
INSERT INTO t2 (mot) VALUES
('test'),('joce'),('ouee');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mot numreponse topic date pseudo numreponse titre numeropost
ouvert date auteur icone nbrep maxnumrep vue lastauteur
sondage next prev subcat
test 0 1 0000-00-00 00:00:00 joce 0 test 1 1
0000-00-00 00:00:00 0 0 0 0 0 0
0 0
joce 0 1 0000-00-00 00:00:00 joce 0 test 1 1
0000-00-00 00:00:00 0 0 0 0 0 0
0 0
ouee 0 1 0000-00-00 00:00:00 joce 0 test 1 1
0000-00-00 00:00:00 0 0 0 0 0 0
0 0
DELETE FROM t2,t3 USING t2 as a,t3 as
b,t1 as c WHERE b.numreponse=a.numreponse AND
b.topic=c.numeropost AND c.date<'2002-05-30';
Query OK, 4 rows affected (0.01 sec)
SELECT * FROM t2 as
a,t3 as b,t1 as c WHERE
b.numreponse=a.numreponse AND b.topic=c.numeropost AND c.date<'2002-01-01'
LIMIT 0,30;
Empty set (0.00 sec)
DELETE FROM t2,t3 USING
t2 as a,t3 as
b,t1 as c WHERE b.numreponse=a.numreponse AND
b.topic=c.numeropost AND c.date<'2002-05-30';
Query OK, 0 rows affected (0.00 sec)
drop database xx;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ 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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
