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