Re: SELECT id, myFunc(id) FROM aTable ORDER BY id; returns bad data
Because Mitch' results look so strange (and because he made it so easy to reproduce it all, i didnt need to change a single thing, so, good one Mitch!) I decided to try and reproduce the results. (Which baffle me completely)
Obviously, in the test Mitch does, the SELECT count(*) always yields one row, and because he feeds it with an id for a row that always exists, i decided to test your suggestion to see if his usage of EXISTS would have any affect on the particular strange result he was getting. I deleted the IF EXISTS bit from the procedure definition and as you'd expect, it did not affect the resultset. So far, i was just trying to isolate a simpler statement still capable of delivering the wrong result.
This convinced me that Mitch has found a genuine bug there.
Then I was wondering if Mitch knew something about EXISTS that I did not know, because I was (and still am) under the same impression as you, namely that EXISTS is a unary logical operator that accepts a subquery operand and returns true only if that subquery yields at least one row. (I was thinking that maybe EXISTS meant something different when used outside a SELECT, e.g. as part of a sp statement. I was thinking of this possiblity because of that other usage of EXISTS, I mean the EXISTS you use in DDL context to test if an object exists.)
So I just set up the small procedure p() to test this, and when you go by that example, EXISTS really does seem to work different in this case....
Anyway, i just rewrote that little test using this table onerow:
create table onerow(c int);
insert into onerow values(1);
rather than DUAL, and loo and behold: EXISTS is behaving as expected again!
So, the strange results I'm getting through that sidewalk have got more to do with DUAL than with EXISTS.
Can you confirm these results:
TEST 1: EXISTS (SELECT count(*) FROM DUAL) can be FALSE
delimiter $
create procedure p_test_exists_dual (p int)
if exists (select count(*) from dual where 1 = p) then
select 'true';
else
select 'false';
end if;
$
mysql> call p_test_exists_dual(1)$
+------+
| true |
+------+
| true |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call p_test_exists_dual(0)$
+-------+
| false |
+-------+
| false |
+-------+
1 row in set (0.00 sec)
(Both should return the 'true' resultset.)
TEST 2: EXISTS (SELECT count(*) FROM usertable) cannot be FALSE
create table t_test_exists(c int)$
/*
Note that we are not inserting any rows in t_test_exists
t_test_exists remains empty
*/
create procedure p_test_exists (p int)
if exists (select count(*) from t_test_exists where 1 = p) then
select 'true';
else
select 'false';
end if;
$
mysql> call p_test_exists(1)
-> $
+------+
| true |
+------+
| true |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call p_test_exists(0)$
+------+
| true |
+------+
| true |
+------+
1 row in set (0.01 sec)
(works as expected, contrary to the equivalent TEST1 results)
TEST 3: try something like TEST1 in another context
insert into t_test_exists values (1)
set @p=1$
select 'true'
from t_test_exists
where exists (select count(*) from dual where @p=1)
$
+------+
| true |
+------+
| true |
+------+
1 row in set (0.00 sec)
set @p=0$
select 'true'
from t_test_exists
where exists (select count(*) from dual where @p=1)
$
Empty set (0.00 sec)
(Expected both queries to return a non empty set, seems same bug as in TEST1)
I tried it with a constant value rather than with COUNT(*); it gives me the same results.
So there goes my illumination, sorry for the confusion. (Shall I report this DUAL business as a bug or what?)
Roland.