[MySQL] "drop temporary table" is an invalid syntax for YB

[shubham-yb]

If we have a temporary table defined in a function or stored procedure in MySQL, and we have a drop temporary table statement associated with it, it exports as is, which is an invalid syntax for YB. It only supports drop table statements.

Example source schema:

delimiter //

create procedure foo(p_id int)
deterministic
begin
    drop temporary table if exists temp;

    create temporary table temp(id int, name text);

    insert into temp(id,name) select id,p_name from bar where p_id=id;

    select name from temp;

end//
delimiter ;

Exported schema:

CREATE OR REPLACE PROCEDURE foo (p_id integer) AS $body$
BEGIN
    drop temporary table if exists temp;

    create temporary table temp(id int, name text);

    insert into temp(id,name) select id,p_name from bar where p_id=id;

    select name from temp;

end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

Workaround:

Remove "temporary" from the drop statement.