----- Original Message -----
From: "Jocelyn Fournier" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Saturday, May 25, 2002 3:35 PM
Subject: Re: multi-table delete bug
Hi,
Yes, you're right, but to see the problem, you have to restart mysql
between
> INSERT INTO t2 (mot) VALUES
> ('test'),('joce'),('ouee');
>
> Query OK, 3 rows affected (0.02 sec)
> Records: 3 Duplicates: 0 Warnings: 0
AND
> 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';
Then you will obtain :
mysql> INSERT INTO t2 (mot) VALUES
-> ('test'),('joce'),('ouee');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> exit
[root@forum] /home/mysql-4.0> mysqladmin -uroot -p shutdown
Enter password:
[root@forum] /home/mysql-4.0> mysqld_safe &
[1] 22419
[root@forum] /home/mysql-4.0> Starting mysqld daemon with databases from
/home/mysql
[root@forum] /home/mysql-4.0> mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 95 to server version: 4.0.2-alpha
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use Hardwarefr;
Database changed
mysql> 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)
Regards,
Jocelyn
> ----- Original Message -----
> From: "Sinisa Milivojevic" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Saturday, May 25, 2002 12:57 PM
> Subject: Re: multi-table delete bug
>
>
> >
> > 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
