Usage of IN at creating materialized view

[Alptug Tokgoz <al...@matriksdata.com>]

Hello, 


I have struggled with the following problem for last a couple of days. First of all I am using cqlsh 5.0.1 ,Cassandra 3.11.1 and CQL spec 3.4.4.


What I am trying to do is creating a materialized view, named income_periods from a table named income. The problem is, i am able to create the materialized view table without an error but the table does not meet the expected feature which is i can not get the rows for item in ('BFG','GEL').  My code is here:
CREATE MATERIALIZED VIEW income_periods AS
       SELECT symbol,period,item FROM income
       WHERE period IS NOT NULL AND symbol IS NOT NULL AND item in ('BFG'; 'GEL')
       PRIMARY KEY (symbol, period, item);

Have the log;

INFO  [Native-Transport-Requests-3] 2019-05-27 13:07:50,121 MigrationManager.java:369 - Create new view: org.apache.cassandra.config.ViewDefinition@4501ac19[ksName=mtxfundamentals,viewName=income_periods,baseTableId=0375b280-42e1-11e8-871f-dfe7f527f8be,baseTableName=income,includeAllColumns=false,whereClause=period IS NOT NULL AND symbol IS NOT NULL AND item IN ('BFG', 'GEL'),metadata=org.apache.cassandra.config.CFMetaData@365eed1c[cfId=6e008d90-8080-11e9-b077-ebc67cf7305e,ksName=mtxfundamentals,cfName=income_periods,flags=[COMPOUND],params=TableParams{comment=, read_repair_chance=0.0, dclocal_read_repair_chance=0.1, bloom_filter_fp_chance=0.01, crc_check_chance=1.0, gc_grace_seconds=864000, default_time_to_live=0, memtable_flush_period_in_ms=0, min_index_interval=128, max_index_interval=2048, speculative_retry=99PERCENTILE, caching={'keys' : 'ALL', 'rows_per_partition' : 'NONE'},
compaction=CompactionParams{class=org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy, options={min_threshold=4, max_threshold=32}}, compression=org.apache.cassandra.schema.CompressionParams@fcf27507, extensions={}, cdc=false},comparator=comparator(org.apache.cassandra.db.marshal.Int32Type, org.apache.cassandra.db.marshal.UTF8Type),partitionColumns=[[] | []],partitionKeyColumns=[symbol],clusteringColumns=[period, item],keyValidator=org.apache.cassandra.db.marshal.UTF8Type,columnMetadata=[period, symbol, item],droppedColumns={},triggers=[],indexes=[]]]
INFO  [MigrationStage:1] 2019-05-27 13:07:50,312 ColumnFamilyStore.java:408 - Initializing mtxfundamentals.income_periods
WARN  [CompactionExecutor:5838] 2019-05-27 13:07:50,328 ViewBuilder.java:189 - Materialized View failed to complete, sleeping 5 minutes before restarting
org.apache.cassandra.exceptions.InvalidRequestException: IN restrictions are not supported on indexed columns
    at org.apache.cassandra.cql3.statements.RequestValidations.invalidRequest(RequestValidations.java:199) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.cql3.restrictions.SingleColumnRestriction$INRestriction.addRowFilterTo(SingleColumnRestriction.java:222) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.cql3.restrictions.ClusteringColumnRestrictions.addRowFilterTo(ClusteringColumnRestrictions.java:212) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.cql3.restrictions.StatementRestrictions.getRowFilter(StatementRestrictions.java:626) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.cql3.statements.SelectStatement.getRowFilter(SelectStatement.java:776) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.cql3.statements.SelectStatement.getRangeCommand(SelectStatement.java:587) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.cql3.statements.SelectStatement.getQuery(SelectStatement.java:305) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.cql3.statements.SelectStatement.getQuery(SelectStatement.java:295) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.db.view.View.getReadQuery(View.java:185) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.db.view.ViewBuilder.buildKey(ViewBuilder.java:75) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.db.view.ViewBuilder.run(ViewBuilder.java:158) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.db.compaction.CompactionManager$16.run(CompactionManager.java:1719) [apache-cassandra-3.11.1.jar:3.11.1]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_102]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_102]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_102]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_102]
    at org.apache.cassandra.concurrent.NamedThreadFactory.lambda$threadLocalDeallocator$0(NamedThreadFactory.java:81) [apache-cassandra-3.11.1.jar:3.11.1]
    at java.lang.Thread.run(Thread.java:745) ~[na:1.8.0_102]


I also tried to work only with the SELECT statement as the following, and i was able to select the values as 'BFG' or 'GEL' of item column.

       SELECT * FROM income WHERE period='something' AND symbol='something' AND item IN ('BFG', 'GEL')


Besides, I am able to create the materialized view table as filled if i use one of the items that i want 
CREATE MATERIALIZED VIEW income_periods AS
       SELECT symbol,period,item FROM income
       WHERE period IS NOT NULL AND symbol IS NOT NULL AND item='BFG'
       PRIMARY KEY (symbol, period, item);


Last of all, i observed that the  item in ('BFG', 'GEL') statement works like item='BFG' OR item='GEL' at SELECT, on the other hand it works like item='BFG' AND item='GEL' in materialized view where clause.

Are there any ideas to fix this issue?