This is a tracking ticket for problems and workarounds related to the following issue:
# MySQL and older versions of MariaDB (and possibly other database systems) do not allow a temporary table to be used more than once in a query. Any attempt to self-join a temporary table, or to use a temporary table in a join as well as a sub-query, or two sub-queries, will result in "Error 1137: Can't reopen table".
# MediaWiki's framework for phpunit tests "shadows" all database tables with temporary tables.
# Thus, all query that use any table more than once will fail in phpunit tests.
There is no real fix as long as we support databases that have this problem. The purpose of this ticket is to have a central place all code related to this issue can link to, so such code can easily be identified and found. In addition, this ticket provides a central place for discussing the issue and best practices around it. It exists so people running into this issue will find it and can benefit from past experience.
Workaround for this include:
* Skip the test under MySQL
* Force the use of real tables instead of temporary tables
* Copy the relevant (temporary) table and use the copy for one of the usages in the query
There is no real fix as long as we support databases that have this problem. The purpose of this ticket is to have a central place all code related to this issue can link to, so such code can easily be identified and found. In addition, this ticket provides a central place for discussing the issue and best practices around it.
See also:
* The original bug report against MySQL from 2005: https://bugs.mysql.com/bug.php?id=10327
* The fix in MariaDB from 2016: https://jira.mariadb.org/browse/MDEV-5535
* Discussion of workarounds in the MySQL manual: https://dev.mysql.com/doc/refman/8.0/en/temporary-table-problems.html
* Discussion on stack overflow: https://stackoverflow.com/questions/343402/getting-around-mysql-cant-reopen-table-error