Error occurs with repeat date

I see the same. I am using:
date 7.x-2.x-dev
calendar 7.x-2.x-dev
views 7.x-3.x-dev
ctools 7.x-1.x-dev

Default view "Calendar page" is showing OK with Contextual Filters set to "Date: Date (node) (Content: Updated date)"
I have a content type Event with a field date.
When changing the Contextual Filter to Date: Date (node) (Content: Date (field_date)) I get:

* SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS field_data_field_date_ FROM node node LEFT JOIN field_data_field_date field_' at line 1
* SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS field_data_field_date_ FROM node node LEFT JOIN field_data_field_date field_' at line 1

Same problem here, but I'm not using the calendar module. The error occurs if I enable the date views module.

I use the latest view-7.x-3.x-dev and date 7.x-2.x-dev

Same error when I use filter in views. I added filter by date field and set condition on "Is less than or equal to" now. This produces error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<= '2011-04-25') ))) subquery' at line 2

Same here. For reference, here's a very simple query that is throwing the syntax error. Looks like the date field itself is not being written to the query string for contextual filters

SELECT node.title AS node_title, node.nid AS nid
FROM 
{node} node
LEFT JOIN {field_data_field_exh_dates} field_data_field_exh_dates ON node.nid = field_data_field_exh_dates.entity_id AND (field_data_field_exh_dates.entity_type = :views_join_condition_0 AND field_data_field_exh_dates.deleted = :views_join_condition_1)
WHERE (( ( = '2011-04') ))

Same here. I have upgraded date, calendar, views and ctools modules to the latest dev version, cleared cache etc. I got this (or similar) error in case of every view which uses dates. I'm not sure whether it's a date or views bug. Here's the SQL query

SELECT node.nid AS nid, node.title AS node_title, node.language AS node_language, field_data_field_event_date.field_event_date_value AS field_data_field_event_date_field_event_date_value, 'node' AS field_data_field_event_date_node_entity_type
FROM 
{node} node
LEFT JOIN {field_data_field_event_date} field_data_field_event_date ON node.nid = field_data_field_event_date.entity_id AND (field_data_field_event_date.entity_type = :views_join_condition_0 AND field_data_field_event_date.deleted = :views_join_condition_1)
WHERE (( (node.type IN  ('esemeny')) AND ( >= '2011-04-26') ))
ORDER BY field_data_field_event_date_field_event_date_value ASC

As yskel mentioned above, it seems that views omits the date field for query.

Again same problem here, the field isn't being specified in the condition.

Syntax from a previous working version:
(DATE_FORMAT(STR_TO_DATE(field_data_field_ann_expires.field_ann_expires_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-04-26')

Syntax from this latest version:
( >= '2011-04-26')

Also getting a debug message which seems to be the timestamp of the current time in UTC. See screenshot ...

Should be fixed now. This was caused by a recent commit to keep the constructor from defaulting to MYSQL so Postgres would work right, but it was missing some additional changes to make sure the db_type got set up.

I can confirm it is fixed. Just downloaded HEAD from repo and everything is back to normal. Thanks for the fast reply!

Great, seems fixed in alpha2 also :-)

I have not observed this error to be fixed. I'm running Date 7.x-2.x-dev and have a view with a few filters. When I add a date filter using "greater than or equal to" and "now", I get this error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '>= '2011-04-29') ))) subquery' at line 3

I can paste in the entire view if that would help, but for the sake of brevity, I will paste in the diff now:

/* Filter criterion: Fields: Certification Expiration Date (field_certificate_exp_date) */
$handler->display->display_options['filters']['field_certificate_exp_date_value']['id'] = 'field_certificate_exp_date_value';
$handler->display->display_options['filters']['field_certificate_exp_date_value']['table'] = 'field_data_field_certificate_exp_date';
$handler->display->display_options['filters']['field_certificate_exp_date_value']['field'] = 'field_certificate_exp_date_value';
$handler->display->display_options['filters']['field_certificate_exp_date_value']['operator'] = '>=';
$handler->display->display_options['filters']['field_certificate_exp_date_value']['granularity'] = 'day';
$handler->display->display_options['filters']['field_certificate_exp_date_value']['form_type'] = 'date_select';
$handler->display->display_options['filters']['field_certificate_exp_date_value']['default_date'] = 'now';
$handler->display->display_options['filters']['field_certificate_exp_date_value']['default_to_date'] = '';
$handler->display->display_options['filters']['field_certificate_exp_date_value']['year_range'] = '-3:+3';

Do you see reason why I can't get this to work?

The error shows up only when I use date with repeat. I use:
date 7.x-2.0-alpha3
calendar 7.x-2.x-dev
views 7.x-3.x-dev
ctools 7.x-1.x-dev

I see these three fields in the views contexual filter:

Content: Test Date Repeat (field_test_date_repeat)
Content: Test Date Repeat (field_test_date_repeat:delta)
Content: Test Date Repeat (field_test_date_repeat:rrule)   

I select: Content: Test Date Repeat (field_test_date_repeat) and I get the errors below:

* Notice: Undefined variable: delta_field in calendar_plugin_style->query() (line 187 of /Users/wglinka/Sites/calendar/sites/all/modules/calendar/includes/calendar_plugin_style.inc).
    * Notice: Undefined variable: delta_field in calendar_plugin_style->query() (line 187 of /Users/wglinka/Sites/calendar/sites/all/modules/calendar/includes/calendar_plugin_style.inc).
* SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS field_data_field_test_date_repeat_ FROM node node LEFT JOIN field_data_field' at line 1
* SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS field_data_field_test_date_repeat_ FROM node node LEFT JOIN field_data_field' at line 1

I'd like to report that I redid my upgrade to Date 7.x-2.x-dev and am no longer seeing the bug. I must have not deleted all of the old Date files, or somehow did something wrong with my upgrade. Just FYI!

I still see this problem with a fresh install of D7 core and:
- Views 7.x-3.x-dev
- CTools 7.x-1.x-dev
- Date 7.x-2.x-dev
- Calendar 7.x-2.x-dev

Create a new Date field with repeat options. Edit Calendar view, change contextual filter to use "Content: Date (field_date)" instead of default Content: Updated Date.

Generated SQL looks like this:

SELECT node.title AS node_title, node.nid AS nid, node.changed AS node_changed, field_data_field_date.field_date_value AS field_data_field_date_field_date_value, field_data_field_date. AS field_data_field_date_
FROM
{node} node
LEFT JOIN {field_data_field_date} field_data_field_date ON node.nid = field_data_field_date.entity_id AND (field_data_field_date.entity_type = :views_join_condition_0 AND field_data_field_date.deleted = :views_join_condition_1)
WHERE (( (node.status = '1') )AND( (DATE_FORMAT(ADDTIME(STR_TO_DATE(field_data_field_date.field_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-18000)), '%Y-%m') = '2011-05') ))
LIMIT 10 OFFSET 0

Now if I go back and add a new Date field WITHOUT repeat options, change the calendar view to use this field for contextual filters, the sql looks good:

SELECT node.title AS node_title, node.nid AS nid, node.changed AS node_changed, field_data_field_date_norepeat.field_date_norepeat_value AS field_data_field_date_norepeat_field_date_norepeat_value
FROM
{node} node
LEFT JOIN {field_data_field_date_norepeat} field_data_field_date_norepeat ON node.nid = field_data_field_date_norepeat.entity_id AND (field_data_field_date_norepeat.entity_type = :views_join_condition_0 AND field_data_field_date_norepeat.deleted = :views_join_condition_1)
WHERE (( (node.status = '1') )AND( (DATE_FORMAT(ADDTIME(STR_TO_DATE(field_data_field_date_norepeat.field_date_norepeat_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-18000)), '%Y-%m') = '2011-05') ))
LIMIT 10 OFFSET 0

ok, I guess this is caused by the calendar module, not the date module. As wernerglinka pointed out there is an error on line 187 of calendar/includes/calendar_plugin_style.inc.

I changed:
//$this->view->query->add_field($table, $delta_field);
$this->view->query->add_field($table, 'delta');

And so far so good.

Here is a link to the corresponding Calendar issue mentioned in #16: #1137538: SQLSTATE[42000]: Syntax error or access violation.

Cheers,

Antoine