Re: possible optimizations - pushing filter before aggregation

2016-11-19 3:59 GMT+01:00 Douglas Doole <dougdoole(at)gmail(dot)com>:

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

yes, you have true,

thank you for correcting

Regards

Pavel

>
> - Doug
> Salesforce
>