PostgreSQL: Statistics broken

[Drachenkaetzchen]

Statistics fail on PostgreSQL because of the following SQL statement:

SELECT SUM(p0_.stockLevel) AS sclr0, p1_.id AS id1, p1_.name AS name2, p1_.shortName AS shortName3, p1_.is_default AS is_default4 FROM PartUnit p1_ LEFT JOIN Part p0_ ON p1_.id = p0_.partUnit_id GROUP BY p1_.id;

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

LINE 1: ...LECT SUM(p0_.stockLevel) AS sclr0, p1_.id AS id1, p1_.name A...

If we just retrieve the SUM() without any field of p1_, it works.

[Drachenkaetzchen]

Analog to the SQL, the DQL used to generate it is:

SELECT SUM(p.stockLevel) AS stockLevel, pu FROM de\RaumZeitLabor\PartKeepr\Part\PartUnit pu LEFT JOIN pu.parts p GROUP BY pu.id

If we omit "pu" from the SELECT, it works (but we don't have any information about the unit then).

[Drachenkaetzchen]

This was a bug in Doctrine, which has been fixed. Verified and closing.

[Drachenkaetzchen]

It seems that the issue is related to PostgreSQL 8 (it is confirmed to work on PostgreSQL 9)

[apex-]

The same problem occurs when trying to create a project report:

SQLSTATE[42803]: Grouping error: 7 ERROR: column "p1_.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT SUM(p0_.quantity) AS sclr0, p1_.id AS id1, s2_.name A... ^

[apex-]

Another probably related one in the "CreateStatisticSnapshot" cron job:

tbruese@saturn:/var/www/devpartkeepr/cronjobs$ php CreateStatisticSnapshot.php
PHP Deprecated: Comments starting with '#' are deprecated in /etc/php5/cli/conf.d/imagick.ini on line 1 in Unknown on line 0
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42803]: Grouping error: 7 ERROR: column "p1_.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...LECT SUM(p0_.stockLevel) AS sclr0, p1_.id AS id1, p1_.name A...
^' in /usr/share/php/Doctrine/DBAL/Connection.php:618
Stack trace:
#0 /usr/share/php/Doctrine/DBAL/Connection.php(618): PDO->query('SELECT SUM(p0_....')
#1 /usr/share/php/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php(46): Doctrine\DBAL\Connection->executeQuery('SELECT SUM(p0_....', Array, Array)
#2 /usr/share/php/Doctrine/ORM/Query.php(249): Doctrine\ORM\Query\Exec\SingleSelectExecutor->execute(Object(Doctrine\DBAL\Connection), Array, Array)
#3 /usr/share/php/Doctrine/ORM/AbstractQuery.php(607): Doctrine\ORM\Query->_doExecute()
#4 /usr/share/php/Doctrine/ORM/AbstractQuery.php(413): Doctrine\ORM\AbstractQuery->execute(Array, 1)
#5 /var/www/devpartkeepr/src/backend/de/RaumZeitLabor/PartKeepr/PartUn in /usr/share/php/Doctrine/DBAL/Connection.php on line 618

[Drachenkaetzchen]

I believe all of these are are done now, but to be sure, can you retest when you find time?

[apex-]

All mentioned issues seem to be fixed

[Drachenkaetzchen]

I'm glad :) Closing this one and preparing release now.