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

On Sat, 2008-11-29 at 04:57 -0500, [email protected] wrote:
> Thomas,
> 
> It looks to me like it's behaving as though some portion of your  
> upgrade has introduced trailing spaces or non-printable characters. It  
> might be useful to whoever ends up looking at this, if you were to  
> report the behavior of your query using left and trim (or rtrim)  
> respectively.

Hi Carol,
I've imported the original database structure from the old server, but
then - at least for this particular table - deleted all entries and
started with new records. The contact_id field is populated via
auto_increment - thus IMO ruling out any kind of extra characters.
Strangely enough I get correct matches on "contact_id" when I only use
this field for the query (see Query 1 from my original post).
> 
> If one or both of those brings back your expected result set, again it  
> might be helpful to check the output data file to see if it was  
> introduced when outputting the data to the disk or on the latter input.
> 
I've followed Valeriy's advice and upgraded to the latest version -
5.1.30-community MySQL Community Server (GPL) - apparently the bug is
not related to the version I had.

I'll dump/load the DB on another server - to see if I can reproduce the
result.

> OTOH, if you just send them your files, the programmer will surely do  
> the same check. This might be most useful if you're in a terrible  
> hurry and are looking for a work-around.
> 
I could go further, and give the developer ssh access - this is not a
production server.

Thanks for your insights, I'll post any updates here.

Cheers,
Thomas

> -carol stone
> 
> 
> 
> Quoting Thomas Klettke <[email protected]>:
> 
> > 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)
> 
> 


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