Select with group by behavior in 5.5 vs 10.0 - Source

Revision: 29013
User: Bill Rios
Date: 2014-06-01 11:06

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

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.

Select with group by behavior in 5.5 vs 10.0 - Source

Answer

Products

Services

Pricing

Resources

About Us

Download MariaDB