select count x with nulls

I came across this when trying to find why a query didnt work. And I was repeatedly assured it had always worked correctly. Of course it had always worked correctly, or rather, it had been made to work in a "satisfactory" way to the masters and callers that examined the output.

So how come it suddenly refused to return the expected results?
Surely this was a database bug ?

Well, it wasnt the database at fault, nor was it the code at fault. The code in question was a roundabout way to check for existence or presence of a number of children for a set of parents. Not elegant either, mais soit.

You can imagine that "the problem" did not occur immediately when the query was run in SQL*Plus, but we persisted, looking for the oddities and edge-conditions.

When we findally found it, I whipped up a demo in SQL*plus to show the developers. I had to disguise (rewrite) the query to remove references to original table, and I simplified it. The original was a tad more complex (30+ lines..), with more bind-vars and the issue hidden in the middle of it. But the concept was the same.

You can copy-paste the whole thing into SQL*Plus, provided you have scott/tiger with empt/depth.

Go see for yourself.



/*

select_null.sql:
how you can acidentially not select a thing..

You get the Idea:
If you count empty strings, or NULLs,
the result will be ...

*/

-- First we show accepted behaviour,
-- you count records by selecting count('x'):

select count ('x') from dept ;

-- And the following is an accepted (Oracle) quirk,
-- and a known pitfall...

select count ('') from dept ;


-- But if the count is hidden in a subqry,
-- and if the count is disquised with a bind-var,
-- Suddenly; a lot depends on the bind-var ....


variable x varchar2(10) ;

exec :x := 'a' ;

select :x as bindvar, d.*
from dept d
where ( select count (:x)
from emp e
where e.deptno = d.deptno
) > 0
/

-- and if the variable is empty...

exec :x := '' ;

select :x as bindvar, d.*
from dept d
where ( select count (:x)
from emp e
where e.deptno = d.deptno
) > 0
/

-- Be careful what you count for,
-- you might get it...



Remarks on a postcard in the comment dept.

NB: Does anyone have an established, and format-perserving way to present SQL-code in blogger? My stmnts seem to left-align whatever I do...