Default date ordering changed with v1.3.24

[mike-braidwell]

Describe the bug

Version 1.3.24 changed the default ordering behavior on date queries that have multiple granularities. Previously, querying, say, day and value would order by the value timestamp. With this change, it orders by the day.

v1.3.23...v1.3.24

To Reproduce

Create a cube that has a date field. Query it with GraphQL like so:

query Foo {
    cube {
      intradayPerformance {
        observation_date {
          value
          day
        }
      }
    }
  }

With v1.3.23, the results will be ordered ascending by value. With v1.3.24, the results will be ordered by the day. (If you look at the generated SQL, you can see both columns in the select but the order by index changes from the timestamp to the day.)

Expected behavior

I expect the v1.3.23 behavior where the order is on the finest date granularity.

Screenshots

v1.3.23

v1.3.24

Minimally reproducible Cube Schema

N/A

Version:

v1.3.24 thru v.1.3.47

Additional context

Nothing.

[igorlukanin]

Hey @mike-braidwell, thanks for the question!

Indeed, this was changed here: #9630

Cube does not provide guarantees for ordering behavior on queries that have multiple granularities. So, while the behavior changed, we do not consider it breaking.

Please use the order / orderBy parameters to specify the order explicitly.

[mike-braidwell]

Thanks for the reply. I'm not sure how to get the ordering that I want and from my reading of the docs, I'm not sure that I can? This exhibits the same issue:

query Foo {
    cube {
        intradayPerformance(orderBy: { observation_date: asc }) {
            observation_date {
                value
                day
            }
        }
    }
}

And this gives the error "Enum "OrderBy" cannot represent non-enum value: {value: asc}.":

query Foo {
    cube {
        intradayPerformance(orderBy: { observation_date: { value: asc } }) {
            observation_date {
                value
                day
            }
        }
    }
}

I also considered querying just the value and then generating the day field on the client but that didn't seem right. I could also order the results at the client but that also didn't seem right.

One last thought fwiw, it seems like ordering on the finest granularity when multiple granularities are specified would always give unsurprising results.

[mike-braidwell]

The fix is to add "second" to the date field:

query Foo {
    cube {
        intradayPerformance {
            observation_date {
                value
                second
                day
            }
        }
    }
}

While the PR description says this:

Now column with the lowest granularity (week in the example) will be used for ordering.

it does not consider "value" to be a granularity.

Of course this fix will not order milliseconds but perhaps a custom granularity could be added for that.