Mysql Converts Varchar To Int 0 In Select

[samshel]

Hi All,

 

I am having a weird problem with a simple select with joins.

 

Example Table Structure:

 

id INT(11) - Primary Key

another_id INT(11) -

some_text VARCHAR (10)

 

I am firing a SELECT query with WHERE clause

 

SELECT * FROM table_name WHERE another_id = 'S123';

 

This returns all records from table where another_id = 0. another_id is INT and it converts the S123 to integer which is 0.

 

Shouldn't MySQL return 0 records?

 

this is just an example. Actual implementation has multiple joins.

 

I know that i should be validating the input and convert it into integer. I will be doing that.

 

However what baffles me is why MySQL behave in this way?

 

Why does it treat a varchar input as 0 when comparing to an INT field?

 

Is this normal?

 

Thanks

Sam

[Pikachu2000]

Perhaps you should post the actual query and structures of the tables involved.

[PFMaBiSmAd]

What value were you expecting the string 'S123', which starts with a non-numerical character, to be converted to for comparison with an integer field, 0.2?

 

If you are putting a value like that into a query statement in a place where an integer value is expected, it seems like you are not validating your data properly before using it in a query. You shouldn't even have single-quotes around that value inside the query (mysql convents a single-quoted string to a floating point value when used in a numerical comparison.)

[samshel]

Here it is.

 

 

SELECT

voos.order_id, po_number,

voos.created_datetime,

vendors.vendor_name, users.user_name,

products.sku, oos.message

FROM

voos

INNER JOIN vendors ON voos.vendor_id = vendors.vendor_id

INNER JOIN products ON voos.order_item_id = products.order_item_id

LEFT JOIN users ON voos.user_id = users.user_id

LEFT JOIN oos ON voos.order_item_id = oos.order_item_id AND voos.vendor_id = oos.vendor_id

WHERE voos.po_number = 'S5008044';

 

 

CREATE TABLE `voos` (

`id` int(11) NOT NULL auto_increment,

`order_id` int(11) NOT NULL default '0',

`order_item_id` int(11) NOT NULL default '0',

`po_number` int(11) NOT NULL default '0',

`vendor_id` int(11) NOT NULL default '0',

`reason` varchar(255) default NULL,

`created_datetime` datetime NOT NULL default '0000-00-00 00:00:00',

`last_modified` datetime default NULL,

`user_id` int(11) default NULL,

PRIMARY KEY (`id`),

KEY `order_id` (`order_id`),

KEY `order_item_id` (`order_item_id`),

KEY `vendor_id` (`vendor_id`),

KEY `created_datetime` (`created_datetime`),

KEY `last_modified` (`last_modified`)

) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

 

the main issue is po_number is INT(11) and when i fire a query on the table with WHERE po_number = 'ALPHANUMERIC' it returns all rows with po_number = 0.

[samshel]

@PFMaBiSmAd

 

Yes. I understand. i am already working on validating the data before i put it in the query.

 

I just want to know if this MySQL behavior is expected.

 

As per your statement, MySQL will convert the variables inside quotes to float while comparing numeric values so that answers my question.

 

Thanks

[PFMaBiSmAd]

I just want to know if this MySQL behavior is expected.

 

Yes.