Hmmmnn ... I don't think so ... COUNT() is also an aggregate function and a SELECT COUNT(*) FROM table should work on every database without grouping.
I think it's an error in MySQL. Why else this different behaviour with and without unique index? > -----Ursprüngliche Nachricht----- > Von: Mikhail Entaltsev [mailto:mike_lynx@;smtp.ru] > Gesendet: Montag, 21. Oktober 2002 18:01 > An: [EMAIL PROTECTED]; [MYSQL] > Betreff: Re: Strange behavior of MySQL 3.23.51 > > > Stephan, > > > 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) > > you are using aggregate function. So you need to use "group by" clause. > Just add at the end "group by m.pid": > > 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) > GROUP BY m.pid > > Best regards, > Mikhail. > > P.S. But actually it is strange that MySQl didn't report syntax > error there. > :( > > ----- 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. > > > > -- > > ---------------------------------------------------------------- > > Stephan Skusa mailto:stephan.skusa@;lippe-net.de > > Lippe-Net Online-Service http://www.lippe-net.de > > Herforder Strasse 309 tel.: +49 (0)521 - 977 998 - 0 > > 33609 Bielefeld - Germany fax: +49 (0)521 - 977 998 - 9 > > ---------------------------------------------------------------- > > > > > > --------------------------------------------------------------------- > > 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 > > > > > --------------------------------------------------------------------- > 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 > > --------------------------------------------------------------------- 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
