TestIdClassCompanyModel.testBasicQueries fails with the following exception:
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ERROR: function pg_catalog.substring(character varying, bigint) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 658
{prepstmnt 795758045 SELECT t0.id, t0.DTYPE, t0.firstName, t2.id, t2.city, t2.phoneNumber, t2.postalCode, t2.state, t2.streetAddress, t0.lastName, t1.id, t3.id, t3.city, t3.phoneNumber, t3.postalCode, t3.state, t3.streetAddress, t1.name, t0.hireDate, t4.id, t4.DTYPE, t4.firstName, t4.HOMEADDRESS_ID, t4.lastName, t4.COMPANY_ID, t4.hireDate, t4.title, t4.salary, t0.title, t0.salary, t0.wage, t0.weeklyHours FROM IDC_Person t0 INNER JOIN IDC_Company t1 ON t0.COMPANY_ID = t1.id LEFT OUTER JOIN IDC_Address t2 ON t0.HOMEADDRESS_ID = t2.id LEFT OUTER JOIN IDC_Person t4 ON t0.MANAGER_ID = t4.id LEFT OUTER JOIN IDC_Address t3 ON t1.ADDRESS_ID = t3.id WHERE (((POSITION(t1.name IN SUBSTRING(?, ((? - ?) + 1))) - 1 + (? - ?)) + ?) > ?) AND t0.DTYPE IN (?, ?, ?) AND (t4.DTYPE IS NULL OR t4.DTYPE IN (?)) [params=?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?]}
[code=0, state=42883]
FailedObject: select x from IDC_Employee x where locate(x.company.name, 'x', 1) > 0 [java.lang.String]
In the push-down sql:
(((POSITION(t0.firstName IN SUBSTRING(?, ((? - ?) + 1))) - 1 + (? - ?)) + ?) > ?)
OpenJPA set the parameters as follows:
pstmt.setString(1, "x");
pstmt.setLong(2, 1);
pstmt.setInt(3, 1);
pstmt.setLong(4, 1);
pstmt.setInt(5, 1);
pstmt.setInt(6, 1);
pstmt.setLong(7, 0);
The second parameter is set to type Long, which causes the error that the function pg_catalog.substring(character varying, bigint) does not exist. It turns out the for Postgres, the start index in the locate function must be int:
http://www.postgresql.org/docs/8.1/static/functions-string.html
substring(string [from int] [for int])
The fix is to modify JPQLExpressionBuilder to create a Lit of Integer value for the locateFromIndex.
Although this fix affects all database, it is reasonable to assume that the start index in the locate function is in the integer range.
TestIdClassCompanyModel.testBasicQueries fails with the following exception:
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ERROR: function pg_catalog.substring(character varying, bigint) does not exist
{prepstmnt 795758045 SELECT t0.id, t0.DTYPE, t0.firstName, t2.id, t2.city, t2.phoneNumber, t2.postalCode, t2.state, t2.streetAddress, t0.lastName, t1.id, t3.id, t3.city, t3.phoneNumber, t3.postalCode, t3.state, t3.streetAddress, t1.name, t0.hireDate, t4.id, t4.DTYPE, t4.firstName, t4.HOMEADDRESS_ID, t4.lastName, t4.COMPANY_ID, t4.hireDate, t4.title, t4.salary, t0.title, t0.salary, t0.wage, t0.weeklyHours FROM IDC_Person t0 INNER JOIN IDC_Company t1 ON t0.COMPANY_ID = t1.id LEFT OUTER JOIN IDC_Address t2 ON t0.HOMEADDRESS_ID = t2.id LEFT OUTER JOIN IDC_Person t4 ON t0.MANAGER_ID = t4.id LEFT OUTER JOIN IDC_Address t3 ON t1.ADDRESS_ID = t3.id WHERE (((POSITION(t1.name IN SUBSTRING(?, ((? - ?) + 1))) - 1 + (? - ?)) + ?) > ?) AND t0.DTYPE IN (?, ?, ?) AND (t4.DTYPE IS NULL OR t4.DTYPE IN (?)) [params=?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?]}Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 658
[code=0, state=42883]
FailedObject: select x from IDC_Employee x where locate(x.company.name, 'x', 1) > 0 [java.lang.String]
In the push-down sql:
(((POSITION(t0.firstName IN SUBSTRING(?, ((? - ?) + 1))) - 1 + (? - ?)) + ?) > ?)
OpenJPA set the parameters as follows:
pstmt.setString(1, "x");
pstmt.setLong(2, 1);
pstmt.setInt(3, 1);
pstmt.setLong(4, 1);
pstmt.setInt(5, 1);
pstmt.setInt(6, 1);
pstmt.setLong(7, 0);
The second parameter is set to type Long, which causes the error that the function pg_catalog.substring(character varying, bigint) does not exist. It turns out the for Postgres, the start index in the locate function must be int:
http://www.postgresql.org/docs/8.1/static/functions-string.html
substring(string [from int] [for int])
The fix is to modify JPQLExpressionBuilder to create a Lit of Integer value for the locateFromIndex.
Although this fix affects all database, it is reasonable to assume that the start index in the locate function is in the integer range.