"no such column": fetchCount(_:) discards annotated(with:) columns used in filters

[JaSpa]

What did you do?

Build a query using the query interface

1. start from some simple Table.all()
2. annotate with an additional column
3. apply a filter expression using that additional column
4. use fetchCount(_:) on the resulting query

What did you expect to happen?

A result reflecting the number of rows in the table matching the filter expression.

What happened instead?

fetchCount(_:) throws an error: "no such column: …" mentioning the column with which the table was annotated in step 2. above. The statement included in the error shows that the annotated column has "disappeared" in favour of a single count(*) but the filter clause still refers to that column.

Environment

GRDB flavor(s): (GRDB, SQLCipher, Custom SQLite build?)
GRDB version: 6.10.0
Installation method: package dependency in an Xcode project
Xcode version: 14.3
Swift version: 5.8
Platform(s) running GRDB: macOS
macOS version running Xcode: 13.1

Demo Project

FetchCountDiscardedColumns.zip

import GRDB

try DatabaseQueue().writeWithoutTransaction { db in
    struct Value: Identifiable, Codable, Equatable, TableRecord, FetchableRecord, PersistableRecord {
        var id: Int
        var number: Int
    }

    try db.create(table: "value") {
        $0.primaryKey("id", .integer)
        $0.column("number", .text).notNull()
    }

    try Value(id: 1, number: 10).insert(db)
    try Value(id: 2, number: -20).insert(db)

    let withSquaredGT0 = Value.all()
        .annotated(with: (Column("number") + Column("number")).forKey("doubled"))
        .filter(Column("doubled") > 0)

    let all = try withSquaredGT0.fetchAll(db)
    print("fetchAll = \(all.map(\.id))")
    print("fetchAll.count = \(all.count)")

    // THIS LINE CRASHES
    let count = try withSquaredGT0.fetchCount(db)
    print("fetchCount = \(count)")
}

SQLite error 1: no such column: doubled - while executing SELECT COUNT(*) FROM "value" WHERE "doubled" > ?

[groue]

Hello @JaSpa,

Thank you, you have a found a bug :-)

Until the fix ships, please use this workaround:

let countRequest: SQLRequest<Int> = "SELECT COUNT(*) FROM (\(withSquaredGT0))"
let count = try countRequest.fetchOne(db)!

[groue]

The fix has shipped in v6.10.1. Thanks again for the report, @JaSpa 👍

[JaSpa]

Incredible, thank you for the quick fix!