Bug #78523 can't see temporary table in statement with a subquery and a function

• Submitted: 22 Sep 2015 22:54
• Modified: 23 Sep 2015 9:29
• Reporter: Benoit Duffez
• Status: Verified
• Category: MySQL Server: Prepared statements
• Severity: S3 (Non-critical)
• Version: 5.7.8-rc-log community version, 5.6.26, 5.6.28, 5.7.10
• OS: Debian (8.1)
• CPU Architecture: Any

[22 Sep 2015 22:54] Benoit Duffez

Description:
I have a complicated procedure that runs a bunch of queries, which results are stored in temporary tables. The queries are chained and use the previous temporary tables to create more advanced results.

One of the queries uses a subquery and a custom function. It fails, complaining about a table that would not exist, however it is created just before as a temporary table.

The temporary table was indeed created but it seems it cannot be seen from a subquery created inside a statement.

How to repeat:
Shell code and reproduction log available at https://gist.github.com/BenoitDuffez/8dc23ed48e995d3e3254

Simplified version here:

drop database if exists test;
create database test;
delimiter $$
create function func() returns int(10)
begin
  return 1;
end$$

create procedure testbug()
begin
    drop table if exists src;
    drop table if exists result;
    
    create temporary table src (id int);
    
    set @sql = "create temporary table result
    select * from (select * from src where id < func()) a";
    PREPARE s FROM @sql;
    EXECUTE s;
    deallocate prepare s;
END$$

Test case: just run: call testbug();

Expected result: nothing happens, but the table `result` exists.
Actual result: ERROR 1146 (42S02) at line 1: Table 'test.src' doesn't exist

The same behavior was observed on 5.6.26-log

[23 Sep 2015 9:29] MySQL Verification Team

Hello Benoit Duffez,

Thank you for the report and test case.
Observed that 5.6(checked 5.6.26/5.6.28) and 5.7(5.7.10) builds are affected.

Thanks,
Umesh