I had a very complicated problem, but i narrowed it down to this, First, let me give you some test data:
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`value` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
INSERT INTO test (value) VALUES
SELECT * FROM test WHERE value = 1;
The reason you get the third row is implicit datatype conversion performed by MySQL. Your query has a predicate (condition) in the WHERE clause
WHERE value = 1
On the right side of the equality comparison operator (the equal sign), we have a numeric literal. On the left side, we have a column that is datatype TEXT.
It's not possible for MySQL to do a comparison of those two different datatypes.
So, MySQL converts one side or the other to a type that is compatible, so a comparison can be performed. In this case, MySQL is converting the value from the column to be numeric, so it compare to the numeric literal.
As a demonstration of what that looks like, we can add a zero (forcing MySQL to do a conversion), and exhibit the results in a SELECT.
SELECT t.value, t.value + 0 FROM test t t.value t.value + 0 ------- ----------- 1 1 1 1 1,2 1 3 3
It's documented in the MySQL Reference Manual somewhere, how MySQL does the conversion. At a risk of misstating what the manual says: MySQL reads the string character by character from left to right, until it encounters a character where it can no longer convert to numeric.
In the case if the string
'1,2', that happens to be the comma character. That's where MySQL stops. So the conversion returns a numeric value of 1. You would be right to point out that other databases would throw an error attempting to do a conversion of that string to numeric. But MySQL doesn't throw an error or warning.
Basically, the predicate in your query is equivalent to specifying:
WHERE value + 0 = 1
Which forces a conversion of the contents of the column
value to numeric, and then a comparison to the numeric literal.
That's why the third row is being returned.
To get a different result, consider comparing to a string literal
WHERE value = '1'