substringof, startswith, endswith all raise unexpected end of query" error

[GoogleCodeExporter]

What steps will reproduce the problem?
1.
2.
3.

What is the expected output? What do you see instead?

What version of the product are you using? On what operating system?
odata4j 0.5

Please provide any additional information below.

http://localhost:8080/cwsREST/cwsd/DispatchManualInfo?$filter=substringof('04190
0000019',clmNum)

Original issue reported on code.google.com by serbanva...@gmail.com on 13 Mar 2012 at 9:08

[GoogleCodeExporter]

Sorry guys, pressed enter by mistake
So, the error I get is below, running odata4j 0.5 in netbeans 7.1, glassfish 
3.1.2 against SQL Server 2008, with MSSQL jdbc driver.

java.lang.IllegalArgumentException: An exception occurred while creating a 
query in EntityManager: 
Exception Description: Syntax error parsing the query [SELECT t0 FROM 
DispatchManualInfo t0 WHERE (CASE WHEN t0.clmNum LIKE '%041900000019%' THEN 
TRUE ELSE FALSE END)], line 0, column -1: unexpected end of query.
Internal Exception: NoViableAltException(-1@[661:1: 
simpleConditionalExpressionRemainder[Object left] returns [Object node] : (n= 
comparisonExpression[left] | (n1= NOT )? n= 
conditionWithNotExpression[(n1!=null), left] | IS (n2= NOT )? n= 
isExpression[(n2!=null), left] );])

Original comment by serbanva...@gmail.com on 13 Mar 2012 at 9:11

[GoogleCodeExporter]

Is it a valid sql server query?

SELECT t0 FROM DispatchManualInfo t0 WHERE (CASE WHEN t0.clmNum LIKE 
'%041900000019%' THEN TRUE ELSE FALSE END)

Original comment by john.spurlock on 16 Mar 2012 at 6:40

[GoogleCodeExporter]

First of all, John, thanks for taking a look at this and for bringing odata to 
java. I am sure you know your efforts are greatly appreciated by all of us.

Now, back to my error...
The translation of the JPQL query 
[SELECT t0 FROM DispatchManualInfo t0 WHERE (CASE WHEN t0.clmNum LIKE 
'%041900000019%' THEN TRUE ELSE FALSE END)]  
going through the mapped names for the table and column names from the entity 
data class built by Netbeans, should mean:

SELECT * FROM DISPATCH_MANUAL_INFO WHERE CLM_NUM LIKE '%041900000019%' 

Tested that and it returned a row of data, so it worked.

Now, if the JPA query means literally
SELECT t0 FROM DispatchManualInfo t0 WHERE (CASE WHEN t0.clmNum LIKE 
'%041900000019%' THEN TRUE ELSE FALSE END)
that one gives me an error like this in sql server
An expression of non-boolean type specified in a context where a condition is 
expected, near ')'.

Do I use the wrong dialect for eclipse link?
I read that the eclipselink.target-database param should force the dialect to 
be compatible with the target database server type. So, what I did in the 
persistence.xml config file, was:

  <persistence-unit name="cwsDispatchUserPU" transaction-type="JTA">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <jta-data-source>jdbc/cws_model_dispatch</jta-data-source>
        <class>net.prcins.cws.jpa.producer.DispatchManualInfo</class>

    <properties>
      <property name="eclipselink.logging.level" value="INFO"/>
      <property name="javax.persistence.jdbc.password" value="xxx"/>
      <property name="javax.persistence.jdbc.user" value="username"/>
      <property name="javax.persistence.jdbc.driver" value="net.sourceforge.jtds.jdbc.Driver"/>
      <property name="javax.persistence.jdbc.url" value="jdbc:jtds:sqlserver://sqlservername/databasename"/>
      <property name="javax.persistence.jdbc.schema" value="dbo"/>
      <property name="eclipselink.target-database" value="SQLServer"/>
    </properties>
  </persistence-unit>

Usually, the first error one gets with the sql dialect not being good is that 
the pagination doesn't work, but for me the $skip, $top work just fine, just 
like an "equal" filter.

This is a big booboo for me...searching by exact strings is not good enough...
By the way, I noticed that for this case I used the jtds driver, but the same 
happened for the mssql driver. See below an error from another search, and this 
time I collected the error from the glassfish console
URL query:
http://localhost:8080/eClaimREST/dispatch/EstimateTypeCd?$filter=substringof('S'
,EstimateTypeCd)

Glassfish reports:
Caused by: Exception [EclipseLink-8028] (Eclipse Persistence Services - 
2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [SELECT t0 FROM 
EstimateTypeCd t0 WHERE (CASE WHEN t0.EstimateTypeCd LIKE '%S%' THEN TRUE ELSE 
FALSE END)], line 0, column -1: unexpected end of query.
Internal Exception: NoViableAltException(-1@[661:1: 
simpleConditionalExpressionRemainder[Object left] returns [Object node] : (n= 
comparisonExpression[left] | (n1= NOT )? n= 
conditionWithNotExpression[(n1!=null), left] | IS (n2= NOT )? n= 
isExpression[(n2!=null), left] );])

Thanks again

Original comment by serbanva...@gmail.com on 16 Mar 2012 at 9:09

[GoogleCodeExporter]

OK, I figured out what the real ticket should have been :) 

Steps to reproduce the problem:
Run the examples that come with the source package of odata4j, any version 
(just tested against 0.7, but it happens in the other versions, as well)
Test this URL query:
http://localhost:8886/NortwindJpaProducerExample.svc/Orders?$filter=startswith(S
hipName,'Vins')
You will be getting an error like this: Syntax error parsing the query [], etc.

The error is not happening if you add "eq true" at the end, meaning the URL is 
this:
http://localhost:8886/NortwindJpaProducerExample.svc/Orders?$filter=startswith(S
hipName,'Vins') eq true

The WCF version of the service works in both cases.
Please test here:
http://services.odata.org/Northwind/Northwind.svc/Orders?$filter=startswith(Ship
Name,'Vins')
as well as 
http://services.odata.org/Northwind/Northwind.svc/Orders?$filter=startswith(Ship
Name,'Vins') eq true

The reason I noticed the error is because I tested my web service with OData 
Explorer, and that Silverlight client was building the startswith queries 
without the "eq true" suffix.

Regards,
Serban

Original comment by serbanva...@gmail.com on 30 Apr 2012 at 7:47

[GoogleCodeExporter]

Anyone know the fix for this (or even where in the code this happens)? I've 
seen this same bug using LINQPad.

Original comment by jonathan...@gmail.com on 11 Oct 2012 at 8:04

[GoogleCodeExporter]

The best solution would be to add "eq true" in the parser if it can be implied. 
Otherwise, we need to have more pedantic clients. For instance, the following 
in LINQ:

from p in Person
where  p.Name.ToUpper().Contains("ADI")
select p

Generates this failing URL: 
http://localhost:8080/project/odata/Person()?$filter=substringof('ADI',toupper(N
ame))

And that fails. In this case, however, you can be more specific in the LINQ as 
follows:

from p in Person
where  p.Name.ToUpper().Contains("ADI") == true
select p

Generates this working URL: 
http://localhost:8080/project/odata/Person()?$filter=substringof('ADI',toupper(N
ame)) eq true

Anybody know enough about the parser to add the "eq true" if it can be implied 
by the context?

Original comment by jonathan...@gmail.com on 11 Oct 2012 at 9:53

[GoogleCodeExporter]

Hello, 

By following the thread I was able to solve the issue for me by changing 
JPQLGenerator.java accordingly. Please check the attached diff and decide if 
it's more generally applicable.

Original comment by rodrigo....@gmail.com on 16 Oct 2013 at 3:14

Attachments:

• patch.diff

[GoogleCodeExporter]

Note that the patch I submitted can be further simplified by simply using the 
following condition in all changed lines:

+          "(%s LIKE '%s%%')",

Original comment by rodrigo....@gmail.com on 17 Oct 2013 at 6:28