Error saving the TotalRevenue widget with date range all.

[roelvanhintum]

Description

After adding the TotalRevenue widget with date range all, the dashboard breaks.

Returned error from request:

{"error":"SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'craft.orders.dateOrdered' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\nThe SQL being executed was: SELECT SUM(`total`) as revenue, COUNT(`id`) as count, CONCAT(EXTRACT(YEAR FROM `dateOrdered`), '-', EXTRACT(MONTH FROM `dateOrdered`)) as datekey\nFROM `commerce_orders` `orders`\nWHERE (`dateOrdered` >= '2019-10-11 07:00:00') AND (`dateOrdered` <= '2020-02-01 07:59:59') AND (`isCompleted`=1)\nGROUP BY EXTRACT(YEAR FROM `dateOrdered`), EXTRACT(MONTH FROM `dateOrdered`)\nORDER BY EXTRACT(YEAR FROM `dateOrdered`) ASC, EXTRACT(MONTH FROM `dateOrdered`) ASC"}

Steps to reproduce

1. TotalRevenue widget
2. Set date range to all

Additional info

• Craft version: 3.4.1
• PHP version: 7.2.24
• Database driver & version: MySQL 5.7.28
• Plugins & versions:
  Asset Usage 2.0.1
  Buckaroo for Craft Commerce 1.0.0
  Color Extractor 2.0.6
  Craft Commerce 3.0.3
  Enforce password 1.0.0
  Image Hotspots (beta) 1.0.0-beta.6
  Linkit 1.1.11
  Redactor 2.5.0
  Sentry SDK 1.0.6
  SEOmatic 3.2.41
  Super Table 2.4.1
  Two-Factor Authentication 2.4.0

[nfourtythree]

Hi @roelvanhintum

Thanks for bringing this to our attention, we have just pushed a fix for this that will be included in the next release.

If you would like to get this update early please update you Commerce requirement in your composer.json with the details below and then run composer update.

"require": {
  "craftcms/commerce": "dev-develop#47e871a7e06689f3d535cc8f2549144c9fcd8d40 as 3.0.3",
  "...": "..."
}

Thanks!