Re: Simple query returns inconsistent results when using "=" operator

Hi Thomas,

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

Had you dumped your data in 4.1.9 and re-loaded in 5.0.x? If not, had 
you tried to run mysql_upgrade script?

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

Please, send the results of

SHOW CREATE TABLE contact_comments\G
SHOW TABLE STATUS LIKE 'contact_comments';
CHECK TABLE contact_comments FOR UPGRADE;

Best regards,
-- 
Valeriy Kravchuk, MySQL Principal Support Engineer
Sun Microsystems, Inc.
Kiev, Ukraine, www.sun.com/mysql

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