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

你的桌子有一个有趣的名字：

CREATE TABLE 'segments' 

但我不认为这是问题所在。对于每个请求，下面是我执行的查询，其中'segments'表被重新创建为segments：

^{2}$

Answer 2

一个解决方案（我使用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收集最外层外部联接左侧的名称，其中一个名称是“start”，然后为最外层的外部联接提供一些操作。这对我很管用——也许升级会带来更多的问题，而不是消除它们，但当它到来时，我会跨越这座桥。在

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