Documentation should indicate against relying on case-insensitivity when collating DB results

[d-w-moore]

Related: irods/python-irodsclient#600
Add to GenQuery 1 & 2 and/or specific query related docs( as well as in any more general DB / query related sections that might exist ) a general caution against depending on lexicographical order in result sets from any query in iRODS.

This is because:

In default installations of MySQL, select 1 where 'a' = 'A' yields a row containing 1 (ie, the condition is true). However, PostgreSQL considers 'A' > 'a' as being true, whereas SQLite3 evaluates 'a' > 'A' as true.

This will affect, at the very least, any of the following:

• sorting in ORDER BY
• result sets depending on > , = and < and similar operators when comparing strings
• result sets depending on BETWEEN string comparisons.

[d-w-moore]

This seems to affect GenQuery1 and would affect any query interface that is simply a pass-thru to the database for collative operators such as > and BETWEEN (as opposed to matching operators, such as LIKE or =). Consider if data objects named 'a', 'A' and 'a_' are defined (and for this simple case, no other data objects).

Then under PostgreSQL defaults:

$ iquest "select  DATA_NAME where DATA_NAME between 'a' 'a_'"
DATA_NAME = a
------------------------------------------------------------
DATA_NAME = a_
------------------------------------------------------------

but under MySQL defaults:

$ iquest "select  DATA_NAME where DATA_NAME between 'a' 'a_'"
DATA_NAME = a
------------------------------------------------------------
DATA_NAME = A
------------------------------------------------------------
DATA_NAME = a_
------------------------------------------------------------

This is consistent with the MySQL property of considering 'a' = 'A' to be true in MySQL, as noted in the description above . Although ,notably, given the aforementioned existing data objects, case-sensitive defaults for MySQL still do not consider them equal in matching contexts:

$ iquest "select  DATA_NAME where DATA_NAME = 'a'"
DATA_NAME = a
------------------------------------------------------------
$ iquest "select  DATA_NAME where DATA_NAME = 'A'"
DATA_NAME = A
------------------------------------------------------------

I would suggest it is higher priority to add a caution regarding this into the GenQuery 1 section of the docs, and perhaps that section only, as it is a legacy interface and therefore immutable. GenQuery 2 however will likely include improvements in the future to avoid the potential pitfalls of relying on fixed lexical order across databases.