Hi list,

I have the following schema:
   CREATE TABLE a(id int primary key);
   CREATE TABLE b(id int primary key);

I want to find information about a particular id, and my query boils
down to something like
   select * from a left natural join (select * from b) where id = 1;
(in the real code, the inner query has a where-clause).

I would expect SQLite to just look up 1 in the indexes for a.id and
b.id. Unfortunately, it does a full table scan:
   sqlite> explain query plan
      ...> select * from a left natural join (select * from b) where id = 1;
   1|0|0|SCAN TABLE b (~1000000 rows)
   0|0|0|SEARCH TABLE a USING COVERING INDEX sqlite_autoindex_a_1 (id=?) (~1 rows)
   0|1|1|SCAN SUBQUERY 1 (~100000 rows)

However, if I add "where id = 1" to the inner query I get the plan I
want:
   sqlite> explain query plan
      ...> select * from a left natural join (select * from b where id = 1)
      ...> where id = 1;
   1|0|0|SEARCH TABLE b USING COVERING INDEX sqlite_autoindex_b_1 (id=?) (~1 rows)
   0|0|0|SEARCH TABLE a USING COVERING INDEX sqlite_autoindex_a_1 (id=?) (~1 rows)
   0|1|1|SCAN SUBQUERY 1 (~1 rows)

I also get a nice plan if I use an inner join instead of an outer
join, or if I replace "select * from b" with just "b" (but, as I
mentioned above, I can't do that in reality).

This happens in SQLite 3.7.9, as well as the latest version from
Fossil. Is it a bug, or am I just expecting too much from the query
optimiser?

Nick

Try not to use sub-selects when you can use a JOIN instead.  Especially don't use them in combination.  If you express this as just a JOIN you'll find that the optimizer works as expected.  The reason it can't work here is because it can work across JOINs but not SELECTs.

Simon.

Natural joins are generally considered to be evil.  Too many columns in common can be bad.

If you just spell it out it works as expected

sqlite> explain query plan select * from a left join b where a.id=1 and b.id=a.id;
0|0|0|SEARCH TABLE a USING COVERING INDEX sqlite_autoindex_a_1 (id=?) (~1 rows)
0|1|1|SEARCH TABLE b USING COVERING INDEX sqlite_autoindex_b_1 (id=?) (~1 rows)

You can expand the where clause of course to do what you want.

Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems

Make it

select * from a left join b on b.id=a.id where a.id=1;

The join condition must be in the ON clause, otherwise the left join behaves like a plain vanilla inner join.
--
Igor Tandetnik

This does the trick, of course. The problem is that I am really joining
with a view, i.e.
   select * from a left natural join some_view where id = 1;
so there is no way to avoid a subquery if I want to use the view.

I suppose I will just give up on using a view here, then, and write my
query from scratch instead. Thanks anyway!

Nick

"Black, Michael (IS)" <Michael.Black2*******>
writes:

Thanks, but this is a red herring: it makes no difference whether you
use natural join or an explicit join here (and I would've been very
surprised if it had, because they are exactly the same
operation). Rather, the difference between my query and your query is
that I have a subquery (select * from b) and you don't.

Nick

I understand that SQLite handles VIEWs as if you had defined and saved a SELECT statement.  So if you JOIN with a VIEW, does SQLite handle it as as JOIN or a sub-SELECT ?

Simon.

It's just the same as if you JOIN with a sub-SELECT (the query for the
view), as far as I can tell. So if you LEFT JOIN with a VIEW you always
get this problem.

Nick