Results 1 to 2 of 2
  1. #1
    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.

  2. #2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •