Closed niravjprabtani closed 1 month ago
If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you. If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines. You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.
@paveltiunov It looks like this might be fixed at the MSSQL driver level—or at a more general level, so that order
will be stripped from queries used to calculate totals. Might be a single-line fix here: https://github.com/cube-js/cube/blob/0723bd5421619001aca54b0331724cb6d23fa6dd/packages/cubejs-api-gateway/src/gateway.ts#L1413-L1416
We're running into this as well. I'm taking a look at fixing it with @Nick-PC.
@igorlukanin so far we haven't had any luck making the update in cube/packages/cubejs-api-gateway/src/gateway.ts.
We have tried setting normalizedTotal.order = null
and also delete normalizedTotal.order
to no avail. We'll continue poking at it, but any guidance would be greatly appreciated.
@igorlukanin I think we found the proper fix in the above pull request
Thanks for your contribution @rdwoodring 🙌
THe fix will be released in v0.35.81 soon.
Describe the bug We are currently implementing server-side pagination in a web application, utilizing the "total": true attribute to obtain an exact count of records based on given conditions, irrespective of limit and offset. This helps in acquiring insights into the total number of pages for the application. However, when executing the Cube.js API, an error occurs.
Investigation and Findings Upon switching the database from MSSQL to Snowflake, it became evident that the issue pertains specifically to MSSQL. Further investigation revealed that Cube.js internally executes an identical query without limit and offset to obtain the total record count for the API request. Cube.js wraps the original query within a "select count(*) from" statement. This method fails in MSSQL due to its lack of support for an order statement within this clause.
To Reproduce Steps to reproduce the behavior:
Expected behavior It should give results along with the total number of records.
Minimally reproducible Cube Schema You can use selects without tables in order to achieve that as follows.
Please invoke below API request to get the API response
Sample request URL : http://localhost:4000/cubejs-api/v1/load Query param :
This should provide list of status along with total number of records.
Version: Here is my sample docker-compose.xml
Additional context Investigation and Findings: Upon switching the database from MSSQL to Snowflake, it became evident that the issue pertains specifically to MSSQL. Further investigation revealed that Cube.js internally executes an identical query without limit and offset to obtain the total record count for the API request. Cube.js wraps the original query within a "select count(*) from" statement. This method fails in MSSQL due to its lack of support for an order statement within this clause.
Queries:
The problem lies in query number 1, as the "order by" clause is within the "select count(*) from" statement, which is not supported in MSSQL. We seek guidance on how to address this issue to achieve the intended functionality. Despite attempting various solutions, using the "total": true attribute in the JSON request consistently results in an exception.
Any assistance in resolving this matter would be greatly appreciated.