quarkus-reactive-mssql-client - Pagination throws Error

[git4rputuval]

Version

Quarkus - 2.7.2

Context

I am using pagination parameters, page Number and records per page to implement Pagination. For the same, below section of code is used.
Integer pageNo = paginationDto.getPageNumber(); Integer recordsPerPage = paginationDto.getRecordsPerPage(); Page page = new Page(pageNo, recordsPerPage); query = query.page(page);

The query when executed, gives the error as attached herewith.
Error Log.txt

Do you have a reproducer?

Have created the code inside the Quarkus Hibernate code base. PFA.
hibernate-reactive-panache-quickstart.zip

Steps to reproduce

1. Start server
2. Run postman script -> AppUsers Pagination.postman_collection.json.zip
3. See error log

[tsegismont]

The error in the logs says that the MSSQL client could not prepare the query because of a syntax problem around ?. The problem is that the question mark is a JDBC standard but this is not what MSSQL server expects.

@DavideD can you take a look please? Do you believe this comes from Hibernate Reactive? Or is that query provided by the user?

[DavideD]

It's probably our conversion to a compatible MSSQL query that doesn't work in this case. I will have a look

[gavinking]

@DavideD note that there are multiple impls of LimitHandler for SQL Server, depending on the version of the SQL Server.

[DavideD]

@git4rputuval Could you try the latest Quarkus: 2.9.2?

[DavideD]

OK, it took me a while but I can now see the exception. It happens with 2.9.2 as well

[DavideD]

Yes, it's a bug in Hibernate Reactive. I'm going to create an issue.
As a workaround, it will work if the query has an order by clause (or a filter):

@git4rputuval For your example, it will work if you change BaseDataSvc#createCriteriaQuery to:

	protected PanacheQuery<CommonAppUsersMdl> createCriteriaQuery(CommonAppUsersQueryDto tQuery) {
		BaseQueryVO baseQueryVO = getQuery(tQuery);
		logger.info(baseQueryVO.getQuery());
		PanacheQuery<CommonAppUsersMdl> query = getRepository()
                                 // It doesn't matter the field, as long as there is a Sort.by
				.find( baseQueryVO.getQuery(), Sort.by( "loginname" ), baseQueryVO.getQueryMap());

		query = setPagination(query, tQuery);
		return query;
	}

[DavideD]

I've created the issue for Hibernate Reactive: hibernate/hibernate-reactive#1342

[git4rputuval]

Yes, it's a bug in Hibernate Reactive. I'm going to create an issue. As a workaround, it will work if the query has an order by clause (or a filter):

@git4rputuval For your example, it will work if you change BaseDataSvc#createCriteriaQuery to:

	protected PanacheQuery<CommonAppUsersMdl> createCriteriaQuery(CommonAppUsersQueryDto tQuery) {
		BaseQueryVO baseQueryVO = getQuery(tQuery);
		logger.info(baseQueryVO.getQuery());
		PanacheQuery<CommonAppUsersMdl> query = getRepository()
                                 // It doesn't matter the field, as long as there is a Sort.by
				.find( baseQueryVO.getQuery(), Sort.by( "loginname" ), baseQueryVO.getQueryMap());

		query = setPagination(query, tQuery);
		return query;
	}

Hi,

Have tested this with the "Sort" option. It is working. So we are going ahead with this solution till you resolve the issue.

Regards,

[git4rputuval]

I've created the issue for Hibernate Reactive: hibernate/hibernate-reactive#1342

I see the progress of 1342 as committed. Please advise how we should progress with this change in Quarkus. At the moment, we are using Quarkus 2.7.2, and we are in a stage of release, so cannot plan to upgrade to latest version of Quarkus. Please advise what needs to be done to get the latest version of hibernate fix in Quarkus 2.7.2. Thanks.

[DavideD]

When we release the next Hibernate Reactive, you can try replacing the dependency in Quarkus. It shouldn't contain any breaking change, BUT Quarkus integration with Hibernate Reactive and Hibernate ORM is pretty strong, so I'm not 100% sure it's going to work. Maybe @Sanne knows?

[DavideD]

BTW, I've just realized that this issue is for the Vert.x SQL client. I think we can close it.

[tsegismont]

Thanks @DavideD

[tsegismont]

Closed, see issue hibernate/hibernate-reactive#1342

[DavideD]

@git4rputuval It seems that Hibernate Reactive 1.1.7.Final (the one that contains the fix) will be backported to Quarkus 2.7. So, you might not have to upgrade up to Quarkus 2.10 to get it. See quarkusio/quarkus#26482 (comment)

[Sanne]

you can also override the version of Hibernate Reactive on existing releases of Quarkus 2.7.x

[git4rputuval]

@git4rputuval It seems that Hibernate Reactive 1.1.7.Final (the one that contains the fix) will be backported to Quarkus 2.7. So, you might not have to upgrade up to Quarkus 2.10 to get it. See quarkusio/quarkus#26482 (comment)

Thanks. We will wait. For now, we have added the Order By and progressing with the client side deployment. But I will update once I receive the version 1.1.17.