Hello.
On Mon 2002-10-21 at 18:42:05 +0200, [EMAIL PROTECTED] wrote:
[...]
> > I think it's an error in MySQL. Why else this different
> > behaviour with and without unique index?
>
> IMHO it's different issue.
IMHO, it's the main issue. Adding or removing an index, even an unique
one may not change the result of a SELECT. So he found a malfunction
with his setting.
To ponder about GROUP BY or not, while he has a malfunctioning mysqld,
only takes focus from real the issue. When the malfunction is
rectified, one can look into the query (which IMHO should work as it
is).
Stefan, I think the problem is that the optimizer makes a wrong guess,
related to UNIQUE index. Could you please try with the newest 3.23.x
version and if it is still reproducable, report it to
[EMAIL PROTECTED] (or even better, use "mysqlbug").
Regards,
Benjamin.
PS: People, please trim the quotes in your replies. It really does not
make any sense to quote the list footer several times.
[...]
> > > ----- Original Message -----
> > > From: <[EMAIL PROTECTED]>
> > > To: "[MYSQL]" <[EMAIL PROTECTED]>
> > > Sent: Monday, October 21, 2002 5:46 PM
> > > Subject: Strange behavior of MySQL 3.23.51
> > >
> > >
> > > >
> > > > I have a database structure as follows (simplyfied) :
> > > >
> > > > CREATE TABLE masterTABLE (
> > > > pid int(11) unsigned NOT NULL default '0',
> > > > c1id int(11) unsigned default NULL,
> > > > c2id int(11) unsigned default NULL,
> > > > value int(11) unsigned NOT NULL default '0',
> > > > UNIQUE KEY pid2 (pid,c1id,c2id),
> > > > UNIQUE KEY pid (pid,value)
> > > > ) TYPE=MyISAM;
> > > >
> > > > INSERT INTO masterTABLE VALUES (1, 1, NULL, 1);
> > > > INSERT INTO masterTABLE VALUES (1, 2, NULL, 2);
> > > > INSERT INTO masterTABLE VALUES (1, NULL, 3, 3);
> > > > INSERT INTO masterTABLE VALUES (1, 4, NULL, 4);
> > > > INSERT INTO masterTABLE VALUES (1, 5, NULL, 5);
> > > >
> > > > CREATE TABLE childTABLE1 (
> > > > id int(11) unsigned NOT NULL default '0',
> > > > active enum('Yes','No') NOT NULL default 'Yes',
> > > > PRIMARY KEY (id)
> > > > ) TYPE=MyISAM;
> > > >
> > > > INSERT INTO childTABLE1 VALUES (1, 'Yes');
> > > > INSERT INTO childTABLE1 VALUES (2, 'No');
> > > > INSERT INTO childTABLE1 VALUES (4, 'Yes');
> > > > INSERT INTO childTABLE1 VALUES (5, 'No');
> > > >
> > > > CREATE TABLE childTABLE2 (
> > > > id int(11) unsigned NOT NULL default '0',
> > > > active enum('Yes','No') NOT NULL default 'Yes',
> > > > PRIMARY KEY (id)
> > > > ) TYPE=MyISAM;
> > > >
> > > > INSERT INTO childTABLE2 VALUES (3, 'Yes');
> > > >
> > > > If I do this query:
> > > >
> > > > SELECT MAX( value )
> > > > FROM masterTABLE AS m
> > > > LEFT JOIN childTABLE1 AS c1
> > > > ON m.c1id = c1.id AND
> > > > c1.active = 'Yes'
> > > > LEFT JOIN childTABLE2 AS c2
> > > > ON m.c2id = c2.id
> > > > AND c2.active = 'Yes'
> > > > WHERE m.pid=1
> > > > AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
> > > >
> > > > the Result will be "5" which is probably wrong.
> > > > The expected Result ist "4".
> > > >
> > > > The correct Result will be returned if you remove
> > > > both UNIQUE KEYs (pid and pid2) from Table masterTABLE.
[...]
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
