[
https://issues.apache.org/jira/browse/OPENJPA-1726?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Fay Wang updated OPENJPA-1726:
------------------------------
Attachment: OPENJPA-1726.patch
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.
> Clean up OpenJPA test case failures for PostgreSQL
> --------------------------------------------------
>
> Key: OPENJPA-1726
> URL: https://issues.apache.org/jira/browse/OPENJPA-1726
> Project: OpenJPA
> Issue Type: Bug
> Affects Versions: 2.1.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Priority: Minor
> Attachments: OPENJPA-1726.patch
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
