Repository navigation
Test failures with mysql and mariadb #597
Description
When running tests against the tip of main on an iRODS 4.3.3-ish server I saw the following tests fail when using a mysql 8.4 or mariadb 11.4 server for hosting the iRODS catalog:
irods.test.genquery2_test.TestGenQuery2.test_select
irods.test.genquery2_test.TestGenQuery2.test_select_and
irods.test.genquery2_test.TestGenQuery2.test_select_or
irods.test.genquery2_test.TestGenQuery2.test_select_with_explicit_zone
irods.test.query_test.TestQuery.test_files_query_case_sensitive
Here are the details:
======================================================================
FAIL [0.184s]: test_select (irods.test.genquery2_test.TestGenQuery2)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/var/lib/irods/python-irodsclient/irods/test/genquery2_test.py", line 38, in test_select
self.assertEqual(query_sql, "select distinct t0.coll_name from R_COLL_MAIN t0 inner join R_OBJT_ACCESS pcoa on t0.coll_id = pcoa.object_id inner join R_TOKN_MAIN pct on pcoa.access_type_id = pct.token_id inner join R_USER_MAIN pcu on pcoa.user_id = pcu.user_id where t
0.coll_name = ? and pcoa.access_type_id >= 1000 fetch first 256 rows only")
AssertionError: 'sele[246 chars] = ? and pcoa.access_type_id >= 1000 limit 256' != 'sele[246 chars] = ? and pcoa.access_type_id >= 1000 fetch first 256 rows only'
Diff is 1225 characters long. Set self.maxDiff to None to see it.
======================================================================
FAIL [0.176s]: test_select_and (irods.test.genquery2_test.TestGenQuery2)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/var/lib/irods/python-irodsclient/irods/test/genquery2_test.py", line 86, in test_select_and
self.assertEqual(query_sql, "select distinct t0.coll_name from R_COLL_MAIN t0 inner join R_OBJT_ACCESS pcoa on t0.coll_id = pcoa.object_id inner join R_TOKN_MAIN pct on pcoa.access_type_id = pct.token_id inner join R_USER_MAIN pcu on pcoa.user_id = pcu.user_id where t
0.coll_name like ? and t0.coll_name like ? and pcoa.access_type_id >= 1000 fetch first 256 rows only")
AssertionError: 'sele[273 chars]ke ? and pcoa.access_type_id >= 1000 limit 256' != 'sele[273 chars]ke ? and pcoa.access_type_id >= 1000 fetch first 256 rows only' Diff is 1333 characters long. Set self.maxDiff to None to see it.
======================================================================
FAIL [0.270s]: test_select_or (irods.test.genquery2_test.TestGenQuery2)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/var/lib/irods/python-irodsclient/irods/test/genquery2_test.py", line 75, in test_select_or
self.assertEqual(query_sql, "select distinct t0.coll_name from R_COLL_MAIN t0 inner join R_OBJT_ACCESS pcoa on t0.coll_id = pcoa.object_id inner join R_TOKN_MAIN pct on pcoa.access_type_id = pct.token_id inner join R_USER_MAIN pcu on pcoa.user_id = pcu.user_id where t
0.coll_name = ? or t0.coll_name = ? and pcoa.access_type_id >= 1000 fetch first 256 rows only")
AssertionError: 'sele[266 chars] = ? and pcoa.access_type_id >= 1000 limit 256' != 'sele[266 chars] = ? and pcoa.access_type_id >= 1000 fetch first 256 rows only'
Diff is 1305 characters long. Set self.maxDiff to None to see it.
======================================================================
FAIL [0.308s]: test_select_with_explicit_zone (irods.test.genquery2_test.TestGenQuery2)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/var/lib/irods/python-irodsclient/irods/test/genquery2_test.py", line 49, in test_select_with_explicit_zone
self.assertEqual(query_sql, "select distinct t0.coll_name from R_COLL_MAIN t0 inner join R_OBJT_ACCESS pcoa on t0.coll_id = pcoa.object_id inner join R_TOKN_MAIN pct on pcoa.access_type_id = pct.token_id inner join R_USER_MAIN pcu on pcoa.user_id = pcu.user_id where t
0.coll_name = ? and pcoa.access_type_id >= 1000 fetch first 256 rows only")
AssertionError: 'sele[246 chars] = ? and pcoa.access_type_id >= 1000 limit 256' != 'sele[246 chars] = ? and pcoa.access_type_id >= 1000 fetch first 256 rows only'
Diff is 1225 characters long. Set self.maxDiff to None to see it.
======================================================================
FAIL [0.770s]: test_files_query_case_sensitive (irods.test.query_test.TestQuery)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/var/lib/irods/python-irodsclient/irods/test/query_test.py", line 188, in test_files_query_case_sensitive
self.assertEqual(len(result13), 1)
AssertionError: 2 != 1
I think the first few are pretty obviously postgres-dependent, and were introduced here: #555
I'm not sure about that last one. Maybe it has to do with case sensitive / insensitive search and defaults, or something.
Anyway, we should investigate this.
Activity
korydraughn commentedon Jul 30, 2024
The tests shouldn't assert equality of the SQL generated by GenQuery2 since it can change over time.
Those assertions need to be removed or changed to assert specific character sequences appear in the result.
d-w-moore commentedon Jul 31, 2024
I think one shouldn't have to assert those anyway. The only needed assertions should be that the results of a query match the expectations of the test, based on the conditions that were set up.
d-w-moore commentedon Jul 31, 2024
Maybe I'm just repeating what @korydraughn has said, though ; )
alanking commentedon Aug 1, 2024
That last test is also a subtle assumption about the database being postgres. This test failure spawned this issue, so I put the details over here: irods/irods#7930
We just need to tweak the test to change the assertion. However, I'm not sure what we want to assert here:
For postgres, we want to assert that only 1 result came back because there is only 1 data object with the name for which we are searching if the query is case sensitive (as it should be in this test). For mysql, the
betweenclause will effectively return a case-insensitive resultset and this is also correct for the mysql implementation of thebetweenclause. If we assert "at least 1" result, it feels like the test is moot because running a case-insensitive search would pass that assertion, too.I'm thinking about splitting this one out into a separate issue and just commenting out this part of the test until we can decide on what to do with it. This does not seem like a blocker for 2.1.0 to me. Thoughts?
korydraughn commentedon Aug 1, 2024
As long as the PRC can demonstrate hitting the GenQuery2 API endpoint successfully, that's all that matters.
Making assertions about the SQL returned by the GenQuery2 API isn't important for the PRC. If we must cover that case, then a few assertions like the following are good enough.
Given that GenQuery2 is experimental, it shouldn't block a release.
[irods#597] genquery2_test: Replace Postgres-specific assertions
[#597] genquery2_test: Replace Postgres-specific assertions
alanking commentedon Aug 1, 2024
The genquery2 tests have been fixed and are now passing for all the database flavors we support. The
irods.test.query_test.TestQuery.test_files_query_case_sensitivetest will be handled separately in #600 because it is a special case. Closing this one.