hibernate/hibernate-orm · error · IllegalArgumentException

HSQLDB only supports the case insensitive flag 'i' as litera

Error message

HSQLDB only supports the case insensitive flag 'i' as literal.

What it means

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.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/function/HSQLRegexpLikeFunction.java:37

 */
public class HSQLRegexpLikeFunction extends AbstractRegexpLikeFunction {

	public HSQLRegexpLikeFunction(TypeConfiguration typeConfiguration) {
		super( typeConfiguration );
	}

	@Override
	public void render(
			SqlAppender sqlAppender,
			List<? extends SqlAstNode> arguments,
			ReturnableType<?> returnType,
			SqlAstTranslator<?> walker) {
		final boolean caseSensitive;
		if ( arguments.size() > 2 ) {
			if ( !(arguments.get( 2 ) instanceof Literal literal)
				|| !(literal.getLiteralValue() instanceof String flags)
				|| !flags.equals( "i" ) ) {
				throw new IllegalArgumentException( "HSQLDB only supports the case insensitive flag 'i' as literal." );
			}
			caseSensitive = false;
		}
		else {
			caseSensitive = true;
		}

		sqlAppender.appendSql( "regexp_like(" );
		if ( !caseSensitive ) {
			sqlAppender.appendSql( "lower(" );
		}
		arguments.get( 0 ).accept( walker );
		if ( !caseSensitive ) {
			sqlAppender.appendSql( ')' );
		}
		sqlAppender.appendSql( ',' );
		if ( !caseSensitive ) {
			sqlAppender.appendSql( "lower(" );

View on GitHub (pinned to fad1729dce)

Solutions

1. Drop the third argument when only case-sensitive matching is needed
2. Pass exactly the literal 'i' when case-insensitive matching is wanted
3. Fold other flag semantics into the pattern with inline modifiers, e.g. '(?i)pat' or '(?s)pat'
4. Run that query as native SQL or on a dialect with full regexp_like flag support

Example fix

// before
where regexp_like(e.code, :pattern, 'im')

// after
where regexp_like(e.code, '(?im)' || :pattern)   -- flags folded into the pattern

// HSQLDB: regexp_like flags must be the literal 'i'
static String sanitizeHsqlFlags(String flags) {
    if (flags == null || flags.isEmpty()) return null; // omit the third argument
    if (!"i".equals(flags)) {
        throw new IllegalArgumentException("HSQLDB supports only flag 'i', got: " + flags);
    }
    return flags;
}

try {
    return em.createQuery(hql).getResultList();
} catch (IllegalArgumentException e) {
    if (e.getMessage() != null && e.getMessage().contains("case insensitive flag")) {
        // retry with flags folded into the pattern: '(?i)' + pattern
        return em.createQuery(foldFlagsIntoPattern(hql)).getResultList();
    }
    throw e;
}

When it happens

Trigger: HQL: regexp_like(e.col, 'pat', 'g') or regexp_like(e.col, 'pat', :flags) executed with the HSQLDB dialect.

Common situations: 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.

Related errors

• PostgreSQL and CockroachDB only support the case insensitive
• MariaDB and legacy MySQL only support the case insensitive f
• Informix only supports the case insensitive flag 'i' as lite
• Can't emulate filter clause for inverse distribution functio
• Can't emulate filter clause for inverse distribution functio

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/19b96c345a15efe1. Report an issue: GitHub.