PostgreSQL error with groupdav.php @r34575

[Anthony_Messina]

Using PostgreSQL, the following occurs when accessing groupdav.php via
Thunderbird/Lightening:

Database error: Invalid SQL: SELECT
egw_cal_repeats.recur_type,egw_cal_repeats.recur_enddate,egw_cal_repeats.recur_interval,egw_cal_repeats.recur_data,egw_cal_repeats.recur_exception,CAST(egw_cal.cal_id
AS
varchar),cal_uid,cal_owner,cal_category,cal_modified,cal_priority,cal_public,cal_title,cal_description,cal_location,cal_reference,cal_modifier,cal_non_blocking,cal_special,cal_etag,cal_creator,cal_created,cal_recurrence,tz_id,cal_deleted,MIN(cal_start)
AS cal_start,cal_end,egw_cal_user.cal_recur_date,NULL AS
participants,NULL AS icons FROM egw_cal JOIN egw_cal_dates ON
egw_cal.cal_id=egw_cal_dates.cal_id JOIN egw_cal_user ON
egw_cal.cal_id=egw_cal_user.cal_id LEFT JOIN egw_cal_repeats ON
egw_cal.cal_id=egw_cal_repeats.cal_id WHERE cal_reference=0 AND
cal_deleted IS NULL AND egw_cal_user.cal_status != ‘R’ AND 1293645806 <
(CASE WHEN recur_type IS NULL THEN cal_end ELSE (CASE WHEN
recur_enddate!=0 THEN recur_enddate ELSE 9999999999 END) END) AND
egw_cal_user.cal_recur_date=0 AND cal_start < 1333821806 AND
egw_cal_user.cal_user_type=‘u’ AND egw_cal_user.cal_user_id IN
(‘9’,’-1’,’-22’,’-24’,’-17’) GROUP BY egw_cal_dates.cal_id\nORDER BY
cal_start:\nERROR: column “egw_cal_repeats.recur_type” must appear in
the GROUP BY clause or be used in an aggregate function\nLINE 1: SELECT
egw_cal_repeats.recur_type,egw_cal_repeats.recur_endd…\n ^

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

[Anthony_Messina]

Using PostgreSQL, the following occurs when accessing groupdav.php via
Thunderbird/Lightening:

Sorry, I didn’t realize the message (and the help part) got cut off…

ERROR: column “egw_cal_repeats.recur_type” must appear in the GROUP BY
clause or be used in an aggregate function at character 8
STATEMENT: SELECT
egw_cal_repeats.recur_type,egw_cal_repeats.recur_enddate,egw_cal_repeats.recur_interval,egw_cal_repeats.recur_data,egw_cal_repeats.recur_exception,CAST(egw_cal.cal_id
AS
varchar),cal_uid,cal_owner,cal_category,cal_modified,cal_priority,cal_public,cal_title,cal_description,cal_location,cal_reference,cal_modifier,cal_non_blocking,cal_special,cal_etag,cal_creator,cal_created,cal_recurrence,tz_id,cal_deleted,caldav_name,MIN(cal_start)
AS cal_start,cal_end,egw_cal_user.cal_recur_date,NULL AS
participants,NULL AS icons FROM egw_cal JOIN egw_cal_dates ON
egw_cal.cal_id=egw_cal_dates.cal_id JOIN egw_cal_user ON
egw_cal.cal_id=egw_cal_user.cal_id LEFT JOIN egw_cal_repeats ON
egw_cal.cal_id=egw_cal_repeats.cal_id WHERE cal_reference=0 AND
cal_deleted IS NULL AND egw_cal_user.cal_status != ‘R’ AND 1293703873 <
(CASE WHEN recur_type IS NULL THEN cal_end ELSE (CASE WHEN
recur_enddate!=0 THEN recur_enddate ELSE 9999999999 END) END) AND
egw_cal_user.cal_recur_date=0 AND cal_start < 1333879873 AND
egw_cal_user.cal_user_type=‘u’ AND egw_cal_user.cal_user_id IN
(‘7’,’-1’,’-22’,’-24’,’-17’) GROUP BY egw_cal_dates.cal_id
ORDER BY cal_start LIMIT 500 OFFSET 0

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

[Ralf_Becker_Stylite]

Hi Anthony,

should be fixed with my recent commit.

Thanks for reporting

I enabled now “sql_mode=ONLY_FULL_GROUP_BY” in my MySQL configuration
(/etc/my.cnf).

Should give an error, as PostgreSQL does, if not all columns are
mentioned. Maybe that helps to avoid that problems in future.

Ralf

@Nathan: Can you enable that on your development box too?

Using PostgreSQL, the following occurs when accessing groupdav.php via
Thunderbird/Lightening:

Sorry, I didn’t realize the message (and the help part) got cut off…

ERROR: column “egw_cal_repeats.recur_type” must appear in the GROUP BY
clause or be used in an aggregate function at character 8
STATEMENT: SELECT
egw_cal_repeats.recur_type,egw_cal_repeats.recur_enddate,egw_cal_repeats.recur_interval,egw_cal_repeats.recur_data,egw_cal_repeats.recur_exception,CAST(egw_cal.cal_id
AS
varchar),cal_uid,cal_owner,cal_category,cal_modified,cal_priority,cal_public,cal_title,cal_description,cal_location,cal_reference,cal_modifier,cal_non_blocking,cal_special,cal_etag,cal_creator,cal_created,cal_recurrence,tz_id,cal_deleted,caldav_name,MIN(cal_start)
AS cal_start,cal_end,egw_cal_user.cal_recur_date,NULL AS
participants,NULL AS icons FROM egw_cal JOIN egw_cal_dates ON
egw_cal.cal_id=egw_cal_dates.cal_id JOIN egw_cal_user ON
egw_cal.cal_id=egw_cal_user.cal_id LEFT JOIN egw_cal_repeats ON
egw_cal.cal_id=egw_cal_repeats.cal_id WHERE cal_reference=0 AND
cal_deleted IS NULL AND egw_cal_user.cal_status != ‘R’ AND 1293703873<
(CASE WHEN recur_type IS NULL THEN cal_end ELSE (CASE WHEN
recur_enddate!=0 THEN recur_enddate ELSE 9999999999 END) END) AND
egw_cal_user.cal_recur_date=0 AND cal_start< 1333879873 AND
egw_cal_user.cal_user_type=‘u’ AND egw_cal_user.cal_user_id IN
(‘7’,’-1’,’-22’,’-24’,’-17’) GROUP BY egw_cal_dates.cal_id
ORDER BY cal_start LIMIT 500 OFFSET 0

---

Xperia™ PLAY
It’s a major breakthrough. An authentic gaming
smartphone on the nation’s most reliable network.
And it wants your games.
http://p.sf.net/sfu/verizon-sfdev

---

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

–
Ralf Becker
Director Software Development

Stylite GmbH
[open style of IT]

Morschheimer Strasse 15
67292 Kirchheimbolanden

fon +49 (0) 6352 70629-0
fax +49 (0) 6352 70629-30
mailto: rb@stylite.de

www.stylite.de
www.egroupware.org

---

Geschäftsführer Andre Keller, Ralf Becker
Registergericht Kaiserslautern HRB 30575
Umsatzsteuer-Id / VAT-Id: DE214280951

---

Xperia™ PLAY
It’s a major breakthrough. An authentic gaming
smartphone on the nation’s most reliable network.
And it wants your games.
http://p.sf.net/sfu/verizon-sfdev

---

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

[Anthony_Messina]

Hi Anthony,

should be fixed with my recent commit.

Thanks for reporting

I enabled now “sql_mode=ONLY_FULL_GROUP_BY” in my MySQL configuration
(/etc/my.cnf).

Should give an error, as PostgreSQL does, if not all columns are
mentioned. Maybe that helps to avoid that problems in future.

Ralf

Thank you. Here are my results with the fix:

With Thunderbird/Lightning GroupDAV: it seems to work, but I get the
following in the httpd log:

PHP Warning: mb_substr() expects parameter 1 to be string, array given
in /var/www/egroupware/addressbook/inc/class.addressbook_ldap.inc.php on
line 287

With Funambol Sync on my BB, I get:

SELECT egw_cal.cal_id,MIN(cal_start) AS cal_start,recur_type FROM
egw_cal JOIN egw_cal_dates ON egw_cal.cal_id=egw_cal_dates.cal_id JOIN
egw_cal_user ON egw_cal.cal_id=egw_cal_user.cal_id LEFT JOIN
egw_cal_repeats ON egw_cal.cal_id=egw_cal_repeats.cal_id WHERE
cal_deleted IS NULL AND 1299736800 < (CASE WHEN recur_type IS NULL THEN
cal_end ELSE (CASE WHEN recur_enddate!=0 THEN recur_enddate ELSE
9999999999 END) END) AND egw_cal_user.cal_recur_date=0 AND cal_start <
1367384400 AND egw_cal_user.cal_user_type=‘u’ AND
egw_cal_user.cal_user_id IN
(‘6’,’-1’,’-25’,’-22’,’-24’,’-6’,’-16’,’-17’,’-19’,’-20’) GROUP BY Array
ORDER BY cal_id,cal_start ASC

I think the “Array” part in the GROUP BY clause is the issue.

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

[Ralf_Becker_Stylite]

Hi Anthony,

should be fixed with my recent commit.

Thanks for reporting

I enabled now “sql_mode=ONLY_FULL_GROUP_BY” in my MySQL configuration
(/etc/my.cnf).

Should give an error, as PostgreSQL does, if not all columns are
mentioned. Maybe that helps to avoid that problems in future.

Ralf

Thank you. Here are my results with the fix:

With Thunderbird/Lightning GroupDAV: it seems to work, but I get the
following in the httpd log:

PHP Warning: mb_substr() expects parameter 1 to be string, array given
in /var/www/egroupware/addressbook/inc/class.addressbook_ldap.inc.php on
line 287

That’s not a PostgreSQL specific problem

With Funambol Sync on my BB, I get:

SELECT egw_cal.cal_id,MIN(cal_start) AS cal_start,recur_type FROM
egw_cal JOIN egw_cal_dates ON egw_cal.cal_id=egw_cal_dates.cal_id JOIN
egw_cal_user ON egw_cal.cal_id=egw_cal_user.cal_id LEFT JOIN
egw_cal_repeats ON egw_cal.cal_id=egw_cal_repeats.cal_id WHERE
cal_deleted IS NULL AND 1299736800< (CASE WHEN recur_type IS NULL THEN
cal_end ELSE (CASE WHEN recur_enddate!=0 THEN recur_enddate ELSE
9999999999 END) END) AND egw_cal_user.cal_recur_date=0 AND cal_start<
1367384400 AND egw_cal_user.cal_user_type=‘u’ AND
egw_cal_user.cal_user_id IN
(‘6’,’-1’,’-25’,’-22’,’-24’,’-6’,’-16’,’-17’,’-19’,’-20’) GROUP BY Array
ORDER BY cal_id,cal_start ASC

I think the “Array” part in the GROUP BY clause is the issue.

Yeap, should be fixed now.

Ralf

Ralf Becker
Director Software Development

Stylite GmbH
[open style of IT]

Morschheimer Strasse 15
67292 Kirchheimbolanden

fon +49 (0) 6352 70629-0
fax +49 (0) 6352 70629-30
mailto: rb@stylite.de

www.stylite.de
www.egroupware.org

---

Geschäftsführer Andre Keller, Ralf Becker
Registergericht Kaiserslautern HRB 30575
Umsatzsteuer-Id / VAT-Id: DE214280951

---

Xperia™ PLAY
It’s a major breakthrough. An authentic gaming
smartphone on the nation’s most reliable network.
And it wants your games.
http://p.sf.net/sfu/verizon-sfdev

---

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

[Anthony_Messina]

I think the “Array” part in the GROUP BY clause is the issue.
Yeap, should be fixed now.

Ralf

Yes, thank you! -A

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