Closed
Description
Hello,
We have Product entity in our domain which has ManyToMany relation to Package entity:
Product
@Entity(name = "product")
@Table(name = "tenants_products")
public class ProductEntity {
...
@ManyToMany(mappedBy = "products", fetch = FetchType.LAZY)
private Set<PackageEntity> packages;
...
}Package
@Entity(name = "package")
@Table(name = "tenants_packages")
public class PackageEntity {
...
@Column(name = "title", length = 256, nullable = false)
private String title;
@ElementCollection
@CollectionTable(name = "tags_packages", joinColumns = @JoinColumn(name = "package_id"))
private List<ArrayElement> tags;
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(
name = "package_product",
joinColumns = {
@JoinColumn(name = "package_id", referencedColumnName = "id"),
@JoinColumn(name = "formation_id", referencedColumnName = "formation_id"),
},
inverseJoinColumns = {
@JoinColumn(name = "product_id", referencedColumnName = "id"),
@JoinColumn(name = "formation_id", referencedColumnName = "formation_id"),
}
)
private Set<ProductEntity> products;
...
}If we filter by "primitive" type field (title in the example) using the lambda operator any (same applies for all) it can be seen that permission check clause is added.
Filtering request:
/v0/products?$filter=packages/any(p:p/title eq 'test-package-1')&$expand=packages
Generated SQL query:
SELECT DISTINCT t0.description,
t0.formation_id,
t0.short_description,
t0.parent,
t0.ord_id,
t0.id,
t0.title
FROM tenants_products t0
WHERE (EXISTS
(SELECT t1.id
FROM package_product t2,
tenants_packages t1
WHERE (((((t2.product_id = t0.id)
AND (t2.formation_id = t0.formation_id))
AND ((t1.id = t2.package_id)
AND (t1.formation_id = t2.formation_id)))
AND ((t1.formation_id = ?)
AND (t1.tenant_id = ?))) --- this is the permission check
AND (t1.title = ?)))
AND ((t0.formation_id = ?)
AND (t0.tenant_id = ?)))However, if we filter by "@ElementCollection" field (tags in the example) using the lambda operator any (same applies for all), it can be seen that a permission check clause is missing in the SELECT under EXISTS:
Filtering request:
/v0/packages?$filter=tags/any(t:t/value eq 'foo-tag')
Generated SQL query:
SELECT DISTINCT t0.version,
t0.description,
t0.runtime_restriction,
t0.ord_id,
t0.licence_type,
t0.custom_policy_level,
t0.support_info,
t0.title,
t0.policy_level,
t0.id,
t0.short_description
FROM tenants_packages t0
WHERE (EXISTS
(SELECT t1.package_id
FROM tenants_packages t2,
tags_packages t1
WHERE (((t2.id = t0.id)
AND (t1.package_id = t2.id))
AND (t1.value = ?))
)
AND ((t0.formation_id = ?)
AND (t0.tenant_id = ?)))We suspect that the missing clause cause a performance degradation on our service in the cases when this specific filtering may be used because a lot of data has to be loaded.
Note: this filtering behaviour is observed across many entities, I used Products and Packages for example purposes.
To locally reproduce this issue, you can:
- start the service locally from the
mainbranch - execute these insert statements so that you have the required bare minimum of entities needed
INSERT INTO public.business_tenant_mappings (id, external_name, external_tenant, provider_name, status, type) VALUES ('3e64ebae-38b5-46a0-b1ed-9ccee153a0ae', 'default', '3e64ebae-38b5-46a0-b1ed-9ccee153a0ae', 'Compass', 'Active', 'account') ON CONFLICT DO NOTHING;
INSERT INTO public.business_tenant_mappings (id, external_name, external_tenant, provider_name, status, type) VALUES ('1eba80dd-8ff6-54ee-be4d-77944d17b10b', 'foo', '1eba80dd-8ff6-54ee-be4d-77944d17b10b', 'Compass', 'Active', 'account') ON CONFLICT DO NOTHING;
INSERT INTO public.business_tenant_mappings (id, external_name, external_tenant, provider_name, status, type) VALUES ('af9f84a9-1d3a-4d9f-ae0c-94f883b33b6e', 'bar', 'af9f84a9-1d3a-4d9f-ae0c-94f883b33b6e', 'Compass', 'Active', 'account') ON CONFLICT DO NOTHING;
--
INSERT INTO public.applications (id, name, description, status_condition, status_timestamp, healthcheck_url, integration_system_id, provider_name, base_url, labels, ready, created_at, updated_at, deleted_at, error, app_template_id, correlation_ids, system_number, documentation_labels, system_status, local_tenant_id, application_namespace, tags) VALUES ('4c63e3b2-3301-4796-bc95-9fb5b2780342', 'system-instance-name', null, 'INITIAL', '2024-02-28 11:56:35.483467', null, null, 'compass', null, null, true, '2024-02-28 11:56:35.483562', null, null, null, null, null, null, null, null, null, null, null);
--
INSERT INTO public.tenant_applications (tenant_id, id, owner, source) VALUES ('3e64ebae-38b5-46a0-b1ed-9ccee153a0ae', '4c63e3b2-3301-4796-bc95-9fb5b2780342', true, '3e64ebae-38b5-46a0-b1ed-9ccee153a0ae');
---
INSERT INTO public.app_templates (id, name, description, application_input, placeholders, access_level, application_namespace, created_at, updated_at) VALUES ('49d9b48b-efea-4935-a7f3-c0ca61223206', 'SAP app-template-name', 'app-template-desc', '{"name": "{{name}}", "labels": {"a": ["b", "c"], "d": ["e", "f"], "applicationType": "SAP app-template-name"}, "baseUrl": null, "bundles": null, "webhooks": [{"url": "http://url.com", "auth": null, "mode": null, "type": "CONFIGURATION_CHANGED", "timeout": null, "version": null, "urlTemplate": null, "inputTemplate": null, "retryInterval": null, "headerTemplate": null, "outputTemplate": null, "statusTemplate": null, "correlationIdKey": null}], "description": "test {{display-name}}", "providerName": "compass-tests", "localTenantID": null, "healthCheckURL": "http://url.valid", "statusCondition": null, "integrationSystemID": null, "applicationNamespace": null}', '[{"Name": "name", "JSONPath": "new-placeholder-name-json-path", "Optional": null, "Description": "app"}, {"Name": "display-name", "JSONPath": "new-placeholder-display-name-json-path", "Optional": null, "Description": "new-placeholder-display-name"}]', 'GLOBAL', null, '2024-03-07 17:39:49.711056', '2024-03-07 17:39:49.711056');
---
INSERT INTO public.app_template_versions (id, app_template_id, version, title, correlation_ids, release_date, created_at) VALUES ('54d9b48b-efea-4935-a7f3-c0ca61223206', '49d9b48b-efea-4935-a7f3-c0ca61223206', '1', null, null, null, '2024-03-07 17:40:12.000000');
---
INSERT INTO public.vendors (ord_id, app_id, title, labels, partners, id, documentation_labels, tags, app_template_version_id) VALUES ('vendor:ord.id', '4c63e3b2-3301-4796-bc95-9fb5b2780342', 'vendor-1', null, null, '2c23e3b2-3301-4796-bc95-9fb5b2780342', null, null, '54d9b48b-efea-4935-a7f3-c0ca61223206');
INSERT INTO public.vendors (ord_id, app_id, title, labels, partners, id, documentation_labels, tags, app_template_version_id) VALUES ('globalvendor:ord.id', null, 'global-vendor-1', null, null, '6c23e3b2-3301-4796-bc95-9fb5b2780342', null, null, '54d9b48b-efea-4935-a7f3-c0ca61223206');
---
INSERT INTO public.products (ord_id, app_id, title, short_description, vendor, parent, labels, correlation_ids, id, documentation_labels, tags, app_template_version_id, description) VALUES ('ord:product.id', '4c63e3b2-3301-4796-bc95-9fb5b2780342', 'test-product-1', 'short desc', 'vendor:ord.id', null, null, null, '1c23e3b2-3301-4796-bc95-9fb5b2780342', null, null, '54d9b48b-efea-4935-a7f3-c0ca61223206', 'desc');
INSERT INTO public.products (ord_id, app_id, title, short_description, vendor, parent, labels, correlation_ids, id, documentation_labels, tags, app_template_version_id, description) VALUES ('ord:product2.id', '4c63e3b2-3301-4796-bc95-9fb5b2780342', 'test-product-2', 'short desc', 'vendor:ord.id', null, null, null, '9c23e3b2-3301-4796-bc95-9fb5b2780342', null, null, '54d9b48b-efea-4935-a7f3-c0ca61223206', 'desc');
---
INSERT INTO public.packages (id, ord_id, title, short_description, description, version, package_links, links, licence_type, tags, countries, labels, policy_level, app_id, custom_policy_level, vendor, part_of_products, line_of_business, industry, resource_hash, documentation_labels, support_info, app_template_version_id, runtime_restriction) VALUES ('fd5e9820-a8ae-4748-bf3f-049ef91804fa', ':package:manuallyAddedIntegrationDependencies:v1', 'test-package-1', 'Manually added package', 'This is test package 1', '1.0.0', null, null, null, '["test-tag1","test-tag2"]', null, null, 'sap:core:v1', '4c63e3b2-3301-4796-bc95-9fb5b2780342', null, 'vendor:ord.id', '["ord:product.id"]', null, null, null, null, null, null, null);
INSERT INTO public.packages (id, ord_id, title, short_description, description, version, package_links, links, licence_type, tags, countries, labels, policy_level, app_id, custom_policy_level, vendor, part_of_products, line_of_business, industry, resource_hash, documentation_labels, support_info, app_template_version_id, runtime_restriction) VALUES ('e01fbd9b-c73c-4ed9-9aa3-9695cbcd8bf0', ':package:manuallyAddedIntegrationDependencies:v2', 'test-package-2', 'Manually added package', 'This is test package 2', '1.0.0', null, null, null, '["foo-tag","bar-tag"]', null, null, 'sap:core:v1', '4c63e3b2-3301-4796-bc95-9fb5b2780342', null, 'vendor:ord.id', '["ord:product2.id"]', null, null, null, null, null, null, null);