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

PROCEDURE using a SELECT from a temporary table does not work well

    XMLWordPrintable

    Details

      Description

      I create a table t1 and a temporary table t1.

      SET sql_mode=DEFAULT;
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT, b VARCHAR(10));
      INSERT INTO t1 VALUES (10,20);
      CREATE TEMPORARY TABLE t1 (x INT);
      INSERT INTO t1 VALUES (10);
      

      Now I create and call a procedure that queries t1:

      DROP PROCEDURE IF EXISTS p1;
      DELIMITER $$
      CREATE PROCEDURE p1()
      BEGIN
        SELECT * FROM t1;
      END;
      $$
      DELIMITER ;
      CALL p1();
      

      It uses the temporary table t1 (rather than the permanent table) and returns this result:

      +------+
      | x    |
      +------+
      |   10 |
      +------+
      

      So far so good.

      Now I drop the temporary table and call the procedure again.

      DROP TEMPORARY TABLE t1;
      CALL p1();
      

      It returns an error:

      ERROR 1054 (42S22): Unknown column 'test.t1.x' in 'field list'
      

      Notice, it still tries to use the column x which belonged to the temporary table and which does not exist in the permanent table. This looks wrong.

      Note, if I now run a stand-alone SELECT query outside of a routine, it works fine:

      SELECT * FROM t1;
      

      +------+------+
      | a    | b    |
      +------+------+
      |   10 | 20   |
      +------+------+
      

      The second CALL is expected to return the same result.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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