ORDER BY clause is not in SELECT list errors - inconsistent MySQL defaults some versions allow some dont

Please be more specific about what query this is.

Where is it generated?
What modules are you using?

I suspect this is a core issue, since that ORDER BY is standard on most content Views.

Pretty sure this is a core issue. Here's the query that is generated by Views for an admin.

SELECT node_field_data.nid AS nid, users_field_data_node_field_data.uid AS users_field_data_node_field_data_uid
FROM 
{node_field_data} node_field_data
INNER JOIN {users_field_data} users_field_data_node_field_data ON node_field_data.uid = users_field_data_node_field_data.uid
LEFT JOIN {node__field_domain_all_affiliates} node__field_domain_all_affiliates ON node_field_data.nid = node__field_domain_all_affiliates.entity_id AND (node__field_domain_all_affiliates.deleted = '0' AND node__field_domain_all_affiliates.langcode = node_field_data.langcode)
WHERE node__field_domain_all_affiliates.field_domain_all_affiliates_value = '1'
ORDER BY node_field_data.changed DESC
LIMIT 50 OFFSET 0

Even though the "Changed" value is a field in the view, it isn't explicit in the SELECT query.

I would expect you to have the same issue with the default /admin/content page, which generates an almost identical query.

SELECT node_field_data.nid AS nid, users_field_data_node_field_data.uid AS users_field_data_node_field_data_uid
FROM 
{node_field_data} node_field_data
INNER JOIN {users_field_data} users_field_data_node_field_data ON node_field_data.uid = users_field_data_node_field_data.uid
WHERE (node_field_data.status = 1 OR (node_field_data.uid = 1 AND 1 <> 0 AND 1 = 1) OR 1 = 1)
ORDER BY node_field_data.changed DESC
LIMIT 50 OFFSET 0

If that isn't compatible with MySQL 5.7, it needs to be reported to core.

As a module maintainer, there is nothing I can do about that.

I was add in settings.php and fixed it

  $databases['default']['default'] ['init_commands'] = ['sql_mode' => "SET sql_mode = ''",];

Yes, but that should still be reported to core developers.

Popping this over to core queue for review and any follow-up. This is not a contrib issue if it does exist.

Running into the same kind of thing in a complex view with aggregation - also I think on a 16.04 instance

Drupal\Core\Database\DatabaseExceptionWrapper: Exception in Buildings[buildings]: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'circle_test.node_field_data.title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT node__field_building_id.field_building_id_value AS node__field_building_id_field_building_id_value, node_field_data_node__og_audience.title AS node_field_data_node__og_audience_title, MIN(node_field_data.nid) AS nid, MIN(node_field_data_node__og_audience.nid) AS node_field_data_node__og_audience_nid FROM {node_field_data} node_field_data LEFT JOIN {node__og_audience} node__og_audience ON node_field_data.nid = node__og_audience.entity_id AND (node__og_audience.deleted = :views_join_condition_0 AND node__og_audience.langcode = node_field_data.langcode) LEFT JOIN {node_field_data} node_field_data_node__og_audience ON node__og_audience.og_audience_target_id = node_field_data_node__og_audience.nid AND node_field_data_node__og_audience.type = :views_join_condition_2 LEFT JOIN {node__field_building_id} node__field_building_id ON node_field_data.nid = node__field_building_id.entity_id AND (node__field_building_id.deleted = :views_join_condition_3 AND node__field_building_id.langcode = node_field_data.langcode) LEFT JOIN {node__field_building_usage} node__field_building_usage ON node_field_data.nid = node__field_building_usage.entity_id AND (node__field_building_usage.deleted = :views_join_condition_5 AND node__field_building_usage.langcode = node_field_data.langcode) WHERE (node_field_data.status = :db_condition_placeholder_7) AND (node_field_data.type IN (:db_condition_placeholder_8)) GROUP BY node__field_building_id_field_building_id_value, node_field_data_node__og_audience_title ORDER BY node_field_data.title ASC LIMIT 25 OFFSET 0; Array ( [:db_condition_placeholder_7] => 1 [:db_condition_placeholder_8] => building [:views_join_condition_0] => 0 [:views_join_condition_2] => campus [:views_join_condition_3] => 0 [:views_join_condition_5] => 0 ) in Drupal\views\Plugin\views\query\Sql->execute() (line 1488 of core/modules/views/src/Plugin/views/query/Sql.php).

Possibly related to #2545480?

+1 This happened to me as well, and fixed with the same recommendation:

$databases['default']['default'] ['init_commands'] = [
'sql_mode' => "SET sql_mode = ''",
];

Setting sql_mode to blank fixed issue for me too.

It might be safer to first check your SQL_MODE and only remove the single issue.

Eg

mysql> SELECT @@sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                             |
+------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ANSI_QUOTES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

If ANSI_QUOTES was your issue, you could then set the mode to the existing elements minus the one issue:

$databases['default']['default'] ['init_commands'] = [
  'sql_mode' => "SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |'",
];

Rather than removing all other options which may have unforeseen consequences elsewhere in your site.

I have a similar problem with /admin/content/node on MySQL 5.7.

The workaround that helped me was to remove ONLY_FULL_GROUP_BY from the sql_mode. I left the rest in:

$databases['default']['default'] ['init_commands'] = ['sql_mode' => "SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'"];

Would you try this patch? It works for me, fixes the problem and all the rest of the site is working as expected.
The patch is for Drupal 8.7.4.

We need some steps to reproduce this on a vanilla Drupal install, and then we need an automated test that follows those steps.

Currently this a little too vague to start looking for a fix.

I get this problem also with MySQL 5.7.29 and not on MySQL 5.5

You can reproduce the error on the media page list "/admin/content/media" and then sort the list by a column (mid, title, create, etc...).

This is because of the default sql-modes in this version of MySql : "ONLY_FULL_GROUP_BY"

Now every query must also define the column in the SELECT to be able to do a GROUP BY on this column.

@Tomefa since the default media list view doesn't use aggregation, you must have done something more than just add the sorting. Any way you can reproduce this on a clean Drupal install? I just tried this on a clean Umami install and I can't break it that way.

Excluding "ONLY_FULL_GROUP_BY" fixed this issue for me on the "/admin/content/media" page.
This issue stems from Views entity field click sorting for tables. Adding any field that is in the base entity field and the table has click sorting enabled. If the column is not in the query's fields then only the orderby is added- as is the case for some fields displayed from the base entity table.

See core/modules/views/src/Plugin/views/field/EntityField.php
"Column is not in query; add a sort on it (without adding the column)."

/**
   * Called to determine what to tell the clicksorter.
   */
  public function clickSort($order) {
    // No column selected, can't continue.
    if (empty($this->options['click_sort_column'])) {
      return;
    }

    $this->ensureMyTable();
    $field_storage_definition = $this->getFieldStorageDefinition();
    $column = $this->getTableMapping()->getFieldColumnName($field_storage_definition, $this->options['click_sort_column']);
    if (!isset($this->aliases[$column])) {
      // Column is not in query; add a sort on it (without adding the column).
      $this->aliases[$column] = $this->tableAlias . '.' . $column;
    }

    $this->query->addOrderBy(NULL, NULL , $order, $this->aliases[$column]);
  }

To fix the issue for any query on the site, I added the following that is the sql mode without "ONLY_FULL_GROUP_BY":

$databases['default']['default'] ['init_commands'] = ['sql_mode' => "SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'"];

More info on sql mode changes in 5.7:
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes

I ran into this issue on my site with the Content Lock module's admin/content/lock page. Like the previous comment said, views click sorting fails with the ONLY_FULL_GROUP_BY connection option enabled. However, I also needed to enable some more modes otherwise the representative node view relationship failed to work (it needed ANSI_QUOTES).

By default Drupal sets the connection modes to:

'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY'

According to the MySQL documentation the ANSI mode contains the ONLY_FULL_GROUP_BY option for MySQL versions 5.7.5+. Therefore I needed to use modes which gave equivalent functionality to the ANSI mode but didn't contain ONLY_FULL_GROUP_BY.

These were the modes I ended up using:

$databases['default']['default'] ['init_commands'] = ['sql_mode' => "SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO'"];

Ideally click sorting should be fixed but I don't know enough about how views work to even begin to figure out where to fix this. Is the reason those fields aren't added to the select portion of the main query because they get pick up via a post-processing operation?

I have aggregate views, not fancy just plain counts of nodes and users. #27 worked for me as MySQL version was 5.7+

I have a View using Distinct which fails only in 5.7 and the sql_modes fixes mentioned do not help. The error is:

Drupal\Core\Database\DatabaseExceptionWrapper: Exception in Clients[clients]: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'sia8.user__field_crm_client_id.field_crm_client_id_value' which is not in SELECT list; this is incompatible with DISTINCT: SELECT DISTINCT users_field_data.uid AS uid, node_field_data_users_field_data.nid AS node_field_data_users_field_data_nid FROM {users_field_data} users_field_data LEFT JOIN {node_field_data} node_field_data_users_field_data ON users_field_data.uid = node_field_data_users_field_data.uid INNER JOIN {user__roles} user__roles ON users_field_data.uid = user__roles.entity_id AND user__roles.deleted = :views_join_condition_0 LEFT JOIN {user__field_crm_client_id} user__field_crm_client_id ON users_field_data.uid = user__field_crm_client_id.entity_id AND user__field_crm_client_id.deleted = :views_join_condition_1 WHERE ((user__roles.roles_target_id IN(:user__roles_roles_target_id__0, :user__roles_roles_target_id__1))) AND ((users_field_data.default_langcode = :db_condition_placeholder_2) AND (user__field_crm_client_id.field_crm_client_id_value != :db_condition_placeholder_3)) ORDER BY user__field_crm_client_id.field_crm_client_id_value DESC LIMIT 25 OFFSET 0; Array ( [:db_condition_placeholder_2] => 1 [:db_condition_placeholder_3] => 0 [:views_join_condition_0] => 0 [:views_join_condition_1] => 0 [:user__roles_roles_target_id__0] => client [:user__roles_roles_target_id__1] => user ) in Drupal\views\Plugin\views\query\Sql->execute() (line 1543 of core\modules\views\src\Plugin\views\query\Sql.php).

@liquidcms
I have also seen this before with a view, there was a mysql configuration option to opt for the previous version MySQL behavior that we changed to fix this, but I can't find it, sorry, keep looking and post your followup here please!

@liquidcms,

check your settings.php

if you're using MySQL 5.7 or higher make sure to avoid using the following configurations in settings.php for the mysql driver options:

 'init_commands' => [
 'sql_mode' => "SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'",
 ],

These settings only seemed to work in Drupal 8 with mysql 5.6 but were not necessary with 5.5 and don't work properly with 5.7

I recently did a Drupal 9 upgrade and had to upgrade from mysql 5.6 to mysql 5.7 , when doing so, the site was unusable until I removed these sql_modes

MariaDB servers did not require this, I tested 10.1 and 10.3 MariaDB , neither of them needed any special sql_mode , so if you can perhaps convince your client to switch to MariaDB if they can't figure this out.

@joseph.olstad Do you know a solution for Drupal 9?
Seems like the init_commands solution doesn't work anymore

@Dennis Cohn, sorry no, I don't know of a solution, unfortunately I think MySQL 5.7 is a steaming pile of dung, much slower than 5.6 and 8. 5.6 had some weirdness also

Possibly go with MySQL 8 or a recent version of MariaDB

I'm using MariaDB , one of my clients is using MySQL

I just observed this issue with

MySQL 8.0.28
and MySQL 5.7.30

The error does NOT occur with MySQL 5.7.31 nor does it occur with MySQL 5.7.32
does not occur with MariaDB 10.3.34-MariaDB-1:10.3.34 either.

However the bug is exposed by MySQL 8.0.28 which unfortunately is the latest release of MySQL

I openned a bug report with Oracle however they told me it is 'not a bug'

https://bugs.mysql.com/bug.php?id=106813

I'm in the process of digging up some more sql debug information to see if the query comes out different with different versions of MySQL and MariaDB (so far MariaDB seems like the one that has the least issues for me)

Edit:
I dug up the exact query that was behaving differently in different versions of MySQL, crashing in one, not the other, added details upstream, so waiting for the upstream feedback. Drupal in my tests has been generating a consistent query and I have put that query in the linked issue.

IMHO, this is for sure a MySQL bug, their query execution is inconsistent between versions, crashing on one, not the other.

https://support-acquia.force.com/s/article/1500001586101-Drupal-throws-M...

https://stackoverflow.com/a/39353160

as mentioned above in comment as a workaround to the problem solution from comment #26 and #27 works for 5.7.30

  'driver' => 'mysql',
  'init_commands' => ['sql_mode' => "SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO
_CREATE_USER,NO_ENGINE_SUBSTITUTION'"],

This same setting will cause MySQL 8.0.28 to crash , no queries will execute. It's only a good workaround for 5.7.30.

ok, a bit more troubleshooting, I have this working on 8.0.28 and 5.7.30:

note:

workaround for MySQL 8.0.28 is to do the following:

add this to your settings.php default default db settings *EDITTED*

  'driver' => 'mysql',
  'init_commands' => ['sql_mode' => "SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'"],

workaround for MySQL 5.7.30 is to add this instead (note: this doesn't work on MySQL 8.0.28, only 5.7.30):

  'driver' => 'mysql',
  'init_commands' => ['sql_mode' => "SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO
_CREATE_USER,NO_ENGINE_SUBSTITUTION'"],

doing this requires no patching.

NOTE: this issue appears to be the same or very similar to #2894688: Content View with Multilingual modules and DISTINCT throws an SQL error (3065) on MySQL 5.7+

No surprise for MySQL 8, sql mode NO_AUTO_CREATE_USER SQL is obsolete.

Suggest to remove ONLY_FULL_GROUP_BY from sql mode rather then setting invalid modes.

EDIT: See https://stackoverflow.com/a/63842659

@mmjvb, you are correct,

I excluded NO_AUTO_CREATE_USER_SQL which you correctly state is obsolete for MySQL 8.0.28.

Now the query runs despite the order by column not being in the select.

'driver' => 'mysql',
    'init_commands' => ['sql_mode' => "SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'"],

So this is a valid workaround for MySQL 8.0.28

with that said, maybe it's possible to fix the issue at the root cause.

Comment #26 explains what appears to be the root cause (no mention of a patch though):

#2856270-26: ORDER BY clause is not in SELECT list errors - inconsistent MySQL defaults some versions allow some dont

Not convinced about the root cause being referred to. Looks like no proper understanding of the feature.

It is true that MySQL 5.7 includes the sql-mode ONLY_FULL_GROUP_BY. That is in addition to improving detecting SQL errors. The workaround to remove the mode ONLY_FULL_GROUP_BY is only suggested for queries that you have no control over. Which means you can only hope that the result is valid. The recommended solution is to fix the query. That relies on proper investigation on which queries are considered wrong when ONLY_FULL_GROUP_BY is set. Then make sure ANY_VALUE is used or do whatever is needed to make it clear that column is depending on ordered or grouped list.

@mmjvb there's several patches in several linked issues and none of them worked in my case and I even tried cooking one up myself.

None of these worked for sorting by moderation state (my use case):

#2894688: Content View with Multilingual modules and DISTINCT throws an SQL error (3065) on MySQL 5.7+
#2815881-145: Switching on aggregation generates fatal "Column not found: 1054 Unknown column" SQL error when using multi-column Fields

if you find one that works let me know.

Also @mmjvb, the same configuration/same code base running on MySQL 5.7.30 ends up with the query error , however MySQL 5.7.31 doesn't have the same error, unless somehow the server configuration was different but I verified that a few times. MySQL 8.0.28 gave me a different result than 5.7.31.

I have another test environment with MariaDB , didn't have to use any special sql_mode.

Essentialy the error is telling you the query is wrong. Telling the server not to check the query is not something you should do, you should fix the query. The query is wrong because it reports a column at a time there could be different values. Picking one is considered wrong. That is why you need something like MINIMUM or MAXIMUM or ANY_VALUE to convince the server you are reporting the right data.

Obviously, bug fixes make problems go away, they are meant to do that. That would explain earlier versions to report errors. Hope it stays fixed in 5.7.32+

There wouldn't be a reason for having MariaDB when it works exactly the same as MySQL !!
So, it has to do things differently! The differences is why you would prefer one above the other.

Have seen queries that REPLACE 'ONLY_FULL_GROUP_BY' by '' to remove that mode from the modes in use. Suggest to Google for that when you insist on doing the wrong thing.

@mmjvb,
It's not me doing the "wrong" sql it's core. Being "wrong" in this case is debatable given the inconsistencies of implementations between versions of MySQL with regards to defaults.

With that said, I'm leaving it to the views module experts to write a patch or to determine if a patch for views should be created or a patch for the db abstraction layer should be created. In my case where my view is generating the moderation_state order by I'm inclined to suspect it's something for the views module or the content_moderation module code to adjust.

core/modules/content_moderation/src/ViewsData.php

   /**
     * Returns the views data.
     *
     * @return array
     *   The views data.
     */
    public function getViewsData() {
      $data = [];
  
      $entity_types_with_moderation = array_filter($this->entityTypeManager->getDefinitions(), function (EntityTypeInterface $type) {
        return $this->moderationInformation->isModeratedEntityType($type);
      });
  
      foreach ($entity_types_with_moderation as $entity_type_id => $entity_type) {
        $table = $entity_type->getDataTable() ?: $entity_type->getBaseTable();
  
        $data[$table]['moderation_state'] = [
        'title' => t('Moderation state'),
        'field' => [
          'id' => 'moderation_state_field',
          'default_formatter' => 'content_moderation_state',
          'field_name' => 'moderation_state',
        ],
        'filter' => ['id' => 'moderation_state_filter', 'allow empty' => TRUE],
        'sort' => ['id' => 'moderation_state_sort'],
      ];

      $revision_table = $entity_type->getRevisionDataTable() ?: $entity_type->getRevisionTable();
      $data[$revision_table]['moderation_state'] = [
        'title' => t('Moderation state'),
        'field' => [
          'id' => 'moderation_state_field',
          'default_formatter' => 'content_moderation_state',
          'field_name' => 'moderation_state',
        ],
        'filter' => ['id' => 'moderation_state_filter', 'allow empty' => TRUE],
        'sort' => ['id' => 'moderation_state_sort'],
      ];
    }

    return $data;

perhaps what needs to be added here is something like this (refactored from the user module):

  $data[$revision_table]['moderation_state']['argument'] += [
      'name table' => 'content_moderation_state_field_data',
      'name field' => 'moderation_state',
      'empty field name' => 'draft',
    ];

I'm not quite sure what tells views to add the field to the select but if it had a name table and name field value maybe it would work, I haven't tried this yet ***EDIT***(tried it, does nothing)***EDIT*** definately needs a lot of time soaked into this one to figure out, should be split into it's own issue.

For those comming here wanting to sort moderation state, there's a patch here:
#3221622-6: Unable to sort by current moderation state. Column not found

For me help add moderation_state field to Sort criteria section, then sort in table header stop return error.

Suspect this is a duplicate issue:

There's what appears to be a simpler fix in
#3221622: Unable to sort by current moderation state. Column not found

https://www.drupal.org/files/issues/2022-04-06/content_moderation-fix-Mo...
Works for me

See patch 11, it is a simpler approach.
#3221622: Unable to sort by current moderation state. Column not found