I am using a limit 1 subquery inside a while loop to run a procedure against all records in a table. I'm targeting MySQL 5.7, which I do not have the ability to change.
When calling MyMultiRecordProc, MySQL is telling me the subquery returns more than 1 row, which I believe is functionally impossible. I've verified that the procedure doesn't error when I exclude the subquery from the while loop.
I've tried the OFFSET version of the limit syntax as well and the issue occurs. Documentation says that this should be limiting the result set to 1. When I spot check the just the subquery against random serial numbers, I receive the expected 1 row.
Am I missing something? Is limit actually pulling multiple records but filtering at the last second and that's breaking the procedure or something? I don't understand why I'm getting this specific error.
My query below. For testing, I've limited it to 10 records. I am invoking it like so: call MyMultiRecordProc('foo');
create procedure MyMultiRecordProc(in fooIn varchar(12))
begin
declare n int default 0;
declare i int default 0;
declare bar int default 0;
if (fooIn is null or trim(fooIn) = '') then
begin signal sqlstate '45005' set message_text = 'errors!'; end;
end if;
set n = 10;
set i = 0;
while i < n do
set bar = (select s.SerialNumber from serialitems s order by s.SerialItemId asc limit i, 1);
call MySingleRecordProc(bar, fooIn);
set i = i + 1;
end while;
end;
I created a test table with 12 rows of data and tried your procedure except the nested procedure MySingleRecordProc since you did not provide the code. It runs fine. I suppose it is your MySingleRecordProc procedure at fault.
create table serialitems(SerialItemId serial , SerialNumber char(8));
insert serialitems (SerialNumber)values
(12113345),
(12342513),
(12213561),
(11267832),
(35785367),
(35519527),
(25125331),
(35138512),
(35987632),
(38561252),
(66885544),
(88665544);
select * from serialitems;
-- result set
+--------------+--------------+
| SerialItemId | SerialNumber |
+--------------+--------------+
| 1 | 12113345 |
| 2 | 12342513 |
| 3 | 12213561 |
| 4 | 11267832 |
| 5 | 35785367 |
| 6 | 35519527 |
| 7 | 25125331 |
| 8 | 35138512 |
| 9 | 35987632 |
| 10 | 38561252 |
| 11 | 66885544 |
| 12 | 88665544 |
+--------------+--------------+
Now let's try your procedure. Note I have to comment your calling of the nested procedure MySingleRecordProc as you did not share it with us. Instead, I display the bar value.
delimiter //
drop procedure if exists MyMultiRecordProc//
create procedure MyMultiRecordProc(in fooIn varchar(12))
begin
declare n int default 0;
declare i int default 0;
declare bar int default 0;
if (fooIn is null or trim(fooIn) = '') then
begin signal sqlstate '45005' set message_text = 'errors!'; end;
end if;
set n = 10;
set i = 0;
while i < n do
set bar = (select s.SerialNumber from serialitems s order by s.SerialItemId asc limit i, 1);
select bar;
-- call MySingleRecordProc(bar, fooIn);
set i = i + 1;
end while;
end//
delimiter ;
call MyMultiRecordProc('ab');
+----------+
| bar |
+----------+
| 12113345 |
+----------+
1 row in set (0.01 sec)
+----------+
| bar |
+----------+
| 12342513 |
+----------+
1 row in set (0.02 sec)
+----------+
| bar |
+----------+
| 12213561 |
+----------+
1 row in set (0.04 sec)
+----------+
| bar |
+----------+
| 11267832 |
+----------+
1 row in set (0.05 sec)
+----------+
| bar |
+----------+
| 35785367 |
+----------+
1 row in set (0.07 sec)
+----------+
| bar |
+----------+
| 35519527 |
+----------+
1 row in set (0.08 sec)
+----------+
| bar |
+----------+
| 25125331 |
+----------+
1 row in set (0.09 sec)
+----------+
| bar |
+----------+
| 35138512 |
+----------+
1 row in set (0.11 sec)
+----------+
| bar |
+----------+
| 35987632 |
+----------+
1 row in set (0.12 sec)
+----------+
| bar |
+----------+
| 38561252 |
+----------+
1 row in set (0.14 sec)
As shown above, each bar value is correctly set. Please verify your MySingleRecordProc procedure or provide its code so we can further offer you help.