Fix the sargability check for a slow query

Review the AI's codeEasy12 min

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 a WHERE clause, one per string, such as status = 'failed'.
  • Return one verdict per predicate, in order: "function_on_column", "leading_wildcard", "sargable" or "invalid".

Rules the tests check, in order:

  1. Trim whitespace from both ends. An empty predicate is "invalid".
  2. 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".
  3. 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".
  4. Otherwise the verdict is "sargable". A function on the other side, like created_at >= NOW(), does not stop the index.

Examples

check_sargability(predicates: list[str]) -> list[str]
Example 1
Inputcheck_sargability(["DATE(created_at) = CURRENT_DATE", "LOWER(email) LIKE '%@acme.com'", "status = 'failed'"])
Output["function_on_column", "function_on_column", "sargable"]
Example 2
Inputcheck_sargability(["created_at >= CURRENT_DATE", "email LIKE '%@acme.com'", "email LIKE 'acme%'"])
Output["sargable", "leading_wildcard", "sargable"]
Example 3
Inputcheck_sargability([])
Output[]