Lucene range query problem

Submitted by hinata on Thu, 2012-02-16 20:53.Troubleshooting

I am using DBSight 4.3.1
I have a column Salary with Type = number(integer) and Field Type = Keyword. It has values: 0, 8, 10, 50, 80, 120, 230, 255, 400, 600
When I try the DBSight API SearchQuery q = new SearchQuery("Salary:[0,100]");
or a request http://...search.do?q=Salary:[0,100]
I have a correct result with 0, 8, 10, 50, 80
But when I use Lucene Query in DBSight API
SearchQuery q = new SearchQuery("").setLuceneQuery("Salary:[0 TO 100]");
or a request http://...search.do?lq=Salary:[0 TO 100]
I have an incorrect result with 0, 10
I have same problem when I try to change from DBSight Query to Lucene Query

Salary:(0, 100] => Salary:{0 TO 100]
Salary:(, 100] => Salary:{* TO 100]

Please help me!

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Submitted by will on Fri, 2012-02-17 10:44.

You will have to use DBSight query in this case. DBSight internally store numbers in a more efficient way, and Lucene query does not understand the format.

Submitted by hinata on Sun, 2012-02-19 00:36.

I try to use Lucene Query because I need to do a group search like
((Field01:value01 AND Field02:[min,max]) OR Field03:{min,max}) OR Field04:value04
I tried DBSight Query but it possibly does not support the group search. So Lucene Query is my only option and I come across the Lucene range query problem.
My temporary solution is to convert from number to string. I think it has bad performance. And it also makes DBSight Range query stop working.
I look forward to hearing some advices from you.

Submitted by will on Tue, 2012-02-21 01:24.

Please try to use Number(Decimal,Float), which I think Lucene default query parser can recognize.

Let me know if it works for you or not.

Submitted by hinata on Tue, 2012-02-21 18:41.

I try to use Number(Decimal,Float) but Lucene range query does not work. Lucene query parser can recognize the value if I search in a specific field or field grouping. It only has problem with the range search.

Submitted by will on Wed, 2012-02-22 01:52.

Actually it works for me.

The syntax is:
&lq=field_name:[ 100 TO 200 ]

What's the query you use?

Submitted by hinata on Wed, 2012-02-22 18:38.

I retried with a fresh database and DBSight Index.

ID / Name / MyFloat
1 / Test01 / 0
2 / Test02 / 8
3 / Test03 / 10
4 / Test04 / 50
5 / Test05 / 80
6 / Test06 / 120
7 / Test07 / 230
8 / Test08 / 255
9 / Test09 / 400
10 / Test10 / 600

===========
DBSight query: &q=MyFloat:[0,10]
Correct Result: 0.0 8.0 10.0
Lucene query: &lq=MyFloat:[0 TO 10]
Incorrect Result: 0.0
===========
DBSight query: &q=MyFloat:[0,80]
Correct Result: 0.0 8.0 10.0 50.0 80.0
Lucene query: &lq=MyFloat:[0 TO 80]
Incorrect Result: 0.0 8.0 10.0 50.0 120.0 230.0 255.0 400.0 600.0
===========
DBSight query: &q=MyFloat:[100,200]
Correct Result: 120.0
Lucene query: &lq=MyFloat:[100 TO 200]
Correct Result: 120.0
===========
DBSight query: &q=MyFloat:[230,400]
Correct Result: 230.0 255.0 400.0
Lucene query: &lq=MyFloat:[230 TO 400]
Incorrect Result: 230.0 255.0

I tried another sample

DBSight query: &q=MyFloat:[0,10]
Correct Result: 0.0 8.0 10.0
Lucene query: &lq=MyFloat:[0 TO 10]
Incorrect Result: 0.0
Lucene query: &lq=MyFloat:[0.0 TO 10.0]
Incorrect Result: 0.0 10.0

DBSight range query always returns a correct result, but Lucene range query does not.

You should try with my database value and query.

Submitted by will on Wed, 2012-02-22 22:33.

I understand the issue now. My test was not a good test.

Let's say you have a column as "myColumn". Basically, in DBSight, the numbers are stored as is in "myColumn", and in a sortable format in another column, prepended with "s", as "smyColumn". DBSight does the translation automatically.

So to search with the lucene query, you would need to search on "smyColumn", and format the value the same way as DBSight does.

We will send you more details via email directly.

Submitted by phalgo on Wed, 2013-11-13 17:56.

This article here pretty explain the problem with "range" and "numeric value"

http://brettscott.wordpress.com/2011/11/19/lucene-number-range-search-integers-floats/

Basically, you should use "string" and pad item with 0.

Example:
Item 1 - Price: 240
Item 2 - Price: 1500

Should be indexed like this:
Item 1 - Price: 00240
Item 2 - Price: 01500

And then, search for lq=price:[00100 TO 00500]