CODEWITHSUNDEEP
cassandracassandra-3.0
Rimander
Rimander

Reputation: 131

Cassandra IS NOT NULL Unsupported restriction

The materialized view is not populated when I run it. I think it's because the SQL query generates errors.

select * from table where id is not null

InvalidRequest: Error from server: code=2200 [Invalid query] message="Unsupported restriction: id IS NOT NULL"

Previously this SQL query was that let me place is not null. I think that when I do an update of the system this fails me.

Upvotes: 0

Views: 3517

Answers (1)

Highstead
Highstead

Reputation: 2441

So cassandra's where clause support =, <, >, >=, <=. There is no support for not null or != null. It is worth noting the only time IS NOT NULL exists is within the materialized view. This is because you cannot have a null in your primary key and in this case you're coercing variables that might be otherwise unset INTO a primary key. Additionally you do need to include all of the primary key from the base table in your materialized view.

Example Materialized View

CREATE KEYSPACE test_keyspace
WITH REPLICATION = { 'class': 'SimpleStrategy', 'replication_factor': 1};

CREATE TABLE myTable(
  id int, 
  col1 varchar, 
  col2 varchar, 
PRIMARY KEY(id, col1)) ;

CREATE MATERIALIZED VIEW test_view AS 
  SELECT col2, col1, id FROM myTable 
  WHERE col2 IS NOT NULL AND col1 IS NOT NULL
PRIMARY KEY(col2, id, col1);

It should be noted materialized views come with some performance implications and you may be better off rolling your own.

Upvotes: 2

Related Questions