ExpressionEngine query module and ordering SQL UNION

Question

I'm receiving a MySQL Error on this code in ExpressionEngine 1.6.4 (very old version)

{exp:query  limit="10" paginate="bottom"
    sql="SELECT 'gallery' AS `content_type`, `cat_id` AS `entry_id`, `recent_entry_date` AS `entry_date`
        FROM `exp_gallery_categories`
        WHERE `gallery_id` = 9 AND total_files > 0
        UNION
        SELECT 'video' AS `content_type`, `entry_id`, `entry_date`
        FROM `exp_weblog_titles`
        WHERE `weblog_id` = 6 ORDER BY `entry_date` DESC"

    }
<p>{content_type} - {entry_id} - {entry_date format="%d %F %Y"}</p>
{paginate}{pagination_links}{/paginate}
{/exp:query}

MySQL returns this:

MySQL ERROR: Error Number: 1054
Description: Unknown column 'entry_date' in 'order clause'
Query: SELECT COUNT(*) AS count FROM `exp_gallery_categories` WHERE `gallery_id` = 9 AND total_files > 0 UNION SELECT COUNT(*) AS count FROM `exp_weblog_titles` WHERE `weblog_id` = 6 ORDER BY `entry_date` DESC

It seems like EE is modifying my query before MySQL and making it fail (On a SQL client this works smoothly).

Answer 1

The answer is to patch mod.query.php to stop modifing the query

$query = $DB->query("SELECT COUNT(*) AS count FROM ({$sql}) AS query");

Here is the ultimate in-depth answer by Dom Stubbs: ExpressionEngine 1 Query Module and Ordering SQL UNION