Select with group by behavior in 5.5 vs 10.0
Hi,
I've upgraded from MariaDB 5.5.27 to 10.0.11 and noticed that adding a group by to the query below now returns all NULL values for the third column (b.oid) whereas in 5.5.27 it returns the matching row in table b. I realize this is non-standard SQL since I'm selecting a non aggregated column that is not in the group by, but I would expect that it would find the one matching row in table b as it did in 5.5.27. Is it by design that it behaves this way now in 10.0 or is this a bug?
Query without a group by clause:
MariaDB [group_by_test]> select a.oidGroup, a.oid, b.oid from table_one a left join table_two b on a.oid=b.oid where a.oidGroup=1; +----------+-----+------+ | oidGroup | oid | oid | +----------+-----+------+ | 1 | 1 | NULL | | 1 | 2 | NULL | | 1 | 3 | 3 | | 1 | 4 | NULL | +----------+-----+------+ 4 rows in set (0.00 sec)
With group by in v5.5.27 (same results):
MariaDB [group_by_test]> select a.oidGroup, a.oid, b.oid from table_one a left join table_two b on a.oid=b.oid where a.oidGroup=1 group by a.oid; +----------+-----+------+ | oidGroup | oid | oid | +----------+-----+------+ | 1 | 1 | NULL | | 1 | 2 | NULL | | 1 | 3 | 3 | | 1 | 4 | NULL | +----------+-----+------+ 4 rows in set (0.00 sec)
With group by in v10.0.11 (all NULL values for column 3 as if there were no matching rows):
MariaDB [group_by_test]> select a.oidGroup, a.oid, b.oid from table_one a left join table_two b on a.oid=b.oid where a.oidGroup=1 group by a.oid; +----------+-----+------+ | oidGroup | oid | oid | +----------+-----+------+ | 1 | 1 | NULL | | 1 | 2 | NULL | | 1 | 3 | NULL | | 1 | 4 | NULL | +----------+-----+------+ 4 rows in set (0.05 sec)
Thanks in advance!
Answer Answered by Elena Stepanova in this comment.
This is a bug MDEV-5719, the work on it is currently in progress. I have added your test case to it as well, thanks for it.