improvement: eval() could use VIEW instead of temporary table

[GoogleCodeExporter]

suggestion: eval could generate a VIEW instead of a temporary table. This would 
have the advantage of not requiring any storage, and it could be faster too 
since the resultset would need to be materialized only once, namely when the 
cursor is opened (now, the set is first materialized from the argument query, 
then stored, and then that set is opened again to loop through the cursor).

Code would need to be modified slightly because MySQL does not have temporary 
views:

CREATE PROCEDURE eval(sql_query TEXT CHARSET utf8) 
MODIFIES SQL DATA
SQL SECURITY INVOKER
COMMENT 'Evaluates queries resulting from given query'
BEGIN
  SET @q := CONCAT('CREATE OR REPLACE VIEW _tmp_eval_queries', connection_id(), ' AS ', sql_query);  
  PREPARE st FROM @q;
  EXECUTE st;
  DEALLOCATE PREPARE st;

  BEGIN 
    DECLARE current_query TEXT CHARSET utf8 DEFAULT NULL;
    DECLARE done INT DEFAULT 0;
    DECLARE eval_cursor CURSOR FOR SELECT query FROM _tmp_eval_queries;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN eval_cursor;
    read_loop: LOOP
      FETCH eval_cursor INTO current_query;
      IF done THEN
        LEAVE read_loop;
      END IF;
      SET @execute_query := current_query;
      IF @common_schema_verbose THEN
          SELECT @execute_query AS now_executing FROM DUAL;
      END IF;
      PREPARE st FROM @execute_query;
      EXECUTE st;
      DEALLOCATE PREPARE st;
    END LOOP;

    CLOSE eval_cursor;
  END;

  SET @q := CONCAT('DROP VIEW _tmp_eval_queries', connection_id());  
  PREPARE st FROM @q;
  EXECUTE st;
  DEALLOCATE PREPARE st;
END $$

Original issue reported on code.google.com by roland.bouman on 6 Sep 2011 at 9:54

[GoogleCodeExporter]

The temporary tables provides with two solutions that the view does not:
1. I expect use of eval() to be long time consuming (e.g. ALTER, DROP, etc.) 
sometimes it's just massive KILL, but that, two, can sometimes take time (try 
killing a slave thread that is busy).
So I can expect the query to crash sometime, perhaps due to human intervention 
(Ctrl+C).
In which case I cannot count on the final DROP statement to execute. Which is 
why temporary tables play so nicely: eventually they will get cleaned up. VIEWs 
will not.

2. You are assuming the the evaluated query returns a column called "query". I 
do not. It can return a column of any name, I don't care, not do I want to 
force its name.
I agree that I'm already forcing the type of query: to return one textual 
column which is a query. But I like it that I don't have to force more than 
that.

Original comment by shlomi.n...@gmail.com on 6 Sep 2011 at 10:30

[GoogleCodeExporter]

PS, the trick with CREATE OR REPLACE and CONNECTION_ID() is very cool; but it 
can still make for tens of thousands of 'loose' views, in theory.

Original comment by shlomi.n...@gmail.com on 6 Sep 2011 at 10:31

[GoogleCodeExporter]

Good points. Well set it to "Won't fix" I guess?

Original comment by roland.bouman on 6 Sep 2011 at 12:12

[GoogleCodeExporter]

Afraid so :)

Original comment by shlomi.n...@gmail.com on 6 Sep 2011 at 12:18

• Changed state: WontFix