Обсуждение: Bug in psql - Postgresql 7.3.1?

On Fri, Oct 03, 2003 at 09:18:44 -0400, "John B. Scalia" <jscalia@cityblueprinting.com> wrote:
> All,
> 
>  
> 
> I'm not certain if what I'm trying to do is legal, but if I execute a
> statement like:
> 
>  
> 
> UPDATE my_table SET field1='new_value' AND SET field2='different_value'
> WHERE my_table_id = 'key';

It should be:
UPDATE my_table SET field1='new_value', field2='different_value'
WHERE my_table_id = 'key';

> in psql, it reports that it has successfully updated one record.

I am not sure why you aren't getting a syntax error.

> However, the record does not appear to be updated when I subsequently
> issue a SELECT for that record. I'll buy the fact that I may be using
> illegal syntax - I'm not SQL expert, but the update says it worked when
> in fact it did not. Should this have worked or should it have reported
> an error?

Shouldn't that be "UPDATE my_table SET field1 = 'new_value', field2 = 
'different_value' WHERE my_table_id = 'key';"?

Wei

On Fri, 3 Oct 2003, John B. Scalia wrote:

> All,
> 
>  
> 
> I'm not certain if what I'm trying to do is legal, but if I execute a
> statement like:
> 
>  
> 
> UPDATE my_table SET field1='new_value' AND SET field2='different_value'
> WHERE my_table_id = 'key';
> 
>  
> 
> in psql, it reports that it has successfully updated one record.
> However, the record does not appear to be updated when I subsequently
> issue a SELECT for that record. I'll buy the fact that I may be using
> illegal syntax - I'm not SQL expert, but the update says it worked when
> in fact it did not. Should this have worked or should it have reported
> an error?
> 
>  
> 
> For record, this is Postgresql 7.3.1 on Solaris 9. Problem originally
> seen during a php4 script, but has been duplicated on the psql command
> line.
> 
>  
> 
> TIA,
> 
> John
> 
> 

John,

>
> UPDATE my_table SET field1='new_value' AND SET field2='different_value'
> WHERE my_table_id = 'key';

Well, your SQL is bad:

UPDATE my_table SET field1='new_value, field2='different_value'
WHERE my_table_id = 'key';

> in psql, it reports that it has successfully updated one record.
> However, the record does not appear to be updated when I subsequently
> issue a SELECT for that record. I'll buy the fact that I may be using
> illegal syntax - I'm not SQL expert, but the update says it worked when
> in fact it did not. Should this have worked or should it have reported
> an error?

It's an error if it actually occurred that way.  Can you cut-and-paste your
actual PSQL session?

> For record, this is Postgresql 7.3.1 on Solaris 9. Problem originally
> seen during a php4 script, but has been duplicated on the psql command
> line.

BTW, there are known bugs in 7.3.1; you should upgrade to 7.3.4.

--
-Josh BerkusAglio Database SolutionsSan Francisco

John,

> Yeah, I figured out my SQL was bad and had switched to the comma
> separated version, instead. In my mind, the first form should have
> caused an error. I've attached a cut-and-pasted session from psql where
> I used this syntax on a test table. While edited for brevity and to
> obscure passwords, this is how the output appeared.

Here's your problem:

accounting=# update all_user set usr_current = True AND usr_location = 1002;
UPDATE 3

PostgreSQL interpreted the expression "True AND usr_location = 1002" as a
single, unitary, boolean expression.   AND is the boolean AND operator.
Since none of the users on your list had "usr_location = 1002", you got:

user_current = (True AND (usr_location = 1002))
user_current = (True AND False)
user_current = False

Since all 3 rows already had false, they did not appear to get updated, but in
fact they were.

Time to look up your order of operations!

--
-Josh BerkusAglio Database SolutionsSan Francisco

"John B. Scalia" <jscalia@cityblueprinting.com> writes:
> UPDATE my_table SET field1='new_value' AND SET field2='different_value'
> WHERE my_table_id = 'key';

The other responses have focused on your obvious syntax error, but I'm
assuming you didn't actually cut-and-paste that from your psql session.

> in psql, it reports that it has successfully updated one record.
> However, the record does not appear to be updated when I subsequently
> issue a SELECT for that record.

I'm wondering if you are checking from another transaction that doesn't
think the first one is committed yet.  Are you using BEGIN/END or
autocommit-off mode?
        regards, tom lane