#1
  1. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96

    Very weird results


    Why do these two queries return different results:

    Code:
    SELECT pkey, headline
     FROM news
    WHERE type='news'
     AND featured_nelson=0
     AND approved=1
     AND visible=1
     AND display_on_nelson_news_index=1
     ORDER BY posted_date DESC LIMIT 5
    and

    Code:
    SELECT pkey
    FROM news
     WHERE type='news' 
    AND featured_nelson=0 
    AND approved=1 
    AND visible=1 
    AND display_on_nelson_news_index=1
     ORDER BY posted_date DESC LIMIT 5
    The only difference is the first query is also pulling the headline, but the ordering and limit is exactly the same. The second field shouldn't have anything to do with the returned records. Should it?

    The first query returns this:

    1766...
    1764...
    1763...
    1762...
    1765...

    The second query returns this:

    1766...
    1761...
    1765...
    1763...
    1764...

    In my code, I don't need the headline field so I wasn't originally pulling it until I noticed an error on the. Only after adding the headline field did it fix the error. Seriously, WTF?
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,375
    Rep Power
    391
    What happens is that in your table, the fifth and the sixth record (ordered by posted_date descending) have the same posted_date value and the DBMS can return any of those two records.

    Probably an index is used in one of the queries but not the other.
  4. #3
  5. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96
    Well, dang. Is there no guarantee for the same results for running the same query twice then?
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Not until you actually specify a definite order. Use the primary key as a second ordering column.

    Comments on this post

    • sr agrees : Good comment!
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo