An entity maps a boolean to the characters 'Y' and 'N' through a JPA AttributeConverter. What can go wrong when you query, sort or index that column, and how do you work around it?

## Where a converter sits A converter is invoked in exactly two places: when the provider binds a value into a JDBC statement, and when it extracts one from a result set. Everything else in the stack — the SQL text, the database's evaluation of predicates, the index, the sort — deals with the *stored* form and knows nothing about the Java type. Once you hold that model, every surprise in this area is predictable. ## Parameters are converted; the database's own work is not A JPQL query comparing a converted attribute to a bound parameter works as you expect: ```java em.createQuery("select o from Order o where o.active = :flag", Order.class) .setParameter("flag", true) .getResultList(); ``` The provider knows `o.active` is converted, so it runs `true` through `convertToDatabaseColumn` and binds `'Y'`. The Criteria API behaves the same way, since it also goes through parameter binding. Inline **literals** are the shakier case. Historically, providers did not push literals in query text through the converter, so `where o.active = true` compiled to a comparison against a boolean the column cannot hold, producing either a type error or silently wrong results. Modern Hibernate handles many of these cases, but the version dependence is exactly why the durable advice is: **bind parameters, do not write literals** against converted attributes. ## Native SQL bypasses conversion for input A native query is text you hand to the database. Nothing in it is converted, so you must write the stored form: `where active = 'Y'`. This cuts both ways — results mapped back onto entities *are* converted on extraction, because that path goes through the mapping, but scalar projections come back raw. The asymmetry catches people who convert one direction and forget the other. Bulk `update`/`delete` statements deserve the same care: in JPQL they bind parameters and convert, but they bypass the persistence context, and in native form they bypass conversion too. ## Database-side evaluation sees the stored form This is the substantial category: - **Ordering.** `order by o.active` sorts `'N'` before `'Y'`, which happens to match `false` before `true` — a coincidence. Store a status enum as `'A'`, `'P'`, `'X'` and `order by status` gives you alphabetical order, not lifecycle order. If a report depends on domain order, either store an order-preserving code or sort in a `case` expression. - **Range and pattern predicates.** `like`, `between`, `>` and `<` all mean whatever they mean over the stored form. A `Money` converted to `BigDecimal` ranges correctly; a `Money` converted to the string `"12.34 EUR"` does not. - **Functions.** `upper(o.code)` runs on the stored string; if the converter also uppercases, you have two places doing the same thing, and if it does not, case handling differs between Java and SQL paths. - **Aggregates.** `min`/`max`/`avg` are computed by the database on the stored type, then extracted and converted; an aggregate over a converted type may not even be extractable back into the Java type. - **Grouping and `distinct`** collapse on the stored form, so two Java values that convert to the same stored value become one group. ## Indexes Indexes are unaffected in the sense that they index the stored column normally — a `char(1)` index over `'Y'`/`'N'` works, though its selectivity is poor. The real damage comes from converters that make the column opaque: a value object serialized to JSON or to a delimited string cannot be range-scanned, and a predicate over one of its parts requires either a full scan with a database-side function or a vendor-specific expression index. If you filter on it, do not hide it inside a converted blob. ## Nulls If `convertToDatabaseColumn(null)` returns a non-null value — say an empty string, or `'N'` for a null Boolean — then `where o.active is null` will never match a row that was null in Java. Conversely, a converter that maps a sentinel stored value back to Java `null` creates rows the database does not consider null. Decide the null contract deliberately and document it, because query semantics hinge on it. ## Design rules 1. Choose a stored form whose natural comparison and ordering match the domain's, or accept that ordering must be expressed explicitly. 2. Bind parameters; avoid literals in JPQL over converted attributes. 3. Treat native SQL as a separate world and write the stored form there by hand — and keep the mapping between the two forms in one place, so it can change in one place. 4. Never convert a column you need to filter, range-scan or index into an opaque or composite representation. Use separate columns or an embeddable instead. 5. Watch out for reporting and ad-hoc SQL by other teams: converters are invisible outside your application, so the stored form is the real public contract of that column.