Different results for the same query in 10g and 11g

From: Santana <paulito.santana_at_gmail.com>
Date: Mon, 27 Jun 2011 12:17:57 -0700 (PDT)
Message-ID: <fedac0b6-f711-4cce-aa47-5518735e1ffc_at_q1g2000vbj.googlegroups.com>

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

---

| Id | Operation | Name | E-Rows |

---

|*  1 |  FILTER              |       |        |


|   2 |   TABLE ACCESS FULL  | TESTS |      2 |


|   3 |   SORT AGGREGATE     |       |      1 |


|*  4 |    FILTER            |       |        |
|*  5 |     TABLE ACCESS FULL| TESTS |      1 |
-----------------------------------------------

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

---

| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-
Mem |

---

| 0 | SELECT STATEMENT | | | |
| |

|* 1 | HASH JOIN | | 1 | 899K| 899K| 207K (0)|
| 2 | VIEW | VW_SQ_1 | 1 | |
| |
| 3 | HASH GROUP BY | | 1 | 1001K|

1001K|          |
|*  4 |     TABLE ACCESS FULL| TESTS   |      1 |       |


|          |

|   5 |   TABLE ACCESS FULL  | TESTS   |      2 |       |


|          |

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

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 Received on Mon Jun 27 2011 - 21:17:57 CEST