Replace Null function in Cassandra Select Query

Question

A set of records are to be selected from a cassandra table.

select a1,a2,a3 from tableB where solr_query='...';

Where some of a1 (int type) are null.

1. In Oracle, we could use nvl(a1,0).
2. In MS SQL Server, we could use isnull(a1,0).

I don't know the same to handle in cassandra way.

How I handle this in Java :

Create TableBObject class , in which all attributes are mapped with tableB (cassandra table).

Create Java accessor interface with the accessor method

@Accessor
public interface TableBAccessor
{
    @Query(select a1,a2,a3 from tableB where solr_query='...';)
    Result<TableBObject> getTableOnSolrQuery (@Param("solrQuery") String solrQuery);

}

In my calling method, I have created the mappingManager and used :

TableBAccessor tableBAccessor = mappingManager.createAccessor();

    List<TableBObject> tabBList = tableBAccessor.getTableOnSolrQuery("someSolrQuery").all();

    for(TableBObject tabB : tabBList){

    int a1 = tabB.getA1(); /*a1 column in cassandra table*/

    if(a1!=null){
    // replace a1 by some int value (zero / something else)

    }
  }

Above way, I am handling the same in Java.

It is consuming additional effort to me.

What's the solution in cassandra other than handling the same in Java Code ?

**Can I not write like : **

select isnull(a1,0),a2,a3 from tableB where solr_query='...'; or

select nvl(a1,0),a2,a3 from tableB where solr_query='...';

??

**My Question Is : **

If Yes, then how ? If not, then why ?

Answer 1

There is no built-in support for functions like nvl in Cassandra.

In theory, since Cassandra 2.2 you could define an UDF (user-defined function) that does what you are looking for; however, currently it is not possible to call functions passing literals as arguments, so nvl(a1,0) would throw an error; this limitation will go away with CASSANDRA-10783.

In the meanwhile, you should either avoid storing nulls in the first place, or apply the function client-side.

Answer 2

I found here that Cassandra doesn't support querying based on null, even for secondary indexes.

You may need to adapt the given solution to your case: have another boolean column called a1_uninitialized defaulted to true, that will help you handle the empty values.