| Project: | Signup |
| Version: | 6.x-1.0-rc6 |
| Component: | Database |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
| Issue tags: | date, interpolation, interval, PostgreSQL |
Issue Summary
When running Signup 6.x-1.0-rc4 on Drupal 6.13 and Postgres 8.3 you get an error due to incorrect syntax.
query: SELECT n.title, n.nid, n.type, s.reminder_email, s.forwarding_email, e.event_start AS event_start, e.timezone FROM node n INNER JOIN signup s ON s.nid = n.nid INNER JOIN event e ON n.nid = e.nid INNER JOIN event_timezones tz ON e.timezone = tz.timezone WHERE (s.send_reminder = 1) AND (n.type = 'event') AND (('2009-06-22 06:21:03' > ((e.event_start - CASE WHEN tz.is_dst = 1 THEN tz.offset_dst ELSE tz."offset" END) - INTERVAL 's.reminder_days_before days'))) AND (('2009-06-22 06:21:03' <= ((e.event_start - CASE WHEN tz.is_dst = 1 THEN tz.offset_dst ELSE tz."offset" END)))) in /var/www/intranet/sites/all/modules/signup/includes/cron.inc on line 46.
Changing line 110 in signup/includes/event.6x-2.inc from:
"('". gmdate('Y-m-d H:i:s') ."' > (". event_where_utc() ." - INTERVAL 's.reminder_days_before days'))",to:
"('". gmdate('Y-m-d H:i:s') ."' > (". event_where_utc() ." - INTERVAL '1 days'*s.reminder_days_before))",fixed the problem for me.
Comments
#1
I'd love to know if the proposed syntax works on other versions of pgsql, too...
#2
It does. :) (At least in 6.5 and 7.2)
Although maybe one (if one where a bit better at php than I am) would want a neater or more elegant solution that what I proposed. The real error is that the string containing the query that is passed to Postgres contains the variables name (s.reminder_days_before) instead of the value. I assume it has something to do with how quotations are handled in php, since it works when I moved the variable outside the single quotes in the string.
#3
Thank you for this post, was just hunting down this problem.
#4
It's back. This is with Signup 1.0-rc6 and PostgreSQL 8.4. (Ubuntu Karmic)
warning: pg_query() [function.pg-query]: Query failed: ERROR: invalid input syntax for type interval: "s.reminder_days_before DAYS" LINE 1: ..., 'FMYYYY-FMMM-FMDDTFMHH24:FMMI:FMSS') - INTERVAL 's.reminde... ^ in /usr/share/drupal6/includes/database.pgsql.inc on line 139.user warning: query: SELECT n.title, n.nid, n.type, s.reminder_email, s.forwarding_email, field_date_value FROM node n INNER JOIN signup s ON s.nid = n.nid INNER JOIN content_field_date ON content_field_date.vid = n.vid WHERE (s.send_reminder = 1) AND (n.type = 'event') AND ('2010-01-21 19:44:41' >= (TO_DATE(field_date_value, 'FMYYYY-FMMM-FMDDTFMHH24:FMMI:FMSS') - INTERVAL 's.reminder_days_before DAYS')) AND ('2010-01-21 19:44:41' <= (TO_DATE(field_date_value, 'FMYYYY-FMMM-FMDDTFMHH24:FMMI:FMSS') + INTERVAL '1 HOURS')) in /etc/drupal/6/sites/all/modules/signup/includes/cron.inc on line 46.
The patch in the description is already in place. It seems the same syntax was used in the includes/date.inc _signup_date_sql_math() function. The attached patch fixes it. It is similar to the patch above.
#5
This occurs in signup 6x-1.0 for me.
Although the problem seems to be in the date module rather then signup.
See: #1112576: Postgres Error with Signup - sql_date_math
#6
Tagging