Re: Bug in MySQL with Correlated Subqueries?
Are you saying this is a bug which has already been fixed for the next release? In any case, it may be worth noting that the manual says correlated subqueries are inefficient and likely to be slow. Rewriting the query as a join might improve performance. http://www.mysql.com/doc/en/Correlated_subqueries.html. SELECT person.pid, count(*) AS C FROM person, phone WHERE person.pid=phone.pid GROUP BY person.pid; +--+---+ | pid | C | +--+---+ |1 | 2 | |2 | 1 | +--+---+ 2 rows in set (0.00 sec) Michael Miguel Angel Solorzano wrote: At 13:59 27/3/2004, Ed Smith wrote: Hi, Below the results from a server built with BK 4.1 tree 3 days ago: Correct results snipped Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug in MySQL with Correlated Subqueries?
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in MySQL with Correlated Subqueries?
At 13:59 27/3/2004, Ed Smith wrote: Hi, Below the results from a server built with BK 4.1 tree 3 days ago: C:\mysql\binmysqld --standalone --console --ansi --default-table-type=innodb 040328 0:36:59 InnoDB: Started; log sequence number 0 43634 mysqld: ready for connections. Version: '4.1.2-alpha-max-debug' socket: '' port: 3306 C:\mysql\binmysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.1.2-alpha-max-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE TABLE person (pid INTEGER, name CHAR(5)); Query OK, 0 rows affected (0.17 sec) mysql CREATE TABLE phone (pid INTEGER, num CHAR(10)); Query OK, 0 rows affected (0.16 sec) mysql mysql mysql INSERT INTO PERSON VALUES (1, 'Bob'); Query OK, 1 row affected (0.07 sec) mysql INSERT INTO PERSON VALUES (2, 'Jane'); Query OK, 1 row affected (0.05 sec) mysql INSERT INTO PHONE VALUES (1, '12345'); Query OK, 1 row affected (0.07 sec) mysql INSERT INTO PHONE VALUES (1, '23456'); Query OK, 1 row affected (0.05 sec) mysql INSERT INTO PHONE VALUES (2, '34567'); Query OK, 1 row affected (0.06 sec) mysql SELECT PID, (SELECT COUNT(*) FROM PHONE H WHERE - H.pid = P.pid) AS C FROM person P; +--+---+ | PID | C | +--+---+ |1 | 2 | |2 | 1 | +--+---+ 2 rows in set (0.01 sec) 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 | 2 | +--+---+ 2 rows in set (0.00 sec) Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
