Re: [sqlite] Problems loading extensions on Windows 10

[Kyle]

As indicated by "Random Coder", the problem was that I was missing the
required dependencies.

Thank you.

On Tue, Mar 5, 2019 at 8:03 PM Thomas Kurz  wrote:

> Are both of the same architecture, either 32bit or 64bit?
>
> - Original Message -
> From: Kyle 
> To: sqlite-users@mailinglists.sqlite.org <
> sqlite-users@mailinglists.sqlite.org>
> Sent: Tuesday, March 5, 2019, 23:30:35
> Subject: [sqlite] Problems loading extensions on Windows 10
>
> I am having problems loading sqlite3 extensions on Windows 10.
>
> I have downloaded both sqlite3 and mod_spatialite.dll (the extension) and
> copied them to C:\Windows\System32.
>
> When I run
> SELECT load_extension('mod_spatialite')
> sqlite returns "Error: the specified module could not be found"
>
> I have also tried
> SELECT load_extension('C:\Windows\System32\mod_spatialite.dll')
> which returns the same error.
>
> These commands work perfectly fine in Ubuntu and the extension loads with
> no problems. What do I need to do differently on Windows?
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems loading extensions on Windows 10

[Thomas Kurz]

Are both of the same architecture, either 32bit or 64bit?

- Original Message - 
From: Kyle 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Tuesday, March 5, 2019, 23:30:35
Subject: [sqlite] Problems loading extensions on Windows 10

I am having problems loading sqlite3 extensions on Windows 10.

I have downloaded both sqlite3 and mod_spatialite.dll (the extension) and
copied them to C:\Windows\System32.

When I run
SELECT load_extension('mod_spatialite')
sqlite returns "Error: the specified module could not be found"

I have also tried
SELECT load_extension('C:\Windows\System32\mod_spatialite.dll')
which returns the same error.

These commands work perfectly fine in Ubuntu and the extension loads with
no problems. What do I need to do differently on Windows?
___
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

Re: [sqlite] Problems loading extensions on Windows 10

[Random Coder]

On Tue, Mar 5, 2019 at 2:30 PM Kyle  wrote:
> I have downloaded both sqlite3 and mod_spatialite.dll (the extension) and
> copied them to C:\Windows\System32.
>
> When I run
> SELECT load_extension('mod_spatialite')
> sqlite returns "Error: the specified module could not be found"

Helpfully, on Windows, the error message for unable to find a DLL, and
unable to find a dependency of that DLL are the same error.

The version of mod_spatialite.dll I'm familiar with has several
dependencies.  You'll need to make sure all of the dependent DLL files
are also copied to somewhere in your path.  You might try using
Dependency Walker ( http://www.dependencywalker.com/ ) to see which
DLLs a component is missing.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problems loading extensions on Windows 10

[Kyle]

I am having problems loading sqlite3 extensions on Windows 10.

I have downloaded both sqlite3 and mod_spatialite.dll (the extension) and
copied them to C:\Windows\System32.

When I run
SELECT load_extension('mod_spatialite')
sqlite returns "Error: the specified module could not be found"

I have also tried
SELECT load_extension('C:\Windows\System32\mod_spatialite.dll')
which returns the same error.

These commands work perfectly fine in Ubuntu and the extension loads with
no problems. What do I need to do differently on Windows?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Equiv stmts, different explain plans

[Keith Medcalf]

On Tuesday, 5 March, 2019 12:53, James K. Lowden  
wrote:

>On Mon, 04 Mar 2019 20:20:08 -0700> "Keith Medcalf"  
>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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segmentation fault running a query

[David Raymond]

And if it does still crash, please provide the database schema to help with 
debugging.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Tuesday, March 05, 2019 3:14 PM
To: SQLite mailing list
Subject: Re: [sqlite] Segmentation fault running a query

On 5 Mar 2019, at 8:06pm, Peter Hardman  wrote:

> If i run the following query from Python 3.7 using the sqlite3 DBAPI adapter 
> or from sqlitebrowser I get a segmentation fault.

Please find the SQLite command-line tool on your computer.  If you don't 
already have one you can download one for your platform in the section 
"Precompiled Binaries" on



Using that program, please run

PRAGMA integrity_check;

If that reports no problems (should return no results) then please run your 
SELECT query in that program just to verify that it gets the same result your 
own code does.
___
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

Re: [sqlite] Segmentation fault running a query

[Simon Slavin]

On 5 Mar 2019, at 8:06pm, Peter Hardman  wrote:

> If i run the following query from Python 3.7 using the sqlite3 DBAPI adapter 
> or from sqlitebrowser I get a segmentation fault.

Please find the SQLite command-line tool on your computer.  If you don't 
already have one you can download one for your platform in the section 
"Precompiled Binaries" on



Using that program, please run

PRAGMA integrity_check;

If that reports no problems (should return no results) then please run your 
SELECT query in that program just to verify that it gets the same result your 
own code does.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Segmentation fault running a query

[Peter Hardman]

If i run the following query from Python 3.7 using the sqlite3 DBAPI 
adapter or from sqlitebrowser I get a segmentation fault.


The query runs error free on PostgreSQL 9.6

The query fails if the 'in' list has more than two entries.

Any ideas? I could create a test database but it will take a while as it 
has personal information in which I would have to obfuscate to avoid 
falling foul of the GDPR.


Query:

select  m.member_no, p.title, p.initials, p.forename, p.surname,
m.address_1, m.address_2, m.address_3, m.post_town, m.county,
m.post_code, m.country, s.class_name, m.joined, s.renewed,
s.expires, s.pmt_method, m.non_renewal, m.share_info,
m.last_changed, p.person_id, r.region
from person p join mem_pers mp on p.person_id = mp.person_id
join member m on mp.member_no = m.member_no
join current_member_status s on m.member_no = s.member_no
left join in_region r on m.county = r.county
 join mem_dates d on s.expires > d.renewal_date where m.non_renewal = ''
and p.person_id in 
(200,270,271,355,427,484,512,598,685,724,847,922,973,1095,1189,1371,1421,1483,1516,1523,1592,1799,1866,2024,2076,2077) 


 order by m.country, m.county, p.surname, p.initials

Core dump (the interesting bit only):

[peter@system04 ppd-memrpt2]$ coredumpctl info 10267
   PID: 10267 (sqlitebrowser)
   UID: 1000 (peter)
   GID: 1000 (peter)
Signal: 11 (SEGV)
 Timestamp: Tue 2019-03-05 18:55:38 GMT (55min ago)
  Command Line: sqlitebrowser
Executable: /usr/bin/sqlitebrowser
 Control Group: /user.slice/user-1000.slice/session-1.scope
  Unit: session-1.scope
 Slice: user-1000.slice
   Session: 1
 Owner UID: 1000 (peter)
   Boot ID: f27026461d4944c8a24ca6524def7c0c
Machine ID: 47f95fceb9d84436be17de2a1639655e
  Hostname: system04
   Storage: 
/var/lib/systemd/coredump/core.sqlitebrowser.1000.f27026461d4944c8a24ca6524def7c0c.10267.1551812138>

   Message: Process 10267 (sqlitebrowser) of user 1000 dumped core.

Stack trace of thread 10267:
#0  0x7ffaf1357223 sqlite3VdbeFindCompare 
(libsqlite3.so.0)
#1  0x7ffaf12ba157 sqlite3BtreeMovetoUnpacked 
(libsqlite3.so.0)

#2  0x7ffaf134db86 sqlite3VdbeExec (libsqlite3.so.0)
#3  0x7ffaf1352920 sqlite3_step (libsqlite3.so.0)
#4  0x556bb8b1b09e _ZN10MainWindow12executeQueryEv 
(sqlitebrowser)

#5  0x556bb8c1da2f n/a (sqlitebrowser)
#6  0x7ffaf0a4187c 
_ZN11QMetaObject8activateEP7QObjectiiPPv (libQt5Core.so.5)
#7  0x7ffaf16f5633 _ZN7QAction9triggeredEb 
(libQt5Widgets.so.5)
#8  0x7ffaf16f7d1a 
_ZN7QAction8activateENS_11ActionEventE (libQt5Widgets.so.5)

#9  0x7ffaf17ea45e n/a (libQt5Widgets.so.5)
#10 0x7ffaf17ea696 
_ZN15QAbstractButton17mouseReleaseEventEP11QMouseEvent (libQt5Widgets.so.5)
#11 0x7ffaf18da04b 
_ZN11QToolButton17mouseReleaseEventEP11QMouseEvent (libQt5Widgets.so.5)
#12 0x7ffaf173cb68 _ZN7QWidget5eventEP6QEvent 
(libQt5Widgets.so.5)
#13 0x7ffaf18da0f4 _ZN11QToolButton5eventEP6QEvent 
(libQt5Widgets.so.5)
#14 0x7ffaf16fbe24 
_ZN19QApplicationPrivate13notify_helperEP7QObjectP6QEvent 
(libQt5Widgets.so.5)
#15 0x7ffaf1703929 
_ZN12QApplication6notifyEP7QObjectP6QEvent (libQt5Widgets.so.5)
#16 0x7ffaf0a16e99 
_ZN16QCoreApplication15notifyInternal2EP7QObjectP6QEvent (libQt5Core.so.5)
#17 0x7ffaf1702c08 
_ZN19QApplicationPrivate14sendMouseEventEP7QWidgetP11QMouseEventS1_S1_PS1_R8QPoi>

lines


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

Re: [sqlite] Equiv stmts, different explain plans

[James K. Lowden]

On Mon, 04 Mar 2019 20:20:08 -0700
"Keith Medcalf"  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.  

--jkl


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

Re: [sqlite] [EXTERNAL] Equiv stmts, different explain plans

[James K. Lowden]

On Tue, 5 Mar 2019 08:13:32 +
Hick Gunter  wrote:

> The second statement uses a *correlated* subquery as the RHS of an IN
> expression. The QP needs to actually run this query for every record
> of t1 that matches the condition t1.c == 1.

I'm not sure what you mean be "needs", above.  If you're describing the
way the SQLite QP works, OK.  If you're asserting that the QP or any QP
must work that way, no, that's common fallacy.  The person writing the
query may think of a correlated subquery that way; it's *logically*
true.  But the planner is free to execute the query however it
chooses.  In fact, SQLite explains in great detail when the optimizer
will "flatten" a subquery into a join.  

>  select * from t1
>where c=1 and d in (select d from t2 where c=1);
>  select * from t1
>where c=1 and d in (select d from t2 where t2.c=t1.c);

Consider:  

select distinct t1.* 
from t1 join t2
on t1.c = t2.c and t1.d = t2.d
where t1.c = 1

Every existential quantification can be recast as a join.  

--jkl

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

Re: [sqlite] Equiv stmts, different explain plans

[Keith Medcalf]

On Tuesday, 5 March, 2019 04:09, Simon Slavin  wrote:

>On 5 Mar 2019, at 2:06am, kk  wrote:

 select * from t1
where c=1 and d in (select d from t2 where c=1);
 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?

>How do you expect a SQL engine to approach the above statements ?
>Should it process the inner SELECT first, or the outer SELECT first ?

>If it processes the inner SELECT first, where does it get the value
>it needs for t1.c ?

>If it processes the outer SELECT first, what strategy does it use for
>selecting on t1.d when it doesn't yet know whether there's going to
>be no, a single, or multiple values ?

>> Do you agree? And in SQLite what is best way to write such stmt (or
>in other terms, what is difference)?
>
>Using a JOIN.
>
>SELECT t1.* FROM t1
>INNER JOIN t2 ON t2.c=1 AND t2.d = t1.d
>WHERE t1.c=1;

Technically this is invalid.  t2.c == 1 is NOT a equijoin condition and should 
NOT appear in the ON clause (though it would be valid in the case of an outer 
join).  However, since [INNER] JOIN is merely syntactic sugar for a , and the 
ON is merely syntactic sugar for a WHERE clause, this does not really matter 
much.  Moreover, the query plans will be different because in this case you are 
joining T1 against T2 using only the common column d, and then filtering the 
interim results based on t1.c and t2.c.  (The actual plan will use the c==1 
condition to constrain the outer loop, then loop through the inner table based 
on the join column d, then filter the result of that with the "other" c==1 
condition -- which table is chosen as the outer table is up to the query 
planner (and it will choose whichever one it things the constraint c==1 will 
produce the least rows)).

This is entirely different from the below query where you are joining T1 and T2 
on the common columns c and d, then filtering for a specific value of c in t1 
(which will constrain the outer loop).

The total number of candidate solutions (the number of intersects in the nested 
loops) can be quite different.

>SELECT t1.* FROM t1
>INNER JOIN t2 ON t2.c=t1.c AND t2.d = t1.d
>WHERE t1.c=1;

>The INNER JOIN (as opposed to OUTER JOIN) means that a row must exist
>in t2 for the equivalent row in t1 to be returned.  INNER is the
>default kind of JOIN.  Of the two statements, it seems that the fist
>one requires less processing.

Actually, the latter (the properly phrased equijoin) will require the least 
processing since it gives the query planner the greatest latitude to generate 
an optimal solution.  In one case (the former) you have the condition "t1.c == 
1 AND t2.c == 1".  The query planner cannot possibly derive from this the fact 
that "t1.c == t2.c".  However, in the latter case, you have the condition "t1.c 
== t2.c AND t1.c == 1" from which the query planner can determine the fact that 
"t2.c == 1".  Depending on the shape of the data and the available statistics 
this may have a great effect on the performance of the query because the query 
planner has more information and may choose a more optimal solution (that is, 
it may now choose whether t1 or t2 is the outer loop, and the descent into the 
inner table uses both columns).

This is, of course, also fully equivalent to the properly phrased JOIN but it 
does constrain the solution (it is equivalent to specifying "t1 CROSS JOIN t2" 
in the above properly phrased equijoin in that it constrains the order of 
traversal of the tables):

SELECT *
  FROM t1
 WHERE c == 1
   AND EXISTS (SELECT * FROM t2 WHERE c == t1.c AND d == t1.d)
;

since no actual data from t2 needs to be returned.  Which "phrasing" of the 
myriad of perhaps different queries producing (quite possibly) the same (or 
perhaps different) results probably depends on how one "translates" the 
original problem statement from English into SQL (and the adequacy of that 
problem statement) together with the maintainability of the application and the 
actual schema including the indexes ...

>Internally, SQLite does comparison and conversion when faced with
>different ways of phrasing your query.  But that's not your problem.
>Phrase what you want in as specific terms as possible, and let SQLite
>pick its preferred way of solving the problem.

Of course, which one chooses depends entirely on the original problem 
statement.  If one "assumes" that the t1.c=1 and t2.c=1 in the original 
statement:

select * from t1
   where c=1 and d in (select d from t2 where c=1);

is merely a lazy programmer expressing that t1.c == t2.c without actually 
saying so, then the properly phrased equijoin is the most efficient because it 
gives the query planner the most latitude to generate an optimal 

Re: [sqlite] Equiv stmts, different explain plans

[Simon Slavin]

On 5 Mar 2019, at 2:06am, kk  wrote:

>>> select * from t1
>>>where c=1 and d in (select d from t2 where c=1);
>>> 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?

How do you expect a SQL engine to approach the above statements ?  Should it 
process the inner SELECT first, or the outer SELECT first ?

If it processes the inner SELECT first, where does it get the value it needs 
for t1.c ?

If it processes the outer SELECT first, what strategy does it use for selecting 
on t1.d when it doesn't yet know whether there's going to be no, a single, or 
multiple values ?

> Do you agree? And in SQLite what is best way to write such stmt (or in other 
> terms, what is difference)?

Using a JOIN.

SELECT t1.* FROM t1
INNER JOIN t2 ON t2.c=1 AND t2.d = t1.d
WHERE t1.c=1;
SELECT t1.* FROM t1
INNER JOIN t2 ON t2.c=t1.c AND t2.d = t1.d
WHERE t1.c=1;

The INNER JOIN (as opposed to OUTER JOIN) means that a row must exist in t2 for 
the equivalent row in t1 to be returned.  INNER is the default kind of JOIN.  
Of the two statements, it seems that the fist one requires less processing.

Internally, SQLite does comparison and conversion when faced with different 
ways of phrasing your query.  But that's not your problem.  Phrase what you 
want in as specific terms as possible, and let SQLite pick its preferred way of 
solving the problem.

Simon.

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

Re: [sqlite] Handling ROLLBACK

[Simon Slavin]

On 5 Mar 2019, at 3:22am, Rowan Worth  wrote:

> Wait what? If I've understood correctly you're describing a situation where 
> statements outlive their transaction context?

The documentation on transactions covers this possibility.  This includes 
continuing to do _step() for one statement while another executes END or 
ROLLBACK.  Your guess about how it is treated is correct.

Nevertheless I consider this behaviour extremely high-risk and would reject any 
production program which depended on it as the product of a poor programmer.

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

Re: [sqlite] Equiv stmts, different explain plans

[R Smith]

On 2019/03/05 4:06 AM, kk wrote:

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



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//...


They are very much not equivalent. They happen to produce the same 
output with this very specific crafted schema and queries, but that does 
not say that they mean the same thing, in fact they mean very different 
things in execution. I think Keith explained it well enough technically, 
but in case it is not 100% clear yet, let me add to it this example:


Say we have a group of random people, and I asked you to separate out 
all the people aged above 25, and then from that group separate out all 
the women, and then from that group separate all who have 
husbands/partners in the original group.


The next day, with the same group of beings, I might ask to first 
separate out all partnered pairs from the group, then from that group 
separate out all females and from that remainder, get everything that's 
been on Earth more than 25 years.


You might rightfully protest that, in the end, we would have the exact 
same people we've already picked out yesterday, and it would be true - 
however, the intermediate groups along the execution plan look very 
different, and the method you've used to achieve this second result 
follows a very different set of instructions, and, if the origin group 
allowed non-humans in, the second query may actually yield different 
results.


They are not equivalent in function just because they happen to yield 
the same end-results for the specific schema and content.



Hope that is a useful clarification!

Ryan


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

Re: [sqlite] [EXTERNAL] Equiv stmts, different explain plans

[Hick Gunter]

Both statements generate the same result set, but they are neither equivalent 
nor equally fast.

The first statement uses a *constant* subquery as the RHS of an IN expression. 
The QP is free to materialize this query (i.e. run it once and keep the results 
in an "ephemeral" table with an index for fast lookup).

The second statement uses a *correlated* subquery as the RHS of an IN 
expression. The QP needs to actually run this query for every record of t1 that 
matches the condition t1.c == 1.

Imagine what happens if the constraint t1.c == 1 is changed to t1.c == 2.

The first statement will still be checking against the same constant subquery 
result set.

The second statement will be checking against a new and possibly quite 
different correlated subquery result set.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Kyle
Gesendet: Dienstag, 05. März 2019 02:05
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Equiv stmts, different explain plans

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?
TIA
--
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); 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users