[wp-trac] [WordPress Trac] #25458: Missing parenthesis in SQL syntax return wrong results

[WordPress Trac]

#25458: Missing parenthesis in SQL syntax return wrong results
-----------------------------+-----------------------------
 Reporter:  andrejcremoznik  |      Owner:
     Type:  defect (bug)     |     Status:  new
 Priority:  normal           |  Milestone:  Awaiting Review
Component:  Query            |    Version:  3.6.1
 Severity:  major            |   Keywords:
-----------------------------+-----------------------------
 I'm using a custom WP_Query running the following SQL on a MySQL database:

 {{{
 SELECT SQL_CALC_FOUND_ROWS  xyz_posts.ID
 FROM xyz_posts
 INNER JOIN xyz_term_relationships
 ON (xyz_posts.ID = xyz_term_relationships.object_id)
 WHERE 1=1
 AND (xyz_term_relationships.term_taxonomy_id IN (2))
 AND xyz_posts.post_type = 'company'
 AND (xyz_posts.post_status = 'publish')
 GROUP BY xyz_posts.ID
 ORDER BY xyz_posts.menu_order DESC
 LIMIT 10,10
 }}}

 The line applying the post_type param is missing parenthesis, so the
 correct SQL should be like this:

 {{{
 SELECT SQL_CALC_FOUND_ROWS  xyz_posts.ID
 FROM xyz_posts
 INNER JOIN xyz_term_relationships
 ON (xyz_posts.ID = xyz_term_relationships.object_id)
 WHERE 1=1
 AND (xyz_term_relationships.term_taxonomy_id IN (2))
 AND (xyz_posts.post_type = 'company')
 AND (xyz_posts.post_status = 'publish')
 GROUP BY xyz_posts.ID
 ORDER BY xyz_posts.menu_order DESC
 LIMIT 10,10
 }}}

 Without the parenthesis by changing the LIMIT the query produces
 incosistent/unordered/random results.

 In my case I can reproduce by running with LIMIT 0,100, noting the last
 result, and then running with LIMIT 79,100 which will produce a different
 last result (80 records in the database).

 This happens on 3 tested MySQL versions: 5.1.58, 5.1.69, 5.5.32 and I'm
 assuming on everything in between as well.

 MariaDB, however, works fine.

--
Ticket URL: <http://core.trac.wordpress.org/ticket/25458>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software