Thread: where clause bug

When specifying criteria in a where claue such as
user_id = 'value' if a non-numeric is entered the
query returns back all rows.
user_id = user_id brings back every user_id instead of
error because user_id is a numeric field and it
shouldnt be able to accept chars.
-Kyle

__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year!  http://personal.mail.yahoo.com/

kyle writes:

> When specifying criteria in a where claue such as
> user_id = 'value' if a non-numeric is entered the
> query returns back all rows.
> user_id = user_id brings back every user_id instead of
> error because user_id is a numeric field and it
> shouldnt be able to accept chars.

Reproduceable test case please.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

On Fri, 8 Jun 2001, kyle wrote:

> When specifying criteria in a where claue such as
> user_id = 'value' if a non-numeric is entered the
> query returns back all rows.
> user_id = user_id brings back every user_id instead of
> error because user_id is a numeric field and it
> shouldnt be able to accept chars.

What version are you running?

Can you give a test case of the first case (user_id='value' for
non-numeric value) with schema and sample data?  When
I try something like that on current sources I get
"Bad numeric input format 'value'" for a numeric column.

The second looks okay (user_id=user_id) since the
value of the user_id column is going to be equal to itself
for any non NULL value in user_id. I don't know why you'd
expect that one to error.

On 11 Jun 2001 16:52:09 +0200, Peter Eisentraut wrote:
> kyle writes:
>
> > When specifying criteria in a where claue such as
> > user_id = 'value' if a non-numeric is entered the
> > query returns back all rows.

nml=# CREATE temp table tempo (a int);
nml=# INSERT INTO tempo values (1);
nml=# INSERT INTO tempo values (2);
nml=# INSERT INTO tempo values (3);
nml=# INSERT INTO tempo values (4);
nml=# SELECT * from tempo where a='a';
ERROR:  pg_atoi: error in "a": can't parse "a"

> > user_id = user_id brings back every user_id instead of

FIELD_N = FIELD_N is always true

cu
--
 Nabil Sayegh

Nabil Sayegh writes:

> > > When specifying criteria in a where claue such as
> > > user_id = 'value' if a non-numeric is entered the
> > > query returns back all rows.
>
> nml=# CREATE temp table tempo (a int);
> nml=# INSERT INTO tempo values (1);
> nml=# INSERT INTO tempo values (2);
> nml=# INSERT INTO tempo values (3);
> nml=# INSERT INTO tempo values (4);
> nml=# SELECT * from tempo where a='a';
> ERROR:  pg_atoi: error in "a": can't parse "a"
>
> > > user_id = user_id brings back every user_id instead of
>
> FIELD_N = FIELD_N is always true

I don't see an issue here.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter