Re: SELECT id, myFunc(id) FROM aTable ORDER BY id; returns bad data

[Roland Bouman]

I can confirm that the EXISTS is not responsible for this.

CREATE FUNCTION GetUserFullName(
usrID INTEGER
) RETURNS varchar(32)
BEGIN
DECLARE uName VARCHAR(32) DEFAULT null;
SELECT concat(firstName, ' ', lastName) INTO uName FROM users WHERE userID = usrID;
RETURN uName;
END;

returns exactly the same data. Really weird result, it's as if the variable is not properly assigned.

I think this really is a bug.

(BTW, Jay the EXISTS does seem to do what Mitch intends it to do:

create procedure p(a integer)
if exists (select count(*) from dual where 1=a) then
select 'x';
else
select 'y';
end if;
%%

mysql> call p(1);
-> %%
+---+
| x |
+---+
| x |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call p(0);
-> %%
+---+
| y |
+---+
| y |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

In other words, the EXISTS does seem to evaluate the scalar result returned by the SELECT in this case, evaluting to FALSE if the result is equeal to zero.
)



Edited 1 time(s). Last edit at 09/23/2005 06:14PM by Roland Bouman.