Thread: Bug #869: varchar 's comparision

trainee (trainee12@yeah.net) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
varchar 's comparision

Long Description
SERVER:  PGSQL 7.31 + MANDRAKE LINUX 8.1
CLIENT:  VISIVAL FOXPRO 7.0 + PGSQLODBC 7.02

TABLE :    MYTABLE
       M_ID      VARCHAR(10)         M_DESC    VARCHAR(10)
    -----------------------------------------------------------
       ABC                           HELLO

I create a romate view in vfp, when I modify "m_desc" from "HELLO"
TO "GOOD" ,and update table, vfp trigger a error "UPDATE CONFLICT".

In odbc-log: (THIS SQL SYNTAX IS GENERATED BY VFP )
---------------------------------------------------------------
UPDATE MYTABLE SET M_DESC='GOOD' WHERE M_ID='ABC       ' AND M_DESC='HELLO     '

UPDATE 0 RECORDS
----------------------------------------------------------------
no record be updated, so VFP trigger a error "UPDATE CONFLICT"

But if you execute SQL  manually
---------------------------------------------------------
UPDATE MYTABLE SET M_DESC='GOOD' WHERE M_ID='ABC' AND M_DESC='HELLO'

UPDATE 1 RECORDS
---------------------------------------------------------

HOW CAN I DO?
THANKS


Sample Code


No file was uploaded with this report

On Thu, 9 Jan 2003 pgsql-bugs@postgresql.org wrote:

> trainee (trainee12@yeah.net) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> varchar 's comparision
>
> Long Description
> SERVER:  PGSQL 7.31 + MANDRAKE LINUX 8.1
> CLIENT:  VISIVAL FOXPRO 7.0 + PGSQLODBC 7.02
>
> TABLE :    MYTABLE
>        M_ID      VARCHAR(10)         M_DESC    VARCHAR(10)
>     -----------------------------------------------------------
>        ABC                           HELLO
>
> I create a romate view in vfp, when I modify "m_desc" from "HELLO"
> TO "GOOD" ,and update table, vfp trigger a error "UPDATE CONFLICT".
>
> In odbc-log: (THIS SQL SYNTAX IS GENERATED BY VFP )
> ---------------------------------------------------------------
> UPDATE MYTABLE SET M_DESC='GOOD' WHERE M_ID='ABC       ' AND M_DESC='HELLO     '

varchar() considers trailing spaces as significant, so 'ABC'!='ABC '
If it's going to generate values with trailing spaces, you might want to
use char() instead.

pgsql-bugs@postgresql.org writes:
>        M_ID      VARCHAR(10)         M_DESC    VARCHAR(10)
>     -----------------------------------------------------------
>        ABC                           HELLO

> UPDATE MYTABLE SET M_DESC='GOOD' WHERE M_ID='ABC       ' AND M_DESC='HELLO     '

> UPDATE 0 RECORDS

My guess is that the stored values do not have trailing spaces.
Trailing spaces are significant data in varchar fields.

            regards, tom lane