Java Filter Expression Range Filter not working

1 
QueryPolicy queryPolicy = new QueryPolicy();
queryPolicy.readModeSC=ReadModeSC.ALLOW_REPLICA;
queryPolicy.replica=Replica.PREFER_RACK;
// Create statement
Statement stmt = new Statement();

// Set namespace and set name
stmt.setNamespace(namespace);
stmt.setSetName(ourSet);
stmt.setFilter(Filter.range("updateTime", offsetStart, offsetEnd));
stmt.setFilter(Filter.equal("fromAccountId", accountId, null));
RecordSet recordSet = client.query(queryPolicy, stmt);

Is this method of filtering on a range correct? It does not seem to be working and is pulling all records regardless of updateTime.

2

Cross posted from Discord - being answered there.

3

@pgupta It looks like only latest one setFilter condition is working, pls advise how to add multiple filter conditions.

4

Let me walk you through a simple example with different options for querying.

My test data:

Initialized the client and connected to the cluster.
key0 : (gen:1),(exp:455917634),(bins:(name:Sandra),(age:34))
key1 : (gen:1),(exp:455917634),(bins:(name:Jack),(age:26))
key2 : (gen:1),(exp:455917634),(bins:(name:Jill),(age:20))
key3 : (gen:1),(exp:455917634),(bins:(name:James),(age:38))
key4 : (gen:1),(exp:455917634),(bins:(name:Jim),(age:46))
key5 : (gen:1),(exp:455917634),(bins:(name:Julia),(age:62))
key6 : (gen:1),(exp:455917634),(bins:(name:Sally),(age:32))
key7 : (gen:1),(exp:455917634),(bins:(name:Sean),(age:24))
key8 : (gen:1),(exp:455917634),(bins:(name:Sam),(age:12))
key9 : (gen:1),(exp:455917634),(bins:(name:Susan),(age:42))

I will omit all the import statements - just show the relevant code:

Basic Query: Find all records where age between 20 and 30.

Code:

//Run SI query
Statement stmt = new Statement();
stmt.setNamespace("test");
stmt.setSetName("testset");
stmt.setFilter(Filter.range("age", 20,30));
QueryPolicy qp = new QueryPolicy();
RecordSet rs = client.query(qp, stmt);

WritePolicy wp = new WritePolicy();

int nCount = 0;

while(rs.next()){
    Record r = rs.getRecord();
    Key thisKey = rs.getKey();  
    System.out.println(r);
}

Output:

(gen:1),(exp:455917634),(bins:(name:Sean),(age:24))
(gen:1),(exp:455917634),(bins:(name:Jill),(age:20))
(gen:1),(exp:455917634),(bins:(name:Jack),(age:26))

Modify the query to only return when name starts with S.
Find all records where age between 20 and 30 AND name starts with S.

Code:

Statement stmt = new Statement();
stmt.setNamespace("test");
stmt.setSetName("testset");
stmt.setFilter(Filter.range("age", 20,30));

Expression nameExp = Exp.build( 
     Exp.regexCompare("^S.*", RegexFlag.ICASE|RegexFlag.NEWLINE, Exp.stringBin("name")) 
     );

QueryPolicy qp = new QueryPolicy();

qp.filterExp = nameExp;

RecordSet rs = client.query(qp, stmt);

while(rs.next()){
    Record r = rs.getRecord();
    Key thisKey = rs.getKey();  
    System.out.println(r);
}

Output:

(gen:1),(exp:455917634),(bins:(name:Sean),(age:24))

If instead of AND you want OR - its no longer a Secondary Index assisted query. We must scan every record.

Find all records where age between 20 and 30 OR name starts with S.

Code:

Statement stmt = new Statement();
stmt.setNamespace("test");
stmt.setSetName("testset");
stmt.setFilter(null);

Expression nameExp = Exp.build( 
    Exp.or(
       Exp.regexCompare("^S.*", RegexFlag.ICASE|RegexFlag.NEWLINE, Exp.stringBin("name")),
       Exp.and( 
           Exp.gt(Exp.intBin("age"), Exp.val(20)), 
           Exp.lt(Exp.intBin("age"), Exp.val(30))) 
          )
     );

QueryPolicy qp = new QueryPolicy();

qp.filterExp = nameExp;

RecordSet rs = client.query(qp, stmt);

while(rs.next()){
    Record r = rs.getRecord();
    Key thisKey = rs.getKey();  
    System.out.println(r);
}

Output:

(gen:1),(exp:455917634),(bins:(name:Sandra),(age:34))
(gen:1),(exp:455917634),(bins:(name:Susan),(age:42))
(gen:1),(exp:455917634),(bins:(name:Sally),(age:32))
(gen:1),(exp:455917634),(bins:(name:Sean),(age:24))
(gen:1),(exp:455917634),(bins:(name:Jack),(age:26))
(gen:1),(exp:455917634),(bins:(name:Sam),(age:12))

Hope that explains the different query constructs.

5

BTW, in the initial post:


queryPolicy.readModeSC=ReadModeSC.ALLOW_REPLICA;

This does not apply to Queries - applies to reads.

6

Please advise in following code, READ from REPLICA will be valid or any other way to optimize this codebase.

		stmt.setNamespace(namespace);
		stmt.setSetName(uetrSet);
		stmt.setFilter(Filter.range(UPDATE_TIME_BIN, offsetStart, offsetEnd));

		QueryPolicy fromAccountQueryPolicy = new QueryPolicy();
		fromAccountQueryPolicy.readModeSC = ReadModeSC.ALLOW_REPLICA;
		fromAccountQueryPolicy.replica = Replica.PREFER_RACK;
		fromAccountQueryPolicy.filterExp = Exp.build(Exp.eq(Exp.stringBin(FROM_ACCOUNT_ID_BIN), Exp.val(accountId)));

RecordSet fromAccountRecordSet = client.query(fromAccountQueryPolicy, stmt);
7

That does not do anything for QueryPolicy - ignored.

Why are you using PREFER_RACK? Do you have a rack-aware namespace? and you want to read from a specific rack-id?

8

Yes @pgupta ,

We are running multisite cluster 9 nodes (3 nodes in each region with strong consistency). In the application, we are reading the rows based upon UPDATE_TIME_BIN (offsetStart and offsetEnd is the range) and FROM_ACCOUNT_ID_BIN equal to accountId. Once these rows are read, we are doing sum of resevebalance and sum of available balance. If request.amount is less than available balance then we are inserting the row int he same set.

For getting requires response time of less than 250ms, all the reads must be completed in 20-30ms because insert is taking approx 60-70ms due to network latency between sites. This is the reason, we prefer local reads (read from replica and prefer rack id - same as current DC).

Kindly advise.

9

Please see the code example in Fetch Secondary Index results from a Single Node using replication factor 128 - #7 by pgupta That will work for AP or SC, regardless. For Queries, once you specify PREFER_RACK, it will automatically use master or replica partitions on that rack to execute the query, whether it is AP mode or SC mode - no difference.

Specific code part:

//Instantiate client object with Preferred Rack ClientPolicy
//Here, this client is indicating, its preferred rack is with rack-id=1.
ClientPolicy cp = new ClientPolicy();
cp.rackId = 1;   //Next, changed to 2 and then 3, for testing. 
cp.rackAware = true;
AerospikeClient client = new AerospikeClient(cp, "localhost", 3000);

//Run SI query
Statement stmt = new Statement();
stmt.setNamespace("test");
stmt.setSetName("testset");
stmt.setFilter(Filter.range("age", 20,30));
QueryPolicy qp = new QueryPolicy();

//Specify query to use preferred rack
qp.replica = Replica.PREFER_RACK;

RecordSet rs = client.query(qp, stmt);

while(rs.next()){
    Record r = rs.getRecord();
    Key thisKey = rs.getKey();  
    System.out.println(r);
}

//Close this client
client.close();
1 Like
10

@pgupta We are getting following error for above mentioned code when we scale up to 35 threads in application:

com.aerospike.client.AerospikeException: Error 22,1,0,30000,0,5,BB9AFD1F394FC06 10.3.0.136 3000: Operation not allowed at this time

2024-05-16T18:02:24.974Z ERROR 1 — [debit] [io-8100-exec-43] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path threw exception [Request processing failed: com.aerospike.client.AerospikeException: Error 22,1,0,30000,0,5,BB9AFD1F394FC06 10.3.0.136 3000: Operation not allowed at this time] with root cause

Please advise.

11

This kind of stuff - which is operational issues and may require looking at logs etc - please open a support ticket. You must have Enterprise license - so access to support right?

1 Like