Bug #24489 @@sql_select_limit applies to stored functions, triggers, events
Submitted: 21 Nov 2006 22:16 Modified: 5 Dec 2006 16:58
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0 BK, 5.1 BK OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Tags: sql_select_limit, stored procedure, trigger

[21 Nov 2006 22:16] Konstantin Osipov
Description:
MySQL manual says:
http://dev.mysql.com/doc/refman/5.1/en/set-option.html ... 
"SQL_SELECT_LIMIT does not apply to SELECT statements executed 
within stored routines."

This is not the case for stored functions, triggers and events.

How to repeat:
Use the following test case:
drop table if exists t1, t2;
create table t1 (a int);
create table t2 (a int);
insert into t2 (a) values (1), (2), (3), (4), (5);
delimiter ||
create trigger t1_bi before insert on t1 for each row
begin
  declare val int;
  declare count int default 0;
  declare c cursor for select a from t2;
  open c;
  while count < new.a do
    fetch c into val;
    set count= count+1;
  end while;
  close c;
  set new.a=val;
end||
delimiter ;
insert into t1 (a) values (1);
select * from t1;
insert into t1 (a) values (2);
select * from t1;
set @@sql_select_limit=1;
insert into t1 (a) values (3);
select * from t1;

It produces the following output:

kostja@bodhi:~> mysql test -uroot
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.30-valgrind-max-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists t1, t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 (a) values (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> delimiter ||
mysql> create trigger t1_bi before insert on t1 for each row
    -> begin
    ->   declare val int;
    ->   declare count int default 0;
    ->   declare c cursor for select a from t2;
    ->   open c;
    ->   while count < new.a do
    ->     fetch c into val;
    ->     set count= count+1;
    ->   end while;
    ->   close c;
    ->   set new.a=val;
    -> end||
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> insert into t1 (a) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+
| a    |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

mysql> insert into t1 (a) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+
| a    |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.00 sec)

mysql> set @@sql_select_limit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 (a) values (3);
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
mysql> select * from t1;
+------+
| a    |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)
[21 Nov 2006 22:28] Konstantin Osipov
When fixing this bug, we should not only look at @@sql_select_limit, but at all variables that affect behaviour of queries:
 - may cause a runtime execution error
 - may effect in a different result set returned by a query.

We can employ one of the following strategies: 
 - reset all these variables to their default values when starting execution of a stored program. In this case, what happens if a default value changes from version to version of the server?
 - store the variables along with the definition of the stored program and activate when the stored program is being executed.
[22 Nov 2006 9:56] Sveta Smirnova
Thank you for the report.

Verified as described on Linux using last BK sources.
[20 Feb 2008 19:39] Omer Barnir
workaround: use explicit LIMIT clause in SELECT
[21 Mar 2013 1:24] Paul Dubois
Per Bug#65657, the statement about sql_select_limit not applying within stored programs has been removed from the manual.