I am trying to create a stored procedure that looks similar to:
delimiter //
CREATE OR REPLACE PROCEDURE get_results (in_page_num INT UNSIGNED, in_page_size INT UNSIGNED)
AS
DECLARE
_offset INT UNSIGNED = in_page_size * (in_page_num+1);
BEGIN
ECHO SELECT *
FROM RESULTS
ORDER BY ID
LIMIT in_page_size OFFSET _offset;
END //
delimiter ;
Thanks for bringing this to our notice. We are going to apply fix for this in 7.0 and also backport that fix to 6.8 and it should be available in the next 6.8 release (most likely 6.8.13).
delimiter //
CREATE OR REPLACE PROCEDURE get_results (in_page_num INT UNSIGNED, in_page_size INT UNSIGNED)
AS
DECLARE
_offset INT UNSIGNED = in_page_size * (in_page_num+1);
q QUERY(id int) = TO_QUERY(CONCAT("SELECT id from RESULTS ORDER BY ID LIMIT " , in_page_size, " OFFSET ", _offset));
BEGIN
ECHO SELECT * FROM q;
END //
delimiter ;
This should help you bypass the issue for now although there are two cons of this approach: 1) q QUERY(...) leads to all columns getting fixed when you write the Stored Procedure. So if you alter the table (add/delete column), you will have to drop the stored procedure and construct it again (Since you are doing SELECT *) 2) TO_QUERY() will have a very slight runtime penalty as it does string concatenation at runtime.
They have different semantics. TO_QUERY is used to construct a query type variable which can then be passed as a parameter to other stored procedures (or can be used intermediately to make code cleaner) whereas EXECUTE IMMEDIATE just executes the query
Performance wise, they should be similar (with TO_QUERY() probably a bit slower) but TO_QUERY output will be query type variable which might make it a bit trickier to debug in non-trivial code (As we don’t have an inbuilt function to convert a query type variable to string)