JOIN and limit

[Abe Weinograd <ab...@flonet.com>]

Given the following query....


select * from a left outer join b on a.col2 = b.col2 and b.col3 = 'X' WHERE
a.col1 = 'Y' LIMIT 1000


After playing with this for a while and getting results that didn't make
sense, it seems the LIMIT is being pushed on b or something like it before
the join is applied and not after the full resultset is computed.  I was
digging around a little bit.  Is that expected behavior?

Thanks,
Abe

Re: JOIN and limit

[Maryann Xue <ma...@gmail.com>]

Hi Abe,

The expected behavior should be pushing the LIMIT to a (since it's left
outer join) while checking the limit again against the final joined
results. But it does not work as expected, it should be bug.

Could you please verify it and report an issue with a test case attached?


Thanks,
Maryann

On Thu, Sep 18, 2014 at 8:51 PM, Abe Weinograd <ab...@flonet.com> wrote:

> Given the following query....
>
>
> select * from a left outer join b on a.col2 = b.col2 and b.col3 = 'X'
> WHERE a.col1 = 'Y' LIMIT 1000
>
>
> After playing with this for a while and getting results that didn't make
> sense, it seems the LIMIT is being pushed on b or something like it before
> the join is applied and not after the full resultset is computed.  I was
> digging around a little bit.  Is that expected behavior?
>
> Thanks,
> Abe
>



-- 
Thanks,
Maryann