What You Still Don’t KnowAbout FlexibleSearch: a 2026 revisit of the query translator

In 2016 I published two short notes on FlexibleSearch. Both were written against hybris 6.x. Since then the product has been renamed twice, the persistence layer has grown an entirely new write path, and the query cache has acquired three separate TTL mechanisms that did not exist back then.

So the obvious question: how much of that is still true?

I went back through the behavior of a current SAP Commerce Cloud 2211. The short answer is that almost everything from 2016 still holds — including the ORDER BY marker problem, which has in fact become slightly more interesting and a number of new things became known. The long answer is this article, because once you start looking at what the translator actually does with your query string, you find a lot more than three modifiers.

FlexibleSearch is one of the oldest components in the platform. It predates the ServiceLayer, it predates the model layer, and it shows. It is also the single component that every project touches on day one and almost nobody reads carefully. What follows is the part of its behavior that you will not find in the SAP Help Portal.

It should be noted that some undocumented features are undocumented for a reason. They are legacy, not always successful solutions that SAP for some reason does not remove from the code—apparently for compatibility with certain ancient implementations. They are undocumented not least because of their inadequacy or uselessness. But it’s still worth knowing about them, because you can occasionally run into them by accident and end up with a bug or undesired behavior.

How SAP Commerce Parses FS

The first thing worth internalizing is that FlexibleSearch does not have a SQL parser. It locates clause boundaries by searching for the words select, from, where, group by and order by in the lower-cased query string, each search starting from where the previous one ended.

// illustrative
int selectPos  = findWholeWord(lower, "select");
int fromPos    = findWholeWord(lower, "from",     selectPos + "select".length());
int wherePos   = findWholeWord(lower, "where",    fromPos + "from".length());
int groupByPos = findWholeWord(lower, "group by", wherePos >= 0 ? wherePos + 5 : fromPos + 4);
int orderByPos = findWholeWord(lower, "order by", groupByPos >= 0 ? ... : ...);

Illustrative reconstruction, not platform source. See the note above.

“Whole word” here means only that the characters on either side are not letters, digits or underscores. There is no awareness of string literals, comments or nesting.

Example. This query does not do what it looks like:

SELECT {pk} FROM {Product} WHERE {code} = 'order by me'

The scan for order by starts after where, and the first match it finds is inside the literal. The WHERE clause is cut at that point — it becomes {code} = ' — and me' becomes the ORDER BY clause. The translator then appends its type predicate to the truncated WHERE, which puts it inside the string literal:

SELECT item_t0.PK FROM products item_t0
WHERE ( item_t0.p_code = ') AND (item_t0.TypePkString IN (?,?,...) ) order by me'

On HSQLDB this ends with unexpected token : LIMIT required: ). Exactly the same happens with 'group by me'. You are relying on the database to reject the result; whether the mangled statement happens to stay syntactically valid depends on what follows the keyword in your literal.

The same applies to group by. select, from and where are more robust because each is searched from the end of the previous one, so the first match is almost always the real keyword — but a literal containing from inside the select list will still break it.

Practical rule: anything that might contain order by, group by or from goes in as a bind parameter, never concatenated into the query text. This is good practice anyway, but here the motivation is not injection — it is that the query simply will not be split correctly.

One consequence worth noting: HAVING is not in the keyword list. It is not treated as its own clause; it gets absorbed into the GROUP BY text. That turns out to be harmless: the field expressions inside it are translated together with the GROUP BY, including the rewrite to uhu.* columns when the query becomes a UNION (see below). HAVING MAX({name}) IS NULL over {Principal} correctly becomes HAVING MAX(uhu.groupByArg0) IS NULL.

{{ … }} is matched by counting braces

Subqueries are extracted before anything else, by walking the string and counting braces. Double braces open and close a subquery; single braces are counted separately so that a field expression does not accidentally terminate one.

// illustrative
if (c == '{') {
    if (next == '{') { if (++subqueryDepth == 1) start = pos; pos++; }
    else             { singleBraceDepth++; }
} else if (c == '}') {
    if (next == '}' && singleBraceDepth == 0) { /* close subquery, emit placeholder */ }
    else if (--singleBraceDepth < 0) {
        throw new FlexibleSearchException("missing '{' for '}' at " + pos);
    }
}

Illustrative reconstruction, not platform source. See the note above.

Again: no literal awareness.

Example. This fails before it ever reaches the database:

SELECT {pk} FROM {Product} WHERE {code} = '}'

missing '{' for '}' at 43 in '''

The mirror case fails too, with the opposite message:

SELECT {pk} FROM {Product} WHERE {code} = '{'

missing '}' for '{' at 11 in '{code} = '{''

Note that this one quotes the WHERE clause, not the query, and the position is counted within that fragment — by that point the clauses have already been split. Neither message mentions that the brace sits inside a string literal, so they are easy to misread as a typo in a field expression.

The field expression has a real grammar — and most of it is undocumented

This is where the 2016 article stopped at three modifier letters. There is considerably more. The full shape of a field expression is:

{ [type] ( '.' | ':' ) attribute [ '[' language ']' ] [ ':' options ] }

First, . and : are interchangeable for the type separator

{Product.code} and {Product:code} are the same thing. The difference is that the dot only works as the first separator, while the colon works both as the type separator and as the separator before the options. So all of these are valid:

{Product.code}
{Product:code}
{Product.code:o}
{Product:code:o}

Second, a numeric type qualifier is a positional index, not a type name

If the type qualifier consists only of digits, it is interpreted as a 1-based index into the FROM clause, not as a type code.

// illustrative
Object parseTypeQualifier(String s) {
    if (s == null || s.isEmpty()) return null;              // → default (first) type
    if (isAllDigits(s))           return parseInt(s) - 1;   // → positional index
    return s;                                               // → type code or alias
}

Illustrative reconstruction, not platform source. See the note above.

Example.

SELECT {1:code}, {2:version}
FROM   {Product AS p JOIN CatalogVersion AS cv ON {p:catalogVersion} = {cv:pk}}

{1:code} is the product code, {2:version} is the catalog version. The index counts every type in the FROM clause, relation types included: in {Product AS p JOIN CategoryProductRelation AS r ON ... JOIN Category AS c ON ...} the category is {3:code}, not {2:code}.

The engine itself considers this legacy — the platform contains the message

deprecated syntax of type index (like {1:name}) is not allowed inside restrictions
- use alias names instead

In practice I could not trigger it with a plain restriction: a restriction with the query {1:isocode} = 'en' on Language was applied without complaint, and 1 was resolved relative to the restricted type — even when Language was the second type in the outer query's FROM clause. The explanation seems to be that inside a restriction the field's type is already pinned to the restricted type before the qualifier is examined at all, so the positional lookup — and with it the guard — is never reached. You would need a nested query with its own FROM inside the restriction to get there. Either way: use aliases.

The part that bites is different: a type whose code is entirely numeric can never be addressed by name in a field expression, because it will always be read as an index.

Third, an empty type qualifier means “the default type”

You need a type separator in order to specify options. But the type itself may be empty:

SELECT {pk} FROM {Product} WHERE {:name:o} IS NULL

An empty qualifier resolves to the default type, which is the first type in the FROM clause.

And here is the trap. This is not the same thing:

WHERE {code:o} IS NULL

Walking the automaton by hand: at the first colon the parser is still in its initial state, so it concludes that code is the type (or alias) and o is the attribute. You get

cannot find (visible) type for alias code within [Product:Product]

or, if an alias called code happens to exist in the query, silently the wrong column. Options without an explicit — or at least empty — type qualifier do not work, and there is no diagnostic for it.

Fourth, [any] — search a localized attribute across all languages

The language clause accepts an ISO code, but it also accepts one special value:

// illustrative
PK resolveLanguage(String clause) {
    if (clause == null)              return null;        // session language
    if ("any".equalsIgnoreCase(clause)) return PK.NULL_PK; // marker: do not filter by language
    PK byIso = languagePkForIsoCode(clause);
    return byIso != null ? byIso : PK.parse(clause);     // fallback: a language PK
}

Illustrative reconstruction, not platform source. See the note above.

Example. Find products whose name contains “bike” in any language, without knowing which:

SELECT DISTINCT {p:pk}
FROM   {Product AS p}
WHERE  {p:name[any]} LIKE '%bike%'

Without [any] this filters on the session language only. I have never seen [any] mentioned in the documentation, and it is the cleanest way to do a cross-language lookup without hand-writing a join to the *lp table. The translator drops the main table entirely and queries the lp table on its own:

SELECT DISTINCT lp_t0.ITEMPK FROM productslp lp_t0
WHERE ( lp_t0.p_name LIKE '%bike%') AND (lp_t0.ITEMTYPEPK IN (?,?,...) )

That has a side effect the platform itself exploits. Because the lp table is now a real table in the statement with a predictable alias, you can write a join condition against that alias even though the table appears nowhere in your FROM clause. A query shaped like this one — you can see it in the SQL log while backoffice builds its type-system cache — pulls the entire “type × language × translation” matrix in one statement:

SELECT {t.code}, {l.isocode}, {t.name[any]}
FROM   { ComposedType AS t JOIN Language AS l ON lp_t0.langpk = {l.pk} }
WHERE  {l.isocode} IN (?langs)

lp_t0 is not declared anywhere. It exists only because {t.name[any]} caused the translator to emit it, and it is named _t0 only because it happened to be the first table of its kind in this query. It is a genuinely clever trick and an extremely brittle one — it depends on the alias counter, which depends on the shape of the rest of the query. I would not write it in project code. It is, however, a very clear illustration of the theme of this article: the thing you are writing is not a query language, it is a string that gets rewritten into SQL, and anything visible in the output is fair game.

The language clause also accepts a language PK instead of an ISO code: {p:name[8796093055008]} works. That is the fallback when the value is not a known ISO code, which is why a typo produces a rather confusing message:

pk has wrong format: 'xx':For input string: "xx"

Fifth, [language] must come before :options, not after

The grammar above is not a suggestion about style — the order is enforced. The language clause is only accepted while the attribute is still being read, so once a second colon has introduced the options, a [ is a syntax error:

{Product:name[en]:o}   -- fine
{Product:name:o[en]}   -- wrong search field definiton ... found '[' outside attribute spec

Note the spelling in that message. definiton, with the i missing, is what the platform actually emits — useful to know when you are grepping logs for it, and a reminder of how old this code is.

Sixth, there are c / l / o options

These are the three from 2016, and they still behave the same way:

// illustrative
String flags = options.toUpperCase();
this.core      = flags.indexOf('C') > -1;
this.localized = flags.indexOf('L') > -1 || customLanguagePk != null;
this.optional  = flags.indexOf('O') > -1;

Illustrative reconstruction, not platform source. See the note above.

Two properties of that implementation are worth stating explicitly, because they are easy to get wrong:

• Case does not matter. :o, :O, :oL all work.
• Any other character is silently ignored. :xyz is not an error; it simply enables nothing. A typo in an option produces no diagnostic whatsoever.

o remains the most useful of the three. Localized attributes live in a separate *lp table which is joined with an inner join by default — meaning rows that have no lp entry for the requested language disappear from the result set entirely.

Example. Products with no name in the current language:

SELECT {p:code}
FROM   {Product AS p}
WHERE  {p:name:o} IS NULL

Without :o you only get products that have an lp row for the language, with NULL in it. Products with no lp row at all — usually the majority — are already gone after the inner join. On my test system the difference was 331 rows versus 102,366:

-- without :o — the products table is not even in the query
SELECT COUNT(*) FROM productslp lp_t0
WHERE lp_t0.LANGPK = ? AND lp_t0.p_name IS NULL AND lp_t0.ITEMTYPEPK IN (...)

-- with :o
SELECT COUNT(*) FROM products item_t0
LEFT JOIN productslp lp_t0 ON item_t0.PK = lp_t0.ITEMPK AND lp_t0.LANGPK = ?
WHERE lp_t0.p_name IS NULL AND item_t0.TypePkString IN (...)

This is the single most common “FlexibleSearch returns fewer rows than the database has” confusion, and the fix is one character.

Seventh, {pk} and {itemtype} are resolved late

Both are special-cased and resolved in a second pass, after table assignment. Which column they map to depends on which table the field expression landed in:

// illustrative
if ("pk".equals(name))
    return alias + (table.isMainTable() ? ".PK" : ".ITEMPK");
if ("itemtype".equals(name))
    return alias + (table.isMainTable() ? ".TypePkString" : ".ITEMTYPEPK");

Illustrative reconstruction, not platform source. See the note above.

This is why, in a complex query involving localized attributes or dynamic properties, {pk} occasionally resolves against a table you did not expect.

“Which table” has more answers than the usual two. A type can contribute four of them to a statement, and the translator fills them in by placeholder as it assembles the SQL: the core table (products), the localized table (productslp), a property table for attributes stored as dynamic properties, and a localized property table for the ones that are both. Most discussions of FlexibleSearch mention only the first two, which is fine until a query touches a dynamic attribute and {pk} lands on a table whose PK column is called ITEMPK.

Type modifiers: !, *, and the one nobody mentions, ^

The 2016 article covered ! and *. There is a third, and the semantics of ! are worth pinning down precisely, because they are very commonly remembered backwards.

For ! the decisive evidence is the error you get when you apply it to an abstract type:

cannot search on abstract type 'AbstractOrder' with no-subtypes option ('!')

That message only makes sense in one direction: ! excludes subtypes, so an abstract type with ! has nothing left to search. If ! added subtypes, searching an abstract type with it would be the one case that works.

The modifier is not a suffix, and the three of them have a pecking order

It is natural to read {Product!} as “type code, then a trailing modifier”. That is not how it is found. Each of the three characters is searched for in the type expression, and the type code is whatever precedes the first one that matches. Two consequences follow, both reproducible.

They have a fixed precedence, regardless of the order you write them in. ! is looked for first, then *, then ^. So {Product*!} and {Product!*} both mean !, and {Product^*} means * — the ^ never gets a chance. Writing two modifiers is obviously a mistake, but it is a silent one.

A modifier is also recognised in the middle of the code. {Pro*duct} does not search Product with a modifier; it searches for a type called Pro, which almost certainly does not exist. The error you get names the truncated code, which is at least a readable clue.

And the trap worth remembering:

{Product AS p!}   -- NOT "no subtypes"

The search for the modifier only covers the part of the expression before AS. Written after the alias, the ! is simply part of the alias — the type is matched with subtypes as usual, and your alias is now p!, so every {p:...} in the query fails to resolve. The query breaks, but it breaks somewhere else, with a message about an unknown alias rather than about the modifier you thought you had written.

* switches off search restrictions

This is the part that deserves a security review line item rather than a trivia entry.

// illustrative
if (!query.restrictionsDisabled() && !this.typeChecksDisabled()) {
    restrictions = loadQueryFilters(query, this.getCode(), includeSubtypes);
} else {
    restrictions = Collections.emptyList();   // ← '*' lands here
}

Illustrative reconstruction, not platform source. See the note above.

The * modifier sets the same internal flag that disables type checking, and that flag also short-circuits restriction loading. So {Product*} does two things, not one:

1. it drops the TypePkString predicate, returning every row in the table — including rows belonging to other types that share the same deployment; and
2. it drops every search restriction defined for that type.

Example. On a catalogue-aware B2B storefront, these two queries do not return the same set of products for a restricted user:

SELECT {pk} FROM {Product}   -- restrictions applied
SELECT {pk} FROM {Product*}  -- restrictions bypassed

If search restrictions are your access-control mechanism — and in catalogue and B2B scenarios they usually are — then * is functionally equivalent to calling setDisableSearchRestrictions(true), except that it is a single character buried inside a string literal and invisible to anyone skimming the code.

Worth adding to your review checklist:

grep -rnE '\{[A-Za-z]+\*' --include=*.java --include=*.xml --include=*.impex .

That grep will find what your team wrote. It will not find the more interesting case: the platform does this to itself. The Integration API / OData layer resolves items by generating queries of the form

SELECT DISTINCT {mytype:pk} FROM {MyType* AS mytype} WHERE {mytype:a} = ?a

— and * there is not an option someone picked for a special case, it is the default item hierarchy mode for that subsystem. The item-type match is configurable (there is also a mode for “this type only”, which maps to !, and one for “type and subtypes”, which maps to no modifier at all), but unless something sets it, integration lookups run with type checking and search restrictions switched off.

Whether that is a problem depends entirely on how your integration endpoints are secured. If you assumed that search restrictions apply uniformly because they are enforced in the persistence layer, this is the counterexample: they are enforced in the translator, and a caller that generates the query text decides whether to invoke them.

There are also two global kill switches for restrictions with similarly low visibility:

flexible.search.disable.individual.search.restrictions=false
flexible.search.disable.individual.search.restrictions.for.members.of=

Both are read once and memoized, so a change to them does not reliably take effect without a restart.

^ as a way to avoid a UNION

^ keeps subtypes that live in the same table and drops the ones with their own deployment. That is precisely the lever you want when a type has subtypes you do not care about and the UNION they force is wrecking your execution plan — which brings us to the next section.

{Media} on a system with the rule engine installed is a good example: it unions medias with droolskiemodulemedia, while {Media^} reads medias only. But ^ does nothing for a type that has no table of its own. {AbstractOrder^} still unions carts, orders and quotes, and {Principal^} still unions usergroups and users: there is no "same table" to stay in.

Where uhu comes from

When a type has subtypes stored in different tables, one SELECT is not enough. The engine emits one SELECT per table and unions them. If the query also has a GROUP BY, an ORDER BY or an aggregate, the whole union gets wrapped in an outer select — with a hard-coded alias:

SELECT ... FROM (
    SELECT ... FROM products  WHERE ...
    UNION ALL
    SELECT ... FROM mediaitems WHERE ...
) uhu
ORDER BY ...

If you have ever found uhu in a slow-query log and wondered which developer left it there: it is the platform. (German for “owl”. The platform was built in Munich.)

By the way, UNION ALL is the default, and you can only change it from code

// illustrative
boolean unionAll() {
    Object v = currentSession().getAttribute("unionAllTypeHierarchy");
    return (v instanceof Boolean) ? (Boolean) v : true;   // default: UNION ALL
}

Illustrative reconstruction, not platform source. See the note above.

Note that this is a session attribute, not a configuration property. The default is UNION ALL, which is the right default — PKs from different tables cannot collide, so deduplication would be pure cost.

But it is the wrong default the moment you select something other than the PK:

SELECT {code} FROM {Product}

across a hierarchy with multiple deployments, this can return duplicate codes. If you need deduplication:

sessionService.setAttribute("unionAllTypeHierarchy", Boolean.FALSE);

The flag participates in the translation cache key, so flipping it does not poison the cache for other sessions.

The [-- / --] markers still there, and now they can eat your data

This was the closing section of the 2016 article, and it is the one finding I most expected to have been fixed. It has not been.

The engine wraps the top-level ORDER BY in markers so that it can later produce a count query by cutting the sort out:

SELECT ... FROM ... WHERE ...  [--order by p_name ASC--]

Two different operations consume those markers:

// illustrative
private static final Pattern MARKERS = Pattern.compile("(\\[--)|(--\\])");
private static final Pattern CLAUSES = Pattern.compile("\\[--(.*?)--\\]", Pattern.DOTALL);

String getSql()      { return MARKERS.matcher(sql).replaceAll(""); }  // strip markers only
String getCountSql() { return CLAUSES.matcher(sql).replaceAll(""); }  // strip ORDER BY entirely

Illustrative reconstruction, not platform source. See the note above.

Both are replaceAll. Not “replace the pair the engine inserted” — every occurrence, anywhere in the query. Exactly as in 2016, these all behave identically:

Select {name} from {Language} where {isocode} = '--]ru[--'
Select {name} from {Language} where {isocode} = 'ru[--'
Select {name} from {Language} where {isocode} = '[--ru--]'
Select {name} --]from {Language}[-- where {isocode} = '--]ru[----][--'

All of them look for the language with isocode ru.

Three things to add to the original finding:

The count query is a bigger blast radius than the main query. getCountSql() removes everything between [-- and --], non-greedy and with DOTALL enabled — so newlines do not stop it. A query that contains both markers in order loses the entire span between them, including a legitimate ORDER BY that happened to sit inside. Feeding the marker-stripping directly with

SELECT x FROM t WHERE a = '--]ru[--' AND b = ? [--order by x--]

gives a perfectly fine main query (... a = 'ru' AND b = ? order by x) and this count query:

SELECT x FROM t WHERE a = '--]ru

Everything from the [-- inside the literal to the real closing marker is gone, the bind parameter included.

The stripping runs after value substitution. The marker removal operates on the final statement text, which means that if values have been inlined into the SQL rather than bound (see the section on the bind-parameter limit below, which happens by default on SQL Server above a threshold), then user data is in scope. A value of "--]" gets silently edited out of the string before it reaches the database. "-- ]" is fine; "--]" is not.

Count parameters are taken off the tail. The bind values for the ORDER BY are removed by trimming the end of the value list:

// illustrative
int cut = values.size() - orderByClause.getParameterCount();
countValues = values.subList(0, cut);

Illustrative reconstruction, not platform source. See the note above.

That assumption holds for generated SQL. It stops holding the moment you post-process the statement yourself and reorder parameters.

Example of the practical risk. A product search feature that allows users to filter by SKU and passes the term straight through will, for a user who types --], quietly run a different query than the one you wrote. It will not appear in the application log, because by the time the SQL is logged it has already been cleaned.

Pagination is implemented seven times, and one of them rewrites your sort

There is a per-database pagination strategy, selected by database name: HSQLDB, Oracle, MySQL, HANA, SQL Server, PostgreSQL — and a fallback.

The fallback is not pagination. It returns the original statement unchanged and reports that the engine has no database-level limit support, which means the full result set is fetched and the page is sliced in Java. You also land here by setting:

flexible.search.disable.specific.db.limit.support=true

which is also readable as a session attribute of the same name.

Oracle gets the classic double-nested rownum form:

SELECT * FROM (
  SELECT row_.*, rownum rownum_ FROM (
     ...original query...
  ) row_ WHERE rownum <= ?
) WHERE rownum_ > ?

SQL Server is the interesting one. OFFSET … FETCH is only legal after an ORDER BY. If your query does not have one, the engine supplies it:

// illustrative
if (!orderByInfo.exists()) {
    return sql + " ORDER BY 1 OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
}

Illustrative reconstruction, not platform source. See the note above.

ORDER BY 1 — sort by the first column of the select list.

Example. This paginated query:

SELECT {code}, {name} FROM {Product}

returns page 2 sorted by code on SQL Server, and page 2 in whatever order the engine produced rows on Oracle. Same code, same data, different rows. If the first select column is {pk}, you get a sort by PK — which for counter-based PKs is roughly creation order, so the bug “works” and stays hidden until the day someone migrates the database.

A related edge: SQL Server with count == 0 falls back to the no-pagination builder, because FETCH NEXT 0 ROWS is illegal.

And HANA has a configuration flag whose name tells you everything about its history:

hanadb.hack.limit.parameters.as.literal=true

Two different ways around the bind-parameter limit

Databases cap the number of bind parameters per statement. The platform works around this in two unrelated ways, and it is useful to know which one is firing.

Oracle: the collection becomes a CTE. When a collection parameter exceeds db.supported.params.limit (1000 by default), the IN (?param) fragment is rewritten into a WITH table built from a UNION of SELECT … FROM dual:

with with_tbl_productPks1 (id) as (
  select '8796093022208' from dual UNION
  select '8796093022272' from dual UNION
  ...
)
SELECT ... WHERE pk IN ( SELECT (id) FROM with_tbl_productPks1 )

Note that the values are string literals, not bind variables — so a 5000-item list produces a few hundred kilobytes of unique SQL text, and a unique entry in the shared pool. Such a query also bypasses the translation cache entirely and is re-parsed on every call.

Everything else: parameters are inlined. Above a configured threshold, bind values are substituted into the statement text:

db.supported.inline.in.params.sqlserver=2000

That line ships in the default configuration. On SQL Server, a query carrying more than 2000 bind values is rewritten with literals — and on SQL Server, strings are inlined too, with quote doubling:

// illustrative
String inline(Object v) {
    if (v instanceof String) return "'" + ((String) v).replace("'", "''") + "'";
    if (v instanceof PK)     return ((PK) v).getLongValueAsString();
    if (v instanceof Number) return v.toString();
    throw new IllegalArgumentException("cannot inline " + v);
}

Illustrative reconstruction, not platform source. See the note above.

The escaping is correct. The operational consequence is not about safety, it is about the plan cache: every such statement is textually unique. Bulk operations over large PK lists — catalogue sync, price recalculation, index rebuilds — will fill the SQL Server plan cache with single-use plans.

The symptom to grep for in the application log:

got query ... with > 2000 parameters on sqlserver, trying to inline them

and, when inlining did not help:

got query ... with > 2000 parameters on sqlserver - this will most likely crash!

This is also where the [-- / --] markers reconnect: once values are inlined, they are part of the statement text, and the [-- / --] stripping applies to them.

Named parameters go deeper than ?session.user

The 2016 article noted that ?session.user, ?session.language and ?session.currency work, and that named values can carry objects whose attributes you can then reach. Both are still true, and both are more general than they looked.

First, there is no whitelist of session values

?session.X is simply a lookup of attribute X in the session context. Anything in there works — the documented three, but also, for example:

?session.catalogversions     -- expands to a list of bind values
?session.user.name

and any attribute your own code has put into the session.

The converse also holds, and it is worth knowing before you guess at a name: a value that is not in the session fails at translation time, even if it sounds like it ought to be there. ?session.locale gives could not translate value expression 'session.locale'. And a name can resolve and still be unusable — ?session.timezone finds a TimeZone object and then fails in JDBC with incompatible data type in conversion. The lookup is generic; nothing curates it for you.

Second, attribute paths work for any named parameter

The traversal is not a feature of session; session just switches the lookup source. For every other name, the source is the query’s own parameter map:

// illustrative
Object resolve(String key, Map<String,Object> values, SessionContext ctx) {
    String[] parts = key.split("\\.");
    boolean fromSession = "session".equalsIgnoreCase(parts[0]);
    StringBuilder name = new StringBuilder();
    for (int i = fromSession ? 1 : 0; i < parts.length; i++) {
        if (name.length() > 0) name.append('.');
        name.append(parts[i]);
        Object o = fromSession ? ctx.getAttribute(name.toString())
                               : values.get(name.toString());
        if (o != null) {
            return (i + 1 < parts.length) ? walkAttributes(o, parts, i, ctx) : o;
        }
    }
    throw new FlexibleSearchException("could not translate value expression '" + key + "'");
}

Illustrative reconstruction, not platform source. See the note above.

Example — the one from 2016, still valid:

String queryStr = "SELECT {code}, ?language.isocode FROM {Product} ORDER BY {name}";
FlexibleSearchQuery query = new FlexibleSearchQuery(queryStr);
query.addQueryParameter("language", currentLanguageModel);
query.setResultClassList(Arrays.asList(String.class, String.class));
SearchResult<List> result = fss.search(query);

Example — arbitrary depth, mixing session and attributes:

SELECT {o:code}
FROM   {Order AS o}
WHERE  {o:deliveryAddress}        = ?session.user.defaultShipmentAddress
  AND  {o:currency}               = ?session.currency

Each segment is an attribute read with Jalo permission checks and full lazy loading, so a long path can cost you several round trips to the database per query. That is the real reason to use it sparingly — not correctness, but the hidden I/O.

Note also how the name is assembled incrementally: a, then a.b, then a.b.c. A parameter whose name itself contains dots will therefore still resolve.

Positional parameters exist as well: if the character after ? is a digit, it is read as a number, so ?1, ?2 work. Parameter names may contain letters, digits, _, - and ..

Third, there is an interesting behavior with null attributes

If the final attribute in a path is null or an empty collection, the result is not null but a typed wrapper carrying the attribute’s Java class. Strict databases reject an untyped setObject(null), and this is how the engine avoids that. The class is public, so you can supply one yourself — it is the only correct way to bind a typed null into a FlexibleSearch query.

The caching issue, ten years on

The 2016 note said: the platform query cache does not look at your SQL, so a query using NOW() will be served from cache and can return stale rows.

That is still exactly true. The cache key is built from the translated SQL template, the bind values, the language, the row range, the result classes and the per-type generation counters. Nothing in it inspects the text of the query for non-deterministic functions.

SELECT {pk} FROM {Promotion}
WHERE  {startDate} <= NOW() AND {endDate} >= NOW()

This gets cached like anything else, and stays cached until someone modifies a Promotion.

I verified this directly: SELECT CURRENT_TIMESTAMP FROM {Language} WHERE {isocode} = 'en' returns the same timestamp on every call until something invalidates the Language type.

What has changed is that there are now three TTL mechanisms. It is tempting to read them as “maximum age of a cached result” — I did, in the first draft of this article — and that is wrong for two of the three.

Per-session TTL and query categories: a staleness window, not an expiry

A session attribute, in seconds:

sessionService.setAttribute("TTLForUnitInCache", Integer.valueOf(60));
try {
    return flexibleSearchService.search(query);
} finally {
    sessionService.removeAttribute("TTLForUnitInCache");
}

Or tag the query with a category in code, and configure the policy outside it:

QueryOptions options = QueryOptions.newBuilder()
        .withQuery("SELECT {pk} FROM {Promotion} WHERE {startDate} <= NOW() AND {endDate} >= NOW()")
        .withHints(List.of(FlexibleSearchHints.categorizedQuery("promotions")))
        .build();

flexiblesearch.categorizedQuery.promotions.ttl=60
flexiblesearch.categorizedQuery.promotions.useReadOnlyDataSource=true

If several category hints are attached, the smallest TTL wins.

Neither of these makes a NOW() query fresh. With TTLForUnitInCache = 2, and separately with a category TTL of 2 seconds, the CURRENT_TIMESTAMP query above kept returning the same value after 3, 5, 7 and 9 seconds. The TTL changes the cache key (the first call with it is a miss), but it does not make the entry expire.

The platform's own test class, FlexibleSearchTTLTest, explains why. Its method names alone spell out the semantics:

shouldBeActualWhenCacheKeyHasTTLThatIsReachedAndGenerationsHasNotChanged
shouldBeActualWhenCacheKeyHasTTLNotReachedAndAnyGenerationHasChanged
shouldBeNotActualWhenCacheKeyHasTTLReachedAndGenerationsHasChanged

An entry with a TTL is invalidated only when the TTL has passed and the data has changed. The TTL is how long you are willing to keep serving a stale result after a modification — it relaxes invalidation, it does not add expiry. That is useful for hot queries over frequently-written types, and useless for queries whose result depends on the clock.

The category mechanism is still worth having for a different reason: it lets you move a whole class of queries to a read replica with one property.

Cache domains, per data source

flexiblesearch.datasource.myReplica.cacheDomain=replicaDomain
flexiblesearch.cacheDomain.replicaDomain.ttl=30

The domain becomes part of the cache key, so replica results never mix with master results. But note what happens when a domain has a TTL configured: the engine switches to a cache key whose generation check is hard-wired to “not current”, and whose validity is time only.

// illustrative
class TypeGenerationIgnoringKey extends FlexibleSearchCacheKey {
    @Override boolean isGenerationCurrent(FlexibleSearchCacheKey other) { return false; }
    @Override public boolean isActual(Map<String,Long> generations) {
        return hasTtl() ? validUntil > now() : super.isActual(generations);
    }
}

Illustrative reconstruction, not platform source. See the note above.

Data-change invalidation is ignored entirely for that domain. For a lagging read replica that is arguably the honest behavior — invalidation would outrun replication anyway — but it should be a deliberate choice, not a surprise.

This is the only one of the three mechanisms that gives a NOW() query a real maximum age (the test class has shouldBeNotActualWhenIgnoreGenerationCacheKeyHasTTLReached for it), and it is the one you cannot switch on casually, because it only exists for a configured data source — I could not try it end to end for that reason. On the master data source the 2016 advice still stands: keep the clock out of the query text — pass the timestamp as a parameter, rounded to whatever precision you can tolerate.

What actually turns the cache off

Only three things:

Two corrections to the 2016 note

Subqueries are cacheable. I wrote in 2016 that subqueries are not cached by either layer. On a current installation that is not the case: there is no structural check anywhere in the caching path, and the types referenced inside a {{ … }} subquery are collected into the dependency set along with the outer ones, so the result is cached and invalidated correctly. (What is skipped — and only under Oracle’s large-IN rewrite — is the translation cache, not the result cache. Different cache, different reason.)

The MySQL half of the article is obsolete. The MySQL Query Cache was deprecated in 5.7.20 and removed in 8.0. Oracle, SQL Server and HANA never had a result cache of that kind — they cache plans, not rows. So the only result cache worth reasoning about today is the platform’s own, and the three mechanisms above are how you reason about it.

The other cache — translation — is keyed per user

Everything above is about the result cache. There is a second one, easy to forget because it almost never misbehaves: the translated SQL itself is cached, sized by cache.flexiblesearchquery (1000 entries by default).

The part worth knowing is what the key has to contain. Search restrictions are compiled into the generated SQL, and restrictions depend on who is asking — so the same query string cannot share a translation between two users with different restrictions. The key therefore includes the principal, alongside the query text, the dynamic restrictions, the failOnUnknownFields / language / restriction flags, and the unionAllTypeHierarchy setting from the uhu section above. (That last one is why flipping unionAllTypeHierarchy does not poison the cache for other sessions.)

The practical consequence is a capacity question nobody budgets for: on a storefront where many users have individual restrictions, the effective number of cache entries is queries × distinct principals, not queries. Push past 1000 and you are re-translating on every request — parsing, type lookups, restriction compilation, the lot — while the result cache above still looks perfectly healthy. If you raise the limit, note that the platform clamps it at 50,000 with a warning in the log suggesting you contact support.

Query hints: MAXDOP

On SQL Server the engine can append OPTION (MAXDOP n) to limit parallelism. Sources are checked in this order:

1. an explicit hint passed with the query (if it already carries a MAXDOP, nothing is added);
2. the session attribute flexiblesearch.hints.sqlserver.maxdop;
3. the property of the same name, default -1, meaning “do not add the hint”.

Two details that are not in the documentation:

• A value of -1 in the session means “suppress the hint entirely”, so a session can cancel a global setting, not merely override the number.
• The hint is not applied when the query runs against a read-only replica. If you capped MAXDOP to protect OLTP throughput, be aware that the cap silently lifts on the replica.

sessionService.setAttribute("flexiblesearch.hints.sqlserver.maxdop", Integer.valueOf(4));

The property is change-listener aware, so it takes effect without a restart.

Reference — the session attributes

Most of FlexibleSearch’s behavior is steered by session attributes rather than by properties. Collected in one place:

Conclusion

Ten years later the picture is remarkably stable. The field and type modifiers from 2016 are untouched. The ORDER BY marker behavior is untouched — and has quietly gained a new way to hurt you, now that values can be inlined into the statement text. The caching observation is untouched too. The platform has grown TTL settings, but two of the three bound staleness after a change rather than the age of a result, so outside a TTL-configured cache domain the 2016 workaround — keep time out of the query text, or round it — is still the one that works.

What struck me most on this pass is how much of FlexibleSearch is string manipulation rather than parsing. Clause boundaries found with indexOf. Subqueries matched by counting braces. Count queries produced with a regular expression over generated SQL. Sort order supplied out of thin air when the target database demands one. None of this is unreasonable for a component designed in the early 2000s and kept backward compatible ever since — but it explains a whole category of bugs that look inexplicable from the outside, and it is the reason the advice “always bind your values” matters more here than in most ORMs.