If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Strange mysql query problem

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 1
Strange mysql query problem

Hi, I was wondering if someone could help me with a strange mysql problem I am having.

I am seeing strange query behavior from one of my mysql servers. I ran the following two queries on three different servers all with the same data.

Code:
SELECT t1.Field1, t1.Field2, t1.Field3, 
(SELECT t2.Field1
  FROM thetable AS t2
  WHERE Field2=t1.Field2 AND Field3=t1.Field3 AND t2.Field1>t1.Field1
  ORDER BY Field1
  LIMIT 1) AS MinimumResult
FROM thetable AS t1
WHERE t1.UserID=5;

SELECT t1.Field1, t1.Field2, t1.Field3, 
(SELECT min(t2.Field1)
  FROM thetable AS t2
  WHERE Field2=t1.Field2 AND Field3=t1.Field3 AND t2.Field1>t1.Field1) AS MinimumResult
FROM thetable AS t1
WHERE t1.UserID=5;
On two of the three servers both queries return the same result. On the third, I get two different results for the MinimumResult field. The first query, returns a small number. The second query returns what I would expect and what both queries return on the other servers.

I am not sure how to respond to this? Is this a server config problem or expected behavior?

Thanks in advance for any help you can provide.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 860
If you are 100% convinced your data is identical then have a look at the COLLATION used on each of the tables. Run a SHOW CREATE TABLE on each of the servers for the same table. I suspect that one server has a different COLLATION which will sort your data differently, hence, your different result.
__________________
Ronan Cashell
Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
http://www.it-iss.com
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On