SUBSTRING() arguments

4 messages

SUBSTRING() arguments

Reply Threaded

Hi All

While testing the Ingres OpenJPA dictionary that I have been developing, I
encountered a few test failures relating to arguments to the substring
function.

Ingres currently doesn't have a substring function that accepts a BIGINT as
the index argument. This seems reasonable, since VARCHARs are limited to
2000 characters anyway.

The following tests fail as they are trying to use a BIGINT argument to
SUBSTRING.

*) org.apache.openjpa.persistence.jpql.functions.TestEJBQLFunction
.testConcatSubStringFunc
*) org.apache.openjpa.persistence.models.company.CompanyModelTest
.testBasicQueries
(This is an abstract class implemented 4 times by various models)

I looked at the code and I couldn't figure out why the framework was sending
the index as a BIGINT. Can anyone advise?

Here is the error returned, although I think it is really coming from the
Ingres Database.

Function requires argument to be numeric. {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.wage, t0.weeklyHours FROM
BAS_Person t0 INNER JOIN BAS_Company t1 ON t0.COMPANY_ID = t1.id LEFT OUTER
JOIN BAS_Address t2 ON t0.HOMEADDRESS_ID = t2.id LEFT OUTER JOIN BAS_Person
t4 ON t0.MANAGER_ID = t4.id LEFT OUTER JOIN BAS_Address t3 ON t1.ADDRESS_ID
= t3.id WHERE (((POSITION((t1.name) IN (SUBSTRING(?, ((CAST(? AS BIGINT) -
CAST(? AS BIGINT)) + 1)))) - 1 + (CAST(? AS BIGINT) - CAST(? AS BIGINT))) +
?) > ?) AND t0.DTYPE = ?} [code=330897, state=42000]

Alan

smime.p7s (4K) Download Attachment

Miłosz Tylenda

Re: SUBSTRING() arguments

Reply Threaded

Hi Alan,

What is the value of requiresCastForMathFunctions in your dictionary? Have you tried running it with requiresCastForMathFunctions=false?

Greetings,
Milosz

> Hi All
>
> While testing the Ingres OpenJPA dictionary that I have been developing, I
> encountered a few test failures relating to arguments to the substring
> function.
>
> Ingres currently doesn't have a substring function that accepts a BIGINT as
> the index argument. This seems reasonable, since VARCHARs are limited to
> 2000 characters anyway.
>
> The following tests fail as they are trying to use a BIGINT argument to
> SUBSTRING.
>
> *) org.apache.openjpa.persistence.jpql.functions.TestEJBQLFunction
> .testConcatSubStringFunc
> *) org.apache.openjpa.persistence.models.company.CompanyModelTest
> .testBasicQueries
> (This is an abstract class implemented 4 times by various models)
>
> I looked at the code and I couldn't figure out why the framework was sending
> the index as a BIGINT. Can anyone advise?
>
> Here is the error returned, although I think it is really coming from the
> Ingres Database.
>
> Function requires argument to be numeric. {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.wage, t0.weeklyHours FROM
> BAS_Person t0 INNER JOIN BAS_Company t1 ON t0.COMPANY_ID = t1.id LEFT OUTER
> JOIN BAS_Address t2 ON t0.HOMEADDRESS_ID = t2.id LEFT OUTER JOIN BAS_Person
> t4 ON t0.MANAGER_ID = t4.id LEFT OUTER JOIN BAS_Address t3 ON t1.ADDRESS_ID
> = t3.id WHERE (((POSITION((t1.name) IN (SUBSTRING(?, ((CAST(? AS BIGINT) -
> CAST(? AS BIGINT)) + 1)))) - 1 + (CAST(? AS BIGINT) - CAST(? AS BIGINT))) +
> ?) > ?) AND t0.DTYPE = ?} [code=330897, state=42000]
>
> Alan
>
>

Alan Raison

RE: SUBSTRING() arguments

Reply Threaded

Hi Milosz

The requiresCastForMathFunctions was true. When set to false, I get the same error, without the CASTs:

Function requires argument to be numeric. {SELECT t0.id, t1.id, t1.city, t1.phoneNumber, t1.postalCode, t1.state, t1.streetAddress, t0.NAME0 FROM IDC_Company t0 INNER JOIN IDC_Address t1 ON t0.ADDRESS_ID = t1.id WHERE (((POSITION((t1.city) IN (SUBSTRING(?, ((? - ?) + 1)))) - 1 + (? - ?)) + ?) > ?)} [code=330897, state=42000]"

I guess that's why I set the "requiresCast..." property, but it doesn't seem to be working!

Alan

-----Original Message-----
From: Milosz Tylenda [mailto:[hidden email]]
Sent: 10 June 2009 13:18
To: [hidden email]
Subject: Re: SUBSTRING() arguments

Hi Alan,

What is the value of requiresCastForMathFunctions in your dictionary? Have you tried running it with requiresCastForMathFunctions=false?

Greetings,
Milosz

Miłosz Tylenda

RE: SUBSTRING() arguments

Reply Threaded

Hi Alan,

It looks like a similar problem we have with PostgreSQL. I haven't looked into it yet. If the problem boils down to casting to a specific type, you could try to override the substring method so that it creates something like

SUBSTRING(?, CAST(... AS specific_type))

The reason might also be that Ingres won't accept anything other than numeric literal (Firebird does that if I remember) as the argument of SUBSTRING. If this is the case, we probably can't help it and only literals (constants) will be allowed as an argument.

Regards,
Milosz

> Hi Milosz
>
> The requiresCastForMathFunctions was true. When set to false, I get the same error, without the CASTs:
>
> Function requires argument to be numeric. {SELECT t0.id, t1.id, t1.city, t1.phoneNumber, t1.postalCode, t1.state, t1.streetAddress, t0.NAME0 FROM IDC_Company t0 INNER JOIN IDC_Address t1 ON t0.ADDRESS_ID = t1.id WHERE (((POSITION((t1.city) IN (SUBSTRING(?, ((? - ?) + 1)))) - 1 + (? - ?)) + ?) > ?)} [code=330897, state=42000]"
>
> I guess that's why I set the "requiresCast..." property, but it doesn't seem to be working!
>
> Alan
>
> -----Original Message-----
> From: Milosz Tylenda [mailto:[hidden email]]
> Sent: 10 June 2009 13:18
> To: [hidden email]
> Subject: Re: SUBSTRING() arguments
>
> Hi Alan,
>
> What is the value of requiresCastForMathFunctions in your dictionary? Have you tried running it with requiresCastForMathFunctions=false?
>
> Greetings,
> Milosz
>
>
> > Hi All
> >
> > While testing the Ingres OpenJPA dictionary that I have been developing, I
> > encountered a few test failures relating to arguments to the substring
> > function.
> >
> > Ingres currently doesn't have a substring function that accepts a BIGINT as
> > the index argument. This seems reasonable, since VARCHARs are limited to
> > 2000 characters anyway.
> >
> > The following tests fail as they are trying to use a BIGINT argument to
> > SUBSTRING.
> >
> > *) org.apache.openjpa.persistence.jpql.functions.TestEJBQLFunction
> > .testConcatSubStringFunc
> > *) org.apache.openjpa.persistence.models.company.CompanyModelTest
> > .testBasicQueries
> > (This is an abstract class implemented 4 times by various models)
> >
> > I looked at the code and I couldn't figure out why the framework was sending
> > the index as a BIGINT. Can anyone advise?
> >
> > Here is the error returned, although I think it is really coming from the
> > Ingres Database.
> >
> > Function requires argument to be numeric. {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.wage, t0.weeklyHours FROM
> > BAS_Person t0 INNER JOIN BAS_Company t1 ON t0.COMPANY_ID = t1.id LEFT OUTER
> > JOIN BAS_Address t2 ON t0.HOMEADDRESS_ID = t2.id LEFT OUTER JOIN BAS_Person
> > t4 ON t0.MANAGER_ID = t4.id LEFT OUTER JOIN BAS_Address t3 ON t1.ADDRESS_ID
> > = t3.id WHERE (((POSITION((t1.name) IN (SUBSTRING(?, ((CAST(? AS BIGINT) -
> > CAST(? AS BIGINT)) + 1)))) - 1 + (CAST(? AS BIGINT) - CAST(? AS BIGINT))) +
> > ?) > ?) AND t0.DTYPE = ?} [code=330897, state=42000]
> >
> > Alan
> >
> >
>