Drupal 7 began forcing mySQL to act like a traditional SQL server so that all SQL code used in Drupal will be highly compatible with other database systems like PostgreSQL or SQLite. This means that mySQL statements that syntactically work in other contexts may produce errors under Drupal 7 or 9. Drupal 8+ adds the 'ONLY_FULL_GROUP_BY' restriction, which can potentially cause custom mySQL code that worked in Drupal 7 to fail with errors in Drupal 8+.
Here's the exact settings being used in Drupal 8:
- 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, ONLY_FULL_GROUP_BY'
The mySQL Reference Guide has full details on these SQL modes and their effects. (See especially the section on STRICT mode and ONLY_FULL_GROUP_BY mode.)