search

potassco / clorm

🗃️ A Python ORM-like interface for the Clingo Answer Set Programming (ASP) reasoner
https://clorm.readthedocs.io
MIT License
52 stars 5 forks source link

Non-deterministic behaviour when generating the query plan #86

Closed daveraja closed 2 years ago

daveraja commented 2 years ago

If a query join() clause joins the same two tables/predicates using different fields then internally only one of these joins will be used as the actual join and the other will be moved to the where() clause. However, which one is which is currently non-deterministic. I think this is because of the python hash function being non-deterministic.

Unfortunately, this is bad because the choice of join fields can have a dramatically affect on the performance of the query. This means that you can get some dramatically different behaviour when you run the same program multiple times.

So, need to make the query plan generation deterministic. It is better to have consistent behaviour, because, even when it produces worse performance, the user can at least identify that the query needs to be restructured.

Note: one option would be to throw an error with instructions to explicitly move one of the joins into the where() clause. I need to look at how SQL handles with when you specify with INNER JOIN ... ON .... If SQL doesn't allow it then I'll do the same.

daveraja commented 2 years ago

Note: SQLite allows you to specify multiple join conditions between the same two tables with an INNER JOIN ... ON .... It therefore seems reasonable to allow a clorm join() clause to have multiple joins between the same two predicates. So, nothing else to do beyond #115.