Missing permission check clause when filtering by @ElementCollection using lambda operator

[nyordanoff]

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 main branch
• 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);

[nyordanoff]

I was using version 2.0.2 of the JPA Processor. When bumping it to version 2.1.1 the query was optimised which led to improved performance.

I am closing this issue because currently I don't think there is need for the suggested permission check.