{"record":{"id":"19b96c345a15efe1","repo":"hibernate/hibernate-orm","slug":"hsqldb-only-supports-the-case-insensitive-flag-i","errorCode":null,"errorMessage":"HSQLDB only supports the case insensitive flag 'i' as literal.","messagePattern":"HSQLDB only supports the case insensitive flag 'i' as literal\\.","errorType":"exception","errorClass":"IllegalArgumentException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/function/HSQLRegexpLikeFunction.java","lineNumber":37,"sourceCode":" */\npublic class HSQLRegexpLikeFunction extends AbstractRegexpLikeFunction {\n\n\tpublic HSQLRegexpLikeFunction(TypeConfiguration typeConfiguration) {\n\t\tsuper( typeConfiguration );\n\t}\n\n\t@Override\n\tpublic void render(\n\t\t\tSqlAppender sqlAppender,\n\t\t\tList<? extends SqlAstNode> arguments,\n\t\t\tReturnableType<?> returnType,\n\t\t\tSqlAstTranslator<?> walker) {\n\t\tfinal boolean caseSensitive;\n\t\tif ( arguments.size() > 2 ) {\n\t\t\tif ( !(arguments.get( 2 ) instanceof Literal literal)\n\t\t\t\t|| !(literal.getLiteralValue() instanceof String flags)\n\t\t\t\t|| !flags.equals( \"i\" ) ) {\n\t\t\t\tthrow new IllegalArgumentException( \"HSQLDB only supports the case insensitive flag 'i' as literal.\" );\n\t\t\t}\n\t\t\tcaseSensitive = false;\n\t\t}\n\t\telse {\n\t\t\tcaseSensitive = true;\n\t\t}\n\n\t\tsqlAppender.appendSql( \"regexp_like(\" );\n\t\tif ( !caseSensitive ) {\n\t\t\tsqlAppender.appendSql( \"lower(\" );\n\t\t}\n\t\targuments.get( 0 ).accept( walker );\n\t\tif ( !caseSensitive ) {\n\t\t\tsqlAppender.appendSql( ')' );\n\t\t}\n\t\tsqlAppender.appendSql( ',' );\n\t\tif ( !caseSensitive ) {\n\t\t\tsqlAppender.appendSql( \"lower(\" );","sourceCodeStart":19,"sourceCodeEnd":55,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/HSQLRegexpLikeFunction.java#L19-L55","documentation":"HSQLDB's REGEXP_LIKE only supports the case-insensitive flag 'i', which Hibernate's HSQLRegexpLikeFunction emulates by wrapping both operands in lower(). The third argument must therefore be the string literal 'i'; any other flag value ('g', 'im', 's', ...) or a non-literal flags expression (parameter, concat) throws IllegalArgumentException at SQL rendering.","triggerScenarios":"HQL: regexp_like(e.col, 'pat', 'g') or regexp_like(e.col, 'pat', :flags) executed with the HSQLDB dialect.","commonSituations":"Portable test suites running HSQLDB instead of Oracle/PostgreSQL that accept richer flags; flag strings assembled at runtime; copying ORM-agnostic JPQL with flags between projects.","solutions":["Drop the third argument when only case-sensitive matching is needed","Pass exactly the literal 'i' when case-insensitive matching is wanted","Fold other flag semantics into the pattern with inline modifiers, e.g. '(?i)pat' or '(?s)pat'","Run that query as native SQL or on a dialect with full regexp_like flag support"],"exampleFix":"// before\nwhere regexp_like(e.code, :pattern, 'im')\n\n// after\nwhere regexp_like(e.code, '(?im)' || :pattern)   -- flags folded into the pattern","handlingStrategy":"validation","validationCode":"// HSQLDB: regexp_like flags must be the literal 'i'\nstatic String sanitizeHsqlFlags(String flags) {\n    if (flags == null || flags.isEmpty()) return null; // omit the third argument\n    if (!\"i\".equals(flags)) {\n        throw new IllegalArgumentException(\"HSQLDB supports only flag 'i', got: \" + flags);\n    }\n    return flags;\n}","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql).getResultList();\n} catch (IllegalArgumentException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"case insensitive flag\")) {\n        // retry with flags folded into the pattern: '(?i)' + pattern\n        return em.createQuery(foldFlagsIntoPattern(hql)).getResultList();\n    }\n    throw e;\n}","preventionTips":["Keep regexp flags as compile-time constants restricted to 'i'","Fold advanced flags into the pattern with '(?i)' style inline modifiers","Never bind flags as a query parameter on dialects that require literals"],"tags":["hibernate","hsql","regexp-like","sql-flags","dialect-limitation"],"backgroundTag":"regex-flags-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}