This largely comes down to how Specify 6 and Specify 7 manages Boolean (True/False) fields in the database.

Specify 6 used a Java library called Hibernate, which handled Booleans by using a BIT(1) column type - a single bit value; thus boolean fields were created with that data type.

While integrating Django within Specify 7, we had to extend the default database engine so that Django can translate the literal bit values to boolean values:

https://github.com/specify/specify7/tree/production/specifyweb/hibernateboolsbackend

For its database purposes, Django uses the bool datatype, which the database vendor interprets as a TINYINT data type:

https://github.com/django/django/blob/40d5516385448a73426aad396778f369a363eda9/django/db/backends/mysql/base.py#L112

https://dev.mysql.com/doc/refman/8.4/en/other-vendor-data-types.html

From a database perspective then, we have two different types of boolean fields. The boolean fields created in Specify 6 use BIT(1), while any new boolean fields created via migrations have the TINYINT datatype.

Django can handle the discrepancy because of the extended engine, but sqlalchemy has no such custom solution: which causes this Issue.

Currently, all boolean fields are being converted to the BIT(1) type for sqlalchemy:

This Issue arose because of the recent addition of the isStrict field to the SpQueryField table from #5272, but theoretically any Specify 7 boolean field created from migrations could cause this problem.

Whenever a query's fields are accessed to create a report template, MySQL and/or MariaDB have the incorrect column mapping (whenever the value of the boolean field is NULL), which causes the error:

I think a solution to this problem should try and avoid as much technical debt as possible. Having a custom field or attribute on a field (on either the models or datamodel) which assures migrations correctly sets the data type might be an OK solution in the short term, but will cost much more time and mental overhead in the future: we have to remember to always use the solution lest the problem crop up again, and instruct new developers to do the same.

In my opinion, an ideal solution would allow us to define boolean fields as we have and operate in the background (much like the existing custom engine for Django and BIT to Boolean field).

I've been looking into some solutions with using sqlalchemy reflect/inspection tools¹ to read and inspect the columns of tables in the database before they are mapped with the sqlalchemy orm.
I'm open for other ideas as well!

Hmm I suspect the error is because of this line. Could you try opening that in a debugger and see the backtrace? I think it allows you to step inside library functions (python).

https://github.com/sqlalchemy/sqlalchemy/blob/5ad094729b8432cf0b1e08c03caa44f37ab68f88/lib/sqlalchemy/dialects/mysql/types.py#L384-L394

I also suspect that, when using tinyint, value is int (rather than bitstring) thus the iterable error.

Could you, potentially, make a custom type (eh, subclass that BIT class) and override result_processor so that checks if the value is bitstring in the result_processor function (and if not, just returns that value)? The moment 6 is retired, you could make a migration to replace it in the database. Maybe devs should have been using that in the first place though (bit) in sp7.

But, auto inspection and reflection would be cooler. I suspect this issue also breaks the new boolean fields introduced (isPrimary, isSubstrate etc) in query builder. Do those still work?