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) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
