Problem with subqueries and order by clause

Hi,

I want to report a problem with sub queries in MySQL version 4.1.0.
Here is the script for a test table (generated with phpMyAdmin) and the SQL
query:

# Table structure

CREATE TABLE `test` (
  `id` int(11) NOT NULL default '0',
  `parentid` int(11) NOT NULL default '0',
  `txt` varchar(255) NOT NULL default ''
) TYPE=MyISAM;

# Table data

INSERT INTO `test` VALUES (1, 0, 'Test 1');
INSERT INTO `test` VALUES (2, 1, 'Test 1-1');
INSERT INTO `test` VALUES (3, 1, 'Test 1-2');
INSERT INTO `test` VALUES (4, 0, 'Test');

# Query

SELECT *, (SELECT COUNT(*) FROM test AS testcount WHERE testcount.parentid =
test.id) childcount FROM test WHERE parentid = 0

When I execute the query I get the following results:

+----+----------+--------+------------+
| id | parentid | txt    | childcount |
+----+----------+--------+------------+
|  1 |        0 | Test 1 |          2 |
|  4 |        0 | Test   |       NULL |
+----+----------+--------+------------+

When I add an ORDER BY clause ( ORDER BY test.txt) the results will be as
followed:

+----+----------+--------+------------+
| id | parentid | txt    | childcount |
+----+----------+--------+------------+
|  4 |        0 | Test   |          0 |
|  1 |        0 | Test 1 |       NULL |
+----+----------+--------+------------+

How is it possible that the childcount is different when I use the ORDER BY
clause, especially with subqueries?
I've tested this situation on a Microsoft SQL Server 7.0 and it works
properly.
Is this a limitation of MySQL or a BUG?

Best Regards,

Wouter van der Logt


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]