Numeric/String comparisons in queries

[dave_gough <ba...@googlemail.com>]

Hi,

I have a a String property stored in the repository, but would like to query
it using conditional logic as if it were a number

I have tried to implement the Atom spec in JCR and allowed extensions to be
added on an ad-hoc basis. In this case there are number of "meta" fields
that extend the Atom model, but they are all saved as Strings in an attempt
to keep it simple.

However I now want to find all Entries that have a width property > 10000,
but the query below does an ASCII comparison, so 20 > 1000

 /jcr:root/element(*,atom:Entry)[./meta/width/@atom:value>'1000'] 

I tried to spoof itby removing the single=quotes around 1000 and this gave
mixed results

 /jcr:root/element(*,atom:Entry)[./meta/width/@atom:value>1000] 

Is there a function  such as "jcr:to_number()" or something similar that
coerces the property to be a number? Or does anyone have any suggestions on
how to do compare strings as numbers

regards

Dave Gough

regards
-- 
View this message in context: http://n4.nabble.com/Numeric-String-comparisons-in-queries-tp932301p932301.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.

Re: Numeric/String comparisons in queries

[Alexander Klimetschek <ak...@day.com>]

2009/12/1 François Cassistat <f...@maya-systems.com>:
>> However I now want to find all Entries that have a width property > 10000,
>> but the query below does an ASCII comparison, so 20 > 1000
>>
>> /jcr:root/element(*,atom:Entry)[./meta/width/@atom:value>'1000']
>>
>> I tried to spoof itby removing the single=quotes around 1000 and this gave
>> mixed results
>
> It's normal since @atom:value property is from string type.

Right, on the xpath level, there is no specific ">" semantics defined
for a string ('1000'). But see below...

>> /jcr:root/element(*,atom:Entry)[./meta/width/@atom:value>1000]
>>
>> Is there a function  such as "jcr:to_number()" or something similar that
>> coerces the property to be a number? Or does anyone have any suggestions on
>> how to do compare strings as numbers

Jackrabbit uses Lucene under the hood for the search index, which only
supports lexicographical comparisons. Therefore, all JCR long
properties are padded with zeros at the beginning when indexed. The
same happens when taking the value from the XPath query (1000 above)
and querying lucene (eg. "00000001000").

That's why you get some results (and not an invalid query error or
similar), but since the original property is indexed as plain string
"1000" without padded zeros, the query won't work. Note that a simple
and automatic coercion upon search would be very slow, since it would
have to be done on all values from the index, and hence the index
structure couldn't be leveraged, leading to slow queries.

So the proper way is to store your number fields with property type
long (or double).

> There is a number function in standard XPath (http://www.w3.org/TR/xpath#function-number), you can try :
> /jcr:root/element(*,atom:Entry)[number(./meta/width/@atom:value)>1000]
> But I'm not sure it is supported in JCR/JackRabbit.

It's not supported (as explained above, but also not defined by the
JCR 1.0 spec).

Regards,
Alex

-- 
Alexander Klimetschek
alexander.klimetschek@day.com

Re: Numeric/String comparisons in queries

[François Cassistat <f...@maya-systems.com>]

Le 2009-12-01 à 12:10 PM, dave_gough a écrit :

> 
> Hi,
> 

Hi

> I have a a String property stored in the repository, but would like to query
> it using conditional logic as if it were a number
> 
> I have tried to implement the Atom spec in JCR and allowed extensions to be
> added on an ad-hoc basis. In this case there are number of "meta" fields
> that extend the Atom model, but they are all saved as Strings in an attempt
> to keep it simple.
> 
> However I now want to find all Entries that have a width property > 10000,
> but the query below does an ASCII comparison, so 20 > 1000
> 
> /jcr:root/element(*,atom:Entry)[./meta/width/@atom:value>'1000'] 
> 
> I tried to spoof itby removing the single=quotes around 1000 and this gave
> mixed results
> 

It's normal since @atom:value property is from string type.

> /jcr:root/element(*,atom:Entry)[./meta/width/@atom:value>1000] 
> 
> Is there a function  such as "jcr:to_number()" or something similar that
> coerces the property to be a number? Or does anyone have any suggestions on
> how to do compare strings as numbers
> 

There is a number function in standard XPath (http://www.w3.org/TR/xpath#function-number), you can try :
/jcr:root/element(*,atom:Entry)[number(./meta/width/@atom:value)>1000] 
But I'm not sure it is supported in JCR/JackRabbit.

> regards
> 
> Dave Gough
> 
> regards
> -- 
> View this message in context: http://n4.nabble.com/Numeric-String-comparisons-in-queries-tp932301p932301.html
> Sent from the Jackrabbit - Users mailing list archive at Nabble.com.



Frank