Greetings: In short: Should it be possible to select by key that is NULL in our DBs? This is needed for Cinder, review 657543[1]. Tests run on sqlite. Long explanation: The review tinkers with SQL Alchemy's query, so it may be doing it wrong. However, I looked at the queries and this seems to happen. A query for everything finds the NULL entry. The query looks like this: SELECT backups.created_at AS backups_created_at, (... very many AS ...) FROM backups LEFT OUTER JOIN backup_metadata AS backup_metadata_1 ON backup_metadata_1.backup_id = backups.id AND backup_metadata_1.deleted = false WHERE backups.deleted = false AND backups.host = %(host_1)s So, the entry is in the database. However, this one returns everything that matches the arguments, but not the one with key of NULL: SELECT backups.created_at AS backups_created_at, (... very many AS ...) FROM backups LEFT OUTER JOIN backup_metadata AS backup_metadata_1 ON backup_metadata_1.backup_id = backups.id AND backup_metadata_1.deleted = false WHERE backups.deleted = false AND backups.status IN (%(status_1)s, %(status_2)s, %(status_3)s, %(status_4)s, %(status_5)s, %(status_6)s, NULL) AND backups.host = %(host_1)s ORDER BY backups.created_at DESC, backups.id DESC The backup with a NULL status is in the DB, but I cannot get it with objects.BackupList.get_all(ctxt, filters={'status': (None,)}) One other thing: Is there an easy way to make negative query through SQLalchemy? My real task is to find all the backups that are not stable (complete and error), so that I can restart it. Thanks for any suggestions, -- Pete [1] https://review.opendev.org/c/openstack/cinder/+/657543
On 01/22/24 16:15, Pete Zaitcev wrote:
Greetings:
In short: Should it be possible to select by key that is NULL in our DBs? This is needed for Cinder, review 657543[1]. Tests run on sqlite.
Long explanation:
The review tinkers with SQL Alchemy's query, so it may be doing it wrong. However, I looked at the queries and this seems to happen.
A query for everything finds the NULL entry. The query looks like this:
SELECT backups.created_at AS backups_created_at, (... very many AS ...) FROM backups LEFT OUTER JOIN backup_metadata AS backup_metadata_1 ON backup_metadata_1.backup_id = backups.id AND backup_metadata_1.deleted = false WHERE backups.deleted = false AND backups.host = %(host_1)s
So, the entry is in the database. However, this one returns everything that matches the arguments, but not the one with key of NULL:
SELECT backups.created_at AS backups_created_at, (... very many AS ...) FROM backups LEFT OUTER JOIN backup_metadata AS backup_metadata_1 ON backup_metadata_1.backup_id = backups.id AND backup_metadata_1.deleted = false WHERE backups.deleted = false AND backups.status IN (%(status_1)s, %(status_2)s, %(status_3)s, %(status_4)s, %(status_5)s, %(status_6)s, NULL) AND backups.host = %(host_1)s ORDER BY backups.created_at DESC, backups.id DESC
The backup with a NULL status is in the DB, but I cannot get it with objects.BackupList.get_all(ctxt, filters={'status': (None,)})
Have you tried using sql.null()? https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expres... You can find examples by searching for the literal string "sql.null()": https://codesearch.openstack.org/?q=sql.null()&i=nope&literal=fosho&files=&excludeFiles=&repos=
One other thing: Is there an easy way to make negative query through SQLalchemy? My real task is to find all the backups that are not stable (complete and error), so that I can restart it.
For that you could use not_in() or notin_(), but you would have to implement something a bit lower level. I don't see a way to do it with what BackupList.get_by_all() is currently doing. https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expres... Examples by searching for literal string "notin_(": https://codesearch.openstack.org/?q=notin_(&i=nope&literal=fosho&files=&excludeFiles=&repos= HTH, -melwitt
Thanks for any suggestions, -- Pete
On 22/01, Pete Zaitcev wrote:
Greetings:
In short: Should it be possible to select by key that is NULL in our DBs? This is needed for Cinder, review 657543[1]. Tests run on sqlite.
Long explanation:
The review tinkers with SQL Alchemy's query, so it may be doing it wrong. However, I looked at the queries and this seems to happen.
A query for everything finds the NULL entry. The query looks like this:
SELECT backups.created_at AS backups_created_at, (... very many AS ...) FROM backups LEFT OUTER JOIN backup_metadata AS backup_metadata_1 ON backup_metadata_1.backup_id = backups.id AND backup_metadata_1.deleted = false WHERE backups.deleted = false AND backups.host = %(host_1)s
So, the entry is in the database. However, this one returns everything that matches the arguments, but not the one with key of NULL:
SELECT backups.created_at AS backups_created_at, (... very many AS ...) FROM backups LEFT OUTER JOIN backup_metadata AS backup_metadata_1 ON backup_metadata_1.backup_id = backups.id AND backup_metadata_1.deleted = false WHERE backups.deleted = false AND backups.status IN (%(status_1)s, %(status_2)s, %(status_3)s, %(status_4)s, %(status_5)s, %(status_6)s, NULL) AND backups.host = %(host_1)s ORDER BY backups.created_at DESC, backups.id DESC
Hi, As far as I know this is SQL thing and is not specific to SQlite. NULL is treated differently than normal values, for example in the IN operator it won't work (just like you've observed). When I worked to remove the API race condition in Cinder by using atomic conditional updates I had to consider cases where `None` was an element of a list and change how the filter was generated and used the `or_` operator instead: orm_field = getattr(model, field) # For values that must match and are iterables we use IN if (isinstance(value, collections.Iterable) and not isinstance(value, six.string_types)): # We cannot use in_ when one of the values is None if None not in value: return orm_field.in_(value) return or_(orm_field == v for v in value) # For values that must match and are not iterables we use == return orm_field == value
The backup with a NULL status is in the DB, but I cannot get it with objects.BackupList.get_all(ctxt, filters={'status': (None,)})
If you follow the Cinder calls of that method you'll end up in the `_process_backups_filters` of the `cinder/db/sqlalquemy/api.py` file and you'll see that if you only want the status to support an iterable with one of it's elements being `None` you either have to apply the same kind of code of above or do it with an or of the IS NULL and the IN. If you don't want an iterable and just want to check for None, I think it may work doing: objects.BackupList.get_all(ctxt, filters={'status': None}) Hope that helps. Cheers, Gorka.
One other thing: Is there an easy way to make negative query through SQLalchemy? My real task is to find all the backups that are not stable (complete and error), so that I can restart it.
Thanks for any suggestions, -- Pete
On Wed, 2024-01-24 at 12:35 +0100, Gorka Eguileor wrote:
orm_field = getattr(model, field) # For values that must match and are iterables we use IN if (isinstance(value, collections.Iterable) and not isinstance(value, six.string_types)): # We cannot use in_ when one of the values is None if None not in value: return orm_field.in_(value)
return or_(orm_field == v for v in value)
Thanks for the tip. I ended adding another way to penetrate Cinder's layers and tinker with the query of sqlalchemy. The latter has a magical filter with "is_" that creates "WHERE Foo IS NULL" in the SQL statement. column_attr = getattr(models.Backup, key) query = query.filter(column_attr.is_(None)) See https://review.opendev.org/c/openstack/cinder/+/657543 Anyway, thanks again -- P
participants (3)
-
Gorka Eguileor
-
melanie witt
-
Pete Zaitcev