sqlite regexp() function doesn't behave like mysql

Bug #1298690 reported by Chris Friesen
12
This bug affects 2 people
Affects Status Importance Assigned to Milestone
OpenStack Compute (nova)
Won't Fix
Low
Unassigned
Icehouse
Won't Fix
Low
Unassigned

Bug Description

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.

Tags: db
Chris Friesen (cbf123)
tags: added: compute db
melanie witt (melwitt)
tags: removed: compute
Changed in nova:
importance: Undecided → Medium
status: New → Confirmed
Revision history for this message
Chris Friesen (cbf123) wrote :

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

Revision history for this message
Alan Pevec (apevec) wrote :

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

Revision history for this message
Jay Pipes (jaypipes) wrote :

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

Changed in nova:
importance: Medium → Low
Revision history for this message
Chris Friesen (cbf123) wrote :

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

Revision history for this message
Chris Friesen (cbf123) wrote :

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

Changed in nova:
status: Confirmed → Won't Fix
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers