Fix the sargability check for a slow query
Your task
An AI wrote a query with WHERE DATE(created_at) = CURRENT_DATE AND LOWER(email) LIKE '%@acme.com'. It was correct, but it scanned all 500 million rows in 40 seconds: a function on an indexed column, or a LIKE pattern that starts with %, stops a btree index from being used. A predicate that can use the index is called sargable. Your team now checks each predicate in review.
An assistant wrote check_sargability(predicates). It is in the editor. It has a bug. Find it and fix it.
Input and output:
predicates: the conditions from aWHEREclause, one per string, such asstatus = 'failed'.- Return one verdict per predicate, in order:
"function_on_column","leading_wildcard","sargable"or"invalid".
Rules the tests check, in order:
- Trim whitespace from both ends. An empty predicate is
"invalid". - The column side is the text before the first space, or the whole predicate if it has no space. If the column side contains
(, the verdict is"function_on_column". - Otherwise, if the predicate contains
LIKE(a space, LIKE in any letter case, a space), look at the text after it with leading spaces removed. If that starts with'%or%, the verdict is"leading_wildcard". - Otherwise the verdict is
"sargable". A function on the other side, likecreated_at >= NOW(), does not stop the index.
Examples
check_sargability(predicates: list[str]) -> list[str]Example 1
Input
check_sargability(["DATE(created_at) = CURRENT_DATE", "LOWER(email) LIKE '%@acme.com'", "status = 'failed'"])Output
["function_on_column", "function_on_column", "sargable"]Example 2
Input
check_sargability(["created_at >= CURRENT_DATE", "email LIKE '%@acme.com'", "email LIKE 'acme%'"])Output
["sargable", "leading_wildcard", "sargable"]Example 3
Input
check_sargability([])Output
[]