SQLite Forum

fts5 rowid peculiarity in UPDATE FROM
Login
Great, the citizen is now less special again )

I checked with my data, everything works as expected, including UPDATE FROM and WITHOUT ROWID tables.

There's one thing I noticed though. Sometimes one can introduce his or here own rowid (plenty of ways to do this, cte, forced rowid in "without rowid" tables, etc). The specifics here is that to be ambiguous in two senses, the rowids should be by the same kind. So joining two tables with "user rowid" gives "ambiguous column name: rowid" error and joining two tables with "sqlite rowid" gives "no such column: rowid", but mixing them (one user, one sqlite) gives no error. I don't know whether this mixing was possible before this fix, but if not then this might be a reason for hiding rowids in joins. 

The example of this when sqlite is ok with the mixed ambiguity (testtable is a general rowid table and according to my observation the rowid in ON clause is interpreted as coming from ctesource)

<pre>
with ctesource(rowid, id) as
  (
     select 12324, 1
  )
select * from testtable, ctesource on rowid=ctesource.id
</pre>

I'm not sure this is a big deal, just to take an additional look here.