Mariadb – Why is it not possible to “SELECT” Data as parameters for certain clauses like LIMIT

mariadbmariadb-10.2

EXAMPLE:

CREATE TABLE t (A INT);
INSERT INTO t VALUES(1),(2),(3),(4),(5);

This works:

SELECT A FROM t WHERE A=(SELECT 1)

This does not:

SELECT A FROM t LIMIT (SELECT 1);

This works:

SELECT A FROM t WHERE A=substring((SELECT 123),1,1);

This does not:

SELECT A FROM t WHERE A=1 PROCEDURE ANALYSE((SELECT 1),10000);

What makes some clauses and functions support SELECT statements and others not support it. I would think that running (SELECT 1) returns a 1 as a parameter and as long as the parameter is the expected type it should work. This seems to be true in some cases, but not others. Why is that?

Best Answer

this is the way LIMIT can be done in MySQL using variable parameters:

SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

In the place where it says SET @skip=1, One can also write SET @skip=(SELECT 1).

Related Question