DISTINCT throws error
| Project: | Views |
| Version: | 6.x-2.3 |
| Component: | Miscellaneous |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed |
| Issue tags: | select distinct as distinct bug |
Jump to:
When I add the "Distinct" option from 'No' to 'Yes' in the default settings of the calendar view, I get the following error:
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 'DISTINCT(node.nid), node.changed AS node_changed, node.title AS node_title' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node.changed AS node_changed, node.title AS node_title, node.type AS node_type FROM node node WHERE (node.status <> 0) AND ((DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node.changed), SEC_TO_TIME(-28800)), '%Y-%m') <= '2009-02' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node.changed), SEC_TO_TIME(-28800)), '%Y-%m') >= '2009-02')) ORDER BY node_changed ASC in /home/cx4443/public_html/drupal-6.9/sites/all/modules/views/includes/view.inc on line 735.
The query below that appears as follows:
SELECT DISTINCT(node.nid) AS nid,
node.changed AS node_changed,
node.title AS node_title,
node.type AS node_type
FROM node node
WHERE (node.status <> 0)
AND ((DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node.changed), SEC_TO_TIME(-28800)), '%Y-%m') <= '2009-02' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node.changed), SEC_TO_TIME(-28800)), '%Y-%m') >= '2009-02'))
ORDER BY node_changed ASC
Then when I save the view, I get this error:
* 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 'DISTINCT(node.nid), node_data_field_date.field_date_value AS node_data_field_' at line 1 query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node_data_field_date.field_date_value AS node_data_field_date_field_date_value, node_data_field_date.field_date_value2 AS node_data_field_date_field_date_value2, node_data_field_date.field_date_rrule AS node_data_field_date_field_date_rrule, node_data_field_date.delta AS node_data_field_date_delta, node.title AS node_title, node.vid AS node_vid, node.type AS node_type, node.changed AS node_changed FROM node node LEFT JOIN content_field_date node_data_field_date ON node.vid = node_data_field_date.vid WHERE ((node.status <> 0) AND (node.type in ('event'))) AND (DATE_FORMAT(ADDTIME(node_data_field_date.field_date_value, SEC_TO_TIME(-28800)), '%Y-%m-%d') >= '2009-02-13') ORDER BY node_changed ASC ) count_alias in /home/cx4443/public_html/drupal-6.9/sites/all/modules/views/includes/view.inc on line 705.
* 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 'DISTINCT(node.nid), node_data_field_date.field_date_value AS node_data_field_' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node_data_field_date.field_date_value AS node_data_field_date_field_date_value, node_data_field_date.field_date_value2 AS node_data_field_date_field_date_value2, node_data_field_date.field_date_rrule AS node_data_field_date_field_date_rrule, node_data_field_date.delta AS node_data_field_date_delta, node.title AS node_title, node.vid AS node_vid, node.type AS node_type, node.changed AS node_changed FROM node node LEFT JOIN content_field_date node_data_field_date ON node.vid = node_data_field_date.vid WHERE ((node.status <> 0) AND (node.type in ('event'))) AND (DATE_FORMAT(ADDTIME(node_data_field_date.field_date_value, SEC_TO_TIME(-28800)), '%Y-%m-%d') >= '2009-02-13') ORDER BY node_changed ASC LIMIT 0, 5 in /home/cx4443/public_html/drupal-6.9/sites/all/modules/views/includes/view.inc on line 731.
No data appears in the block that previously showed the same event 5 or 6 times. The export of the view is quite long but I can paste that if it would be helpful. I'm running 6.x-2.0-rc6 Calendar module.

#1
The error seemed to be caused by using the Updated date for the argument instead of the CCK date that the node uses for the event's start date. the repeating issue was my own stupidity. it was a repeating event.
#2
see also: http://drupal.org/node/284392#comment-1049183