Streamline case sensitive behavior of PropertyValueCriteria

[bwaidelich]

Currently the PropertyValueCriteriaInterface implementations make no assumption on whether the properties are compared in case sensitive or insensitive manner.

Tasks

• Document current behavior and add tests
• Consider adding support for explicit case sensitive mode (see related PR)

[bwaidelich]

Copied from Slack:

regarding properties and case sensitivity I see a lot of use of JSON_SEARCH in the codebase, for which documentation is spotty at best but this suggests it's case sensitive by default and would need a lowercase wrapping to make it case insensitive https://stackoverflow.com/questions/63242596/how-to-make-the-json-search-not-case-sensitive

Usually case (in)sensitivity depends on the collation (e.g. the "ci" in utf8_unicode_ci stands for case insensitive).

I tested the following with MariaDB 10.8:

SELECT
  JSON_EXTRACT(properties, '$.title.value')
FROM
  cr_default_p_graph_node
WHERE
  JSON_SEARCH(properties, 'one', 'home', NULL, '$.title.value') IS NOT NULL

Will return nodes with a title property of "Home" (upper case).
But if I change the WHERE part to

JSON_SEARCH(properties COLLATE utf8mb3_bin, 'one', 'home', NULL, '$.title.value') IS NOT NULL

Only nodes matching the exact case are returned.

For MySQL (8.0.33) the default behavior seems to be different and JSON_SEARCH works case sensitive
So to make that case insensitive we'd have to cast the incoming string like:

JSON_SEARCH(properties, 'one', 'home' COLLATE utf8mb4_unicode_ci, NULL, '$.title.value') IS NOT NULL;

=> I would suggest that we always specify the collation (for now _bin on both to reproduce todays case sensitive behavior, later depending on the flag if that is added to the PropertyValueCriteria)

Note: I'm a bit puzzled that the cr_default_p_graph_node is based on utf8mb3 and the incoming parameters on utf8mb4 (that's why I used utf8mb3_bin and utf8mb4_unicode_ci above for the casting)

For PostgreSQL everything is slightly different, but I imagine the solution to be similar.

that leads me to another question, JSON_SEARCH is (my|maria) specific so the dbal adapter is not compatible with anything but the two?

Yes, for now the adapter supports MariaDB and MySQL (and potentially SQLite?)

We'll have to double check that (and also the supported versions), document it and fail early if the platform is not supported (see #4337)

[kitsunet]

Great, thanks for testing this out! Sooo weird that they behave differently!

[mficzel]

Nut sure wether i like a flag or distinct criteria classes better since =~ is clearly different from =. In the end it does not matter that much so whatever gets build would be fine with me.