mysql - Error "result consisted of more than one row" -

mysql - Error "result consisted of more than one row" -

i'm getting error ("result consisted of more 1 row") when trying select table of numbers , append column sum of previous 4 records (i.e. add together values both columns record , columns next 3 records). totals column generated using function. here's simplified illustration - create , populate table first:

create table test.table1 ( num1 float default null, num2 float default null ); insert test.table1 (num1, num2) values (2.5, 3.2), (1.4, 0.9), (1.7, 2.3), (2.6, 1.2); (3.3, 0.8); (2.0, 2.2);

now, create function:

drop function if exists addnum; delimiter $$ create function addnum (num1 float, num2 float) returns float deterministic reads sql info begin declare total, sum1 float; declare maxv int unsigned default 4; declare int unsigned default 0; while < maxv select (num1 + num2) sum1 table1; set total = total + sum1; set = + 1; end while; homecoming (total); end $$

when phone call function within select statement, i'm getting aforementioned error.

select num1, num2, addnum(t.num1, t.num2) 'sum' table1 t;

any ideas how prepare this? or maybe there's easier way accomplish end result. i'm quite new mysql, might approaching wrong angle.

thanks in advance!

the error results

select (num1 + num2) sum1 table1

it tries select many rows table1 one variable sum1. prepare this, can limit result set

select (num1 + num2) sum1 table1 limit 1

furthermore, must initialize variable total

declare total float default 0;

otherwise, value null, see declare

the solution using variable limit or offset seems prepare , execute statement

prepare stmt "select * table1 limit ? offset ?"; execute stmt using lim, off;

see variable limit clause in mysql or using variables offset in select statments within mysql's stored functions more elaborate , detailed solutions.

there's hint bug report/feature request @ sp not take variables in limit clause , prepare in version 5.5.6 , 6.0.14 respectively, http://bugs.mysql.com/bug.php?id=11918#c328636.

mysql sql