fix: cqn transformation is OData agnostic

[patricebender]

[danjoa]

As discussed we need to revisit the way we translate OData $apply requests to valid queries, and have those queries translated to native SQL without any assumption of its origin from OData. → https://github.com/cap-js/cds-dbs/pull/990/commits/bed7329671f3a82b5220ab78784d0ab1e6c4bb6a adds comments to relevant tests about expected valid queries.

[danjoa]

Moreover, and more importantly, we have to ensure that we correctly implement group by, also for things which OData cannot express. In particular, for expands of to-many associations like that:

SELECT from Orders {
   ID, sum (Items.product.price * Items.quantity) as total,
   Items { product.name, quantity }
} group by ID

[patricebender]

As discussed we need to revisit the way we translate OData $apply requests to valid queries, and have those queries translated to native SQL without any assumption of its origin from OData. → bed7329 adds comments to relevant tests about expected valid queries.

Agree 👍

Moreover, and more importantly, we have to ensure that we correctly implement group by, also for things which OData cannot express. In particular, for expands of to-many associations like that:

SELECT from Orders {
   ID, sum (Items.product.price * Items.quantity) as total,
   Items { product.name, quantity }
} group by ID

Also agree, however I would make vote for a two step approach:

1. revert changes in db-service which implemented OData stuff on cqn transformation level, to be OData agnostic again → coordinate with runtime to make the OData adapter come up with a proper CQN which is then transformed as usual.
2. Come up with a plan to implement group by, which also depends on the actual DB-Driver we are talking to

[patricebender]

@johannes-vogel I reverted the special handling of expand + group by which we added due to issues of OData $apply + $expand. Now there are some tests failing (the ones Daniel already commented on)

I think the next step would be for the runtime to patch the OData adapter in a way, that the CQNs are generated so that the tests will work again, even without cqn4sql doing some magic for those cases. Will you dispatch that?