Bug 8769 - Bad SQL for PostgreSQL in Timed View stats migration
Bad SQL for PostgreSQL in Timed View stats migration
Status: NEW
Product: OJS
Classification: Unclassified
Component: Installer
2.4.5
All All
: P3 normal
Assigned To: beghelli
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2014-05-21 05:57 PDT by Tom Christensen
Modified: 2014-05-21 15:29 PDT (History)
1 user (show)

See Also:
Version Reported In:
Also Affects:


Attachments
Patch for OJS 2.4.4-1 (2.20 KB, patch)
2014-05-21 05:58 PDT, Tom Christensen
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Tom Christensen 2014-05-21 05:57:48 PDT
Upgrading from 2.4.2 to 2.4.4-1 I see this error:
ojs2 has produced an error
  Message: WARNING: pg_query_params(): Query failed: ERROR:  column "a.journal_id" must appear in the GROUP BY clause or be used in an aggregate function at character 347
  In file: /home/ojs/stage-current/lib/pkp/lib/adodb/drivers/adodb-postgres7.inc.php
  At line: 124
  Stacktrace: 
  Server info:
   OS: Linux
   PHP Version: 5.1.6
   Apache Version: N/A
   DB Driver: postgres
   DB server version: PostgreSQL 8.4.20 on i386-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-54), 32-bit
-1: ERROR:  column "a.journal_id" must appear in the GROUP BY clause or be used in an aggregate function at character 347
                                                                ADOConnection._Execute(INSERT INTO metrics (load_id, metric_type, assoc_type, assoc_id, day, month, country_id, region, city, submission_id, metric, co..., Array[3])% line  860, file: /home/ojs/stage-current/lib/pkp/lib/adodb/adodb.inc.php
                                                        ADOConnection.Execute(INSERT INTO metrics (load_id, metric_type, assoc_type, assoc_id, day, month, country_id, region, city, submission_id, metric, co..., Array[3])% line  226, file: /home/ojs/stage-current/lib/pkp/classes/db/DAO.inc.php
                                                DAO.update(INSERT INTO metrics (load_id, metric_type, assoc_type, assoc_id, day, month, country_id, region, city, submission_id, metric, co..., Array[3])% line 1149, file: /home/ojs/stage-current/classes/install/Upgrade.inc.php
                                        Upgrade.migrateTimedViewsUsageStatistics(Object:Upgrade, Array[2])% line    0, file: 
                                call_user_func(Array[2], Object:Upgrade, Array[2])% line  419, file: /home/ojs/stage-current/lib/pkp/classes/install/Installer.inc.php
<h1>DB Error: ERROR:  column "a.journal_id" must appear in the GROUP BY clause or be used in an aggregate function at character 347</h1>ojs2: DB Error: ERROR:  column "a.journal_id" must appear in the GROUP BY clause or be used in an aggregate function at character 347

I can only guess that PostgreSQL is more strict here than MySQL.
Also working around this issue it will then complain about pa_issue.id from the same SELECT.
The galley migration following runs into the same issue.

A quick fix that allows me to go further is wrapping the affected fields in an aggregate function such as max() but I'm not sure if it is the right fix.
I'll attach a patch that does this.

For reference on how PostgreSQL treats GROUP BY:
http://www.postgresql.org/docs/8.4/static/queries-table-expressions.html
Comment 1 Tom Christensen 2014-05-21 05:58:17 PDT
Created attachment 4023 [details]
Patch for OJS 2.4.4-1