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 [at] m.gmane.org
