Re: [sqlite] Conditional expressions and evaluation
D. Richard Hipp wrote: > On Feb 23, 2009, at 1:39 PM, John Elrick wrote: > > >> A clarification question...given the query: >> >> create table x (x_id integer, f varchar); >> create table y (y_id integer, x_id integer, f varchar); >> >> insert into x values(1, 'wibble'); >> insert into y values(1, 1, 'foo'); >> insert into y values(1, 1, 'bar'); >> >> select y.y_id, case when y.f = 'foo' then 'very good' else (select x.f >> from x where x.x_id = y.x_id) end from y >> >> The documentation states that: >> >> "If the SELECT expression does contain variables from the outer query, >> then the SELECT is reevaluated every time it is needed." >> >> Am I correct in assuming that if (using the above example) y.f = 'foo' >> that the sub-select is considered "not needed" and therefore is not >> executed? >> >> > > I think that is what it does. It is clearly what it ought to do. You > can double check by doing an EXPLAIN and examining the generated code > to make sure the subquery is skipped completely when y.f=='foo'. > EXPLAIN indicates that your assumption is correct. Thanks for clarifying that for me. It is especially reassuring to have indication that the lazy evaluation is intentional and therefore can be relied upon in the future. John Elrick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conditional expressions and evaluation
On Feb 23, 2009, at 1:39 PM, John Elrick wrote: > A clarification question...given the query: > > create table x (x_id integer, f varchar); > create table y (y_id integer, x_id integer, f varchar); > > insert into x values(1, 'wibble'); > insert into y values(1, 1, 'foo'); > insert into y values(1, 1, 'bar'); > > select y.y_id, case when y.f = 'foo' then 'very good' else (select x.f > from x where x.x_id = y.x_id) end from y > > The documentation states that: > > "If the SELECT expression does contain variables from the outer query, > then the SELECT is reevaluated every time it is needed." > > Am I correct in assuming that if (using the above example) y.f = 'foo' > that the sub-select is considered "not needed" and therefore is not > executed? > I think that is what it does. It is clearly what it ought to do. You can double check by doing an EXPLAIN and examining the generated code to make sure the subquery is skipped completely when y.f=='foo'. > > John Elrick > Fenestra Technologies > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Conditional expressions and evaluation
A clarification question...given the query: create table x (x_id integer, f varchar); create table y (y_id integer, x_id integer, f varchar); insert into x values(1, 'wibble'); insert into y values(1, 1, 'foo'); insert into y values(1, 1, 'bar'); select y.y_id, case when y.f = 'foo' then 'very good' else (select x.f from x where x.x_id = y.x_id) end from y The documentation states that: "If the SELECT expression does contain variables from the outer query, then the SELECT is reevaluated every time it is needed." Am I correct in assuming that if (using the above example) y.f = 'foo' that the sub-select is considered "not needed" and therefore is not executed? John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
