Group columns (additional) = Entity ID results in sql error + aggregation type MIN, MAX, AVG and SUM don't work

I had the same problem (View configuration 1)
I applied patch from http://drupal.org/node/1194900 and worked

sorry, I put back the issue title

Could you please provide an export which allows to be used by everyone?

This would save quite some time trying to reproduce the problem.

1) Retest with 7.x-3.x-dev.
2) If the problem persists, provide a view export.

Sub. The problem is "entity id" and other fields aren't enforcing SQL safe column names. We either need to escape them with tick marks "`" or double check that the real column name is used, instead of the column Title.

I just tried this and it worked fine, SUM aggregation was correct as long as I grouped the results correctly.

However, it also totally busted the Chart view provided by the Google Chart API. Reporting over there as well.

I am having what I think is the same problem.

Whether or not they are using compliant SQL may not specifically the issue here (or at least in my case).

The code in views/modules/field/views_handler_field_field.inc around line #523, reads:

<?php
$group_columns = array(
  'entity id' => t('Entity ID'),
) + drupal_map_assoc(array_keys($this->field_info['columns']), 'ucfirst');
?>

The problem here is that looking at my database table columns, there is no 'entity id' column, but there is an 'entity_id' column (note the underscore).

You can probably confirm if this is true for your case as well by looking at the columns of the table 'field_data_field_bedrag'.

I switched it to 3.x-dev so that the patch can be tested.

FYI:
I am also having different problems with aggregation after this issue is fixed.
I am starting to believe the the aggregation is not well tested and may have a number of other SQL issues than just this.

Oops I sent the completely wrong patch.

There seems to have been more cases of 'entity id' that should have been 'entity_id'.
I corrected those in the new patch and hopefully I did not include anything that should not have been renamed.

EDIT:
Also, I am seeing the new problem:
SQLSTATE[42601]: Syntax error: 7 ERROR: non-integer constant in GROUP BY LINE 10: GROUP BY node.title, node.nid, 'node', field_data_field_even... ^

The problem is that they 'node' (with the quotes) are being passed as a SELECT column.
This is going to take some time to find given how common the phrase 'node' is.

The second issue is a result of invalid code defined at or around:
views/modules/field/views_handler_field_field.inc:156:
<?php $this->aliases['entity_type'] = $this->query->add_field(NULL, "'$entity_type'", $entity_type_alias); ?>

The first two parameters are completely wrong.
The first parameter should be the table and not NULL.
The second argument has the quoted entity type, instead this should be 'entity_type'.

The corrected code should look like:
<?php $this->aliases['entity_type'] = $this->query->add_field($this->definition['table'], 'entity_type', $entity_type_alias); ?>

Here is the re-made patch that fixes this second problem (this patch includes the changes from the previous patch as well).
Once this patch is applied, I found no new problems and no SQL errors.

Please review this patch and my notes to make sure I made no mistakes.

+++ b/modules/field/views_handler_field_field.incundefined
@@ -147,13 +147,13 @@ class views_handler_field_field extends views_handler_field {
+    $this->aliases['entity_type'] = $this->query->add_field($this->definition['table'], 'entity_type', $entity_type_alias);

This probably changes the bevahior. Currently it does't need to have a field joined on the actual field table, but it uses the entity_type as something like a variable used later.

In general maybe post some sql queries of before and after and how you expect it etc.

In general thanks to work on this issue!

This probably changes the bevahior. Currently it does't need to have a field joined on the actual field table, but it uses the entity_type as something like a variable used later.

I was never aware that one could select a string in such a way, or perhaps I so rarely used it that I forgot such a thing existed.

So after playing around, I realize that I was not using a clean source code base to test this problem.
I still had my attempted fix for the issue here applied: https://drupal.org/node/1331056#comment-5341796
I believe this is a case of me shooting my own foot.
After using a clean source base and then applying the fix here, I was able to confirm that the patch from the said thread causes the second problem.

I removed the changes from #12 and will address the #12 problem in the appropriate thread.

Regarding #12 i'm still convinced of "this works as expected".

The problem with renaming parameters here is that some things might break. I would not change entity id to entity_id, though
add some special check in

      if ($this->options['group_column'] != 'entity id') {
        $options = array($this->options['group_column'] => $this->options['group_column']);
      }

      $options += $this->options['group_columns'];

What do you think about this?

Ugh, looking at this thread after a month of taking a break from it I am beginning to think that the wading through the SQL standards has caused some minor form of insanity...

The problem with 'entity_id' vs 'entity id' is that I see no instances of entity (space) id in the code nor any instances of entity (space) id in the database tables (aside from the locations found and changed by the #14 patch).

This confuses me as, entity (underscore) id appears everywhere else.
This makes me believe that the (space) should be an (underscore).

The suggested fix in #15 is sound, but it only addresses that if the column does or does not exist.
Perhaps I am looking in all the wrong places for entity (space) id, but I still believe that the code is intended to use the entity (underscore) id.

When I apply the said patch, the views seem to function as expected whereas there are database errors without the said patch.

+++ b/modules/field/views_handler_field_field.incundefined
@@ -520,7 +520,7 @@ class views_handler_field_field extends views_handler_field {
+      'entity_id' => t('Entity ID'),

One thing i'm wondering here is what will happen if someone with an already configured view will come and try to submit the existing form, doesn't he get a "invalid option" error? This seems to be kind of a big problem

Thats exactly the problem I get before changing 'entity id' to 'entity_id'.

Based on the original post and my post, the view never worked in the first place on MySQL or on Postgresql.
I cannot see how people got a functional view and if they did, it very likely means that they are pulling in incorrect results.
There was never an 'entity id' column an any database table for any project I can find thus far, including drupal core.

MySQL seems to drop the 'entity id' and convert it into 'entityid'.
I cannot find an 'entityid' in any of the existing code either.
You can easily see the issue in the original bug report sql query at the spot that looks like:
SUM(field_data_field_bedrag.entity id) AS field_data_field_bedrag_entityid
There is both the 'entity id' and 'entityid' and both are clearly invalid.

grep -r '\<entityid\>' /var/www/html/
returns nothing.

grep -r '\<entity id\>' /var/www/html/
returns only comments.

grep -r '\<entity_id\>' /var/www/html/
returns a whole bunch of uses, including numerous *.install files, such as in /var/www/html/modules/field/modules/field_sql_storage/field_sql_storage.install

Yeah i seem to have overthought this, after some testing i committed that to 7.x-3.x
Thanks for the patch again!

Since upgrading to views 3.3, some of my views are no longer working.
I suspect this patch to be the culprit.

The error is:

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 'id, field_revision_field_katern_entity id, field_revision_field_pagenr_entity id' at line 9

"field_revision_field_katern_entity id" and "field_revision_field_pagenr_entity id" are clearly wrong.
the database tables are "field_revision_field_katern" and "field_revision_field_pagenr", and they have "entity_id" columns.

can you review this patch again. maybe the wrong version was committed?

things revert to normal when I revert this change:

if ($this->options['group_column'] != 'entity_id')

to
if ($this->options['group_column'] != 'entity id')

will attach a patch in a minute

So we have a case where 'entity id' is actually installed as a column name somewhere?

I am wondering if the problem is related to that the view was created before the 3.3 version.
Have to tried editing and resaving the view?
The problem could be an issue where the 'entity id' was saved in your view and even though the new version changed the behavior, the view was not updated.

If this is true, then this might call for an sql update addition to the appropriate views.install file?

@jbeckers, could you to try one of the broken views without your partial revert patch and see what happens after you edit and resave the grouping option for the fields in that view? It should be under the "Aggregate settings" for each field. The "Group column" should be reset to "Entity ID" and if you are using "Entity ID" for "Group columns (additional)", that probably needs to be recheck as well (assuming of course that a the given field was grouping by entity id).

@thekevinday: no, I checked the DB like I said in my previous comment, and it clearly states entity_id (with the underscore) as the column name. Or do I have to look at another table?

Will try your suggestion later this week.

    if ($this->options['group_column'] == 'entity id') {
      $this->options['group_column'] = 'entity_id';
    }

This code will automatically convert the existing value to the new setting, so the previous patch is not needed.

Just by way of confirmation:

I had applied an earlier patch to Views 7.x-3.1 to fix this. Upgrading to Views 7.x-3.3 reverted to producing SQL "entityid" field errors for me when using aggregation. Using Views 7.x-3.x-dev (March 08) fixed it, thanks.

I upgraded to Views 3.3 and began getting the same SQL error due to "entity id" appearing.

As suggested by thekevinday in #24...

Editing and re-applying the aggregation settings to each of the offending fields, then re-saving the view updated the query, fixing the view/SQL error.

Thankyou #28, I resaved the fields that had "entity id" in the query and that fixed it for me too. I had a 42000 You have an error in your SQL syntax.