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

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.i开发者_StackOverflow中文版d = 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

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.

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.

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>)

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.

Seen this happen when there is an index on the specific column being selected, and the index is 'corrupted' for want of a better word. SQL is returning the value stored in the index, instead of the value stored on the row. Dropping and recreating that index solved the issue.