We're having problem with a combination of the core language modules and the Content view using DISTINCT. Here are steps to reproduce it in a minimal 8.3.5 site installation. It must be using MySQL 5.7+ with ONLY_FULL_GROUP_BY enabled.

Enable the Multilingual modules:

Configuration Translation config_translation
Content Translation content_translation
Interface Translation locale
Language language

Navigate to /admin/config/regional/language/detection and deselect URL

Navigate to /admin/config/regional/language and Add Language. Select Danish and wait for the translation files to download

Navigate to /admin/config/regional/language/detection/selected and set the default language to Danish

Navigate to /admin/structure/views/view/content and select Distinct in the query settings (Indstillinger for forespørgsel:Indstillinger / Særskilt)

Select Content (Indhold)

Drupal\Core\Database\DatabaseExceptionWrapper: Exception in Indhold[content]: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'drupal8.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 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; Array ( ) i Drupal\views\Plugin\views\query\Sql->execute() (linje 1488 af /home/ubuntu/deleteme/drupal83/drupal/web/core/modules/views/src/Plugin/views/query/Sql.php).

The issue is related to the use of DISTINCT with ONLY_FULL_GROUP_BY in MySQL 5.7, and the fact that the ORDER BY column is not in the select.

This was performed on Ubuntu 16.04 with the following

Drupal 8.3.5
PHP 7.0.19-1+deb.sury.org~xenial+2
MySQL 5.7.18-0ubuntu0.16.04.1
nginx/1.10.0

CommentFileSizeAuthor
#38 sql_error_distinct_order_D10-2894688-32.patch796 bytessuramita
#31 sql_error_distinct_order_D8_9_18-2894688-31.patch869 byteskaloyan.damyanov
#27 sql_error_distinct_order_by-2894688-27.patch874 bytesvredko
#23 sql_error_distinct_order_by2894688-23.patch688 bytesDom.
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

wurban55 created an issue. See original summary.

cilefen’s picture

cilefen CreditAttribution: cilefen commented
Priority: Normal » Major
Issue tags: -views
Parent issue: #2874942: Drupal 8 Views Distinct throws an SQL error »
Related issues: +#2874942: Drupal 8 Views Distinct throws an SQL error

Exceptions caused by clicking around in the UI can usually get major priority.

Version: 8.3.x-dev » 8.4.x-dev

Drupal 8.3.6 was released on August 2, 2017 and is the final full bugfix release for the Drupal 8.3.x series. Drupal 8.3.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.4.0 on October 4, 2017. (Drupal 8.4.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.4.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

edurenye’s picture

edurenye CreditAttribution: edurenye at ENDPHASYS Technologies commented
Parent issue: » #2616488: [meta] MySQL 5.7 / MariaDb 10.1.* support
mvwensen’s picture

mvwensen CreditAttribution: mvwensen commented

Same issue over here with MySQL 5.7, temporary fixed it by adding in settings.php:

$databases['default']['default'] ['init_commands'] = array(
  'sql_mode' => "SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'",
);
drupalmind’s picture

drupalmind CreditAttribution: drupalmind as a volunteer commented

I am working on triaging this issue at Event, following the instructions in #2474049: [meta] Major issue triage: @fgm is helping as our mentor.

drupalmind’s picture

drupalmind CreditAttribution: drupalmind as a volunteer commented

I could not produces this bug on drupal 8.5.x and drupal 8.3.7 and drupal 8.3.5 on mysql 5.5. Unable to update mysql for now.

Version: 8.4.x-dev » 8.5.x-dev

Drupal 8.4.4 was released on January 3, 2018 and is the final full bugfix release for the Drupal 8.4.x series. Drupal 8.4.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.5.0 on March 7, 2018. (Drupal 8.5.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.5.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.6.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.5.x-dev » 8.6.x-dev

Drupal 8.5.6 was released on August 1, 2018 and is the final bugfix release for the Drupal 8.5.x series. Drupal 8.5.x will not receive any further development aside from security fixes. Sites should prepare to update to 8.6.0 on September 5, 2018. (Drupal 8.6.0-rc1 is available for testing.)

Bug reports should be targeted against the 8.6.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.7.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

andypost’s picture

andypost
he/him
Primary language Russian
CreditAttribution: andypost as a volunteer and at Skilld commented
Version: 8.6.x-dev » 8.7.x-dev
Issue tags: +MySQL 8

This still reproduce on mysql 5.7+ and I bet the same for pgsql

Version: 8.7.x-dev » 8.8.x-dev

Drupal 8.7.0-alpha1 will be released the week of March 11, 2019, which means new developments and disruptive changes should now be targeted against the 8.8.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.8.x-dev » 8.9.x-dev

Drupal 8.8.0-alpha1 will be released the week of October 14th, 2019, which means new developments and disruptive changes should now be targeted against the 8.9.x-dev branch. (Any changes to 8.9.x will also be committed to 9.0.x in preparation for Drupal 9’s release, but some changes like significant feature additions will be deferred to 9.1.x.). For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

sic’s picture

sic CreditAttribution: sic commented

Having the issue on mysql 5.7 (mamp).

Version: 8.9.x-dev » 9.1.x-dev

Drupal 8.9.0-beta1 was released on March 20, 2020. 8.9.x is the final, long-term support (LTS) minor release of Drupal 8, which means new developments and disruptive changes should now be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

liquidcms’s picture

liquidcms CreditAttribution: liquidcms commented

I have this issue with: Drupal 8.8.4 and MySql 5.7.24

At the very least; should Drupal 8 requirements not be corrected where it says Drupal 8 works with MySQL 5.5.3/MariaDB 5.5.20/Percona Server 5.5.8 or higher as it would seem it is not compatible with MySQL 5.7

aiphes’s picture

aiphes
Primary language French
CreditAttribution: aiphes commented

after update php 7.2 to 7.3, I get mysql 5.7 too and th eerror on views content happen.

yfma’s picture

yfma CreditAttribution: yfma commented

We are having this issue. Drupal 8.8.8, MySQL 5.7.30

Version: 9.1.x-dev » 9.2.x-dev

Drupal 9.1.0-alpha1 will be released the week of October 19, 2020, which means new developments and disruptive changes should now be targeted for the 9.2.x-dev branch. For more information see the Drupal 9 minor version schedule and the Allowed changes during the Drupal 9 release cycle.

tobiberlin’s picture

tobiberlin
Primary language German
Location Berlin, Germany
CreditAttribution: tobiberlin at Abgeordnetenwatch commented

Same here: Drupal 8.9.10 with Configuration Translation, Interface Translation and Language enabled (not Content Translation). When I choose "Distinct" for the view listing nodes it gives this error

Mysql 5.7.26

But this does not only happen with nodes but also with views for other entity types. As soon as "distinct" is set this errors appears. Another example is a view with a custom entity type which references an user in a field. This reference is brought into the view with a relationship. The error message:

SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'drupal8.users_field_data_politician.access' which is not in SELECT list; this is incompatible with DISTINCT

This only happens on my local machine. On our develop/testing instance there is MariaDB 5.5.5 running. On this machine the error does not appear but the "distinct" flag does not lead to reduction of duplicated results.

I found this on Stackoverflow stating that changing the sql-mode for MySQL would solve these problems but at least for me it did not: https://stackoverflow.com/questions/36829911/how-to-resolve-order-by-cla...

anish.a’s picture

anish.a CreditAttribution: anish.a at QBurst commented

Same issue happens for me. As soon as MySQL is upgraded to 5.7, this issue appeared.

anish.a’s picture

anish.a CreditAttribution: anish.a at QBurst commented
Title: Drupal 8 Content View with Multilingual modules and DISTINCT throws an SQL error (3065) » Drupal 8 Content View with Multilingual modules and DISTINCT throws an SQL error (3065) on MySQL 5.7+
anish.a’s picture

anish.a CreditAttribution: anish.a at QBurst commented

This issue is preventing us in upgrading our production environments. Any solution, or pointers to fix this issue?

Dom.’s picture

Dom. CreditAttribution: Dom. as a volunteer and at ACINO commented
Status: Active » Needs review
FileSize
sql_error_distinct_order_by2894688-23.patch688 bytes

Hi !
I had the same issue with the admin/people view on a multilingual environnement. Adding the 'DISTINCT' option while using 'ORDER BY' the created field of user entity gave me the same SQL error as described
Attached is a very simple patch that helped me move on with this issue. Let me see what testbot think of it.

Status: Needs review » Needs work

The last submitted patch, 23: sql_error_distinct_order_by2894688-23.patch, failed testing. View results

weseze’s picture

weseze CreditAttribution: weseze as a volunteer commented

UPDATED/DELETED MY ORIGINAL ANSWER because I initially reported the patch was a good fix, but it seems to break other things...

Only thing that seems to fix it for us is to revert to MySQL 5.6 or override the sql_mode with "STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE".

Neither seems like the correct solution. Will report back if we find a better solution.

Version: 9.2.x-dev » 9.3.x-dev

Drupal 9.2.0-alpha1 will be released the week of May 3, 2021, which means new developments and disruptive changes should now be targeted for the 9.3.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

vredko’s picture

vredko CreditAttribution: vredko as a volunteer and commented
FileSize
sql_error_distinct_order_by-2894688-27.patch874 bytes

Thanks @Dom. for the patch. Works good, but views where don't use DISTINCT are crash.
New patch attached

vredko’s picture

vredko CreditAttribution: vredko as a volunteer and commented
Status: Needs work » Needs review
fjgarlin’s picture

fjgarlin CreditAttribution: fjgarlin as a volunteer and commented
Status: Needs review » Reviewed & tested by the community

In my case, changing the "sql_mode" didn't seem to have any effect (probably due to hosting configuration?) but the patch did indeed work.
I tested views with and without "distinct" and the results were as expected.

MySQL version: 5.7.33-google-log (google cloud mysql).

Marking this as RTBC.

catch’s picture

catch
he/him
Primary language English
CreditAttribution: catch at Third and Grove commented
Status: Reviewed & tested by the community » Needs work
Issue tags: +Needs tests

Is there an reason not to always add the table alias and column?

Also this could use some test coverage.

kaloyan.damyanov’s picture

kaloyan.damyanov CreditAttribution: kaloyan.damyanov commented
FileSize
sql_error_distinct_order_D8_9_18-2894688-31.patch869 bytes

Version 8.9.18

Version: 9.3.x-dev » 9.4.x-dev

Drupal 9.3.0-rc1 was released on November 26, 2021, which means new developments and disruptive changes should now be targeted for the 9.4.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

joseph.olstad’s picture

joseph.olstad
Primary language French
CreditAttribution: joseph.olstad commented

workaround for MySQL 8.0.28 is to do the following:

add this to your settings.php default default db settings

  '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, also note, this is not necessary if you are using 5.7.31 or 5.7.32 both of which do not require a settings change for whatever reason):

  '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 #2856270: ORDER BY clause is not in SELECT list errors - inconsistent MySQL defaults some versions allow some dont

Version: 9.4.x-dev » 9.5.x-dev

Drupal 9.4.0-alpha1 was released on May 6, 2022, which means new developments and disruptive changes should now be targeted for the 9.5.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.5.x-dev » 10.1.x-dev

Drupal 9.5.0-beta2 and Drupal 10.0.0-beta2 were released on September 29, 2022, which means new developments and disruptive changes should now be targeted for the 10.1.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 10.1.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch, which currently accepts only minor-version allowed changes. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

mstrelan’s picture

mstrelan CreditAttribution: mstrelan at PreviousNext for Service NSW commented
Title: Drupal 8 Content View with Multilingual modules and DISTINCT throws an SQL error (3065) on MySQL 5.7+ » Content View with Multilingual modules and DISTINCT throws an SQL error (3065) on MySQL 5.7+
Status: Needs work » Postponed (maintainer needs more info)

I suspect this is fixed by #3195178: Views table format sorting + distinct results in a SQL error on some db engines, can anyone confirm if this is still applicable?

suramita’s picture

suramita CreditAttribution: suramita commented
FileSize
sql_error_distinct_order_D10-2894688-32.patch796 bytes

Recreated the patch to apply on Drupal 10 core views.

suramita’s picture

suramita CreditAttribution: suramita commented
Status: Postponed (maintainer needs more info) » Needs review
Lendude’s picture

Lendude
Primary language Dutch
Location Amsterdam
CreditAttribution: Lendude as a volunteer and at iO commented
Status: Needs review » Postponed (maintainer needs more info)

The question was if this still needs a patch at all, is this still an issue or was this fixed by the issue linked by @mstrelan ?

joseph.olstad’s picture

joseph.olstad
Primary language French
CreditAttribution: joseph.olstad commented
Related issues: +#3221622: Unable to sort by current moderation state. Column not found