Thread: Parser bug results in ambiguous errors/behaviour

Hi,

A bug/short coming in the parser leads to some pretty ambiguous errors
and/or foot shooting. Consider the following:

template1=# create table foo(i int, b bool, t text);
CREATE TABLE
template1=# insert into foo values(1, 'f', 'foo');
INSERT 0 1
template1=# update foo set i=2,b='t' and t='bar' where i=1;
UPDATE 1

Now there's an error in the SQL: "b='t' AND t='bar'". We don't detect
this. Result:

template1=# select * from foo;i | b |  t
---+---+-----2 | f | foo
(1 row)


It gets more interesting:

template1=# update foo set b='t', i=2 and t='bar' where i=1;
ERROR:  argument of AND must be type boolean, not type integer

Now, obviously the SQL is invalid but I think we should detect it. This
happens in HEAD, 8.0 and 7.2 -- and I presume other releases.

Comments?

Thanks,

Gavin

Gavin Sherry wrote:

> A bug/short coming in the parser leads to some pretty ambiguous errors
> and/or foot shooting. Consider the following:
>
> template1=# create table foo(i int, b bool, t text);
> CREATE TABLE
> template1=# insert into foo values(1, 'f', 'foo');
> INSERT 0 1
> template1=# update foo set i=2,b='t' and t='bar' where i=1;
> UPDATE 1

Read it as:
update foo set=2, b=('t' and t='bar') where i=1;

This works because: 't' can be translated to boolean true, t='bar' to 
boolean false, (true and false) becomes false, of course.

> template1=# select * from foo;
> i | b |  t
> ---+---+-----
> 2 | f | foo
> (1 row)

Seems to be the correct result, at least if the syntax without parenthesis 
is allowed by the SQL spec.

> It gets more interesting:
>
> template1=# update foo set b='t', i=2 and t='bar' where i=1;
> ERROR:  argument of AND must be type boolean, not type integer

update foo set b='t', i=(2 and t='bar') where i=1;

This is supposed to fail. There is no (at least implicit) cast from integer 
to boolean. So 2 cannot be converted to a boolean value and the boolean AND 
operator fails.

It comes down to the question if the query is valid syntax in the first 
place. The answers PostgreSQL gives are correct nevertheless.

Best Regards,
Michael Paesold 

Gavin Sherry <swm@linuxworld.com.au> writes:
> template1=# update foo set i=2,b='t' and t='bar' where i=1;
> UPDATE 1

This is perfectly legal SQL.  If it doesn't do what you intended,
well, too bad.  We're not going to "fix" it.
        regards, tom lane

On Thu, 22 Sep 2005, Tom Lane wrote:

> Gavin Sherry <swm@linuxworld.com.au> writes:
> > template1=# update foo set i=2,b='t' and t='bar' where i=1;
> > UPDATE 1
>
> This is perfectly legal SQL.  If it doesn't do what you intended,
> well, too bad.  We're not going to "fix" it.

Hmmm. Okay. It wasn't that I intended it to do anything -- it just looked
incorrect.

Thanks,

Gavin