Different results for the same query in 10g and 11g
Santana
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
Jonathan Lewis
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...@gmail.com> wrote in message
news:5ab9936f-80b0-4117...@a31g2000vbt.googlegroups.com...
Grzegorz
> 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
> ),
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
onedbguru
> On 2011-06-27 22:09, Santana wrote:
>
> > 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
> > ),
>
> Answered at oracle-l by Timur Akhmadeev
>
> Turning it off with the _fix_control will fix it.
>
> Regards
> GG
Make sure you gather statistics as well.. I have seen interesting
results when there are no statistics on the table.