[Sqlalchemy-tickets] Issue #3064: Materialized path join: additional join criteria results in indeterministic SQL (zzzeek/sqlalchemy)

[Jack Z. <iss...@bi...>]

New issue 3064: Materialized path join: additional join criteria results in indeterministic SQL
https://bitbucket.org/zzzeek/sqlalchemy/issue/3064/materialized-path-join-additional-join

Jack Zhou:

Consider the following configuration:

```
#!python
class Node(Base):
    __tablename__ = "node"
    id = Column(Integer, primary_key=True)
    path = Column(String(500), nullable=False)
    depth = Column(Integer, nullable=False)
    children = relationship("Node", viewonly=True,
                            primaryjoin=and_(
                                remote(foreign(path)).like(path.concat(".%")),
                                remote(depth) == depth + 1))
```

And the query:

```
#!python
db.add(Node(path="foo", depth=0))
db.add(Node(path="foo.baz", depth=1))
db.flush()
db.expunge_all()
print(db.query(Node).first().children)
db.expunge_all()
print(db.query(Node).options(subqueryload(Node.children)).first().children)
```

**The output of this is not deterministic.** Depending on external factors (such as whether an unused import is present), the second print statement may or may not be an empty list. I've tracked it down to incorrectly rendered SQL. On "correct" runs, the rendered SQL looks like this:

```
#!sql
SELECT node.id AS node_id,
       node.path AS node_path,
       node.depth AS node_depth,
       anon_1.node_path AS anon_1_node_path,
       anon_1.node_depth AS anon_1_node_depth
FROM
  (SELECT DISTINCT node.path AS node_path,
                   node.depth AS node_depth
   FROM node LIMIT 1) AS anon_1
JOIN node ON node.path LIKE (anon_1.node_path || '.%')
AND node.depth = anon_1.node_depth + 1
ORDER BY anon_1.node_path,
         anon_1.node_depth;
```

On incorrect runs, the rendered SQL looks like this:

```
#!sql
SELECT node.id AS node_id,
       node.path AS node_path,
       node.depth AS node_depth,
       anon_1.node_depth AS anon_1_node_depth,
       anon_1.node_path AS anon_1_node_path
FROM
  (SELECT DISTINCT node.depth AS node_depth,
                   node.path AS node_path
   FROM node LIMIT 1) AS anon_1
JOIN node ON node.path LIKE (anon_1.node_path || '.%')
AND node.depth = anon_1.node_depth + 1
ORDER BY anon_1.node_depth,
         anon_1.node_path;
```

The problem lies in `anon_1`. The correct subquery for `anon_1` should be `SELECT DISTINCT node.depth AS node_depth FROM node LIMIT 1`. I may be wrong, but I've inferred from this that SQLAlchemy is incorrectly considering `depth` to be a foreign key when it is not marked as such.