Missing parenthesis in SQL syntax return wrong results

[andrejcremoznik]

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.

[andrejcremoznik]

Actually stratch that, MySQL is still returning inconsistent results. The parenthesis should probably still be there though.

[nacin]

There's no need for parentheses there.