I can confirm this behavior using 
KDE SC 4.9.3
Akonadi 1.8.1
Qt: 4.8.2
KDE: 4.9.3
Kontact: 4.9.3
on gentoo linux

Still happening with akonadi-1.10.3. Sqlite-backend specific bug perhaps?

Table CollectionPimItemRelation exists. If it does not exists on your setup, your database is probably corrupted.

If you are on SQLite, run this query to create the table, then restart Akonadi and see whether it fixes the problem.

CREATE TABLE CollectionPimItemRelation (Collection_id BIGINT NOT NULL,
                                        PimItem_id BIGINT NOT NULL,
                                        PRIMARY KEY (Collection_id, PimItem_id))

(for other backends, more queries are needed).

Relation CollectionPimItemRelation exists in my case, still query in question fails.

UPDATE PimItemTable SET atime = :0 WHERE ( CollectionPimItemRelation.Collection_id = :1 AND ( CollectionPimItemRelation.PimItem_id = PimItemTable.id ) )

When I actually run in directly on my SQLite akonadi db, it fails differently:
"Query Error: no such column: CollectionPimItemRelation.Collection_id Unable to execute statement"

Is this some MySQL shortcut? (bug reporter doesn't seem to have that problem)
I admit I haven't written SQL in a while but that SQL statement updates PimItemTable but in WHERE clause it references foreign table CollectionPimItemRelation, which would otherwise require separate lookup.
More portable query would look like:

UPDATE PimItemTable SET atime = :0 WHERE id = (SELECT PimItem_id FROM CollectionPimItemRelation WHERE Collection_id = :1 AND PimItem_id = PimItemTable.id )

Still it won't work when directly executed on database propably because of :0 and :1, which are likely stored procedure or prepared statement params (hence value weren't printed in akonadi log).

Anway.. if "parameter mismatch" is the only error here, maybe stored procedure/prepared statement parameter quoting is broken somewhere there?

Btw, it seems related code is below:

void FetchHelper::updateItemAccessTime()
{
  Transaction transaction( mConnection->storageBackend() );
  QueryBuilder qb( PimItem::tableName(), QueryBuilder::Update );
  qb.setColumnValue( PimItem::atimeColumn(), QDateTime::currentDateTime() );
  ItemQueryHelper::scopeToQuery( mScope, mConnection, qb );

  if ( !qb.exec() )
    qWarning() << "Unable to update item access time";
  else
    transaction.commit();
}

From quick look it's fine. But sorry for silly question, but how is it possible that it generates "WHERE ( CollectionPimItemRelation.Collection_id = :1 AND ( CollectionPimItemRelation.PimItem_id = PimItemTable.id ) )" ?
Thanks to "scopeToQuery"?
(if so, way to go.. no wonder nobody is brave enough to write proper no-SQL backend for akonadi cache yet if it's so difficult to follow what exactly is going on now...)

AAh, right. I completely missed that we are not joining in the CollectionPimItemRelation table, I'll look into it.

The WHERE condition indeed comes from scopeToQuery(). We don't want to support no-SQL (I don't think it makes sense for us), so using methods to generate parts of queries in SQL makes the code easier to maintain and more bullet-proof.

Git commit 6fe8b071f183d64dd934663c8cd1c5e4d587d93d by Dan Vrátil.
Committed on 27/11/2013 at 20:44.
Pushed by dvratil into branch '1.11'.

SQLite: Handle joins in UPDATE queries

Since SQLite does not directly support JOIN expression in UPDATE or UPDATE on
multiple tables at once, we have to convert the JOIN into a subquery in WHERE
condition.

The conversion is far from perfect, but it passes the unit-tests based on the
query that does this (I think there's only one such query in Akonadi that does
this, see FetchHelper::updateItemAccessTime())

This fixes updating atime on items from virtual collections when using SQLite
backend.
FIXED-IN: 1.11

A  +88   -0    server/src/search/searchcollector.cpp     [License: LGPL (v2+)]
A  +98   -0    server/src/search/searchcollector.h     [License: LGPL (v2+)]
A  +62   -0    server/src/search/searchinstance.cpp     [License: LGPL (v2+)]
A  +47   -0    server/src/search/searchinstance.h     [License: LGPL (v2+)]
A  +82   -0    server/src/search/searchresultsretriever.cpp     [License: LGPL (v2+)]
A  +57   -0    server/src/search/searchresultsretriever.h     [License: LGPL (v2+)]
M  +52   -6    server/src/storage/querybuilder.cpp
M  +7    -0    server/src/storage/querybuilder.h
M  +18   -2    server/tests/unittest/querybuildertest.cpp

http://commits.kde.org/akonadi/6fe8b071f183d64dd934663c8cd1c5e4d587d93d

Wow, so many changes. Was updating item access time actually worth it?

I committed the files in /server/src/search stuff accidentally, the real fix is the +52 in querybuilder.cpp :-)