CODEWITHSUNDEEP
orientdb
Barbara
Barbara

Reputation: 11

OrientDB query using GROUP BY

The below query returns incorrect number of rows:

SELECT keyName.left(keyName.indexOf('_Configuration/')) AS keyValue, 
address.zip AS zip, 
customer_map AS customerMap 
FROM CUSTOMERS
WHERE customer_group = 'xyz' 
GROUP BY keyValue, customerMap, zip

where column datatypes are as the following: 

keyName String 
address EMBEDDEDMAP 
customer_map EMBEDDEDMAP

However, if the GROUP BY is changed to use the column names instead of aliases, the query returns correct number of rows:

SELECT keyName.left(keyName.indexOf('_Configuration/')) AS keyValue, 
address.zip AS zip, 
customer_map AS customerMap 
FROM CUSTOMERS 
WHERE customer_group = 'xyz' 
GROUP BY keyValue, customer_map, address.zip

Interestingly, using an alias for keyName.left(...) does not affect GROUP BY, but a function like for example: ifnull(keyName, 'ABC) AS keyValue makes the query to return incorrect number of rows.

Note that the first query does not give any errors/warnings, just returns incorrect number of rows.

Is that expected behaviour of GROUP BY?
Unfortunately OrientDB docs do not have too many details about GROUP BY.

Upvotes: 1

Views: 1142

Answers (1)

Alessandro Rota
Alessandro Rota

Reputation: 3570

The query execution flow is as follows:

  • find query target (indexes first, then fallback on cluster iterators)
  • iterate over the target and filter based on WHERE condition (excluding conditions already matched by indexes)
  • calculate projections on filtered records
  • apply UNWIND and EXPAND
  • apply Group By
  • apply Order By
  • apply SKIP and LIMIT

You can use

select keyValue,zip,customerMap from (SELECT keyName.left(keyName.indexOf('_Configuration/')) AS keyValue, 
address.zip AS zip, 
customer_map AS customerMap 
FROM CUSTOMERS
WHERE customer_group = 'xyz' )
GROUP BY keyValue, customerMap, zip

Upvotes: 1

Related Questions