Support JPA SUBSTRING function in SELECT clause

[GoogleCodeExporter]

What steps will reproduce the problem?
1. entityManager.createQuery("SELECT SUBSTRING(v.name, 1, 3) from Venue
v") returns v.name but NOT the substring.

2. entityManager.createQuery("SELECT v from Venue
v WHERE SUBSTRING(v.name, 1, 1) = 'A'") doesn't return any result although
there is a record with v.name = 'Apple'

I am using appengine for Java SDK 1.3.0.

Original issue reported on code.google.com by lhw...@gmail.com on 5 Jan 2010 at 1:55

• Blocked on: #103

[GoogleCodeExporter]

Original comment by max.r...@gmail.com on 6 Jan 2010 at 5:16

• Changed state: Accepted

[GoogleCodeExporter]

I'm looking at the BNF for JPQL and I don't see anything to indicate that 
SELECT 
SUBSTRTING(v.name, 1, 3) should be supported.  What makes you think this should 
work?

As for using SUBSTRING in the 'where' clause, we can only support this when the 
start 
index is 1 since this is equivalent to a startsWith query, which the datastore 
can 
efficiently support.  Anything else, however,  should throw an exception.

Original comment by max.r...@gmail.com on 6 Jan 2010 at 5:28

[GoogleCodeExporter]

JPA2 PFD2 spec 4.6.17 states "Scalar expressions may be used in the SELECT 
clause of
a query as well as in the WHERE[58] and HAVING clauses". A scalar expression 
can be
"string_primary". A "string_primary" is defined as 

string_expression ::= string_primary | (substring_primary ::=
state_field_path_expression |
string_literal |
input_parameter |
functions_returning_strings |
aggregate_expression |
case_expression

and there we have "functions_returning_strings". QED

Original comment by googleco...@yahoo.co.uk on 6 Jan 2010 at 3:12

[GoogleCodeExporter]

That explains it - I was looking at the JPA1 spec.  I don't expect to start 
supporting JPA2 
features until the DataNucleus 2.0 upgrade.  In the meantime it should be very 
easy to 
perform the substring operation on the results in-memory.

Original comment by max.r...@gmail.com on 12 Jan 2010 at 4:41

• Added labels: FoundIn-1.0.4

[GoogleCodeExporter]

Original comment by googleco...@yahoo.co.uk on 7 Aug 2011 at 3:17

• Changed title: Support JPA SUBSTRING function in SELECT clause
• Added labels: Priority-Low, Type-Enhancement
• Removed labels: Priority-Medium, Type-Defect

[GoogleCodeExporter]

All GAE datastore queries return entities matching the filter. Any result is 
processed in-memory, and SVN trunk now ought to support the majority of all 
such possibilities.

Original comment by googleco...@yahoo.co.uk on 28 Feb 2012 at 10:16

• Changed state: Fixed
• Added labels: TargetRelease-2.0.1

[GoogleCodeExporter]

A little late lol...but your substring should be (v.name, 0, 1)to get the first 
letter

Original comment by aidandel...@gmail.com on 13 Mar 2014 at 5:08