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)"
