CODEWITHSUNDEEP
databasecassandracqldatastax-java-driver
chg87
chg87

Reputation: 43

Cassandra 2.0.6: Higher number of results when adding AND to query

We are getting "hidden results" for a query with an AND conjuction of a secondary index after we deleted the all rows of the table with a certain projectId (the primary key) using datastax Querybuilder.

Example:

primary key:

secondary indices:

The query with AND:

SELECT * FROM a.test WHERE projectId='test' AND appVersion=1 AND viewState='FeedListActivity';

results 1 row

Without:

SELECT * FROM a.test WHERE projectId='test' AND appVersion=1;

results 0 rows.

We were asuming that it might have something to do with Cassandras Tombstones. So we executed nodetool repair and compact and restarted the cassandra service and the server -> Nothing changed.

Running on:

Table properties (describe tables):

WITH
bloom_filter_fp_chance=0.010000 AND
caching='KEYS_ONLY' AND
comment='' AND
dclocal_read_repair_chance=0.000000 AND
gc_grace_seconds=0 AND
index_interval=128 AND
read_repair_chance=0.100000 AND
replicate_on_write='true' AND
populate_io_cache_on_flush='false' AND
default_time_to_live=0 AND
speculative_retry='99.0PERCENTILE' AND
memtable_flush_period_in_ms=0 AND
compaction={'class': 'SizeTieredCompactionStrategy'} AND
compression={'sstable_compression': 'LZ4Compressor'};

Workaround: We copied the whole content to a identical table, truncated the old one and copied the data back. Now we get for both queries one result.

Question

Is there a correct way where we do not need to use the workaround? Are we missing something?

Thanks in advance...

Added table creation process

DROP TABLE IF EXISTS a.test;
CREATE TABLE a.test (
  projectid text,  
  appversion int,  
  viewstate text,
  PRIMARY KEY (projectid)
);

CREATE INDEX ON a.test (appversion);
CREATE INDEX ON a.test (viewstate);

Upvotes: 1

Views: 75

Answers (1)

Aaron
Aaron

Reputation: 57843

I'm going to guess that your secondary index on appversion got out-of sync. This shouldn't happen often, but if it happens again, try rebuilding the index with NodeTool rebuild_index.

nodetool rebuild_index a test

That should rebuild all of the secondary indexes on the a.test table. If you have the name of the index on appversion, you can add that as the final parameter to just rebuild that particular index.

Also, just wondering, but have you tried building the indexed columns as clustering columns on the primary key?

PRIMARY KEY((projectid),appversion,viewstate)

This primary key definition will allow both of your queries (mentioned above) to work. It should also be faster than using secondary indexes, and you won't have to worry about it getting out-of-sync again.

Upvotes: 2

Related Questions