Odd 'UNION' behavior in an Oracle SQL query
- by RenderIn
Here's my query:
SELECT my_view.*
FROM my_view
WHERE my_view.trial in (select 2 as trial_id from dual union select 3 from dual union select 4 from dual)
and my_view.location like ('123-%')
When I execute this query it returns results which do not conform to the my_view.location like ('123-%') condition. It's as if that condition is being ignored completely. I can even change it to my_view.location IS NULL and it returns the same results, despite that field being not-nullable.
I know this query seems ridiculous with the selects from dual, but I've structured it this way to replicate a problem I have when I use a 'WITH' clause (the results of that query are where the selects from dual inline view are).
I can modify the query like so and it returns the expected results:
SELECT my_view.*
FROM my_view
WHERE my_view.trial in (2, 3, 4)
and my_view.location like ('123-%')
Unfortunately I do not know the trial values up front (they are queried for in a 'WITH' clause) so I cannot structure my query this way. What am I doing wrong?
I will say that the my_view view is composed of 3 other views whose results are UNION ALL and each of which retrieve some data over a DB Link. Not that I believe that should matter, but in case it does.
