Parse and Evaluate a SQL WHERE Clause

import operator
import re
from typing import Any, Callable

Pred = Callable[[dict], Any]  # returns True / False / None (UNKNOWN)
TOKEN = re.compile(r"\s*(?:(-?\d+(?:\.\d+)?)|('(?:[^']|'')*')|(<=|>=|<>|!=|[=<>(),])|([A-Za-z_]\w*))")
OPS = {"=": operator.eq, "!=": operator.ne, "<>": operator.ne, "<": operator.lt,
       "<=": operator.le, ">": operator.gt, ">=": operator.ge}


def tokenize(text: str) -> list[tuple[str, Any]]:
    tokens, pos, text = [], 0, text.rstrip()
    while pos < len(text):
        m = TOKEN.match(text, pos)
        if not m:
            raise SyntaxError(f"unexpected character at {pos}")
        num, string, op, ident = m.groups()
        if num:
            tokens.append(("LIT", float(num) if "." in num else int(num)))
        elif string:
            tokens.append(("LIT", string[1:-1].replace("''", "'")))
        elif op:
            tokens.append(("OP", op))
        elif ident.upper() in ("AND", "OR", "NOT", "IN"):
            tokens.append(("KW", ident.upper()))
        else:
            tokens.append(("IDENT", ident))
        pos = m.end()
    return tokens + [("EOF", None)]


def compile_where(expr: str) -> Pred:
    toks, i = tokenize(expr), 0

    def take(expected: tuple[str, Any] | None = None) -> tuple[str, Any]:
        nonlocal i
        tok = toks[i]
        if expected and tok != expected:
            raise SyntaxError(f"expected {expected[1]!r}, got {tok[1]!r}")
        i = min(i + 1, len(toks) - 1)
        return tok

    def binary(word: str, sub: Callable[[], Pred], join: Callable[[Pred, Pred], Pred]) -> Callable[[], Pred]:
        def parse() -> Pred:  # left-associative chain: sub (word sub)*
            node = sub()
            while toks[i] == ("KW", word):
                take()
                node = join(node, sub())
            return node
        return parse

    def not_expr() -> Pred:
        if toks[i] == ("KW", "NOT"):
            take()
            inner = not_expr()
            return lambda row: None if (v := inner(row)) is None else not v
        return predicate()

    def and3(a: Pred, b: Pred) -> Pred:  # FALSE dominates, then UNKNOWN
        def f(row: dict) -> Any:
            x = a(row)
            if x is False:
                return False
            y = b(row)
            return False if y is False else None if x is None or y is None else True
        return f

    def or3(a: Pred, b: Pred) -> Pred:  # TRUE dominates, then UNKNOWN
        def f(row: dict) -> Any:
            x = a(row)
            if x is True:
                return True
            y = b(row)
            return True if y is True else None if x is None or y is None else False
        return f

    # precedence: OR < AND < NOT < comparison
    and_expr = binary("AND", not_expr, and3)
    or_expr = binary("OR", and_expr, or3)

    def operand() -> Pred:
        kind, val = take()
        if kind == "LIT":
            return lambda row: val
        if kind == "IDENT":
            return lambda row: row.get(val)
        raise SyntaxError(f"expected value, got {val!r}")

    def predicate() -> Pred:
        if toks[i] == ("OP", "("):
            take()
            node = or_expr()
            take(("OP", ")"))
            return node
        lhs = operand()
        if toks[i] == ("KW", "IN"):
            take(); take(("OP", "("))
            options = [operand()]
            while toks[i] == ("OP", ","):
                take(); options.append(operand())
            take(("OP", ")"))

            def in_list(row: dict) -> Any:
                a = lhs(row)
                if a is None:
                    return None
                vals = [o(row) for o in options]
                return True if a in vals else None if None in vals else False
            return in_list
        kind, op = take()
        if kind != "OP" or op not in OPS:
            raise SyntaxError(f"expected comparison, got {op!r}")
        rhs, fn = operand(), OPS[op]
        # any NULL operand makes the comparison UNKNOWN
        return lambda row: None if (a := lhs(row)) is None or (b := rhs(row)) is None else fn(a, b)

    node = or_expr()
    if toks[i][0] != "EOF":
        raise SyntaxError(f"trailing token {toks[i][1]!r}")
    return lambda row: node(row) is True  # WHERE keeps only TRUE, never UNKNOWN