View is not flattened when inside an IN sub-query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

View is not flattened when inside an IN sub-query

E.Pasma
Hello, below are two equivalent delete statements. The difference is  
that the second version uses views, actually sub-queries, on the base  
tables. These are simple one to one views that could be flattened out,  
as in
     http://www.sqlite.org/optoverview.html#flattening
The second query plan has a full scan on t2 instead of direct access  
via the existing index sqlite_autoindex_t2_1. The view is apparently  
not flattened. Is it possible changing this? Thanks, E. Pasma


.version
SQLite 3.21.0 2017-10-02 02:52:54  
c9104b59c7ed360291f7f6fc8caae938e9840c77620d598e4096f78183bf807a
create table t1 (a, b, primary key(a,b));
create table t2 (b, c, primary key(b,c));
create table t3 (a, c, primary key(a,c));

.eqp on
delete from t3 where (a,c) in (
     select  a,c
     from    t1
     join    t2 using(b)
         );
--EQP-- 0,0,0,SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (a=?  
AND c=?)
--EQP-- 0,0,0,EXECUTE LIST SUBQUERY 0
--EQP-- 0,0,0,SCAN TABLE t1
--EQP-- 0,1,1,SEARCH TABLE t2 USING COVERING INDEX  
sqlite_autoindex_t2_1 (b=?)
"issue2.txt" 35L, 1393C                                        
1,1           Top

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: View is not flattened when inside an IN sub-query

E.Pasma
Thanks very much for finding this worth a change. I found that in the  
SQLite3 timeline and I tested the change. As written in an other topic:

.. Just sit tight and again wait and see if Dr Hipp agrees the  
behavior should change or not.

It is comforting that this is even true for an unanswered and  
undiscussed message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: View is not flattened when inside an IN sub-query

Richard Hipp-3
On 11/18/17, E.Pasma <[hidden email]> wrote:
> I found that in the
> SQLite3 timeline

Thanks for watching the timeline!

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users