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

Subquery in Procedure somehow affecting temporary table

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

          What do you mean "without the subselect", how does the stored procedure look in this case?

          serg Sergei Golubchik added a comment - What do you mean "without the subselect", how does the stored procedure look in this case?
          Jeff Scott Jeff Scott added a comment -

          Sorry, I should have included that as well. Here's the other version of it without the subselect:

          CREATE PROCEDURE `util`.`tmp_table_tester` ()
          BEGIN

          drop temporary table if exists tt_table;

          create temporary table tt_table(id int not null);

          select tt.* from tt_table tt;

          END

          Jeff Scott Jeff Scott added a comment - Sorry, I should have included that as well. Here's the other version of it without the subselect: CREATE PROCEDURE `util`.`tmp_table_tester` () BEGIN drop temporary table if exists tt_table; create temporary table tt_table(id int not null); select tt.* from tt_table tt; END
          Jeff Scott Jeff Scott added a comment -

          Just to follow up on my comment from last night, I was able to reproduce this with MariaDB 5.5.27.

          Jeff Scott Jeff Scott added a comment - Just to follow up on my comment from last night, I was able to reproduce this with MariaDB 5.5.27.

          Reproducible as described. MTR-formatted test case:

          --delimiter |
          CREATE PROCEDURE p ()
          BEGIN
          select A.* from ( select tt.* from t1 tt ) A ;
          END |
          --delimiter ;

          drop temporary table if exists t1;
          create temporary table if not exists t1 (id int not null);

          CALL p();
          CALL p();

          1. End of the test case

          It fails on the 2nd CALL p() with
          query 'CALL p()' failed: 1146: Table 'test.t1' doesn't exist

          MariaDB 5.2.14, MySQL 5.5.32, Percona 5.5.29 all return the correct result.

          Remarkably, it also fails with a prepared statement, even on the first execution of one:

          drop temporary table if exists t1;
          create temporary table if not exists t1 (id int not null);

          prepare stmt from "select A.* from ( select tt.* from t1 tt ) A ";
          execute stmt;

          1. end of test

          At line 6: query 'execute stmt' failed: 1146: Table 'test.t1' doesn't exist

          It also works all right on MySQL.

          elenst Elena Stepanova added a comment - Reproducible as described. MTR-formatted test case: --delimiter | CREATE PROCEDURE p () BEGIN select A.* from ( select tt.* from t1 tt ) A ; END | --delimiter ; drop temporary table if exists t1; create temporary table if not exists t1 (id int not null); CALL p(); CALL p(); End of the test case It fails on the 2nd CALL p() with query 'CALL p()' failed: 1146: Table 'test.t1' doesn't exist MariaDB 5.2.14, MySQL 5.5.32, Percona 5.5.29 all return the correct result. Remarkably, it also fails with a prepared statement, even on the first execution of one: drop temporary table if exists t1; create temporary table if not exists t1 (id int not null); prepare stmt from "select A.* from ( select tt.* from t1 tt ) A "; execute stmt; end of test At line 6: query 'execute stmt' failed: 1146: Table 'test.t1' doesn't exist It also works all right on MySQL.

          TABLE_LIST::skip_temporary is set on the PS execution.

          sanja Oleksandr Byelkin added a comment - TABLE_LIST::skip_temporary is set on the PS execution.

          SELECT_LEX::mark_as_belong_to_derived set the flag (Why?)

          sanja Oleksandr Byelkin added a comment - SELECT_LEX::mark_as_belong_to_derived set the flag (Why?)

          The patch committed to review.

          sanja Oleksandr Byelkin added a comment - The patch committed to review.

          Pushed to 5.3

          sanja Oleksandr Byelkin added a comment - Pushed to 5.3

          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.