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.

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

Table alias of outer query is missing for refs in function args in expand / exists #779

Closed
pavlzk opened this issue Aug 15, 2024 · 1 comment · Fixed by #795 or #761
Closed

Table alias of outer query is missing for refs in function args in expand / exists #779

pavlzk opened this issue Aug 15, 2024 · 1 comment · Fixed by #795 or #761
Assignees
Labels
bug Something isn't working cqn4sql all issues related to db-service/cqn4sql

Comments

@pavlzk
Copy link

pavlzk commented Aug 15, 2024

Description of erroneous behaviour

Getting incorrect result on expanded associations defined with sql functions in on clause

Database schema defined as follows:

namespace db;

entity Posts {
  key ID: Integer;
  name: String;
  similar: Association to many Posts on name = similar.name;
  iSimilar: Association to many Posts on UPPER(name) = UPPER(iSimilar.name);
}

Service definition:

using {db} from '../db/schema';

service PostsService {
  entity Posts as projection on db.Posts
}

CSV data:

ID,name
1,some
2,some
3,Some
4,some1
5,some2
6,Some4

Odata request:
/odata/v4/posts/Posts(1)?$expand=similar,iSimilar

Actual result:

{
  "@odata.context": "$metadata#Posts/$entity",
  "ID": 1,
  "name": "some",
  "similar": [
    {
      "ID": 1,
      "name": "some"
    },
    {
      "ID": 2,
      "name": "some"
    }
  ],
  "iSimilar": [
    {
      "ID": 1,
      "name": "some"
    },
    {
      "ID": 2,
      "name": "some"
    },
    {
      "ID": 3,
      "name": "Some"
    },
    {
      "ID": 4,
      "name": "some1"
    },
    {
      "ID": 5,
      "name": "some2"
    },
    {
      "ID": 6,
      "name": "Some4"
    }
  ]
}

Expected result:

{
  "@odata.context": "$metadata#Posts/$entity",
  "ID": 1,
  "name": "some",
  "similar": [
    {
      "ID": 1,
      "name": "some"
    },
    {
      "ID": 2,
      "name": "some"
    }
  ],
  "iSimilar": [
    {
      "ID": 1,
      "name": "some"
    },
    {
      "ID": 2,
      "name": "some"
    },
    {
      "ID": 3,
      "name": "Some"
    }
  ]
}

Details about your project

@cap-js/asyncapi: 1.0.2
@cap-js/cds-types: 0.6.5
@cap-js/openapi: 1.0.4
@cap-js/sqlite: 1.7.3
@sap/cds: 8.1.1
@sap/cds-compiler: 5.1.2
@sap/cds-dk (global): 8.0.3
@sap/cds-fiori: 1.2.7
@sap/cds-foss: 5.0.1
@sap/cds-mtxs: 2.0.3
@sap/eslint-plugin-cds: 3.0.4
Node.js: v20.10.0
@pavlzk pavlzk added the bug Something isn't working label Aug 15, 2024
@patricebender patricebender self-assigned this Sep 2, 2024
patricebender added a commit that referenced this issue Sep 2, 2024
 we need to drill recursively into the function arguments found in
on-conditions and prepend aliases of the outer query in case of scoped
queries / expands

fixes #779
@patricebender
Copy link
Member

Thanks for reporting, should be fixed with #795

@patricebender patricebender added the cqn4sql all issues related to db-service/cqn4sql label Sep 2, 2024
@patricebender patricebender changed the title Associations with sql functions incorrect results Table alias of outer query is missing for refs in function args in expand / exists Sep 2, 2024
johannes-vogel added a commit that referenced this issue Sep 2, 2024
)

we need to drill recursively into the function arguments found in
on-conditions and prepend aliases of the outer query in case of scoped
queries / expands

fixes #779

Co-authored-by: Johannes Vogel <31311694+johannes-vogel@users.noreply.github.com>
johannes-vogel pushed a commit that referenced this issue Sep 4, 2024
🤖 I have created a release *beep* *boop*
---


<details><summary>db-service: 1.12.1</summary>

##
[1.12.1](db-service-v1.12.0...db-service-v1.12.1)
(2024-09-03)


### Fixed

* deep `groupby` expand queries
([#768](#768))
([5423cf3](5423cf3))
* **deep:** prevent false unique constraint errors and combine delete
queries ([#781](#781))
([01de95f](01de95f))
* **logging:** from changes in @sap/cds
([#791](#791))
([1e8bf06](1e8bf06))
* prepend aliases to refs within function args in on conditions
([#795](#795))
([9b34314](9b34314)),
closes [#779](#779)
* prevent $search queries from throwing
([#772](#772))
([cdf4d37](cdf4d37))
</details>

<details><summary>hana: 1.2.0</summary>

##
[1.2.0](hana-v1.1.1...hana-v1.2.0)
(2024-09-03)


### Added

* wrap client if @dynatrace/oneagent-sdk is present
([#777](#777))
([147b88e](147b88e))


### Fixed

* Add `{list:[{val}]}` json function support
([#784](#784))
([ce5f856](ce5f856))
* Allow applications to configure `acquireTimeoutMillis`
([#796](#796))
([abac818](abac818))
* deep `groupby` expand queries
([#768](#768))
([5423cf3](5423cf3))
* Enforce `LargeStrings` to be `Strings` for `sql_simple_queries=1`
([#774](#774))
([c38a9e6](c38a9e6))
* wrong falsy checks for vals in list optimization
([#797](#797))
([e818da8](e818da8))


### Changed

* requires @sap/cds &gt;= 8.2
([#789](#789))
([3bbde18](3bbde18))
</details>

---
This PR was generated with [Release
Please](https://github.com/googleapis/release-please). See
[documentation](https://github.com/googleapis/release-please#release-please).
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working cqn4sql all issues related to db-service/cqn4sql
Projects
None yet
2 participants