0

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" -- difference
"    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 ", -- difference
"order by st2d_idep asc"); -- difference

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

The differences are subtle (I marked the lines), 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

Here's the routine info of the procedure:

SELECT * FROM information_schema.ROUTINES where routine_schema='bicou_gtfs_rennes' and specific_name = 'Get1CJourneys' \G 
*************************** 1. row ***************************
           SPECIFIC_NAME: Get1CJourneys
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: bicou_gtfs_rennes
            ROUTINE_NAME: Get1CJourneys
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: begin
*content removed for clarity*
end
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2015-03-15 22:58:20
            LAST_ALTERED: 2015-03-15 22:58:20
                SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: bicou_gtfs@%
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.01 sec)

Edit March 16th 23:09pm GMT+1

OK I could narrow it down to this:

CREATE PROCEDURE `testbug`()
begin
    drop table if exists t1d;
    drop table if exists t1a;
    drop table if exists trip1;
    drop table if exists t2d;

    create temporary table t1d as
    select t1.id as t1_iid, t1.trip_type as t1_type
    from stops s1d
    left join stop_times_12 st1d on st1d.stop_iid = s1d.id
    inner join trips_part t1 on t1.id = st1d.trip_iid
    limit 10;

    create temporary table t1a as
    select t1.*, st1a.idep as st1a_idep, s1a.id as s1a_iid
    from t1d t1
    left join stop_times_12 st1a on st1a.trip_iid = t1.t1_iid
    left join stops s1a on s1a.id = st1a.stop_iid;

    create temporary table trip1
    select t1.*, cs1.to_stop_iid as cs1_to, GtfsTimeAdd(st1a_idep, cs1.distance) as t1_arr
    from t1a t1
    inner join stop_connections cs1 on cs1.from_stop_iid = t1.s1a_iid
    order by t1_arr asc;

    set @sql = "create temporary table t2d as
    select * from (
        select t1.*
        from trip1 t1
        left join stop_times_12 st2d on st2d.idep < GtfsTimeAdd(t1.t1_arr, 45*60)
    ) a ";

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

More code available here.

The last step fails in this version, but:

  • succeeds if I remove the set @sql ... prepare ... execute and just execute the query directly (as the others)
  • succeeds if I change the st2d.idep < GtfsTimeAdd(t1.t1_arr, 45*60) to st2d.idep < 1000 (random dumb value)
  • succeeds if I change the select * from (...) a to just the inner ... query

I'm confused!

EDIT 2015/09/23

I was able to reproduce it with very little code. I have uploaded the code as a GitHub gist.

I have submitted a MySQL bug.

  • SECURITY INVOKER? Or SECURITY DEFINER? – Rick James Mar 15 '15 at 23:54
  • Thanks Rick; however I don't know about these. How can I check? My stored procedure doesn't specify anything so it's the server defaults. – Benoit Duffez Mar 15 '15 at 23:57
  • SELECT * FROM information_schema.ROUTINES -- or add a WHERE clause to limit to the TABLE_SCHEMA or whatever. – Rick James Mar 16 '15 at 0:09
  • Please, have a look at the last edit. Is that what you requested? – Benoit Duffez Mar 16 '15 at 0:16
  • DEFINER -- does bicou_gtfs@% have access to the table in question? Is trip1 a table? or a view? We may need to see SHOW CREATE ... for it. Or maybe the answer is simply that a CREATE TEMPORARY TABLE cannot be seen by a PROCEDURE that is not SECURITY = INVOKER – Rick James Mar 16 '15 at 1:02
1

I'm going to guess at the answer...

ReCREATE the Stored Procedure with SECURITY INVOKER to see if that will let it see the TEMPORARY table trip1.

(This assumes that you were not doing SECURITY DEFINER in order to gain some permissions that the INVOKER does not otherwise have.)

Based on the discussion of access to Temporary Tables on http://dev.mysql.com/doc/refman/5.6/en/privileges-provided.html , I would expect it to be ok (> version 5.6.3).

  • I have added SQL SECURITY INVOKER before the begin statement, the result is the same. – Benoit Duffez Mar 16 '15 at 1:09
  • Nice catch for 5.6.3. However I can't seem to find any information about how to install it, it doesn't look like it's released. I can't be the first to try this query... – Benoit Duffez Mar 16 '15 at 21:16
  • Suggest you write a trivial test case: simple tmp table and simple proc that tries to access the tmp table. If it fails for that, write a bug a bugs.mysql.com . It may turn out that there is some design reason for disallowing it; if so, they will say so. – Rick James Mar 16 '15 at 21:21
  • I was exactly doing that :) I didn't have much time to investigate this since yesterday. Thanks for your kind help Rick. – Benoit Duffez Mar 16 '15 at 21:26
  • OK I tried to narrow it down to the minimum in my last edit. – Benoit Duffez Mar 16 '15 at 22:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.