When setting up a query using a temporary lookup table, I got this error:
ERROR 1137 (HY000): Can't reopen table: 'tmp_journals'
It transpires that since 4.1 the way MySQL handles temporary tables has changed. This affects joins, unions and subqueries. There is an obvious fix:
mysql> CREATE TEMPORARY TABLE tmp_journals_2 LIKE tmp_journals; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO tmp_journals_2 SELECT * FROM tmp_journals; Query OK, 3228659 rows affected (2.01 sec) Records: 3228659 Duplicates: 0 Warnings: 0
Then the query is easy:
SELECT COUNT(1) cnt, journal_invoice_ref FROM tmp_journals GROUP BY journal_date HAVING cnt > 10000</pre> UNION SELECT COUNT(1) cnt, journal_invoice_ref FROM tmp_journals_2 GROUP BY journal_invoice_ref HAVING cnt < 10