On Tuesday, 5 March, 2019 12:53, James K. Lowden <[email protected]> wrote:
>On Mon, 04 Mar 2019 20:20:08 -0700> "Keith Medcalf" <[email protected]> >wrote: >> In the first query the subselect that creates the list is >> independent. >> In the second query the subselect that creates the list is >> correlated. >Yes, and if it can be shown that the two queries are logically >equivalent under relational algebra, then it's theoretically possible >for the query planner to have arrived at the same plan in both cases. >That is the only test that could support/deny the assertion that they >could be rendered according to the same execution plan. >> In the first query you have requested that the subquery be executed >> to create the list for use by the IN operator. >No. The query requests no such thing. SQL makes no request or >suggestion for how to execute a query. It simply describes a result. >It's up to the implementation to determine how to produce that >result. You are, of course, correct. However for the two queries given I do not believe that any query planner currently in existence will recognize that t1.c == 1 and t2.c == 1 implies that t1.c == t2.c. However, that implication may be stated explicitly (as it is in the correlated subquery). It is also entirely possible that if the (first) query were phrased as: select * from t1 where c == ?0 and d in (select d from t2 where c == ?0 and d == t1.d) ; then it is quite possible for the query planner to take notice of the fact that t1.c == t2.c ... Similarly I would not *expect* that a query planner would consider t1.c and t2.c to be transitively equal if the query were phrased as: select * from t1 where c == ? and d in (select d from t2 where c == ? and d == t1.d) ; even if the two parameters were the same value ... As another note, you also commented that a "select distinct * from t1 join ...." is (possibly) equivalent. This is not necessarily the case because there is nothing in the schema which requires the rows of t1 (or t2 for that matter) to be distinct and thus the loop order does affect the output (without distinct). Granted, with distinct the output (set) will be the same no matter the loop nesting order, it may not be the same without distinct depending on the data in the tables. In other words, we arrive at the same point in the end. It depends on the original "problem statement" which the SQL was composed to solve. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
