[Question] How to combine two filters including a collated one?

[eytanschulman]

Question

How can I combined two filters, one being a collated one? Or combine two collated ones?

Description

I have a query which contains two filters. The filter searches based off of a TYPE column, and a NAME column. In my database, many of the characters in NAME have polish characters, therefore I have written a custom collation. I am trying to filter based off of TYPE, and a collated NAME (with my custom collation)

I tried taking my existing query and adding .collate to the nameFilter:

let rawTypes = types.map { $0.rawValue.uppercased() }
let cityFilter = rawTypes.contains(Column(Location.Column.type.rawValue))
let nameFilter = Column(Location.Column.name.rawValue).like("%\(searchText)%").collating(AccentInsensitiveCollation)
let request = Location.filter(cityFilter && nameFilter)
return try request.fetchAll(db)

When I did this, I got the following error:

Binary operator '&&' cannot be applied to operands of type 'SQLExpression' and 'SQLCollatedExpression'

When I saw this, I was like "alright, no big deal, lets make the SQLExpression (the none-collated one) into a SQLCollatedExpression, surely I can compare those?

So I changed:

let cityFilter = rawTypes.contains(Column(Location.Column.type.rawValue))

To:

let cityFilter = rawTypes.contains(Column(Location.Column.type.rawValue)).collating(.nocase)

And then I got:

Binary operator '&&' cannot be applied to two 'SQLCollatedExpression' operands

Disclamer

This could totally be an SQLite thing I don't understand (not being able to compare two collated expressions), I have done research into this but wanted to post here knowing some experts could help 😄

Environment

**GRDB flavor(s): GRDB
**GRDB version: 5.0.4
**Installation method: SPM
**Xcode version: 13.0
**Swift version: Swift 5.0
**Platform(s) running GRDB: iOS
**macOS version running Xcode: 12.0.1 (21A559)

[groue]

Hello @eytanschulman,

Please first upgrade your copy of GRDB. This will fix your compiler errors. But this will not fix your query!

Mind the behavior of the SQLite IN operator and collations. See how, below, only the second query returns the expected result:

$ # The SQLite command-line tool is a great playground!
$ sqlite3
SQLite version 3.32.3 2020-06-18 14:16:19
sqlite> SELECT 'a' IN ('A') COLLATE NOCASE;
0
sqlite> SELECT ('a' COLLATE NOCASE) IN ('A');
1

In Swift, this translates to:

// (name COLLATE NOCASE) IN ('foo', 'bar')
let collatedColumn = Column("name").collating(.nocase)
let filter = ["foo", "bar"].contains(collatedColumn)

Finally, the SQLite LIKE operator ignores collations. That's because it does not involve string comparison. Instead, it is implemented as a function: like(X, Y), independently of collations. The behavior of this function is described here.

I'm afraid you will have to find a replacement for your LIKE filter...

[eytanschulman]

@groue Thanks for the detailed response! Apologizes on not checking my GRDB version beforehand, you live and you learn 😄

Will look into other types of filters like IN that support collation to replace my LIKE filter...

[groue]

Flagging "needs revisiting", because I was initially surprised collection.contains(column).collating(.nocase) would not work. GRDB already performs expression rewriting for BETWEEN which "suffers" from the same gotcha. I think we should perform expression rewriting for IN as well, if this does not create any problem.

[groue]

Flagging "needs revisiting", because I was initially surprised collection.contains(column).collating(.nocase) would not work. GRDB already performs expression rewriting for BETWEEN which "suffers" from the same gotcha. I think we should perform expression rewriting for IN as well, if this does not create any problem.

Next version of GRDB will generate the same SQL when contains has a collated argument, or if it is the contains expression that is collated:

// name COLLATE NOCASE IN ('foo', 'bar')
["foo", "bar"].contains(Column("name")).collating(.nocase)
["foo", "bar"].contains(Column("name").collating(.nocase))

// name BETWEEN 'foo' AND 'bar' COLLATE NOCASE
("foo"..."bar").contains(Column("name")).collating(.nocase)
("foo"..."bar").contains(Column("name").collating(.nocase))

// (name >= 'foo' COLLATE NOCASE) AND (name < 'bar' COLLATE NOCASE)
("foo"..<"bar").contains(Column("name")).collating(.nocase)
("foo"..<"bar").contains(Column("name").collating(.nocase))

Arguably this is not the nicest part of GRDB, but I'm not sure SQL and SQLite are quite consistent either.

Rationale

Since name BETWEEN 'foo' AND 'bar' COLLATE NOCASE is valid SQL, users are entitled to collate a contains expression:

// name BETWEEN 'foo' AND 'bar' COLLATE NOCASE
("foo"..."bar").contains(Column("name")).collating(.nocase)

But now users have learned to collate contains expressions, and they will write:

("foo"..<"bar").contains(Column("name")).collating(.nocase)
["foo", "bar"].contains(Column("name")).collating(.nocase)

If we would generate (name >= 'foo' AND name < 'bar') COLLATE NOCASE, or name IN ('foo', 'bar') COLLATE NOCASE, the query would not behave as expected, and the user would have a bad surprise (this is why this issue was opened).

The "correct" way to write such an expression would be to collate the column, not the contains expression:

// (name >= 'foo' COLLATE NOCASE) AND (name < 'bar' COLLATE NOCASE)
("foo"..<"bar").contains(Column("name").collating(.nocase))

// (name COLLATE NOCASE) IN ('foo', 'bar')
["foo", "bar"].contains(Column("name").collating(.nocase))

I do not like that users pay for the mismatch between the Swift API and SQL, or to remember subtle SQLite rules. So I prefer that both ways to collate contains expressions are equivalent:

// (name >= 'foo' COLLATE NOCASE) AND (name < 'bar' COLLATE NOCASE)
("foo"..<"bar").contains(Column("name")).collating(.nocase)
("foo"..<"bar").contains(Column("name").collating(.nocase))

// name COLLATE NOCASE IN ('foo', 'bar')
["foo", "bar"].contains(Column("name")).collating(.nocase)
["foo", "bar"].contains(Column("name").collating(.nocase))

Now contains(x).collating(...) and contains(x.collating(...)) are generally equivalent. The commit 89421c9 just completes picture.