Re: possible optimizations - pushing filter before aggregation

On Fri, Nov 18, 2016 at 12:47 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

Isn't possible in this case push equivalence before aggregation?

If I'm understanding you correctly, that would lead to wrong results.
Here's a simple example:

CREATE VIEW v AS SELECT MIN(a) m FROM t;

and table T contains:

T:A
---
1
2
3

SELECT * FROM v WHERE m = 2

The minimum value of A is 1, so the query should return no rows.

However, if we filter first we'd be effectively doing the query:

SELECT MIN(a) m FROM
(SELECT a FROM t WHERE a=2) AS v(a)

The subquery is going to return an intermediate result of:

V:A
---
2

And the minimum of that is 2, which is the wrong answer.

- Doug
Salesforce