Bug in MySQL with Correlated Subqueries?

[Ed Smith]

MySQL 4.1.1-alpha incorrectly computes select-list
correlated subqueries.  See the explanation of the
problem below.  Is this a known problem?

Thanks!

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