UPDATE statement with syntax error doesn't raise a warning?

Mirko Pace <mirkop82@gmail.com> wrote:

I've ran an update statement like this (obviously wrong, I know!):

update my_table
set boolean_field = true AND
my_notes = 'something'
where id in
(select id from my_table order by random() limit 4000);

in my psql client and I had a "UPDATE 4000" result but, correctly, anything
was changed in my_table.

So... why pg didn't raise a warning about syntax issue?

Because there is no syntax error? "TRUE AND my_notes =
'something'" is a valid expression (and equivalent to
"my_notes = 'something'").

Tim

Mirko Pace wrote:

I've ran an update statement like this (obviously wrong, I know!):

update my_table
set boolean_field = true AND
my_notes = 'something'
where id in
(select id from my_table order by random() limit 4000);

in my psql client and I had a "UPDATE 4000" result but, correctly,
anything was changed in my_table.

So... why pg didn't raise a warning about syntax issue?

Thank you!

Mirko

P.S.
PostgreSQL 8.3.8 on i686-pc-linux-gnu, compiled by GCC gcc (Debian
4.3.2-1.1) 4.3.2
psql 8.3.8 (Ubuntu package)

are you sure its wrong?

maybe pg looked at it like:
boolean_field = (true AND my_notes = 'something')

-Andy

On Fri, 25 Sep 2009 17:05:13 +0200, Mirko Pace wrote about [GENERAL]
UPDATE statement with syntax error doesn't raise a warning?:

I've ran an update statement like this (obviously wrong, I know!):

Not so obvious.

update my_table
set boolean_field = true AND
my_notes = 'something'
where id in
(select id from my_table order by random() limit 4000);

in my psql client and I had a "UPDATE 4000" result but, correctly,
anything was changed in my_table.

I presume you meant "nothing" rather than "anything".

So... why pg didn't raise a warning about syntax issue?

If we add some redundant parentheses, the SET clause becomes:

SET boolean_field = (true AND my_notes = 'something')

As you can see, this is a valid Boolean expression. There is no syntax
error. It just doesn't mean what you wanted.
--
Regards,

Dave [RLU #314465]
=======================================================================
david.w.noon@ntlworld.com (David W Noon)
=======================================================================

---------- Forwarded message ----------
From: Mirko Pace <mirkop82@gmail.com>
Date: Mon, Sep 28, 2009 at 2:59 PM
Subject: Re: [GENERAL] UPDATE statement with syntax error doesn't raise a
warning?
To: David W Noon <dwnoon@ntlworld.com>

I presume you meant "nothing" rather than "anything".

sorry for my poor english :/

SET boolean_field = (true AND my_notes = 'something')

As you can see, this is a valid Boolean expression. There is no syntax
error. It just doesn't mean what you wanted.

uhm... I forgot to specify:
PostgreSQL 8.3.8 on i686-pc-linux-gnu, compiled by GCC gcc (Debian
4.3.2-1.1) 4.3.2
psql 8.3.8 (Ubuntu package)
_AND_
Mirko 0.01alpha

thank you all for being so patient with me.

Have a nice day!

Mirko