This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

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.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.