Selecting more then one column when groupBy is present (db_select) - Drupal 8 added 'ONLY_FULL_GROUP_BY' In my custom Drupal 8 plug-in I perform such query:

$select = db_select('my_table', 'e');
$select-&gt;addField('e', 'field1');

$select-&gt;condition('field3', 'NULL', '!=');
$select-&gt;condition('field4', array(11, 12, 31, 32, 33), 'IN');

$select-&gt;groupBy("e.field1");
$select-&gt;orderBy("e.field1");
$select-&gt;orderBy("e.field5");
$entries = $select-&gt;execute()-&gt;fetchAll(\PDO::FETCH_ASSOC); 


And it works fine. It returns me all records grouped by field1 that meets the condition, orderered. But it returns only field1 column.

I wanted to get more columns, so I have added another $select-&gt;addField('e', 'field2'); just after first one:

$select = db_select('my_table', 'e');
$select-&gt;addField('e', 'field1');
$select-&gt;addField('e', 'field2');

$select-&gt;condition('field3', 'NULL', '!=');
$select-&gt;condition('field4', array(11, 12, 31, 32, 33), 'IN');

$select-&gt;groupBy("e.field1");
$select-&gt;orderBy("e.field1");
$select-&gt;orderBy("e.field5");
$entries = $select-&gt;execute()-&gt;fetchAll(\PDO::FETCH_ASSOC); 


This time I get error:


  Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1055 Użyto 'my_database.e.field2' bez umieszczenia w group by:


Which means "my_database.e.field2 has been used without putting in into group by". But I don't want to group by every column I select.

Unfortunatelly Drupal 8 added ONLY_FULL_GROUP_BY :(
Then, how can I use groupBy and select more then one column at the same time?
This is pretty annoying, but it is to provide cross database compatibility for Drupal.  You can get around it by adding the following to your database connection array if you only ever intend to live in MySQL compatible land:

'init_commands' =&gt; [
  'sql_mode' =&gt; "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'",
],


But I haven't yet figured out how to disable in my Kernel tests.  Still getting something like "PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 'local.nfd.title' isn't in GROUP BY"