Group by not working when columns not returned directly in select #8625
Description
OrientDB Version: 3.0.9
Java Version: openjdk version "1.8.0_171"
OS: alpine 3.8.1 (I'm using the official Docker image)
Given table such as:
| @Rid | @Version | @Class | last_name | first_name |
|---|---|---|---|---|
| 41:0 | 1 | Test | Francescone | Onida |
| 41:1 | 1 | Test | Springham | Lauree |
| 41:2 | 1 | Test | Whal | Cory |
| 42:0 | 1 | Test | Ryan | Onida |
| 42:1 | 1 | Test | Ranking | Abran |
| 42:2 | 1 | Test | Schoales | Cory |
| 43:0 | 1 | Test | Ryan | Maximilien |
| 43:1 | 1 | Test | Stannas | Abran |
| 43:2 | 1 | Test | Schoales | Augusta |
| 44:0 | 1 | Test | Henzley | Dot |
| 44:1 | 1 | Test | Stannas | Gypsy |
| 44:2 | 1 | Test | Garrard | Walliw |
Then running a query with GROUP BY such as:
SELECT first_name + ' ' + last_name, COUNT(@rid) FROM Test GROUP BY last_name, first_name
Expected behavior
Should group specified columns like in SQL:
| first_name + ' ' + last_name | COUNT(@Rid) |
|---|---|
| Onida Francescone | 1 |
| Lauree Springham | 1 |
| Cory Whal | 1 |
| Onida Ryan | 1 |
| Abran Ranking | 1 |
| Cory Schoales | 1 |
| Maximilien Ryan | 1 |
| Abran Stannas | 1 |
| Augusta Schoales | 1 |
| Dot Henzley | 1 |
| Gypsy Stannas | 1 |
| Walliw Garrard | 1 |
Actual behavior
Actually groups people with same last_name to same bucket
| first_name + ' ' + last_name | COUNT(@Rid) |
|---|---|
| Onida Francescone | 1 |
| Lauree Springham | 1 |
| Cory Whal | 1 |
| Maximilien Ryan | 2 |
| Abran Ranking | 1 |
| Augusta Schoales | 2 |
| Gypsy Stannas | 2 |
| Dot Henzley | 1 |
| Walliw Garrard | 1 |
Steps to reproduce
Prepare table with data:
CREATE CLASS TEST EXTENDS V;
INSERT INTO Test (first_name, last_name) VALUES ('Onida', 'Francescone'),
('Onida', 'Ryan'),
('Maximilien', 'Ryan'),
('Dot', 'Henzley'),
('Lauree', 'Springham'),
('Abran', 'Ranking'),
('Abran', 'Stannas'),
('Gypsy', 'Stannas'),
('Cory', 'Whal'),
('Cory', 'Schoales'),
('Augusta', 'Schoales'),
('Walliw', 'Garrard');
Query:
SELECT first_name + ' ' + last_name, COUNT(@rid) FROM Test GROUP BY first_name, last_name
Workaround:
The query seems to give expected results when using GROUP BY first_name + ' ' + last_name syntax:
SELECT first_name + ' ' + last_name, COUNT(@rid) FROM Test GROUP BY first_name + ' ' + last_name
Also when selecting first_name and last_name fields directly in SELECT the query returns results as expected.
SELECT first_name, last_name, COUNT(@rid) FROM Test GROUP BY first_name, last_name