Чого ви досі не знаєтеПро FlexibleSearch: транслятор запитів очима 2026 року

У 2016 році я опублікував дві короткі нотатки про FlexibleSearch. Обидві стосувалися hybris 6.x. Відтоді продукт двічі перейменували, шар персистентності обзавівся цілком новим шляхом запису, а кеш запитів — трьома окремими механізмами TTL, яких тоді не було.

Тож напрошується питання: що з того досі правда?

Я знову перевірив поведінку актуальної SAP Commerce Cloud 2211. Коротка відповідь: майже все з 2016 року досі чинне — зокрема й проблема з маркерами ORDER BY, яка стала навіть трохи цікавішою, — а ще випливла низка нових речей. Довга відповідь — ця стаття, бо щойно починаєш розбиратися, що транслятор насправді робить із рядком вашого запиту, знаходиш значно більше, ніж три модифікатори.

FlexibleSearch — один із найстаріших компонентів платформи. Він старший за ServiceLayer, старший за шар моделей, і це помітно. А ще це єдиний компонент, з яким кожен проєкт стикається першого ж дня і який майже ніхто не читає уважно. Далі — та частина його поведінки, якої ви не знайдете на SAP Help Portal.

Варто застерегти: деякі недокументовані можливості не задокументовані не без причини. Це спадщина — не завжди вдалі рішення, які SAP чомусь не прибирає з коду, мабуть, задля сумісності з якимись прадавніми впровадженнями. Не документують їх не в останню чергу через те, що вони неадекватні або марні. Та знати про них однаково варто: на них можна випадково натрапити й отримати баг чи небажану поведінку.

Як SAP Commerce розбирає FS

Перше, що варто засвоїти: FlexibleSearch не має SQL-парсера. Межі речень запиту він визначає, шукаючи слова select, from, where, group by і order by у рядку запиту, зведеному до нижнього регістру, — причому кожен пошук починається там, де закінчився попередній.

// ілюстрація
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 ? ... : ...);

Ілюстративна реконструкція, а не вихідний код платформи. Див. примітку вище.

«Ціле слово» тут означає лише те, що символи з обох боків — не літери, не цифри й не підкреслення. Ні рядкових літералів, ні коментарів, ні вкладеності рушій не враховує.

Приклад. Цей запит робить не те, на що схожий:

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

Пошук order by починається після where, і перший збіг трапляється всередині літерала. Речення WHERE обрізається в цьому місці — від нього лишається {code} = ', — а me' стає реченням ORDER BY. Далі транслятор дописує свій предикат типу до обрізаного WHERE, і той опиняється всередині рядкового літерала:

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

На HSQLDB усе закінчується помилкою unexpected token : LIMIT required: ). З 'group by me' відбувається те саме. Ви покладаєтеся на те, що база даних відхилить результат; чи лишиться покалічений запит синтаксично коректним, залежить від того, що стоїть після ключового слова у вашому літералі.

Те саме стосується group by. select, from і where надійніші, бо кожне шукають від кінця попереднього, тож перший збіг майже завжди виявляється справжнім ключовим словом, — але літерал із from усередині списку вибірки однаково все зламає.

Практичне правило: усе, що може містити order by, group by чи from, передавайте як bind-параметр і ніколи не склеюйте з текстом запиту. Це й так добра практика, але тут ідеться не про ін'єкції, а про те, що запит просто не буде правильно розбито на частини.

Окремо варто відзначити один наслідок: HAVING у списку ключових слів немає. Окремим реченням його не вважають, і його поглинає текст GROUP BY. Як виявилося, це нешкідливо: вирази полів усередині нього транслюються разом із GROUP BY, зокрема з переписуванням на стовпці uhu.*, коли запит перетворюється на UNION (див. нижче). HAVING MAX({name}) IS NULL для {Principal} коректно перетворюється на HAVING MAX(uhu.groupByArg0) IS NULL.

{{ … }} зіставляють, рахуючи дужки

Підзапити видобуваються раніше за все інше: рушій проходить рядком і рахує фігурні дужки. Подвійні дужки відкривають і закривають підзапит; одинарні рахуються окремо, щоб вираз поля випадково його не завершив.

// ілюстрація
if (c == '{') {
    if (next == '{') { if (++subqueryDepth == 1) start = pos; pos++; }
    else             { singleBraceDepth++; }
} else if (c == '}') {
    if (next == '}' && singleBraceDepth == 0) { /* закрити підзапит, вставити плейсхолдер */ }
    else if (--singleBraceDepth < 0) {
        throw new FlexibleSearchException("missing '{' for '}' at " + pos);
    }
}

Ілюстративна реконструкція, а не вихідний код платформи. Див. примітку вище.

І знову: літерали не враховуються.

Приклад. Цей запит падає, навіть не дійшовши до бази даних:

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

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

Дзеркальний випадок теж падає, з протилежним повідомленням:

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

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

Зверніть увагу: тут повідомлення цитує речення WHERE, а не весь запит, і позицію відраховано всередині цього фрагмента — на той момент речення вже розділено. Жодне з повідомлень не каже, що дужка стоїть усередині рядкового літерала, тож їх легко сплутати з друкарською помилкою у виразі поля.

Вираз поля має справжню граматику — і майже вся вона не задокументована

Стаття 2016 року зупинилася на трьох літерах-модифікаторах. Насправді тут значно більше. Повна форма виразу поля:

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

По-перше, . і : взаємозамінні як роздільник типу

{Product.code} і {Product:code} — одне й те саме. Різниця в тому, що крапка працює лише як перший роздільник, а двокрапка — і як роздільник типу, і як роздільник перед опціями. Тож коректні всі ці варіанти:

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

По-друге, числовий кваліфікатор типу — це позиційний індекс, а не назва типу

Якщо кваліфікатор типу складається лише з цифр, його тлумачать як індекс у реченні FROM (відлік від 1), а не як код типу.

// ілюстрація
Object parseTypeQualifier(String s) {
    if (s == null || s.isEmpty()) return null;              // → тип за замовчуванням (перший)
    if (isAllDigits(s))           return parseInt(s) - 1;   // → позиційний індекс
    return s;                                               // → код типу або аліас
}

Ілюстративна реконструкція, а не вихідний код платформи. Див. примітку вище.

Приклад.

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

{1:code} — це код продукту, {2:version} — версія каталогу. Індекс ураховує кожен тип у реченні FROM, зокрема й типи зв'язків: у {Product AS p JOIN CategoryProductRelation AS r ON ... JOIN Category AS c ON ...} категорія — це {3:code}, а не {2:code}.

Сам рушій уважає це спадщиною — у платформі є повідомлення

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

На практиці викликати його звичайним обмеженням мені не вдалося: обмеження із запитом {1:isocode} = 'en' на Language застосувалося без заперечень, і 1 розв'язалося відносно обмежуваного типу — навіть коли Language був другим типом у реченні FROM зовнішнього запиту. Схоже, пояснення в тому, що всередині обмеження тип поля жорстко прив'язано до обмежуваного типу ще до того, як кваліфікатор узагалі розглядають, тож до позиційного пошуку — а разом із ним і до перевірки — справа просто не доходить. Щоб туди потрапити, знадобився б вкладений запит із власним FROM усередині обмеження. Хай там як: використовуйте аліаси.

Болісно б'є інше: до типу, код якого складається із самих цифр, у виразі поля ніколи не звернутися за назвою — його завжди прочитають як індекс.

По-третє, порожній кваліфікатор типу означає «тип за замовчуванням»

Щоб задати опції, потрібен роздільник типу. Але сам тип може бути порожнім:

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

Порожній кваліфікатор розв'язується в тип за замовчуванням, тобто в перший тип у реченні FROM.

І ось тут пастка. Це не те саме:

WHERE {code:o} IS NULL

Пройдімо автомат вручну: на першій двокрапці парсер ще в початковому стані, тому вирішує, що code — це тип (або аліас), а o — атрибут. Маєте

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

або, якщо в запиті випадково є аліас code, мовчки не той стовпець. Опції без явного — чи бодай порожнього — кваліфікатора типу не працюють, і жодної діагностики на цей випадок немає.

По-четверте, [any] — пошук за локалізованим атрибутом усіма мовами

Вказівка мови приймає ISO-код, але є й одне особливе значення:

// ілюстрація
PK resolveLanguage(String clause) {
    if (clause == null)              return null;        // мова сесії
    if ("any".equalsIgnoreCase(clause)) return PK.NULL_PK; // маркер: не фільтрувати за мовою
    PK byIso = languagePkForIsoCode(clause);
    return byIso != null ? byIso : PK.parse(clause);     // запасний варіант: PK мови
}

Ілюстративна реконструкція, а не вихідний код платформи. Див. примітку вище.

Приклад. Знайти продукти, у назві яких є «bike» будь-якою мовою, не знаючи, якою саме:

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

Без [any] фільтрування відбувається лише за мовою сесії. Я жодного разу не бачив згадки про [any] у документації, а це ж найчистіший спосіб шукати всіма мовами, не прописуючи вручну join із таблицею *lp. Транслятор узагалі відкидає основну таблицю й звертається безпосередньо до таблиці lp:

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

Це має побічний ефект, яким користується сама платформа. Оскільки таблиця lp тепер є справжньою таблицею в запиті з передбачуваним аліасом, можна написати умову join за цим аліасом, хоча у вашому реченні FROM цієї таблиці ніде немає. Запит на кшталт цього — його видно в SQL-журналі, поки backoffice будує свій кеш системи типів, — витягає всю матрицю «тип × мова × переклад» одним запитом:

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 ніде не оголошено. Вона існує лише тому, що {t.name[any]} змусив транслятор її згенерувати, і називається _t0 лише тому, що виявилася першою таблицею такого роду в цьому запиті. Це справді дотепний трюк і вкрай крихкий — він залежить від лічильника аліасів, а той залежить від форми решти запиту. У проєктному коді я б так не писав. Зате це дуже наочна ілюстрація головної теми статті: ви пишете не запит мовою запитів, а рядок, який переписують на SQL, і все, що видно на виході, — законна здобич.

Замість ISO-коду вказівка мови приймає й PK мови: {p:name[8796093055008]} працює. Це запасний варіант на випадок, коли значення не є відомим ISO-кодом, і саме тому друкарська помилка дає доволі заплутане повідомлення:

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

По-п'яте, [language] має стояти перед :options, а не після

Граматика вище — не порада щодо стилю: порядок перевіряється примусово. Вказівка мови приймається лише доти, доки читається атрибут, тож щойно друга двокрапка відкрила опції, [ — це синтаксична помилка:

{Product:name[en]:o}   -- працює
{Product:name:o[en]}   -- wrong search field definiton ... found '[' outside attribute spec

Зверніть увагу на написання в цьому повідомленні. definiton без i — саме те, що видає платформа: корисно знати, коли шукаєте його grep'ом у логах, і заразом нагадування про те, який старий цей код.

По-шосте, є опції c / l / o

Це ті самі три опції з 2016 року, і поводяться вони так само:

// ілюстрація
String flags = options.toUpperCase();
this.core      = flags.indexOf('C') > -1;
this.localized = flags.indexOf('L') > -1 || customLanguagePk != null;
this.optional  = flags.indexOf('O') > -1;

Ілюстративна реконструкція, а не вихідний код платформи. Див. примітку вище.

Ця реалізація має дві властивості, які варто назвати явно, бо в них легко помилитися:

• Регістр не має значення. :o, :O, :oL — усе працює.
• Будь-який інший символ мовчки ігнорується. :xyz — не помилка, просто нічого не вмикається. Друкарська помилка в опції не дає взагалі жодної діагностики.

Найкориснішою з трьох досі лишається o. Локалізовані атрибути зберігаються в окремій таблиці *lp, яку за замовчуванням приєднують внутрішнім join, — а отже, рядки, що не мають запису в lp для потрібної мови, повністю зникають із результату.

Приклад. Продукти без назви поточною мовою:

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

Без :o ви отримаєте лише продукти, які мають рядок lp для цієї мови, із NULL у ньому. Продукти зовсім без рядка lp — зазвичай їх більшість — відсіюються ще на внутрішньому join. На моїй тестовій системі різниця становила 331 рядок проти 102 366:

-- без :o — таблиці products у запиті навіть немає
SELECT COUNT(*) FROM productslp lp_t0
WHERE lp_t0.LANGPK = ? AND lp_t0.p_name IS NULL AND lp_t0.ITEMTYPEPK IN (...)

-- з :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 (...)

Це найпоширеніша плутанина на кшталт «FlexibleSearch повертає менше рядків, ніж є в базі», і виправляють її одним символом.

По-сьоме, {pk} і {itemtype} розв'язуються пізно

Обидва обробляються в особливий спосіб і розв'язуються під час другого проходу, уже після розподілу між таблицями. На який стовпець вони перетворяться, залежить від того, до якої таблиці потрапив вираз поля:

// ілюстрація
if ("pk".equals(name))
    return alias + (table.isMainTable() ? ".PK" : ".ITEMPK");
if ("itemtype".equals(name))
    return alias + (table.isMainTable() ? ".TypePkString" : ".ITEMTYPEPK");

Ілюстративна реконструкція, а не вихідний код платформи. Див. примітку вище.

Ось чому в складному запиті з локалізованими атрибутами чи динамічними властивостями {pk} іноді розв'язується відносно не тієї таблиці, на яку ви розраховували.

На запитання «яка таблиця» є більше відповідей, ніж звичні дві. Тип може додати до запиту чотири таблиці, і транслятор підставляє їх на місце плейсхолдерів, складаючи SQL: основну таблицю (products), локалізовану (productslp), таблицю властивостей для атрибутів, що зберігаються як динамічні властивості, і локалізовану таблицю властивостей для атрибутів, які водночас динамічні й локалізовані. Більшість обговорень FlexibleSearch згадує лише перші дві, і це нормально — доки запит не зачепить динамічний атрибут і {pk} не потрапить до таблиці, де стовпець PK називається ITEMPK.

Модифікатори типу: !, * і той, про який ніхто не згадує, — ^

Стаття 2016 року описувала ! і *. Є й третій, а семантику ! варто зафіксувати точно, бо її дуже часто запам'ятовують навпаки.

Для ! вирішальний доказ — помилка, яку ви отримаєте, застосувавши його до абстрактного типу:

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

Це повідомлення має сенс лише в одному напрямку: ! виключає підтипи, тож в абстрактного типу з ! шукати просто нічого. Якби ! додавав підтипи, пошук за абстрактним типом із ним був би якраз тим єдиним випадком, що працює.

Модифікатор — не суфікс, і три модифікатори мають ієрархію

{Product!} природно читати як «код типу, а за ним модифікатор». Але знаходять його не так. Кожен із трьох символів шукають у виразі типу, а кодом типу вважають усе, що стоїть перед першим знайденим. Звідси два наслідки, обидва відтворювані.

Вони мають фіксований пріоритет, хоч у якому порядку ви їх напишете. Спершу шукають !, потім *, потім ^. Тож і {Product*!}, і {Product!*} означають !, а {Product^*} означає * — до ^ справа не доходить. Два модифікатори поспіль — очевидна помилка, але мовчазна.

Модифікатор розпізнається й посеред коду. {Pro*duct} шукає не Product із модифікатором, а тип Pro, якого майже напевно немає. У помилці буде обрізаний код — це бодай зрозуміла підказка.

І пастка, яку варто запам'ятати:

{Product AS p!}   -- НЕ «без підтипів»

Пошук модифікатора охоплює лише частину виразу до AS. Якщо написати ! після аліаса, він просто стане частиною аліаса — тип, як завжди, береться разом із підтипами, а ваш аліас тепер p!, тож жодне {p:...} у запиті не розв'яжеться. Запит ламається, але в іншому місці — з повідомленням про невідомий аліас, а не про модифікатор, який ви, як вам здавалося, написали.

* вимикає пошукові обмеження

Ця частина заслуговує на окремий пункт у security review, а не на місце серед курйозів.

// ілюстрація
if (!query.restrictionsDisabled() && !this.typeChecksDisabled()) {
    restrictions = loadQueryFilters(query, this.getCode(), includeSubtypes);
} else {
    restrictions = Collections.emptyList();   // ← сюди потрапляє '*'
}

Ілюстративна реконструкція, а не вихідний код платформи. Див. примітку вище.

Модифікатор * вмикає той самий внутрішній прапорець, що вимикає перевірку типу, а цей прапорець заразом пропускає завантаження обмежень. Тож {Product*} робить не одну, а дві речі:

1. прибирає предикат TypePkString і повертає всі рядки таблиці — зокрема й рядки, що належать іншим типам із тим самим деплойментом;
2. відкидає всі пошукові обмеження, визначені для цього типу.

Приклад. На B2B-вітрині з урахуванням каталогів ці два запити повертають користувачеві з обмеженнями різні набори продуктів:

SELECT {pk} FROM {Product}   -- обмеження застосовуються
SELECT {pk} FROM {Product*}  -- обмеження оминаються

Якщо пошукові обмеження — ваш механізм контролю доступу (а в сценаріях із каталогами й B2B так зазвичай і є), то * функціонально рівнозначний виклику setDisableSearchRestrictions(true), з тією різницею, що це один символ, захований у рядковому літералі й непомітний для будь-кого, хто лише пробігає код очима.

Варто додати до чек-листа рев'ю:

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

Цей grep знайде те, що написала ваша команда. Цікавішого випадку він не знайде: платформа робить це сама із собою. Шар Integration API / OData знаходить елементи, генеруючи запити такого вигляду

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

— і * тут не опція, яку хтось обрав для особливого випадку, а режим ієрархії елементів за замовчуванням для цієї підсистеми. Зіставлення типу елемента можна налаштувати (є ще режим «лише цей тип», що відповідає !, і режим «тип і підтипи», якому не відповідає жоден модифікатор), але якщо його ніхто не задав, пошук в інтеграціях виконується з вимкненими перевіркою типу та пошуковими обмеженнями.

Чи це проблема, цілком залежить від того, як захищено ваші інтеграційні ендпоінти. Якщо ви вважали, що пошукові обмеження діють однаково всюди, бо їх забезпечує шар персистентності, — ось контрприклад: їх забезпечує транслятор, і сторона, яка генерує текст запиту, сама вирішує, чи їх задіяти.

Є ще два глобальні рубильники для обмежень, так само малопомітні:

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

Обидва зчитуються один раз і мемоїзуються, тож без перезапуску їхня зміна надійно не застосовується.

^ як спосіб уникнути UNION

^ залишає підтипи, що зберігаються в тій самій таблиці, і відкидає ті, що мають власний деплоймент. Це саме той важіль, який потрібен, коли тип має непотрібні вам підтипи, а UNION, якого вони вимагають, нищить план виконання, — і це підводить нас до наступного розділу.

Добрий приклад — {Media} на системі з установленим rule engine: він об'єднує medias із droolskiemodulemedia, тоді як {Media^} читає лише medias. Але для типу без власної таблиці ^ нічого не дає. {AbstractOrder^} так само об'єднує carts, orders і quotes, а {Principal^} — usergroups і users: «тієї самої таблиці», у якій можна було б лишитися, просто немає.

Звідки береться uhu

Коли підтипи типу зберігаються в різних таблицях, одного SELECT замало. Рушій генерує окремий SELECT для кожної таблиці й об'єднує їх. Якщо в запиті є ще й GROUP BY, ORDER BY чи агрегат, усе об'єднання загортається в зовнішній select — із жорстко прописаним аліасом:

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

Якщо ви колись натрапляли на uhu в журналі повільних запитів і гадали, який розробник його там залишив, — це платформа. (Німецькою «пугач». Платформу створювали в Мюнхені.)

До речі, за замовчуванням використовується UNION ALL, і змінити це можна лише з коду

// ілюстрація
boolean unionAll() {
    Object v = currentSession().getAttribute("unionAllTypeHierarchy");
    return (v instanceof Boolean) ? (Boolean) v : true;   // за замовчуванням: UNION ALL
}

Ілюстративна реконструкція, а не вихідний код платформи. Див. примітку вище.

Зверніть увагу: це атрибут сесії, а не конфігураційна властивість. За замовчуванням — UNION ALL, і це правильний вибір: PK з різних таблиць не можуть збігтися, тож дедуплікація була б суто витратами.

Але цей вибір стає хибним, щойно ви вибираєте щось інше, ніж PK:

SELECT {code} FROM {Product}

на ієрархії з кількома деплойментами може повернути дублікати кодів. Якщо потрібна дедуплікація:

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

Прапорець входить до ключа кешу трансляції, тож його перемикання не отруює кеш для інших сесій.

Маркери [-- / --] досі на місці, і тепер вони можуть з'їсти ваші дані

Цим розділом завершувалася стаття 2016 року, і саме цю знахідку я найбільше сподівався побачити виправленою. Її не виправили.

Рушій загортає ORDER BY верхнього рівня в маркери, щоб згодом отримати count-запит, вирізавши сортування:

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

Ці маркери обробляються двома різними операціями:

// ілюстрація
private static final Pattern MARKERS = Pattern.compile("(\\[--)|(--\\])");
private static final Pattern CLAUSES = Pattern.compile("\\[--(.*?)--\\]", Pattern.DOTALL);

String getSql()      { return MARKERS.matcher(sql).replaceAll(""); }  // прибрати лише маркери
String getCountSql() { return CLAUSES.matcher(sql).replaceAll(""); }  // прибрати ORDER BY повністю

Ілюстративна реконструкція, а не вихідний код платформи. Див. примітку вище.

Обидві — replaceAll. Не «замінити пару, яку вставив рушій», а кожне входження будь-де в запиті. Точнісінько як у 2016 році, усі ці запити поводяться однаково:

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[----][--'

Усі вони шукають мову з isocode ru.

До початкової знахідки варто додати три речі:

Count-запит має більший радіус ураження, ніж основний. getCountSql() видаляє все між [-- і --], нежадібно й з увімкненим DOTALL, — тож переведення рядків його не зупиняють. Запит, у якому обидва маркери йдуть у такому порядку, втрачає весь фрагмент між ними, зокрема й легітимний ORDER BY, якщо той опинився всередині. Якщо передати функції вирізання маркерів безпосередньо такий рядок

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

основний запит вийде цілком нормальним (... a = 'ru' AND b = ? order by x), а count-запит — ось таким:

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

Усе від [-- усередині літерала до справжнього закривального маркера зникло, разом із bind-параметром.

Вирізання відбувається після підставлення значень. Маркери вирізаються з остаточного тексту запиту, а отже, якщо значення вбудовано просто в SQL, а не передано через bind (див. розділ про ліміт bind-параметрів нижче — на SQL Server це стається за замовчуванням, коли перевищено поріг), то під удар потрапляють дані користувачів. Значення "--]" мовчки вирізається з рядка ще до того, як дійде до бази. "-- ]" — гаразд; "--]" — ні.

Параметри count-запиту відтинаються з кінця. Bind-значення для ORDER BY прибираються відтинанням кінця списку значень:

// ілюстрація
int cut = values.size() - orderByClause.getParameterCount();
countValues = values.subList(0, cut);

Ілюстративна реконструкція, а не вихідний код платформи. Див. примітку вище.

Для згенерованого SQL це припущення справджується. Воно перестає справджуватися, щойно ви самі постобробляєте запит і змінюєте порядок параметрів.

Приклад практичного ризику. Пошук продуктів, що дає змогу фільтрувати за SKU й передає введене значення без змін, для користувача, який набрав --], тихо виконає не той запит, який ви написали. У журналі застосунку цього не буде видно, бо на момент логування SQL уже вичищено.

Пагінацію реалізовано сім разів, і одна з реалізацій переписує ваше сортування

Для кожної бази даних є своя стратегія пагінації, яку обирають за назвою БД: HSQLDB, Oracle, MySQL, HANA, SQL Server, PostgreSQL — і запасний варіант.

Запасний варіант — це не пагінація. Він повертає початковий запит без змін і повідомляє, що рушій не підтримує лімітів на рівні БД, — тобто вибирається весь результат, а сторінка вирізається вже в Java. Сюди ж ви потрапляєте, встановивши:

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

Це значення зчитується і як однойменний атрибут сесії.

Oracle отримує класичну форму з подвійним вкладенням і rownum:

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

Найцікавіший — SQL Server. OFFSET … FETCH допустимий лише після ORDER BY. Якщо у вашому запиті його немає, рушій додає його сам:

// ілюстрація
if (!orderByInfo.exists()) {
    return sql + " ORDER BY 1 OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
}

Ілюстративна реконструкція, а не вихідний код платформи. Див. примітку вище.

ORDER BY 1 — сортування за першим стовпцем списку вибірки.

Приклад. Цей запит із пагінацією:

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

на SQL Server повертає другу сторінку, відсортовану за code, а на Oracle — другу сторінку в тому порядку, у якому рушій видав рядки. Той самий код, ті самі дані, різні рядки. Якщо перший стовпець вибірки — {pk}, ви отримаєте сортування за PK, що для PK на лічильниках приблизно відповідає порядку створення, — тож баг «працює» й лишається прихованим аж до дня, коли хтось мігрує базу даних.

Суміжний граничний випадок: SQL Server із count == 0 повертається до побудовника без пагінації, бо FETCH NEXT 0 ROWS неприпустимий.

А в HANA є конфігураційний прапорець, назва якого все каже про його історію:

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

Два різні способи обійти ліміт bind-параметрів

Бази даних обмежують кількість bind-параметрів в одному запиті. Платформа обходить це двома не пов'язаними між собою способами, і корисно знати, який із них спрацьовує.

Oracle: колекція перетворюється на CTE. Коли параметр-колекція перевищує db.supported.params.limit (за замовчуванням 1000), фрагмент IN (?param) переписується на таблицю WITH, зібрану з UNION запитів 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 )

Зверніть увагу: значення тут — рядкові літерали, а не bind-змінні, тож список із 5000 елементів дає кілька сотень кілобайтів унікального SQL-тексту й унікальний запис у shared pool. Такий запит до того ж повністю оминає кеш трансляції й заново розбирається під час кожного виклику.

У всіх інших випадках параметри вбудовуються в текст. Понад налаштований поріг bind-значення підставляються просто в текст запиту:

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

Цей рядок є в конфігурації за замовчуванням. На SQL Server запит, що несе понад 2000 bind-значень, переписується з літералами — причому на SQL Server вбудовуються й рядки, з подвоєнням лапок:

// ілюстрація
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);
}

Ілюстративна реконструкція, а не вихідний код платформи. Див. примітку вище.

Екранування коректне. Практичний наслідок стосується не безпеки, а кешу планів: кожен такий запит текстуально унікальний. Масові операції над великими списками PK — синхронізація каталогу, перерахунок цін, перебудова індексів — заповнять кеш планів SQL Server одноразовими планами.

Симптом, який варто шукати grep'ом у журналі застосунку:

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

і, якщо вбудовування не допомогло:

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

Тут знову виринають маркери [-- / --]: щойно значення вбудовано, ці значення стають частиною тексту запиту, і вирізання [-- / --] застосовується й до них.

Іменовані параметри сягають глибше, ніж ?session.user

У статті 2016 року зазначалося, що ?session.user, ?session.language і ?session.currency працюють і що іменовані значення можуть нести об'єкти, до атрибутів яких потім можна дістатися. Обидва твердження досі правдиві, і обидва ширші, ніж здавалося.

По-перше, білого списку значень сесії немає

?session.X — це просто пошук атрибута X у контексті сесії. Працює все, що там є, — задокументовані три, але й, наприклад:

?session.catalogversions     -- розгортається в список bind-значень
?session.user.name

і будь-який атрибут, який поклав у сесію ваш власний код.

Справджується й зворотне, і це варто знати, перш ніж угадувати назву: значення, якого в сесії немає, падає на етапі трансляції, навіть якщо з вигляду здається, що воно там має бути. ?session.locale дає could not translate value expression 'session.locale'. А назва може розв'язатися й однаково виявитися непридатною: ?session.timezone знаходить об'єкт TimeZone, а потім падає в JDBC з incompatible data type in conversion. Пошук універсальний, і ніхто не відбирає цей список за вас.

По-друге, шляхи атрибутів працюють для будь-якого іменованого параметра

Обхід атрибутів — не особливість session; session лише перемикає джерело пошуку. Для будь-якої іншої назви джерело — власна мапа параметрів запиту:

// ілюстрація
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 + "'");
}

Ілюстративна реконструкція, а не вихідний код платформи. Див. примітку вище.

Приклад — той самий, що у 2016 році, досі робочий:

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);

Приклад — довільна глибина, сесія впереміш з атрибутами:

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

Кожен сегмент — це читання атрибута з перевіркою прав Jalo та повним лінивим завантаженням, тож довгий шлях може коштувати кількох звернень до бази даних на кожен запит. Ось справжня причина вдаватися до цього помірно — не коректність, а приховане введення-виведення.

Зверніть увагу й на те, що назву збирають поступово: a, потім a.b, потім a.b.c. Тому параметр, у самій назві якого є крапки, однаково розв'яжеться.

Є й позиційні параметри: якщо символ після ? — цифра, його читають як число, тож ?1, ?2 працюють. Назви параметрів можуть містити літери, цифри, _, - і ..

По-третє, цікава поведінка з null-атрибутами

Якщо останній атрибут у шляху дорівнює null або порожній колекції, результатом буде не null, а типізована обгортка, що несе Java-клас атрибута. Суворі бази даних відхиляють нетипізований setObject(null), і так рушій цього уникає. Клас публічний, тож ви можете передати таку обгортку й самі — це єдиний коректний спосіб прив'язати типізований null у запиті FlexibleSearch.

Проблема з кешуванням десять років по тому

Нотатка 2016 року стверджувала: кеш запитів платформи не дивиться на ваш SQL, тому запит із NOW() віддаватиметься з кешу й може повернути застарілі рядки.

Це досі цілковита правда. Ключ кешу будується з трансльованого SQL-шаблону, bind-значень, мови, діапазону рядків, класів результату й лічильників поколінь для кожного типу. Ніщо в ньому не перевіряє текст запиту на недетерміновані функції.

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

Це кешується, як і все інше, і лишається в кеші, доки хтось не змінить Promotion.

Я перевірив це безпосередньо: SELECT CURRENT_TIMESTAMP FROM {Language} WHERE {isocode} = 'en' повертає ту саму мітку часу під час кожного виклику, доки щось не інвалідує тип Language.

Змінилося інше: тепер є три механізми TTL. Їх кортить прочитати як «максимальний вік закешованого результату» — я так і зробив у першій чернетці цієї статті, — і для двох із трьох це хибно.

TTL на рівні сесії та категорії запитів: вікно застарівання, а не термін дії

Атрибут сесії, у секундах:

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

Або позначте запит категорією в коді, а політику налаштуйте поза ним:

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

Якщо до запиту прикріплено кілька хінтів категорій, перемагає найменший TTL.

Жоден із цих способів не робить запит із NOW() свіжим. З TTLForUnitInCache = 2, а окремо — з TTL категорії у 2 секунди, наведений вище запит із CURRENT_TIMESTAMP і далі повертав те саме значення через 3, 5, 7 і 9 секунд. TTL змінює ключ кешу (перший виклик із ним — промах), але не обмежує час життя запису.

Чому так, пояснює власний тестовий клас платформи, FlexibleSearchTTLTest. Самі лише назви його методів розкривають семантику:

shouldBeActualWhenCacheKeyHasTTLThatIsReachedAndGenerationsHasNotChanged
shouldBeActualWhenCacheKeyHasTTLNotReachedAndAnyGenerationHasChanged
shouldBeNotActualWhenCacheKeyHasTTLReachedAndGenerationsHasChanged

Запис із TTL інвалідується, лише коли TTL минув і дані змінилися. TTL — це те, скільки часу ви готові віддавати застарілий результат після зміни: він послаблює інвалідацію, а не додає терміну дії. Це корисно для гарячих запитів до типів, у які часто пишуть, і марно для запитів, результат яких залежить від годинника.

Механізм категорій усе одно варто мати, але з іншої причини: він дає змогу однією властивістю перевести цілий клас запитів на репліку для читання.

Домени кешу для кожного джерела даних

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

Домен стає частиною ключа кешу, тож результати з репліки ніколи не змішуються з результатами з мастера. Але зверніть увагу, що відбувається, коли для домену налаштовано TTL: рушій перемикається на ключ кешу, у якого перевірку поколінь жорстко зашито в «неактуально», а чинність визначається лише часом.

// ілюстрація
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);
    }
}

Ілюстративна реконструкція, а не вихідний код платформи. Див. примітку вище.

Інвалідацію через зміну даних для такого домену повністю ігноровано. Для репліки, що відстає, це, мабуть, чесна поведінка — інвалідація однаково випереджала б реплікацію, — але це має бути свідомий вибір, а не несподіванка.

Це єдиний із трьох механізмів, що дає запиту з NOW() справжній максимальний вік (у тестовому класі для цього є shouldBeNotActualWhenIgnoreGenerationCacheKeyHasTTLReached), і саме його не ввімкнеш мимохідь, бо він існує лише для налаштованого джерела даних, — з тієї ж причини я не зміг перевірити його від початку до кінця. На мастер-джерелі досі чинна порада 2016 року: тримайте годинник поза текстом запиту — передавайте мітку часу параметром, округлену до тієї точності, яку ви можете собі дозволити.

Що насправді вимикає кеш

Лише три речі:

Дві поправки до нотатки 2016 року

Підзапити кешуються. У 2016 році я писав, що підзапити не кешує жоден із шарів. На актуальній інсталяції це не так: ніде на шляху кешування немає структурної перевірки, а типи, згадані всередині підзапиту {{ … }}, потрапляють до набору залежностей разом із зовнішніми, тож результат і кешується, і коректно інвалідується. (Що справді пропускається — і лише під час переписування великих IN для Oracle, — то це кеш трансляції, а не кеш результатів. Інший кеш, інша причина.)

Та половина статті, що стосувалася MySQL, застаріла. MySQL Query Cache оголосили застарілим у 5.7.20 і вилучили в 8.0. Oracle, SQL Server і HANA такого кешу результатів ніколи не мали — вони кешують плани, а не рядки. Тож єдиний кеш результатів, про який сьогодні варто міркувати, — власний кеш платформи, і три описані вище механізми — це і є спосіб про нього міркувати.

Інший кеш — кеш трансляції — прив'язаний до користувача

Усе сказане вище стосується кешу результатів. Є й другий, про який легко забути, бо він майже ніколи не поводиться дивно: кешується й сам трансльований SQL, а розмір цього кешу задає cache.flexiblesearchquery (за замовчуванням 1000 записів).

Варто знати, що має входити до його ключа. Пошукові обмеження компілюються просто в згенерований SQL, а обмеження залежать від того, хто питає, — тож той самий рядок запиту не може ділити трансляцію між двома користувачами з різними обмеженнями. Тому ключ містить principal, а разом із ним текст запиту, динамічні обмеження, прапорці failOnUnknownFields / мови / обмежень і налаштування unionAllTypeHierarchy з розділу про uhu вище. (Саме через останнє перемикання unionAllTypeHierarchy не отруює кеш для інших сесій.)

Практичний наслідок — питання місткості, яке ніхто не закладає в бюджет: на вітрині, де багато користувачів мають індивідуальні обмеження, ефективна кількість записів кешу дорівнює запити × унікальні principal'и, а не запити. Перевалите за 1000 — і ви знову транслюєте запит під час кожного звернення (розбір, пошук типів, компіляція обмежень, усе разом), тоді як кеш результатів вище й далі має цілком здоровий вигляд. Якщо підніматимете ліміт, зважте, що платформа обмежує його значенням 50 000 і пише в лог попередження з порадою звернутися до підтримки.

Хінти запитів: MAXDOP

На SQL Server рушій може дописувати OPTION (MAXDOP n), щоб обмежити паралелізм. Джерела перевіряються в такому порядку:

1. явний хінт, переданий разом із запитом (якщо в ньому вже є MAXDOP, нічого не додається);
2. атрибут сесії flexiblesearch.hints.sqlserver.maxdop;
3. однойменна властивість, за замовчуванням -1, що означає «не додавати хінт».

Дві деталі, яких немає в документації:

• Значення -1 в сесії означає «повністю придушити хінт», тож сесія може скасувати глобальне налаштування, а не просто перевизначити число.
• Хінт не застосовується, коли запит виконується на репліці лише для читання. Якщо ви обмежили MAXDOP, щоб захистити пропускну здатність OLTP, майте на увазі, що на репліці це обмеження мовчки знімається.

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

За властивістю стежить change listener, тож зміна набирає чинності без перезапуску.

Довідник — атрибути сесії

Більшістю поведінки FlexibleSearch керують атрибути сесії, а не властивості. Ось вони в одному місці:

Висновок

Десять років по тому картина напрочуд стабільна. Модифікатори полів і типів із 2016 року ніхто не чіпав. Поведінку маркерів ORDER BY ніхто не чіпав — і вона непомітно здобула новий спосіб нашкодити, тепер, коли значення можуть вбудовуватися в текст запиту. Спостереження про кешування теж досі слушне. Платформа отримала налаштування TTL, але два з трьох обмежують застарівання після зміни, а не вік результату, тож поза доменом кешу з налаштованим TTL обхідний шлях 2016 року — тримати час поза текстом запиту або округлювати його — досі єдиний робочий.

Найбільше мене цього разу вразило те, наскільки FlexibleSearch — це маніпуляції з рядками, а не синтаксичний розбір. Межі речень шукають через indexOf. Підзапити зіставляють, рахуючи дужки. Count-запити отримують регулярним виразом поверх згенерованого SQL. Порядок сортування беруть зі стелі, коли цього вимагає цільова база даних. Ніщо з цього не є нерозумним для компонента, спроєктованого на початку 2000-х і відтоді зворотно сумісного, — але це пояснює цілу категорію багів, які ззовні здаються незбагненними, і саме тому порада «завжди передавайте значення через bind» тут важить більше, ніж у більшості ORM.