ORDER BY is not working for preaggregations queries

[b-jan]

Describe the bug Using BigQuery and matching a preaggregation, a query with order parameter on a measure returns unsorted results. The generated SQL query that I can inspect in the playground does not use any ORDER BY clause.

Example of a query:

{
  "measures": [
    "Cube.count"
  ],
  "dimensions": [
    "Cube.status"
  ],
  "order": [
    [
      "Cube.count",
      "desc"
    ]
  ]
}

The generated SQL is:

SELECT
  `cube__status`,
  sum(`cube__count`) `cube__count`
FROM
  (
    SELECT
      `cube__status` `cube__status`,
      sum(`cube__count`) `cube__count`
    FROM
      pre_aggregations_xxxxxxxxxxxxxxx.cube AS `cube__main`
    GROUP BY
      1
  ) `base`
GROUP BY
  1

Expected behavior order should be working when a preaggregation is matched.

Version: "@cubejs-backend/bigquery-driver": "0.35.30", "@cubejs-backend/server": "0.35.30"

Other question: Also I noticed the count measure is returned by preaggregations (from BigQuery) as a string instead of a number. Is there any reason for this to happen?

[igorlukanin]

Hi @b-jan 👋

Sorry, I wasn't able to reproduce this. By any chance, can you provide more details? would love to see a screenshot of your Playground where this order-less SQL can be seen—together with the order settings. Also, if you can reproduce this in Cube Cloud (you can create a free account), it would be tremendously helpful.

Also I noticed the count measure is returned by preaggregations (from BigQuery) as a string instead of a number. Is there any reason for this to happen?

This is a limitation of how JSON is parsed in web browsers. If your measures have their values over a certain number (Integer.MAX_SAFE_INTEGER), then they can only be safely transferred as strings. This is why Cube does this by default. You can use castNumerics to override. See https://cube.dev/docs/reference/frontend/cubejs-client-core#loadmethodoptions

[b-jan]

Thanks a lot for the answer @igorlukanin. I did not know the castNumerics, exactly what I was looking for.

Still even with this option, I still have the order issue. I dont have a shareable dataset but here are some screens of my Playground :

[b-jan]

And for the same query on another dimension not matching pre-aggregations, I see the ORDER BY clause:

[igorlukanin]

This is very helpful, thank you! Let me tag @paveltiunov and @ovr in case they can guess what's going on (wrong) here.

Does this reproduce if you change your cube definitions' sql from SELECT * FROM table to SELECT 1 AS column_1, 2 AS column_2, ... UNION ALL SELECT 1 AS column_1, 2 AS column_2, ...? If it does, then connecting your dataset would not be needed.