Cassandra IN Query using Accessor Interface with 2 INs

[Andrei]

Hi,

This select in an accessor

SELECT * FROM my_table WHERE (id IN :ids) and (user IN :users)" 

returns always zero size list.

 I tried it like this as well

SELECT * FROM my_table WHERE id IN :ids and user IN :users

or 

SELECT * FROM my_table WHERE id IN ? and user IN ?

my_table has user as partition key and id as cluster key

create table if not exists my_table (id uuid,
    name text,user text, primary key ((user),id))


SELECT * FROM my_table WHERE id IN ?  allow filtering; worked but allow filtering is not ok for production.

Any idea why this happens? I tried the select from DBEAVER and it worked with no warnings so I imagined this is a valid select for production. 

Thank you,
Andrei

[Olivier Michallat]

Hi Andrei,

I can't reproduce your issue, the accessor returns some results for me. See my test program below (I've simplified the schema for convenience, but this shouldn't influence the results).

Regarding ALLOW FILTERING, this happens because the query is inefficient: the only way for Apache Cassandra to execute it is to scan and filter all partitions. See this blog post for more explanations. 

------

import com.datastax.driver.core.Cluster;

import com.datastax.driver.core.ResultSet;

import com.datastax.driver.core.Session;

import com.datastax.driver.mapping.MappingManager;

import com.datastax.driver.mapping.annotations.Accessor;

import com.datastax.driver.mapping.annotations.Param;

import com.datastax.driver.mapping.annotations.Query;

import com.google.common.collect.Lists;

import java.util.List;

public class Tmp {

    public static void main(String[] args) {

        Cluster cluster = null;

        try {

            cluster = Cluster.builder()

                    .addContactPoint("127.0.0.1")

                    .build();

            Session session = cluster.connect();

            createAndPopulateSchema(session);

            MappingManager manager = new MappingManager(session);

            MyAccessor accessor = manager.createAccessor(MyAccessor.class);

            ResultSet rs = accessor.getUsers(Lists.newArrayList(1), Lists.newArrayList("user1", "user2"));

            System.out.println(rs.all().size()); // prints 2 as expected

        } finally {

            if (cluster != null) cluster.close();

        }

    }

    @Accessor

    public interface MyAccessor {

        @Query("SELECT * FROM test.my_table WHERE id IN :ids AND user IN :users")

        ResultSet getUsers(@Param("ids") List<Integer> ids, @Param("users") List<String> users);

    }

    private static void createAndPopulateSchema(Session session) {

        session.execute("CREATE KEYSPACE IF NOT EXISTS test WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1}");

        session.execute("create table if not exists test.my_table (id int, user text, primary key ((user),id))");

        session.execute("insert into test.my_table (user, id) values ('user1', 1)");

        session.execute("insert into test.my_table (user, id) values ('user1', 2)");

        session.execute("insert into test.my_table (user, id) values ('user2', 1)");

        session.execute("insert into test.my_table (user, id) values ('user2', 2)");

    }

}

--

Olivier Michallat

Driver & tools engineer, DataStax

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com.

[Mayank Rathore]

Hi Oliver,

How we can do metering data read by accessors, can you share some samples? 

Thanks in advance.

Thanks

Mayank

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