[CASSANDRA-13547] Filtered materialized views missing data

• Type: Bug
• Status: Resolved
• Priority: Urgent
• Resolution: Duplicate
• Fix Version/s: 3.11.5
• Component/s: Feature/Materialized Views
• Labels: materializedviews

Environment

Official Cassandra 3.10 Docker image (ID 154b919bf8ce).

• Severity: Critical
• Since Version: 3.10
• Assignee: Krishna Dattu Koneru
• Reporter: Craig Nicholson

Authors

Krishna Dattu Koneru

Reviewers

Zhao Yang

• Created: 23/May/17 14:44
• Updated: 01/Aug/21 11:28
• Resolved: 26/Jul/17 09:55

Description

When creating a materialized view against a base table the materialized view does not always reflect the correct data.

Using the following test schema:

Schema

DROP KEYSPACE IF EXISTS test;
CREATE KEYSPACE test
  WITH REPLICATION = { 
   'class' : 'SimpleStrategy', 
   'replication_factor' : 1 
  };
CREATE TABLE test.table1 (
                id int,
                name text,
                enabled boolean,
                foo text,
                PRIMARY KEY (id, name));
CREATE MATERIALIZED VIEW test.table1_mv1 AS SELECT id, name, foo
                FROM test.table1
                WHERE id IS NOT NULL 
                AND name IS NOT NULL 
                AND enabled = TRUE
                PRIMARY KEY ((name), id);
CREATE MATERIALIZED VIEW test.table1_mv2 AS SELECT id, name, foo, enabled
                FROM test.table1
                WHERE id IS NOT NULL 
                AND name IS NOT NULL 
                AND enabled = TRUE
                PRIMARY KEY ((name), id);

When I insert a row into the base table the materialized views are updated appropriately.

Insert row

cqlsh> INSERT INTO test.table1 (id, name, enabled, foo) VALUES (1, 'One', TRUE, 'Bar');
cqlsh> SELECT * FROM test.table1;

 id | name | enabled | foo
----+------+---------+-----
  1 |  One |    True | Bar

(1 rows)
cqlsh> SELECT * FROM test.table1_mv1;

 name | id | foo
------+----+-----
  One |  1 | Bar

(1 rows)
cqlsh> SELECT * FROM test.table1_mv2;

 name | id | enabled | foo
------+----+---------+-----
  One |  1 |    True | Bar

(1 rows)

Updating the record in the base table and setting enabled to FALSE will filter the record from both materialized views.

Disable the row

cqlsh> UPDATE test.table1 SET enabled = FALSE WHERE id = 1 AND name = 'One';
cqlsh> SELECT * FROM test.table1;

 id | name | enabled | foo
----+------+---------+-----
  1 |  One |   False | Bar

(1 rows)
cqlsh> SELECT * FROM test.table1_mv1;

 name | id | foo
------+----+-----

(0 rows)
cqlsh> SELECT * FROM test.table1_mv2;

 name | id | enabled | foo
------+----+---------+-----

(0 rows)

However a further update to the base table setting enabled to TRUE should include the record in both materialzed views, however only one view (table1_mv2) gets updated.
It appears that only the view (table1_mv2) that returns the filtered column (enabled) is updated.
Additionally columns that are not part of the partiion or clustering key are not updated. You can see that the foo column has a null value in table1_mv2.

Enable the row

cqlsh> UPDATE test.table1 SET enabled = TRUE WHERE id = 1 AND name = 'One';
cqlsh> SELECT * FROM test.table1;

 id | name | enabled | foo
----+------+---------+-----
  1 |  One |    True | Bar

(1 rows)
cqlsh> SELECT * FROM test.table1_mv1;

 name | id | foo
------+----+-----

(0 rows)
cqlsh> SELECT * FROM test.table1_mv2;

 name | id | enabled | foo
------+----+---------+------
  One |  1 |    True | null

(1 rows)

Issue Links

• is duplicated by: CASSANDRA-11500 Obsolete MV entry may not be properly deleted
• is related to: CASSANDRA-11500 Obsolete MV entry may not be properly deleted
• is related to: CASSANDRA-13127 Materialized Views: View row expires too soon
• is related to: CASSANDRA-13409 Materialized Views: View cells are resurrected
• relates to: CASSANDRA-10368 Support Restricting non-PK Cols in Materialized View Select Statements