Jev was the tipping point - I built the last MySQL search statement

#showdev #mysql #jev

The problem that kept finding me was mapping unstructured data into an existing corpus. Supplier products that needed to match a catalog, text that described something already in the database, but with no ID to join on and no guaranteed format. You've been there too.

The options were limited: a pile of LIKE conditions that break in reality, or an external pipeline that exports the data and embeds it somewhere else.

So I built AILIKE.

It lets you filter rows with natural-language conditions, right inside the query:

SELECT * FROM film
WHERE description AILIKE 'The story takes place somewhere in Asia';

And it does the thing I actually needed all along - comparing two values inside a JOIN, so the database itself can answer "do these two descriptions mean the same product?":

SELECT a.id AS supplier_product_id, b.id AS catalog_product_id
FROM supplier_products AS a
JOIN catalog_products AS b
  ON a.category_id = b.category_id
 AND ailike(a.description, b.description,
            'Left and right describe the same product type, allowing different wording.');

Why inside MySQL

Simplicity. The plugin installs into the server you already run, and then it's just SQL - nothing separate to deploy or run. There's a second reason: if an agent is writing your queries, it already knows SQL. A predicate inside the query is something it can use on its own, instead of you teaching it another layer.

Why Jev

The conditions are judged by TypeSafe Jev, and this was the straightforward part.

Jev does exactly what a filter predicate needs: hand it values and a natural-language condition, get back a judgment. The judgment comes back structured, in one pass. No prompt scaffolding, no parsing free text out of a chat model.
The answer comes back shaped the way a query needs it. 1 for match, 0 for mismatch, NULL for null values.

The contract with your query planner

Each uncached comparison makes one API call to Jev, which is an API call and will always be the heavy part of any SQL statement.

So the deal is simple, narrow with ordinary SQL first - indexes, ranges, categories - and let Jev judge the survivors. The JOIN example above works because the category join cuts the candidate pairs down before a single call is made. Point it at a million-row table and you're paying for a million judgments (so don't :))

The hardest part

Making column AILIKE 'prompt' work safely inside an existing MySQL server. The rewrite must understand enough SQL to handle quotes, comments, identifiers, and precedence without changing unrelated queries. A particularly awkward constraint: MySQL's preparse rewrite resets recognition of bound parameters. That's why prepared queries must use the function form.

Another subtle problem was failure handling: setting a MySQL UDF's error flag alone produces NULL. In a filter, that could silently exclude rows when the API fails. We explicitly raise a statement error instead.

Why open source

This code runs inside someone's database and sends selected values to an external service. Users should be able to inspect that behavior, build it themselves, fix problems, and maintain their own version. That makes openness useful to adoption and trust. The plugin is open source. Jev remains a hosted dependency.

The code is at https://github.com/maayanlevy/mysql-ailike. If you try it, I'd like to know: where does it break for you?

DEV Community