[ postfixadmin-Bugs-2859165 ] domain list incorrect SELECT

[SourceForge.net <no...@so...>]

Bugs item #2859165, was opened at 2009-09-15 11:43
Message generated for change (Comment added) made by christian_boltz
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=937964&aid=2859165&group_id=191583

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: Database
Group: v 2.3
>Status: Closed
>Resolution: Fixed
Priority: 5
Private: No
Submitted By: ksb (ksb4ever)
Assigned to: Nobody/Anonymous (nobody)
Summary: domain list incorrect SELECT

Initial Comment:
Hi!
2.3rc7
For me list-domain.php gives an error:
DEBUG INFORMATION:
Invalid query: ERROR: column "domain.uid" must appear in the GROUP BY clause or be used in an aggregate function 
So as I have uid and gid columns (used by dovecot) in domain and mailboxes tables (now filled with default values) error comes out. Backend - ppostgresql.
# fetch domain data and number of mailboxes
# (PgSQL requires the extensive GROUP BY statement, https://sourceforge.net/forum/message.php?msg_id=7386240)
$query = "
   SELECT domain.* , COUNT( DISTINCT mailbox.username ) AS mailbox_count
   FROM domain
   LEFT JOIN mailbox ON domain.domain = mailbox.domain
   $where
   GROUP BY domain.domain, domain.description, domain.aliases, domain.mailboxes,
   domain.maxquota, domain.quota, domain.transport, domain.backupmx, domain.created,
   domain.modified, domain.active
   ORDER BY domain.domain
   ";
$result = db_query($query);

Good comment, yeah ;) but no so good practice to use "SELECT * " even it isn't postgresql and without GROUP BY clause.
I think, best way to resove this problem would be exact select of columns!

----------------------------------------------------------------------

>Comment By: Christian Boltz (christian_boltz)
Date: 2011-04-10 16:48

Message:
Even if the solution is easy, it needs some time to do it - and sometimes
even 10 minutes can be hard to find...

Finally fixed in SVN r1028 (trunk) and r1029 (2.3 branch).

Thanks for your bugreport!

----------------------------------------------------------------------

Comment By: ksb (ksb4ever)
Date: 2010-02-04 13:51

Message:
Bug still exists in 2.3 final!
Resolution is easy as 123, exact select on columns.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=937964&aid=2859165&group_id=191583