Consider the following schema and data: CREATE TABLE person (pid INTEGER, name CHAR(5)); CREATE TABLE phone (pid INTEGER, num CHAR(10));
INSERT INTO PERSON VALUES (1, 'Bob'); INSERT INTO PERSON VALUES (2, 'Jane'); INSERT INTO PHONE VALUES (1, '12345'); INSERT INTO PHONE VALUES (1, '23456'); INSERT INTO PHONE VALUES (2, '34567'); Now consider the following query: mysql> SELECT PID, (SELECT COUNT(*) FROM PHONE H WHERE H.pid = P.pid) AS C FROM person P; +------+---+ | PID | C | +------+---+ | 1 | 2 | | 2 | 0 | +------+---+ 2 rows in set (0.00 sec) Obviously the count for PID=2 is incorrect. If I run the same query with an ORDER BY, I get mysql> SELECT PID, (SELECT COUNT(*) FROM PHONE H WHERE H.pid = P.pid) AS C FROM person P ORDER BY pid DESC; +------+---+ | PID | C | +------+---+ | 2 | 1 | | 1 | 0 | +------+---+ 2 rows in set (0.00 sec) Now PID=1 is wrong. It appears to always mess up the last element. What's up? I ran this on MySQL 4.1.1-alpha under both Linux (RedHat) and Windows with the same results. I start up the server as follows: bin\mysqld-max --standalone --console --ansi --default-table-type=innodb __________________________________ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
