Re: missing optimization - column <> column

2016-12-05 16:41 GMT+01:00 Stephen Frost <sfrost(at)snowman(dot)net>:

> Pavel,
>
> * Pavel Stehule (pavel(dot)stehule(at)gmail(dot)com) wrote:
> > 2016-12-05 16:23 GMT+01:00 Stephen Frost <sfrost(at)snowman(dot)net>:
> > > * Pavel Stehule (pavel(dot)stehule(at)gmail(dot)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.
>

ok - when the expression is tested on NULL, then a <> a should not be
reduced. But when there are not this test, then I can replace this
expression by false.

Regards

Pavel

>
> Thanks!
>
> Stephen
>