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