Thank you for your answer, Keith. I had my problem "fixed" before I wrote the first mail. Also with every problem I also provided the fix that worked, for anyone that might run into the same problem. However, it's difficult to not get a little frustrated with your answer.
At https://sqlite.org/queryplanner.html I read: "The best feature of SQL (in all its implementations, not just SQLite) is that it is a declarative language, not a procedural language. When programming in SQL you tell the system what you want to compute, not how to compute it." And I completely agree with this, "how to compute it" is called relational algebra and it's what a query planner should do best. And the two queries are algebrically identical. "(X ∊ S or X:=null) AND (X is not null)" is equivalent to "X ∊ S is not null". The two queries might look different only from an imperative programming point of view. As to why the query is written that way: with the above in mind, I will contend that there can absolutely never exist a "mistaken" way to write a query, as long as the description of the predicates is correct and consistent with the schema. You should consider that quite frequently queries are the result of one or more levels of logic abstraction (ORM, DBAL, etc). In my case, modifying the query was not difficult to do, but in other cases one may have few options on rewriting the way the query structure is generated. The only way to reduce a fabricated query is through relational algebra, and that is up to the DB, not the programmer, not the abstractions in-between. In this particular case, the where is optional; depending on parameters, I want the set of data that is correctly defined as the left join of tables a and b, or I might want a subset of this join that has a particular property over the left-joined set. The query was correctly written, to rewrite it so that the query planner might know how to run it is wrong, IMHO. To sum it up: I think it's every DB's intention to optimize as best possible a query into an execution plan. None does it perfectly, but all try to, very hard. With this intention, I reported a case where the query planner COULD be improved. I think you will at least agree with me that making it better can't be wrong. Whether that happens tomorrow, in a year or never, that's up to the mercy, resources and priorities of the developers, so I am really am not interested in an argue over this. -- Sent from: http://sqlite.1065341.n5.nabble.com/ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
