db: Resolve additional SAWarning warnings

Resolving the following SAWarning warnings:

  Coercing Subquery object into a select() for use in IN(); please pass
  a select() construct explicitly

  SELECT statement has a cartesian product between FROM element(s)
  "foo" and FROM element "bar". Apply join condition(s) between each
  element to resolve.

While the first of these was a trivial fix, the second one is a little
more involved. It was caused by attempting to build a query across
tables that had no relationship as part of our archive logic. For
example, consider the following queries, generated early in
'_get_fk_stmts':

  SELECT instances.uuid
  FROM instances, security_group_instance_association
  WHERE security_group_instance_association.instance_uuid = instances.uuid
    AND instances.id IN (__[POSTCOMPILE_id_1])

  SELECT security_groups.id
  FROM security_groups, security_group_instance_association, instances
  WHERE security_group_instance_association.security_group_id = security_groups.id
    AND instances.id IN (__[POSTCOMPILE_id_1])

While the first of these is fine, the second is clearly wrong: why are
we filtering on a field that is of no relevance to our join? These were
generated because we were attempting to archive one or more instances
(in this case, the instance with id=1) and needed to find related tables
to archive at the same time. A related table is any table that
references our "source" table - 'instances' here - by way of a foreign
key. For each of *these* tables, we then lookup each foreign key and
join back to the source table, filtering by matching entries in the
source table. The issue here is that we're looking up every foreign key.
What we actually want to do is lookup only the foreign keys that point
back to our source table. This flaw is why we were generating the second
SELECT above: the 'security_group_instance_association' has two foreign
keys, one pointing to our 'instances' table but also another pointing to
the 'security_groups' table. We want the first but not the second.

Resolve this by checking if the table that each foreign key points to is
actually the source table and simply skip if not. With this issue
resolved, we can enable errors on SAWarning warnings in general without
any filters.

Conflicts:
    nova/tests/fixtures/nova.py

NOTE(melwitt): The conflict is because change
I63f57980e01f472a25821790610f0836f1882a7f (tests: Restore - don't reset
- warning filters) is not in Xena.

Change-Id: I63208c7bd5f9f4c3d5e4a40bd0f6253d0f042a37
Signed-off-by: Stephen Finucane <sfinucan@redhat.com>
(cherry picked from commit 8142b9dc47b7096ab9d8180f0b5b1e52d513e2dc)
(cherry picked from commit ce2cc54bfe236554badb9f6bf53a958417e5525d)

3 files changed, 25 insertions, 4 deletions

diff --git a/nova/db/main/api.py b/nova/db/main/api.py
index 0ae9a453b8..32a2e2c8e0 100644
--- a/nova/db/main/api.py
+++ b/nova/db/main/api.py
@@ -4332,6 +4332,12 @@ def _get_fk_stmts(metadata, conn, table, column, records):
             fk_column = fk_table.c.id
 
         for fk in fk_table.foreign_keys:
+            if table != fk.column.table:
+                # if the foreign key doesn't actually point to the table we're
+                # archiving entries from then it's not relevant; trying to
+                # resolve this would result in a cartesian product
+                continue
+
             # We need to find the records in the referring (child) table that
             # correspond to the records in our (parent) table so we can archive
             # them.
@@ -4378,6 +4384,7 @@ def _get_fk_stmts(metadata, conn, table, column, records):
                 # deque.
                 fk_delete = fk_table.delete().where(fk_column.in_(fk_records))
                 deletes.appendleft(fk_delete)
+
         # Repeat for any possible nested child tables.
         i, d = _get_fk_stmts(metadata, conn, fk_table, fk_column, fk_records)
         inserts.extendleft(i)
diff --git a/nova/objects/cell_mapping.py b/nova/objects/cell_mapping.py
index 595ec43e48..1355182420 100644
--- a/nova/objects/cell_mapping.py
+++ b/nova/objects/cell_mapping.py
@@ -279,11 +279,15 @@ class CellMappingList(base.ObjectListBase, base.NovaObject):
         # SELECT DISTINCT cell_id FROM instance_mappings \
         #   WHERE project_id = $project_id;
         cell_ids = context.session.query(
-            api_db_models.InstanceMapping.cell_id).filter_by(
-            project_id=project_id).distinct().subquery()
+            api_db_models.InstanceMapping.cell_id
+        ).filter_by(
+            project_id=project_id
+        ).distinct()
         # SELECT cell_mappings WHERE cell_id IN ($cell_ids);
-        return context.session.query(api_db_models.CellMapping).filter(
-            api_db_models.CellMapping.id.in_(cell_ids)).all()
+        return context.session.query(
+            api_db_models.CellMapping).filter(
+            api_db_models.CellMapping.id.in_(cell_ids)
+        ).all()
 
     @classmethod
     def get_by_project_id(cls, context, project_id):
diff --git a/nova/tests/fixtures/nova.py b/nova/tests/fixtures/nova.py
index f8f4cc89f9..5bca8f115a 100644
--- a/nova/tests/fixtures/nova.py
+++ b/nova/tests/fixtures/nova.py
@@ -833,6 +833,16 @@ class WarningsFixture(fixtures.Fixture):
 
         self.addCleanup(warnings.resetwarnings)
 
+        # Enable general SQLAlchemy warnings also to ensure we're not doing
+        # silly stuff. It's possible that we'll need to filter things out here
+        # with future SQLAlchemy versions, but that's a good thing
+
+        warnings.filterwarnings(
+            'error',
+            module='nova',
+            category=sqla_exc.SAWarning,
+        )
+
 
 class ConfPatcher(fixtures.Fixture):
     """Fixture to patch and restore global CONF.