Q: Behaviour of temp tables in stored procedures I've been working on a platform which allows users to write their own queries and I am thinking of using stored procedures for row level access. This is how it would work; the user calls the query using the stored procedure, and the procedure will check if it has access to the rows, and will return the result if it passes the check. Here's a little simplified version of the part I am having trouble with.
You guys can copy and paste this code to see what I mean.
CREATE TABLE student_tests (
  name CHAR(10), test CHAR(10), 
  score TINYINT, test_date DATE
);

INSERT INTO student_tests 
(name, test, score, test_date) VALUES
('Chun', 'SQL', 75, '2012-11-05'), 
('Chun', 'Tuning', 73, '2013-06-14'),
('Esben', 'SQL', 43, '2014-02-11'), 
('Esben', 'Tuning', 31, '2014-02-09'), 
('Kaolin', 'SQL', 56, '2014-01-01'),
('Kaolin', 'Tuning', 88, '2013-12-29'), 
('Tatiana', 'SQL', 87, '2012-04-28'), 
('Tatiana', 'Tuning', 83, '2013-09-30');

DELIMITER //
CREATE or REPLACE PROCEDURE proc1 (IN qry LONGTEXT) 
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
BEGIN

EXECUTE IMMEDIATE CONCAT("create or replace temporary table tab1 as (",qry,");");

SELECT * from tab1;

END
//
DELIMITER ;

#this works perfectly
call proc1 ("select * from student_tests"); 

#it doesnt work when I try to select columns
## Unknown column 'db.tab1.score' in 'field list'
call proc1 ("select name,test from student_tests");

I believe this has something to do with the line SELECT * from tab1; in the stored procedure. Can someone maybe enlighten me on what is going on? I think I do not understand temporary tables/stored procedures properly.
