Hacker News new | ask | show | jobs
by mwsherman 4797 days ago
Correct, these are semantically different. You’ve described it well. The ORM would be incorrect if it did not generate the SQL that it did.

Part of the issue is confusing object data with metadata. The id is an implementation detail of the data store – metadata. What the user is trying to do here is “talk database” and “talk model” at the same time.

Which is a perfectly good argument against an ORM. But one should commit to a metaphor, or not: http://clipperhouse.com/2012/02/29/suspension-of-disbelief/
1 comments
zzzeek 4797 days ago
If the schema is known to have a FOREIGN KEY constraint on sometable.other_id, then the SQL is as close as can be to wrong without actually returning the wrong answer. In the presence of the FK, the two queries have the same intent. But the first produces a vastly complex query plan for no reason, and this has nothing at all to do with the application/model layer.

Edit: plus! even if you want the same answer assuming that FKs are not in place and that bogus values might be present in other_id, the query is still far less efficient than it should be. You should be doing this:

    select * from sometable where not exists 
    (select 1 from othertable where othertable.id=sometable.other_id)
compare the query plans on any reasonable database and see (and yes, SQLAlchemy produces the NOT EXISTS form when the relationship is a one-to-many versus many-to-one and you ask it for objects with empty collections).
link
mwsherman 4796 days ago
“Wrong but works correctly” is a pretty novel definition of wrong.

The argument you are making is that this particular ORM needs optimizations. Which is true! The point stands that the semantics of the two expressions is different, and thus should do different things. The point also stands that the user is mixing metaphors.

If the ORM can be informed of the guarantee that the FK constraint provides, and optimize accordingly, that’s good too. But this doesn’t tell us much about ORMs, except that they can be improved.

Also, a database that has two different query plans for queries that are logically equivalent…needs improvement.

link