Feb 102014
 

Below is a direct copy and paste of some code I ran (nothing is done in between the sections). There’s clearly data in the tables, but it vanishes when they are joined:

insert into theres_a_row_here 
values ('Indeed there is');
insert into and_a_row_here 
values ('Indeed there is');

select * from theres_a_row_here;

BUT_FOR_SOME_REASON
-------------------
Indeed there is                  

select * from and_a_row_here;

THEY_DISAPPEAR_WHEN_JOINED
--------------------------
Indeed there is                         

select but_for_some_reason, 
       they_disappear_when_joined 
from   theres_a_row_here, 
       and_a_row_here
where  but_for_some_reason = 
         they_disappear_when_joined;

no rows selected

Why? As always, put your answers in the comments!

  7 Responses to “SQL Quiz: Why does this query return no data?”


  1. CREATE TABLE theres_a_row_here
    ( but_for_some_reason VARCHAR2(20 ) ;

    CREATE TABLE and_a_row_here
    ( they_disappear_when_joined CHAR(20) ) ;

  2. How about a stale Materialized View with Query Rewrite?
    Other options could include: Timeout-controlled Transaction Rollback before the join-SELECT, after the two single-table-SELECT, but I don’t know how to reproduce this easily, as it most likely would involve an Application server, while you just seem to be entering SQLs in SQL*Plus or SQL Developer.

    • Great point on stale MVs Salek, I hadn’t thought of that.

      Yep, I just copy-pasted from SQL Developer – not sure if you can have timeout-controlled rollback there. If you can put together an example I’d love to see it!

      • Me too :) I don’t think its possible. I thought about how timeouts could solve your riddle and googled for it. WebLogic JMS/JTA came up. I think that in this case the AS issues a ROLLBACK. I found nothing about an implicit rollback after x seconds in Oracle itself.
        dba-oracle.com has an article “Timeout dead or idle Oracle sessions”. But this is about sessions, not transactions.

  3. Yep, classic symptom of presence of char columns – almost as annoying as case sensitive columns

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>