huh? select * from table where integer = '1a' ....

Why does a select from table with an integer field return a row if I compare
the integer to an alpha character?

Here's the info ( a straight copy and paste), then compare my two select
statements:

mysql> desc product;
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| prd_id | int(11) | | PRI | 0 | |
| prd_name | varchar(30) | | | | |
| prd_short_desc | varchar(250) | YES | | NULL | |
| prd_long_desc1 | text | YES | | NULL | |
| prd_long_desc2 | text | YES | | NULL | |
| prd_long_desc3 | text | YES | | NULL | |
| prd_thumb | varchar(250) | YES | | NULL | |
| prd_full_image | varchar(250) | YES | | NULL | |
| prd_price | decimal(8,2) | YES | | NULL | |
| prd_custom1 | varchar(10) | YES | | NULL | |
| prd_custom2 | varchar(10) | YES | | NULL | |
| prd_start_date | date | YES | | NULL | |
| prd_end_date | date | YES | | NULL | |
| prd_instock | int(11) | | | 0 | |
| prd_search_words | text | YES | | NULL | |
+------------------+--------------+------+-----+---------+-------+
15 rows in set (0.00 sec)

mysql> select prd_id from product where prd_id = 33;
+--------+
| prd_id |
+--------+
| 33 |
+--------+
1 row in set (0.01 sec)

mysql> select prd_id from product where prd_id = '33a';
+--------+
| prd_id |
+--------+
| 33 |
+--------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 3.23.32 |
+-----------+
1 row in set (0.00 sec)

mysql>


notice that
select prd_id from product where prd_id = 33;
and
select prd_id from product where prd_id = '33a';
both reutrun a row, the same row actually!

I noticed that this returns nothing:
select prd_id from product where prd_id = 'a33';

So 'What Gives?'

I can't seem to track down any info about this so any insight would be
great.
(please notice that my version is 3.23.32)

Thanks in advandce
JohnL

re: huh? select * from table where integer = '1a' ....

"John" <dsklfjhsdk> wrote in message
news:vtrmksg4g4us29@corp.supernews.com...[color=blue]
> Why does a select from table with an integer field return a row if I[/color]
compare[color=blue]
> the integer to an alpha character?
>
> notice that
> select prd_id from product where prd_id = 33;
> and
> select prd_id from product where prd_id = '33a';
> both reutrun a row, the same row actually!
>
> I noticed that this returns nothing:
> select prd_id from product where prd_id = 'a33';
>
> So 'What Gives?'
>
> I can't seem to track down any info about this so any insight would be
> great.
> (please notice that my version is 3.23.32)
>
> Thanks in advandce
> JohnL
>[/color]


I would imagine it's a bug/feature of mysql that will truncate a number at
a character on input, in which case:

1) '33' = 33
2) '33a' = 33
3) 'a33' = nothing

try selecting something like this: select prd_id from product where prd_id =
'33a4';

and see what that gives you. Should just return 33 by my estimation.

re: huh? select * from table where integer = '1a' ....

"John" <dsklfjhsdk> wrote in message
news:vtrmksg4g4us29@corp.supernews.com...[color=blue]
> Why does a select from table with an integer field return a row if I[/color]
compare[color=blue]
> the integer to an alpha character?
>
> notice that
> select prd_id from product where prd_id = 33;
> and
> select prd_id from product where prd_id = '33a';
> both reutrun a row, the same row actually!
>
> I noticed that this returns nothing:
> select prd_id from product where prd_id = 'a33';
>
> So 'What Gives?'
>
> I can't seem to track down any info about this so any insight would be
> great.
> (please notice that my version is 3.23.32)
>
> Thanks in advandce
> JohnL
>[/color]


I would imagine it's a bug/feature of mysql that will truncate a number at
a character on input, in which case:

1) '33' = 33
2) '33a' = 33
3) 'a33' = nothing

try selecting something like this: select prd_id from product where prd_id =
'33a4';

and see what that gives you. Should just return 33 by my estimation.

re: huh? select * from table where integer = '1a' ....

"SwissCheese" <SwissCheese@cfl.rr.com> wrote in message
news:ZMWDb.85326$b01.1847707@twister.tampabay.rr.c om...[color=blue]
> "John" <dsklfjhsdk> wrote in message
> news:vtrmksg4g4us29@corp.supernews.com...[color=green]
> > Why does a select from table with an integer field return a row if I[/color]
> compare[color=green]
> > the integer to an alpha character?
> >
> > notice that
> > select prd_id from product where prd_id = 33;
> > and
> > select prd_id from product where prd_id = '33a';
> > both reutrun a row, the same row actually!
> >
> > I noticed that this returns nothing:
> > select prd_id from product where prd_id = 'a33';
> >
> > So 'What Gives?'
> >
> > I can't seem to track down any info about this so any insight would be
> > great.
> > (please notice that my version is 3.23.32)
> >
> > Thanks in advandce
> > JohnL
> >[/color]
>
>
> I would imagine it's a bug/feature of mysql that will truncate a number[/color]
at[color=blue]
> a character on input, in which case:
>
> 1) '33' = 33
> 2) '33a' = 33
> 3) 'a33' = nothing
>
> try selecting something like this: select prd_id from product where prd_id[/color]
=[color=blue]
> '33a4';
>
> and see what that gives you. Should just return 33 by my estimation.
>[/color]
That's partially correct. It's actually a feature of SQL in general. If SQL
is expecting a number and gets a letter, it drops everything after the
letter. Therefore:
33a = 33
a33 = nothing
3a3 = 3

re: huh? select * from table where integer = '1a' ....

Thanks for the replies.

sure enough in mysql '3a3' = 3

but in oracle it always fails on a 'non-number' character. (Oracle 8i)
Here's the output

SQL> select id from id_table where id = '6a7';
select plc_id from coop_placement where plc_id = '6a7'
*
ERROR at line 1:
ORA-01722: invalid number


I think I prefer getting an error in queries like this. Most of the time I
like it that mysql will take nearly any query and give it a shot, but in my
apps I'd rather it crapped out.

Thanks
JohnL


"Ryan Stewart" wrote in message news:fbCdncmaB7rfeHyiRTvUqQ@texas.net...[color=blue]
> "SwissCheese" wrote in message
> news:ZMWDb.85326$b01.1847707@twister.tampabay.rr.c om...[color=green]
> > "John" <dsklfjhsdk> wrote in message
> > news:vtrmksg4g4us29@corp.supernews.com...[color=darkred]
> > > Why does a select from table with an integer field return a row if I[/color]
> > compare[color=darkred]
> > > the integer to an alpha character?
> > >
> > > notice that
> > > select prd_id from product where prd_id = 33;
> > > and
> > > select prd_id from product where prd_id = '33a';
> > > both reutrun a row, the same row actually!
> > >
> > > I noticed that this returns nothing:
> > > select prd_id from product where prd_id = 'a33';
> > >
> > > So 'What Gives?'
> > >
> > > I can't seem to track down any info about this so any insight would be
> > > great.
> > > (please notice that my version is 3.23.32)
> > >
> > > Thanks in advandce
> > > JohnL
> > >[/color]
> >
> >
> > I would imagine it's a bug/feature of mysql that will truncate a number[/color]
> at[color=green]
> > a character on input, in which case:
> >
> > 1) '33' = 33
> > 2) '33a' = 33
> > 3) 'a33' = nothing
> >
> > try selecting something like this: select prd_id from product where[/color][/color]
prd_id[color=blue]
> =[color=green]
> > '33a4';
> >
> > and see what that gives you. Should just return 33 by my estimation.
> >[/color]
> That's partially correct. It's actually a feature of SQL in general. If[/color]
SQL[color=blue]
> is expecting a number and gets a letter, it drops everything after the
> letter. Therefore:
> 33a = 33
> a33 = nothing
> 3a3 = 3
>
>[/color]

re: huh? select * from table where integer = '1a' ....

"John" <dsklfjhsdk> wrote in message
news:vu4acn3nj225d7@corp.supernews.com...[color=blue]
> Thanks for the replies.
>
> sure enough in mysql '3a3' = 3
>
> but in oracle it always fails on a 'non-number' character. (Oracle 8i)
> Here's the output
>
> SQL> select id from id_table where id = '6a7';
> select plc_id from coop_placement where plc_id = '6a7'
> *
> ERROR at line 1:
> ORA-01722: invalid number
>
>
> I think I prefer getting an error in queries like this. Most of the time[/color]
I[color=blue]
> like it that mysql will take nearly any query and give it a shot, but in[/color]
my[color=blue]
> apps I'd rather it crapped out.
>
> Thanks
> JohnL
>[/color]

I think you might need to start validating some variables before sending
your queries to the database...

re: huh? select * from table where integer = '1a' ....

"SwissCheese" <SwissCheese@cfl.rr.com> wrote in message
news:ZMWDb.85326$b01.1847707@twister.tampabay.rr.c om...[color=blue]
> "John" <dsklfjhsdk> wrote in message
> news:vtrmksg4g4us29@corp.supernews.com...[color=green]
> > Why does a select from table with an integer field return a row if I[/color]
> compare[color=green]
> > the integer to an alpha character?
> >
> > notice that
> > select prd_id from product where prd_id = 33;
> > and
> > select prd_id from product where prd_id = '33a';
> > both reutrun a row, the same row actually!
> >
> > I noticed that this returns nothing:
> > select prd_id from product where prd_id = 'a33';
> >
> > So 'What Gives?'
> >
> > I can't seem to track down any info about this so any insight would be
> > great.
> > (please notice that my version is 3.23.32)
> >
> > Thanks in advandce
> > JohnL
> >[/color]
>
>
> I would imagine it's a bug/feature of mysql that will truncate a number[/color]
at[color=blue]
> a character on input, in which case:
>
> 1) '33' = 33
> 2) '33a' = 33
> 3) 'a33' = nothing
>
> try selecting something like this: select prd_id from product where prd_id[/color]
=[color=blue]
> '33a4';
>
> and see what that gives you. Should just return 33 by my estimation.
>[/color]
That's partially correct. It's actually a feature of SQL in general. If SQL
is expecting a number and gets a letter, it drops everything after the
letter. Therefore:
33a = 33
a33 = nothing
3a3 = 3

re: huh? select * from table where integer = '1a' ....

Thanks for the replies.

sure enough in mysql '3a3' = 3

but in oracle it always fails on a 'non-number' character. (Oracle 8i)
Here's the output

SQL> select id from id_table where id = '6a7';
select plc_id from coop_placement where plc_id = '6a7'
*
ERROR at line 1:
ORA-01722: invalid number


I think I prefer getting an error in queries like this. Most of the time I
like it that mysql will take nearly any query and give it a shot, but in my
apps I'd rather it crapped out.

Thanks
JohnL


"Ryan Stewart" wrote in message news:fbCdncmaB7rfeHyiRTvUqQ@texas.net...[color=blue]
> "SwissCheese" wrote in message
> news:ZMWDb.85326$b01.1847707@twister.tampabay.rr.c om...[color=green]
> > "John" <dsklfjhsdk> wrote in message
> > news:vtrmksg4g4us29@corp.supernews.com...[color=darkred]
> > > Why does a select from table with an integer field return a row if I[/color]
> > compare[color=darkred]
> > > the integer to an alpha character?
> > >
> > > notice that
> > > select prd_id from product where prd_id = 33;
> > > and
> > > select prd_id from product where prd_id = '33a';
> > > both reutrun a row, the same row actually!
> > >
> > > I noticed that this returns nothing:
> > > select prd_id from product where prd_id = 'a33';
> > >
> > > So 'What Gives?'
> > >
> > > I can't seem to track down any info about this so any insight would be
> > > great.
> > > (please notice that my version is 3.23.32)
> > >
> > > Thanks in advandce
> > > JohnL
> > >[/color]
> >
> >
> > I would imagine it's a bug/feature of mysql that will truncate a number[/color]
> at[color=green]
> > a character on input, in which case:
> >
> > 1) '33' = 33
> > 2) '33a' = 33
> > 3) 'a33' = nothing
> >
> > try selecting something like this: select prd_id from product where[/color][/color]
prd_id[color=blue]
> =[color=green]
> > '33a4';
> >
> > and see what that gives you. Should just return 33 by my estimation.
> >[/color]
> That's partially correct. It's actually a feature of SQL in general. If[/color]
SQL[color=blue]
> is expecting a number and gets a letter, it drops everything after the
> letter. Therefore:
> 33a = 33
> a33 = nothing
> 3a3 = 3
>
>[/color]

re: huh? select * from table where integer = '1a' ....

"John" <dsklfjhsdk> wrote in message
news:vu4acn3nj225d7@corp.supernews.com...[color=blue]
> Thanks for the replies.
>
> sure enough in mysql '3a3' = 3
>
> but in oracle it always fails on a 'non-number' character. (Oracle 8i)
> Here's the output
>
> SQL> select id from id_table where id = '6a7';
> select plc_id from coop_placement where plc_id = '6a7'
> *
> ERROR at line 1:
> ORA-01722: invalid number
>
>
> I think I prefer getting an error in queries like this. Most of the time[/color]
I[color=blue]
> like it that mysql will take nearly any query and give it a shot, but in[/color]
my[color=blue]
> apps I'd rather it crapped out.
>
> Thanks
> JohnL
>[/color]

I think you might need to start validating some variables before sending
your queries to the database...