Odd 'UNION' behavior in an Oracle SQL query

Question

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.

Answer 1

Try changing the UNION query to use UNION ALL, as in:

SELECT my_view.* 
FROM my_view 
WHERE my_view.trial in (select 2 as trial_id from dual
                        UNION ALL
                        select 3 AS TRIAL_ID from dual
                        UNION ALL
                        select 4 AS TRIAL_ID from dual) 
and my_view.location like ('123-%') 

I also put in "AS TRIAL_ID" on the 3 and 4 cases. I agree that neither of these should matter, but I've run into cases occasionally where things that I thought shouldn't matter mattered.

Good luck.

Answer 2

One thing you could try if you don't have luck with this route is to replace "IN" with an "EXISTS" or "NOT EXISTS" statement.

If you could accomplish what you want using joins, that would be the best option because of performance. If you have views pulling data from views, you can often make a single query to do what you want that gives you better performance using subqueries.

Answer 3

If you do

EXPLAIN PLAN FOR 
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-%');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

you should see where the location predicate is 9or is not) being applied. My bet is that it has something to do with the DB links and you won't be able to reproduce it if all the tables are local.

Optimizing a distributed query gets complicated.