Fork me on GitHub
Subscribe 1

Ticket #95 (fixed bug)

SQL error when showing all posts from an user (with PostgreSQL)

  • Created: 2010-08-09 15:54:06
  • Reported by: delroth
  • Assigned to: Reines
  • Milestone: 1.4.3
  • Component: search
  • Priority: high

Hi,

The SQL query used to get all the posts from a specific user fails when FluxBB is using the PostgreSQL database backend.

The query is the following :

'SELECT t.id FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'posts AS p ON t.id=p.topic_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=t.forum_id AND fp.group_id='.$pun_user['g_id'].') WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND p.poster_id='.$user_id.' GROUP BY t.id ORDER BY t.last_post DESC'

This fails with the following error message on PostgreSQL :

ERROR:  column "t.last_post" must appear in the GROUP BY clause or be used in an aggregate function

Steps to reproduce :
1) install FluxBB >= 1.4.0 (did not test previous versions) with the PostgreSQL database backend
2) create a user
3) try to access /search.php?action=show_user&user_id=2

Regards,
Pierre Bourdon

History

Reines 2010-08-09 16:24:33

  • Milestone set to 1.4.3.
  • Owner set to Reines.
  • Status changed from open to fixed.

Thanks, fixed in a4fc0d5c2b58a342e421.

Reines 2010-08-09 16:25:21

Commit a4fc0d5 to fluxbb fluxbb-1.4

Fixing a bug in searching for a users posts when using PgSQL, reported by delroth. #95