WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 4 years ago

#25458 closed defect (bug) (invalid)

Missing parenthesis in SQL syntax return wrong results

Reported by: andrejcremoznik Owned by:
Milestone: Priority: normal
Severity: major Version: 3.6.1
Component: Query Keywords:
Focuses: Cc:

Description

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.

Change History (2)

#1 @andrejcremoznik
4 years ago

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

#2 @nacin
4 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to invalid
  • Status changed from new to closed

There's no need for parentheses there.

Note: See TracTickets for help on using tickets.