MySQL Bugs: #106813: Some versions of mysql - ORDER BY clause is not in SELECT list

Bug #106813	Some versions of mysql - ORDER BY clause is not in SELECT list - Distinct error
Submitted:	23 Mar 2022 19:24	Modified:	28 Mar 2022 18:07
Reporter:	Joseph Olstad	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.28-0ubuntu0.20.04.3	OS:	Ubuntu (20.04.3)
Assigned to:		CPU Architecture:	x86 (Ryzen 5700u)

Description:
SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'dbname.content_moderation_state.moderation_state' 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", "node_field_data_node_field_revision"."nid" AS "node_field_data_node_field_revision_nid", "users_field_data_node_field_data"."uid" AS "users_field_data_node_field_data_uid", "users_field_data_content_lock"."uid" AS "users_field_data_content_lock_uid" FROM "node_field_data" "node_field_data" INNER JOIN "node_field_revision" "node_field_revision" ON node_field_data.vid = node_field_revision.vid LEFT JOIN "node_field_data" "node_field_data_node_field_revision" ON node_field_revision.vid = node_field_data_node_field_revision.vid AND node_field_data_node_field_revision.langcode = node_field_revision.langcode 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 "content_lock" "node_field_data_node_field_revision__content_lock" ON node_field_data_node_field_revision.nid = node_field_data_node_field_revision__content_lock.entity_id AND node_field_data_node_field_revision__content_lock.entity_type = :views_join_condition_1 LEFT JOIN "users_field_data" "users_field_data_content_lock" ON node_field_data_node_field_revision__content_lock.uid = users_field_data_content_lock.uid LEFT JOIN "content_moderation_state_field_revision" "content_moderation_state" ON node_field_data.vid = content_moderation_state.content_entity_revision_id AND (content_moderation_state.content_entity_type_id = :views_join_condition_2 AND content_moderation_state.content_entity_id = node_field_data.nid AND content_moderation_state.langcode = node_field_data.langcode) WHERE "node_field_data"."langcode" IN (:db_condition_placeholder_5) ORDER BY "content_moderation_state"."moderation_state" ASC LIMIT 25 OFFSET 0; Array ( [:db_condition_placeholder_5] => en [:views_join_condition_1] => node [:views_join_condition_2] => node )

1-Mysql 5.7.32 is ok with DISTINCT keyword on above query , works fine
2-MySQL 5.7.31 is ok with DISTINCT keyword on above query , works fine
3-MySQL 5.7.30 is NOT ok, has the distinct bug on above query when sorting moderation state
4-MySQL 8.0.28-0 is NOT ok, has the distinct bug on above query when sorting moderation state
5-MariaDB Server version: 10.3.34-MariaDB-1:10.3.34 works fine, no issue.

How to repeat:
To reproduce this issue, install Drupal 9.3.8 , enable the content_moderation module and content_translation modules.  Take this view configuration yaml https://gitlab.com/agrcms/proto/-/blob/9.3.x/custom/config/sync/views.view.content.yml and load it into this instance of Drupal 9.3.8 after having installed and configured the mentioned modules and also the workflow module and add the built in content types to the editorial workflow.

If this is too complicated to reproduce or you don't have time or are confused, contact my email address and I can assist you or provide you an environment all ready to go.

Suggested fix:
see description for workarounds.

ORDER BY clause is not in SELECT list

once the setup is done, sort by moderation state, this exposes the query that fails.

Hi Mr. Olstad,

Thank you for your bug report.

However, it is not a bug.

If you inspect your query, you will find that column for sorting is not specified in the SELECT list. It is only specified in the list of common columns, but not in the SELECT list. Tuning that behaviour is possible, for which we recommend you warmly to read our entire Reference Manual.

Not a bug.

This is not a Drupal db abstraction bug, Drupal is generating a consistent query, what the problem is is MySQL is not behaving consistently between major and minor releases.

The actual query I used, removed special characters { } and double quotes

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, node_field_data_node_field_revision.nid AS node_field_data_node_field_revision_nid, users_field_data_node_field_data.uid AS users_field_data_node_field_data_uid, users_field_data_content_lock.uid AS users_field_data_content_lock_uid
FROM
node_field_data node_field_data
INNER JOIN node_field_revision node_field_revision ON node_field_data.vid = node_field_revision.vid
LEFT JOIN node_field_data node_field_data_node_field_revision ON node_field_revision.vid = node_field_data_node_field_revision.vid AND node_field_data_node_field_revision.langcode = node_field_revision.langcode
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 content_lock node_field_data_node_field_revision__content_lock ON node_field_data_node_field_revision.nid = node_field_data_node_field_revision__content_lock.entity_id AND node_field_data_node_field_revision__content_lock.entity_type = 'node'
LEFT JOIN users_field_data users_field_data_content_lock ON node_field_data_node_field_revision__content_lock.uid = users_field_data_content_lock.uid
LEFT JOIN content_moderation_state_field_revision content_moderation_state ON node_field_data.vid = content_moderation_state.content_entity_revision_id AND (content_moderation_state.content_entity_type_id = 'node' AND content_moderation_state.content_entity_id = node_field_data.nid AND content_moderation_state.langcode = node_field_data.langcode)
WHERE node_field_data.langcode IN ('en')
ORDER BY content_moderation_state.moderation_state ASC

Not sure why 5.7.31 and 5.7.32 has different defaults than 5.7.30 and 8.0.28

however there's apparently a workaround to this:

https://support-acquia.force.com/s/article/1500001586101-Drupal-throws-MySQL-expression-of...

as a workaround this sql_mode setting resolves the issue when using MySQL 5.7.30

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'"

Next I will test this same workaround with 8.0.28

I tested the above sql_mode with MySQL 8.0.28 and it caused MySQL 8.0.28 to crash the application so nothing worked.

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 above setting only worked in 5.7.30 but not in 8.0.28

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

  'driver' => 'mysql',
  'init_commands' => ['sql_mode' => "SET sql_mode = ''"],

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.

Reminder that the workaround is not necessary if you are using 5.7.31 or 5.7.32 instead of 5.7.30 or 8.0.28

updated workaround that works for MySQL 8.0.28:

  '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'"],

Hi Mr. Olstad,

Thank you for your comments.

However, we do not provide support for products from other companies, but only for our own. Regarding your problems with a product that you named as "Drupal", you should contact those in charge of that project.

Also, we already wrote to you that there is a simple remedy for this problem in MySQL. It is described in our Reference Manual.

Not a bug.

Hi Mr. Olstad,

This is not a bug.

Simply, that query is not written properly and violates SQL standards. Yes, the solution is in the sql_mode, so please read more about ONLY_FULL_GROUP_BY.

Not a bug.

We already wrote that this is all explained in our Reference Manual, so there is no need for you to search anywhere else.

Hi Mr. Olstad,

This is MySQL bugs forum.

Once again, please do not mention or write about other products in our bugs forum.

We do change defaults for sql_mode, because it is necessary to keep up with SQL standards and to accommodate changes in our code.

That is why we publish Release Notes, which you can find in dev.mysql.com. We do recommend you to read Release Notes whenever you find differences between versions and releases.

Not a bug.

I checked the release notes of MySQL 5.7.31 and it was not noted that the sql_mode had changed since 5.7.30

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-31.html

I also checked 8.0 release notes, no mention of related sql_mode default changes.

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-0.html

Seems like this is an undocumented change (some people might call that a bug).

I'm satisfied with the workarounds discovered in this issue however still confused about the sql standards that are changed arbitrarily between versions and haven't found anything from the reference guides about this or the release notes between affected versions.  With that said, not much other choice than to deal with it one way or another.