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,