Results 1 to 2 of 2
Thread: Strange mysql query problem
12-17-12, 23:38 #1Registered User
- Join Date
- Dec 2012
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.
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;
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.
12-20-12, 07:10 #2Registered User
- Join Date
- Sep 2009
- San Sebastian, Spain
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)