[Sqlalchemy-tickets] Issue #3064: Materialized path join: additional join criteria results in indet
Brought to you by:
zzzeek
|
From: Jack Z. <iss...@bi...> - 2014-05-31 02:57:45
|
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. |