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

Created on 27 February 2017, almost 8 years ago

Updated 12 February 2024, 10 months ago

I'm running MySQL 5.7 under Ubuntu 16.04. The query for the affiliated content leads to an error:

Drupal\Core\Database\DatabaseExceptionWrapper: Exception in Affiliated content[affiliated_content]: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'gh.node_field_data.changed' which is not in SELECT list; this is incompatible with DISTINCT: SELECT DISTINCT node_field_data.langcode AS node_field_data_langcode, users_field_data_node_field_data.langcode AS users_field_data_node_field_data_langcode, 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_access} node__field_domain_access ON node_field_data.nid = node__field_domain_access.entity_id AND (node__field_domain_access.deleted = :views_join_condition_0 AND node__field_domain_access.langcode = node_field_data.langcode) WHERE (( (node__field_domain_access.field_domain_access_target_id = :db_condition_placeholder_2) )) ORDER BY node_field_data.changed DESC LIMIT 50 OFFSET 0; Array ( [:db_condition_placeholder_2] => polybau [:views_join_condition_0] => 0 ) in Drupal\views\Plugin\views\query\Sql->execute() (Zeile 1488 in /web/core/modules/views/src/Plugin/views/query/Sql.php).

Switching from MySQL 5.7.30 to MySQL 5.7.31 resolves the issue

however upgrading to MySQL 8.0.28 brings back the ORDER BY clause is not in SELECT list found in MySQL 5.7.30

I have not found the deprecation notice or change notice upstream in the Oracle Documentation that deals with this however I did open a bug with Oracle.

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

🐛 Bug report

Status

Closed: duplicate

Version

9.5

Component

Views →

Last updated about 12 hours ago