`irods.test.query_test.TestQuery.test_files_query_case_sensitive`'s `between` test fails for mysql

[alanking]

This was first discovered in #597. See also: #597 (comment) and irods/irods#7930

We need to be able to detect the database type being used in the iRODS server and change the assertions based on that.

[alanking]

This sub-test(ish) is now being skipped as of PR #601. A TODO linked to this issue has been left next to the code which needs to be fixed:

python-irodsclient/irods/test/query_test.py

Lines 181 to 184 in 7af55af

	# BETWEEN tests
	# TODO(#600): Uncomment these lines and/or make a new test when database flavor can be detected.
	# The resultset for BETWEEN queries can differ from database to database.

[d-w-moore]

Re:

python-irodsclient/irods/test/query_test.py

Lines 181 to 184 in 7af55af

	# BETWEEN tests
	# TODO(#600): Uncomment these lines and/or make a new test when database flavor can be detected.
	# The resultset for BETWEEN queries can differ from database to database.

1. Are we better able to detect the DB flavor now?
2. What is the specific issue in the BETWEEN operator when using MySQL? What makes its behavior different to other DB's?

[korydraughn]

1. Are we better able to detect the DB flavor now?

No. That isn't something clients should care about. The test and/or test harness needs to provide that information. Alternatively, you can try and adjust the assertions so that the test passes regardless of the database.

2. What is the specific issue in the BETWEEN operator when using MySQL? What makes its behavior different to other DB's?

That is best answered by running an experiment and/or reading the docs.

• https://dev.mysql.com/doc/refman/8.4/en/comparison-operators.html#operator_between
• https://www.postgresql.org/docs/current/functions-comparison.html

Use of the BETWEEN keyword is optional. BETWEEN can likely be replaced by explicit range checks, leading to more consistent results across database technologies. However, there may be other factors at play. For that reason, reading the docs, examining the values used in the test, running experiments, and considering the database's configuration will help in figuring out the difference.

[d-w-moore]

Thanks @korydraughn . I did do a little experimentation. MySQL seems to fuzz the case by default, where as eg PostgreSQL does not.

[korydraughn]

It's not clear to me what you mean by "fuzz". Please explain.

[d-w-moore]

That was inaccurate of me. By "fuzz" I really just meant the MySQL BETWEEN is doing character comparisons/sorting case-insensitively regardless of what case we throw at it with our object names. So MySQL query results might be influenced by its differently-sorted inputs to the BETWEEN operator according to:
'a' < 'B' < 'c'
rather than, for example:
'B' < 'a' < 'c'
as you might expect from the characters' ASCII codes.

I understand from past experience there are locales in which case insensitive ordering is enforced. Our mysql DB might, in a default installation, have such a locale set for itself; therefore its BETWEEN operator might operate on top of that altered (relative to the other DBs) sort order, rendering results differently.

I kinda repeated myself there, so pardonnez-moi si vous plait. :)

[d-w-moore]

As I stated during previous group discussions, I may end up moving the failing parts of the mysql test to a separate test that only runs when the client unittest is run on the same node as the server and DB. (Such a test harness exists in branch https://github.com/d-w-moore/python-irodsclient/tree/502.m

[korydraughn]

Makes sense.

That branch targets #502 which is part of the 3.2.0 milestone. Sounds like this issue should be moved into that milestone as well. You agree?