A query: select * returns data, but select Column does not return results

Question

I have a query like this:

select pln.* 
  from plan pln 
 where pln.id = '0003'  
   and pln.seq = (select max(pln_es.seq)
                    from plan pln_es
                   where pln_es.id = pln.emplid
                     and pln_es.career =pln.career
                     and pln_es.nbr = pln.nbr
                     and pln_es.dt = pln.dt)

However, if I do (Select Column...), using the same conditions, it does not return data! Makes no sense to me.

Here's the query that Does Not return results:

select pln.MYDATE
  from plan pln 
 where pln.id = '0003'  
   and pln.seq = (select max(pln_es.seq)
                    from plan pln_es
                   where pln_es.id = pln.emplid
                     and pln_es.career =pln.career
                     and pln_es.nbr = pln.nbr
                     and pln_es.dt = pln.dt)

Does anyone know why could this be happenning? The column per se is a Date, and the DB is Oracle 10g

Answer 1

Is MYDATE always NULL in the returned rows? SQL (at least some implementations, I'm not familiar with Oracle) will drop rows consisting of entirely NULL values from the result set.

Answer 2

For the below joins I'd check for: 1) Are you comparing different types in your sub query? 2) Is any of the number columns you are joining null, that seems to cause issues.

Joins to look at...

pln_es.id = pln.emplid
and pln_es.career =pln.career
and pln_es.nbr = pln.nbr
and pln_es.dt = pln.dt

Also have you tried seeing of the results of a query like this returns anything.

select pln.MYDATE
  from plan pln 
 where pln.id = '0003'  
   and pln.seq = (select max(pln_es.seq)
                    from plan pln_es
                   where pln_es.id = <insert hard coded value>
                     and pln_es.career =<insert hard coded value>
                     and pln_es.nbr = <insert hard coded value>
                     and pln_es.dt = <insert hard coded value>)

Answer 3

Assuming you aren't using a sloppy transaction isolation level, and that the data isn't changing between executions, what you've described shouldn't happen and is likely a bug. The query plans for selecting one column vs. selecting all can easily be very different if there happens to a covering index for the one column, so the fact that the bug would appear in one query but not the other is no surprise. (Similarly, if there's no bug, but you're allowing dirty reads, you might get them from one plan but not the other.) You could try things like rebuilding indexes that are used by the query that produces wrong results. You could also see if you're using any of the features that frequently appear near the phrase "wrong results" in Oracle bug fix lists like this one.

Answer 4

Slightly off-topic, but your query would be better written as:

select MYDATE 
  from (Select plan.*,
               max(pln_es.seq) Over (id,career,nbr) max_seq
          from plan
         where id = '0003')
 where seq = max_seq;

Be interesting to see if this query has the same issue.