[hibernate-issues] [JIRA] (HHH-16874) Hibernate 6 NativeQuery doesn't allow setting Array[] Parameter of Database Function

Pedro Vitor ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5daa227... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNzVhODljNWQy... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16874?atlOrigin=eyJpIjoiNzVhOD... ) HHH-16874 ( https://hibernate.atlassian.net/browse/HHH-16874?atlOrigin=eyJpIjoiNzVhOD... ) Hibernate 6 NativeQuery doesn't allow setting Array[] Parameter of Database Function ( https://hibernate.atlassian.net/browse/HHH-16874?atlOrigin=eyJpIjoiNzVhOD... ) Issue Type: Bug Affects Versions: 6.2.4 Assignee: Unassigned Components: hibernate-core, hibernate-orm-modules Created: 29/Jun/2023 13:12 PM Environment: Java: 20 Wildfly: 27.0.1.Final Hibernate: 6.2.4.Final PostgreSQL: 15 Operating System: Windows 11 Priority: Major Reporter: Pedro Vitor ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5daa227... ) I am encountering an issue with Hibernate 6's NativeQuery when trying to pass a parameter of type array to a database function. The problem arises when the parameter is passed as a single value or as a list of values. The issue occurs in the following method: public Integer getTotalHousedPoultry(List<Long> idOwners) { // Query StringBuilder sql = new StringBuilder("SELECT calculate_batch_poultry_quantity_housed_with_mortality_on_date(:idOwners)"); try { NativeQuery<Integer> query = getSession().createNativeQuery(sql.toString(), Integer.class); query.setParameterList("idOwners", idOwners); return query.getSingleResult(); } catch (Exception ex) { printError(getClass(), ex); return 0; } } The function declaration in the database is as follows: CREATE FUNCTION calculate_batch_poultry_quantity_housed_with_mortality_on_date(id_owners BIGINT[]); *Details:* * When a single value is passed in the parameter *(list size equals 1)* , the following error occurs: ERROR: function calculate_batch_poultry_quantity_housed_with_mortality_on_date(bigint) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 8 * When two or more values are passed in the parameter *(list size greater than 1)* , the following error occurs: ERROR: function calculate_batch_poultry_quantity_housed_with_mortality_on_date(record) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 8 *Attempted Solutions:* I have tried converting the list to an array using idOwners.toArray() when setting the parameter, but the same issue persists. I also attempted to pass the parameter as a direct string in the SQL query using the syntax "ARRAY [1, 2] ". Although *this approach seems to work* , it is not a recommended solution as the *syntax for array parameters may vary across different database systems* , posing compatibility issues. *Expected Behavior:* The Hibernate NativeQuery should correctly pass the parameter as bigint[] to match the function declaration in the database, regardless of whether it is a single value or a list of values. ( https://hibernate.atlassian.net/browse/HHH-16874#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16874#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100227- sha1:d7f1864 )