Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[REGRESSION] db-service aggregation query throws error #1112

Open
hakimio opened this issue Apr 2, 2025 · 6 comments · May be fixed by #1114 or #1126
Open

[REGRESSION] db-service aggregation query throws error #1112

hakimio opened this issue Apr 2, 2025 · 6 comments · May be fixed by #1114 or #1126
Labels
author action bug Something isn't working

Comments

@hakimio
Copy link

hakimio commented Apr 2, 2025 •
edited
Loading

Description of erroneous behaviour

Running the following query:

/MyEntities?$apply=filter(<filter>)/groupby((column1,column2),aggregate(amount with sum as totalAmount))

Results in an error:

[cds] - TypeError: (transformedQuery.SELECT.from.ref || transformedQuery.SELECT.from.SELECT.from.ref) is not iterable
    at expandColumn (node_modules\@cap-js\db-service\lib\cqn4sql.js:805:46)
    at handleExpand (node_modules\@cap-js\db-service\lib\cqn4sql.js:428:40)
    at node_modules\@cap-js\db-service\lib\cqn4sql.js:362:32
    at getTransformedColumns (node_modules\@cap-js\db-service\lib\cqn4sql.js:390:21)
    at transformSelectQuery (node_modules\@cap-js\db-service\lib\cqn4sql.js:191:41)
    at cqn4sql (node_modules\@cap-js\db-service\lib\cqn4sql.js:109:26)
    at PostgresService.cqn4sql (node_modules\@cap-js\db-service\lib\SQLService.js:402:17)
    at PostgresService.cqn2sql (node_modules\@cap-js\db-service\lib\SQLService.js:380:18)
    at PostgresService.onSELECT (node_modules\@cap-js\db-service\lib\SQLService.js:131:39)
    at PostgresService.onSELECT (node_modules\@cap-js\postgres\lib\PostgresService.js:326:18)

This is a regression because the same query works fine with db-service v1.18.0, but throws error with v1.19.1.

Detailed steps to reproduce

  1. Run the GET query mentioned above
  2. Observe the result

Details about your project

Package Version
@cap-js/asyncapi 1.0.3
@cap-js/cds-types 0.9.0
@cap-js/change-trackin 1.0.8
@cap-js/db-service 1.19.1
@cap-js/openapi 1.2.1
@cap-js/postgres 1.13.0
@sap/cds 8.9.0
@sap/cds-compiler 5.9.0
@sap/cds-dk 8.9.0
@sap/cds-fiori 1.4.0
@sap/cds-foss 5.0.1
@sap/cds-mtxs 2.7.0
@sap/eslint-plugin-cds 3.2.0
Node.js v22.13.1

@danjoa
Might be releated to 0828c25
@hakimio hakimio added the bug Something isn't working label Apr 2, 2025
@hakimio hakimio changed the title [REGRESSION] db-service TypeError: (transformedQuery.SELECT.from.ref || transformedQuery.SELECT.from.SELECT.from.ref) is not iterable [REGRESSION] db-service aggregation query throws error Apr 2, 2025
@danjoa
Copy link
Contributor

danjoa commented Apr 2, 2025

@patricebender do we have a test for that use case?

@danjoa danjoa linked a pull request Apr 2, 2025 that will close this issue
Open
@danjoa
Copy link
Contributor

danjoa commented Apr 2, 2025 •
edited
Loading

@patricebender
Copy link
Member

Hi @hakimio,

Could you please help me reproduce the issue?

I ran this query with the bookshop, which resembles the structure you suggested:

http://localhost:4004/admin/Books?$apply=filter(title eq 'bar')/groupby((title,ID),aggregate(price with sum as totalAmount))

but that didn't do the trick.

@hakimio
Copy link
Author

hakimio commented Apr 7, 2025 •
edited
Loading

@patricebender I think you need to group by child entities, sth like groupby((author/name,author/birthdate),aggregate(price with sum as totalAmount)).
Just for reference, here is my full request which is failing:

GET TimeTransactions?$apply=filter(start ge 2025-03-04T00:00:00Z and start le 2025-04-02T23:59:59Z and ((timeVault/entity eq 'Customer' and timeVault/entityId eq 2564ef84-fafa-468c-936d-4ade6500e6fe) or (timeVault/entity eq 'Contract' and (timeVault/entityId eq e7242cc4-c692-4e71-8401-1b434f8a541d or timeVault/entityId eq ac19d41b-d09f-470c-87b3-cdbce0952d84 or timeVault/entityId eq 4eb97232-955b-4176-a451-e060a265475b or timeVault/entityId eq 4049807c-fc07-4fa8-b7cc-13345f2d022c or timeVault/entityId eq 53aa917c-ac5a-41a2-9350-20b0f5f52c1d))) and (timeVault/entity eq 'Contract'))/groupby((timeVault/contract/ID,timeVault/contract/orderProduct/product/ID,timeVault/contract/orderProduct/product/name),aggregate(amount with sum as totalAmount))

EDIT: related issue which you fixed sometime ago and regressed now: #708

patricebender added a commit that referenced this issue Apr 8, 2025
@patricebender
fix(expand): proper subquery from construction
a2180c9 
if a query selects from another query, we need to consider the case
where the subquery has path expressions (and therefore joins).

The best solution is then to drill down into the innermost query of the from clause,
because we do not know the depth.

It is also not a good idea to check for the `ref` of the innermost _untransformed_ query,
as it is potentially a scoped query (as shown in one of the tests).

fix #1112
@patricebender patricebender linked a pull request Apr 8, 2025 that will close this issue
Open
@patricebender
Copy link
Member

@hakimio thanks for your help in analyzing this, my fix should cover your case (and even more;))

@hakimio
Copy link
Author

hakimio commented Apr 8, 2025

@patricebender Thank you for coming up with a fix for the issue so fast 🙂

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
author action bug Something isn't working
Projects
None yet
Milestone
No milestone
3 participants
@hakimio @danjoa @patricebender