sqlite regexp() function doesn't behave like mysql

[Chris Friesen]

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

The issue seems to be when we do

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

Eventually down in db/sqlalchemy/api.py we end 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 we provide one in openstack/common/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.

[Chris Friesen (cbf123)]

If this is "fix released" in icehouse, could someone point to the patch that fixed it?

[Alan Pevec (apevec)]

Sorry, script misdetected " (tracked under bug 1298690)" from commit 40ae1eefe42f093631e2988814e60f45defdb3c3

[Jay Pipes (jaypipes)]

Setting to Low priority because nobody uses SQLite in production. Would be nice to fix, but not medium priority...

[Chris Friesen (cbf123)]

Just in case anyone is interested...the regex used by SQLite is now in a separate package. oslo.db/oslo_db/sqlalchemy/session.py

[Chris Friesen (cbf123)]

I've opened bug 1439455 against oslo.db, so I'm not sure where that leaves this bug...