Closed (fixed)
Project:
Date
Version:
5.x-1.4
Component:
Code
Priority:
Critical
Category:
Bug report
Assigned:
Unassigned
Reporter:
Created:
30 Mar 2007 at 18:14 UTC
Updated:
14 May 2007 at 17:21 UTC
If I create a view that uses a date field as a filter, I get an SQL error when loading the view page because the date field name is not included in the SQL statement. (My date field label is "Event Date".)
For the SQL statement to execute properly, this section of SQL:
REPLACE(node_data_field_event_date.,'T',' ')
should be:
REPLACE(node_data_field_event_date.field_event_date_value,'T',' ')
So, where and how can we fix this in the code?
Here is the entire error message returned to the browser.
* user warning: 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 ''T',' ') + INTERVAL (-18000) SECOND) >= (NOW() + INTERVAL (-18000) SECOND))' at line 1 query: SELECT count( DISTINCT(node.nid)) FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN node_news node_data_field_event_date ON node.vid = node_data_field_event_date.vid WHERE (node.status = '1') AND (term_node.tid = '21') AND (( REPLACE(node_data_field_event_date.,'T',' ') + INTERVAL (-18000) SECOND) >= (NOW() + INTERVAL (-18000) SECOND)) in /path/path/path/includes/database.mysql.inc on line 172.
* user warning: 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 ''T',' ') + INTERVAL (-18000) SECOND) >= (NOW() + INTERVAL (-18000) SECOND)) ORD' at line 1 query: SELECT DISTINCT(node.nid), node.created AS node_created_created FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN node_news node_data_field_event_date ON node.vid = node_data_field_event_date.vid WHERE (node.status = '1') AND (term_node.tid = '21') AND (( REPLACE(node_data_field_event_date.,'T',' ') + INTERVAL (-18000) SECOND) >= (NOW() + INTERVAL (-18000) SECOND)) ORDER BY node_created_created DESC LIMIT 0, 15 in /path/path/path/includes/database.mysql.inc on line 172.
(This is a date type, js popup widget, site timezone, EST, No 'multiple' option, No 'required' option.)
Comments
Comment #1
karens commentedThis is a Views query which is missing some date info. Any time you see things like this it indicates you made an update to either CCK or the Date module (a database update or changing a field from multiple to not multiple or some other change) that interfered with something in your view. You need to open up the View that is throwing the error and make sure all your fields, filters, arguments and content types look right. Look especially for items that have no name and delete them and replace them with the correct item from the field list, then re-save the view.
This is because CCK is not completely able to update everything in Views when the database changes. This will be fixed in future versions of Views and CCK, but for now you need to manually update your Views when you run into errors like this.
Comment #2
edhel commentedI have the same problem with 1.4 module version. Re-save didn't help. I suppose that this report is related with http://drupal.org/node/132517
In 1.2 there is not such problem.
Comment #3
(not verified) commentedComment #4
SpatnyNick commentedI have also this problem with versions 1.3, 1.4, 1.5 for D5.1. Version 1.2 works correctly.
Comment #5
ronan commentedFor anyone still having this problem, make sure you are using the latest version of both CCK and Views. Obvious I know, but it solved it for me.