| /* |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, software |
| * distributed under the License is distributed on an "AS IS" BASIS, |
| * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| * See the License for the specific language governing permissions and |
| * limitations under the License. |
| */ |
| package org.apache.cassandra.cql3.validation.entities; |
| |
| import org.junit.Test; |
| import org.apache.cassandra.cql3.CQLTester; |
| |
| public class SecondaryIndexOnStaticColumnTest extends CQLTester |
| { |
| @Test |
| public void testSimpleStaticColumn() throws Throwable |
| { |
| createTable("CREATE TABLE %s (id int, name text, age int static, PRIMARY KEY (id, name))"); |
| |
| createIndex("CREATE INDEX static_age on %s(age)"); |
| int id1 = 1, id2 = 2, age1 = 24, age2 = 32; |
| String name1A = "Taylor", name1B = "Swift", |
| name2 = "Jamie"; |
| |
| execute("INSERT INTO %s (id, name, age) VALUES (?, ?, ?)", id1, name1A, age1); |
| execute("INSERT INTO %s (id, name, age) VALUES (?, ?, ?)", id1, name1B, age1); |
| execute("INSERT INTO %s (id, name, age) VALUES (?, ?, ?)", id2, name2, age2); |
| |
| assertRows(execute("SELECT id, name, age FROM %s WHERE age=?", age1), |
| row(id1, name1B, age1), row(id1, name1A, age1)); |
| assertRows(execute("SELECT id, name, age FROM %s WHERE age=?", age2), |
| row(id2, name2, age2)); |
| |
| // Update the rows. Validate that updated values will be reflected in the index. |
| int newAge1 = 40; |
| execute("UPDATE %s SET age = ? WHERE id = ?", newAge1, id1); |
| assertEmpty(execute("SELECT id, name, age FROM %s WHERE age=?", age1)); |
| assertRows(execute("SELECT id, name, age FROM %s WHERE age=?", newAge1), |
| row(id1, name1B, newAge1), row(id1, name1A, newAge1)); |
| execute("DELETE FROM %s WHERE id = ?", id2); |
| assertEmpty(execute("SELECT id, name, age FROM %s WHERE age=?", age2)); |
| } |
| |
| @Test |
| public void testIndexOnCompoundRowKey() throws Throwable |
| { |
| createTable("CREATE TABLE %s (interval text, seq int, id int, severity int static, PRIMARY KEY ((interval, seq), id) ) WITH CLUSTERING ORDER BY (id DESC)"); |
| |
| execute("CREATE INDEX ON %s (severity)"); |
| |
| execute("insert into %s (interval, seq, id , severity) values('t',1, 3, 10)"); |
| execute("insert into %s (interval, seq, id , severity) values('t',1, 4, 10)"); |
| execute("insert into %s (interval, seq, id , severity) values('t',2, 3, 10)"); |
| execute("insert into %s (interval, seq, id , severity) values('t',2, 4, 10)"); |
| execute("insert into %s (interval, seq, id , severity) values('m',1, 3, 11)"); |
| execute("insert into %s (interval, seq, id , severity) values('m',1, 4, 11)"); |
| execute("insert into %s (interval, seq, id , severity) values('m',2, 3, 11)"); |
| execute("insert into %s (interval, seq, id , severity) values('m',2, 4, 11)"); |
| |
| assertRows(execute("select * from %s where severity = 10 and interval = 't' and seq = 1"), |
| row("t", 1, 4, 10), row("t", 1, 3, 10)); |
| } |
| |
| @Test |
| public void testIndexOnCollections() throws Throwable |
| { |
| createTable("CREATE TABLE %s (k int, v int, l list<int> static, s set<text> static, m map<text, int> static, PRIMARY KEY (k, v))"); |
| |
| createIndex("CREATE INDEX ON %s (l)"); |
| createIndex("CREATE INDEX ON %s (s)"); |
| createIndex("CREATE INDEX ON %s (m)"); |
| createIndex("CREATE INDEX ON %s (keys(m))"); |
| |
| execute("INSERT INTO %s (k, v, l, s, m) VALUES (0, 0, [1, 2], {'a'}, {'a' : 1, 'b' : 2})"); |
| execute("INSERT INTO %s (k, v) VALUES (0, 1) "); |
| execute("INSERT INTO %s (k, v, l, s, m) VALUES (1, 0, [4, 5], {'d'}, {'b' : 1, 'c' : 4})"); |
| |
| // lists |
| assertRows(execute("SELECT k, v FROM %s WHERE l CONTAINS 1"), row(0, 0), row(0, 1)); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE k = 1 AND l CONTAINS 1")); |
| assertRows(execute("SELECT k, v FROM %s WHERE l CONTAINS 4"), row(1, 0)); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE l CONTAINS 6")); |
| |
| // update lists |
| execute("UPDATE %s SET l = l + [3] WHERE k = ?", 0); |
| assertRows(execute("SELECT k, v FROM %s WHERE l CONTAINS 3"), row(0, 0), row(0, 1)); |
| |
| // sets |
| assertRows(execute("SELECT k, v FROM %s WHERE s CONTAINS 'a'"), row(0, 0), row(0, 1)); |
| assertRows(execute("SELECT k, v FROM %s WHERE k = 0 AND s CONTAINS 'a'"), row(0, 0), row(0, 1)); |
| assertRows(execute("SELECT k, v FROM %s WHERE s CONTAINS 'd'"), row(1, 0)); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE s CONTAINS 'e'")); |
| |
| // update sets |
| execute("UPDATE %s SET s = s + {'b'} WHERE k = ?", 0); |
| assertRows(execute("SELECT k, v FROM %s WHERE s CONTAINS 'b'"), row(0, 0), row(0, 1)); |
| execute("UPDATE %s SET s = s - {'a'} WHERE k = ?", 0); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE s CONTAINS 'a'")); |
| |
| // maps |
| assertRows(execute("SELECT k, v FROM %s WHERE m CONTAINS 1"), row(1, 0), row(0, 0), row(0, 1)); |
| assertRows(execute("SELECT k, v FROM %s WHERE k = 0 AND m CONTAINS 1"), row(0, 0), row(0, 1)); |
| assertRows(execute("SELECT k, v FROM %s WHERE m CONTAINS 4"), row(1, 0)); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE m CONTAINS 5")); |
| |
| assertRows(execute("SELECT k, v FROM %s WHERE m CONTAINS KEY 'b'"), row(1, 0), row(0, 0), row(0, 1)); |
| assertRows(execute("SELECT k, v FROM %s WHERE k = 0 AND m CONTAINS KEY 'b'"), row(0, 0), row(0, 1)); |
| assertRows(execute("SELECT k, v FROM %s WHERE m CONTAINS KEY 'c'"), row(1, 0)); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE m CONTAINS KEY 'd'")); |
| |
| // update maps. |
| execute("UPDATE %s SET m['c'] = 5 WHERE k = 0"); |
| assertRows(execute("SELECT k, v FROM %s WHERE m CONTAINS 5"), row(0, 0), row(0, 1)); |
| assertRows(execute("SELECT k, v FROM %s WHERE m CONTAINS KEY 'c'"), row(1, 0), row(0, 0), row(0, 1)); |
| execute("DELETE m['a'] FROM %s WHERE k = 0"); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE m CONTAINS KEY 'a'")); |
| } |
| |
| @Test |
| public void testIndexOnFrozenCollections() throws Throwable |
| { |
| createTable("CREATE TABLE %s (k int, v int, l frozen<list<int>> static, s frozen<set<text>> static, m frozen<map<text, int>> static, PRIMARY KEY (k, v))"); |
| |
| createIndex("CREATE INDEX ON %s (FULL(l))"); |
| createIndex("CREATE INDEX ON %s (FULL(s))"); |
| createIndex("CREATE INDEX ON %s (FULL(m))"); |
| |
| execute("INSERT INTO %s (k, v, l, s, m) VALUES (0, 0, [1, 2], {'a'}, {'a' : 1, 'b' : 2})"); |
| execute("INSERT INTO %s (k, v) VALUES (0, 1) "); |
| execute("INSERT INTO %s (k, v, l, s, m) VALUES (1, 0, [4, 5], {'d'}, {'b' : 1, 'c' : 4})"); |
| execute("UPDATE %s SET l=[3], s={'3'}, m={'3': 3} WHERE k=3" ); |
| |
| // lists |
| assertRows(execute("SELECT k, v FROM %s WHERE l = [1, 2]"), row(0, 0), row(0, 1)); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE k = 1 AND l = [1, 2]")); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE l = [4]")); |
| assertRows(execute("SELECT k, v FROM %s WHERE l = [3]"), row(3, null)); |
| |
| // update lists |
| execute("UPDATE %s SET l = [1, 2, 3] WHERE k = ?", 0); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE l = [1, 2]")); |
| assertRows(execute("SELECT k, v FROM %s WHERE l = [1, 2, 3]"), row(0, 0), row(0, 1)); |
| |
| // sets |
| assertRows(execute("SELECT k, v FROM %s WHERE s = {'a'}"), row(0, 0), row(0, 1)); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE k = 1 AND s = {'a'}")); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE s = {'b'}")); |
| assertRows(execute("SELECT k, v FROM %s WHERE s = {'3'}"), row(3, null)); |
| |
| // update sets |
| execute("UPDATE %s SET s = {'a', 'b'} WHERE k = ?", 0); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE s = {'a'}")); |
| assertRows(execute("SELECT k, v FROM %s WHERE s = {'a', 'b'}"), row(0, 0), row(0, 1)); |
| |
| // maps |
| assertRows(execute("SELECT k, v FROM %s WHERE m = {'a' : 1, 'b' : 2}"), row(0, 0), row(0, 1)); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE k = 1 AND m = {'a' : 1, 'b' : 2}")); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE m = {'a' : 1, 'b' : 3}")); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE m = {'a' : 1, 'c' : 2}")); |
| assertRows(execute("SELECT k, v FROM %s WHERE m = {'3': 3}"), row(3, null)); |
| |
| // update maps. |
| execute("UPDATE %s SET m = {'a': 2, 'b': 3} WHERE k = ?", 0); |
| assertEmpty(execute("SELECT k, v FROM %s WHERE m = {'a': 1, 'b': 2}")); |
| assertRows(execute("SELECT k, v FROM %s WHERE m = {'a': 2, 'b': 3}"), row(0, 0), row(0, 1)); |
| } |
| |
| @Test |
| public void testStaticIndexAndNonStaticIndex() throws Throwable |
| { |
| createTable("CREATE TABLE %s (id int, company text, age int static, salary int, PRIMARY KEY(id, company))"); |
| createIndex("CREATE INDEX on %s(age)"); |
| createIndex("CREATE INDEX on %s(salary)"); |
| |
| String company1 = "company1", company2 = "company2"; |
| |
| execute("INSERT INTO %s(id, company, age, salary) VALUES(?, ?, ?, ?)", 1, company1, 20, 1000); |
| execute("INSERT INTO %s(id, company, salary) VALUES(?, ?, ?)", 1, company2, 2000); |
| execute("INSERT INTO %s(id, company, age, salary) VALUES(?, ?, ?, ?)", 2, company1, 40, 2000); |
| |
| assertRows(execute("SELECT id, company, age, salary FROM %s WHERE age = 20 AND salary = 2000 ALLOW FILTERING"), |
| row(1, company2, 20, 2000)); |
| } |
| |
| @Test |
| public void testIndexOnUDT() throws Throwable |
| { |
| String typeName = createType("CREATE TYPE %s (street text, city text)"); |
| |
| createTable(String.format( |
| "CREATE TABLE %%s (id int, company text, home frozen<%s> static, price int, PRIMARY KEY(id, company))", |
| typeName)); |
| createIndex("CREATE INDEX on %s(home)"); |
| |
| String addressString = "{street: 'Centre', city: 'C'}"; |
| String companyName = "Random"; |
| |
| execute("INSERT INTO %s(id, company, home, price) " |
| + "VALUES(1, '" + companyName + "', " + addressString + ", 10000)"); |
| assertRows(execute("SELECT id, company FROM %s WHERE home = " + addressString), row(1, companyName)); |
| String newAddressString = "{street: 'Fifth', city: 'P'}"; |
| |
| execute("UPDATE %s SET home = " + newAddressString + " WHERE id = 1"); |
| assertEmpty(execute("SELECT id, company FROM %s WHERE home = " + addressString)); |
| assertRows(execute("SELECT id, company FROM %s WHERE home = " + newAddressString), row(1, companyName)); |
| } |
| } |