Error "result consisted of more than one row"

Question

I'm getting this error ("Result consisted of more than one row") when trying to SELECT from a table of numbers and append a column with the sum of the previous 4 records (i.e. add the values from both columns for that record and also from the columns from the next 3 records). The totals column is generated using a FUNCTION. Here's my simplified example - create and populate the table first:

CREATE TABLE test.table1 (
  num1 float DEFAULT NULL,
  num2 float DEFAULT NULL
  );

INSERT INTO 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 the FUNCTION:

DROP FUNCTION IF EXISTS ADDNUM;
DELIMITER $$
CREATE FUNCTION ADDNUM (num1 float, num2 float) RETURNS float
DETERMINISTIC
READS SQL DATA
BEGIN
  DECLARE total, sum1 float;
  DECLARE maxv int UNSIGNED DEFAULT 4;
  DECLARE i int UNSIGNED DEFAULT 0;

  WHILE i < maxv DO
    SELECT (num1 + num2) INTO sum1 FROM table1;
     SET total = total + sum1;
    SET i = i + 1;
  END WHILE;

  RETURN (total);
END
$$

When I call the function within my SELECT statement, I'm getting the aforementioned error.

SELECT num1, num2, addnum(t.num1, t.num2) AS 'Sum' FROM table1 t;

Any ideas how to fix this? Or maybe there's an easier way to achieve my end result. I'm really quite new to MySQL, so might be approaching this from the wrong angle.

Thanks in advance!

Because you have no `WHERE` clause to limit to one row, and no `LIMIT`. How about adding an explicit `ORDER BY` and `LIMIT,OFFSET`? — Michael Berkowski, Jun 22 '14 at 21:57

score 1 · Answer 1 · answered Jun 27 '14 at 17:13

Ok, I approached the problem in a different way and finally reached the desired outcome. Here's the code if anyone ever gets stuck with this same problem. The way I solved is by using 'dynamic' views instead of loops. I'm not sure whether it's the most elegant way of doing things, but I'm still learning and it worked for me, so hey!

1) First thing is to create the table

CREATE TABLE table2 (
  DateT date DEFAULT NULL,
  HTeam varchar(255) DEFAULT NULL,
  ATeam varchar(255) DEFAULT NULL,
  HScore int(10) UNSIGNED DEFAULT NULL,
  AScore int(10) UNSIGNED DEFAULT NULL
) ENGINE = INNODB;

2) Populate with data

INSERT INTO test.table2(DateT, HTeam, ATeam, HScore, AScore) VALUES
('2014-03-01', 'T1', 'T2', 1, 0);
INSERT INTO test.table2(DateT, HTeam, ATeam, HScore, AScore) VALUES
('2014-03-01', 'T3', 'T4', 2, 1);
INSERT INTO test.table2(DateT, HTeam, ATeam, HScore, AScore) VALUES
('2014-03-08', 'T2', 'T3', 2, 2);
INSERT INTO test.table2(DateT, HTeam, ATeam, HScore, AScore) VALUES
('2014-03-08', 'T4', 'T1', 0, 2);
INSERT INTO test.table2(DateT, HTeam, ATeam, HScore, AScore) VALUES
('2014-03-15', 'T1', 'T3', 3, 1);
INSERT INTO test.table2(DateT, HTeam, ATeam, HScore, AScore) VALUES
('2014-03-15', 'T2', 'T4', 1, 2);
INSERT INTO test.table2(DateT, HTeam, ATeam, HScore, AScore) VALUES
('2014-03-22', 'T4', 'T2', 3, 2);
INSERT INTO test.table2(DateT, HTeam, ATeam, HScore, AScore) VALUES
('2014-03-22', 'T3', 'T1', 0, 0);
INSERT INTO test.table2(DateT, HTeam, ATeam, HScore, AScore) VALUES
('2014-03-29', 'T1', 'T4', 1, 1);
INSERT INTO test.table2(DateT, HTeam, ATeam, HScore, AScore) VALUES
('2014-03-29', 'T3', 'T2', 4, 2);
INSERT INTO test.table2(DateT, HTeam, ATeam, HScore, AScore) VALUES
('2014-04-05', 'T2', 'T1', 0, 2);
INSERT INTO test.table2(DateT, HTeam, ATeam, HScore, AScore) VALUES
('2014-04-05', 'T4', 'T3', 3, 1);

3) Next, I created a few functions that I can later use in my views to make them act in a 'dynamic' way. This is a little trick I came across on this website:

DROP FUNCTION IF EXISTS func_hteam;
CREATE FUNCTION func_hteam() RETURNS VARCHAR(255) DETERMINISTIC
  RETURN @hteam;

DROP FUNCTION IF EXISTS func_ateam;
CREATE FUNCTION func_ateam() RETURNS VARCHAR(255) DETERMINISTIC
  RETURN @ateam;

DROP FUNCTION IF EXISTS func_datet;
CREATE FUNCTION func_datet() RETURNS date DETERMINISTIC
  RETURN @datet;

4) Next, I created a 'dynamic' view:

DROP VIEW IF EXISTS view_total;
CREATE VIEW view_total (DateT, HTeam, ATeam, HScore, AScore, Total)
AS 
(SELECT DateT, HTeam, ATeam, HScore, AScore, (HScore + AScore) AS 'Sum' FROM test.table2
    WHERE (HTeam = func_hteam() OR ATeam = func_hteam()) AND DateT < func_datet()
    ORDER BY DateT DESC LIMIT 4)
UNION DISTINCT
(SELECT DateT, HTeam, ATeam, HScore, AScore, (HScore + AScore) AS 'Sum' FROM test.table2
    WHERE (HTeam = func_ateam() OR ATeam = func_ateam()) AND DateT < func_datet()
    ORDER BY DateT DESC LIMIT 4)
ORDER BY DateT DESC;

5) Next, I created a function that calculates the total sum of all rows that the previous view returns:

DROP FUNCTION IF EXISTS func_total;
DELIMITER $$
CREATE FUNCTION func_total() RETURNS int DETERMINISTIC
BEGIN
  DECLARE num int UNSIGNED DEFAULT 0;
  SELECT SUM(total) INTO num FROM view_total;
RETURN (num);
END
$$

6) Finally, the function that makes all this gel together:

DROP FUNCTION IF EXISTS func_final;
DELIMITER $$
CREATE FUNCTION func_final(hteam VARCHAR(255), ateam VARCHAR(255), datet DATE) RETURNS int DETERMINISTIC
  BEGIN
    DECLARE total INT DEFAULT 0;
    SELECT func_total() INTO total
      FROM (SELECT @hteam:=hteam hteam,
                   @ateam:=ateam ateam,
                   @datet:=datet datet) t1, view_total t
      ORDER BY t.datet ASC LIMIT 1;
RETURN (total);
END
$$

7) ...and now we get the data out:

SELECT DateT, HTeam, ATeam, HScore, AScore, func_final(HTeam, ATeam, Datet) AS TOTAL
  FROM table2 ORDER BY DateT DESC;

All works, could this have been written in a nicer way? Maybe, but it's beyond my abilities at this stage :)

Good day! :P

Don't know if you are aware but the question "could this have been written in a nicer way?" would probably be on topic on [codereview.se]. — Andriy M, Jun 29 '14 at 08:22

score 0 · Accepted Answer · edited May 23 '17 at 12:20

0

The error results from

SELECT (num1 + num2) INTO sum1 FROM table1

It tries to select many rows from table1 into one variable sum1. To fix this, you can limit the result set with

SELECT (num1 + num2) INTO sum1 FROM table1 limit 1

Furthermore, you must initialize your variable total

declare total float default 0;

Otherwise, its value will be NULL, see DECLARE

The solution to using a variable limit or offset seems to prepare and execute a statement

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

See also Variable LIMIT Clause in MySQL or Using variables as OFFSET in SELECT statments inside mysql's stored functions for more elaborate and detailed solutions.

There's also a hint to a bug report/feature request at SP does not accept variables in LIMIT clause and a fix in version 5.5.6 and 6.0.14 respectively, http://bugs.mysql.com/bug.php?id=11918#c328636.

edited May 23 '17 at 12:20

Community

1
1

answered Jun 22 '14 at 22:06

Olaf Dietsche

72,253
8
102
198

Thanks Olaf/Michael... Managed to get the sum from each row stored in the variable using LIMIT and OFFSET (code below), however, I still struggle to get the full total of the top 4 rows using the WHILE loop. For example, the top (2.5, 3.2), total of top 4 rows = 15.8 (1.4, 0.9), total of top 4 rows = 14.2 (1.7, 2.3), total of top 4 rows = 16.1 (2.6, 1.2); (3.3, 0.8); (2.0, 2.2); – Roman Jun 24 '14 at 22:07
`DROP FUNCTION IF EXISTS ADDNUM; DELIMITER $$ CREATE FUNCTION ADDNUM (num1 float, num2 float) RETURNS float DETERMINISTIC READS SQL DATA BEGIN DECLARE total, sum1 float; DECLARE maxv int UNSIGNED DEFAULT 4; DECLARE i int UNSIGNED DEFAULT 0; WHILE i < maxv DO SELECT (num1 + num2) INTO sum1 FROM table1 LIMIT i, 1; SET total = total + sum1; SET i = i + 1; END WHILE; RETURN (total); END $$ SELECT num1, num2, addnum(num1, num2) FROM table1` – Roman Jun 24 '14 at 22:07
Problem seems to be with this assignment statement **SET total = total + sum1;** – Roman Jun 24 '14 at 22:09
You haven't initialized `total`, see updated answer. – Olaf Dietsche Jun 24 '14 at 23:07
Thanks Olaf, that helped. The problem I'm facing now is that I cannot use a variable to set the OFFSET. I need the function to loop through the top 4 records, so I need the offset to be dynamic (i.e. incremented by 1 at each run of the loop). I used the statement **SELECT (num1 + num2) INTO sum1 FROM table1 LIMIT 1 OFFSET i;** where i would iterate from 0 to 3. The idea is to get the sum of each row of the top 4 rows and then add them all together. Any ideas? I really appreciate your help thus far :) – Roman Jun 25 '14 at 19:26
I never did this myself, but it seems doable. Please see updated answer and the quoted questions. – Olaf Dietsche Jun 25 '14 at 21:35
Getting error: "Dynamic SQL is not allowed in stored function or trigger". Looked it up and found: "SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use Dynamic SQL (where you construct statements as strings and then execute them)", Will have to find a different way of achieving my result I guess :/ – Roman Jun 26 '14 at 08:58
What mysql version do you use? – Olaf Dietsche Jun 26 '14 at 09:35
Found a way to have this work without using loops, which seems to be very restricted in MySQL, thanks for your help, Olaf – Roman Jun 27 '14 at 17:15

Error "result consisted of more than one row"

2 Answers2