Hello, guys!
Recently, I've found a peculiar situation when building a SQL query. The purpose was add a "where" clause using a "case" statement that was intented to verify if determined condition was greater than zero. I've reproduced using a "with" clause for better understanding, so here it go:
with test as (
select 1 as col from dual
union all
select 2 as col from dual
)
select *
from test
where (case when 1 = 1 then 1 else (select count(*) from dual where 1 = 0) end) > 0;
This query was supposed to retrieve the following lines:
col
----------
1
2
----------
As we know, one is equal one, but when I've added the "else" condition that counts from dual on a "impossible-to-retrieve-condition" (zero equals one), it seems like Oracle stops entering the first condition (1 = 1) and enters at second condition, returning zero lines.
When I don't use the count(*) at the "else" statement, the results are returned, even thought the count result is zero, same as the following query:
with test as (
select 1 as col from dual
union all
select 2 as col from dual
)
select *
from test
where (case when 1 = 1 then 1 else 0 end) > 0;
Another weird fact: if I exchange "> 0" to ">= 1", it works normally.
Any clues about it? I haven't figured out yet what is causing this unusual "bug".
Thanks.
Yep, it's a bug of some description. I'm not sure which, but it's fixed in 12.1:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> with test as (
2 select 1 as col from dual
3 union all
4 select 2 as col from dual
5 )
6 select *
7 from test
8 where (
9 case
10 when 1 = 1 then 1
11 else (select count(*) from dual where 1 = 0)
12 end
13 ) > 0;
no rows selected
SQL> conn chris/chris@db121
Connected.
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
SQL> with test as (
2 select 1 as col from dual
3 union all
4 select 2 as col from dual
5 )
6 select *
7 from test
8 where (
9 case
10 when 1 = 1 then 1
11 else (select count(*) from dual where 1 = 0)
12 end
13 ) > 0;
COL
----------
1
2