I have a puzzling situation where a query works, but only outside of a
function. If I try to abstract away some of the complexity by placing the
subquery inside a function, the db seems to get stuck in an infinite query
and the entire system becomes unresponsive (from the MySQL console I can
ctrl-c to break out, but from the GUI query browser there is no way to stop
it other than hard reset).
Anyway, the db is laid out so that a class of elements is kept in one table,
while another table keeps instances of the elements. The 'entry' number from
the class table is used as a foreign key in the instance table. Makes sense
so far, right? The tricky part is that the 'entry' number is just an
internal value used as a unique key, so the users of the instance table have
to keep looking up the 'name' of the element in order to find out what the
'entry' number is. The idea is to make life easier for them by translating
the 'name' string into the 'entry' number via a function.
So, here's a raw query string, which works ok:
SELECT * FROM tbl1 WHERE id=(SELECT entry FROM tbl2 WHERE name='someguy');
-- returns the correct rows from tbl1
If I take the subquery and put it in a function, which takes the name string
as an argument, then make a simple SELECT query to test the function by
itself, it returns the correct value, like so:
create function getNumber(cName varchar(255)) returns int begin
return (select entry from tbl2 where name=cName);
end;
SELECT getNumber('someguy'); -- returns the correct value
Now, when I try to use the function in the full query, that's when the
problem state occurs:
SELECT * FROM tbl1 WHERE id=getNumber('someguy'); -- disaster
Can anybody tell me what's wrong?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
