SQLSTATE[42000]: Syntax error or access violation: 1064: error in view block when enabling Use aggregations

Can confirm this is an issue for me in views-7.x-3.6. Rolling back to 3.5 brings the views back.

Have tested on 3.7 and 3.x-dev and the issue is present on both. Running drupal 7.21.

Any solutions available for this, as it stands I cannot run the latest views security release due to this problem.

subscribe

If it was working in past, it deserve regression tag.
Would you care to provide full SQL generated by views? I'm not so fluent with views arcana, but not bad on SQL level, maybe I'll be able to get some idea what's wrong.

I do have the same type of error when using the aggregation feature of Views:

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_geolocation_, field_data_field_office_telephone.field_office' at line 1

SELECT node.title AS node_title, node.nid AS nid, node.language AS node_language, field_data_field_office_region.field_office_region_tid AS field_data_field_office_region_field_office_region_tid, field_data_field_office_name.field_office_name_value AS field_data_field_office_name_field_office_name_value, field_data_field_address.field_address_country AS field_data_field_address_field_address_country, field_data_field_address.field_address_administrative_area AS field_data_field_address_field_address_administrative_area, field_data_field_address.field_address_locality AS field_data_field_address_field_address_locality, field_data_field_geolocation. <strong>AS field_data_field_geolocation_, </strong>field_data_field_office_telephone.field_office_telephone_value AS field_data_field_office_telephone_field_office_telephone_val, field_data_field_office_fax.field_office_fax_value AS field_data_field_office_fax_field_office_fax_value, MIN(node.nid) AS nid_1, 'node' AS field_data_field_office_region_node_entity_type, 'node' AS field_data_field_office_name_node_entity_type, 'node' AS field_data_field_address_node_entity_type, 'node' AS field_data_field_geolocation_node_entity_type, 'node' AS field_data_field_office_telephone_node_entity_type, 'node' AS field_data_field_office_fax_node_entity_type
FROM 
{node} node
INNER JOIN {…………

The AS part is truncating the field name:
AS field_data_field_geolocation_,

Yes, in that example I am using the addressfield and geolocation modules.

To fix you need to delete and add again the field (here geolocation), and select some columns.

I confirm that when Aggregation is enabled, somehow the argument of the AS clause are truncated, resulting into an invalid SQL query. Here is my example:

Before aggregation

SELECT node.nid AS nid, field_collection_item_field_data_field_station_livestream.item_id AS field_collection_item_field_data_field_station_livestream_it, node_field_data_field_location_prefecture.nid AS node_field_data_field_location_prefecture_nid, node.title AS node_title, node.language AS node_language, field_collection_item_field_data_field_station_livestream__field_data_field_livestream_bitrate.field_livestream_bitrate_value AS field_collection_item_field_data_field_station_livestream__f, 'node' AS field_data_field_location_prefecture_node_entity_type, 'node' AS field_data_field_station_shortdescription_node_entity_type, 'field_collection_item' AS field_data_field_livestream_url_field_collection_item_entity

After aggregation

SELECT node.nid AS nid, field_collection_item_field_data_field_station_livestream.item_id AS field_collection_item_field_data_field_station_livestream_it, node_field_data_field_location_prefecture__field_data_field_location_prefecture.field_location_prefecture_target_id AS node_field_data_field_location_prefecture__field_data_field_, node.title AS node_title, node.language AS node_language, field_data_field_station_shortdescription.field_station_shortdescription_value AS field_data_field_station_shortdescription_field_station_shor, field_collection_item_field_data_field_station_livestream__field_data_field_livestream_url. AS field_collection_item_field_data_field_station_livestream__f, field_collection_item_field_data_field_station_livestream__field_data_field_livestream_bitrate.field_livestream_bitrate_value AS field_collection_item_field_data_field_station_livestream__f_1, MIN(node_field_data_field_location_prefecture.nid) AS node_field_data_field_location_prefecture_nid, 'node' AS field_data_field_location_prefecture_node_entity_type, MIN(node.nid) AS nid_1, 'node' AS field_data_field_station_shortdescription_node_entity_type, MIN(field_collection_item_field_data_field_station_livestream.item_id) AS field_collection_item_field_data_field_station_livestream_it_1, 'field_collection_item' AS field_data_field_livestream_url_field_collection_item_entity

Error given

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_collection_item_field_data_field_station_livestream__f, field_collectio' at line 1

I am also using field collections as you can see

I'm having this issue also, but the only things different between my dev box (where it works) and production (where I'm seeing the problem), are the MYSQL versions.

Dev = PHP 5.3.10-1ubuntu3.6 && MYSQL 5.5.31-0ubuntu0.12.04.2
Prod = PHP 5.3.2-1ubuntu4.11 && MYSQL 5.1.41-3ubuntu12.10

I was hoping that reverting from 3.5 to 3.7 would fix the issue, but I'm still having the problem:

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_collection_item_field_data_field_criterion_fc__field_d, field_collectio' at line 1

Issue persists, trying to use aggregation

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_upload_image_, field_data_field_campaign_staff_pick.field_ca' at line 1

just marking it as a duplicate of https://www.drupal.org/node/1809584 Turning aggregation on with existing image fields fails with SQLSTATE[42000]

#6 in that thread fixed the problem