python sqlite3 不会执行连接，但单独的 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 库执行它，我会得到一个错误:

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

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

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

一个解决方案(针对我使用 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 将最外层连接左侧的名称收集在一起，其中之一是“开始”，然后为最外层连接提供一些操作依据。这对我有用 - 升级可能会引入更多问题而不是消除它们，但我会在它到达时跨过那座桥。