Search code examples
orientdbcoalesce

OrientDB COALESCE Function With Unexpected Results

Using OrientDB 2.1.2, I was trying to use the inherent COALESCE functionality and ran into some strange results.

Goal: select the maximum value of a property based on certain conditions OR 0 if there is no value for that property given the conditions.

Here's what I tried to use to produce my results.

Attempt 1: Just selecting the Maximum value of a property based on some condition - This worked as I expected... a single result enter image description here

Attempt 2: Same query as before but now I'm adding an extra condition that I know will cause no results to be returned - This also worked as I expected... no results found enter image description here

Attempt 3: Using COALESCE to select 0 if the result from the second query returns no results - This is where the query fails (see below). enter image description here

I would expect the result from the second query to return no results, thereby qualifying as a "NULL" result meaning that the COALESCE function should then go on to return 0. What happens instead is that the COALESCE function is seeing the results of the inner select (which again, returns no results) as a valid non-null value, causing the COALESCE function to never return the intended "0" value.

Two questions for those who are familiar with using the OrientDB API:

  1. Do you think this functionality is working properly or should an issue be filed with the orientdb issue tracker?
  2. Is there another way to achieve my goal without using COALESCE or by using COALESCE in a different way?
Solution

  • Try rather:

    select coalesce($a, 0) from ... let $a = (subquery) where ...

    Or also this variant because the sub-select returns a result set, but the coalescence wants a single value:

    select coalesce($a[0], 0) from ... let $a = (subquery) where ...