Offset does not allow a local variable

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 ;

It is throwing a run-time error:

ERROR 1054 ER_BAD_FIELD_ERROR: Unhandled exception Type: ER_BAD_FIELD_ERROR Message: Unknown column '_offset' in 'offset clause'

Sample call:
CALL get_results (0, 10);

Are local variables not allowed in the OFFSET field?

This was tested in MemSQL Versions 6.8.7 & 6.8.9.

Hi geet,

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).

For now, to mitigate this issue, you can use TO_QUERY() built-in (SingleStoreDB Cloud · SingleStore Documentation) to construct a similar query
E.g.

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.

Thank you for the reply.

@Saksham,
Also is TO_QUERY better than Dynamic SQL with EXECUTE IMMEDIATE ? Which would you recommend?

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)

2 Likes