CODEWITHSUNDEEP
mysqlstored-procedures
bfavaretto
bfavaretto

Reputation: 71918

MySQL Error 1172 - Result consisted of more than one row

I'm getting this error from MySQL when running a query inside a stored procedure:

Error Code: 1172 Result consisted of more than one row

I understand the error: I'm doing a SELECT (...) INTO (var list), and thus the query is required to return a single row. When I use LIMIT 1 or SELECT DISTINCT, the error goes away.

However: when I run the original query manually (without LIMIT or DISTINCT), it does return a single row. So I'm suspecting I may have bumped into a MySQL bug. Does anyone know what could be happening?

EDIT

I'm posting the SQL as requested. Everything that starts with an underscore is a variable declared earlier inside the procedure. When I test it, I'm replacing _cd_pai_vc with the ID for the record that is causing the problem.

SELECT  a.valor,    IFNULL(p.valor, 0), fn_cd2alias(ra.cd_registro),    fn_cd2alias(IFNULL(p.valor,0))
INTO    _valor,     _cd_pai_vc,         _alias_verbete,                 _alias_pai
FROM dados_registros ra
    INNER JOIN dados_varchar255 a
    ON a.cd_registro = ra.cd_registro
        AND a.fl_excluido = 0
        AND a.alias = 'vc-verbetes-termo'
    LEFT OUTER JOIN dados_registros rp
        INNER JOIN dados_int p
        ON p.cd_registro = rp.cd_registro
            AND p.fl_excluido = 0
            AND p.alias = 'vc-remissoes-termo referenciado'
        INNER JOIN dados_int pt
        ON pt.cd_registro = rp.cd_registro
            AND pt.fl_excluido = 0
            AND pt.alias = 'vc-remissoes-tipo remissao'
            AND fn_cd2alias(pt.valor) = 'hierarquica'
    ON ra.cd_registro = rp.cd_entidade
        AND rp.fl_excluido = 0
        AND fn_cd2alias(rp.cd_modulo) = 'vc-remissoes'
WHERE ra.cd_registro = _cd_pai_vc 
    AND ra.fl_excluido = 0;

Upvotes: 9

Views: 32623

Answers (5)

rohithd
rohithd

Reputation: 147

The issue i had was IN parameter and the column name both were same so altered IN parameter name and it worked

Upvotes: 2

Ashish Surve
Ashish Surve

Reputation: 3

I had the same problem, the solution by Nava Bogatee worked like a charm, below is a part of the procedure

select attendance
into brb
from Stud
where Rno like rno;

This didn't work I have no idea why. Then I changed this to 

select t.attendance
into brb
from Stud t
where t.Rno like rno;

and it worked. I guess due to some reason the Rno column is returning all the values in the table, that is why the multiple row error.

Upvotes: 0

Nava Bogatee
Nava Bogatee

Reputation: 1765

I had the similiar issue and when I put table alias it worked like a charm.

SELECT t.tax_amount,t.tax_percentage FROM nepse_tax t

Upvotes: 5

David
David

Reputation: 51

I had this problem and found it went away when I used both table name and column name in select statements, even simple ones.

Upvotes: 4

Ataboy Josef
Ataboy Josef

Reputation: 2101

I have experienced the same error in mysql.

MySQL Error 1172 - Result consisted of more than one row

Then I saw the question:

mysql stored procedure error (1172, 'Result consisted of more than one row')

But it was not I meant to ask. LIMIT 1;-wasn't up to my expectation. It will just return the first row for all the case.

Then I started looking at this one deeply and now I got the solution.

This case happend because the code for stored procedure returns multiple rows and that was because I had many extra spaces and tab characters in my code[the code I wrote for stored procedure] and when I removed them with just one/two appropriate tab chars- it was just like a flying machine.

I don't know if it is the same case that you are experienced with. Anyway give a try.

Thank you.

Upvotes: 1

Related Questions