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

Try:

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

even I don't understand the "b.id>2" thing nor the "a.id=b.id".


On 27.6.2011. 21:17, Santana wrote:

dont be annoyed, but
first I´d check wether there is realy the same data in both databases.

select 'ID = 1", count(id) from tests where id=1;
select 'ID = 2", count(id) from tests where id=2;
select 'ID > 2", count(id) from tests where id>2;

and also the DDL of the table.

w.r.


Am 10.07.2011 23:22, schrieb a@a.com:

Hi,
I had a same kind of problem. What versions are you using ?
There are many bugs in 11.2.0.1. I was told to upgrade to 11.2.0.3 and after that I got the right result.

When using 10.2.0.4 optimizer in 11g database I got the same result as in 10g. You could try SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.4') */ hint in you query in 11g.

Regards,

Maija-Leena