Support for selecting non-aggregate expressions that don't appear in GROUP BY clause

[zennetizen]

Steps to reproduce:

dolttest> select hash from dolt_branches group by  hash;
+----------------------------------+
| hash                             |
+----------------------------------+
| tiv6bkuqdt3e9g6bhd857u7br9k7g0t2 |
| hqr7jvi2urkf4vhpicu7nt69tl2fvp5t |
+----------------------------------+
dolttest> select name, hash from dolt_branches group by  hash ;
GroupBy aggregate expression 'dolt_branches.name' doesn't appear in the grouping columns

Expected output:

+----------------------------------+--------+
| hash                             | name   |
+----------------------------------+--------+
| tiv6bkuqdt3e9g6bhd857u7br9k7g0t2 | master |
| hqr7jvi2urkf4vhpicu7nt69tl2fvp5t | test-1 |
+----------------------------------+--------+

Workaround: use SELECT DISTINCT keyword

+----------------------------------+--------+
| hash                             | name   |
+----------------------------------+--------+
| tiv6bkuqdt3e9g6bhd857u7br9k7g0t2 | master |
| hqr7jvi2urkf4vhpicu7nt69tl2fvp5t | test-1 |
+----------------------------------+--------+

[timsehn]

I think this might have something to do with this:

https://github.com/dolthub/dolt/issues/407#issuecomment-587290825

What version of MySQL are you comparing to?

[zennetizen]

Every version of MySQL i've used over the last 10+ years (even pre MariaDB) has supported this syntax.

The version I tested this against is:

# mysql -V
mysql  Ver 14.14 Distrib 5.6.40, for Linux (x86_64) using  EditLine wrapper

[timsehn]

I'm not arguing that it should not work as you expect. I've run into GROUP BY issues before and some of the parsing is controlled by a MySQL variable, specifically ONLY_FULL_GROUP_BY.

Also If you look at issue #407, it looks like the behavior has changed over the past year.

[zennetizen]

Ah sorry I didn't mean that to sound as harsh as it sounded when I read it back. I'm just having a look at that issue now :)

[zachmu]

MySQL supports the more lenient (undefined behavior) version, and we should too. It's tricky to get right though.

For now, as you've discovered, Dolt behaves the same as MySQL when ONLY_FULL_GROUP_BY is part of your SQL_MODE, no way to configure it.

[timsehn]

I tested this in another group by and we now suppirt this syntax.

[timsehn]

Actually this still busts. Reopening.

[timsehn]

test-join-plan $ dolt sql -q "select name, hash from dolt_branches group by  hash"
error on line 1 for query select name, hash from dolt_branches group by  hash: expression 'dolt_branches.name' doesn't appear in the group by expressions
expression 'dolt_branches.name' doesn't appear in the group by expressions

[jycor]

Hey @zennetizen, the fix for this has made its way to main. Expect a dolt release sometime this week. We now support the ability to configure @@sql_mode to enforce ONLY_FULL_GROUP_BY, and it is enabled by default. You can disable ONLY_FULL_GROUP_BY using:

set sql_mode=(select replace(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

Alternatively, we now also support any_value(). See: https://github.com/dolthub/dolt/issues/4604