CODEWITHSUNDEEP
mysqlstored-proceduresguid
Tsar Bomba
Tsar Bomba

Reputation: 1106

MySQL stored procedure where clause is not filtering records

I'm working with a db where the keys are all binary(16), essentially stored as a GUID with a couple of the values flipped around. I have a simple stored procedure where I want to filter out a single by ID.

delimiter //
create procedure select_item_by_id (
    in id binary(16)
)
begin
    select
        `id`,
        `name`
    from
        `item`
    where
        `id` = id;
end //
delimiter ;

When I fire it like so, it pulls back all the records in the table, no filtering is done:

call select_item_by_id(unhex('11e7deb1b1628696ad3894b2c0ab197a'));

However, if I run it manually...it filters the record exactly as expected:

select
    `id`,
    `name`
from
    `item`
where
    `id` = unhex('11e7deb1b1628696ad3894b2c0ab197a');

I even tried passing in a string/chars and doing the unhex inside of the sproc, but that pulls zero results:

delimiter //
create procedure select_item_by_id (
    in id char(32)
)
begin
    select
        `id`,
        `name`
    from
        `item`
    where
        `id` = unhex(id);
end //
delimiter ;

call select_item_by_id('11e7deb1b1628696ad3894b2c0ab197a');

Pretty weird. What am I doing wrong?

Upvotes: 1

Views: 1682

Answers (2)

ztaylor54
ztaylor54

Reputation: 406

It's likely that WHERE id = id is always evaluating to true, as it might be checking if the row's id is equal to itself. Rename the parameter to something else.

Upvotes: 4

Patrick Artner
Patrick Artner

Reputation: 51683

Rename the parameter of your proc:

create procedure select_item_by_id (
    in idToTest char(32)
)

and use 

    where
        `id` = idToTest;

to avoid ambiguity.

Upvotes: 2

Related Questions