Hibernate 6 Teradata issue with getMaxResults

Hibernate ORM
1

Versions:
hibernate-community-dialects: 6.6.1.Final
hibernate-core: 6.6.1.Final
terajdbc: 20.00.00.34

Hibernate query translation for Teradata is creating the query incorrectly for limiting the results. The select statement generated has parenthesis () after the TOP keyword which is not applicable for Teradata.

Eg Code:

@Query()
public List<String> getColumnFromTable(Integer id) {
  return(List<String>) em.createQuery("SELECT COLUMN FROM TABLE WHERE id = :id ORDER BY id DESC").setParameter("id",id).setMaxResults(50).getResultList();
  }

Below is the error:

2024-10-02 12:01:43.666 DEBUG DTCSB-4PRH23213 --- [nio-8081-exec-4] o.h.o.s.exec                             : Skipping reading Query result cache data: cache-enabled = false, cache-mode = NORMAL
2024-10-02 12:01:43.675 DEBUG DTCSB-4PRH23213 --- [nio-8081-exec-4] o.h.SQL                                  : select top (?) column from table where id=? order by id desc
2024-10-02 12:01:43.773 DEBUG DTCSB-4PRH23213 --- [nio-8081-exec-4] o.h.e.j.s.SqlExceptionHelper             : could not prepare statement [select top (?) column from table where id=? order by id desc]
java.sql.SQLException: [Teradata Database] [TeraJDBC 20.00.00.34] [Error 3707] [SQLState 42000] Syntax error, expected something like an integer between '(' and '?'.

The correct query translation for teradata is: select top ? column from table where id=? order by id desc but currently it translates to select top (?) column from table where id=? order by id desc which has additional parenthesis

Can this be addressed in a future release?

2

The Teradata database is not officially supported by Hibernate, so we won’t be fixing this in the future. The TeratadaDialect is a community-supported dialect, so you might try to fix this yourself and submit a PR against Hibernate’s repository and we will be happy to review it and merge it.

3

I am new to this codebase. Can you point me to the class where the select query with getMaxResults is translated?

4

@skrishnappa looks like this is the statement rendering the top clause: hibernate-orm/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/TeradataSqlAstTranslator.java at 52adf4f664d1f2d5011922a6122a60c8071c1683 · hibernate/hibernate-orm · GitHub

The last parameter needsParenthesis is set to true, changing that to false should have the effect you are looking for. I would ask if you could test this locally before submitting a PR, as we have no way of verifying this works as we do not test on Teradata database.

5

@mbladel - Is there any documentation on how to push the fix & send the PR? I was able to download the code from 6.6 version brand but the build is failing

6

Refer to the how to use GitHub documentation provided by GitHub. Forking repositories and opening a PR are the most basic things, we expect contributors to know how that works.

7

@mbladel @VladoKuruc @sebersole - Can you please help me with this? I can work on the changes and add the test case but i am totally lost on how to do it. If you can guide me through the steps, it would be great. I am unable to understand the above GitHub process

8

You are going to have to be more specific.

What are you needing help with?

With how to use GitHub? Christian already pointed you to basic information on that. There is also hibernate-orm/CONTRIBUTING.md at main · hibernate/hibernate-orm · GitHub for additional information about working with the Hibernate repo specifically.

With doing the actual work? Marco already told you this is not a supported Dialect so we won’t be doing that. He went on to tell you where the thing you say is wrong is happening.

If something else, again, you are going to have to be specific or we can’t help you.