And what about the WHERE-Clause?? It is not used in Statements with Aggregat functions? ... cool answer ... but I really can't think so ...
> -----Ursprüngliche Nachricht----- > Von: Mikhail Entaltsev [mailto:mike_lynx@;smtp.ru] > Gesendet: Montag, 21. Oktober 2002 18:42 > An: [EMAIL PROTECTED]; [MYSQL] > Cc: [EMAIL PROTECTED] > Betreff: Re: Strange behavior of MySQL 3.23.51 > > > > http://www.mysql.com/doc/en/Group_by_functions.html > > "...If you use a group function in a statement containing no GROUP BY > clause, it is equivalent to grouping on all rows..." > > So when you are asking about "SELECT MAX( value ) FROM > masterTABLE AS m ..." > without GROUP BY clause, > MySQL is looking MAX(value) through whole table. > It explains also why there is no syntax error. > > > I think it's an error in MySQL. Why else this different > > behaviour with and without unique index? > > IMHO it's different issue. > > Best regards, > Mikhail. > > > ----- Original Message ----- > From: <[EMAIL PROTECTED]> > To: "[MYSQL]" <[EMAIL PROTECTED]> > Sent: Monday, October 21, 2002 6:21 PM > Subject: AW: Strange behavior of MySQL 3.23.51 > > > > > > > > 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 > --------------------------------------------------------------------- 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
