how to cast input in “SELECT * FROM PRODUCT LIMIT CAST(select CAST(‘1’ AS UNSIGNED)), 5”?

select * from product limit (select CAST('1' AS UNSIGNED)) ,5;

this will give error for syntax

also tried create view to replace the (select CAST(‘1’ AS UNSIGNED)), which still doesnt work

I am using mysql.

Answer

LIMIT clause cannot take expressions or variables. Therefore if you absolutely need such functionality, you must do a huge workaround – you have to simulate it. Here is one variant:

SELECT t1.*
FROM(
   SELECT product.*, @rownum:=@rownum+1 AS rownum
   FROM product
   JOIN (SELECT @rownum:=0) AS t
) AS t1
JOIN (SELECT @limit:=CAST('1' AS UNSIGNED) AS thelimit) AS t2
JOIN (SELECT @offset:=2 AS theoffset) AS t3
WHERE t1.rownum>t3.theoffset AND t1.rownum <= t2.thelimit+t3.theoffset;

A detailed explanation is below:

1. The table t1 is generated by the following query:

   SELECT product.*, @rownum:=@rownum+1 AS rownum
   FROM product
   JOIN (SELECT @rownum:=0) AS t
   

   In this query we practically select everything from the product table and attach a row number counter to every fetched row from it (using the @rownum variable which increments with 1 for every row).

2. The t2 table

   SELECT @limit:=CAST('1' AS UNSIGNED) AS thelimit
   

   is just your limit clause stored as variable.

3. The t3 table

   SELECT @offset:=5 AS theoffset
   

   is your offset

4. We then join those three tables and we apply condition

   WHERE t1.rownum>t3.theoffset AND t1.rownum <= t2.thelimit+t3.theoffset;
   

   by which we are simulating a regular LIMIT clause.

Furthermore – if you wish, you can use just @limit and @offset instead of t2.thelimit and t3.theoffset – it will work fine and it will be probably more readable for some people. The final query can simplify it’s look a bit like this:

SELECT t1.*
FROM(
   SELECT product.*, @rownum:=@rownum+1 AS rownum
   FROM product
   JOIN (SELECT @rownum:=0) AS t
) AS t1
JOIN (SELECT @limit:=CAST('1' AS UNSIGNED)) AS t2
JOIN (SELECT @offset:=2) AS t3
WHERE rownum>@offset AND rownum <= @limit+@offset;

or you can even skip the two JOINs if you declare the variables before executing the query (this may be a bit faster):

SET @limit:=CAST('1' AS UNSIGNED);
SET @offset:=2;
SELECT t1.*
FROM(
   SELECT product.*, @rownum:=@rownum+1 AS rownum
   FROM product
   JOIN (SELECT @rownum:=0) AS t
) AS t1
WHERE rownum>@offset AND rownum <= @limit+@offset;