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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 28 Jun 2011 09:34:43 +0100
Message-ID: <M7ydnRJHBpu_D5TTnZ2dnUVZ8t-dnZ2d_at_bt.com>


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.com> wrote in message 
news:5ab9936f-80b0-4117-8bd3-366952d98ac3_at_a31g2000vbt.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 Tue Jun 28 2011 - 03:34:43 CDT

Original text of this message