db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: How does WHERE condition in a VIEW with UNIONs get applied
Date Wed, 11 Sep 2013 11:10:33 GMT
jefffro <jferla@us.ibm.com> writes:

> Suppose I have a view defined as the union of multiple table queries e.g. 
>
> create view v1 as 
>  SELECT col1 as a , col2 as b FROM table1
>  UNION
>  SELECT col2 as a , col3 as b FROM table2
>  UNION 
>  SELECT col4 as a , col5 as b from table 3;
>
> If I use this view in a query ... select * from v1 where a=5 ... what is the
> behavior of applying the where condition into the view.  From what I can
> tell, it seems to be running each unconditioned SELECT without a where
> clause and then filtering the actual results that than applying the where
> clause into the individual SELECTs of the union ... e.g. 
>
>  SELECT col1 as a , col2 as b FROM table1 where col1=5
>  UNION
>  SELECT col2 as a , col3 as b FROM table2 where col2=5
>  UNION 
>  SELECT col4 as a , col5 as b from table3 where col4=5
>
> I need to verify that this is not due to out of data statistics (I do not
> think that is the case) but I felt it was probably worth finding out whether
> I am being a little too optimistic on what the planner can do. 

Hi Jeff,

There is a similar report in the bug tracker, where the predicate in the
outer SELECT is not pushed down to the UNION:

https://issues.apache.org/jira/browse/DERBY-3714

I'm not sure if the optimizer lacks support for pushing predicates down
to UNIONs altogether, or if it just has problems with some specific
queries. If you find out that this is the problem you're seeing with
your query, it would be helpful if you could attach a reproducible test
case to the bug report.

Thanks,

-- 
Knut Anders

Mime
View raw message