Empty Resultset for IN clause on primary key using accessor-annotated interface

[Girish]

Hi
Am using Cassandra 2.2.0 with CQL spec 3.3.0 and Native protocol v4
This has support for IN clause on primary key.

When running the query on CQL, it gives the below result:
------------------------------------------------------------------------------------
cqlsh:testkeyspace> select * from roles_users where role IN ('ROLE_QA','ROLE_TESTER');

 role                   |   username
-------------------------+---------------------
         ROLE_QA | te...@abc.com
         ROLE_QA | te...@abc.com
         ROLE_QA | te...@abc.com
 ROLE_TESTER | te...@abc.com
 ROLE_TESTER | te...@abc.com

(5 rows)

But when I try to use accessor annotated interfaces for the same above query, I just get empty resultset.

Below are the logs generated for 2 scenarios:


1 role (ROLE_QA), get list of users
2015-10-30 13:47:03,159 [http-bio-8080-exec-1] DEBUG com.test.service.UserService - roles: ROLE_QA
2015-10-30 13:47:03,173 [cluster1-nio-worker-1] DEBUG com.datastax.driver.core.QueryLogger.NORMAL - [cluster1] [/127.0.0.1:9042] Query completed normally, took 10 ms: [1 bound values] SELECT * FROM testkeyspace.roles_users WHERE role =:roles;
2015-10-30 13:47:03,174 [http-bio-8080-exec-1] DEBUG com.test.service.UserService - roleusersList: [RoleUser [role=ROLE_QA, username=te...@abc.com], RoleUser [role=ROLE_QA, username=te...@abc.com], RoleUser [role=ROLE_QA, username=te...@abc.com]]

2 roles (ROLE_QA, ROLE_TESTER), get list of users
2015-10-30 13:42:29,609 [http-bio-8080-exec-1] DEBUG com.test.service.UserService - roles: [ROLE_QA, ROLE_TESTER]
2015-10-30 13:42:29,628 [cluster1-nio-worker-1] DEBUG com.datastax.driver.core.QueryLogger.NORMAL - [cluster1] [/127.0.0.1:9042] Query completed normally, took 14 ms: [1 bound values] SELECT * FROM testkeyspace.roles_users WHERE role IN (:roles);
2015-10-30 13:42:29,630 [http-bio-8080-exec-1] DEBUG com.test.service.UserService - roleusersList: []

@Accessor
public interface RoleUserAccessor {

    @Query("SELECT * FROM testkeyspace.roles_users WHERE role IN (:roles)")
    Result<RoleUser> getUsersByRoles(@Param("roles") List<String> roles); // this returns me empty resultset

    @Query("SELECT * FROM testkeyspace.roles_users WHERE role =:role")
    Result<RoleUser> getUsersByRole(@Param("role") String role); //this returns me list of RoleUser objects

}

CQL Table:
----------
CREATE TABLE testkeyspace.roles_users (
    role text,
    username text,
    PRIMARY KEY (role, username)
) WITH CLUSTERING ORDER BY (username ASC);

Does java driver 2.1.8, Accessor-annotated interfaces support IN clauses on primary key.

Let me know if any of the recent java driver has the support for IN clause on primary key.

[Olivier Michallat]

Hi,

You need to remove the parentheses since you want to bind the whole list, not a value inside the list:

    @Query("SELECT * FROM testkeyspace.roles_users WHERE role IN :roles")

    Result<RoleUser> getUsersByRoles(@Param("roles") List<String> roles);

The other version should give you an error though, I'll open a ticket to investigate the issue.

--

Olivier Michallat

Driver & tools engineer, DataStax

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

[Olivier Michallat]

Created JAVA-974.

[Girish]

Thanks Olivier
Removing the paranthesis fixed the issue :)

[Krishna Sanjay]

Hi

i want to write a accessor interface like this way

Query("select * from employee where empid=:empid and (empdevices, initialtime)>=:(empdevices, initialtime) and (empdevices, initialtime)<=:(empdevices, initialtime)")

this query working fine with cqlsh

But in java code i am getting issues

Thanks in advance