SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'drupal10.n.created' which is not functionally dependent on columns in GROUP BY clause

Problem/Motivation

I get an SQL error with white screen when adding the relevant content block to the page.

The website encountered an unexpected error. Please try again later.

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'drupal10.n.created' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT "n"."nid" AS "nid", COUNT(*) AS "cnt" FROM "node_field_data" "n" INNER JOIN "taxonomy_index" "ti" ON n.nid = ti.nid WHERE ("n"."status" = :db_condition_placeholder_0) AND ("n"."type" IN (:db_condition_placeholder_1)) AND ("ti"."tid" IN (:db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4, :db_condition_placeholder_5, :db_condition_placeholder_6)) AND ("n"."nid" NOT IN (:db_condition_placeholder_7)) GROUP BY "n"."nid" ORDER BY "cnt" DESC, "n"."created" DESC, "n"."nid" DESC LIMIT 10 OFFSET 0; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => post [:db_condition_placeholder_2] => 14 [:db_condition_placeholder_3] => 17 [:db_condition_placeholder_4] => 686 [:db_condition_placeholder_5] => 711 [:db_condition_placeholder_6] => 730 [:db_condition_placeholder_7] => 50 ) in Drupal\relevant_content\RelevantContentService->findRelevantContent() (line 125 of modules/contrib/relevant_content/src/RelevantContentService.php).
Drupal\Core\Database\StatementWrapper->execute(Array, Array) (Line: 765)
Drupal\Core\Database\Connection->query('SELECT "n"."nid" AS "nid", COUNT(*) AS "cnt"
FROM
{node_field_data} "n"
INNER JOIN {taxonomy_index} "ti" ON n.nid = ti.nid
WHERE ("n"."status" = :db_condition_placeholder_0) AND ("n"."type" IN (:db_condition_placeholder_1)) AND ("ti"."tid" IN (:db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4, :db_condition_placeholder_5, :db_condition_placeholder_6)) AND ("n"."nid" NOT IN (:db_condition_placeholder_7))
GROUP BY "n"."nid"
ORDER BY "cnt" DESC, "n"."created" DESC, "n"."nid" DESC
LIMIT 10 OFFSET 0', Array, Array) (Line: 525)
Drupal\Core\Database\Query\Select->execute() (Line: 125)
Drupal\relevant_content\RelevantContentService->findRelevantContent(Array, Array, Array, 10) (Line: 68)
Drupal\relevant_content\Plugin\Block\RelevantContentBlock->build() (Line: 171)
Drupal\block\BlockViewBuilder::preRender(Array)
call_user_func_array(Array, Array) (Line: 101)
Drupal\Core\Render\Renderer->doTrustedCallback(Array, Array, 'Render #pre_render callbacks must be methods of a class that implements \Drupal\Core\Security\TrustedCallbackInterface or be an anonymous function. The callback was %s. See https://www.drupal.org/node/2966725', 'exception', 'Drupal\Core\Render\Element\RenderCallbackInterface') (Line: 788)
Drupal\Core\Render\Renderer->doCallback('#pre_render', Array, Array) (Line: 374)
Drupal\Core\Render\Renderer->doRender(Array) (Line: 446)
Drupal\Core\Render\Renderer->doRender(Array, ) (Line: 204)
Drupal\Core\Render\Renderer->render(Array) (Line: 477)
Drupal\Core\Template\TwigExtension->escapeFilter(Object, Array, 'html', NULL, 1) (Line: 88)
__TwigTemplate_158714afbd97a8420c25ab18db8aef73->doDisplay(Array, Array) (Line: 394)
Twig\Template->displayWithErrorHandling(Array, Array) (Line: 367)
Twig\Template->display(Array) (Line: 379)
Twig\Template->render(Array, Array) (Line: 40)
Twig\TemplateWrapper->render(Array) (Line: 53)
twig_render_template('themes/custom/aaas_scidip/templates/layout/page.html.twig', Array) (Line: 372)
Drupal\Core\Theme\ThemeManager->render('page', Array) (Line: 433)
Drupal\Core\Render\Renderer->doRender(Array, ) (Line: 204)
Drupal\Core\Render\Renderer->render(Array) (Line: 477)
Drupal\Core\Template\TwigExtension->escapeFilter(Object, Array, 'html', NULL, 1) (Line: 80)
__TwigTemplate_739750fbae51c7503865e2ec95599a07->doDisplay(Array, Array) (Line: 394)
Twig\Template->displayWithErrorHandling(Array, Array) (Line: 367)
Twig\Template->display(Array) (Line: 379)
Twig\Template->render(Array, Array) (Line: 40)
Twig\TemplateWrapper->render(Array) (Line: 53)
twig_render_template('core/themes/stable9/templates/layout/html.html.twig', Array) (Line: 372)
Drupal\Core\Theme\ThemeManager->render('html', Array) (Line: 433)
Drupal\Core\Render\Renderer->doRender(Array, ) (Line: 204)
Drupal\Core\Render\Renderer->render(Array) (Line: 158)
Drupal\Core\Render\MainContent\HtmlRenderer->Drupal\Core\Render\MainContent\{closure}() (Line: 580)
Drupal\Core\Render\Renderer->executeInRenderContext(Object, Object) (Line: 159)
Drupal\Core\Render\MainContent\HtmlRenderer->renderResponse(Array, Object, Object) (Line: 90)
Drupal\Core\EventSubscriber\MainContentViewSubscriber->onViewRenderArray(Object, 'kernel.view', Object)
call_user_func(Array, Object, 'kernel.view', Object) (Line: 111)
Drupal\Component\EventDispatcher\ContainerAwareEventDispatcher->dispatch(Object, 'kernel.view') (Line: 168)
Symfony\Component\HttpKernel\HttpKernel->handleRaw(Object, 1) (Line: 74)
Symfony\Component\HttpKernel\HttpKernel->handle(Object, 1, 1) (Line: 58)
Drupal\Core\StackMiddleware\Session->handle(Object, 1, 1) (Line: 48)
Drupal\Core\StackMiddleware\KernelPreHandle->handle(Object, 1, 1) (Line: 106)
Drupal\page_cache\StackMiddleware\PageCache->pass(Object, 1, 1) (Line: 85)
Drupal\page_cache\StackMiddleware\PageCache->handle(Object, 1, 1) (Line: 48)
Drupal\Core\StackMiddleware\ReverseProxyMiddleware->handle(Object, 1, 1) (Line: 51)
Drupal\Core\StackMiddleware\NegotiationMiddleware->handle(Object, 1, 1) (Line: 51)
Drupal\Core\StackMiddleware\StackedHttpKernel->handle(Object, 1, 1) (Line: 686)
Drupal\Core\DrupalKernel->handle(Object) (Line: 19)

Steps to reproduce

• Create a Relevant Content Preset entity:
• Enabled Content Types: Post
• Enabled Vocabularies: Topics
• Place the block in page region, restrict display to node type 'Post'
• Visit a node type 'Post', and i get this white screen with the aforementioned SQL error

Proposed resolution

Would probably need to add aggregation on the column.

Note that when I try the SQL INIT command in the db connection settings as found in this issue thread, the error goes away. However, this is changing the default SQL mode which may have undesirable side effects.

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