Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5005

Subquery in Procedure somehow affecting temporary table

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.4, 5.5.32, 5.3.12
    • Fix Version/s: 10.0.5, 5.5.33a, 5.3.13
    • Component/s: None
    • Labels:
      None
    • Environment:
      CentOS 6.4

      Description

      After calling a SP which uses a temporary table in a sub query, I am unable to call the same stored procedure a second (or any time after) time. It appears that the query engine thinks the temporary table is in fact a real table and it reports an error when it is unable to find it.

      Using this stored procedure:

      CREATE PROCEDURE `util`.`tmp_table_tester_sub` ()
      BEGIN
      drop temporary table if exists tt_table;
      create temporary table if not exists tt_table(id int not null);
      select A.*
      from
      (
      	select tt.*
      	from tt_table tt
      )A
      ;
      END

      I can call it once and get a valid result set. On the second (and all subsequent) calls I get this error: "Error Code: 1146. Table 'util.tt_table' doesn't exist" - note that it's trying to find the temporary table in the schema of the procedure.

      Without the subselect I can run the SP many times without problems.

      Resetting the connection (by reconnecting in workbench) allows one execution to complete successfully, then the same exception.

      I can verify that this stored procedure works in Percona Server 5.5.27-28.1. I'm downloading MariaDB 5.5.27 for comparison, but it's going so slowly that I'll have to wait until tomorrow. In either event, I don't see a reason that a subquery should impact the rest of the session.

      Please let me know if any other information would be useful,

      Jeff

        Attachments

          Activity

            People

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              Jeff Scott Jeff Scott
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Due:
                Created:
                Updated:
                Resolved: