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/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Problem with subqueries and order by clause

Wouter,

> When I add an ORDER BY clause ( ORDER BY test.txt) the results will be as
> followed:
>=20
> +----+----------+--------+------------+
> | id | parentid | txt | childcount |
> +----+----------+--------+------------+
> | 4 | 0 | Test | 0 |
> | 1 | 0 | Test 1 | NULL |
> +----+----------+--------+------------+
>=20
> How is it possible that the childcount is different when I use the ORDER =
BY
> clause, especially with subqueries?

This has been fixed in the upcoming 4.1.1 release (just tested it to make
sure).


Rgds,
Indrek

--=20
| Indrek Siitan, MySQL AB, Support Engineer & Bugmaster
| Uuem=F5isa, Haapsalu, Estonia
+-=20
| Are you MySQL Certified? http://www.mysql.com/certification/
--


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org