MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:H. Steuer Date:November 15 2006 10:48am
Subject:bug in mysql with COUNT() in subqueries
View as plain text  
guys,

strange things happen when using COUNT() in subqueries. even the use of
HAVING in a SELECT statement turns up a weired result, as it should work
on the result set and should be filtered while rows are returned to the
client.

(you can find the selects and stuff in a more readable way on
http://pastebin.com/824848



mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 4.1.18    |
+-----------+
1 row in set (0.00 sec)

... same with other versions, too.

mysql> SELECT COUNT( distinct aCount.isin ) FROM anleihen aCount,
offlinekurse oCount WHERE oCount.isin='DE0001135176' AND
aCount.isin=oCount.isin AND
aCount.fonds=10000;                                        
+-------------------------------+
| COUNT( distinct aCount.isin ) |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.00 sec)

... so in fact the count is 1.

SELECT f.nr, (SELECT COUNT( distinct aCount.isin ) FROM anleihen aCount,
offlinekurse oCount WHERE oCount.isin='DE0001135176' AND
aCount.isin=oCount.isin AND aCount.fonds=f.nr) AS counter FROM fonds f
HAVING nr=10000;
+-------+---------+
| nr    | counter |
+-------+---------+
| 10000 |       1 |
+-------+---------+
1 row in set (0.01 sec)


... correct result here, but simply removing the HAVING returns:

mysql> SELECT f.nr, (SELECT COUNT( distinct aCount.isin ) FROM anleihen
aCount,  offlinekurse oCount WHERE oCount.isin='DE0001135176' AND
aCount.isin=oCount.isin AND aCount.fonds=f.nr) AS counter FROM fonds f;
+-------+---------+
| nr    | counter |
+-------+---------+
|  2000 |       0 |
|  2001 |       0 |
|  2002 |       0 |
|  2003 |       0 |
|  2004 |       0 |
|  2005 |       0 |
|  2100 |       0 |
|  2101 |       0 |
|  2200 |       0 |
|  2201 |       0 |
|  2202 |       0 |
|  2203 |       0 |
|  2300 |       0 |
|  2301 |       0 |
|  2302 |       0 |
|  2303 |       0 |
|  2304 |       0 |
|  2305 |       0 |
|  2306 |       0 |
|  2307 |       0 |
|  2400 |       0 |
|  2401 |       0 |
|  2500 |       0 |
|  2501 |       0 |
|  2600 |       0 |
|  2601 |       0 |
|  2602 |       0 |
|  2603 |       0 |
|  2700 |       0 |
|  9000 |       0 |
|  9001 |       0 |
| 10000 |       0 |
| 60000 |       0 |
+-------+---------+
33 rows in set (0.01 sec)

... which is obviously wrong at least for the row with nr 10000.

mysql> EXPLAIN SELECT f.nr, (SELECT COUNT( distinct aCount.isin ) FROM
anleihen aCount,  offlinekurse oCount WHERE oCount.isin='DE0001135176'
AND  aCount.isin=oCount.isin AND aCount.fonds=f.nr) AS counter FROM
fonds f;
+----+--------------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type        | table  | type  | possible_keys | key     |
key_len | ref   | rows | Extra                    |
+----+--------------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
|  1 | PRIMARY            | f      | index | NULL          | PRIMARY |
2 | NULL  |   33 | Using index              |
|  2 | DEPENDENT SUBQUERY | oCount | ref   | isin          | isin    |
13 | const |    1 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | aCount | ALL   | NULL          | NULL    |
NULL | NULL  |  143 | Using where              |
+----+--------------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
3 rows in set (0.00 sec)

mysql> EXPLAIN SELECT f.nr, (SELECT COUNT( distinct aCount.isin ) FROM
anleihen aCount,  offlinekurse oCount WHERE oCount.isin='DE0001135176'
AND  aCount.isin=oCount.isin AND aCount.fonds=f.nr) AS counter FROM
fonds f HAVING nr=10000;
+----+--------------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type        | table  | type  | possible_keys | key     |
key_len | ref   | rows | Extra                    |
+----+--------------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
|  1 | PRIMARY            | f      | index | NULL          | PRIMARY |
2 | NULL  |   33 | Using index              |
|  2 | DEPENDENT SUBQUERY | oCount | ref   | isin          | isin    |
13 | const |    1 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | aCount | ALL   | NULL          | NULL    |
NULL | NULL  |  142 | Using where              |
+----+--------------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
3 rows in set (0.00 sec)

Thread
bug in mysql with COUNT() in subqueriesH. Steuer15 Nov