COLLATE nocase and noaccent

Applies to VAST Cluster 5.5.

Functions › Text basics › Case. COLLATE after a string in a comparison compares it without regard to case (nocase), to accents (noaccent), or to both (nocase.noaccent). Language collations, such as COLLATE de, aren't available.

Syntax

s COLLATE nocase = t
s COLLATE noaccent = t
s COLLATE nocase.noaccent = t

Argument	Type	Description
`s`	`VARCHAR`	The string to compare: a column or a constant. `COLLATE` follows it directly.
`t`	`VARCHAR`	The string to compare it with.
collation	`nocase`, `noaccent` or `nocase.noaccent`	What to ignore: case, accents, or both.

Returns: BOOLEAN, the result of the comparison.

Aliases: none.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_collate. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of five customers. Rows 2 and 3 are the same person, typed with and without accents and capitals. name_key is a lower-case, accent-free copy of the name, written by the application when it stored the row. The last row has no values:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_collate"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.customers"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR, city VARCHAR, name_key VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Ada Lovelace', 'London', 'ada lovelace'), "
    "(2, 'José Martí', 'Havana', 'jose marti'), "
    "(3, 'jose marti', 'HAVANA', 'jose marti'), "
    "(4, 'Renée Dupont', 'Montréal', 'renee dupont'), "
    "(5, NULL, NULL, NULL)")

[]
[]
[(5,)]

What it does

A plain = on strings compares them exactly: 'abc' = 'ABC' is false. COLLATE changes what counts as equal:

Collation	Ignores	In a plan, the column becomes	Example	Result
none	nothing	`s`	`'abc' = 'ABC'`	`false`
`nocase`	case	`lower(s)`, and the constant is lower-cased too	`'abc' COLLATE nocase = 'ABC'`	`true`
`noaccent`	accents, but not case	`strip_accents(s)`	`'Crème' COLLATE noaccent = 'creme'`	`false` (C and c differ)
`nocase.noaccent`	case and accents	`lower(strip_accents(s))`	`'É' COLLATE nocase.noaccent = 'e'`	`true`

The Query Engine rewrites the comparison into the function it stands for. In a plan, city COLLATE nocase = 'havana' shows up as lower(city) = 'havana', city COLLATE nocase = 'HAVANA' also as lower(city) = 'havana', name COLLATE noaccent = 'cafe' as strip_accents(name) = 'cafe', and name COLLATE nocase.noaccent = 'cafe' as lower(strip_accents(name)) = 'cafe'. So nocase follows the rules of lower, and noaccent those of strip_accents. noaccent also removes an accent written as a combining mark: 'e\u0301' COLLATE noaccent = 'e' (e followed by U+0301) is true.

noaccent on its own is still case-sensitive. To ignore both, use nocase.noaccent.

Where else COLLATE works:

Use	Example	Result
Upper-case collation name	`'a' COLLATE NOCASE = 'A'`	`true`
On the right operand	`'A' = 'a' COLLATE nocase`	`true`
Ordering comparisons	`'b' COLLATE nocase > 'A'`	`true`
`IN`	`'ABC' COLLATE nocase IN ('abc')`	`true`
`ORDER BY`	`ORDER BY s COLLATE nocase`	rows sorted as `lower(s)` would sort them, NULLs last

NULLs and edge cases

Input	Result
`'abc' = 'ABC'`	`false`
`'abc' COLLATE nocase = 'ABC'`	`true`
`'Crème' COLLATE noaccent = 'creme'`	`false`
`'É' COLLATE nocase.noaccent = 'e'`	`true`
A `NULL` value, such as row 5 in Examples	`NULL` (`None` in Python)
`COLLATE de` or another language collation	Error: `Collation with name "de" is not in the catalog`

The same cases, as the cluster returns them; the last example in Examples shows the NULL row. They use the table and helpers from Set up the examples:

run("SELECT 'abc' = 'ABC', 'abc' COLLATE nocase = 'ABC', 'Crème' COLLATE noaccent = 'creme', 'É' COLLATE nocase.noaccent = 'e'")
run("SELECT 'a' COLLATE de = 'A'")

[(False, True, False, True)]
InternalError: Catalog Error: Collation with name "de" is not in the catalog, but it exists in the icu extension.

Examples

Find the customers in Havana, however the city was typed:

run(f"SELECT id, name, city FROM {TABLE} WHERE city COLLATE nocase = 'havana' ORDER BY id LIMIT 10")

[(2, 'José Martí', 'Havana'), (3, 'jose marti', 'HAVANA')]

Ignore accents only. Row 2 matches; row 3 doesn't, because noaccent still compares case:

run(f"SELECT id, name FROM {TABLE} WHERE name COLLATE noaccent = 'Jose Marti' ORDER BY id LIMIT 10")

[(2, 'José Martí')]

Ignore both, and show the comparison for every row. Rows 2 and 3 match; the row with no name gives None:

run(f"SELECT id, name COLLATE nocase.noaccent = 'jose marti' FROM {TABLE} ORDER BY id LIMIT 10")

[(1, False), (2, True), (3, True), (4, False), (5, None)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). VAST DataBase can evaluate a comparison of a column with a constant, but COLLATE turns the comparison into a function of the column, so it's always evaluated by the Query Engine:

In the SELECT list, the scan reads the column and the Query Engine computes the comparison for each row it returns.
In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes lower() or strip_accents() and keeps the rows that match. No rows are skipped in storage.

The plans of the first two examples show the rewritten comparison in a FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE city COLLATE nocase = 'havana'")
plan(f"SELECT id FROM {TABLE} WHERE name COLLATE noaccent = 'Jose Marti'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (lower(city) = 'havana') │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            city           │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (strip_accents(name) =   │
│       'Jose Marti')       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            name           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

Store a normalized key and compare it exactly. If you search a column without regard to case or accents often, write a lower-case, accent-free copy when you store the rows (here name_key) and compare it with a normalized constant. A comparison of the raw column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan has no FILTER step:

run(f"SELECT id, name FROM {TABLE} WHERE name_key = 'jose marti' ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE name_key = 'jose marti'")

[(2, 'José Martí'), (3, 'jose marti')]
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Combine it with a pushed-down condition. In id > 1 AND city COLLATE nocase = 'havana', id > 1 is still evaluated in VAST DataBase, so the lower() filter runs only on the rows that pass it.

Limitations and errors

Only nocase, noaccent and nocase.noaccent. Language collations, such as COLLATE de for German sorting rules, aren't available in the VAST Query Engine. They fail with Catalog Error: Collation with name "de" is not in the catalog, but it exists in the icu extension. Use nocase or noaccent, or apply language rules in your code.
noaccent alone keeps case. 'Crème' COLLATE noaccent = 'creme' is false; use nocase.noaccent.
Strings only. COLLATE on the bare literal NULL fails with Binder Error: collations are only supported for type varchar. A NULL from a VARCHAR column is fine and gives NULL.
GROUP BY s COLLATE nocase limits the select list. The select list can't use another expression of s: SELECT lower(s), count(*) FROM t GROUP BY s COLLATE nocase fails with column "s" must appear in the GROUP BY clause or must be part of an aggregate function.

run("SELECT 'a' COLLATE de = 'A'")
run("SELECT 'Crème' COLLATE noaccent = 'creme', 'Crème' COLLATE nocase.noaccent = 'creme'")

InternalError: Catalog Error: Collation with name "de" is not in the catalog, but it exists in the icu extension.
[(False, True)]

Similar functions

The same pair of strings, 'É' and 'e', compared in different ways:

Comparison	Result
`'É' = 'e'`	`false`
`'É' COLLATE nocase = 'e'`	`false`: lower of `'É'` is `'é'`, which still has its accent
`'É' COLLATE noaccent = 'e'`	`false`: strip_accents of `'É'` is `'E'`, which is still upper case
`'É' COLLATE nocase.noaccent = 'e'`	`true`
`lower(strip_accents('É')) = 'e'`	`true`: the same as `nocase.noaccent`, written with functions

For a pattern match without regard to case, see ILIKE.

run("SELECT 'É' = 'e', 'É' COLLATE nocase = 'e', 'É' COLLATE noaccent = 'e', 'É' COLLATE nocase.noaccent = 'e'")
run("SELECT lower('É'), strip_accents('É'), lower(strip_accents('É')) = 'e'")

[(False, False, False, True)]
[('é', 'E', True)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]