PostgreSQL mail app error trunk@r44619

Anthony_Messina 2013-11-20 22:00:07 UTC #1

When testing the ‘mail’ (not felamimail) app, I get the following error using
PostgreSQL:

Invalid SQL: SELECT egw_ea_accounts.,egw_ea_identities.,(SELECT
ARRAY_TO_STRING(ARRAY_AGG(account_id), ‘,’) FROM egw_ea_valid WHERE
egw_ea_valid.acc_id=egw_ea_accounts.acc_id GROUP BY egw_ea_valid.acc_id) AS
account_id FROM egw_ea_accounts JOIN egw_ea_identities ON
egw_ea_identities.ident_id=egw_ea_accounts.ident_id JOIN egw_ea_valid ON
egw_ea_valid.acc_id=egw_ea_accounts.acc_id WHERE egw_ea_valid.account_id IN
(-1,-22,-24,-23,-30,6,0) GROUP BY egw_ea_accounts.acc_id ORDER BY
egw_ea_valid.account_id ASC,ident_org ASC,ident_realname ASC,acc_name ASC
ERROR: column “egw_ea_identities.ident_id” must appear in the GROUP BY clause
or be used in an aggregate function
LINE 1: SELECT egw_ea_accounts.,egw_ea_identities.,(SELECT ARRAY_T…
^ (-1)

-A

–
Anthony - http://messinet.com - http://messinet.com/~amessina/gallery
8F89 5E72 8DF0 BCF0 10BE 9967 92DC 35DC B001 4A4E

Leithoff_Klaus 2013-11-21 09:03:42 UTC #2

It is quite an “early” stage for the postgres side of the new saving scheme.
We will have a look. thanks for reporting

kl@stylite.de
Stylite AG
Developement
[ open style of IT ]
Morschheimer Strasse 15
67292 Kirchheimbolanden
fon 06352 . 70629-0
fax 06352 . 70629-30
www.stylite.de | www.egroupware.org

Managing Directors: Andre Keller | Ralf Becker | Gudrun Mueller
Chairman of the supervisory board: Prof. Dr. Birger Leon Kropshofer

VAT DE214280951 | Registered HRB 31158 Kaiserslautern Germany

Leithoff_Klaus 2013-11-26 08:27:15 UTC #3

please try Revision 44651
That should fix the SQL error.

kl@stylite.de
Stylite AG
Developement
[ open style of IT ]
Morschheimer Strasse 15
67292 Kirchheimbolanden
fon 06352 . 70629-0
fax 06352 . 70629-30
www.stylite.de | www.egroupware.org

Managing Directors: Andre Keller | Ralf Becker | Gudrun Mueller
Chairman of the supervisory board: Prof. Dr. Birger Leon Kropshofer

VAT DE214280951 | Registered HRB 31158 Kaiserslautern Germany

Anthony_Messina 2013-11-27 14:12:59 UTC #4

I think we’re closer. Now I get (with trunk@r44679):

ERROR: argument of AND must be type boolean, not type integer at character 78
STATEMENT: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND account_id IN
(0,6) AND cred_type & 11 ORDER BY account_id ASC

-A

please try Revision 44651
That should fix the SQL error.

kl@stylite.de

From: Anthony Messina [amessina@messinet.com ]
To: egroupware-developers@lists.sourceforge.net
Date: Wed, 20 Nov 2013 16:00:07 -0600

When testing the ‘mail’ (not felamimail) app, I get the following error
using >
PostgreSQL:

Invalid SQL: SELECT egw_ea_accounts.,egw_ea_identities.,(SELECT
ARRAY_TO_STRING(ARRAY_AGG(account_id), ‘,’) FROM egw_ea_valid WHERE
egw_ea_valid.acc_id=egw_ea_accounts.acc_id GROUP BY
egw_ea_valid.acc_id) AS
account_id FROM egw_ea_accounts JOIN egw_ea_identities ON
egw_ea_identities.ident_id=egw_ea_accounts.ident_id JOIN egw_ea_valid
ON
egw_ea_valid.acc_id=egw_ea_accounts.acc_id WHERE
egw_ea_valid.account_id IN
(-1,-22,-24,-23,-30,6,0) GROUP BY egw_ea_accounts.acc_id ORDER BY
egw_ea_valid.account_id ASC,ident_org ASC,ident_realname ASC,acc_name
ASC
ERROR: column “egw_ea_identities.ident_id” must appear in the GROUP BY
clause >

or be used in an aggregate function
LINE 1: SELECT egw_ea_accounts.,egw_ea_identities.,(SELECT ARRAY_T…
^ (-1)

–
Anthony - http://messinet.com - http://messinet.com/~amessina/gallery
8F89 5E72 8DF0 BCF0 10BE 9967 92DC 35DC B001 4A4E

Leithoff_Klaus 2013-11-27 14:56:08 UTC #5

Would you try && for min in line 119 and 271
instead of & in emailadmin/inc/class.emailadmin_credentials.inc.php
and if this does not work trying to replace the && in those lines with AND

kl@stylite.de
Stylite AG
Developement
[ open style of IT ]
Morschheimer Strasse 15
67292 Kirchheimbolanden
fon 06352 . 70629-0
fax 06352 . 70629-30
www.stylite.de | www.egroupware.org

Managing Directors: Andre Keller | Ralf Becker | Gudrun Mueller
Chairman of the supervisory board: Prof. Dr. Birger Leon Kropshofer

VAT DE214280951 | Registered HRB 31158 Kaiserslautern Germany

Anthony_Messina 2013-11-27 15:49:36 UTC #6

Would you try && for min in line 119 and 271
instead of & in emailadmin/inc/class.emailadmin_credentials.inc.php
and if this does not work trying to replace the && in those lines with AND

With “&&”:

Invalid SQL: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND account_id IN
(0,6) AND cred_type && 11 ORDER BY account_id ASC
ERROR: operator does not exist: smallint && integer
LINE 1: …RE acc_id=1 AND account_id IN (0,6) AND cred_type && 11 ORDE…
^
HINT: No operator matches the given name and argument type(s). You might need
to add explicit type casts. (-1)

With “AND”:

Invalid SQL: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND account_id IN
(0,6) AND cred_type AND 11 ORDER BY account_id ASC
ERROR: argument of AND must be type boolean, not type smallint
LINE 1: …tials WHERE acc_id=1 AND account_id IN (0,6) AND cred_type …
^ (-1)

–
Anthony - http://messinet.com - http://messinet.com/~amessina/gallery
8F89 5E72 8DF0 BCF0 10BE 9967 92DC 35DC B001 4A4E

Anthony_Messina 2013-11-27 16:26:05 UTC #7

Would you try && for min in line 119 and 271
instead of & in emailadmin/inc/class.emailadmin_credentials.inc.php
and if this does not work trying to replace the && in those lines with AND

With “&&”:

Invalid SQL: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND account_id
IN (0,6) AND cred_type && 11 ORDER BY account_id ASC
ERROR: operator does not exist: smallint && integer
LINE 1: …RE acc_id=1 AND account_id IN (0,6) AND cred_type && 11 ORDE…
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts. (-1)

With “AND”:

Invalid SQL: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND account_id
IN (0,6) AND cred_type AND 11 ORDER BY account_id ASC
ERROR: argument of AND must be type boolean, not type smallint
LINE 1: …tials WHERE acc_id=1 AND account_id IN (0,6) AND cred_type …
^ (-1)

More info from the PostgreSQL logs:

ERROR: operator does not exist: smallint && integer at character 88
HINT: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
STATEMENT: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND account_id IN
(0,6) AND cred_type && 11 ORDER BY account_id ASC
ERROR: operator does not exist: smallint && integer at character 88
HINT: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
STATEMENT: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND account_id IN
(0,6) AND cred_type && 11 ORDER BY account_id ASC
ERROR: argument of AND must be type boolean, not type smallint at character
78
STATEMENT: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND account_id IN
(0,6) AND cred_type AND 11 ORDER BY account_id ASC
ERROR: argument of AND must be type boolean, not type smallint at character
78
STATEMENT: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND account_id IN
(0,6) AND cred_type AND 11 ORDER BY account_id ASC

–
Anthony - http://messinet.com - http://messinet.com/~amessina/gallery
8F89 5E72 8DF0 BCF0 10BE 9967 92DC 35DC B001 4A4E

Ralf_Becker_Stylite 2013-11-28 07:21:25 UTC #8

Should be fixed with following commit:

http://svn.stylite.de/viewvc/egroupware?revision=44693&view=revision

Ralf

Would you try && for min in line 119 and 271
instead of & in emailadmin/inc/class.emailadmin_credentials.inc.php
and if this does not work trying to replace the && in those lines with AND

With “&&”:

Invalid SQL: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND account_id
IN (0,6) AND cred_type && 11 ORDER BY account_id ASC
ERROR: operator does not exist: smallint && integer
LINE 1: …RE acc_id=1 AND account_id IN (0,6) AND cred_type && 11 ORDE…
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts. (-1)

With “AND”:

Invalid SQL: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND account_id
IN (0,6) AND cred_type AND 11 ORDER BY account_id ASC
ERROR: argument of AND must be type boolean, not type smallint
LINE 1: …tials WHERE acc_id=1 AND account_id IN (0,6) AND cred_type …
^ (-1)

More info from the PostgreSQL logs:

ERROR: operator does not exist: smallint && integer at character 88
HINT: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
STATEMENT: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND account_id IN
(0,6) AND cred_type && 11 ORDER BY account_id ASC
ERROR: operator does not exist: smallint && integer at character 88
HINT: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
STATEMENT: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND account_id IN
(0,6) AND cred_type && 11 ORDER BY account_id ASC
ERROR: argument of AND must be type boolean, not type smallint at character
78
STATEMENT: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND account_id IN
(0,6) AND cred_type AND 11 ORDER BY account_id ASC
ERROR: argument of AND must be type boolean, not type smallint at character
78
STATEMENT: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND account_id IN
(0,6) AND cred_type AND 11 ORDER BY account_id ASC

Rapidly troubleshoot problems before they affect your business. Most IT
organizations don’t have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349351&iu=/4140/ostg.clktrk

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers

–
Ralf Becker
Director Software Development

Stylite AG

Morschheimer Strasse 15 | Tel. +49 6352 70629 0
D-67292 Kirchheimbolanden | Fax. +49 6352 70629 30

Email: rb@stylite.de

www.stylite.de | www.egroupware.org

Managing Directors: Andre Keller | Ralf Becker | Gudrun Mueller
Chairman of the supervisory board: Prof. Dr. Birger Leon Kropshofer

VAT DE214280951 | Registered HRB 31158 Kaiserslautern Germany

Rapidly troubleshoot problems before they affect your business. Most IT
organizations don’t have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349351&iu=/4140/ostg.clktrk

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers

Anthony_Messina 2013-11-29 05:09:13 UTC #9

Thanks again, Ralf! -A

Should be fixed with following commit:

http://svn.stylite.de/viewvc/egroupware?revision=44693&view=revision

Ralf

Would you try && for min in line 119 and 271
instead of & in emailadmin/inc/class.emailadmin_credentials.inc.php
and if this does not work trying to replace the && in those lines with
AND

With “&&”:

Invalid SQL: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND
account_id
IN (0,6) AND cred_type && 11 ORDER BY account_id ASC
ERROR: operator does not exist: smallint && integer
LINE 1: …RE acc_id=1 AND account_id IN (0,6) AND cred_type && 11
ORDE…
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts. (-1)

With “AND”:

Invalid SQL: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND
account_id
IN (0,6) AND cred_type AND 11 ORDER BY account_id ASC
ERROR: argument of AND must be type boolean, not type smallint
LINE 1: …tials WHERE acc_id=1 AND account_id IN (0,6) AND cred_type …
^ (-1)

More info from the PostgreSQL logs:

ERROR: operator does not exist: smallint && integer at character 88
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
STATEMENT: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND
account_id IN (0,6) AND cred_type && 11 ORDER BY account_id ASC
ERROR: operator does not exist: smallint && integer at character 88
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
STATEMENT: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND
account_id IN (0,6) AND cred_type && 11 ORDER BY account_id ASC
ERROR: argument of AND must be type boolean, not type smallint at
character 78
STATEMENT: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND
account_id IN (0,6) AND cred_type AND 11 ORDER BY account_id ASC
ERROR: argument of AND must be type boolean, not type smallint at
character 78
STATEMENT: SELECT * FROM egw_ea_credentials WHERE acc_id=1 AND
account_id IN (0,6) AND cred_type AND 11 ORDER BY account_id ASC

–
Anthony - http://messinet.com - http://messinet.com/~amessina/gallery
8F89 5E72 8DF0 BCF0 10BE 9967 92DC 35DC B001 4A4E