Hi!
I will send you a special version of row0sel.c
with which you can print a lot of trace info on
SELECT execution. Those traces may reveal what is wrong.
Regards,
Heikki
>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
