Postgres Pro
Downloads
Home   >   mailing lists

Thread: boolean bugs

boolean bugs

From
"Robert B. Easter"
Date:
CREATE TABLE nulltest (nullfield);
INSERT INTO nulltest VALUES (null);

pgcvs=# select (nullfield = 'willbenull') is null from nulltest;
 ?column?
----------
 t
(1 row)

pgcvs=# select (nullfield = 'willbenull') is true from nulltest;
 ?column?
----------

(1 row)

pgcvs=# select (nullfield = 'willbenull') is false from nulltest;
 ?column?
----------

(1 row)

The IS operator is supposed to return only TRUE or FALSE, never NULL.  See
ISO/IEC 9075-2:1999  6.30 <boolean value expression>

pgcvs=# select (nullfield = 'willbenull') is (false is false) from nulltest;
ERROR:  parser: parse error at or near "("

The IS operator has a problem if right side is in parenthesis.

pgcvs=# select (nullfield = 'willbenull') and (false is false) from nulltest;
 ?column?
----------

(1 row)

AND and OR are ok with the paren, why not IS?


The UNKNOWN literal is not understood.  It should be synonymous with NULL:

pgcvs=# select TRUE IS UNKNOWN;
ERROR:  parser: parse error at or near "unknown"
pgcvs=#

This should be the same as:
pgcvs=# select TRUE IS NULL;
 ?column?
----------
 f
(1 row)

See ISO/IEC 9075-2:1999 5.3 <literal>

pgcvs=# select true is null;
 ?column?
----------
 f
(1 row)

pgcvs=# select null is true;
 ?column?
----------

(1 row)

This is strange.  Just reversing the order changes the result.

I'm using the cvs version.  I think 7.0.3 has all this (wrong?) behavior too.

--
-------- Robert B. Easter  reaster@comptechnews.com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------

Re: boolean bugs

From
Tom Lane
Date:
"Robert B. Easter" <reaster@comptechnews.com> writes:
> The IS operator is supposed to return only TRUE or FALSE, never NULL.  See
> ISO/IEC 9075-2:1999  6.30 <boolean value expression>

Yeah, we do not implement IS TRUE, IS FALSE, etc per spec.  IS [NOT] NULL
is the only one of the group that works per-spec; the others all
erroneously produce NULL for null input, and IS UNKNOWN isn't there at all.

I've had that on my to-do list for awhile, but it's pretty low priority.


> pgcvs=# select (nullfield = 'willbenull') is (false is false) from nulltest;
> ERROR:  parser: parse error at or near "("

> The IS operator has a problem if right side is in parenthesis.

I'd be interested to know how you derive that expression from the spec.
By my reading of the grammar, IS is supposed to be followed by one or
two literal keywords, not an expression.

            regards, tom lane

Re: boolean bugs

From
"Robert B. Easter"
Date:
On Saturday 06 January 2001 17:56, Tom Lane wrote:
> "Robert B. Easter" <reaster@comptechnews.com> writes:
> > The IS operator is supposed to return only TRUE or FALSE, never NULL.
> > See ISO/IEC 9075-2:1999  6.30 <boolean value expression>
>
> Yeah, we do not implement IS TRUE, IS FALSE, etc per spec.  IS [NOT] NULL
> is the only one of the group that works per-spec; the others all
> erroneously produce NULL for null input, and IS UNKNOWN isn't there at all.
>
> I've had that on my to-do list for awhile, but it's pretty low priority.
>
> > pgcvs=# select (nullfield = 'willbenull') is (false is false) from
> > nulltest; ERROR:  parser: parse error at or near "("
> >
> > The IS operator has a problem if right side is in parenthesis.
>
> I'd be interested to know how you derive that expression from the spec.
> By my reading of the grammar, IS is supposed to be followed by one or
> two literal keywords, not an expression.
>
>             regards, tom lane

         <boolean test> ::=
              <boolean primary> [ IS [ NOT ] <truth value> ]

         <truth value> ::=
                TRUE
              | FALSE
              | UNKNOWN


You're right about the spec.  I guess there is some good reason not to allow
IS to take expressions on the right like AND and OR, even though I feel like
IS is just like AND and OR as an op that takes two boolean args and returns a
boolean (true/false only).


--
-------- Robert B. Easter  reaster@comptechnews.com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------