Can't see temp table from subquery

I have a query that selects data from tables and one temp table. This works fine, but there is a GROUP BY/ORDER BY combination that doesn't work well for me. Indeed, I need to order the results and only then group by.

So I enclosed the query (without the group by) into a subquery, which is then grouped by the column. This works well in development, however when executed from a procedure, I'm getting the following error: Base table or view not found: 1146 Table 'bicou_gtfs_rennes.trip1' doesn't exist.

The trip1 table is temporary, all other tables are standard. I'm using InnoDB.

Here's the query that generates this error:

In this context, t is a string containing a table name, and r2 and rt are strings representing a comma separated list of integers (such as 1,34,2,54).

set @sql = concat("create temporary table t2d as
select * from (
     select
         t1.*
         ,s2d.id as s2d_iid
         ,s2d.stop_name as s2d_name, s2d.stop_lat as s2d_lat, s2d.stop_lon as s2d_lon
         ,st2d.idep as st2d_idep
         ,t2.id as t2_iid, t2.trip_headsign as t2_headsign
         ,t2.trip_type as t2_type
         ,r2.route_short_name as r2_rt, r2.route_long_name as r2_route, r2.route_type as r2_type, r2.route_text_color as r2_text_color, r2.route_color as r2_color

     from trip1 t1
     left join stops s2d on s2d.id = t1.cs1_to
     left join ", st, " st2d on st2d.stop_iid = s2d.id
         and st2d.idep > t1.t1_arr
         and st2d.idep < bicou_gtfs.GtfsTimeAdd(t1.t1_arr, 45*60)
     inner join ", t, " t2 on t2.id = st2d.trip_iid
         and t2.route_iid in (", r2, ")
     inner join routes r2 on r2.id = t2.route_iid and r2.route_type in (", rt, ")

     order by st2d_idep asc
) a group by a.t2_type");

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Below is a very similar query that works, but which doesn't get the right order when grouping:

set @sql = concat("create temporary table t2d as
select
     t1.*
     ,s2d.id as s2d_iid
     ,s2d.stop_name as s2d_name, s2d.stop_lat as s2d_lat, s2d.stop_lon as s2d_lon
    ,st2d.idep as st2d_idep
    ,t2.id as t2_iid, t2.trip_headsign as t2_headsign
    ,t2.trip_type as t2_type
    ,r2.route_short_name as r2_rt, r2.route_long_name as r2_route, r2.route_type as r2_type, r2.route_text_color as r2_text_color, r2.route_color as r2_color

from trip1 t1
left join stops s2d on s2d.id = t1.cs1_to
left join ", st, " st2d on st2d.stop_iid = s2d.id
    and st2d.idep > t1.t1_arr 
    and st2d.idep < bicou_gtfs.GtfsTimeAdd(t1.t1_arr, 45*60)
inner join ", t, " t2 on t2.id = st2d.trip_iid
    and t2.route_iid in (", r2, ")
inner join routes r2 on r2.id = t2.route_iid and r2.route_type in (", rt, ")

group by t1_type
order by st2d_idep asc");

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The differences is subtle, the whole query is enclosed into a SELECT * FROM (...) a GROUP BY a.t1_type; and the GROUP BY t1_type from the inner query is of course removed.

Is it a normal limitation? My main goal is to order first, then group. If it's possible without enclosing the query in a SELECT statement, I'm in.

Here's my setup:

• Debian GNU/Linux 3.2 x86_64


• MySQL 5.6.23


• InnoDB tables