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

[John]

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

[SwissCheese]

"John" <dsklfjhsdk> wrote in message
news:vt************@corp.supernews.com...

Why does a select from table with an integer field return a row if I compare 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

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.

[SwissCheese]

"John" <dsklfjhsdk> wrote in message
news:vt************@corp.supernews.com...

Why does a select from table with an integer field return a row if I compare 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

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.

[Ryan Stewart]

"SwissCheese" <Sw*********@cfl.rr.com> wrote in message
news:ZM*********************@twister.tampabay.rr.c om...

"John" <dsklfjhsdk> wrote in message
news:vt************@corp.supernews.com...

Why does a select from table with an integer field return a row if I compare

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

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.

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

[John]

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:fb********************@texas.net...

"SwissCheese" wrote in message
news:ZM*********************@twister.tampabay.rr.c om...

"John" <dsklfjhsdk> wrote in message
news:vt************@corp.supernews.com...

Why does a select from table with an integer field return a row if I compare

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

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.
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

[SwissCheese]

"John" <dsklfjhsdk> wrote in message
news:vu************@corp.supernews.com...

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

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

[Ryan Stewart]

"SwissCheese" <Sw*********@cfl.rr.com> wrote in message
news:ZM*********************@twister.tampabay.rr.c om...

"John" <dsklfjhsdk> wrote in message
news:vt************@corp.supernews.com...

Why does a select from table with an integer field return a row if I compare

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

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.

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

[John]

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:fb********************@texas.net...

"SwissCheese" wrote in message
news:ZM*********************@twister.tampabay.rr.c om...

"John" <dsklfjhsdk> wrote in message
news:vt************@corp.supernews.com...

Why does a select from table with an integer field return a row if I compare

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

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.
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

[SwissCheese]

"John" <dsklfjhsdk> wrote in message
news:vu************@corp.supernews.com...

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

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