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

[Roland Bouman]

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.