combining SelectQueryInterface::union and PagerDefault::$limit generates a broken SQL query

Created on 2 September 2012, almost 12 years ago
Updated 15 September 2023, 9 months ago

Details of my code are in this support request: http://drupal.org/node/1770602

To sum up the issue briefly: I have a query that I need to express using a UNION. I also need to use a pager to display the results properly. When I run my code, I see the pager, but I don't see the actual results.

When I turn on query logging in devel, I see that this is the query that gets generated and run:

SELECT n.nid AS my_id, n.title AS title, n.uid AS uid, n.changed AS changed, n.created AS created, u.name AS username, n.nid AS nid, n.type AS my_type, fdb.body_value AS body
FROM node n
 LEFT OUTER JOIN field_data_body fdb ON n.nid = fdb.entity_id
 INNER JOIN users u ON u.uid = n.uid
 WHERE (type = :db_condition_placeholder_0)
 LIMIT 20 OFFSET 0
UNION ALL
SELECT c.cid AS my_id, c.subject AS title, c.uid AS uid, c.changed AS changed, c.created AS created, c.name AS username, n.nid AS nid, n.type AS my_type, fcb.comment_body_value AS body
 FROM comment c
 INNER JOIN node n ON n.nid = c.nid
 LEFT OUTER JOIN field_data_comment_body fcb ON c.cid = fcb.entity_id
 INNER JOIN users u ON u.uid = c.uid

That query is wrong. In fact, when I tested the query against SQL, I see that it doesn't effectively limit the results: everything is returned! StackExchange explains that it's missing a second LIMIT 20 OFFSET 0, which would enable it to work properly:

SELECT n.nid AS my_id, n.title AS title, n.uid AS uid, n.changed AS changed, n.created AS created, u.name AS username, n.nid AS nid, n.type AS my_type, fdb.body_value AS body
FROM node n
 LEFT OUTER JOIN field_data_body fdb ON n.nid = fdb.entity_id
 INNER JOIN users u ON u.uid = n.uid
 WHERE (type = :db_condition_placeholder_0)
 LIMIT 20 OFFSET 0
UNION ALL
SELECT c.cid AS my_id, c.subject AS title, c.uid AS uid, c.changed AS changed, c.created AS created, c.name AS username, n.nid AS nid, n.type AS my_type, fcb.comment_body_value AS body
 FROM comment c
 INNER JOIN node n ON n.nid = c.nid
 LEFT OUTER JOIN field_data_comment_body fcb ON c.cid = fcb.entity_id
 INNER JOIN users u ON u.uid = c.uid
 LIMIT 20 OFFSET 0

(And, yes, testing that on my system, it does indeed return 20 results as required.)

Note that the same StackExchange discussion also says what to do with ORDER BY (which was giving me an error when I tried to add it). The correct final query, including "ORDER BY created DESC", would be:

(SELECT n.nid AS my_id, n.title AS title, n.uid AS uid, n.changed AS changed, n.created AS created, u.name AS username, n.nid AS nid, n.type AS my_type, fdb.body_value AS body
FROM node n
 LEFT OUTER JOIN field_data_body fdb ON n.nid = fdb.entity_id
 INNER JOIN users u ON u.uid = n.uid
 WHERE (type = :db_condition_placeholder_0)
 ORDER BY created DESC LIMIT 10 OFFSET 0)
UNION ALL
(SELECT c.cid AS my_id, c.subject AS title, c.uid AS uid, c.changed AS changed, c.created AS created, c.name AS username, n.nid AS nid, n.type AS my_type, fcb.comment_body_value AS body
 FROM comment c
 INNER JOIN node n ON n.nid = c.nid
 LEFT OUTER JOIN field_data_comment_body fcb ON c.cid = fcb.entity_id
 INNER JOIN users u ON u.uid = c.uid
 ORDER BY created DESC LIMIT 10 OFFSET 0);

In this case, the parentheses around the two UNIONed SELECT statements are necessary, or the query doesn't work. (Note: I divided the "limit 20" into two equal parts, otherwise I get back 40 results total, but that's a minor detail!)

πŸ› Bug report
Status

Closed: cannot reproduce

Version

7.0 ⚰️

Component
DatabaseΒ  β†’

Last updated about 8 hours ago

  • Maintained by
  • πŸ‡³πŸ‡±Netherlands @daffie
Created by

πŸ‡¬πŸ‡§United Kingdom holtzermann17

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

  • πŸ‡¦πŸ‡ΊAustralia mstrelan

    This has been PMNMI for 11 years. It's time to close it. If anyone has steps to reproduce please re-open or raise a new issue.

Production build 0.69.0 2024