blob: c2bfe9995125a636e051a1c3d446c561a9255712 [file] [log] [blame]
import itertools
import math
import random
import struct
import time
import pytest
import logging
from collections import OrderedDict
from distutils.version import LooseVersion
from uuid import UUID, uuid4
from cassandra import ConsistencyLevel, InvalidRequest
from cassandra.concurrent import execute_concurrent_with_args
from cassandra.protocol import ProtocolException, SyntaxException
from cassandra.query import SimpleStatement
from cassandra.util import sortedset
from dtest import RUN_STATIC_UPGRADE_MATRIX, MAJOR_VERSION_4
from thrift_bindings.thrift010.ttypes import \
ConsistencyLevel as ThriftConsistencyLevel
from thrift_bindings.thrift010.ttypes import (CfDef, Column, ColumnDef,
ColumnOrSuperColumn, ColumnParent,
Deletion, Mutation, SlicePredicate,
SliceRange)
from thrift_test import get_thrift_client
from tools.assertions import (assert_all, assert_invalid, assert_length_equal,
assert_none, assert_one, assert_row_count)
from tools.data import rows_to_list
from tools.misc import add_skip
from .upgrade_base import UpgradeTester
from .upgrade_manifest import build_upgrade_pairs, CASSANDRA_4_0
since = pytest.mark.since
logger = logging.getLogger(__name__)
@pytest.mark.upgrade_test
class TestCQL(UpgradeTester):
def is_40_or_greater(self):
return LooseVersion(self.UPGRADE_PATH.upgrade_meta.family) >= CASSANDRA_4_0
def test_static_cf(self):
""" Test static CF syntax """
cursor = self.prepare()
# Create
cursor.execute("""
CREATE TABLE users (
userid uuid PRIMARY KEY,
firstname text,
lastname text,
age int
);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE users")
# Inserts
cursor.execute("INSERT INTO users (userid, firstname, lastname, age) VALUES (550e8400-e29b-41d4-a716-446655440000, 'Frodo', 'Baggins', 32)")
cursor.execute("UPDATE users SET firstname = 'Samwise', lastname = 'Gamgee', age = 33 WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479")
# Queries
assert_one(cursor, "SELECT firstname, lastname FROM users WHERE userid = 550e8400-e29b-41d4-a716-446655440000", ['Frodo', 'Baggins'])
assert_one(cursor, "SELECT * FROM users WHERE userid = 550e8400-e29b-41d4-a716-446655440000", [UUID('550e8400-e29b-41d4-a716-446655440000'), 32, 'Frodo', 'Baggins'])
assert_all(cursor, "SELECT * FROM users", [[UUID('f47ac10b-58cc-4372-a567-0e02b2c3d479'), 33, 'Samwise', 'Gamgee'],
[UUID('550e8400-e29b-41d4-a716-446655440000'), 32, 'Frodo', 'Baggins']])
# Test batch inserts
cursor.execute("""
BEGIN BATCH
INSERT INTO users (userid, age) VALUES (550e8400-e29b-41d4-a716-446655440000, 36)
UPDATE users SET age = 37 WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479
DELETE firstname, lastname FROM users WHERE userid = 550e8400-e29b-41d4-a716-446655440000
DELETE firstname, lastname FROM users WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479
APPLY BATCH
""")
assert_all(cursor, "SELECT * FROM users", [[UUID('f47ac10b-58cc-4372-a567-0e02b2c3d479'), 37, None, None], [UUID('550e8400-e29b-41d4-a716-446655440000'), 36, None, None]])
@since('2.0', max_version='2.99') # 3.0+ not compatible with protocol version 2
def test_large_collection_errors(self):
""" For large collections, make sure that we are printing warnings """
for version in self.get_node_versions():
if version >= '3.0':
pytest.skip('version {} not compatible with protocol version 2'.format(version))
# We only warn with protocol 2
cursor = self.prepare(protocol_version=2)
cluster = self.cluster
node1 = cluster.nodelist()[0]
self.fixture_dtest_setup.ignore_log_patterns = ["Detected collection for table"]
cursor.execute("""
CREATE TABLE maps (
userid text PRIMARY KEY,
properties map<int, text>
);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE maps")
# Insert more than the max, which is 65535
for i in range(70000):
cursor.execute("UPDATE maps SET properties[{}] = 'x' WHERE userid = 'user'".format(i))
# Query for the data and throw exception
cursor.execute("SELECT properties FROM maps WHERE userid = 'user'")
node1.watch_log_for("Detected collection for table ks.maps with 70000 elements, more than the 65535 limit. "
"Only the first 65535 elements will be returned to the client. "
"Please see http://cassandra.apache.org/doc/cql3/CQL.html#collections for more details.")
def test_noncomposite_static_cf(self):
""" Test non-composite static CF syntax """
cursor = self.prepare()
# Create
cursor.execute("""
CREATE TABLE users (
userid uuid PRIMARY KEY,
firstname ascii,
lastname ascii,
age int
) WITH COMPACT STORAGE;
""")
#4.0 doesn't support compact storage
if self.is_40_or_greater():
cursor.execute("ALTER TABLE users DROP COMPACT STORAGE;")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE users")
# Inserts
cursor.execute("INSERT INTO users (userid, firstname, lastname, age) VALUES (550e8400-e29b-41d4-a716-446655440000, 'Frodo', 'Baggins', 32)")
cursor.execute("UPDATE users SET firstname = 'Samwise', lastname = 'Gamgee', age = 33 WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479")
# Queries
assert_one(cursor, "SELECT firstname, lastname FROM users WHERE userid = 550e8400-e29b-41d4-a716-446655440000", ['Frodo', 'Baggins'])
if self.is_40_or_greater():
assert_one(cursor, "SELECT * FROM users WHERE userid = 550e8400-e29b-41d4-a716-446655440000",
[UUID('550e8400-e29b-41d4-a716-446655440000'), None, 32, 'Frodo', 'Baggins', None])
assert_one(cursor, "SELECT * FROM users WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479",
[UUID('f47ac10b-58cc-4372-a567-0e02b2c3d479'), None, 33, 'Samwise', 'Gamgee', None])
assert_all(cursor, "SELECT * FROM users",
[[UUID('f47ac10b-58cc-4372-a567-0e02b2c3d479'), None, 33, 'Samwise', 'Gamgee', None],
[UUID('550e8400-e29b-41d4-a716-446655440000'), None, 32, 'Frodo', 'Baggins', None]])
else:
assert_one(cursor, "SELECT * FROM users WHERE userid = 550e8400-e29b-41d4-a716-446655440000",
[UUID('550e8400-e29b-41d4-a716-446655440000'), 32, 'Frodo', 'Baggins'])
# FIXME There appears to be some sort of problem with reusable cells
# when executing this query. It's likely that CASSANDRA-9705 will
# fix this, but I'm not 100% sure.
assert_one(cursor, "SELECT * FROM users WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479",
[UUID('f47ac10b-58cc-4372-a567-0e02b2c3d479'), 33, 'Samwise', 'Gamgee'])
assert_all(cursor, "SELECT * FROM users",
[[UUID('f47ac10b-58cc-4372-a567-0e02b2c3d479'), 33, 'Samwise', 'Gamgee'],
[UUID('550e8400-e29b-41d4-a716-446655440000'), 32, 'Frodo', 'Baggins']])
# Test batch inserts
cursor.execute("""
BEGIN BATCH
INSERT INTO users (userid, age) VALUES (550e8400-e29b-41d4-a716-446655440000, 36)
UPDATE users SET age = 37 WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479
DELETE firstname, lastname FROM users WHERE userid = 550e8400-e29b-41d4-a716-446655440000
DELETE firstname, lastname FROM users WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479
APPLY BATCH
""")
if self.is_40_or_greater():
assert_all(cursor, "SELECT * FROM users", [[UUID('f47ac10b-58cc-4372-a567-0e02b2c3d479'), None, 37, None, None, None],
[UUID('550e8400-e29b-41d4-a716-446655440000'), None, 36, None, None, None]])
else:
assert_all(cursor, "SELECT * FROM users", [[UUID('f47ac10b-58cc-4372-a567-0e02b2c3d479'), 37, None, None],
[UUID('550e8400-e29b-41d4-a716-446655440000'), 36, None, None]])
def test_dynamic_cf(self):
""" Test non-composite dynamic CF syntax """
cursor = self.prepare()
cursor.execute("""
CREATE TABLE clicks (
userid uuid,
url text,
time bigint,
PRIMARY KEY (userid, url)
) WITH COMPACT STORAGE;
""")
#4.0 doesn't support compact storage
if self.is_40_or_greater():
cursor.execute("ALTER TABLE clicks DROP COMPACT STORAGE;")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE clicks")
# Inserts
cursor.execute("INSERT INTO clicks (userid, url, time) VALUES (550e8400-e29b-41d4-a716-446655440000, 'http://foo.bar', 42)")
cursor.execute("INSERT INTO clicks (userid, url, time) VALUES (550e8400-e29b-41d4-a716-446655440000, 'http://foo-2.bar', 24)")
cursor.execute("INSERT INTO clicks (userid, url, time) VALUES (550e8400-e29b-41d4-a716-446655440000, 'http://bar.bar', 128)")
cursor.execute("UPDATE clicks SET time = 24 WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479 and url = 'http://bar.foo'")
cursor.execute("UPDATE clicks SET time = 12 WHERE userid IN (f47ac10b-58cc-4372-a567-0e02b2c3d479, 550e8400-e29b-41d4-a716-446655440000) and url = 'http://foo-3'")
# Queries
assert_all(cursor, "SELECT url, time FROM clicks WHERE userid = 550e8400-e29b-41d4-a716-446655440000",
[['http://bar.bar', 128], ['http://foo-2.bar', 24], ['http://foo-3', 12], ['http://foo.bar', 42]])
assert_all(cursor, "SELECT * FROM clicks WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479",
[[UUID('f47ac10b-58cc-4372-a567-0e02b2c3d479'), 'http://bar.foo', 24],
[UUID('f47ac10b-58cc-4372-a567-0e02b2c3d479'), 'http://foo-3', 12]])
assert_all(cursor, "SELECT time FROM clicks", [[24], [12], [128], [24], [12], [42]])
if not self.is_40_or_greater():
# Check we don't allow empty values for url since this is the full underlying cell name (#6152)
assert_invalid(cursor, "INSERT INTO clicks (userid, url, time) VALUES (810e8500-e29b-41d4-a716-446655440000, '', 42)")
def test_dense_cf(self):
""" Test composite 'dense' CF syntax """
cursor = self.prepare()
cursor.execute("""
CREATE TABLE connections (
userid uuid,
ip text,
port int,
time bigint,
PRIMARY KEY (userid, ip, port)
) WITH COMPACT STORAGE;
""")
#4.0 doesn't support compact storage
if self.is_40_or_greater():
cursor.execute("ALTER TABLE connections DROP COMPACT STORAGE;")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE connections")
# Inserts
cursor.execute("INSERT INTO connections (userid, ip, port, time) VALUES (550e8400-e29b-41d4-a716-446655440000, '192.168.0.1', 80, 42)")
cursor.execute("INSERT INTO connections (userid, ip, port, time) VALUES (550e8400-e29b-41d4-a716-446655440000, '192.168.0.2', 80, 24)")
cursor.execute("INSERT INTO connections (userid, ip, port, time) VALUES (550e8400-e29b-41d4-a716-446655440000, '192.168.0.2', 90, 42)")
cursor.execute("UPDATE connections SET time = 24 WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479 AND ip = '192.168.0.2' AND port = 80")
# we don't have to include all of the clustering columns (see CASSANDRA-7990)
if self.is_40_or_greater():
cursor.execute("INSERT INTO connections (userid, ip, port, time) VALUES (f47ac10b-58cc-4372-a567-0e02b2c3d479, '192.168.0.3', 80, 42)")
cursor.execute("UPDATE connections SET time = 42 WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479 AND ip = '192.168.0.4' AND port = 90")
else:
cursor.execute("INSERT INTO connections (userid, ip, time) VALUES (f47ac10b-58cc-4372-a567-0e02b2c3d479, '192.168.0.3', 42)")
cursor.execute("UPDATE connections SET time = 42 WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479 AND ip = '192.168.0.4'")
# Queries
assert_all(cursor, "SELECT ip, port, time FROM connections WHERE userid = 550e8400-e29b-41d4-a716-446655440000",
[['192.168.0.1', 80, 42], ['192.168.0.2', 80, 24], ['192.168.0.2', 90, 42]])
assert_all(cursor, "SELECT ip, port, time FROM connections WHERE userid = 550e8400-e29b-41d4-a716-446655440000 and ip >= '192.168.0.2'",
[['192.168.0.2', 80, 24], ['192.168.0.2', 90, 42]])
assert_all(cursor, "SELECT ip, port, time FROM connections WHERE userid = 550e8400-e29b-41d4-a716-446655440000 and ip = '192.168.0.2'",
[['192.168.0.2', 80, 24], ['192.168.0.2', 90, 42]])
assert_none(cursor, "SELECT ip, port, time FROM connections WHERE userid = 550e8400-e29b-41d4-a716-446655440000 and ip > '192.168.0.2'")
if self.is_40_or_greater():
assert_one(cursor, "SELECT ip, port, time FROM connections WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479 AND ip = '192.168.0.3'",
['192.168.0.3', 80, 42])
assert_one(cursor, "SELECT ip, port, time FROM connections WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479 AND ip = '192.168.0.4'",
['192.168.0.4', 90, 42])
else:
assert_one(cursor, "SELECT ip, port, time FROM connections WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479 AND ip = '192.168.0.3'",
['192.168.0.3', None, 42])
assert_one(cursor, "SELECT ip, port, time FROM connections WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479 AND ip = '192.168.0.4'",
['192.168.0.4', None, 42])
# Deletion
cursor.execute("DELETE time FROM connections WHERE userid = 550e8400-e29b-41d4-a716-446655440000 AND ip = '192.168.0.2' AND port = 80")
res = list(cursor.execute("SELECT * FROM connections WHERE userid = 550e8400-e29b-41d4-a716-446655440000"))
#Without compact storage deleting just the column leaves the row behind
assert_length_equal(res, 3 if self.is_40_or_greater() else 2)
cursor.execute("DELETE FROM connections WHERE userid = 550e8400-e29b-41d4-a716-446655440000")
assert_none(cursor, "SELECT * FROM connections WHERE userid = 550e8400-e29b-41d4-a716-446655440000")
cursor.execute("DELETE FROM connections WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479 AND ip = '192.168.0.3'")
assert_none(cursor, "SELECT * FROM connections WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479 AND ip = '192.168.0.3'")
def test_sparse_cf(self):
""" Test composite 'sparse' CF syntax """
cursor = self.prepare()
cursor.execute("""
CREATE TABLE timeline (
userid uuid,
posted_month int,
posted_day int,
body ascii,
posted_by ascii,
PRIMARY KEY (userid, posted_month, posted_day)
);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE timeline")
frodo_id = UUID('550e8400-e29b-41d4-a716-446655440000')
sam_id = UUID('f47ac10b-58cc-4372-a567-0e02b2c3d479')
# Inserts
cursor.execute("INSERT INTO timeline (userid, posted_month, posted_day, body, posted_by) VALUES (%s, 1, 12, 'Something else', 'Frodo Baggins')", (frodo_id,))
cursor.execute("INSERT INTO timeline (userid, posted_month, posted_day, body, posted_by) VALUES (%s, 1, 24, 'Something something', 'Frodo Baggins')", (frodo_id,))
cursor.execute("UPDATE timeline SET body = 'Yo Froddo', posted_by = 'Samwise Gamgee' WHERE userid = %s AND posted_month = 1 AND posted_day = 3", (sam_id,))
cursor.execute("UPDATE timeline SET body = 'Yet one more message' WHERE userid = %s AND posted_month = 1 and posted_day = 30", (frodo_id,))
# Queries
assert_one(cursor, "SELECT body, posted_by FROM timeline WHERE userid = {} AND posted_month = 1 AND posted_day = 24".format(frodo_id), ['Something something', 'Frodo Baggins'])
assert_all(cursor, "SELECT posted_day, body, posted_by FROM timeline WHERE userid = {} AND posted_month = 1 AND posted_day > 12".format(frodo_id), [
[24, 'Something something', 'Frodo Baggins'],
[30, 'Yet one more message', None]])
assert_all(cursor, "SELECT posted_day, body, posted_by FROM timeline WHERE userid = {} AND posted_month = 1".format(frodo_id), [
[12, 'Something else', 'Frodo Baggins'],
[24, 'Something something', 'Frodo Baggins'],
[30, 'Yet one more message', None]])
def test_limit_ranges(self):
""" Validate LIMIT option for 'range queries' in SELECT statements """
cursor = self.prepare(ordered=True)
cursor.execute("""
CREATE TABLE clicks (
userid int,
url text,
time bigint,
PRIMARY KEY (userid, url)
) WITH COMPACT STORAGE;
""")
#4.0 doesn't support compact storage
if self.is_40_or_greater():
cursor.execute("ALTER TABLE clicks DROP COMPACT STORAGE;")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE clicks")
# Inserts
for id in range(0, 100):
for tld in ['com', 'org', 'net']:
cursor.execute("INSERT INTO clicks (userid, url, time) VALUES ({}, 'http://foo.{}', 42)".format(id, tld))
# Queries
assert_one(cursor, "SELECT * FROM clicks WHERE token(userid) >= token(2) LIMIT 1", [2, 'http://foo.com', 42])
assert_one(cursor, "SELECT * FROM clicks WHERE token(userid) > token(2) LIMIT 1", [3, 'http://foo.com', 42])
def test_limit_multiget(self):
""" Validate LIMIT option for 'multiget' in SELECT statements """
cursor = self.prepare()
cursor.execute("""
CREATE TABLE clicks (
userid int,
url text,
time bigint,
PRIMARY KEY (userid, url)
) WITH COMPACT STORAGE;
""")
#4.0 doesn't support compact storage
if self.is_40_or_greater():
cursor.execute("ALTER TABLE clicks DROP COMPACT STORAGE;")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE clicks")
# Inserts
for id in range(0, 100):
for tld in ['com', 'org', 'net']:
cursor.execute("INSERT INTO clicks (userid, url, time) VALUES ({}, 'http://foo.{}', 42)".format(id, tld))
# Check that we do limit the output to 1 *and* that we respect query
# order of keys (even though 48 is after 2) prior to 2.1.17
if self.get_node_version(is_upgraded) >= '2.1.17':
# the coordinator is the upgraded 2.2+ node or a node with CASSSANDRA-12420
assert_one(cursor, "SELECT * FROM clicks WHERE userid IN (48, 2) LIMIT 1", [2, 'http://foo.com', 42])
else:
# the coordinator is the non-upgraded 2.1 node
assert_one(cursor, "SELECT * FROM clicks WHERE userid IN (48, 2) LIMIT 1", [48, 'http://foo.com', 42])
def test_simple_tuple_query(self):
"""
@jira_ticket CASSANDRA-8613
"""
cursor = self.prepare()
cursor.execute("create table bard (a int, b int, c int, d int , e int, PRIMARY KEY (a, b, c, d, e))")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE bard")
cursor.execute("""INSERT INTO bard (a, b, c, d, e) VALUES (0, 2, 0, 0, 0);""")
cursor.execute("""INSERT INTO bard (a, b, c, d, e) VALUES (0, 1, 0, 0, 0);""")
cursor.execute("""INSERT INTO bard (a, b, c, d, e) VALUES (0, 0, 0, 0, 0);""")
cursor.execute("""INSERT INTO bard (a, b, c, d, e) VALUES (0, 0, 1, 1, 1);""")
cursor.execute("""INSERT INTO bard (a, b, c, d, e) VALUES (0, 0, 2, 2, 2);""")
cursor.execute("""INSERT INTO bard (a, b, c, d, e) VALUES (0, 0, 3, 3, 3);""")
cursor.execute("""INSERT INTO bard (a, b, c, d, e) VALUES (0, 0, 1, 1, 1);""")
assert_all(cursor, "SELECT * FROM bard WHERE b=0 AND (c, d, e) > (1, 1, 1) ALLOW FILTERING;", [[0, 0, 2, 2, 2], [0, 0, 3, 3, 3]])
def test_limit_sparse(self):
""" Validate LIMIT option for sparse table in SELECT statements """
cursor = self.prepare()
cursor.execute("""
CREATE TABLE clicks (
userid int,
url text,
day int,
month text,
year int,
PRIMARY KEY (userid, url)
);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE clicks")
# Inserts
for id in range(0, 100):
for tld in ['com', 'org', 'net']:
cursor.execute("INSERT INTO clicks (userid, url, day, month, year) VALUES ({}, 'http://foo.{}', 1, 'jan', 2012)".format(id, tld))
# Queries
# Check we do get as many rows as requested
res = list(cursor.execute("SELECT * FROM clicks LIMIT 4"))
assert_length_equal(res, 4)
def test_counters(self):
""" Validate counter support """
cursor = self.prepare()
cursor.execute("""
CREATE TABLE clicks (
userid int,
url text,
total counter,
PRIMARY KEY (userid, url)
) WITH COMPACT STORAGE;
""")
#4.0 doesn't support compact storage
if self.is_40_or_greater():
cursor.execute("ALTER TABLE clicks DROP COMPACT STORAGE;")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE clicks")
cursor.execute("UPDATE clicks SET total = total + 1 WHERE userid = 1 AND url = 'http://foo.com'")
assert_one(cursor, "SELECT total FROM clicks WHERE userid = 1 AND url = 'http://foo.com'", [1])
cursor.execute("UPDATE clicks SET total = total - 4 WHERE userid = 1 AND url = 'http://foo.com'")
assert_one(cursor, "SELECT total FROM clicks WHERE userid = 1 AND url = 'http://foo.com'", [-3])
cursor.execute("UPDATE clicks SET total = total+1 WHERE userid = 1 AND url = 'http://foo.com'")
assert_one(cursor, "SELECT total FROM clicks WHERE userid = 1 AND url = 'http://foo.com'", [-2])
cursor.execute("UPDATE clicks SET total = total -2 WHERE userid = 1 AND url = 'http://foo.com'")
assert_one(cursor, "SELECT total FROM clicks WHERE userid = 1 AND url = 'http://foo.com'", [-4])
def test_indexed_with_eq(self):
""" Check that you can query for an indexed column even with a key EQ clause """
cursor = self.prepare()
# Create
cursor.execute("""
CREATE TABLE users (
userid uuid PRIMARY KEY,
firstname text,
lastname text,
age int
);
""")
cursor.execute("CREATE INDEX byAge ON users(age)")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE users")
# Inserts
cursor.execute("INSERT INTO users (userid, firstname, lastname, age) VALUES (550e8400-e29b-41d4-a716-446655440000, 'Frodo', 'Baggins', 32)")
cursor.execute("UPDATE users SET firstname = 'Samwise', lastname = 'Gamgee', age = 33 WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479")
# Queries
assert_none(cursor, "SELECT firstname FROM users WHERE userid = 550e8400-e29b-41d4-a716-446655440000 AND age = 33")
assert_one(cursor, "SELECT firstname FROM users WHERE userid = f47ac10b-58cc-4372-a567-0e02b2c3d479 AND age = 33", ['Samwise'])
def test_select_key_in(self):
""" Query for KEY IN (...) """
cursor = self.prepare()
# Create
cursor.execute("""
CREATE TABLE users (
userid uuid PRIMARY KEY,
firstname text,
lastname text,
age int
);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE users")
# Inserts
cursor.execute("""
INSERT INTO users (userid, firstname, lastname, age)
VALUES (550e8400-e29b-41d4-a716-446655440000, 'Frodo', 'Baggins', 32)
""")
cursor.execute("""
INSERT INTO users (userid, firstname, lastname, age)
VALUES (f47ac10b-58cc-4372-a567-0e02b2c3d479, 'Samwise', 'Gamgee', 33)
""")
# Select
res = list(cursor.execute("""
SELECT firstname, lastname FROM users
WHERE userid IN (550e8400-e29b-41d4-a716-446655440000, f47ac10b-58cc-4372-a567-0e02b2c3d479)
"""))
assert_length_equal(res, 2)
def test_exclusive_slice(self):
""" Test SELECT respects inclusive and exclusive bounds """
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k int,
c int,
v int,
PRIMARY KEY (k, c)
) WITH COMPACT STORAGE;
""")
#4.0 doesn't support compact storage
if self.is_40_or_greater():
cursor.execute("ALTER TABLE test DROP COMPACT STORAGE;")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
# Inserts
for x in range(0, 10):
cursor.execute("INSERT INTO test (k, c, v) VALUES (0, %s, %s)", (x, x))
# Queries
assert_all(cursor, "SELECT v FROM test WHERE k = 0", [[x] for x in range(10)])
assert_all(cursor, "SELECT v FROM test WHERE k = 0 AND c >= 2 AND c <= 6", [[x] for x in range(2, 7)])
assert_all(cursor, "SELECT v FROM test WHERE k = 0 AND c > 2 AND c <= 6", [[x] for x in range(3, 7)])
assert_all(cursor, "SELECT v FROM test WHERE k = 0 AND c >= 2 AND c < 6", [[x] for x in range(2, 6)])
assert_all(cursor, "SELECT v FROM test WHERE k = 0 AND c > 2 AND c < 6", [[x] for x in range(3, 6)])
# With LIMIT
assert_all(cursor, "SELECT v FROM test WHERE k = 0 AND c > 2 AND c <= 6 LIMIT 2", [[3], [4]])
assert_all(cursor, "SELECT v FROM test WHERE k = 0 AND c >= 2 AND c < 6 ORDER BY c DESC LIMIT 2", [[5], [4]])
def test_in_clause_wide_rows(self):
""" Check IN support for 'wide rows' in SELECT statement """
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test1 (
k int,
c int,
v int,
PRIMARY KEY (k, c)
) WITH COMPACT STORAGE;
""")
# composites
cursor.execute("""
CREATE TABLE test2 (
k int,
c1 int,
c2 int,
v int,
PRIMARY KEY (k, c1, c2)
) WITH COMPACT STORAGE;
""")
#4.0 doesn't support compact storage
if self.is_40_or_greater():
cursor.execute("ALTER TABLE test1 DROP COMPACT STORAGE;")
cursor.execute("ALTER TABLE test2 DROP COMPACT STORAGE;")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test1")
cursor.execute("TRUNCATE test2")
# Inserts
for x in range(0, 10):
cursor.execute("INSERT INTO test1 (k, c, v) VALUES (0, %i, %i)" % (x, x))
assert_all(cursor, "SELECT v FROM test1 WHERE k = 0 AND c IN (5, 2, 8)", [[2], [5], [8]])
# Inserts
for x in range(0, 10):
cursor.execute("INSERT INTO test2 (k, c1, c2, v) VALUES (0, 0, {}, {})".format(x, x))
# Check first we don't allow IN everywhere
if self.get_node_version(is_upgraded) >= '2.2':
# the coordinator is the upgraded 2.2+ node
assert_none(cursor, "SELECT v FROM test2 WHERE k = 0 AND c1 IN (5, 2, 8) AND c2 = 3")
else:
# the coordinator is the non-upgraded 2.1 node
assert_invalid(cursor, "SELECT v FROM test2 WHERE k = 0 AND c1 IN (5, 2, 8) AND c2 = 3")
assert_all(cursor, "SELECT v FROM test2 WHERE k = 0 AND c1 = 0 AND c2 IN (5, 2, 8)", [[2], [5], [8]])
def test_order_by(self):
""" Check ORDER BY support in SELECT statement """
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test1 (
k int,
c int,
v int,
PRIMARY KEY (k, c)
) WITH COMPACT STORAGE;
""")
# composites
cursor.execute("""
CREATE TABLE test2 (
k int,
c1 int,
c2 int,
v int,
PRIMARY KEY (k, c1, c2)
);
""")
#4.0 doesn't support compact storage
if self.is_40_or_greater():
cursor.execute("ALTER TABLE test1 DROP COMPACT STORAGE;")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test1")
cursor.execute("TRUNCATE test2")
# Inserts
for x in range(0, 10):
cursor.execute("INSERT INTO test1 (k, c, v) VALUES (0, {}, {})".format(x, x))
assert_all(cursor, "SELECT v FROM test1 WHERE k = 0 ORDER BY c DESC", [[x] for x in reversed(list(range(10)))])
# Inserts
for x in range(0, 4):
for y in range(0, 2):
cursor.execute("INSERT INTO test2 (k, c1, c2, v) VALUES (0, {}, {}, {})".format(x, y, x * 2 + y))
# Check first we don't always ORDER BY
assert_invalid(cursor, "SELECT v FROM test2 WHERE k = 0 ORDER BY c DESC")
assert_invalid(cursor, "SELECT v FROM test2 WHERE k = 0 ORDER BY c2 DESC")
assert_invalid(cursor, "SELECT v FROM test2 WHERE k = 0 ORDER BY k DESC")
assert_all(cursor, "SELECT v FROM test2 WHERE k = 0 ORDER BY c1 DESC", [[x] for x in reversed(list(range(8)))])
assert_all(cursor, "SELECT v FROM test2 WHERE k = 0 ORDER BY c1", [[x] for x in range(8)])
def test_more_order_by(self):
"""
More ORDER BY checks
@jira_ticket CASSANDRA-4160
"""
cursor = self.prepare()
cursor.execute("""
CREATE COLUMNFAMILY Test (
row text,
number int,
string text,
PRIMARY KEY (row, number)
) WITH COMPACT STORAGE
""")
cursor.execute("""
CREATE COLUMNFAMILY test2 (
row text,
number int,
number2 int,
string text,
PRIMARY KEY (row, number, number2)
) WITH COMPACT STORAGE
""")
#4.0 doesn't support compact storage
if self.is_40_or_greater():
cursor.execute("ALTER TABLE Test DROP COMPACT STORAGE;")
cursor.execute("ALTER TABLE test2 DROP COMPACT STORAGE;")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.execute("INSERT INTO Test (row, number, string) VALUES ('row', 1, 'one');")
cursor.execute("INSERT INTO Test (row, number, string) VALUES ('row', 2, 'two');")
cursor.execute("INSERT INTO Test (row, number, string) VALUES ('row', 3, 'three');")
cursor.execute("INSERT INTO Test (row, number, string) VALUES ('row', 4, 'four');")
assert_all(cursor, "SELECT number FROM Test WHERE row='row' AND number < 3 ORDER BY number ASC;", [[1], [2]])
assert_all(cursor, "SELECT number FROM Test WHERE row='row' AND number >= 3 ORDER BY number ASC;", [[3], [4]])
assert_all(cursor, "SELECT number FROM Test WHERE row='row' AND number < 3 ORDER BY number DESC;", [[2], [1]])
assert_all(cursor, "SELECT number FROM Test WHERE row='row' AND number >= 3 ORDER BY number DESC;", [[4], [3]])
assert_all(cursor, "SELECT number FROM Test WHERE row='row' AND number > 3 ORDER BY number DESC;", [[4]])
assert_all(cursor, "SELECT number FROM Test WHERE row='row' AND number <= 3 ORDER BY number DESC;", [[3], [2], [1]])
# composite clustering
cursor.execute("INSERT INTO test2 (row, number, number2, string) VALUES ('a', 1, 0, 'a');")
cursor.execute("INSERT INTO test2 (row, number, number2, string) VALUES ('a', 2, 0, 'a');")
cursor.execute("INSERT INTO test2 (row, number, number2, string) VALUES ('a', 2, 1, 'a');")
cursor.execute("INSERT INTO test2 (row, number, number2, string) VALUES ('a', 3, 0, 'a');")
cursor.execute("INSERT INTO test2 (row, number, number2, string) VALUES ('a', 3, 1, 'a');")
cursor.execute("INSERT INTO test2 (row, number, number2, string) VALUES ('a', 4, 0, 'a');")
assert_all(cursor, "SELECT number, number2 FROM test2 WHERE row='a' AND number < 3 ORDER BY number ASC;", [[1, 0], [2, 0], [2, 1]])
assert_all(cursor, "SELECT number, number2 FROM test2 WHERE row='a' AND number >= 3 ORDER BY number ASC;", [[3, 0], [3, 1], [4, 0]])
assert_all(cursor, "SELECT number, number2 FROM test2 WHERE row='a' AND number < 3 ORDER BY number DESC;", [[2, 1], [2, 0], [1, 0]])
assert_all(cursor, "SELECT number, number2 FROM test2 WHERE row='a' AND number >= 3 ORDER BY number DESC;", [[4, 0], [3, 1], [3, 0]])
assert_all(cursor, "SELECT number, number2 FROM test2 WHERE row='a' AND number > 3 ORDER BY number DESC;", [[4, 0]])
assert_all(cursor, "SELECT number, number2 FROM test2 WHERE row='a' AND number <= 3 ORDER BY number DESC;", [[3, 1], [3, 0], [2, 1], [2, 0], [1, 0]])
def test_order_by_validation(self):
"""
Check we don't allow order by on row key
@jira_ticket CASSANDRA-4246
"""
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k1 int,
k2 int,
v int,
PRIMARY KEY (k1, k2)
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
q = "INSERT INTO test (k1, k2, v) VALUES (%d, %d, %d)"
cursor.execute(q % (0, 0, 0))
cursor.execute(q % (1, 1, 1))
cursor.execute(q % (2, 2, 2))
assert_invalid(cursor, "SELECT * FROM test ORDER BY k2")
def test_order_by_with_in(self):
"""
Check that order-by works with IN
@jira_ticket CASSANDRA-4327
"""
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test(
my_id varchar,
col1 int,
value varchar,
PRIMARY KEY (my_id, col1)
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.default_fetch_size = None
cursor.execute("INSERT INTO test(my_id, col1, value) VALUES ( 'key1', 1, 'a')")
cursor.execute("INSERT INTO test(my_id, col1, value) VALUES ( 'key2', 3, 'c')")
cursor.execute("INSERT INTO test(my_id, col1, value) VALUES ( 'key3', 2, 'b')")
cursor.execute("INSERT INTO test(my_id, col1, value) VALUES ( 'key4', 4, 'd')")
query = "SELECT col1 FROM test WHERE my_id in('key1', 'key2', 'key3') ORDER BY col1"
assert_all(cursor, query, [[1], [2], [3]])
query = "SELECT col1, my_id FROM test WHERE my_id in('key1', 'key2', 'key3') ORDER BY col1"
assert_all(cursor, query, [[1, 'key1'], [2, 'key3'], [3, 'key2']])
query = "SELECT my_id, col1 FROM test WHERE my_id in('key1', 'key2', 'key3') ORDER BY col1"
assert_all(cursor, query, [['key1', 1], ['key3', 2], ['key2', 3]])
def test_reversed_comparator(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k int,
c int,
v int,
PRIMARY KEY (k, c)
) WITH CLUSTERING ORDER BY (c DESC);
""")
cursor.execute("""
CREATE TABLE test2 (
k int,
c1 int,
c2 int,
v text,
PRIMARY KEY (k, c1, c2)
) WITH CLUSTERING ORDER BY (c1 ASC, c2 DESC);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.execute("TRUNCATE test2")
# Inserts
for x in range(0, 10):
cursor.execute("INSERT INTO test (k, c, v) VALUES (0, {}, {})".format(x, x))
assert_all(cursor, "SELECT c, v FROM test WHERE k = 0 ORDER BY c ASC", [[x, x] for x in range(0, 10)])
assert_all(cursor, "SELECT c, v FROM test WHERE k = 0 ORDER BY c DESC", [[x, x] for x in range(9, -1, -1)])
# Inserts
for x in range(0, 10):
for y in range(0, 10):
cursor.execute("INSERT INTO test2 (k, c1, c2, v) VALUES (0, {}, {}, '{}{}')".format(x, y, x, y))
assert_invalid(cursor, "SELECT c1, c2, v FROM test2 WHERE k = 0 ORDER BY c1 ASC, c2 ASC")
assert_invalid(cursor, "SELECT c1, c2, v FROM test2 WHERE k = 0 ORDER BY c1 DESC, c2 DESC")
assert_all(cursor, "SELECT c1, c2, v FROM test2 WHERE k = 0 ORDER BY c1 ASC", [[x, y, '{}{}'.format(x, y)] for x in range(0, 10) for y in range(9, -1, -1)])
assert_all(cursor, "SELECT c1, c2, v FROM test2 WHERE k = 0 ORDER BY c1 ASC, c2 DESC", [[x, y, '{}{}'.format(x, y)] for x in range(0, 10) for y in range(9, -1, -1)])
assert_all(cursor, "SELECT c1, c2, v FROM test2 WHERE k = 0 ORDER BY c1 DESC, c2 ASC", [[x, y, '{}{}'.format(x, y)] for x in range(9, -1, -1) for y in range(0, 10)])
assert_invalid(cursor, "SELECT c1, c2, v FROM test2 WHERE k = 0 ORDER BY c2 DESC, c1 ASC")
def test_null_support(self):
""" Test support for nulls """
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k int,
c int,
v1 int,
v2 set<text>,
PRIMARY KEY (k, c)
);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
# Inserts
cursor.execute("INSERT INTO test (k, c, v1, v2) VALUES (0, 0, null, {'1', '2'})")
cursor.execute("INSERT INTO test (k, c, v1) VALUES (0, 1, 1)")
assert_all(cursor, "SELECT * FROM test", [[0, 0, None, set(['1', '2'])], [0, 1, 1, None]])
cursor.execute("INSERT INTO test (k, c, v1) VALUES (0, 1, null)")
cursor.execute("INSERT INTO test (k, c, v2) VALUES (0, 0, null)")
assert_all(cursor, "SELECT * FROM test", [[0, 0, None, None], [0, 1, None, None]])
assert_invalid(cursor, "INSERT INTO test (k, c, v2) VALUES (0, 2, {1, null})")
assert_invalid(cursor, "SELECT * FROM test WHERE k = null")
assert_invalid(cursor, "INSERT INTO test (k, c, v2) VALUES (0, 0, { 'foo', 'bar', null })")
def test_nameless_index(self):
""" Test CREATE INDEX without name and validate the index can be dropped """
cursor = self.prepare()
cursor.execute("""
CREATE TABLE users (
id text PRIMARY KEY,
birth_year int,
)
""")
cursor.execute("CREATE INDEX on users(birth_year)")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE users")
cursor.execute("INSERT INTO users (id, birth_year) VALUES ('Tom', 42)")
cursor.execute("INSERT INTO users (id, birth_year) VALUES ('Paul', 24)")
cursor.execute("INSERT INTO users (id, birth_year) VALUES ('Bob', 42)")
assert_all(cursor, "SELECT id FROM users WHERE birth_year = 42", [['Tom'], ['Bob']])
def test_deletion(self):
"""
Test simple deletion and in particular check for #4193 bug
@jira_ticket CASSANDRA-4193
"""
cursor = self.prepare()
cursor.execute("""
CREATE TABLE testcf (
username varchar,
id int,
name varchar,
stuff varchar,
PRIMARY KEY(username, id)
);
""")
# Compact case
cursor.execute("""
CREATE TABLE testcf2 (
username varchar,
id int,
name varchar,
stuff varchar,
PRIMARY KEY(username, id, name)
) WITH COMPACT STORAGE;
""")
#4.0 doesn't support compact storage
if self.is_40_or_greater():
cursor.execute("ALTER TABLE testcf2 DROP COMPACT STORAGE;")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE testcf")
cursor.execute("TRUNCATE testcf2")
q = "INSERT INTO testcf (username, id, name, stuff) VALUES (%s, %s, %s, %s);"
row1 = ('abc', 2, 'rst', 'some value')
row2 = ('abc', 4, 'xyz', 'some other value')
cursor.execute(q, row1)
cursor.execute(q, row2)
assert_all(cursor, "SELECT * FROM testcf", [list(row1), list(row2)])
cursor.execute("DELETE FROM testcf WHERE username='abc' AND id=2")
assert_all(cursor, "SELECT * FROM testcf", [list(row2)])
q = "INSERT INTO testcf2 (username, id, name, stuff) VALUES (%s, %s, %s, %s);"
row1 = ('abc', 2, 'rst', 'some value')
row2 = ('abc', 4, 'xyz', 'some other value')
cursor.execute(q, row1)
cursor.execute(q, row2)
assert_all(cursor, "SELECT * FROM testcf2", [list(row1), list(row2)])
cursor.execute("DELETE FROM testcf2 WHERE username='abc' AND id=2")
assert_all(cursor, "SELECT * FROM testcf", [list(row2)])
def test_count(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE events (
kind text,
time int,
value1 int,
value2 int,
PRIMARY KEY(kind, time)
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE events")
full = "INSERT INTO events (kind, time, value1, value2) VALUES ('ev1', %d, %d, %d)"
no_v2 = "INSERT INTO events (kind, time, value1) VALUES ('ev1', %d, %d)"
cursor.execute(full % (0, 0, 0))
cursor.execute(full % (1, 1, 1))
cursor.execute(no_v2 % (2, 2))
cursor.execute(full % (3, 3, 3))
cursor.execute(no_v2 % (4, 4))
cursor.execute("INSERT INTO events (kind, time, value1, value2) VALUES ('ev2', 0, 0, 0)")
assert_all(cursor, "SELECT COUNT(*) FROM events WHERE kind = 'ev1'", [[5]])
assert_all(cursor, "SELECT COUNT(1) FROM events WHERE kind IN ('ev1', 'ev2') AND time=0", [[2]])
def test_batch(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE users (
userid text PRIMARY KEY,
name text,
password text
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE users")
query = SimpleStatement("""
BEGIN BATCH
INSERT INTO users (userid, password, name) VALUES ('user2', 'ch@ngem3b', 'second user');
UPDATE users SET password = 'ps22dhds' WHERE userid = 'user3';
INSERT INTO users (userid, password) VALUES ('user4', 'ch@ngem3c');
DELETE name FROM users WHERE userid = 'user1';
APPLY BATCH;
""", consistency_level=ConsistencyLevel.QUORUM)
cursor.execute(query)
def test_token_range(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k int PRIMARY KEY,
c int,
v int
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
c = 100
for i in range(0, c):
cursor.execute("INSERT INTO test (k, c, v) VALUES ({}, {}, {})".format(i, i, i))
rows = cursor.execute("SELECT k FROM test")
inOrder = [x[0] for x in rows]
assert_length_equal(inOrder, c)
min_token = -2 ** 63
res = list(cursor.execute("SELECT k FROM test WHERE token(k) >= {}".format(min_token)))
assert_length_equal(res, c)
# assert_invalid(cursor, "SELECT k FROM test WHERE token(k) >= 0")
# cursor.execute("SELECT k FROM test WHERE token(k) >= 0")
assert_all(cursor, "SELECT k FROM test WHERE token(k) >= token({}) AND token(k) < token({})".format(inOrder[32], inOrder[65]), [[inOrder[x]] for x in range(32, 65)])
def test_timestamp_and_ttl(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k int PRIMARY KEY,
c text,
d text
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.execute("INSERT INTO test (k, c) VALUES (1, 'test')")
cursor.execute("INSERT INTO test (k, c) VALUES (2, 'test') USING TTL 400")
res = list(cursor.execute("SELECT k, c, writetime(c), ttl(c) FROM test"))
assert_length_equal(res, 2)
for r in res:
assert isinstance(r[2], (int, int))
if r[0] == 1:
assert r[3] is None, res
else:
assert isinstance(r[3], (int, int))
# wrap writetime(), ttl() in other functions (test for CASSANDRA-8451)
res = list(cursor.execute("SELECT k, c, blobAsBigint(bigintAsBlob(writetime(c))), ttl(c) FROM test"))
assert_length_equal(res, 2)
for r in res:
assert isinstance(r[2], (int, int))
if r[0] == 1:
assert r[3] is None, res
else:
assert isinstance(r[3], (int, int))
res = list(cursor.execute("SELECT k, c, writetime(c), blobAsInt(intAsBlob(ttl(c))) FROM test"))
assert_length_equal(res, 2)
for r in res:
assert isinstance(r[2], (int, int))
if r[0] == 1:
assert r[3] is None, res
else:
assert isinstance(r[3], (int, int))
assert_invalid(cursor, "SELECT k, c, writetime(k) FROM test")
res = cursor.execute("SELECT k, d, writetime(d) FROM test WHERE k = 1")
assert_one(cursor, "SELECT k, d, writetime(d) FROM test WHERE k = 1", [1, None, None])
def test_no_range_ghost(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k int PRIMARY KEY,
v int
)
""")
# Example from #3505
cursor.execute("CREATE KEYSPACE ks1 with replication = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };")
cursor.execute("""
CREATE COLUMNFAMILY ks1.users (
KEY varchar PRIMARY KEY,
password varchar,
gender varchar,
birth_year bigint)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.execute("TRUNCATE ks1.users")
for k in range(0, 5):
cursor.execute("INSERT INTO test (k, v) VALUES (%d, 0)" % k)
assert_all(cursor, "SELECT k FROM test", [[k] for k in range(0, 5)], ignore_order=True)
cursor.execute("DELETE FROM test WHERE k=2")
assert_all(cursor, "SELECT k FROM test", [[k] for k in range(0, 5) if k != 2], ignore_order=True)
# Example from #3505
cursor.execute("USE ks1")
cursor.execute("INSERT INTO users (KEY, password) VALUES ('user1', 'ch@ngem3a')")
cursor.execute("UPDATE users SET gender = 'm', birth_year = 1980 WHERE KEY = 'user1'")
assert_all(cursor, "SELECT * FROM users WHERE KEY='user1'", [['user1', 1980, 'm', 'ch@ngem3a']])
cursor.execute("TRUNCATE users")
assert_all(cursor, "SELECT * FROM users", [])
assert_all(cursor, "SELECT * FROM users WHERE KEY='user1'", [])
def test_undefined_column_handling(self):
cursor = self.prepare(ordered=True)
cursor.execute("""
CREATE TABLE test (
k int PRIMARY KEY,
v1 int,
v2 int,
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.execute("INSERT INTO test (k, v1, v2) VALUES (0, 0, 0)")
cursor.execute("INSERT INTO test (k, v1) VALUES (1, 1)")
cursor.execute("INSERT INTO test (k, v1, v2) VALUES (2, 2, 2)")
assert_all(cursor, "SELECT v2 FROM test", [[0], [None], [2]])
assert_all(cursor, "SELECT v2 FROM test WHERE k = 1", [[None]])
def test_range_tombstones(self):
""" Test deletion by 'composite prefix' (range tombstones) """
# Uses 3 nodes just to make sure RowMutation are correctly serialized
cursor = self.prepare(nodes=3)
cursor.execute("""
CREATE TABLE test1 (
k int,
c1 int,
c2 int,
v1 int,
v2 int,
PRIMARY KEY (k, c1, c2)
);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test1")
rows = 5
col1 = 2
col2 = 2
cpr = col1 * col2
for i in range(0, rows):
for j in range(0, col1):
for k in range(0, col2):
n = (i * cpr) + (j * col2) + k
cursor.execute("INSERT INTO test1 (k, c1, c2, v1, v2) VALUES ({}, {}, {}, {}, {})".format(i, j, k, n, n))
for i in range(0, rows):
assert_all(cursor, "SELECT v1, v2 FROM test1 where k = %d" % i, [[x, x] for x in range(i * cpr, (i + 1) * cpr)])
for i in range(0, rows):
cursor.execute("DELETE FROM test1 WHERE k = %d AND c1 = 0" % i)
for i in range(0, rows):
assert_all(cursor, "SELECT v1, v2 FROM test1 WHERE k = %d" % i, [[x, x] for x in range(i * cpr + col1, (i + 1) * cpr)])
self.cluster.flush()
time.sleep(0.2)
for i in range(0, rows):
assert_all(cursor, "SELECT v1, v2 FROM test1 WHERE k = %d" % i, [[x, x] for x in range(i * cpr + col1, (i + 1) * cpr)])
def test_range_tombstones_compaction(self):
""" Test deletion by 'composite prefix' (range tombstones) with compaction """
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test1 (
k int,
c1 int,
c2 int,
v1 text,
PRIMARY KEY (k, c1, c2)
);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test1")
for c1 in range(0, 4):
for c2 in range(0, 2):
cursor.execute("INSERT INTO test1 (k, c1, c2, v1) VALUES (0, %d, %d, '%s')" % (c1, c2, '%i%i' % (c1, c2)))
self.cluster.flush()
cursor.execute("DELETE FROM test1 WHERE k = 0 AND c1 = 1")
self.cluster.flush()
self.cluster.compact()
assert_all(cursor, "SELECT v1 FROM test1 WHERE k = 0", [['{}{}'.format(c1, c2)] for c1 in range(0, 4) for c2 in range(0, 2) if c1 != 1])
def test_delete_row(self):
""" Test deletion of rows """
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k int,
c1 int,
c2 int,
v1 int,
v2 int,
PRIMARY KEY (k, c1, c2)
);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
q = "INSERT INTO test (k, c1, c2, v1, v2) VALUES (%d, %d, %d, %d, %d)"
cursor.execute(q % (0, 0, 0, 0, 0))
cursor.execute(q % (0, 0, 1, 1, 1))
cursor.execute(q % (0, 0, 2, 2, 2))
cursor.execute(q % (0, 1, 0, 3, 3))
cursor.execute("DELETE FROM test WHERE k = 0 AND c1 = 0 AND c2 = 0")
res = list(cursor.execute("SELECT * FROM test"))
assert_length_equal(res, 3)
def test_range_query_2ndary(self):
"""
Test range queries with 2ndary indexes
@jira_ticket CASSANDRA-4257
"""
cursor = self.prepare()
cursor.execute("CREATE TABLE indextest (id int primary key, row int, setid int);")
cursor.execute("CREATE INDEX indextest_setid_idx ON indextest (setid)")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE indextest")
q = "INSERT INTO indextest (id, row, setid) VALUES (%d, %d, %d);"
cursor.execute(q % (0, 0, 0))
cursor.execute(q % (1, 1, 0))
cursor.execute(q % (2, 2, 0))
cursor.execute(q % (3, 3, 0))
assert_invalid(cursor, "SELECT * FROM indextest WHERE setid = 0 AND row < 1;")
assert_all(cursor, "SELECT * FROM indextest WHERE setid = 0 AND row < 1 ALLOW FILTERING;", [[0, 0, 0]])
def test_set(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE user (
fn text,
ln text,
tags set<text>,
PRIMARY KEY (fn, ln)
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE user")
q = "UPDATE user SET %s WHERE fn='Tom' AND ln='Bombadil'"
cursor.execute(q % "tags = tags + { 'foo' }")
cursor.execute(q % "tags = tags + { 'bar' }")
cursor.execute(q % "tags = tags + { 'foo' }")
cursor.execute(q % "tags = tags + { 'foobar' }")
cursor.execute(q % "tags = tags - { 'bar' }")
assert_all(cursor, "SELECT tags FROM user", [[set(['foo', 'foobar'])]])
q = "UPDATE user SET {} WHERE fn='Bilbo' AND ln='Baggins'"
cursor.execute(q.format("tags = { 'a', 'c', 'b' }"))
assert_all(cursor, "SELECT tags FROM user WHERE fn='Bilbo' AND ln='Baggins'", [[set(['a', 'b', 'c'])]])
time.sleep(.01)
cursor.execute(q.format("tags = { 'm', 'n' }"))
assert_all(cursor, "SELECT tags FROM user WHERE fn='Bilbo' AND ln='Baggins'", [[set(['m', 'n'])]])
cursor.execute("DELETE tags['m'] FROM user WHERE fn='Bilbo' AND ln='Baggins'")
assert_all(cursor, "SELECT tags FROM user WHERE fn='Bilbo' AND ln='Baggins'", [[set(['n'])]])
cursor.execute("DELETE tags FROM user WHERE fn='Bilbo' AND ln='Baggins'")
assert_all(cursor, "SELECT tags FROM user WHERE fn='Bilbo' AND ln='Baggins'", [])
def test_map(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE user (
fn text,
ln text,
m map<text, int>,
PRIMARY KEY (fn, ln)
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE user")
q = "UPDATE user SET %s WHERE fn='Tom' AND ln='Bombadil'"
cursor.execute(q % "m['foo'] = 3")
cursor.execute(q % "m['bar'] = 4")
cursor.execute(q % "m['woot'] = 5")
cursor.execute(q % "m['bar'] = 6")
cursor.execute("DELETE m['foo'] FROM user WHERE fn='Tom' AND ln='Bombadil'")
assert_all(cursor, "SELECT m FROM user", [[{'woot': 5, 'bar': 6}]])
q = "UPDATE user SET %s WHERE fn='Bilbo' AND ln='Baggins'"
cursor.execute(q % "m = { 'a' : 4 , 'c' : 3, 'b' : 2 }")
assert_all(cursor, "SELECT m FROM user WHERE fn='Bilbo' AND ln='Baggins'", [[{'a': 4, 'b': 2, 'c': 3}]])
time.sleep(.01)
# Check we correctly overwrite
cursor.execute(q % "m = { 'm' : 4 , 'n' : 1, 'o' : 2 }")
assert_all(cursor, "SELECT m FROM user WHERE fn='Bilbo' AND ln='Baggins'", [[{'m': 4, 'n': 1, 'o': 2}]])
cursor.execute(q % "m = {}")
assert_all(cursor, "SELECT m FROM user WHERE fn='Bilbo' AND ln='Baggins'", [])
def test_list(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE user (
fn text,
ln text,
tags list<text>,
PRIMARY KEY (fn, ln)
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE user")
q = "UPDATE user SET %s WHERE fn='Tom' AND ln='Bombadil'"
cursor.execute(q % "tags = tags + [ 'foo' ]")
cursor.execute(q % "tags = tags + [ 'bar' ]")
cursor.execute(q % "tags = tags + [ 'foo' ]")
cursor.execute(q % "tags = tags + [ 'foobar' ]")
assert_one(cursor, "SELECT tags FROM user", [['foo', 'bar', 'foo', 'foobar']])
q = "UPDATE user SET %s WHERE fn='Bilbo' AND ln='Baggins'"
cursor.execute(q % "tags = [ 'a', 'c', 'b', 'c' ]")
assert_one(cursor, "SELECT tags FROM user WHERE fn='Bilbo' AND ln='Baggins'", [['a', 'c', 'b', 'c']])
cursor.execute(q % "tags = [ 'm', 'n' ] + tags")
assert_one(cursor, "SELECT tags FROM user WHERE fn='Bilbo' AND ln='Baggins'", [['m', 'n', 'a', 'c', 'b', 'c']])
cursor.execute(q % "tags[2] = 'foo', tags[4] = 'bar'")
assert_one(cursor, "SELECT tags FROM user WHERE fn='Bilbo' AND ln='Baggins'", [['m', 'n', 'foo', 'c', 'bar', 'c']])
cursor.execute("DELETE tags[2] FROM user WHERE fn='Bilbo' AND ln='Baggins'")
assert_one(cursor, "SELECT tags FROM user WHERE fn='Bilbo' AND ln='Baggins'", [['m', 'n', 'c', 'bar', 'c']])
cursor.execute(q % "tags = tags - [ 'bar' ]")
assert_one(cursor, "SELECT tags FROM user WHERE fn='Bilbo' AND ln='Baggins'", [['m', 'n', 'c', 'c']])
def test_multi_collection(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE foo(
k uuid PRIMARY KEY,
L list<int>,
M map<text, int>,
S set<int>
);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE foo")
cursor.execute("UPDATE ks.foo SET L = [1, 3, 5] WHERE k = b017f48f-ae67-11e1-9096-005056c00008;")
cursor.execute("UPDATE ks.foo SET L = L + [7, 11, 13] WHERE k = b017f48f-ae67-11e1-9096-005056c00008;")
cursor.execute("UPDATE ks.foo SET S = {1, 3, 5} WHERE k = b017f48f-ae67-11e1-9096-005056c00008;")
cursor.execute("UPDATE ks.foo SET S = S + {7, 11, 13} WHERE k = b017f48f-ae67-11e1-9096-005056c00008;")
cursor.execute("UPDATE ks.foo SET M = {'foo': 1, 'bar' : 3} WHERE k = b017f48f-ae67-11e1-9096-005056c00008;")
cursor.execute("UPDATE ks.foo SET M = M + {'foobar' : 4} WHERE k = b017f48f-ae67-11e1-9096-005056c00008;")
assert_all(cursor, "SELECT L, M, S FROM foo WHERE k = b017f48f-ae67-11e1-9096-005056c00008", [[
[1, 3, 5, 7, 11, 13],
OrderedDict([('bar', 3), ('foo', 1), ('foobar', 4)]),
sortedset([1, 3, 5, 7, 11, 13])
]])
def test_range_query(self):
"""
Range test query from #4372
@jira_ticket CASSANDRA-4372
"""
cursor = self.prepare()
cursor.execute("CREATE TABLE test (a int, b int, c int, d int, e int, f text, PRIMARY KEY (a, b, c, d, e) )")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.execute("INSERT INTO test (a, b, c, d, e, f) VALUES (1, 1, 1, 1, 2, '2');")
cursor.execute("INSERT INTO test (a, b, c, d, e, f) VALUES (1, 1, 1, 1, 1, '1');")
cursor.execute("INSERT INTO test (a, b, c, d, e, f) VALUES (1, 1, 1, 2, 1, '1');")
cursor.execute("INSERT INTO test (a, b, c, d, e, f) VALUES (1, 1, 1, 1, 3, '3');")
cursor.execute("INSERT INTO test (a, b, c, d, e, f) VALUES (1, 1, 1, 1, 5, '5');")
assert_all(cursor, "SELECT a, b, c, d, e, f FROM test WHERE a = 1 AND b = 1 AND c = 1 AND d = 1 AND e >= 2;", [[1, 1, 1, 1, 2, '2'], [1, 1, 1, 1, 3, '3'], [1, 1, 1, 1, 5, '5']])
def test_composite_row_key(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k1 int,
k2 int,
c int,
v int,
PRIMARY KEY ((k1, k2), c)
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
req = "INSERT INTO test (k1, k2, c, v) VALUES ({}, {}, {}, {})"
for i in range(0, 4):
cursor.execute(req.format(0, i, i, i))
assert_all(cursor, "SELECT * FROM test", [[0, 2, 2, 2], [0, 3, 3, 3], [0, 0, 0, 0], [0, 1, 1, 1]])
assert_all(cursor, "SELECT * FROM test WHERE k1 = 0 and k2 IN (1, 3)", [[0, 1, 1, 1], [0, 3, 3, 3]])
assert_invalid(cursor, "SELECT * FROM test WHERE k2 = 3")
if self.get_node_version(is_upgraded) < '2.2':
# the coordinator is the upgraded 2.2+ node
assert_invalid(cursor, "SELECT * FROM test WHERE k1 IN (0, 1) and k2 = 3")
assert_all(cursor, "SELECT * FROM test WHERE token(k1, k2) = token(0, 1)", [[0, 1, 1, 1]])
assert_all(cursor, "SELECT * FROM test WHERE token(k1, k2) > " + str(-((2 ** 63) - 1)), [[0, 2, 2, 2], [0, 3, 3, 3], [0, 0, 0, 0], [0, 1, 1, 1]])
@since('2', max_version='3.99')
def test_cql3_insert_thrift(self):
"""
Check that we can insert from thrift into a CQL3 table
@jira_ticket CASSANDRA-4377
"""
cursor = self.prepare(start_rpc=True)
cursor.execute("""
CREATE TABLE test (
k int,
c int,
v int,
PRIMARY KEY (k, c)
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
node = self.cluster.nodelist()[0]
host, port = node.network_interfaces['thrift']
client = get_thrift_client(host, port)
client.transport.open()
client.set_keyspace('ks')
key = struct.pack('>i', 2)
column_name_component = struct.pack('>i', 4)
# component length + component + EOC + component length + component + EOC
column_name = b'\x00\x04' + column_name_component + b'\x00' + b'\x00\x01' + 'v'.encode() + b'\x00'
value = struct.pack('>i', 8)
client.batch_mutate(
{key: {'test': [Mutation(ColumnOrSuperColumn(column=Column(name=column_name, value=value, timestamp=100)))]}},
ThriftConsistencyLevel.ONE)
assert_one(cursor, "SELECT * FROM test", [2, 4, 8])
@since('2', max_version='3.99')
def test_cql3_non_compound_range_tombstones(self):
"""
Checks that 3.0 serializes RangeTombstoneLists correctly
when communicating with 2.2 nodes.
@jira_ticket CASSANDRA-11930
"""
session = self.prepare(start_rpc=True)
node = self.cluster.nodelist()[0]
host, port = node.network_interfaces['thrift']
client = get_thrift_client(host, port)
client.transport.open()
client.set_keyspace('ks')
# create a CF with mixed static and dynamic cols
column_defs = [ColumnDef('static1'.encode(), 'Int32Type', None, None, None)]
cfdef = CfDef(
keyspace='ks',
name='cf',
column_type='Standard',
comparator_type='AsciiType',
key_validation_class='AsciiType',
default_validation_class='AsciiType',
column_metadata=column_defs)
client.system_add_column_family(cfdef)
session.cluster.control_connection.wait_for_schema_agreement()
for is_upgraded, session, node in self.do_upgrade(session, return_nodes=True):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
upgrade_to_version = self.get_node_version(is_upgraded=True)
if LooseVersion('3.0.0') <= upgrade_to_version <= LooseVersion('3.0.6'):
pytest.skip(msg='CASSANDRA-11930 was fixed in 3.0.7 and 3.7')
elif LooseVersion('3.1') <= upgrade_to_version <= LooseVersion('3.6'):
pytest.skip(msg='CASSANDRA-11930 was fixed in 3.0.7 and 3.7')
session.execute("TRUNCATE ks.cf")
host, port = node.network_interfaces['thrift']
client = get_thrift_client(host, port)
client.transport.open()
client.set_keyspace('ks')
# insert a number of keys so that we'll get rows on both the old and upgraded nodes
for key in ['key{}'.format(i).encode() for i in range(10)]:
logger.debug("Using key " + key.decode())
# insert "static" column
client.batch_mutate(
{key: {'cf': [Mutation(ColumnOrSuperColumn(column=Column(name='static1'.encode(), value=struct.pack('>i', 1), timestamp=100)))]}},
ThriftConsistencyLevel.ALL)
# insert "dynamic" columns
for i, column_name in enumerate(('a', 'b', 'c', 'd', 'e')):
column_value = 'val{}'.format(i)
client.batch_mutate(
{key: {'cf': [Mutation(ColumnOrSuperColumn(column=Column(name=column_name.encode(), value=column_value.encode(), timestamp=100)))]}},
ThriftConsistencyLevel.ALL)
# sanity check on the query
fetch_slice = SlicePredicate(slice_range=SliceRange(''.encode(), ''.encode(), False, 100))
row = client.get_slice(key, ColumnParent(column_family='cf'), fetch_slice, ThriftConsistencyLevel.ALL)
assert 6 == len(row), row
cols = OrderedDict([(cosc.column.name.decode(), cosc.column.value) for cosc in row])
logger.debug(cols)
assert ['a', 'b', 'c', 'd', 'e', 'static1'] == list(cols.keys())
assert 'val0'.encode() == cols['a']
assert 'val4'.encode() == cols['e']
assert struct.pack('>i', 1) == cols['static1']
# delete a slice of dynamic columns
slice_range = SliceRange('b'.encode(), 'd'.encode(), False, 100)
client.batch_mutate(
{key: {'cf': [Mutation(deletion=Deletion(timestamp=101, predicate=SlicePredicate(slice_range=slice_range)))]}},
ThriftConsistencyLevel.ALL)
# check remaining columns
row = client.get_slice(key, ColumnParent(column_family='cf'), fetch_slice, ThriftConsistencyLevel.ALL)
assert 3 == len(row), row
cols = OrderedDict([(cosc.column.name.decode(), cosc.column.value) for cosc in row])
logger.debug(cols)
assert ['a', 'e', 'static1'] == list(cols.keys())
assert 'val0'.encode() == cols['a']
assert 'val4'.encode() == cols['e']
assert struct.pack('>i', 1) == cols['static1']
def test_row_existence(self):
"""
Check the semantic of CQL row existence (part of #4361)
@jira_ticket CASSANDRA-4361
"""
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k int,
c int,
v1 int,
v2 int,
PRIMARY KEY (k, c)
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.execute("INSERT INTO test (k, c, v1, v2) VALUES (1, 1, 1, 1)")
assert_one(cursor, "SELECT * FROM test", [1, 1, 1, 1])
assert_invalid(cursor, "DELETE c FROM test WHERE k = 1 AND c = 1")
cursor.execute("DELETE v2 FROM test WHERE k = 1 AND c = 1")
assert_one(cursor, "SELECT * FROM test", [1, 1, 1, None])
cursor.execute("DELETE v1 FROM test WHERE k = 1 AND c = 1")
assert_one(cursor, "SELECT * FROM test", [1, 1, None, None])
cursor.execute("DELETE FROM test WHERE k = 1 AND c = 1")
assert_none(cursor, "SELECT * FROM test", )
cursor.execute("INSERT INTO test (k, c) VALUES (2, 2)")
assert_one(cursor, "SELECT * FROM test", [2, 2, None, None])
def test_only_pk(self):
"""
Check table with only a PK (part of #4361)
@jira_ticket CASSANDRA-4361
"""
cursor = self.prepare(ordered=True)
cursor.execute("""
CREATE TABLE test (
k int,
c int,
PRIMARY KEY (k, c)
)
""")
# Check for dense tables too
cursor.execute("""
CREATE TABLE test2 (
k int,
c int,
PRIMARY KEY (k, c)
) WITH COMPACT STORAGE
""")
#4.0 doesn't support compact storage
if self.is_40_or_greater():
cursor.execute("ALTER TABLE test2 DROP COMPACT STORAGE;")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.execute("TRUNCATE test2")
q = "INSERT INTO test (k, c) VALUES (%s, %s)"
for k in range(0, 2):
for c in range(0, 2):
cursor.execute(q, (k, c))
query = "SELECT * FROM test"
assert_all(cursor, query, [[x, y] for x in range(0, 2) for y in range(0, 2)])
q = "INSERT INTO test2 (k, c) VALUES (%s, %s)"
for k in range(0, 2):
for c in range(0, 2):
cursor.execute(q, (k, c))
query = "SELECT * FROM test2"
expected = [[x, y, None] for x in range(0, 2) for y in range(0, 2)] if self.is_40_or_greater() else [
[x, y] for x in range(0, 2) for y in range(0, 2)]
assert_all(cursor, query, expected)
def test_no_clustering(self):
cursor = self.prepare()
cursor.execute("CREATE TABLE test (k int PRIMARY KEY, v int)")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
for i in range(10):
cursor.execute("INSERT INTO test (k, v) VALUES (%s, %s)", (i, i))
cursor.default_fetch_size = None
assert_all(cursor, "SELECT * FROM test", [[i, i] for i in range(10)], ignore_order=True)
def test_date(self):
""" Check dates are correctly recognized and validated """
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k int PRIMARY KEY,
t timestamp
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.execute("INSERT INTO test (k, t) VALUES (0, '2011-02-03')")
assert_invalid(cursor, "INSERT INTO test (k, t) VALUES (0, '2011-42-42')")
def test_range_slice(self):
"""
Test a regression from #1337
@jira_ticket CASSANDRA-1337
"""
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k text PRIMARY KEY,
v int
);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.execute("INSERT INTO test (k, v) VALUES ('foo', 0)")
cursor.execute("INSERT INTO test (k, v) VALUES ('bar', 1)")
assert_row_count(cursor, 'test', 2)
def test_composite_index_with_pk(self):
cursor = self.prepare(ordered=True)
cursor.execute("""
CREATE TABLE blogs (
blog_id int,
time1 int,
time2 int,
author text,
content text,
PRIMARY KEY (blog_id, time1, time2)
)
""")
cursor.execute("CREATE INDEX ON blogs(author)")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE blogs")
req = "INSERT INTO blogs (blog_id, time1, time2, author, content) VALUES (%d, %d, %d, '%s', '%s')"
cursor.execute(req % (1, 0, 0, 'foo', 'bar1'))
cursor.execute(req % (1, 0, 1, 'foo', 'bar2'))
cursor.execute(req % (2, 1, 0, 'foo', 'baz'))
cursor.execute(req % (3, 0, 1, 'gux', 'qux'))
query = "SELECT blog_id, content FROM blogs WHERE author='foo'"
assert_all(cursor, query, [[1, 'bar1'], [1, 'bar2'], [2, 'baz']])
query = "SELECT blog_id, content FROM blogs WHERE time1 > 0 AND author='foo' ALLOW FILTERING"
assert_one(cursor, query, [2, 'baz'])
query = "SELECT blog_id, content FROM blogs WHERE time1 = 1 AND author='foo' ALLOW FILTERING"
assert_one(cursor, query, [2, 'baz'])
query = "SELECT blog_id, content FROM blogs WHERE time1 = 1 AND time2 = 0 AND author='foo' ALLOW FILTERING"
assert_one(cursor, query, [2, 'baz'])
query = "SELECT content FROM blogs WHERE time1 = 1 AND time2 = 1 AND author='foo' ALLOW FILTERING"
assert_none(cursor, query)
query = "SELECT content FROM blogs WHERE time1 = 1 AND time2 > 0 AND author='foo' ALLOW FILTERING"
assert_none(cursor, query)
assert_invalid(cursor, "SELECT content FROM blogs WHERE time2 >= 0 AND author='foo'")
# as discussed in CASSANDRA-8148, some queries that should have required ALLOW FILTERING
# in 2.0 have been fixed for 2.2
if self.get_node_version(is_upgraded) < '2.2':
# the coordinator is the non-upgraded 2.1 node
cursor.execute("SELECT blog_id, content FROM blogs WHERE time1 > 0 AND author='foo'")
cursor.execute("SELECT blog_id, content FROM blogs WHERE time1 = 1 AND author='foo'")
cursor.execute("SELECT blog_id, content FROM blogs WHERE time1 = 1 AND time2 = 0 AND author='foo'")
cursor.execute("SELECT content FROM blogs WHERE time1 = 1 AND time2 = 1 AND author='foo'")
cursor.execute("SELECT content FROM blogs WHERE time1 = 1 AND time2 > 0 AND author='foo'")
else:
# the coordinator is the upgraded 2.2+ node
assert_invalid(cursor, "SELECT blog_id, content FROM blogs WHERE time1 > 0 AND author='foo'")
assert_invalid(cursor, "SELECT blog_id, content FROM blogs WHERE time1 = 1 AND author='foo'")
assert_invalid(cursor, "SELECT blog_id, content FROM blogs WHERE time1 = 1 AND time2 = 0 AND author='foo'")
assert_invalid(cursor, "SELECT content FROM blogs WHERE time1 = 1 AND time2 = 1 AND author='foo'")
assert_invalid(cursor, "SELECT content FROM blogs WHERE time1 = 1 AND time2 > 0 AND author='foo'")
def test_limit_bugs(self):
"""
Test for LIMIT bugs from #4579
@jira_ticket CASSANDRA-4579
"""
cursor = self.prepare(ordered=True)
cursor.execute("""
CREATE TABLE testcf (
a int,
b int,
c int,
d int,
e int,
PRIMARY KEY (a, b)
);
""")
cursor.execute("""
CREATE TABLE testcf2 (
a int primary key,
b int,
c int,
);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE testcf")
cursor.execute("TRUNCATE testcf2")
cursor.execute("INSERT INTO testcf (a, b, c, d, e) VALUES (1, 1, 1, 1, 1);")
cursor.execute("INSERT INTO testcf (a, b, c, d, e) VALUES (2, 2, 2, 2, 2);")
cursor.execute("INSERT INTO testcf (a, b, c, d, e) VALUES (3, 3, 3, 3, 3);")
cursor.execute("INSERT INTO testcf (a, b, c, d, e) VALUES (4, 4, 4, 4, 4);")
assert_all(cursor, "SELECT * FROM testcf", [[1, 1, 1, 1, 1], [2, 2, 2, 2, 2], [3, 3, 3, 3, 3], [4, 4, 4, 4, 4]])
assert_all(cursor, "SELECT * FROM testcf LIMIT 1;", [[1, 1, 1, 1, 1]])
assert_all(cursor, "SELECT * FROM testcf LIMIT 2;", [[1, 1, 1, 1, 1], [2, 2, 2, 2, 2]])
cursor.execute("INSERT INTO testcf2 (a, b, c) VALUES (1, 1, 1);")
cursor.execute("INSERT INTO testcf2 (a, b, c) VALUES (2, 2, 2);")
cursor.execute("INSERT INTO testcf2 (a, b, c) VALUES (3, 3, 3);")
cursor.execute("INSERT INTO testcf2 (a, b, c) VALUES (4, 4, 4);")
assert_all(cursor, "SELECT * FROM testcf2;", [[1, 1, 1], [2, 2, 2], [3, 3, 3], [4, 4, 4]])
assert_all(cursor, "SELECT * FROM testcf2 LIMIT 1;", [[1, 1, 1]])
assert_all(cursor, "SELECT * FROM testcf2 LIMIT 2;", [[1, 1, 1], [2, 2, 2]])
assert_all(cursor, "SELECT * FROM testcf2 LIMIT 3;", [[1, 1, 1], [2, 2, 2], [3, 3, 3]])
assert_all(cursor, "SELECT * FROM testcf2 LIMIT 4;", [[1, 1, 1], [2, 2, 2], [3, 3, 3], [4, 4, 4]])
assert_all(cursor, "SELECT * FROM testcf2 LIMIT 5;", [[1, 1, 1], [2, 2, 2], [3, 3, 3], [4, 4, 4]])
def test_npe_composite_table_slice(self):
"""
Test for NPE when trying to select a slice from a composite table
@jira_ticket CASSANDRA-4532
"""
cursor = self.prepare()
cursor.execute("""
CREATE TABLE compositetest(
status ascii,
ctime bigint,
key ascii,
nil ascii,
PRIMARY KEY (status, ctime, key)
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE compositetest")
cursor.execute("INSERT INTO compositetest(status,ctime,key,nil) VALUES ('C',12345678,'key1','')")
cursor.execute("INSERT INTO compositetest(status,ctime,key,nil) VALUES ('C',12345678,'key2','')")
cursor.execute("INSERT INTO compositetest(status,ctime,key,nil) VALUES ('C',12345679,'key3','')")
cursor.execute("INSERT INTO compositetest(status,ctime,key,nil) VALUES ('C',12345679,'key4','')")
cursor.execute("INSERT INTO compositetest(status,ctime,key,nil) VALUES ('C',12345679,'key5','')")
cursor.execute("INSERT INTO compositetest(status,ctime,key,nil) VALUES ('C',12345680,'key6','')")
assert_invalid(cursor, "SELECT * FROM compositetest WHERE ctime>=12345679 AND key='key3' AND ctime<=12345680 LIMIT 3;")
assert_invalid(cursor, "SELECT * FROM compositetest WHERE ctime=12345679 AND key='key3' AND ctime<=12345680 LIMIT 3;")
def test_order_by_multikey(self):
"""
Test for #4612 bug and more generally order by when multiple C* rows are queried
@jira_ticket CASSANDRA-4612
"""
cursor = self.prepare(ordered=True)
cursor.execute("""
CREATE TABLE test(
my_id varchar,
col1 int,
col2 int,
value varchar,
PRIMARY KEY (my_id, col1, col2)
);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.default_fetch_size = None
cursor.execute("INSERT INTO test(my_id, col1, col2, value) VALUES ( 'key1', 1, 1, 'a');")
cursor.execute("INSERT INTO test(my_id, col1, col2, value) VALUES ( 'key2', 3, 3, 'a');")
cursor.execute("INSERT INTO test(my_id, col1, col2, value) VALUES ( 'key3', 2, 2, 'b');")
cursor.execute("INSERT INTO test(my_id, col1, col2, value) VALUES ( 'key4', 2, 1, 'b');")
query = "SELECT col1 FROM test WHERE my_id in('key1', 'key2', 'key3') ORDER BY col1;"
assert_all(cursor, query, [[1], [2], [3]])
query = "SELECT col1, value, my_id, col2 FROM test WHERE my_id in('key3', 'key4') ORDER BY col1, col2;"
assert_all(cursor, query, [[2, 'b', 'key4', 1], [2, 'b', 'key3', 2]])
assert_invalid(cursor, "SELECT col1 FROM test ORDER BY col1;")
assert_invalid(cursor, "SELECT col1 FROM test WHERE my_id > 'key1' ORDER BY col1;")
def test_remove_range_slice(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k int PRIMARY KEY,
v int
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
for i in range(0, 3):
cursor.execute("INSERT INTO test (k, v) VALUES (%d, %d)" % (i, i))
cursor.execute("DELETE FROM test WHERE k = 1")
assert_all(cursor, "SELECT * FROM test", [[0, 0], [2, 2]])
def test_indexes_composite(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
blog_id int,
timestamp int,
author text,
content text,
PRIMARY KEY (blog_id, timestamp)
)
""")
cursor.execute("CREATE INDEX ON test(author)")
time.sleep(1)
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
req = "INSERT INTO test (blog_id, timestamp, author, content) VALUES (%d, %d, '%s', '%s')"
cursor.execute(req % (0, 0, "bob", "1st post"))
cursor.execute(req % (0, 1, "tom", "2nd post"))
cursor.execute(req % (0, 2, "bob", "3rd post"))
cursor.execute(req % (0, 3, "tom", "4nd post"))
cursor.execute(req % (1, 0, "bob", "5th post"))
query = "SELECT blog_id, timestamp FROM test WHERE author = 'bob'"
assert_all(cursor, query, [[1, 0], [0, 0], [0, 2]])
cursor.execute(req % (1, 1, "tom", "6th post"))
cursor.execute(req % (1, 2, "tom", "7th post"))
cursor.execute(req % (1, 3, "bob", "8th post"))
query = "SELECT blog_id, timestamp FROM test WHERE author = 'bob'"
assert_all(cursor, query, [[1, 0], [1, 3], [0, 0], [0, 2]])
cursor.execute("DELETE FROM test WHERE blog_id = 0 AND timestamp = 2")
query = "SELECT blog_id, timestamp FROM test WHERE author = 'bob'"
assert_all(cursor, query, [[1, 0], [1, 3], [0, 0]])
def test_refuse_in_with_indexes(self):
"""
Test for the validation bug of #4709
@jira_ticket CASSANDRA-4709
"""
cursor = self.prepare()
cursor.execute("create table t1 (pk varchar primary key, col1 varchar, col2 varchar);")
cursor.execute("create index t1_c1 on t1(col1);")
cursor.execute("create index t1_c2 on t1(col2);")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE t1")
cursor.execute("insert into t1 (pk, col1, col2) values ('pk1','foo1','bar1');")
cursor.execute("insert into t1 (pk, col1, col2) values ('pk1a','foo1','bar1');")
cursor.execute("insert into t1 (pk, col1, col2) values ('pk1b','foo1','bar1');")
cursor.execute("insert into t1 (pk, col1, col2) values ('pk1c','foo1','bar1');")
cursor.execute("insert into t1 (pk, col1, col2) values ('pk2','foo2','bar2');")
cursor.execute("insert into t1 (pk, col1, col2) values ('pk3','foo3','bar3');")
assert_invalid(cursor, "select * from t1 where col2 in ('bar1', 'bar2');")
def test_reversed_compact(self):
"""
Test for #4716 bug and more generally for good behavior of ordering
@jira_ticket CASSANDRA-4716
"""
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test1 (
k text,
c int,
v int,
PRIMARY KEY (k, c)
) WITH COMPACT STORAGE
AND CLUSTERING ORDER BY (c DESC);
""")
cursor.execute("""
CREATE TABLE test2 (
k text,
c int,
v int,
PRIMARY KEY (k, c)
) WITH COMPACT STORAGE;
""")
#4.0 doesn't support compact storage
if self.is_40_or_greater():
cursor.execute("ALTER TABLE test1 DROP COMPACT STORAGE;")
cursor.execute("ALTER TABLE test2 DROP COMPACT STORAGE;")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test1")
cursor.execute("TRUNCATE test2")
for i in range(0, 10):
cursor.execute("INSERT INTO test1(k, c, v) VALUES ('foo', %s, %s)", (i, i))
query = "SELECT c FROM test1 WHERE c > 2 AND c < 6 AND k = 'foo'"
assert_all(cursor, query, [[5], [4], [3]])
query = "SELECT c FROM test1 WHERE c >= 2 AND c <= 6 AND k = 'foo'"
assert_all(cursor, query, [[6], [5], [4], [3], [2]])
query = "SELECT c FROM test1 WHERE c > 2 AND c < 6 AND k = 'foo' ORDER BY c ASC"
assert_all(cursor, query, [[3], [4], [5]])
query = "SELECT c FROM test1 WHERE c >= 2 AND c <= 6 AND k = 'foo' ORDER BY c ASC"
assert_all(cursor, query, [[2], [3], [4], [5], [6]])
query = "SELECT c FROM test1 WHERE c > 2 AND c < 6 AND k = 'foo' ORDER BY c DESC"
assert_all(cursor, query, [[5], [4], [3]])
query = "SELECT c FROM test1 WHERE c >= 2 AND c <= 6 AND k = 'foo' ORDER BY c DESC"
assert_all(cursor, query, [[6], [5], [4], [3], [2]])
for i in range(0, 10):
cursor.execute("INSERT INTO test2(k, c, v) VALUES ('foo', %s, %s)", (i, i))
query = "SELECT c FROM test2 WHERE c > 2 AND c < 6 AND k = 'foo'"
assert_all(cursor, query, [[3], [4], [5]])
query = "SELECT c FROM test2 WHERE c >= 2 AND c <= 6 AND k = 'foo'"
assert_all(cursor, query, [[2], [3], [4], [5], [6]])
query = "SELECT c FROM test2 WHERE c > 2 AND c < 6 AND k = 'foo' ORDER BY c ASC"
assert_all(cursor, query, [[3], [4], [5]])
query = "SELECT c FROM test2 WHERE c >= 2 AND c <= 6 AND k = 'foo' ORDER BY c ASC"
assert_all(cursor, query, [[2], [3], [4], [5], [6]])
query = "SELECT c FROM test2 WHERE c > 2 AND c < 6 AND k = 'foo' ORDER BY c DESC"
assert_all(cursor, query, [[5], [4], [3]])
query = "SELECT c FROM test2 WHERE c >= 2 AND c <= 6 AND k = 'foo' ORDER BY c DESC"
assert_all(cursor, query, [[6], [5], [4], [3], [2]])
def test_reversed_compact_multikey(self):
"""
Test for the bug from #4760 and #4759
@jira_ticket CASSANDRA-4760
@jira_ticket CASSANDRA-4759
"""
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
key text,
c1 int,
c2 int,
value text,
PRIMARY KEY(key, c1, c2)
) WITH COMPACT STORAGE
AND CLUSTERING ORDER BY(c1 DESC, c2 DESC);
""")
#4.0 doesn't support compact storage
if self.is_40_or_greater():
cursor.execute("ALTER TABLE test DROP COMPACT STORAGE;")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
for i in range(0, 3):
for j in range(0, 3):
cursor.execute("INSERT INTO test(key, c1, c2, value) VALUES ('foo', %i, %i, 'bar');" % (i, j))
# Equalities
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 = 1"
assert_all(cursor, query, [[1, 2], [1, 1], [1, 0]])
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 = 1 ORDER BY c1 ASC, c2 ASC"
assert_all(cursor, query, [[1, 0], [1, 1], [1, 2]])
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 = 1 ORDER BY c1 DESC, c2 DESC"
assert_all(cursor, query, [[1, 2], [1, 1], [1, 0]])
# GT
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 > 1"
assert_all(cursor, query, [[2, 2], [2, 1], [2, 0]])
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 > 1 ORDER BY c1 ASC, c2 ASC"
assert_all(cursor, query, [[2, 0], [2, 1], [2, 2]])
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 > 1 ORDER BY c1 DESC, c2 DESC"
assert_all(cursor, query, [[2, 2], [2, 1], [2, 0]])
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 >= 1"
assert_all(cursor, query, [[2, 2], [2, 1], [2, 0], [1, 2], [1, 1], [1, 0]])
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 >= 1 ORDER BY c1 ASC, c2 ASC"
assert_all(cursor, query, [[1, 0], [1, 1], [1, 2], [2, 0], [2, 1], [2, 2]])
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 >= 1 ORDER BY c1 ASC"
assert_all(cursor, query, [[1, 0], [1, 1], [1, 2], [2, 0], [2, 1], [2, 2]])
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 >= 1 ORDER BY c1 DESC, c2 DESC"
assert_all(cursor, query, [[2, 2], [2, 1], [2, 0], [1, 2], [1, 1], [1, 0]])
# LT
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 < 1"
assert_all(cursor, query, [[0, 2], [0, 1], [0, 0]])
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 < 1 ORDER BY c1 ASC, c2 ASC"
assert_all(cursor, query, [[0, 0], [0, 1], [0, 2]])
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 < 1 ORDER BY c1 DESC, c2 DESC"
assert_all(cursor, query, [[0, 2], [0, 1], [0, 0]])
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 <= 1"
assert_all(cursor, query, [[1, 2], [1, 1], [1, 0], [0, 2], [0, 1], [0, 0]])
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 <= 1 ORDER BY c1 ASC, c2 ASC"
assert_all(cursor, query, [[0, 0], [0, 1], [0, 2], [1, 0], [1, 1], [1, 2]])
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 <= 1 ORDER BY c1 ASC"
assert_all(cursor, query, [[0, 0], [0, 1], [0, 2], [1, 0], [1, 1], [1, 2]])
query = "SELECT c1, c2 FROM test WHERE key='foo' AND c1 <= 1 ORDER BY c1 DESC, c2 DESC"
assert_all(cursor, query, [[1, 2], [1, 1], [1, 0], [0, 2], [0, 1], [0, 0]])
def test_collection_and_regular(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k int PRIMARY KEY,
l list<int>,
c int
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.execute("INSERT INTO test(k, l, c) VALUES(3, [0, 1, 2], 4)")
cursor.execute("UPDATE test SET l[0] = 1, c = 42 WHERE k = 3")
assert_one(cursor, "SELECT l, c FROM test WHERE k = 3", [[1, 1, 2], 42])
def test_batch_and_list(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k int PRIMARY KEY,
l list<int>
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.execute("""
BEGIN BATCH
UPDATE test SET l = l + [ 1 ] WHERE k = 0;
UPDATE test SET l = l + [ 2 ] WHERE k = 0;
UPDATE test SET l = l + [ 3 ] WHERE k = 0;
APPLY BATCH
""")
assert_one(cursor, "SELECT l FROM test WHERE k = 0", [[1, 2, 3]])
cursor.execute("""
BEGIN BATCH
UPDATE test SET l = [ 1 ] + l WHERE k = 1;
UPDATE test SET l = [ 2 ] + l WHERE k = 1;
UPDATE test SET l = [ 3 ] + l WHERE k = 1;
APPLY BATCH
""")
assert_one(cursor, "SELECT l FROM test WHERE k = 1", [[3, 2, 1]])
def test_boolean(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k boolean PRIMARY KEY,
b boolean
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.execute("INSERT INTO test (k, b) VALUES (true, false)")
assert_one(cursor, "SELECT * FROM test WHERE k = true", [True, False])
def test_multiordering(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k text,
c1 int,
c2 int,
PRIMARY KEY (k, c1, c2)
) WITH CLUSTERING ORDER BY (c1 ASC, c2 DESC);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
for i in range(0, 2):
for j in range(0, 2):
cursor.execute("INSERT INTO test(k, c1, c2) VALUES ('foo', %i, %i)" % (i, j))
query = "SELECT c1, c2 FROM test WHERE k = 'foo'"
assert_all(cursor, query, [[0, 1], [0, 0], [1, 1], [1, 0]])
query = "SELECT c1, c2 FROM test WHERE k = 'foo' ORDER BY c1 ASC, c2 DESC"
assert_all(cursor, query, [[0, 1], [0, 0], [1, 1], [1, 0]])
query = "SELECT c1, c2 FROM test WHERE k = 'foo' ORDER BY c1 DESC, c2 ASC"
assert_all(cursor, query, [[1, 0], [1, 1], [0, 0], [0, 1]])
assert_invalid(cursor, "SELECT c1, c2 FROM test WHERE k = 'foo' ORDER BY c2 DESC")
assert_invalid(cursor, "SELECT c1, c2 FROM test WHERE k = 'foo' ORDER BY c2 ASC")
assert_invalid(cursor, "SELECT c1, c2 FROM test WHERE k = 'foo' ORDER BY c1 ASC, c2 ASC")
def test_returned_null(self):
"""
Test for returned null.
StorageProxy short read protection hadn't been updated after the changes made by CASSANDRA-3647,
namely the fact that SliceQueryFilter groups columns by prefix before counting them.
@jira_ticket CASSANDRA-4882
"""
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k int,
c1 int,
c2 int,
v int,
PRIMARY KEY (k, c1, c2)
) WITH CLUSTERING ORDER BY (c1 ASC, c2 DESC);
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
cursor.execute("INSERT INTO test (k, c1, c2, v) VALUES (0, 0, 0, 0);")
cursor.execute("INSERT INTO test (k, c1, c2, v) VALUES (0, 1, 1, 1);")
cursor.execute("INSERT INTO test (k, c1, c2, v) VALUES (0, 0, 2, 2);")
cursor.execute("INSERT INTO test (k, c1, c2, v) VALUES (0, 1, 3, 3);")
query = "SELECT * FROM test WHERE k = 0 LIMIT 1;"
assert_one(cursor, query, [0, 0, 2, 2])
def test_multi_list_set(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k int PRIMARY KEY,
l1 list<int>,
l2 list<int>
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
cursor.execute("TRUNCATE test")
cursor.execute("INSERT INTO test (k, l1, l2) VALUES (0, [1, 2, 3], [4, 5, 6])")
cursor.execute("UPDATE test SET l2[1] = 42, l1[1] = 24 WHERE k = 0")
assert_one(cursor, "SELECT l1, l2 FROM test WHERE k = 0", [[1, 24, 3], [4, 42, 6]])
def test_composite_index_collections(self):
cursor = self.prepare(ordered=True)
cursor.execute("""
CREATE TABLE blogs (
blog_id int,
time1 int,
time2 int,
author text,
content set<text>,
PRIMARY KEY (blog_id, time1, time2)
)
""")
cursor.execute("CREATE INDEX ON blogs(author)")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE blogs")
req = "INSERT INTO blogs (blog_id, time1, time2, author, content) VALUES (%d, %d, %d, '%s', %s)"
cursor.execute(req % (1, 0, 0, 'foo', "{ 'bar1', 'bar2' }"))
cursor.execute(req % (1, 0, 1, 'foo', "{ 'bar2', 'bar3' }"))
cursor.execute(req % (2, 1, 0, 'foo', "{ 'baz' }"))
cursor.execute(req % (3, 0, 1, 'gux', "{ 'qux' }"))
query = "SELECT blog_id, content FROM blogs WHERE author='foo'"
assert_all(cursor, query, [[1, set(['bar1', 'bar2'])], [1, set(['bar2', 'bar3'])], [2, set(['baz'])]])
@pytest.mark.skip("https://issues.apache.org/jira/browse/CASSANDRA-14961")
def test_truncate_clean_cache(self):
cursor = self.prepare(ordered=True, use_cache=True)
if self.node_version_above('2.1'):
cursor.execute("""
CREATE TABLE test (
k int PRIMARY KEY,
v1 int,
v2 int,
) WITH caching = {'keys': 'NONE', 'rows_per_partition': 'ALL'};
""")
else:
cursor.execute("""
CREATE TABLE test (
k int PRIMARY KEY,
v1 int,
v2 int,
) WITH CACHING = ALL;
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
for i in range(0, 3):
cursor.execute("INSERT INTO test(k, v1, v2) VALUES (%d, %d, %d)" % (i, i, i * 2))
query = "SELECT v1, v2 FROM test WHERE k IN (0, 1, 2)"
assert_all(cursor, query, [[0, 0], [1, 2], [2, 4]])
cursor.execute("TRUNCATE test")
query = "SELECT v1, v2 FROM test WHERE k IN (0, 1, 2)"
assert_none(cursor, query)
def test_range_with_deletes(self):
cursor = self.prepare()
cursor.execute("""
CREATE TABLE test (
k int PRIMARY KEY,
v int,
)
""")
for is_upgraded, cursor in self.do_upgrade(cursor):
logger.debug("Querying {} node".format("upgraded" if is_upgraded else "old"))
cursor.execute("TRUNCATE test")
nb_keys = 30
nb_deletes = 5
for i in range(