pythonsqlite3不会执行连接，但是sqlite3单独执行

Question

使用python 2.6.4中的sqlite3标准库，以下查询可以在sqlite3命令行上正常工作：

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

但是如果我通过python中的sqlite3库执行它，我会得到一个错误：

^{pr2}$

连接左侧的（计算的）表似乎有相关列，因为如果我自己检查它，我会得到：

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

我的模式是：

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

我不知道我做错了什么。我尝试过退出/重新启动python和sqlite命令行，但看不出我做错了什么。这可能是非常明显的。在

Answer 1

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版相同