Thread: BUG #1905: When a column with the chkpass datatype is used in a where clause it returns invalid results

The following bug has been logged online:

Bug reference:      1905
Logged by:          Trevor Tingey
Email address:      ttingey@gmail.com
PostgreSQL version: 8.1 beta 2
Operating system:   Windows XP Home Edition SP 2
Description:        When a column with the chkpass datatype is used in a
where clause it returns invalid results
Details:

My schema is as follows:

CREATE TABLE account
(
  "AccountId" int4 NOT NULL DEFAULT
nextval('public."account_AccountId_seq"'::text),
  "AccountName" varchar(255) NOT NULL,
  "Password" chkpass,
  "Description" varchar(255),
  CONSTRAINT account_pkey PRIMARY KEY ("AccountId"),
  CONSTRAINT "account_Username_key" UNIQUE ("AccountName")
)
WITHOUT OIDS;


I ran the following queries and got unexpected results:

UPDATE Account
SET "Password" = 'password1'
WHERE "AccountName" = 'Trevor'

SELECT *
FROM Account
WHERE "AccountName" = 'Trevor'
AND "Password" = 'password2'

(I ran them individually)

The Select query unexpectedly returned a result. I also  added another digit
on the end of 'password2' i.e. 'password21' and that returned the same
result. I also changed it to 'password3' etc. and got the same result and
the same with 'password'. When I changed it to 'passwor1' it did not return
a result.

Running SELECT * FROM Account returns two rows, one with the accountname of
'administrator' and the other 'Trevor'.

"Trevor Tingey" <ttingey@gmail.com> writes:
> I ran the following queries and got unexpected results:

> UPDATE Account
> SET "Password" = 'password1'
> WHERE "AccountName" = 'Trevor'

> SELECT *
> FROM Account
> WHERE "AccountName" = 'Trevor'
> AND "Password" = 'password2'

> (I ran them individually)

> The Select query unexpectedly returned a result.

This is not a bug --- chkpass limits passwords to 8 significant
characters, so the above two values are in fact equal so far as
the datatype is concerned.

(The documentation probably ought to point that out...)

            regards, tom lane