March 08, 2006

Oracle lameness and empty sets :(

So I wanted to run a query which would return a number of rows if their ids were in a set:


  select url from page where id in (....)

This works fine.

Unfortunately it breaks horribly if the set is empty. Why? There is no mathematical ambiguity about an empty set. Why would oracle simply not return 0 rows?


- 4 comments by 3 or more people Not publicly viewable

  1. John Dale

    Why, God, why?

    08 Mar 2006, 13:39

  2. :)

    It is particularly frustrating because it isn't intuitive and managed to escape the plethora of unit tests and integration tests because it never occurred to me that it would be a problem :)

    Oh well…..

    08 Mar 2006, 14:04

  3. Chris May

    it's bizarre. This fail works:

    SQL> select * from dual where dummy in ();
    select * from dual where dummy in ()
    *
    ERROR at line 1:
    ORA-00936: missing expression
    but this works fine
    SQL> select * from dual where dummy in (select dummy from dual where 1 = 0);  

    no rows selected
    It seems to be something as lame as oracle's parser not understanding that () is the empty set. Wierd.

    08 Mar 2006, 20:57

  4. Chris May

    … though not as weird as my spelling.

    08 Mar 2006, 20:58


Add a comment

You are not allowed to comment on this entry as it has restricted commenting permissions.

March 2006

Mo Tu We Th Fr Sa Su
Feb |  Today  | Apr
      1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31      

Search this blog

Tags

Galleries

Most recent comments

  • Interesting… While I'm not completely convinced in such microbenchmarks, I'm pretty sure that 1ms … by Alexander Snaps on this entry
  • Hello. I bought the book yesterday. I was trying to find the source code for chapter 11 and chapter … by Suleman on this entry
  • http://woosight.net/account/login?username=demo by live mashup demo on this entry
  • Thanks mate ….. This blog was really helpful. by Maaz Hurzuk on this entry
  • Ty. Not directly helpful for my problem, but pointed me in the right direction. You will also get th… by Mike E. on this entry

Blog archive

Loading…
Not signed in
Sign in

Powered by BlogBuilder
© MMXX