Hi, this is a follow-up to the previous message.
As far as I've understood when Innodb parses a Having clause it creates a
temporary table where it stores the results of the query satisfying all
the conditions except the HAVING clause, then it makes a
SELECT * from <temp_innodb_table> ... WHERE having_clause
on these temporary results.
However something goes wrong in this second phase, maybe it doesn't
correctly evaluate this last WHERE clause because if it finds records
that don't satisfy the condition it complains and doesn't go
on, instead of simply ignoring those records.
This would explain the following strangeness:
mysql> SELECT DISTINCT (@c:=@c+1), p.*, count(k.keywords) AS found FROM
keywords k, poll_keywords pk, poll p , section_poll sp WHERE 1 AND
k.keywords IN ('secondo', 'terzo') AND k.id_keyword=pk.id_keyword AND
pk.id_poll=p.id_poll AND sp.id_section IN (29, 0, 1, 2, 3, 4, 5, 995, 20,
996, 13, 7, 997, 30, 14, 15, 23, 16, 24, 17, 25, 34, 18, 26, 35, 19, 27,
28) AND sp.id_poll=p.id_poll GROUP BY p.id_poll, id_section having
found=2 ORDER BY p.id_poll LIMIT 0,5;
ERROR 1032: Can't find record in '#sql51e9_442_2c'
VS. (without HAVING)
mysql> SELECT DISTINCT (@c:=@c+1), p.*, count(k.keywords) AS found FROM
keywords k, poll_keywords pk, poll p , section_poll sp WHERE 1 AND
k.keywords IN ('secondo', 'terzo') AND k.id_keyword=pk.id_keyword AND
pk.id_poll=p.id_poll AND sp.id_section IN (29, 0, 1, 2, 3, 4, 5, 995, 20,
996, 13, 7, 997, 30, 14, 15, 23, 16, 24, 17, 25, 34, 18, 26, 35, 19, 27,
28) AND sp.id_poll=p.id_poll GROUP BY p.id_poll, id_section ORDER BY
p.id_poll LIMIT 0,5;
+------------+----------------------+--------+-----------------------------+-----------------------+-----------------------------------------------+----------------+--------+---------------+--------------+--------------+--------------+-------------+------
-----------+----------------+---------+-----------------+----------------+-------------+------+-------------+-----------+---------------+-------------------------------------------+--------------------------------------------------+--------------+--------
----------+-------+-----------+--------------------+-------+ | (@c:=@c+1)
| id_poll | author | title | abstract | question
| authentication | groups | id_created_by |
created_when | stop_showing | begin_voting | stop_voting | id_published_by
| published_when | suspend | id_suspended_by | suspended_when |
answer_type | open | periodicity | vote_type | homepage_flag |
question_img_src | poll_img_src | poll_img_alt | question_img_alt | start
| is_online | update_online_when | found |
+------------+----------------------+--------+-----------------------------+-----------------------+-----------------------------------------------+----------------+--------+---------------+--------------+--------------+--------------+-------------+------
-----------+----------------+---------+-----------------+----------------+-------------+------+-------------+-----------+---------------+-------------------------------------------+--------------------------------------------------+--------------+--------
----------+-------+-----------+--------------------+-------+ | 33 |
poll-994344470-1468 | nico | quarto quinto terzo | abstract secondo poll |
domanda domandona: perché non va l'answerona? | NULL | | nico | 994424179
| 999528420 | 994344420 | 996936420 | operator | 994424179 | 0 | NULL |
NULL | 3 | 0 | 0 | 2 | 0 | /online/images/2001/21-25_05/Image196.jpg |
/online/images/2001/14-18_05/cinemaitaliano2.jpg | NULL
| NULL | 0 | 1 | 994424179 | 1 | | 34 | poll-994424046-16943 | nico |
quarto terzo | abstract secondo poll | domanda domandona: perché non va
l'answerona? | NULL | | nico | 994424179 | 999608040 | 994424040 |
997016040 | operator | 994424179 | 0 | NULL | NULL | 2 | 1 | 0 | 2 | 0 |
/online/images/2001/21-25_05/Image196.jpg |
/online/images/2001/14-18_05/cinemaitaliano2.jpg | NULL
| NULL | 0 | 1 | 994424179 | 1 | | 35 | poll-994424053-16944 | nico |
quarto quinto secondo terzo | abstract secondo poll | domanda domandona:
perché non va l'answerona? | NULL | | nico | 994424179 | 999608040 |
994424040 | 997016040 | operator | 994424179 | 0 | NULL | NULL | 1 | 1 | 0
| 1 | 0 | /online/images/2001/21-25_05/Image196.jpg |
/online/images/2001/14-18_05/cinemaitaliano2.jpg | NULL
| NULL | 0 | 1 | 994424179 | 2 |
+------------+----------------------+--------+-----------------------------+-----------------------+-----------------------------------------------+----------------+--------+---------------+--------------+--------------+--------------+-------------+-----------------+----------------+---------+-----------------+----------------+-------------+------+-------------+-----------+---------------+-------------------------------------------+--------------------------------------------------+--------------+--------
----------+-------+-----------+--------------------+-------+
3 rows in set (0.01 sec)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
