Parser bug results in ambiguous errors/behaviour

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