sqlite regexp() function doesn't behave like mysql

[Chris Friesen]

I reported this originally against nova, but it seems to be in oslo.db now.

In nova bug 1298494 I saw a case where the unit tests (using sqlite) behaved differently than devstack with mysql.

The issue seems to be when nova did:

filters = {'uuid': group.members, 'deleted_at': None}
instances = instance_obj.InstanceList.get_by_filters(
                context, filters=filters)

Eventually down in nova/db/sqlalchemy/api.py we ended up calling

query = query.filter(column_attr.op(db_regexp_op)(
                                 str(filters[filter_name])))

where str(filters[filter_name]) is the string 'None'.

When using mysql, a regexp comparison of the string 'None' against a NULL field fails to match.

Since sqlite doesn't have its own regexp function oslo.db provides one in oslo_db/sqlalchemy/session.py. In the buggy case we end up calling it as regexp('None', None), where the types are "unicode" and "NoneType". However, we end up converting the second arg to text type before calling reg.search() on it, so it matches.

This is a bug, we want the unit tests to behave like the real system.