FAQ
create table tests (id number);

insert into tests values(1);

insert into tests values(2);

commit;

/* in 10g this gives 1 row, in 11g no rows */

SELECT *

FROM tests a

WHERE id = NVL ( (SELECT MAX (b.id)

FROM tests b

WHERE b.id > 2 AND a.id = b.id),

1);

/* the hint will make 11g have same result as 10g */

SELECT *

FROM tests a

WHERE id = NVL ( (SELECT /*+ NO_UNNEST */ MAX (b.id)

FROM tests b

WHERE b.id > 2 AND a.id = b.id),

1);

I got this example from the oracle-plsql group.

Is this an oracle bug?

Regards,

Mike

Search Discussions

  • Coskan gundogar at Jun 28, 2011 at 1:07 pm
    What are your versions ?

    SQL> select * from v$version
    2;

    BANNER

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE 11.2.0.1.0 Production
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production

    SQL> _at_test
    SQL> select * FROM tests a
    2 WHERE id = NVL ( (SELECT MAX (b.id)

    3 FROM tests b
    4 WHERE b.id > 2 AND a.id = b.id),
    5 1);

    ID
    ----------
    1
    On 27 June 2011 22:32, Michael Moore wrote:

    create table tests (id number);



    insert into tests values(1);

    insert into tests values(2);

    commit;



    /* in 10g this gives 1 row, in 11g no rows */

    SELECT *
    FROM tests a

    WHERE id = NVL ( (SELECT MAX (b.id)

    FROM tests b

    WHERE b.id > 2 AND a.id = b.id),

    1);



    /* the hint will make 11g have same result as 10g */

    SELECT *
    FROM tests a

    WHERE id = NVL ( (SELECT /*+ NO_UNNEST */ MAX (b.id)

    FROM tests b

    WHERE b.id > 2 AND a.id = b.id),

    1);


    I got this example from the oracle-plsql group.

    Is this an oracle bug?


    Regards,

    Mike
    --
    --
    Coskan GUNDOGAR

    Oracle DBA

    Email: coskan_at_gmail.com
    Blog: http://coskan.wordpress.com
    Twitter: http://www.twitter.com/coskan
    Linkedin: http://uk.linkedin.com/in/coskan

    --
    http://www.freelists.org/webpage/oracle-l
  • Michael Moore at Jun 28, 2011 at 2:20 pm
    Awesome, I will provide info to our DBA and developer team.

    On Tue, Jun 28, 2011 at 12:16 AM, Timur Akhmadeev
    wrote:
    Hi



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



    Regards

    Timur Akhmadeev

    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Michael Moore
    *Sent:* Tuesday, June 28, 2011 1:32
    *To:* oracle-l@freelists.org
    *Subject:* 10g vs 11g CBO UNNEST different results



    create table tests (id number);



    insert into tests values(1);

    insert into tests values(2);

    commit;



    /* in 10g this gives 1 row, in 11g no rows */

    SELECT *
    FROM tests a

    WHERE id = NVL ( (SELECT MAX (b.id)

    FROM tests b

    WHERE b.id > 2 AND a.id = b.id),

    1);



    /* the hint will make 11g have same result as 10g */

    SELECT *
    FROM tests a

    WHERE id = NVL ( (SELECT /*+ NO_UNNEST */ MAX (b.id)

    FROM tests b

    WHERE b.id > 2 AND a.id = b.id),

    1);



    I got this example from the oracle-plsql group.

    Is this an oracle bug?



    Regards,

    Mike



    ------------------------------
    The information transmitted herein is intended only for the person or
    entity to which it is addressed and may contain confidential, proprietary
    and/or privileged material. Any review, retransmission, dissemination or
    other use of, or taking of any action in reliance upon, this information by
    persons or entities other than the intended recipient is prohibited. If you
    received this in error, please contact the sender and delete the material
    from any computer.



    <#130d51b3f0e9972c_>
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 27, '11 at 9:32p
activeJun 28, '11 at 2:20p
posts3
users2
websiteoracle.com

2 users in discussion

Michael Moore: 2 posts Coskan gundogar: 1 post

People

Translate

site design / logo © 2022 Grokbase