Cassandra secondary index issue - returning zero rows

Question

I have a problem in that a secondary index is returning zero rows in cassandra:

I'm following along the getting started docs:

http://www.datastax.com/documentation/getting_started/doc/getting_started/gettingStartedCQL.html

Based on that I have the following cassandra script

/* hello.cql */
drop keyspace test;
CREATE KEYSPACE test WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
use test;
CREATE TABLE users (  user_id int PRIMARY KEY,   fname text,   lname text);
DESCRIBE TABLES;
INSERT INTO users (user_id,  fname, lname) 
  VALUES (1745, 'john', 'smith');
INSERT INTO users (user_id,  fname, lname) 
  VALUES (1744, 'john', 'doe');
INSERT INTO users (user_id,  fname, lname) 
  VALUES (1746, 'john', 'smith');
SELECT * FROM users;
CREATE INDEX ON users (lname);

/* These queries both return 0 rows ??? */
SELECT * FROM users WHERE lname = 'smith';
SELECT * FROM users WHERE lname = 'doe';

However...

cqlsh < hello.cql 

users


 user_id | fname | lname
---------+-------+-------
    1745 |  john | smith
    1744 |  john |   doe
    1746 |  john | smith

(3 rows)

(0 rows)

(0 rows)

This should be straightforward -- am I missing something?

Answer 1

For the 2 SELECT queries to return results would mean that the CREATE INDEX would execute synchronously and it will only return after all existing data would be indexed.

If you change the order in the script to have the index defined before you insert any data, I'd expect the 2 selects to return results.

Answer 2

Using Cassandra 2.1.0, I get results regardless of whether the index is created before or after data is inserted.

    Connected to Test Cluster at 127.0.0.1:9042.
    [cqlsh 5.0.1 | Cassandra 2.1.0 | CQL spec 3.2.0 | Native protocol v3]
    Use HELP for help.
    cqlsh> 
    cqlsh> CREATE KEYSPACE test WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
    cqlsh> use test;
    cqlsh:test> CREATE TABLE users (  user_id int PRIMARY KEY,   fname text,   lname text);
    cqlsh:test> INSERT INTO users (user_id,  fname, lname) 
        ...   VALUES (1745, 'john', 'smith');
    cqlsh:test> INSERT INTO users (user_id,  fname, lname) 
        ...   VALUES (1744, 'john', 'doe');
    cqlsh:test> INSERT INTO users (user_id,  fname, lname) 
        ...   VALUES (1746, 'john', 'smith');
    cqlsh:test> CREATE INDEX ON users (lname);
    cqlsh:test> SELECT * FROM users WHERE lname = 'smith';

     user_id | fname | lname
    ---------+-------+-------
        1745 |  john | smith
        1746 |  john | smith

    (2 rows)

    cqlsh:test> SELECT * FROM users WHERE lname = 'doe';

     user_id | fname | lname
    ---------+-------+-------
        1744 |  john |   doe

    (1 rows)

Answer 3

Here's the platform and version info for my installation:

john@piggies:~/Dropbox/source/casandra$ nodetool -h localhost version
ReleaseVersion: 2.0.10
john@piggies:~/Dropbox/source/casandra$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 14.04.1 LTS
Release:    14.04
Codename:   trusty
john@piggies:~/Dropbox/source/casandra$ ^C
john@piggies:~/Dropbox/source/casandra$