Turning aggregation on with existing image or link fields fails with SQLSTATE[42000]

Any help regarding information is welcomed :) http://drupal.org/node/571990
Just posting a non-complete or let's say truncated to useless information probably will not be enough to fix the issue.

Please read the document i linked you.

Moving this here from #2030131: Turning aggregation on with existing image fields fails with SQLSTATE[42000] which I accidently duped. Sorry.

1. Steps to reproduce

• Make a clean fresh Drupal 7 install and stick to the default settings.
• Install ctools and views.
• Create one or two articles and fill in the default image field (=upload an image for each node).
• Create a standard view, type page, for article types.
• Change format to "show fields"
• Add a field "Content: Image", select image style "thumbnail".
• Add another field "Content: Image", select image style "large".
• Until here, preview looks fine.
• Now go to "advanced" an turn on aggregation.
• Update the preview area if necessary and receive an 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 'AS field_data_field_image_, node.created AS node_created, 'node' AS field_data_f' at line 1 See below for the full corresponding query.

Note the trailing dot/underscore which probably points to a missing variable or something else when constructing the field name/alias.

2a. What did not help
Trying to change the aggregation settings for each field did not help, in no way.

2b. How I worked around it

• Go to fields section, select "rearrange" from the dropdown.
• Remove both image fields.
• Save the view.
• Go to fields again, now add the two fields again as described above.
• Problem is gone.

3. Possible cause
Without deeper analysis, I suspect that, when aggregation is active, something happens internally if you add a field. Some settings being made, whatever. This is probably omitted for fields which already exist when aggregation is turned on.

4. Affected/counter checks
As far as I have come, this only affects image fields. All other core fields worked fine and did not cause any problems. Looking at #5, however, reveals that obviously also link fields are affected, probably all fields dealing with file/path stuff?

Thanks anyway for the incredible great work on Views 3. Except for some pretty tiny odds, it is a fantastic tool I would never want to miss again! Thus, if I can be of any assistance in investigations or debug tasks, please let me know!

hth, dave

NB: The full query from my test (after enabling aggregation).
SELECT node.title AS node_title, node.nid AS nid, field_data_field_image. AS field_data_field_image_, node.created AS node_created, MIN(node.nid) AS nid_1, 'node' AS field_data_field_image_node_entity_type
FROM
{node} node
LEFT JOIN {field_data_field_image} field_data_field_image ON node.nid = field_data_field_image.entity_id AND (field_data_field_image.entity_type = 'node' AND field_data_field_image.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN ('article')) ))
GROUP BY node_title, nid, field_data_field_image_node_entity_type, field_data_field_image_, node_created
ORDER BY node_created DESC

Update:

I had to dig deeper into the fields API lately, which brought up a new suspect: May it be that views expects any field to have a "value" property? If so, this would be the cause: Image fields do not have this property, instead they have width, height, fid and so on. Same for URL fields and some other (i suppose file fields as well).

I lack the time for a deeper investigation right now, but probably someone else could test that a bit: Follow my reproduction steps above with different field types like URL or file?

And, if it is: May this rather be a views integration issue for the field types in question or are these field types integrated by views itself (I cannot even check for this right now, sorry).

Hopefully these additional findings can push the issue a bit in the right direction!

I just encountered the exact same issue! I can't have an image field in a view using aggregation. It works just fine without the image field!

Thanks @doitDave! I had this problem with a view and removed some commerce price fields and re-added them and the error went away :)

Thanks - I was banging my head against the wall on this one. Remove the image field, set up aggregation and add the image field back in and it works great now!

This bug is still present in 7.x-3.7. As I see no mention of it in the release notes from 7.x-3.8 I assume it is not fixed yet?

The work around from #6 (2b. How I worked around it) works fine. Thanks doitDave!

This is still an issue with the latest release.

I think editing/configuring the effected fields then applying served as a work-around (didn't want to delete and re-add due to the hassle of re-creating all the configurations).

For me it was text fields, rather than images, that were affected.

@doitDave's Workaround #6 2b saved the day for me, too. Views 7.x-3.8. I was getting the error with a media field and a geofield. After removing them both, saving, turning on aggreggation, and re-adding the fields, the query runs without error.

can confirm #6 work around to work, delete upload image field

however I didn't add it back to this particular query as I was using a file usage: file relationship and file path uri for image.

Yes #6 solution works but in my case it din't worked just by removing image field then saving view then re adding image fields again but after editing Aggregation settings of each fields and saving that aggregation setting solved the issue.

remove and add again thanks.

As noted, this also affects link fields, so updating the title.

I also wonder if the patch from #2036817: Error the moment Aggregation is switched on in Views (for Views Calc) might help inform how to get this bug actually fixed.

I'm using Drupal 8 and had the same problem with an image field already there in a view i wanted to aggeregate, and your solution in #6 worked for me as well.

I deleted the image field, added it again, et voila! Everything worked again. Thanks doitDave!

I am also using D8, and for me the solution #6 talked about doesn't work, I get the following error on enabling grouping and adding the imagefield agan:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'node__field_galleryimages.field_galleryimages_' in 'field list': SELECT node_field_data.title AS node_field_data_title, node.nid AS node_nid, node__field_galleryimages.field_galleryimages_ AS node__field_galleryimages_field_galleryimages_, node_field_data.created AS node_field_data_created, MIN(node_field_data.nid) AS nid FROM {node_field_data} node_field_data INNER JOIN {node} node ON node_field_data.nid = node.nid LEFT JOIN {node__field_galleryimages} node__field_galleryimages ON node_field_data.nid = node__field_galleryimages.entity_id AND (node__field_galleryimages.deleted = :views_join_condition_0 AND node__field_galleryimages.langcode = node_field_data.langcode) WHERE (node_field_data.status = :db_condition_placeholder_2) AND (node_field_data.type IN (:db_condition_placeholder_3)) GROUP BY node_field_data_title, node_nid, node__field_galleryimages_field_galleryimages_, node_field_data_created ORDER BY node_field_data_created DESC LIMIT 10 OFFSET 0; Array ( [:db_condition_placeholder_2] => 1 [:db_condition_placeholder_3] => juicebox_gallery [:views_join_condition_0] => 0 )

#6 Thank uuuuuuuuuuu!!!!.
This is the kind of bug that drives you utterly insane.

I don't have anything to add with respect to diving into the API, but in my case the offending fields were an image field, two geofields and a url field. I noticed that when the SQL Query was displayed in the view there were a number of fields which appeared to be incomplete in the query itself.

For example: LEFT JOIN {field_data_field_website} field_data_field_website ON node.nid = field_data_field_website.entity_id AND (field_data_field_website.entity_type = 'node' AND field_data_field_website.deleted = '0') would instead be:
LEFT JOIN {field_data_field_website} field_data_field_website ON node.nid = field_data_field_website. AND (field_data_field_website.entity_type = 'node' AND field_data_field_website.deleted = '0')

I found that I didn't have to remove aggregation as part of fixing the probem. My steps to fix it were to leave aggregation on, remove the fields which were incomplete in the SQL and then re-add them. As soon as that was done, the view worked perfectly again :)

I can reproduce this on a fresh Drupal 9.4.x Installation, when I try to aggregate the Media-View, which is in Core since 8.x.

Solution in #6 did the trick. I turn on Aggregation and the SQL-Error appears. I remove the Media:Thumbnail-Field, saved the View an add the Media:Thumbnail-Field again. Save the View an everything works fine.

#6 is awesome, confirming this is also present in drupal 10.0.8