SQL problem with GROUP BY clause in DataConnector query

[danhammari]

I'm using a Postgres engine that has strict settings when parsing an SQL SELECT statement that has a GROUP BY clause. It requires that each field listed in the SELECT statement must have an aggregate function applied to it else be specifically listed in the SQL statement's GROUP BY clause. The query in lines 53-60 of file LTI/DataConnector/DataConnector_pdo.php has a GROUP BY clause that references only two of the twenty-three fields being asked for:

                $sql = 'SELECT consumer_pk, name, consumer_key, secret, ' .
                    'platform_id, client_id, deployment_id, public_key, ' .
                    'lti_version, signature_method, consumer_name, consumer_version, consumer_guid, ' .
                    'profile, tool_proxy, settings, protected, enabled, ' .
                    'enable_from, enable_until, last_access, created, updated ' .
                    "FROM {$this->dbTableNamePrefix}" . static::PLATFORM_TABLE_NAME . ' ' .
                    'WHERE (platform_id = :platform_id) ' .
                    'GROUP BY platform_id, client_id';

If the GROUP BY clause here is necessary, then it may help to refactor this query to be more universally acceptable across SQL engines that adhere to different levels of strictness. Here is the message that I currently receive from my Postgres SQL engine:

ERROR:  column "lti2_consumer.consumer_pk" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT consumer_pk, name, consumer_key, secret, platform_id,...
               ^
SQL state: 42803
Character: 8

[spvickers]

Thanks for reporting this. I'll take a closer look at it. As you suggest, it does look like the GROUP BY clause is not needed.

[spvickers]

Fixed in version 4.6.5. Thanks.