sqlite - SQLite在使用子查询时说 “no such column: rowid”

Question

这是一个运行良好的查询:

SELECT rowid as msg_rowid, a, b, c FROM messages m1

这是另一个也很好的方法:

SELECT rowid as match_rowid FROM messages m2 WHERE x LIKE '%abc%'

但是，如果我按如下所述将它们放在一起，SQLite会提示:

SELECT rowid as msg_rowid, a, b, c FROM messages m1
JOIN
    (SELECT rowid as match_rowid FROM messages m2 WHERE x LIKE '%abc%')
ON
    msg_rowid >= match_rowid - 10 AND msg_rowid <= match_rowid + 5

给出看起来像是误导性的错误消息: No such column: rowid。

我怎样才能解决这个问题？

如果我使用messages.timestamp字段而不是rowid，则查询运行正常:

SELECT timestamp as msg_ts, a, b, c FROM messages m1
JOIN
    (SELECT timestamp as match_ts FROM messages m2 WHERE x LIKE '%abc%')
ON
    msg_ts >= match_ts - 10 AND msg_ts <= match_ts + 5

这是bug，还是对 rowid的使用所设计的限制？

Answer 1

GuZzie在评论中回答。由于某些原因，SQLite要求我明确说明外部rowid:

SELECT m1.rowid as msg_rowid, a, b, c FROM messages m1
       ^^ HERE
JOIN
    (SELECT rowid as match_rowid FROM messages m2 WHERE x LIKE '%abc%')
ON
    msg_rowid >= match_rowid - 10 AND msg_rowid <= match_rowid + 5