MySQL Result consisted of more than one row on stored procedure

Question

This stored procedure that I'm working on errors out some times. I am getting a Result consisted of more than one row error, but only for certain JOB_ID_INPUT values. I understand what causes this error, and so I have tried to be really careful to make sure that my return values are scalar when they should be. Its tough to see into the stored procedure, so I'm not sure where the error could be generated. Since the error is thrown conditionally, it has me thinking memory could be an issue, or cursor reuse. I don't work with cursors that often so I'm not sure. Thank you to anyone who helps.

DROP PROCEDURE IF EXISTS export_job_candidates;
DELIMITER $$
CREATE PROCEDURE export_job_candidates (IN JOB_ID_INPUT INT(11))
BEGIN

DECLARE candidate_count INT(11) DEFAULT 0;
DECLARE candidate_id INT(11) DEFAULT 0;

# these are the ib variables
DECLARE _overall_score DECIMAL(5, 2) DEFAULT 0.0;

# declare the cursor that will be needed for this SP
DECLARE curs CURSOR FOR SELECT user_id FROM job_application WHERE job_id = JOB_ID_INPUT;

# this table stores all of the data that will be returned from the various tables that will be joined together to build the final export
CREATE TEMPORARY TABLE IF NOT EXISTS candidate_stats_temp_table (
    overall_score_ib DECIMAL(5, 2) DEFAULT 0.0
) engine = memory;

SELECT COUNT(job_application.id) INTO candidate_count FROM job_application WHERE job_id = JOB_ID_INPUT;

OPEN curs;

# loop controlling the insert of data into the temp table that is retuned by this function
insert_loop: LOOP

    # end the loop if there is no more computation that needs to be done
    IF candidate_count = 0 THEN 
        LEAVE insert_loop;
    END IF;

    FETCH curs INTO candidate_id;

    # get the ib data that may exist for this user
    SELECT
        tests.overall_score
    INTO 
        _overall_score
    FROM 
        tests
    WHERE
        user_id = candidate_id;

    #build the insert for the table that is being constructed via this loop 
    INSERT INTO candidate_stats_temp_table (
        overall_score
    ) VALUES (
        _overall_score
    );

    SET candidate_count = candidate_count - 1;

END LOOP;

CLOSE curs;

SELECT * FROM candidate_stats_temp_table WHERE 1;

END $$
DELIMITER ;

Answer 1

The WHERE 1 (as pointed out by @cdonner) definitely doesn't look right, but I'm pretty sure this error is happening because one of your SELECT ... INTO commands is returning more than one row.

This one should be OK because it's an aggregate without a GROUP BY, which always returns one row:

SELECT COUNT(job_application.id) INTO candidate_count
  FROM job_application WHERE job_id = JOB_ID_INPUT;

So it's probably this one:

# get the ib data that may exist for this user
SELECT
    tests.overall_score
INTO 
    _overall_score
FROM 
    tests
WHERE
    user_id = candidate_id;

Try to figure out if it's possible for this query to return more than one row, and if so, how do you work around it. One way might be to MAX the overall score:

SELECT MAX(tests.overall_sore) INTO _overall_score
  FROM tests
  WHERE user_id = candidate_id

Answer 2

I think you want to use

 LIMIT 1

in your select, not

 WHERE 1

Aside from using this safety net, you should understand your data to figure out why you are getting multiple results. Without seeing the data, it is difficult for me to take a guess.