Exactly. And I'd pretty much like SQLite to figure that out for me ;-)
-Kirill On 27.11.2015 03:19, Keith Medcalf wrote: > Would it not be more efficient to say: > > select 1 from t1 limit 1; > > ? > >> -----Original Message----- >> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >> bounces at mailinglists.sqlite.org] On Behalf Of Kirill M?ller >> Sent: Thursday, 26 November, 2015 15:03 >> To: SQLite mailing list >> Subject: Re: [sqlite] Query flattening for left joins involving subqueries >> on the right-hand side >> >> On 26.11.2015 21:12, Clemens Ladisch wrote: >>> Kirill M?ller wrote: >>>> On 25.11.2015 16:32, Clemens Ladisch wrote: >>>>> Kirill M?ller wrote: >>>>>> For a left join with a subquery on the right-hand side, that subquery >>>>>> doesn't seem to be flattened. >>>>> This is rule 3 of <http://www.sqlite.org/optoverview.html#flattening>. >>>> I wonder if this rule might be relaxed a bit. >>> Only if you relax your requirement that the results must be correct. >>> >>> >>> In the general case, a left outer join can be rewritten like this: >>> >>> SELECT ... FROM A JOIN B ON ... >>> UNION ALL >>> SELECT ... FROM A WHERE NOT EXISTS (look up in B) >>> >>> This query would be more likely to be flattenable, but also be slower. >>> >> Thanks. Let's not focus on terminology -- I thought "flattening" was the >> right word to use, but it probably isn't. Of course I'm looking for >> correct results. >> >> Originally, I attached a script but it seems that it's been stripped. >> I've pasted it below. I see no reason why the following two queries (1 >> and 3 in the script) can't be executed with the same plans: >> >> SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN t2 USING (a) LIMIT 1) >> SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) zzz2 >> USING (a) LIMIT 1) >> >> This is for two tables t1 and t2 with a single column "a". The script >> creates them and populates them with 200000 rows each. >> >> >> -Kirill >> >> >> #!/bin/bash >> >> db=test.sqlite >> >> #if false; then >> rm -f $db >> >> n=200000 >> >> sqlite3 $db "CREATE TABLE t1 (a int primary key)" >> seq 1 $n | sqlite3 $db ".import /dev/stdin t1" >> >> sqlite3 $db "CREATE TABLE t2 (a int primary key)" >> seq 1 $n | sqlite3 $db ".import /dev/stdin t2" >> #fi >> >> q() { >> sqlite3 $db "EXPLAIN QUERY PLAN $1" >> time sqlite3 $db "$1" >> } >> >> q "SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN t2 USING (a) LIMIT 1)" >> q "SELECT count(*) FROM (SELECT * FROM (SELECT * FROM t1) zzz1 LEFT JOIN >> t2 USING (a) LIMIT 1)" >> q "SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) >> zzz2 USING (a) LIMIT 1)" >> q "SELECT count(*) FROM (SELECT * FROM t1 INNER JOIN (SELECT * FROM t2) >> zzz2 USING (a) LIMIT 1)" >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
