Re: missing optimization - column <> column

Pavel,

* Pavel Stehule (pavel.stehule@gmail.com) wrote:
> 2016-12-05 16:23 GMT+01:00 Stephen Frost <sfrost@snowman.net>:
> > * Pavel Stehule (pavel.stehule@gmail.com) wrote:
> > > I found some crazy queries in one customer application. These queries are
> > > stupid, but it was surprise for me so there are not some simple
> > optimization
> > >
> > > create table foo(a int);
> > > insert into foo select generate_series(1,100000);
> > > analyze foo;
> > > explain select * from foo where a <> a;
> > >
> > > It does full scan of foo, although it should be replaced by false in
> > > planner time.
> >
> > a <> a could go to NULL.  Obviously, that'll be false for such a simple
> > case, but it might not work out that way in a more complicated WHERE
> > clause.
> >
>
> it should be false everywhere

No, it's NULL, not false, if 'a' is NULL:

=# SELECT 1 WHERE (NULL <> NULL) IS NULL;?column?
----------       1
(1 row)

=*# SELECT 1 WHERE (FALSE) IS NULL;?column?
----------
(0 rows)

You can not make the assumption that 'a <> a' is always false.

Thanks!

Stephen