Simple query returns inconsistent results when using "=" operator

Scenario: Moved from MySQL 4.1.9-0 (Fedora Core 2) to MySQL 5.0.45-7.el5
(CentOS 5.2)
 
Noticed that basic queries yield inconsistent results:

Query 1:

mysql> select contact_id,system_message from contact_comments where
contact_id=168676;                        
+------------+----------------+
| contact_id | system_message |
+------------+----------------+
|     168676 | no             | 
|     168676 | yes            | 
|     168676 | no             | 
|     168676 | yes            | 
|     168676 | no             | 
|     168676 | yes            | 
+------------+----------------+
6 rows in set (0.00 sec)

Query 2:
mysql> select contact_id,system_message from contact_comments where
contact_id=168676 and system_message='no';
Empty set (0.00 sec)

I would expect that this should have resulted in 3 rows.

Query 3:
mysql> select contact_id,system_message from contact_comments where
contact_id=168676 and system_message='yes';
Empty set (0.00 sec)

Should have been 3 rows as well.

Query 4:
mysql> select contact_id,system_message from contact_comments where
contact_id=168676 and system_message<>'no';
+------------+----------------+
| contact_id | system_message |
+------------+----------------+
|     168676 | yes            | 
|     168676 | yes            | 
|     168676 | yes            | 
+------------+----------------+
3 rows in set (0.00 sec)

Now it gets interesting.

Query 5:
mysql> select contact_id,system_message from contact_comments where
contact_id=168676 and system_message<>'yes';
+------------+----------------+
| contact_id | system_message |
+------------+----------------+
|     168676 | no             | 
|     168676 | no             | 
|     168676 | no             | 
+------------+----------------+
3 rows in set (0.00 sec)

Query 6:
mysql> select contact_id,system_message from contact_comments where
contact_id like 168676 and system_message='no';
+------------+----------------+
| contact_id | system_message |
+------------+----------------+
|     168676 | no             | 
|     168676 | no             | 
|     168676 | no             | 
+------------+----------------+
3 rows in set (0.00 sec)

Replaced "=" with "like": That worked - but it found all those records
with the "=" operator in query 1 above.

I'm lost - what am I missing here?

For your reference - this is what the table looks like:

mysql> describe contact_comments;
+-------------------+------------------+------+-----+---------------------+-------+
| Field             | Type             | Null | Key | Default
| Extra |
+-------------------+------------------+------+-----+---------------------+-------+
| contact_id        | int(11)          | NO   | PRI | 0
|       | 
| username          | varchar(25)      | NO   | PRI | 0
|       | 
| comment           | text             | NO   | PRI |
|       | 
| system_message    | enum('yes','no') | NO   | MUL | no
|       | 
| time              | datetime         | NO   | PRI | 0000-00-00
00:00:00 |       | 
| talked_to_contact | enum('yes','no') | NO   |     | no
|       | 
| status            | int(3)           | YES  |     | NULL
|       | 
| hidden            | enum('yes','no') | NO   |     | no
|       | 
+-------------------+------------------+------+-----+---------------------+-------+
8 rows in set (0.00 sec)


Thanks for checking.
Thomas



-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]