[Sqlalchemy-tickets] Issue #2969: Join rewriting (?maybe?) issue (union and joined table inheritance involved) (zzzeek/sqlalchemy)

[dairiki <iss...@bi...>]

New issue 2969: Join rewriting (?maybe?) issue (union and joined table inheritance involved)
https://bitbucket.org/zzzeek/sqlalchemy/issue/2969/join-rewriting-maybe-issue-union-and

dairiki:

This is a regression in 0.9.4.
I suspect this is an issue only with sqlite.
A test script which exercises the problem is attached.

### Symptoms ###
With
```
class A(Base):
    __tablename__ = 'A'
    id = sa.Column(sa.Integer, primary_key=True)
    typ = sa.Column(sa.Integer, nullable=False)
    __mapper_args__ = {'polymorphic_on': typ}
    b_id = sa.Column(sa.ForeignKey('B.id'), nullable=False)

class A1(A):
    __tablename__ = 'A1'
    __mapper_args__ = {'polymorphic_identity': 1}
    id = sa.Column(sa.ForeignKey(A.id), primary_key=True)

class A2(A):
    __tablename__ = 'A2'
    __mapper_args__ = {'polymorphic_identity': 2}
    id = sa.Column(sa.ForeignKey(A.id), primary_key=True)

class B(Base):
    __tablename__ = 'B'
    id = sa.Column(sa.Integer, primary_key=True)
    a1s = sa.orm.relationship(A1)
    a2s = sa.orm.relationship(A2)
```

`sess.query(B).join(B.a1s).join(sess.query(B).join(B.a2s))` compiles erroneously to
```
SELECT anon_1."B_id" AS "anon_1_B_id" FROM (
  SELECT "B".id AS "B_id" 
    FROM "B"
     JOIN (
       SELECT "A".id AS "A_id",
              "A".typ AS "A_typ",
              "A".b_id AS "A_b_id",
              "A1".id AS "A1_id" 
         FROM "A" JOIN "A1" ON "A".id = "A1".id
     ) AS anon_2 ON "B".id = anon_2."A_b_id"
  UNION SELECT "B".id AS "B_id" 
    FROM "B"
     JOIN (
       SELECT anon_2."A_id" AS "A_id", 
              anon_2."A_typ" AS "A_typ",
              anon_2."A_b_id" AS "A_b_id",
              anon_2."A1_id" AS "A1_id",
              "A2".id AS "A2_id" 
         FROM (
           SELECT "A".id AS "A_id",
                  "A".typ AS "A_typ",
                  "A".b_id AS "A_b_id",
                  "A1".id AS "A1_id" 
             FROM "A" JOIN "A1" ON "A".id = "A1".id
         ) AS anon_2
          JOIN "A2" ON anon_2."A_id" = "A2".id
      ) AS anon_3 ON "B".id = anon_3."A_b_id"
) AS anon_1
```

Note that in the the second part of the union (the part corresponding to `sess.query(B).join(B.a2s)` is effectively joining `A` with both `A1` and `A2`
(which always results in zero rows, since an A is either an A1 or an A2, never both.)