Hi all.
I need your help for the following query that return one row in 10g
and dont return any row in 11g: I know which ther is others ways to
implement
this query but i wan understand what is wrong!


This is mysterious query :

select *
from tests a
where id=nvl(( select max(b.id)
from tests b where b.id>2
and a.id=b.id
),
1)



What i must do in 11g in order this query return data ?





There is all script and the execution plan for 10g and 11g:

create table tests (id number)

insert into tests values(1)
insert into tests values(2)

select *
from tests a
where id=nvl(( select max(b.id)
from tests b where b.id>2
and a.id=b.id
),
1)


in 10g return one row and this is the exection plan:
PLAN_TABLE_OUTPUT
SQL_ID 66rp53rd4493w, child number 0
-------------------------------------
select * from tests a where id=nvl(( select
max(b.id)
from tests b where b.id>2 and
a.id=b.id
), 1)

Plan hash value: 2928053570

-----------------------------------------------

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

1 - filter("ID"=NVL(,1))
4 - filter(2<:B1)
5 - filter(("B"."ID"=:B1 AND "B"."ID">2))



in 11g dont return any row and this is the execution plan:


PLAN_TABLE_OUTPUT
SQL_ID 66rp53rd4493w, child number 0
-------------------------------------
select * from tests a where id=nvl(( select
max(b.id)
from tests b where b.id>2 and
a.id=b.id
), 1)

Plan hash value: 848999739

----------------------------------------------------------------------------
----------------------------------------------------------------------------

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

1 - access("ID"=NVL("MAX(B.ID)",1) AND "A"."ID"="ITEM_0")
4 - filter("B"."ID">2)


Regards,
Paulito Santana

Two different results for the same query - they can't both be right.

It's clearly a bug in the code; you have a simple reproducible
demonstration.

Raise an SR with Oracle.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com


"Santana" <paulito.santana (AT) gmail (DOT) com> wrote

On 2011-06-27 22:09, Santana wrote:
Answered at oracle-l by Timur Akhmadeev



It’s a “feature” 7215982 “unnest subquery embedded inside an expression”.
Turning it off with the _fix_control will fix it.


Regards
GG

On Jun 28, 11:27*am, Grzegorz <grzegor... (AT) interia (DOT) pl> wrote:

Make sure you gather statistics as well.. I have seen interesting
results when there are no statistics on the table.