Using the sqlite3 standard library in python 2.6.4, the following query works fine on sqlite3 command line:
select segmentid, node_t, start, number,title from
((segments inner join position using (segmentid))
left outer join titles using (legid, segmentid))
left outer join numbers using (start, legid, version);
But If I execute it via the sqlite3 library in python I get an error:
>>> conn=sqlite3.connect('data/test.db')
>>> conn.execute('''select segmentid, node_t, start, number,title from
((segments inner join position using (segmentid)) left outer join titles using
(legid, segmentid)) left outer join numbers using (start, legid, version)''')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
sqlite3.OperationalError: cannot join using column start - column not present
in both tables
The (computed) table on the left hand side of the join appears to have the relevant column because if I check it by itself I get:
>>> conn.execute('''select * from ((segments inner join position using
(segmentid)) left outer join titles using
(legid, segmentid)) limit 20''').description
(('segmentid', None, None, None, None, None, None), ('html', None, None, None,
None, None, None), ('node_t', None, None, None, None, None, None), ('legid',
None, None, None, None, None, None), ('version', None, None, None, None, None,
None), ('start', None, None, None, None, None, None), ('title', None, None,
None, None, None, None))
My schema is:
CREATE TABLE leg (legid integer primary key, t char(16), year char(16),
no char(16));
CREATE TABLE numbers (
number char(16), legid integer, version integer, start integer,
end integer, prev integer, prev_number char(16), next integer,
next_number char(16), primary key (number, legid, version));
CREATE TABLE position (
segmentid integer, legid integer, version integer, start integer,
primary key (segmentid, legid, version));
CREATE TABLE 'segments'
(segmentid integer primary key, html text, node_t integer);
CREATE TABLE titles (legid integer, segmentid integer, title text,
primary key (legid, segmentid));
CREATE TABLE versions
(legid integer, version integer, primary key (legid, version));
CREATE INDEX idx_numbers_start on numbers (legid, version, start);
I am baffled as to what I am doing wrong. I have tried quitting/restarting both the python and sqlite command lines and can't see what I'm doing wrong. It may be completely obvious.
