Oracle: summing column value with subquery?
You're unfortunately hitting bug 12999577 (Oracle support account required) or something close to it. And the fix for that bug introduced another bug 17564992.

The workaround indicated in the first bug report is to switch the conditions around, i.e. express your where clause as 0 &lt; ( ... ).

The workaround indicated in the second is to set either of these:

alter session set "_fix_control" = '12999577:0';
-- or
alter session set "_optimizer_squ_bottomup"= FALSE;


I've tried your example on an 12.1.0.2 database that reproduces the behavior you are seeing (including returning the expected result for the &gt;=0 and &gt;-1 cases). The workarounds suggested for that second bug appear to work and give the expected result. But do verify the bugs yourself, and seriously consider applying the bugfix rather than these parameters. I'd suggest opening an SR with Oracle if you want confirmation for your particular version.



Interesting difference in the explain plans. With the _fix_control (correct result):

--------------------------------------------------------------------------------
| Id  | Operation       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    13 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL  | FOO      |     1 |    13 |     3   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE    |          |     1 |       |        |          |
|   3 |    TABLE ACCESS FULL| EMPTYTAB |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("F"."X"+ (SELECT COUNT(*) FROM "EMPTYTAB" "EMPTYTAB")&gt;0)


Without the _fix_control (no rows returned):

-------------------------------------------------------------------------------
| Id  | Operation      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    82 |  1066 |     4   (0)| 00:00:01 |
|*  1 |  FILTER        |          |       |       |        |          |
|   2 |   TABLE ACCESS FULL| FOO      |    82 |  1066 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMPTYTAB |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM "EMPTYTAB" "EMPTYTAB"))


Plan is completely different, the filtering clause is interesting.