[ https://issues.apache.org/jira/browse/ASTERIXDB-2372?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16454783#comment-16454783 ]
Taewoo Kim edited comment on ASTERIXDB-2372 at 4/26/18 8:11 PM: ---------------------------------------------------------------- Fact 1. Btree-search is a range search. We need to provide a low value (L) and a high value (H) and their inclusiveness. For example, a genral B-tree search query is "id > 1 and id <= 5". In this case, the low value is 1 and the high value is 5. Also, the low value should not be included in the result and the high value should be included. For an equality-condition search such as "id = 1" is translated as "id >=1 and id <= 1" to get only one value. Fact 2. For the equality-condition search, if a float or double value is provided to an integer index, we transform it to two values - floor( x ) and ceil( x ). The intuition behind this is that the search only makes sense when floor( x ) is equal to ceil( x ). This is only true when a float or double value only contains the integral part (e.g., 1.0, 8.0). For other values, ceil( x ) is not equal to floor ( x ). The cause: the cause is that "id = 1.3" will be translated as "id >=1 and id <=2" because floor(1.3) = 1 and ceil(1.3) = 2. So, two tuples are returned since AsterixDB removed the SELECT operator that checks "id = 1.3" by providing that condition to an index-search. The solution is removing the inclusiveness option when a float value is translated as two different values when applying ceil() and floor(). Case #1: query: id = 1.0 (floor(1) = 1 and ceil(1) = 2) internal: id >=1 and id <= 1 : maintained the inclusiveness Case #2: query: id = 1.3 (floor(1.3) = 1 and ceil(1.3) = 2) internal: id > 1 and id < 2 : removed the inclusiveness was (Author: wangsaeu): Fact 1. Btree-search is a range search. We need to provide a low value (L) and a high value (H) and their inclusiveness. For example, a genral B-tree search query is "id > 1 and id <= 5". In this case, the low value is 1 and the high value is 5. Also, the low value should not be included in the result and the high value should be included. For an equality-condition search such as "id = 1" is translated as "id >=1 and id <= 1" to get only one value. Fact 2. For the equality-condition search, if a float or double value is provided to an integer index, we transform it to two values - floor(x) and ceil(x). The intuition behind this is that the search only makes sense when floor(x) is equal to ceil(x). This is only true when a float or double value only contains the integral part (e.g., 1.0, 8.0). For other values, ceil(x) is not equal to floor(x). The cause: the cause is that "id = 1.3" will be translated as "id >=1 and id <=2" because floor(1.3) = 1 and ceil(1.3) = 2. So, two tuples are returned since AsterixDB removed the SELECT operator that checks "id = 1.3" by providing that condition to an index-search. The solution is removing the inclusiveness option when a float value is translated as two different values when applying ceil() and floor(). Case #1: query: id = 1.0 (floor(1) = 1 and ceil(1) = 2) internal: id >=1 and id <= 1 : maintained the inclusiveness Case #2: query: id = 1.3 (floor(1.3) = 1 and ceil(1.3) = 2) internal: id > 1 and id < 2 : removed the inclusiveness > Providing a float value predicate to an integer primary index does not work > as expected. > ---------------------------------------------------------------------------------------- > > Key: ASTERIXDB-2372 > URL: https://issues.apache.org/jira/browse/ASTERIXDB-2372 > Project: Apache AsterixDB > Issue Type: Bug > Reporter: Taewoo Kim > Assignee: Taewoo Kim > Priority: Critical > > If we have an integer primary index and feed a float value predicate that is > not an integer such as 1.3, the search result is not correct. > > The DDL and DML > {code:java} > drop dataverse test if exists; > create dataverse test; > use test; > create type MyRecord as closed { > id: int64 > }; > create dataset MyData(MyRecord) primary key id; > insert into MyData({"id":1}); > insert into MyData({"id":2}); > select * from MyData where id = 1.3;{code} > > The result should be empty. But, it returns 1 and 2 as the result. > -- This message was sent by Atlassian JIRA (v7.6.3#76005)