Python sqlite3 will not do the connection, but only sqlite3 will

Question

Using the standard sqlite3 library in python 2.6.4, the following query works fine on the 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 executed it through 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 table (calculated) on the left side of the connection has a corresponding column, because if I check it myself, 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 scheme:

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 puzzled by what I am doing wrong. I tried to exit / restart both python and sqlite command lines and can't see what I'm doing wrong. It can be very obvious.

Answer 1

( , python), , :

SELECT legid, version, segmentid, html, node_t, start, number, title 
    from ((segments inner join position using (segmentid))  
    left outer join titles using (legid, segmentid)) as LT 
    left outer join numbers using (start, legid, version);

, , sqlite , "", - . - , , , , .

Answer 2

:

CREATE TABLE 'segments' 

, . 'segments' segments:

$ sqlite3 junk.sqlite
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
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);
sqlite> 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);
Error: ambiguous column name: segmentid

Answer 3

SQLite 3.6.22 - , " "...

sqlite> 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);
Error: ambiguous column name: segmentid

sqlite> select segments.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);
Error: ambiguous column name: start

sqlite> select segments.segmentid, node_t, numbers.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);
sqlite> 

SQLite 3.6.23.1