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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.