[sqlite] use of index in ORDER BY clause

[Jens Miltner]

I have a schema similar to this:

CREATE TABLE foo (id integer primary key, name text);
CREATE TABLE bar (id integer primary key, foo_id integer, something  
text);

CREATE INDEX bar_idx on bar(foo_id, something);


When I run a query like

SELECT
        foo.id AS foo_id,
        bar.id AS bar_id
FROM
        bar
LEFT JOIN
        foo ON foo.id=bar.foo_id
ORDER BY
        bar.something
;

sqlite will only use the primary key index of foo
(as the output of 'explain query plan' shows:

0|0|TABLE bar
1|1|TABLE foo USING PRIMARY KEY

)


If I try to disable the foo primary key index by prefixing foo.id  
with a '+' sign, no index will be used.

I'd have expected the bar_idx index to be used for the ORDER BY  
clause? Or is this some unreasonable assumption?
Is there a way to enforce this?

Thanks,
</jum>