Oracle Scratchpad

October 4, 2013

Quiz Night

Filed under: Bugs,Oracle — Jonathan Lewis @ 3:08 pm GMT Oct 4,2013

Okay, it’s a little early in the day (for me at least) to say “night” – but here’s a fun little detail I picked up in Prague yesterday. What do you think will happen when you try to execute the following two queries:


select 0/0 from dual;

select count(*) from (select 0/0 from dual);

I’ve only tried it on 11.2.0.4 and 12.1.0.1 – I could imagine the results might be different if you’re still running 8i or 9i.

If those are too easy, you might want to think about an example that Julian Dontcheff produced at OpenWorld:


select power(0,0) from dual;

What SHOULD the answer be, and what do you think Oracle will supply ?

20 Comments »

  1. select power(0,0) from dual;
    ==> it should return 1 as anyone who knows maths could tell…so oracle should also return the same.

    select 0/0 from dual;
    ==> should return nothing or error out as again maths rule can not be divided by 0.

    Comment by vishal — October 4, 2013 @ 3:25 pm GMT Oct 4,2013 | Reply

    • anyone who nows maths could tell it is not defined. however limit x -> 0 x^x tends to 1 (from right). Shouldn’t oracle return NULL? :) :)

      Comment by Pavol BabelPavol — October 4, 2013 @ 6:31 pm GMT Oct 4,2013 | Reply

  2. 0/0 is a nonsense (from a mathematical point of view, it is undeterminated), thus “select 0/0 from dual” should raise an error.
    For the same reason, select count(*) from (select 0/0 from dual) should raise the same error. However Oracle is smart enought to know that it does not need to evaluate the function in order to count the number of rows, so it should transform “select count(*) from (select 0/0 from dual)” into something like “select count(*) from (select null from dual)” or just “select count(*) from dual” and not raise any error.

    We can experiment a litte and see what happend if we run

    with t as (select /*+materialize*/ 0/0 from dual) select count(*) from t ;
    with t as (select 0/0 from dual) select count(*) from t ;

    power(0,0) is undeterminated too, thus “select power(0,0) from dual” should raise an error

    regards
    Andrea

    Comment by Andrea — October 4, 2013 @ 3:41 pm GMT Oct 4,2013 | Reply

    • Andrea,

      I like your answers,
      However, one could also say that
      “…However Oracle is smart enought to know that it does not need to evaluate the function in order to count the number of rows…” is false and in fact it is a bug that Oracle doesn”t evaluate the function !
      Take “select count(*) from (select 0/0 from dual)” as an example for that because it – falsely – delivers “1”.
      But – no one would like to see this bug “fixed”, I think because the smart shortcut which is taken here does no harm.

      Matthias

      Comment by Sokrates — October 4, 2013 @ 5:10 pm GMT Oct 4,2013 | Reply

  3. How interesting. So, when doing the count(*) on the subquery involving DUAL it seems like Oracle doesn’t even care what you are SELECTing. It just assumes you are returning one row. A similar example:

    select sqrt(-4) from dual;

    select count(*) from (select sqrt(-4) from dual);

    Comment by Reinhard Hillefeld — October 4, 2013 @ 3:43 pm GMT Oct 4,2013 | Reply

  4. Good one Jonathan – I was suprised to see “power(0,0)” return one after running this in 11.2.0.2 environment, I found this explaination: http://www.math.hmc.edu/funfacts/ffiles/10005.3-5.shtml

    Comment by Anthony — October 4, 2013 @ 3:45 pm GMT Oct 4,2013 | Reply

    • Anthony,
      Interesting – except that I was taught differently many years ago.

      Based on the argument that f(x) -> x^N should be be continuous and differentiable for all x, then power(0,0) is defined as zero.

      Comment by Jonathan Lewis — October 4, 2013 @ 4:05 pm GMT Oct 4,2013 | Reply

      • Indeed – my first expectation was that power(0,0) would be be zero also.

        Comment by Anthony — October 4, 2013 @ 4:30 pm GMT Oct 4,2013 | Reply

      • Hi Jonathan,
        the problem with the definition of 0^0 is that f(x,y) -> x^y is continuos and differentiable almost everywhere , x^0 = 1 for all x > 0 , 0^y = 1 for all y>0, thus we cannot find a consistent definition of 0^0

        Comment by Andrea — October 4, 2013 @ 4:46 pm GMT Oct 4,2013 | Reply

        • oops, there’s a typo:
          f(x,y) -> x^y is continuos and differentiable almost everywhere , x^0 = 1 for all x > 0 , 0^y = 0 for all y>0, thus we cannot find a consistent definition of 0^0

          Comment by Andrea — October 4, 2013 @ 4:48 pm GMT Oct 4,2013

  5. Funny.
    But have you ever tried working with the log function?
    Below results are worrying, I think.
    First time I encountered this was in 8i. In 11.2 still there. Haven’t tried in 12 yet.

    Her goes:
    We all now that by definition, if power(x,y)=z then log(x,z)=y
    So 2^10=1024 means that 2log(1024)=10.
    Not 9, not 9.9999999999, not 9.99999999999999999999999999999999999999999

    So what does Oracle have to say about this:

    SELECT POWER(2,10) power_2_10 from dual;

    POWER_2_10
    —————
    1024

    Correct!

    SELECT LOG(2,1024) log_2_1024 from dual;

    LOG_2_1024
    —————
    10

    APPEARS correct, however:

    SELECT TRUNC(LOG(2,1024)) trunced from dual;

    TRUNCED
    —————
    9

    Excuse me?
    Obviously Oracle seems to think that 2log(1024) is a tiny little bit less then 10.

    Comment by Erik van Roon — October 4, 2013 @ 3:55 pm GMT Oct 4,2013 | Reply

    • Erik,
      You don’t need anything as complex as logarithms:

      SQL> select trunc((1 / 3 ) * 3) from dual;
      
      TRUNC((1/3)*3)
      --------------
                   0
      

      Versions 12.1.0.1 – and probably all others. It’s the effect of rounding errors in arithmetic with a finite precision, and an argument for specifying number (precision, scale) in your tables rather than just declaring numeric columns as number.

      Comment by Jonathan Lewis — October 4, 2013 @ 4:02 pm GMT Oct 4,2013 | Reply

  6. Hi,

    Just another funny dual quiz… What would you expect from?

    insert into sys.dual values (‘X’);
    select count(*) from dual;

    All I can say it wasn’t always like in 12.1, but let us consider it as a feature :)

    Istvan

    Comment by Istvan Stahl — October 4, 2013 @ 8:23 pm GMT Oct 4,2013 | Reply

  7. CTEs don’t bring additional surprises:

    SQL> r
      1  with
      2  basedata as (
      3  select /*+ materialize */ 0/0 from dual)
      4* select count(*) from basedata
    select /*+ materialize */ 0/0 from dual)
                               *
    FEHLER in Zeile 3:
    ORA-01476: Divisor ist Null
    
    SQL> r
      1  with
      2  basedata as (
      3  select /*+ inline */ 0/0 from dual)
      4* select count(*) from basedata
    
      COUNT(*)
    ----------
             1
    

    Though this be madness, yet there is method in ’t.

    Comment by Martin Preiss — October 5, 2013 @ 10:11 pm GMT Oct 5,2013 | Reply

  8. I should have read the comments more careful to see that Andrea already posted the CTE variant.

    Comment by Martin Preiss — October 5, 2013 @ 10:14 pm GMT Oct 5,2013 | Reply

  9. Very intersting to compare results in different db.
    Query select 1 from dual where exists (select 1 / 0 from dual) returns 1 in SQL Server. Is it a feature of various optimizers?

    Comment by Andrew — October 7, 2013 @ 12:59 pm GMT Oct 7,2013 | Reply

  10. I would convert result of 0/0 to binary_* and return Nan :-)
    But correct answer is possible that 0/0 is not pure function (expression) as it throws an exception. Thus according to the docummentation we cant expect any predictable behavior. There are a lot of the simmilar examples of “how many times function will be executed” and funny with using random function with IRS in old oracle versions.

    Comment by Valentin Nikotin — October 9, 2013 @ 7:30 am GMT Oct 9,2013 | Reply

    • Valentin,

      I always enjoy following your thoughts.
      What do you mean by “IRS in old oracle versions” ?

      Matthias

      Comment by Matthias Rogel — October 9, 2013 @ 8:18 am GMT Oct 9,2013 | Reply

      • Thanks Matthias, I mean the case when there was predicate like “where id = dbms_random.random” and oracle called random twice to get start and stop keys.

        Comment by Valentin Nikotin — October 9, 2013 @ 9:58 pm GMT Oct 9,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,521 other followers