Re: WIP Join Removal

On Tue, 2008-09-02 at 14:20 +0300, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > select a.col2
> > from a left outer join b on a.col1 = b.col1
> > where b.col2 = 1;
> >
> > is logically equivalent to
> >
> > select a.col2
> > from a;
>
> No, it's not:
>
> postgres=# CREATE TABLE a (col1 int4, col2 int4);
> CREATE TABLE
> postgres=# CREATE TABLE b (col1 int4, col2 int4);
> CREATE TABLE
> postgres=# INSERT INTO a VALUES (1,1);
> INSERT 0 1
> postgres=# select a.col2 from a;
>   col2
> ------
>      1
> (1 row)
>
> postgres=# select a.col2 from a left outer join b on a.col1 = b.col1
> where b.col2 = 1;
>   col2
> ------
> (0 rows)

You raise an interesting and important point that shows an error of
mine. Notice that

 select a.col2 from a left outer join b on a.col1 = b.col1
 *and* b.col2 = 1;

can be re-written as

 select a.col2 from a;

whereas

 select a.col2 from a left outer join b on a.col1 = b.col1
 where b.col2 = 1;

cannot, as you show.

It seems I wrote my original tests using "and" instead of "where" and
hadn't noticed the distinction. Thanks for helping me catch that error.

I will put back the code that looks for an empty filter condition on the
checkrel. That day was not wasted after all.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support