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

Subquery in Procedure somehow affecting temporary table

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.4, 5.5.32, 5.3.12
    • 10.0.5, 5.5.33a, 5.3.13
    • None
    • None
    • 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

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.