Re: [sqlite] Equiv stmts, different explain plans

[kk]

On 05/03/2019 01:33, Richard Hipp wrote:

On 3/4/19, Kyle  wrote:

On another DB I came across 2 stmts, that I think are equivalent, but
generated different explain plans. I request a second opinion - are
these 2 stmts equivalent? If so, why do they generate different explain
plans even on sqlite?


The two SELECT statements below may well compute the same output
(unless I'm missing something) but they are not the same.  The WHERE
clause in the subquery is different. So why do you expect them to
generate the same query plan?


create table t1(c,d);
create table t2(c,d);
explain select * from t1
where c=1 and d in (select d from t2 where c=1);
explain select * from t1
where c=1 and d in (select d from t2 where t2.c=t1.c);



DRH, many thanks for your reply, I was expecting same output because I 
believe stmts to be equivalent, so was not sure why query plan was 
different. I see the explain plans are very similar.

But I believe original stmts mentioned are still equivalent?
Do you agree? And in SQLite what is best way to write such stmt (or in 
other terms, what is difference)?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs

[kk]

On 05/01/2017 23:35, Richard Hipp wrote:

On 1/5/17, Kyle  wrote:

Dear all,
Under version 3.16.1, SQLite3Close is returning SQLITE_BUSY when I open
a database, execute some PRAGMAS, (run some stmts) then attempt the
close. This did not occur under version 3.15.2, nor does it occur under
the new version if I omit the PRAGMAs.


Probably you are not talking to SQLite directly but are using some
wrapper software.  What is your wrapper software?


All,
Many thanks for all the replies. It seems that I had now need to run 
SQLite3Finalize against the PRAGMA stmts, and including this now 
corrects the issue.
Whilst the behaviour of PRAGMAs may change for release to release, IMO, 
it would be useful if this was documented in the release notes, since in 
this instance we see a behaviour change after upgrade.


BTW, I am using running the most excellent SQLiteForExcel wrapper to 
access SQLite from Excel.


Many thanks again,
K


Furthermore the PRAGMAs now seem to return a resultset whereas
previously they did not? Is this expected (or documented)?
Please advise,
Thanks,
K
--
PRAGMA encoding="UTF-8"
PRAGMA cache_size="-2" (hopefully a reasonable attempt to tell
SQLite that memory is more plentiful than default would suggest).

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users






___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users