project/issues/subscribe-mail shows wrong subscription list on PostgreSQL [#290553]

project/issues/subscribe-mail shows wrong subscription list on PostgreSQL and drupal-5.9.

on line 493 of issues.inc

$result = db_query(db_rewrite_sql("SELECT s.nid, n.title, s.level, p.uri FROM {project_subscriptions} s INNER JOIN {node} n ON n.nid = s.nid INNER JOIN {project_projects} p ON n.nid = p.nid WHERE n.type = 'project_project' AND n.status = 1 AND s.uid = %d ORDER BY n.title", 's'), $user->uid);

db_rewrite_sql() makes the following query when the uid=2:

SELECT s.nid, n.title, s.level, p.uri 
  FROM (SELECT DISTINCT ON (nid) * FROM {project_subscriptions}) s 
    INNER JOIN {node} n ON n.nid = s.nid 
    INNER JOIN {project_projects} p ON n.nid = p.nid 
  WHERE n.type = 'project_project' 
    AND n.status = 1 
    AND s.uid = 2 
  ORDER BY n.title

Postgresql can not guarantee that the table 's' always contains the uid='2' records.

To avoid this, you need to change the query like this:

$result = db_query(db_rewrite_sql("SELECT s.nid, n.title, s.level, p.uri FROM (SELECT ps.nid, ps.level FROM {project_subscriptions} ps WHERE ps.uid = %d) s INNER JOIN {node} n ON n.nid = s.nid INNER JOIN {project_projects} p ON n.nid = p.nid WHERE n.type = 'project_project' AND n.status = 1 ORDER BY n.title", 's'), $user->uid);

db_rewrite_sql() makes the following query when the uid=2:

SELECT s.nid, n.title, s.level, p.uri 
  FROM (SELECT ps.nid, ps.level FROM {project_subscriptions} ps WHERE ps.uid = 2) s 
    INNER JOIN {node} n ON n.nid = s.nid 
    INNER JOIN {project_projects} p ON n.nid = p.nid 
  WHERE n.type = 'project_project' 
    AND n.status = 1 
  ORDER BY n.title

Comments

Comment #1

whalebeach commented 3 August 2008 at 14:33

Above fix causes query error when used with content_access.module.
Then, using the original query and delete the second param of db_rewrite_sql() make the error away.

$result = db_query(db_rewrite_sql("SELECT s.nid, n.title, s.level, p.uri FROM {project_subscriptions} s INNER JOIN {node} n ON n.nid = s.nid INNER JOIN {project_projects} p ON n.nid = p.nid WHERE n.type = 'project_project' AND n.status = 1 AND s.uid = %d ORDER BY n.title"), $user->uid);

Comment #2

aclight commented 3 August 2008 at 15:44

Status:

Active

» Postponed (maintainer needs more info)

Can you please explain what you mean when you say the "wrong subscription list" is shown?

Comment #3

whalebeach commented 8 August 2008 at 23:59

Hi,

- Project listing is OK.
- Subcription listing (none - my issue - all issue) is not same as DB restored subscription data.
For example, When I subscribed 'all issues' to project 'A' and click save button,
the subscription data in DB is ok but the display is sometimes set to 'none'.

The problem is in query '(SELECT DISTINCT ON (nid) * FROM {project_subscriptions}) s' made by db_rewrite_sql().
if it is '(SELECT DISTINCT ON (nid) * FROM {project_subscriptions} where uid = 2'),
you can get user 2's subscription data.

But with the original query, Postgresql firstly make a result set with distinct nid, random uid and level,
secondary selects uid=2 rows from the result set.

http://www.postgresql.org/docs/8.1/static/queries-select-lists.html#QUER...