Oracle: summing column value with subquery

oraclesubquery

Are there some restrictions in Oracle that disallows to have a WHERE clause that compares value of column+(subquery count(*))? If the subquery table is empty, count(*) doesn't yield to zero in this scenario.

I expect this query

select * from Foo where (x+(select count(*) from SomeEmptyTable))>0

To be identical to

select * from Foo where x>0

But instead Oracle doesn't return any results with the first query. In MySql the both queries return the same result selt.

My test SQL snippets:

create table Foo ( x int );

insert into Foo values ( 1 );

create table SomeEmptyTable ( x int );

I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

UPDATE:

Or does the comparison to zero have some special meaning in this case?

Both

select * from Foo where (x+(select count(*) from SomeEmptyTable))>=0

and

select * from Foo where (x+(select count(*) from SomeEmptyTable))>-1

Returns 1 row as would be expected. Only when compared to >0 zero rows are returned.

Best Answer

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 < ( ... ).

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 >=0 and >-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")>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.

Related Question