FromClauseIndex does not contain alias for SqmRoot

Hibernate ORM
1

Hello there! I need in some strange solution as pagination on PostgreSQL ctid identifier. So, im trying to register my function get_ctid()

SqmFunctionRegistry functionRegistry = functionContributions.getFunctionRegistry();
        functionRegistry.registerPattern(
                "get_ctid",
                "(((ctid::text::point)[0]::bigint << 32) | (ctid::text::point)[1]::bigint)",
                basicTypeRegistry.resolve( StandardBasicTypes.LONG ));

It works fine, but until hiber table aliases not used. And now im trying to pass Root as argument to my function (to get alias name). But im getting “InvalidDataAccessResourceUsageException: Error interpreting query [SqmRoot not yet resolved to TableGroup]”

As i understood, the problem is that FromClauseIndex does not contain alias for SqmRoot (sqmRoot has).

Have you any ideas about it? Where i can find info about Sqm root resolution process?

P.S. Sorry for my bad English :slight_smile:

2

Spring Data Spec looks like:

public static Specification<ReportModel> getPgsqlTidPointingSpecification(ReportID key, PagingCriteria.Direction dir) {
        return (root, query, criteriaBuilder) -> {
                // Создаем подзапрос для получения tid записи с указанным key

            Subquery<Long> subquery = query.subquery(Long.class);
            // Определяем Root для Subquery
            Root<ReportModel> subqueryRoot = subquery.from(ReportModel.class);
            subquery.select(criteriaBuilder.function("get_ctid", Long.class, subqueryRoot)).where(criteriaBuilder.equal(subqueryRoot.get("id"), key));

            // Возвращаем условие для выбора записей с tid больше полученного tid из подзапроса

            if (dir == PagingCriteria.Direction.FORWARD)
                return criteriaBuilder.greaterThan(criteriaBuilder.function("get_ctid", Long.class, subqueryRoot), subquery);
            else
                return criteriaBuilder.lessThan(criteriaBuilder.function("get_ctid", Long.class, subqueryRoot), subquery);

        };
    }

And i also have treat() invocations in other specifications, сonjuncted by and()

3

I don’t know what you think you’re doing there, but using the physical order of rows for pagination is a very bad idea, because the physical order can change between transactions.

If you really must use this, I would at least urge you to avoid this complicated bit transformation, because the tid data type is sortable. You can just use this:

functionRegistry.registerPattern(
    "get_ctid",
    new AbstractSqmSelfRenderingFunctionDescriptor( "get_ctid", StandardArgumentsValidators.exactly( 1 ), StandardFunctionReturnTypeResolvers.invariant( basicTypeRegistry.resolve( StandardBasicTypes.STRING ) ), null ) {

        @Override
        public void render(
                SqlAppender sqlAppender,
                List<? extends SqlAstNode> arguments,
                ReturnableType<?> returnType,
                SqlAstTranslator<?> walker) {
            final SqlAstNode sqlAstNode = arguments.get(0);
            final ColumnReference reference;
            if ( sqlAstNode instanceof Assignable ) {
                final Assignable assignable = (Assignable) sqlAstNode;
                reference = assignable.getColumnReferences().get(0);
            }
            else if ( sqlAstNode instanceof Expression ) {
                final Expression expression = (Expression) sqlAstNode;
                reference = expression.getColumnReference();
            }
            else {
                throw new HqlInterpretationException( "path did not map to a column" );
            }
            sqlAppender.appendSql( reference.getQualifier() );
            sqlAppender.appendSql( ".ctid" );
        }
    }
);

and then just use it like:

criteriaBuilder.function("get_ctid", String.class, subqueryRoot.get("id"))
1 Like
4

Thank you so much! I’ll check it out when I have time