blob: 6983d3f05cf776b5302cd649da3f59b53f091112 [file] [log] [blame]
import time
import uuid
import pytest
import logging
from flaky import flaky
from distutils.version import LooseVersion
from cassandra import ConsistencyLevel as CL
from cassandra import InvalidRequest, ReadFailure, ReadTimeout
from cassandra.policies import FallthroughRetryPolicy
from cassandra.query import (SimpleStatement, dict_factory,
named_tuple_factory, tuple_factory)
from dtest import Tester, run_scenarios, create_ks
from tools.assertions import (assert_all, assert_invalid, assert_length_equal,
assert_one, assert_lists_equal_ignoring_order)
from tools.data import rows_to_list
from tools.datahelp import create_rows, flatten_into_set, parse_data_into_dicts
from tools.paging import PageAssertionMixin, PageFetcher
since = pytest.mark.since
logger = logging.getLogger(__name__)
class BasePagingTester(Tester):
def prepare(self, row_factory=dict_factory):
supports_v5 = self.supports_v5_protocol(self.cluster.version())
protocol_version = 5 if supports_v5 else None
cluster = self.cluster
cluster.populate(3).start()
node1 = cluster.nodelist()[0]
session = self.patient_cql_connection(node1,
protocol_version=protocol_version,
consistency_level=CL.QUORUM,
row_factory=row_factory)
return session
@since('2.0')
class TestPagingSize(BasePagingTester, PageAssertionMixin):
"""
Basic tests relating to page size (relative to results set)
and validation of page size setting.
"""
def test_with_no_results(self):
"""
No errors when a page is requested and query has no results.
"""
session = self.prepare()
create_ks(session, 'test_paging_size', 2)
session.execute("CREATE TABLE paging_test ( id int PRIMARY KEY, value text )")
# run a query that has no results and make sure it's exhausted
future = session.execute_async(
SimpleStatement("select * from paging_test", fetch_size=100, consistency_level=CL.ALL)
)
pf = PageFetcher(future)
pf.request_all()
assert [] == pf.all_data()
assert not pf.has_more_pages
def test_with_less_results_than_page_size(self):
session = self.prepare()
create_ks(session, 'test_paging_size', 2)
session.execute("CREATE TABLE paging_test ( id int PRIMARY KEY, value text )")
data = """
|id| value |
+--+----------------+
|1 |testing |
|2 |and more testing|
|3 |and more testing|
|4 |and more testing|
|5 |and more testing|
"""
expected_data = create_rows(data, session, 'paging_test', cl=CL.ALL, format_funcs={'id': int, 'value': str})
future = session.execute_async(
SimpleStatement("select * from paging_test", fetch_size=100, consistency_level=CL.ALL)
)
pf = PageFetcher(future)
pf.request_all()
assert not pf.has_more_pages
assert len(expected_data) == len(pf.all_data())
def test_with_more_results_than_page_size(self):
session = self.prepare()
create_ks(session, 'test_paging_size', 2)
session.execute("CREATE TABLE paging_test ( id int PRIMARY KEY, value text )")
data = """
|id| value |
+--+----------------+
|1 |testing |
|2 |and more testing|
|3 |and more testing|
|4 |and more testing|
|5 |and more testing|
|6 |testing |
|7 |and more testing|
|8 |and more testing|
|9 |and more testing|
"""
expected_data = create_rows(data, session, 'paging_test', cl=CL.ALL, format_funcs={'id': int, 'value': str})
future = session.execute_async(
SimpleStatement("select * from paging_test", fetch_size=5, consistency_level=CL.ALL)
)
pf = PageFetcher(future).request_all()
assert pf.pagecount() == 2
assert pf.num_results_all() == [5, 4]
# make sure expected and actual have same data elements (ignoring order)
assert_lists_equal_ignoring_order(expected_data, pf.all_data(), sort_key="id")
def test_with_equal_results_to_page_size(self):
session = self.prepare()
create_ks(session, 'test_paging_size', 2)
session.execute("CREATE TABLE paging_test ( id int PRIMARY KEY, value text )")
data = """
|id| value |
+--+----------------+
|1 |testing |
|2 |and more testing|
|3 |and more testing|
|4 |and more testing|
|5 |and more testing|
"""
expected_data = create_rows(data, session, 'paging_test', cl=CL.ALL, format_funcs={'id': int, 'value': str})
future = session.execute_async(
SimpleStatement("select * from paging_test", fetch_size=5, consistency_level=CL.ALL)
)
pf = PageFetcher(future).request_all()
assert pf.num_results_all() == [5]
assert pf.pagecount() == 1
# make sure expected and actual have same data elements (ignoring order)
assert_lists_equal_ignoring_order(expected_data, pf.all_data(), sort_key="id")
def test_undefined_page_size_default(self):
"""
If the page size isn't sent then the default fetch size is used.
"""
session = self.prepare()
create_ks(session, 'test_paging_size', 2)
session.execute("CREATE TABLE paging_test ( id uuid PRIMARY KEY, value text )")
def random_txt(text):
return uuid.uuid4()
data = """
| id |value |
+--------+--------+
*5001| [uuid] |testing |
"""
expected_data = create_rows(data, session, 'paging_test', cl=CL.ALL, format_funcs={'id': random_txt, 'value': str})
future = session.execute_async(
SimpleStatement("select * from paging_test", consistency_level=CL.ALL)
)
pf = PageFetcher(future).request_all()
assert pf.num_results_all(), [5000, 1]
# make sure expected and actual have same data elements (ignoring order)
assert_lists_equal_ignoring_order(expected_data, pf.all_data(), sort_key="id")
@since('2.0')
class TestPagingWithModifiers(BasePagingTester, PageAssertionMixin):
"""
Tests concerned with paging when CQL modifiers (such as order, limit, allow filtering) are used.
"""
def test_with_order_by(self):
""""
Paging over a single partition with ordering should work.
(Spanning multiple partitions won't though, by design. See CASSANDRA-6722).
"""
session = self.prepare()
create_ks(session, 'test_paging', 2)
session.execute(
"""
CREATE TABLE paging_test (
id int,
value text,
PRIMARY KEY (id, value)
) WITH CLUSTERING ORDER BY (value ASC)
""")
data = """
|id|value|
+--+-----+
|1 |a |
|1 |b |
|1 |c |
|1 |d |
|1 |e |
|1 |f |
|1 |g |
|1 |h |
|1 |i |
|1 |j |
"""
expected_data = create_rows(data, session, 'paging_test', cl=CL.ALL, format_funcs={'id': int, 'value': str})
future = session.execute_async(
SimpleStatement("select * from paging_test where id = 1 order by value asc", fetch_size=5, consistency_level=CL.ALL)
)
pf = PageFetcher(future).request_all()
assert pf.pagecount() == 2
assert pf.num_results_all() == [5, 5]
# these should be equal (in the same order)
assert pf.all_data() == expected_data
# make sure we don't allow paging over multiple partitions with order because that's weird
with pytest.raises(InvalidRequest, match='Cannot page queries with both ORDER BY and a IN restriction on the partition key'):
stmt = SimpleStatement("select * from paging_test where id in (1,2) order by value asc", consistency_level=CL.ALL)
session.execute(stmt)
def test_with_order_by_reversed(self):
""""
Paging over a single partition with ordering and a reversed clustering order.
"""
session = self.prepare()
create_ks(session, 'test_paging', 2)
session.execute(
"""
CREATE TABLE paging_test (
id int,
value text,
value2 text,
PRIMARY KEY (id, value)
) WITH CLUSTERING ORDER BY (value DESC)
""")
data = """
|id|value|value2|
+--+-----+------+
|1 |a |a |
|1 |b |b |
|1 |c |c |
|1 |d |d |
|1 |e |e |
|1 |f |f |
|1 |g |g |
|1 |h |h |
|1 |i |i |
|1 |j |j |
"""
expected_data = create_rows(data, session, 'paging_test', cl=CL.ALL, format_funcs={'id': int, 'value': str, 'value2': str})
future = session.execute_async(
SimpleStatement("select * from paging_test where id = 1 order by value asc", fetch_size=3, consistency_level=CL.ALL)
)
pf = PageFetcher(future).request_all()
assert pf.pagecount() == 4
assert pf.num_results_all(), [3, 3, 3, 1]
# these should be equal (in the same order)
assert pf.all_data() == expected_data
# drop the ORDER BY
future = session.execute_async(
SimpleStatement("select * from paging_test where id = 1", fetch_size=3, consistency_level=CL.ALL)
)
pf = PageFetcher(future).request_all()
assert pf.pagecount() == 4
assert pf.num_results_all(), [3, 3, 3, 1]
# these should be equal (in the same order)
assert pf.all_data() == list(reversed(expected_data))
def test_with_limit(self):
session = self.prepare()
create_ks(session, 'test_paging_size', 2)
session.execute("CREATE TABLE paging_test ( id int, value text, PRIMARY KEY (id, value) )")
def random_txt(text):
return str(uuid.uuid4())
data = """
| id | value |
+----+---------------+
*5| 1 | [random text] |
*5| 2 | [random text] |
*10| 3 | [random text] |
*10| 4 | [random text] |
*20| 5 | [random text] |
*30| 6 | [random text] |
"""
expected_data = create_rows(data, session, 'paging_test', cl=CL.ALL, format_funcs={'id': int, 'value': random_txt})
scenarios = [
# using equals clause w/single partition
{'limit': 10, 'fetch': 20, 'data_size': 30, 'whereclause': 'WHERE id = 6', 'expect_pgcount': 1, 'expect_pgsizes': [10]}, # limit < fetch < data
{'limit': 10, 'fetch': 30, 'data_size': 20, 'whereclause': 'WHERE id = 5', 'expect_pgcount': 1, 'expect_pgsizes': [10]}, # limit < data < fetch
{'limit': 20, 'fetch': 10, 'data_size': 30, 'whereclause': 'WHERE id = 6', 'expect_pgcount': 2, 'expect_pgsizes': [10, 10]}, # fetch < limit < data
{'limit': 30, 'fetch': 10, 'data_size': 20, 'whereclause': 'WHERE id = 5', 'expect_pgcount': 2, 'expect_pgsizes': [10, 10]}, # fetch < data < limit
{'limit': 20, 'fetch': 30, 'data_size': 10, 'whereclause': 'WHERE id = 3', 'expect_pgcount': 1, 'expect_pgsizes': [10]}, # data < limit < fetch
{'limit': 30, 'fetch': 20, 'data_size': 10, 'whereclause': 'WHERE id = 3', 'expect_pgcount': 1, 'expect_pgsizes': [10]}, # data < fetch < limit
# using 'in' clause w/multi partitions
{'limit': 9, 'fetch': 20, 'data_size': 80, 'whereclause': 'WHERE id in (1,2,3,4,5,6)', 'expect_pgcount': 1, 'expect_pgsizes': [9]}, # limit < fetch < data
{'limit': 10, 'fetch': 30, 'data_size': 20, 'whereclause': 'WHERE id in (3,4)', 'expect_pgcount': 1, 'expect_pgsizes': [10]}, # limit < data < fetch
{'limit': 20, 'fetch': 10, 'data_size': 30, 'whereclause': 'WHERE id in (4,5)', 'expect_pgcount': 2, 'expect_pgsizes': [10, 10]}, # fetch < limit < data
{'limit': 30, 'fetch': 10, 'data_size': 20, 'whereclause': 'WHERE id in (3,4)', 'expect_pgcount': 2, 'expect_pgsizes': [10, 10]}, # fetch < data < limit
{'limit': 20, 'fetch': 30, 'data_size': 10, 'whereclause': 'WHERE id in (1,2)', 'expect_pgcount': 1, 'expect_pgsizes': [10]}, # data < limit < fetch
{'limit': 30, 'fetch': 20, 'data_size': 10, 'whereclause': 'WHERE id in (1,2)', 'expect_pgcount': 1, 'expect_pgsizes': [10]}, # data < fetch < limit
# no limit but with a defined pagesize. Scenarios added for CASSANDRA-8408.
{'limit': None, 'fetch': 20, 'data_size': 80, 'whereclause': 'WHERE id in (1,2,3,4,5,6)', 'expect_pgcount': 4, 'expect_pgsizes': [20, 20, 20, 20]}, # fetch < data
{'limit': None, 'fetch': 30, 'data_size': 20, 'whereclause': 'WHERE id in (3,4)', 'expect_pgcount': 1, 'expect_pgsizes': [20]}, # data < fetch
{'limit': None, 'fetch': 10, 'data_size': 30, 'whereclause': 'WHERE id in (4,5)', 'expect_pgcount': 3, 'expect_pgsizes': [10, 10, 10]}, # fetch < data
{'limit': None, 'fetch': 30, 'data_size': 10, 'whereclause': 'WHERE id in (1,2)', 'expect_pgcount': 1, 'expect_pgsizes': [10]}, # data < fetch
# not setting fetch_size (unpaged) but using limit. Scenarios added for CASSANDRA-8408.
{'limit': 9, 'fetch': None, 'data_size': 80, 'whereclause': 'WHERE id in (1,2,3,4,5,6)', 'expect_pgcount': 1, 'expect_pgsizes': [9]}, # limit < data
{'limit': 30, 'fetch': None, 'data_size': 10, 'whereclause': 'WHERE id in (1,2)', 'expect_pgcount': 1, 'expect_pgsizes': [10]}, # data < limit
]
def handle_scenario(scenario):
# using a limit and a fetch
if scenario['limit'] and scenario['fetch']:
future = session.execute_async(
SimpleStatement(
"select * from paging_test {} limit {}".format(scenario['whereclause'], scenario['limit']),
fetch_size=scenario['fetch'], consistency_level=CL.ALL)
)
# using a limit but not specifying a fetch_size
elif scenario['limit'] and scenario['fetch'] is None:
future = session.execute_async(
SimpleStatement(
"select * from paging_test {} limit {}".format(scenario['whereclause'], scenario['limit']),
consistency_level=CL.ALL)
)
# no limit but a fetch_size specified
elif scenario['limit'] is None and scenario['fetch']:
future = session.execute_async(
SimpleStatement(
"select * from paging_test {}".format(scenario['whereclause']),
fetch_size=scenario['fetch'], consistency_level=CL.ALL)
)
else:
# this should not happen
self.fail("Invalid scenario configuration. Scenario is: {}".format(scenario))
pf = PageFetcher(future).request_all()
assert pf.num_results_all() == scenario['expect_pgsizes']
assert pf.pagecount() == scenario['expect_pgcount']
# make sure all the data retrieved is a subset of input data
self.assertIsSubsetOf(pf.all_data(), expected_data)
run_scenarios(scenarios, handle_scenario, deferred_exceptions=(AssertionError,))
def test_with_allow_filtering(self):
session = self.prepare()
create_ks(session, 'test_paging_size', 2)
session.execute("CREATE TABLE paging_test ( id int, value text, PRIMARY KEY (id, value) )")
data = """
|id|value |
+--+----------------+
|1 |testing |
|2 |and more testing|
|3 |and more testing|
|4 |and more testing|
|5 |and more testing|
|6 |testing |
|7 |and more testing|
|8 |and more testing|
|9 |and more testing|
"""
create_rows(data, session, 'paging_test', cl=CL.ALL, format_funcs={'id': int, 'value': str})
future = session.execute_async(
SimpleStatement("select * from paging_test where value = 'and more testing' ALLOW FILTERING", fetch_size=4, consistency_level=CL.ALL)
)
pf = PageFetcher(future).request_all()
assert pf.pagecount() == 2
assert pf.num_results_all() == [4, 3]
# make sure the allow filtering query matches the expected results (ignoring order)
expected_data = parse_data_into_dicts(
"""
|id|value |
+--+----------------+
|2 |and more testing|
|3 |and more testing|
|4 |and more testing|
|5 |and more testing|
|7 |and more testing|
|8 |and more testing|
|9 |and more testing|
""", format_funcs={'id': int, 'value': str}
)
assert_lists_equal_ignoring_order(expected_data, pf.all_data(), sort_key="value")
@since('2.0')
class TestPagingData(BasePagingTester, PageAssertionMixin):
def test_paging_a_single_wide_row(self):
session = self.prepare()
create_ks(session, 'test_paging_size', 2)
session.execute("CREATE TABLE paging_test ( id int, value text, PRIMARY KEY (id, value) )")
def random_txt(text):
return str(uuid.uuid4())
data = """
| id | value |
+----+------------------------+
*10000| 1 | [replaced with random] |
"""
expected_data = create_rows(data, session, 'paging_test', cl=CL.ALL, format_funcs={'id': int, 'value': random_txt})
future = session.execute_async(
SimpleStatement("select * from paging_test where id = 1", fetch_size=3000, consistency_level=CL.ALL)
)
pf = PageFetcher(future).request_all()
assert pf.pagecount() == 4
assert pf.num_results_all(), [3000, 3000, 3000, 1000]
assert_lists_equal_ignoring_order(expected_data, pf.all_data(), sort_key="value")
def test_paging_across_multi_wide_rows(self):
session = self.prepare()
create_ks(session, 'test_paging_size', 2)
session.execute("CREATE TABLE paging_test ( id int, value text, PRIMARY KEY (id, value) )")
def random_txt(text):
return str(uuid.uuid4())
data = """
| id | value |
+----+------------------------+
*5000| 1 | [replaced with random] |
*5000| 2 | [replaced with random] |
"""
expected_data = create_rows(data, session, 'paging_test', cl=CL.ALL, format_funcs={'id': int, 'value': random_txt})
future = session.execute_async(
SimpleStatement("select * from paging_test where id in (1,2)", fetch_size=3000, consistency_level=CL.ALL)
)
pf = PageFetcher(future).request_all()
assert pf.pagecount() == 4
assert pf.num_results_all(), [3000, 3000, 3000, 1000]
assert_lists_equal_ignoring_order(expected_data, pf.all_data(), sort_key="value")
def test_paging_using_secondary_indexes(self):
session = self.prepare()
create_ks(session, 'test_paging_size', 2)
session.execute("CREATE TABLE paging_test ( id int, mybool boolean, sometext text, PRIMARY KEY (id, sometext) )")
session.execute("CREATE INDEX ON paging_test(mybool)")
def random_txt(text):
return str(uuid.uuid4())
def bool_from_str_int(text):
return bool(int(text))
data = """
| id | mybool| sometext |
+----+-------+----------+
*100| 1 | 1 | [random] |
*300| 2 | 0 | [random] |
*500| 3 | 1 | [random] |
*400| 4 | 0 | [random] |
"""
all_data = create_rows(
data, session, 'paging_test', cl=CL.ALL,
format_funcs={'id': int, 'mybool': bool_from_str_int, 'sometext': random_txt}
)
future = session.execute_async(
SimpleStatement("select * from paging_test where mybool = true", fetch_size=400, consistency_level=CL.ALL)
)
pf = PageFetcher(future).request_all()
# the query only searched for True rows, so let's pare down the expectations for comparison
expected_data = [x for x in all_data if x.get('mybool') is True]
assert pf.pagecount() == 2
assert pf.num_results_all() == [400, 200]
assert_lists_equal_ignoring_order(expected_data, pf.all_data(), sort_key="sometext")
def test_paging_with_in_orderby_and_two_partition_keys(self):
session = self.prepare()
create_ks(session, 'test_paging_size', 2)
session.execute("CREATE TABLE paging_test (col_1 int, col_2 int, col_3 int, PRIMARY KEY ((col_1, col_2), col_3))")
assert_invalid(session, "select * from paging_test where col_1=1 and col_2 IN (1, 2) order by col_3 desc;", expected=InvalidRequest)
assert_invalid(session, "select * from paging_test where col_2 IN (1, 2) and col_1=1 order by col_3 desc;", expected=InvalidRequest)
@since('3.10')
def test_group_by_paging(self):
"""
@jira_ticket CASSANDRA-10707
"""
session = self.prepare(row_factory=tuple_factory)
create_ks(session, 'test_paging_with_group_by', 2)
session.execute("CREATE TABLE test (a int, b int, c int, d int, e int, primary key (a, b, c, d))")
session.execute("INSERT INTO test (a, b, c, d, e) VALUES (1, 2, 1, 3, 6)")
session.execute("INSERT INTO test (a, b, c, d, e) VALUES (1, 2, 2, 6, 12)")
session.execute("INSERT INTO test (a, b, c, d) VALUES (1, 3, 2, 12)")
session.execute("INSERT INTO test (a, b, c, d, e) VALUES (1, 4, 2, 12, 24)")
session.execute("INSERT INTO test (a, b, c, d, e) VALUES (1, 4, 2, 6, 12)")
session.execute("INSERT INTO test (a, b, c, d, e) VALUES (2, 2, 3, 3, 6)")
session.execute("INSERT INTO test (a, b, c, d, e) VALUES (2, 4, 3, 6, 12)")
session.execute("INSERT INTO test (a, b, c, d, e) VALUES (4, 8, 2, 12, 24)")
session.execute("INSERT INTO test (a, b, c, d) VALUES (5, 8, 2, 12)")
# Makes sure that we have some tombstones
session.execute("DELETE FROM test WHERE a = 1 AND b = 3 AND c = 2")
session.execute("DELETE FROM test WHERE a = 5")
for page_size in (2, 3, 4, 5, 7, 10):
session.default_fetch_size = page_size
# Range queries
res = rows_to_list(session.execute("SELECT a, b, e, count(b), max(e) FROM test GROUP BY a"))
assert res == [[1, 2, 6, 4, 24], [2, 2, 6, 2, 12], [4, 8, 24, 1, 24]]
res = rows_to_list(session.execute("SELECT a, b, e, count(b), max(e) FROM test GROUP BY a, b"))
assert res == [[1, 2, 6, 2, 12],
[1, 4, 12, 2, 24],
[2, 2, 6, 1, 6],
[2, 4, 12, 1, 12],
[4, 8, 24, 1, 24]]
res = rows_to_list(session.execute("SELECT a, b, e, count(b), max(e) FROM test"))
assert res == [[1, 2, 6, 7, 24]]
res = rows_to_list(
session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE b = 2 GROUP BY a, b ALLOW FILTERING"))
assert res == [[1, 2, 6, 2, 12],
[2, 2, 6, 1, 6]]
assert_invalid(session, "SELECT a, b, e, count(b), max(e) FROM test WHERE b = 2 GROUP BY a, b;", expected=InvalidRequest)
res = rows_to_list(
session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE b = 2 ALLOW FILTERING"))
assert res == [[1, 2, 6, 3, 12]]
assert_invalid(session, "SELECT a, b, e, count(b), max(e) FROM test WHERE b = 2", expected=InvalidRequest)
# Range queries without aggregates
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test GROUP BY a, b, c"))
assert res == [[1, 2, 1, 3],
[1, 2, 2, 6],
[1, 4, 2, 6],
[2, 2, 3, 3],
[2, 4, 3, 6],
[4, 8, 2, 12]]
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test GROUP BY a, b"))
assert res == [[1, 2, 1, 3],
[1, 4, 2, 6],
[2, 2, 3, 3],
[2, 4, 3, 6],
[4, 8, 2, 12]]
# Range query with LIMIT
res = rows_to_list(session.execute("SELECT a, b, e, count(b), max(e) FROM test GROUP BY a, b LIMIT 2"))
assert res == [[1, 2, 6, 2, 12],
[1, 4, 12, 2, 24]]
res = rows_to_list(session.execute("SELECT a, b, e, count(b), max(e) FROM test LIMIT 2"))
assert res == [[1, 2, 6, 7, 24]]
# Range queries without aggregates and with LIMIT
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test GROUP BY a, b, c LIMIT 3"))
assert res == [[1, 2, 1, 3],
[1, 2, 2, 6],
[1, 4, 2, 6]]
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test GROUP BY a, b LIMIT 3"))
assert res == [[1, 2, 1, 3],
[1, 4, 2, 6],
[2, 2, 3, 3]]
# Range query with PER PARTITION LIMIT
res = rows_to_list(session.execute("SELECT a, b, e, count(b), max(e) FROM test GROUP BY a, b PER PARTITION LIMIT 2"))
assert res == [[1, 2, 6, 2, 12],
[1, 4, 12, 2, 24],
[2, 2, 6, 1, 6],
[2, 4, 12, 1, 12],
[4, 8, 24, 1, 24]]
res = rows_to_list(session.execute("SELECT a, b, e, count(b), max(e) FROM test GROUP BY a, b PER PARTITION LIMIT 1"))
assert res == [[1, 2, 6, 2, 12],
[2, 2, 6, 1, 6],
[4, 8, 24, 1, 24]]
# Range queries with PER PARTITION LIMIT and LIMIT
res = rows_to_list(session.execute("SELECT a, b, e, count(b), max(e) FROM test GROUP BY a, b PER PARTITION LIMIT 2 LIMIT 3"))
assert res == [[1, 2, 6, 2, 12],
[1, 4, 12, 2, 24],
[2, 2, 6, 1, 6]]
res = rows_to_list(session.execute("SELECT a, b, e, count(b), max(e) FROM test GROUP BY a, b PER PARTITION LIMIT 2 LIMIT 5"))
assert res == [[1, 2, 6, 2, 12],
[1, 4, 12, 2, 24],
[2, 2, 6, 1, 6],
[2, 4, 12, 1, 12],
[4, 8, 24, 1, 24]]
res = rows_to_list(session.execute("SELECT a, b, e, count(b), max(e) FROM test GROUP BY a, b PER PARTITION LIMIT 2 LIMIT 10"))
assert res == [[1, 2, 6, 2, 12],
[1, 4, 12, 2, 24],
[2, 2, 6, 1, 6],
[2, 4, 12, 1, 12],
[4, 8, 24, 1, 24]]
# Range queries without aggregates and with PER PARTITION LIMIT
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test GROUP BY a, b, c PER PARTITION LIMIT 2"))
assert res == [[1, 2, 1, 3],
[1, 2, 2, 6],
[2, 2, 3, 3],
[2, 4, 3, 6],
[4, 8, 2, 12]]
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test GROUP BY a, b PER PARTITION LIMIT 1"))
assert res == [[1, 2, 1, 3],
[2, 2, 3, 3],
[4, 8, 2, 12]]
# Range query with DISTINCT
res = rows_to_list(session.execute("SELECT DISTINCT a, count(a)FROM test GROUP BY a"))
assert res == [[1, 1],
[2, 1],
[4, 1]]
res = rows_to_list(session.execute("SELECT DISTINCT a, count(a)FROM test"))
assert res == [[1, 3]]
# Range query with DISTINCT and LIMIT
res = rows_to_list(session.execute("SELECT DISTINCT a, count(a)FROM test GROUP BY a LIMIT 2"))
assert res == [[1, 1],
[2, 1]]
res = rows_to_list(session.execute("SELECT DISTINCT a, count(a)FROM test LIMIT 2"))
assert res == [[1, 3]]
# Single partition queries
res = rows_to_list(
session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE a = 1 GROUP BY a, b, c"))
assert res == [[1, 2, 6, 1, 6],
[1, 2, 12, 1, 12],
[1, 4, 12, 2, 24]]
res = rows_to_list(session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE a = 1"))
assert res == [[1, 2, 6, 4, 24]]
res = rows_to_list(
session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE a = 1 AND b = 2 GROUP BY a, b, c"))
assert res == [[1, 2, 6, 1, 6],
[1, 2, 12, 1, 12]]
res = rows_to_list(session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE a = 1 AND b = 2"))
assert res == [[1, 2, 6, 2, 12]]
# Single partition queries without aggregates
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test WHERE a = 1 GROUP BY a, b"))
assert res == [[1, 2, 1, 3],
[1, 4, 2, 6]]
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test WHERE a = 1 GROUP BY a, b, c"))
assert res == [[1, 2, 1, 3],
[1, 2, 2, 6],
[1, 4, 2, 6]]
# Single partition query with DISTINCT
res = rows_to_list(session.execute("SELECT DISTINCT a, count(a)FROM test WHERE a = 1 GROUP BY a"))
assert res == [[1, 1]]
res = rows_to_list(session.execute("SELECT DISTINCT a, count(a)FROM test WHERE a = 1 GROUP BY a"))
assert res == [[1, 1]]
# Single partition queries with LIMIT
res = rows_to_list(
session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE a = 1 GROUP BY a, b, c LIMIT 10"))
assert res == [[1, 2, 6, 1, 6],
[1, 2, 12, 1, 12],
[1, 4, 12, 2, 24]]
res = rows_to_list(
session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE a = 1 GROUP BY a, b, c LIMIT 2"))
assert res == [[1, 2, 6, 1, 6],
[1, 2, 12, 1, 12]]
res = rows_to_list(session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE a = 1 LIMIT 2"))
assert res == [[1, 2, 6, 4, 24]]
res = rows_to_list(
session.execute("SELECT count(b), max(e) FROM test WHERE a = 1 GROUP BY a, b, c LIMIT 1"))
assert res == [[1, 6]]
# Single partition queries with PER PARTITION LIMIT
res = rows_to_list(
session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE a = 1 GROUP BY a, b, c PER PARTITION LIMIT 2"))
assert res == [[1, 2, 6, 1, 6],
[1, 2, 12, 1, 12]]
res = rows_to_list(
session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE a = 1 GROUP BY a, b, c PER PARTITION LIMIT 3"))
assert res == [[1, 2, 6, 1, 6],
[1, 2, 12, 1, 12],
[1, 4, 12, 2, 24]]
res = rows_to_list(
session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE a = 1 GROUP BY a, b, c PER PARTITION LIMIT 3"))
assert res == [[1, 2, 6, 1, 6],
[1, 2, 12, 1, 12],
[1, 4, 12, 2, 24]]
# Single partition queries without aggregates and with LIMIT
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test WHERE a = 1 GROUP BY a, b LIMIT 2"))
assert res == [[1, 2, 1, 3],
[1, 4, 2, 6]]
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test WHERE a = 1 GROUP BY a, b LIMIT 1"))
assert res == [[1, 2, 1, 3]]
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test WHERE a = 1 GROUP BY a, b, c LIMIT 2"))
assert res == [[1, 2, 1, 3],
[1, 2, 2, 6]]
# Single partition queries with ORDER BY
res = rows_to_list(session.execute(
"SELECT a, b, e, count(b), max(e) FROM test WHERE a = 1 GROUP BY a, b, c ORDER BY b DESC, c DESC"))
assert res == [[1, 4, 24, 2, 24],
[1, 2, 12, 1, 12],
[1, 2, 6, 1, 6]]
res = rows_to_list(
session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE a = 1 ORDER BY b DESC, c DESC"))
assert res == [[1, 4, 24, 4, 24]]
# Single partition queries with ORDER BY and LIMIT
res = rows_to_list(session.execute(
"SELECT a, b, e, count(b), max(e) FROM test WHERE a = 1 GROUP BY a, b, c ORDER BY b DESC, c DESC LIMIT 2"))
assert res == [[1, 4, 24, 2, 24],
[1, 2, 12, 1, 12]]
res = rows_to_list(session.execute(
"SELECT a, b, e, count(b), max(e) FROM test WHERE a = 1 ORDER BY b DESC, c DESC LIMIT 2"))
assert res == [[1, 4, 24, 4, 24]]
# Multi-partitions queries
res = rows_to_list(
session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE a IN (1, 2, 4) GROUP BY a, b, c"))
assert res == [[1, 2, 6, 1, 6],
[1, 2, 12, 1, 12],
[1, 4, 12, 2, 24],
[2, 2, 6, 1, 6],
[2, 4, 12, 1, 12],
[4, 8, 24, 1, 24]]
res = rows_to_list(session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE a IN (1, 2, 4)"))
assert res == [[1, 2, 6, 7, 24]]
res = rows_to_list(session.execute(
"SELECT a, b, e, count(b), max(e) FROM test WHERE a IN (1, 2, 4) AND b = 2 GROUP BY a, b, c"))
assert res == [[1, 2, 6, 1, 6],
[1, 2, 12, 1, 12],
[2, 2, 6, 1, 6]]
res = rows_to_list(
session.execute("SELECT a, b, e, count(b), max(e) FROM test WHERE a IN (1, 2, 4) AND b = 2"))
assert res == [[1, 2, 6, 3, 12]]
# Multi-partitions queries without aggregates
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test WHERE a IN (1, 2, 4) GROUP BY a, b"))
assert res == [[1, 2, 1, 3],
[1, 4, 2, 6],
[2, 2, 3, 3],
[2, 4, 3, 6],
[4, 8, 2, 12]]
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test WHERE a IN (1, 2, 4) GROUP BY a, b, c"))
assert res == [[1, 2, 1, 3],
[1, 2, 2, 6],
[1, 4, 2, 6],
[2, 2, 3, 3],
[2, 4, 3, 6],
[4, 8, 2, 12]]
# Multi-partitions queries with DISTINCT
res = rows_to_list(session.execute("SELECT DISTINCT a, count(a)FROM test WHERE a IN (1, 2, 4) GROUP BY a"))
assert res == [[1, 1],
[2, 1],
[4, 1]]
res = rows_to_list(session.execute("SELECT DISTINCT a, count(a)FROM test WHERE a IN (1, 2, 4)"))
assert res == [[1, 3]]
# Multi-partitions query with DISTINCT and LIMIT
res = rows_to_list(
session.execute("SELECT DISTINCT a, count(a)FROM test WHERE a IN (1, 2, 4) GROUP BY a LIMIT 2"))
assert res == [[1, 1],
[2, 1]]
res = rows_to_list(session.execute("SELECT DISTINCT a, count(a)FROM test WHERE a IN (1, 2, 4) LIMIT 2"))
assert res == [[1, 3]]
# Multi-partitions queries without aggregates and with PER PARTITION LIMIT
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test WHERE a IN (1, 2, 4) GROUP BY a, b PER PARTITION LIMIT 1"))
assert res == [[1, 2, 1, 3],
[2, 2, 3, 3],
[4, 8, 2, 12]]
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test WHERE a IN (1, 2, 4) GROUP BY a, b PER PARTITION LIMIT 2"))
assert res == [[1, 2, 1, 3],
[1, 4, 2, 6],
[2, 2, 3, 3],
[2, 4, 3, 6],
[4, 8, 2, 12]]
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test WHERE a IN (1, 2, 4) GROUP BY a, b PER PARTITION LIMIT 3"))
assert res == [[1, 2, 1, 3],
[1, 4, 2, 6],
[2, 2, 3, 3],
[2, 4, 3, 6],
[4, 8, 2, 12]]
# Multi-partitions queries without aggregates, with PER PARTITION LIMIT and with LIMIT
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test WHERE a IN (1, 2, 4) GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 2"))
assert res == [[1, 2, 1, 3],
[2, 2, 3, 3]]
res = rows_to_list(session.execute("SELECT a, b, c, d FROM test WHERE a IN (1, 2, 4) GROUP BY a, b PER PARTITION LIMIT 3 LIMIT 2"))
assert res == [[1, 2, 1, 3],
[1, 4, 2, 6]]
@since('3.10')
def test_group_by_with_range_name_query_paging(self):
"""
@jira_ticket CASSANDRA-10707
"""
session = self.prepare(row_factory=tuple_factory)
create_ks(session, 'group_by_with_range_name_query_paging_test', 2)
session.execute("CREATE TABLE test (a int, b int, c int, d int, primary key (a, b, c))")
for i in range(1, 5):
for j in range(1, 5):
for k in range(1, 5):
session.execute("INSERT INTO test (a, b, c, d) VALUES ({}, {}, {}, {})".format(i, j, k, i + j))
# Makes sure that we have some tombstones
session.execute("DELETE FROM test WHERE a = 3")
for page_size in (2, 3, 4, 5, 7, 10):
session.default_fetch_size = page_size
# Range queries
res = rows_to_list(session.execute("SELECT a, b, d, count(b), max(d) FROM test WHERE b = 1 and c IN (1, 2) GROUP BY a ALLOW FILTERING"))
assert res == [[1, 1, 2, 2, 2],
[2, 1, 3, 2, 3],
[4, 1, 5, 2, 5]]
res = rows_to_list(session.execute("SELECT a, b, d, count(b), max(d) FROM test WHERE b = 1 and c IN (1, 2) GROUP BY a, b ALLOW FILTERING"))
assert res == [[1, 1, 2, 2, 2],
[2, 1, 3, 2, 3],
[4, 1, 5, 2, 5]]
res = rows_to_list(session.execute("SELECT a, b, d, count(b), max(d) FROM test WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b ALLOW FILTERING"))
assert res == [[1, 1, 2, 2, 2],
[1, 2, 3, 2, 3],
[2, 1, 3, 2, 3],
[2, 2, 4, 2, 4],
[4, 1, 5, 2, 5],
[4, 2, 6, 2, 6]]
# Range queries with LIMIT
res = rows_to_list(session.execute("SELECT a, b, d, count(b), max(d) FROM test WHERE b = 1 and c IN (1, 2) GROUP BY a LIMIT 5 ALLOW FILTERING"))
assert res == [[1, 1, 2, 2, 2],
[2, 1, 3, 2, 3],
[4, 1, 5, 2, 5]]
res = rows_to_list(session.execute("SELECT a, b, d, count(b), max(d) FROM test WHERE b = 1 and c IN (1, 2) GROUP BY a, b LIMIT 3 ALLOW FILTERING"))
assert res == [[1, 1, 2, 2, 2],
[2, 1, 3, 2, 3],
[4, 1, 5, 2, 5]]
res = rows_to_list(session.execute("SELECT a, b, d, count(b), max(d) FROM test WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b LIMIT 3 ALLOW FILTERING"))
assert res == [[1, 1, 2, 2, 2],
[1, 2, 3, 2, 3],
[2, 1, 3, 2, 3]]
# Range queries with PER PARTITION LIMIT
res = rows_to_list(session.execute("SELECT a, b, d, count(b), max(d) FROM test WHERE b = 1 and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 2 ALLOW FILTERING"))
assert res == [[1, 1, 2, 2, 2],
[2, 1, 3, 2, 3],
[4, 1, 5, 2, 5]]
res = rows_to_list(session.execute("SELECT a, b, d, count(b), max(d) FROM test WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 1 ALLOW FILTERING"))
assert res == [[1, 1, 2, 2, 2],
[2, 1, 3, 2, 3],
[4, 1, 5, 2, 5]]
# Range queries with PER PARTITION LIMIT and LIMIT
res = rows_to_list(session.execute("SELECT a, b, d, count(b), max(d) FROM test WHERE b = 1 and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 2 LIMIT 5 ALLOW FILTERING"))
assert res == [[1, 1, 2, 2, 2],
[2, 1, 3, 2, 3],
[4, 1, 5, 2, 5]]
res = rows_to_list(session.execute("SELECT a, b, d, count(b), max(d) FROM test WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 2 ALLOW FILTERING"))
assert res == [[1, 1, 2, 2, 2],
[2, 1, 3, 2, 3]]
@since('3.10')
def test_group_by_with_static_columns_paging(self):
"""
@jira_ticket CASSANDRA-10707
"""
session = self.prepare(row_factory=tuple_factory)
create_ks(session, 'test_paging_with_group_by_and_static_columns', 2)
session.execute("CREATE TABLE test (a int, b int, c int, s int static, d int, primary key (a, b, c))")
# ------------------------------------
# Test with non static columns empty
# ------------------------------------
session.execute("UPDATE test SET s = 1 WHERE a = 1")
session.execute("UPDATE test SET s = 2 WHERE a = 2")
session.execute("UPDATE test SET s = 3 WHERE a = 4")
for page_size in (2, 3, 4, 5, 7, 10):
session.default_fetch_size = page_size
# Range queries
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test GROUP BY a"))
assert res == [[1, None, 1, 0, 1],
[2, None, 2, 0, 1],
[4, None, 3, 0, 1]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test GROUP BY a, b"))
assert res == [[1, None, 1, 0, 1],
[2, None, 2, 0, 1],
[4, None, 3, 0, 1]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test"))
assert res == [[1, None, 1, 0, 3]]
# Range query without aggregates
res = rows_to_list(session.execute("SELECT a, b, s FROM test GROUP BY a, b"))
assert res == [[1, None, 1],
[2, None, 2],
[4, None, 3]]
# Range queries with LIMIT
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test GROUP BY a, b LIMIT 2"))
assert res == [[1, None, 1, 0, 1],
[2, None, 2, 0, 1]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test LIMIT 2"))
assert res == [[1, None, 1, 0, 3]]
# Range query with PER PARTITION LIMIT
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test GROUP BY a, b PER PARTITION LIMIT 2"))
assert res == [[1, None, 1, 0, 1],
[2, None, 2, 0, 1],
[4, None, 3, 0, 1]]
# Range queries with DISTINCT
res = rows_to_list(session.execute("SELECT DISTINCT a, s, count(s) FROM test GROUP BY a"))
assert res == [[1, 1, 1],
[2, 2, 1],
[4, 3, 1]]
res = rows_to_list(session.execute("SELECT DISTINCT a, s, count(s) FROM test "))
assert res == [[1, 1, 3]]
# Range queries with DISTINCT and LIMIT
res = rows_to_list(session.execute("SELECT DISTINCT a, s, count(s) FROM test GROUP BY a LIMIT 2"))
assert res == [[1, 1, 1],
[2, 2, 1]]
res = rows_to_list(session.execute("SELECT DISTINCT a, s, count(s) FROM test LIMIT 2"))
assert res == [[1, 1, 3]]
# Single partition queries
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a = 1 GROUP BY a"))
assert res == [[1, None, 1, 0, 1]]
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a = 1 GROUP BY a, b"))
assert res == [[1, None, 1, 0, 1]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a = 1"))
assert res == [[1, None, 1, 0, 1]]
# Single partition query without aggregates
res = rows_to_list(session.execute("SELECT a, b, s FROM test WHERE a = 1 GROUP BY a, b"))
assert res == [[1, None, 1]]
# Single partition queries with LIMIT
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a = 1 GROUP BY a, b LIMIT 2"))
assert res == [[1, None, 1, 0, 1]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a = 1 LIMIT 2"))
assert res == [[1, None, 1, 0, 1]]
# Single partition queries with PER PARTITION LIMIT
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a = 1 GROUP BY a, b PER PARTITION LIMIT 2"))
assert res == [[1, None, 1, 0, 1]]
# Single partition queries with DISTINCT
res = rows_to_list(session.execute("SELECT DISTINCT a, s, count(s) FROM test WHERE a = 1 GROUP BY a"))
assert res == [[1, 1, 1]]
res = rows_to_list(session.execute("SELECT DISTINCT a, s, count(s) FROM test WHERE a = 1"))
assert res == [[1, 1, 1]]
# Multi-partitions queries
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a"))
assert res == [[1, None, 1, 0, 1],
[2, None, 2, 0, 1],
[4, None, 3, 0, 1]]
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a, b"))
assert res == [[1, None, 1, 0, 1],
[2, None, 2, 0, 1],
[4, None, 3, 0, 1]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4)"))
assert res == [[1, None, 1, 0, 3]]
# Multi-partitions query without aggregates
res = rows_to_list(session.execute("SELECT a, b, s FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a, b"))
assert res == [[1, None, 1],
[2, None, 2],
[4, None, 3]]
# Multi-partitions query with LIMIT
res = rows_to_list(session.execute(
"SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a, b LIMIT 2"))
assert res == [[1, None, 1, 0, 1],
[2, None, 2, 0, 1]]
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) LIMIT 2"))
assert res == [[1, None, 1, 0, 3]]
# Multi-partitions query with PER PARTITION LIMIT
res = rows_to_list(session.execute(
"SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a, b PER PARTITION LIMIT 1"))
assert res == [[1, None, 1, 0, 1],
[2, None, 2, 0, 1],
[4, None, 3, 0, 1]]
# Multi-partitions queries with DISTINCT
res = rows_to_list(
session.execute("SELECT DISTINCT a, s, count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a"))
assert res == [[1, 1, 1],
[2, 2, 1],
[4, 3, 1]]
res = rows_to_list(session.execute("SELECT DISTINCT a, s, count(s) FROM test WHERE a IN (1, 2, 3, 4)"))
assert res == [[1, 1, 3]]
# Multi-partitions queries with DISTINCT and LIMIT
res = rows_to_list(
session.execute("SELECT DISTINCT a, s, count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a LIMIT 2"))
assert res == [[1, 1, 1],
[2, 2, 1]]
res = rows_to_list(
session.execute("SELECT DISTINCT a, s, count(s) FROM test WHERE a IN (1, 2, 3, 4) LIMIT 2"))
assert res == [[1, 1, 3]]
# ------------------------------------
# Test with non static columns not empty
# ------------------------------------
session.execute("UPDATE test SET s = 3 WHERE a = 3")
session.execute("DELETE s FROM test WHERE a = 4")
session.execute("INSERT INTO test (a, b, c, d) VALUES (1, 2, 1, 3)")
session.execute("INSERT INTO test (a, b, c, d) VALUES (1, 2, 2, 6)")
session.execute("INSERT INTO test (a, b, c, d) VALUES (1, 3, 2, 12)")
session.execute("INSERT INTO test (a, b, c, d) VALUES (1, 4, 2, 12)")
session.execute("INSERT INTO test (a, b, c, d) VALUES (1, 4, 3, 6)")
session.execute("INSERT INTO test (a, b, c, d) VALUES (2, 2, 3, 3)")
session.execute("INSERT INTO test (a, b, c, d) VALUES (2, 4, 3, 6)")
session.execute("INSERT INTO test (a, b, c, d) VALUES (4, 8, 2, 12)")
session.execute("INSERT INTO test (a, b, c, d) VALUES (5, 8, 2, 12)")
# Makes sure that we have some tombstones
session.execute("DELETE FROM test WHERE a = 1 AND b = 3 AND c = 2")
session.execute("DELETE FROM test WHERE a = 5")
for page_size in (2, 3, 4, 5, 7, 10):
session.default_fetch_size = page_size
# Range queries
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test GROUP BY a"))
assert res == [[1, 2, 1, 4, 4],
[2, 2, 2, 2, 2],
[4, 8, None, 1, 0],
[3, None, 3, 0, 1]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test GROUP BY a, b"))
assert res == [[1, 2, 1, 2, 2],
[1, 4, 1, 2, 2],
[2, 2, 2, 1, 1],
[2, 4, 2, 1, 1],
[4, 8, None, 1, 0],
[3, None, 3, 0, 1]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test"))
assert res == [[1, 2, 1, 7, 7]]
res = rows_to_list(
session.execute(
"SELECT a, b, s, count(b), count(s) FROM test WHERE b = 2 GROUP BY a, b ALLOW FILTERING"))
assert res == [[1, 2, 1, 2, 2],
[2, 2, 2, 1, 1]]
assert_invalid(session, "SELECT a, b, s, count(b), count(s) FROM test WHERE b = 2 GROUP BY a, b", expected=InvalidRequest)
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE b = 2 ALLOW FILTERING"))
assert res == [[1, 2, 1, 3, 3]]
assert_invalid(session, "SELECT a, b, s, count(b), count(s) FROM test WHERE b = 2", expected=InvalidRequest)
# Range queries without aggregates
res = rows_to_list(session.execute("SELECT a, b, s FROM test GROUP BY a"))
assert res == [[1, 2, 1],
[2, 2, 2],
[4, 8, None],
[3, None, 3]]
res = rows_to_list(session.execute("SELECT a, b, s FROM test GROUP BY a, b"))
assert res == [[1, 2, 1],
[1, 4, 1],
[2, 2, 2],
[2, 4, 2],
[4, 8, None],
[3, None, 3]]
# Range query with LIMIT
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test GROUP BY a LIMIT 2"))
assert res == [[1, 2, 1, 4, 4],
[2, 2, 2, 2, 2]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test LIMIT 2"))
assert res == [[1, 2, 1, 7, 7]]
# Range queries without aggregates and with LIMIT
res = rows_to_list(session.execute("SELECT a, b, s FROM test GROUP BY a LIMIT 2"))
assert res == [[1, 2, 1],
[2, 2, 2]]
res = rows_to_list(session.execute("SELECT a, b, s FROM test GROUP BY a, b LIMIT 10"))
assert res == [[1, 2, 1],
[1, 4, 1],
[2, 2, 2],
[2, 4, 2],
[4, 8, None],
[3, None, 3]]
# Range queries with PER PARTITION LIMITS
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test GROUP BY a, b PER PARTITION LIMIT 2"))
assert res == [[1, 2, 1, 2, 2],
[1, 4, 1, 2, 2],
[2, 2, 2, 1, 1],
[2, 4, 2, 1, 1],
[4, 8, None, 1, 0],
[3, None, 3, 0, 1]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test GROUP BY a, b PER PARTITION LIMIT 1"))
assert res == [[1, 2, 1, 2, 2],
[2, 2, 2, 1, 1],
[4, 8, None, 1, 0],
[3, None, 3, 0, 1]]
# Range queries with PER PARTITION LIMITS and LIMIT
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 5"))
assert res == [[1, 2, 1, 2, 2],
[2, 2, 2, 1, 1],
[4, 8, None, 1, 0],
[3, None, 3, 0, 1]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 4"))
assert res == [[1, 2, 1, 2, 2],
[2, 2, 2, 1, 1],
[4, 8, None, 1, 0],
[3, None, 3, 0, 1]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 2"))
assert res == [[1, 2, 1, 2, 2],
[2, 2, 2, 1, 1]]
# Range queries with DISTINCT
res = rows_to_list(session.execute("SELECT DISTINCT a, s, count(a), count(s) FROM test GROUP BY a"))
assert res == [[1, 1, 1, 1],
[2, 2, 1, 1],
[4, None, 1, 0],
[3, 3, 1, 1]]
res = rows_to_list(session.execute("SELECT DISTINCT a, s, count(a), count(s) FROM test"))
assert res == [[1, 1, 4, 3]]
# Range queries with DISTINCT and LIMIT
res = rows_to_list(session.execute("SELECT DISTINCT a, s, count(a), count(s) FROM test GROUP BY a LIMIT 2"))
assert res == [[1, 1, 1, 1],
[2, 2, 1, 1]]
res = rows_to_list(session.execute("SELECT DISTINCT a, s, count(a), count(s) FROM test LIMIT 2"))
assert res == [[1, 1, 4, 3]]
# Single partition queries
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a = 1 GROUP BY a"))
assert res == [[1, 2, 1, 4, 4]]
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a = 3 GROUP BY a, b"))
assert res == [[3, None, 3, 0, 1]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a = 3"))
assert res == [[3, None, 3, 0, 1]]
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a = 2 AND b = 2 GROUP BY a, b"))
assert res == [[2, 2, 2, 1, 1]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a = 2 AND b = 2"))
assert res == [[2, 2, 2, 1, 1]]
# Single partition queries without aggregates
res = rows_to_list(session.execute("SELECT a, b, s FROM test WHERE a = 1 GROUP BY a"))
assert res == [[1, 2, 1]]
res = rows_to_list(session.execute("SELECT a, b, s FROM test WHERE a = 4 GROUP BY a, b"))
assert res == [[4, 8, None]]
# Single partition queries with LIMIT
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a = 2 GROUP BY a, b LIMIT 1"))
assert res == [[2, 2, 2, 1, 1]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a = 2 LIMIT 1"))
assert res == [[2, 2, 2, 2, 2]]
# Single partition queries without aggregates and with LIMIT
res = rows_to_list(session.execute("SELECT a, b, s FROM test WHERE a = 2 GROUP BY a, b LIMIT 1"))
assert res == [[2, 2, 2]]
res = rows_to_list(session.execute("SELECT a, b, s FROM test WHERE a = 2 GROUP BY a, b LIMIT 2"))
assert res == [[2, 2, 2],
[2, 4, 2]]
# Single partition queries with PER PARTITION LIMIT
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a = 2 GROUP BY a, b PER PARTITION LIMIT 1"))
assert res == [[2, 2, 2, 1, 1]]
# Single partition queries with DISTINCT
res = rows_to_list(
session.execute("SELECT DISTINCT a, s, count(a), count(s) FROM test WHERE a = 2 GROUP BY a"))
assert res == [[2, 2, 1, 1]]
# Single partition queries with ORDER BY
res = rows_to_list(session.execute(
"SELECT a, b, s, count(b), count(s) FROM test WHERE a = 2 GROUP BY a, b ORDER BY b DESC, c DESC"))
assert res == [[2, 4, 2, 1, 1],
[2, 2, 2, 1, 1]]
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a = 2 ORDER BY b DESC, c DESC"))
assert res == [[2, 4, 2, 2, 2]]
# Single partition queries with ORDER BY and LIMIT
res = rows_to_list(session.execute(
"SELECT a, b, s, count(b), count(s) FROM test WHERE a = 2 GROUP BY a, b ORDER BY b DESC, c DESC LIMIT 1"))
assert res == [[2, 4, 2, 1, 1]]
res = rows_to_list(session.execute(
"SELECT a, b, s, count(b), count(s) FROM test WHERE a = 2 ORDER BY b DESC, c DESC LIMIT 2"))
assert res == [[2, 4, 2, 2, 2]]
# Single partition queries with ORDER BY and PER PARTITION LIMIT
res = rows_to_list(session.execute(
"SELECT a, b, s, count(b), count(s) FROM test WHERE a = 2 GROUP BY a, b ORDER BY b DESC, c DESC PER PARTITION LIMIT 1"))
assert res == [[2, 4, 2, 1, 1]]
# Multi-partitions queries
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a"))
assert res == [[1, 2, 1, 4, 4],
[2, 2, 2, 2, 2],
[3, None, 3, 0, 1],
[4, 8, None, 1, 0]]
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a, b"))
assert res == [[1, 2, 1, 2, 2],
[1, 4, 1, 2, 2],
[2, 2, 2, 1, 1],
[2, 4, 2, 1, 1],
[3, None, 3, 0, 1],
[4, 8, None, 1, 0]]
res = rows_to_list(session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4)"))
assert res == [[1, 2, 1, 7, 7]]
res = rows_to_list(session.execute(
"SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) AND b = 2 GROUP BY a, b"))
assert res == [[1, 2, 1, 2, 2],
[2, 2, 2, 1, 1]]
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) AND b = 2"))
assert res == [[1, 2, 1, 3, 3]]
# Multi-partitions queries without aggregates
res = rows_to_list(session.execute("SELECT a, b, s FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a"))
assert res == [[1, 2, 1],
[2, 2, 2],
[3, None, 3],
[4, 8, None]]
res = rows_to_list(session.execute("SELECT a, b, s FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a, b"))
assert res == [[1, 2, 1],
[1, 4, 1],
[2, 2, 2],
[2, 4, 2],
[3, None, 3],
[4, 8, None]]
# Multi-partitions queries with LIMIT
res = rows_to_list(session.execute(
"SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a LIMIT 2"))
assert res == [[1, 2, 1, 4, 4],
[2, 2, 2, 2, 2]]
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) LIMIT 2"))
assert res == [[1, 2, 1, 7, 7]]
# Multi-partitions queries without aggregates and with LIMIT
res = rows_to_list(session.execute("SELECT a, b, s FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a LIMIT 2"))
assert res == [[1, 2, 1],
[2, 2, 2]]
res = rows_to_list(
session.execute("SELECT a, b, s FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a, b LIMIT 10"))
assert res == [[1, 2, 1],
[1, 4, 1],
[2, 2, 2],
[2, 4, 2],
[3, None, 3],
[4, 8, None]]
# Multi-partitions queries with PER PARTITION LIMIT
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a PER PARTITION LIMIT 1"))
assert res == [[1, 2, 1, 4, 4],
[2, 2, 2, 2, 2],
[3, None, 3, 0, 1],
[4, 8, None, 1, 0]]
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a, b PER PARTITION LIMIT 2"))
assert res == [[1, 2, 1, 2, 2],
[1, 4, 1, 2, 2],
[2, 2, 2, 1, 1],
[2, 4, 2, 1, 1],
[3, None, 3, 0, 1],
[4, 8, None, 1, 0]]
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a, b PER PARTITION LIMIT 1"))
assert res == [[1, 2, 1, 2, 2],
[2, 2, 2, 1, 1],
[3, None, 3, 0, 1],
[4, 8, None, 1, 0]]
# Multi-partitions queries with DISTINCT
res = rows_to_list(session.execute(
"SELECT DISTINCT a, s, count(a), count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a"))
assert res == [[1, 1, 1, 1],
[2, 2, 1, 1],
[3, 3, 1, 1],
[4, None, 1, 0]]
# Multi-partitions queries with PER PARTITION LIMIT and LIMIT
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a PER PARTITION LIMIT 1 LIMIT 3"))
assert res == [[1, 2, 1, 4, 4],
[2, 2, 2, 2, 2],
[3, None, 3, 0, 1]]
res = rows_to_list(
session.execute("SELECT a, b, s, count(b), count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a, b PER PARTITION LIMIT 2 LIMIT 3"))
assert res == [[1, 2, 1, 2, 2],
[1, 4, 1, 2, 2],
[2, 2, 2, 1, 1]]
res = rows_to_list(
session.execute("SELECT DISTINCT a, s, count(a), count(s) FROM test WHERE a IN (1, 2, 3, 4)"))
assert res == [[1, 1, 4, 3]]
# Multi-partitions query with DISTINCT and LIMIT
res = rows_to_list(session.execute(
"SELECT DISTINCT a, s, count(a), count(s) FROM test WHERE a IN (1, 2, 3, 4) GROUP BY a LIMIT 2"))
assert res == [[1, 1, 1, 1],
[2, 2, 1, 1]]
res = rows_to_list(
session.execute("SELECT DISTINCT a, s, count(a), count(s) FROM test WHERE a IN (1, 2, 3, 4) LIMIT 2"))
assert res == [[1, 1, 4, 3]]
@since('2.0.6')
def test_static_columns_paging(self):
"""
Exercises paging with static columns to detect bugs
@jira_ticket CASSANDRA-8502.
"""
session = self.prepare(row_factory=named_tuple_factory)
create_ks(session, 'test_paging_static_cols', 2)
session.execute("CREATE TABLE test (a int, b int, c int, s1 int static, s2 int static, PRIMARY KEY (a, b))")
for i in range(4):
for j in range(4):
session.execute("INSERT INTO test (a, b, c, s1, s2) VALUES (%d, %d, %d, %d, %d)" % (i, j, j, 17, 42))
selectors = (
"*",
"a, b, c, s1, s2",
"a, b, c, s1",
"a, b, c, s2",
"a, b, c")
PAGE_SIZES = (2, 3, 4, 5, 15, 16, 17, 100)
for page_size in PAGE_SIZES:
logger.debug("Current page size is {}".format(page_size))
session.default_fetch_size = page_size
for selector in selectors:
results = list(session.execute("SELECT %s FROM test" % selector))
assert_length_equal(results, 16)
assert [0] * 4 + [1] * 4 + [2] * 4 + [3] * 4 == sorted([r.a for r in results])
assert [0, 1, 2, 3] * 4 == [r.b for r in results]
assert [0, 1, 2, 3] * 4 == [r.c for r in results]
if "s1" in selector:
assert [17] * 16 == [r.s1 for r in results]
if "s2" in selector:
assert [42] * 16 == [r.s2 for r in results]
# IN over the partitions
for page_size in PAGE_SIZES:
logger.debug("Current page size is {}".format(page_size))
session.default_fetch_size = page_size
for selector in selectors:
results = list(session.execute("SELECT %s FROM test WHERE a IN (0, 1, 2, 3)" % selector))
assert_length_equal(results, 16)
assert [0] * 4 + [1] * 4 + [2] * 4 + [3] * 4 == sorted([r.a for r in results])
assert [0, 1, 2, 3] * 4 == [r.b for r in results]
assert [0, 1, 2, 3] * 4 == [r.c for r in results]
if "s1" in selector:
assert [17] * 16 == [r.s1 for r in results]
if "s2" in selector:
assert [42] * 16 == [r.s2 for r in results]
# single partition
for i in range(16):
session.execute("INSERT INTO test (a, b, c, s1, s2) VALUES (%d, %d, %d, %d, %d)" % (99, i, i, 17, 42))
for page_size in PAGE_SIZES:
logger.debug("Current page size is {}".format(page_size))
session.default_fetch_size = page_size
for selector in selectors:
results = list(session.execute("SELECT %s FROM test WHERE a = 99" % selector))
assert_length_equal(results, 16)
assert [99] * 16 == [r.a for r in results]
assert list(range(16)) == [r.b for r in results]
assert list(range(16)) == [r.c for r in results]
if "s1" in selector:
assert [17] * 16 == [r.s1 for r in results]
if "s2" in selector:
assert [42] * 16 == [r.s2 for r in results]
# reversed
for page_size in PAGE_SIZES:
logger.debug("Current page size is {}".format(page_size))
session.default_fetch_size = page_size
for selector in selectors:
results = list(session.execute("SELECT %s FROM test WHERE a = 99 ORDER BY b DESC" % selector))
assert_length_equal(results, 16)
assert [99] * 16 == [r.a for r in results]
assert list(reversed(list(range(16)))) == [r.b for r in results]
assert list(reversed(list(range(16)))) == [r.c for r in results]
if "s1" in selector:
assert [17] * 16 == [r.s1 for r in results]
if "s2" in selector:
assert [42] * 16 == [r.s2 for r in results]
# IN on clustering column
for page_size in PAGE_SIZES:
logger.debug("Current page size is {}".format(page_size))
session.default_fetch_size = page_size
for selector in selectors:
results = list(session.execute("SELECT %s FROM test WHERE a = 99 AND b IN (3, 4, 8, 14, 15)" % selector))
assert_length_equal(results, 5)
assert [99] * 5 == [r.a for r in results]
assert [3, 4, 8, 14, 15] == [r.b for r in results]
assert [3, 4, 8, 14, 15] == [r.c for r in results]
if "s1" in selector:
assert [17] * 5 == [r.s1 for r in results]
if "s2" in selector:
assert [42] * 5 == [r.s2 for r in results]
# reversed IN on clustering column
for page_size in PAGE_SIZES:
logger.debug("Current page size is {}".format(page_size))
session.default_fetch_size = page_size
for selector in selectors:
results = list(session.execute("SELECT %s FROM test WHERE a = 99 AND b IN (3, 4, 8, 14, 15) ORDER BY b DESC" % selector))
assert_length_equal(results, 5)
assert [99] * 5 == [r.a for r in results]
assert list(reversed([3, 4, 8, 14, 15])) == [r.b for r in results]
assert list(reversed([3, 4, 8, 14, 15])) == [r.c for r in results]
if "s1" in selector:
assert [17] * 5 == [r.s1 for r in results]
if "s2" in selector:
assert [42] * 5 == [r.s2 for r in results]
# slice on clustering column with set start
for page_size in PAGE_SIZES:
logger.debug("Current page size is {}".format(page_size))
session.default_fetch_size = page_size
for selector in selectors:
results = list(session.execute("SELECT %s FROM test WHERE a = 99 AND b > 3" % selector))
assert_length_equal(results, 12)
assert [99] * 12 == [r.a for r in results]
assert list(range(4, 16)) == [r.b for r in results]
assert list(range(4, 16)) == [r.c for r in results]
if "s1" in selector:
assert [17] * 12 == [r.s1 for r in results]
if "s2" in selector:
assert [42] * 12 == [r.s2 for r in results]
# reversed slice on clustering column with set finish
for page_size in PAGE_SIZES:
logger.debug("Current page size is {}".format(page_size))
session.default_fetch_size = page_size
for selector in selectors:
results = list(session.execute("SELECT %s FROM test WHERE a = 99 AND b > 3 ORDER BY b DESC" % selector))
assert_length_equal(results, 12)
assert [99] * 12 == [r.a for r in results]
assert list(reversed(list(range(4, 16)))) == [r.b for r in results]
assert list(reversed(list(range(4, 16)))) == [r.c for r in results]
if "s1" in selector:
assert [17] * 12 == [r.s1 for r in results]
if "s2" in selector:
assert [42] * 12 == [r.s2 for r in results]
# slice on clustering column with set finish
for page_size in PAGE_SIZES:
logger.debug("Current page size is {}".format(page_size))
session.default_fetch_size = page_size
for selector in selectors:
results = list(session.execute("SELECT %s FROM test WHERE a = 99 AND b < 14" % selector))
assert_length_equal(results, 14)
assert [99] * 14 == [r.a for r in results]
assert list(range(14)) == [r.b for r in results]
assert list(range(14)) == [r.c for r in results]
if "s1" in selector:
assert [17] * 14 == [r.s1 for r in results]
if "s2" in selector:
assert [42] * 14 == [r.s2 for r in results]
# reversed slice on clustering column with set start
for page_size in PAGE_SIZES:
logger.debug("Current page size is {}".format(page_size))
session.default_fetch_size = page_size
for selector in selectors:
results = list(session.execute("SELECT %s FROM test WHERE a = 99 AND b < 14 ORDER BY b DESC" % selector))
assert_length_equal(results, 14)
assert [99] * 14 == [r.a for r in results]
assert list(reversed(list(range(14)))) == [r.b for r in results]
assert list(reversed(list(range(14)))) == [r.c for r in results]
if "s1" in selector:
assert [17] * 14 == [r.s1 for r in results]
if "s2" in selector:
assert [42] * 14 == [r.s2 for r in results]
# slice on clustering column with start and finish
for page_size in PAGE_SIZES:
logger.debug("Current page size is {}".format(page_size))
session.default_fetch_size = page_size
for selector in selectors:
results = list(session.execute("SELECT %s FROM test WHERE a = 99 AND b > 3 AND b < 14" % selector))
assert_length_equal(results, 10)
assert [99] * 10 == [r.a for r in results]
assert list(range(4, 14)) == [r.b for r in results]
assert list(range(4, 14)) == [r.c for r in results]
if "s1" in selector:
assert [17] * 10 == [r.s1 for r in results]
if "s2" in selector:
assert [42] * 10 == [r.s2 for r in results]
# reversed slice on clustering column with start and finish
for page_size in PAGE_SIZES:
logger.debug("Current page size is {}".format(page_size))
session.default_fetch_size = page_size
for selector in selectors:
results = list(session.execute("SELECT %s FROM test WHERE a = 99 AND b > 3 AND b < 14 ORDER BY b DESC" % selector))
assert_length_equal(results, 10)
assert [99] * 10 == [r.a for r in results]
assert list(reversed(list(range(4, 14)))) == [r.b for r in results]
assert list(reversed(list(range(4, 14)))) == [r.c for r in results]
if "s1" in selector:
assert [17] * 10 == [r.s1 for r in results]
if "s2" in selector:
assert [42] * 10 == [r.s2 for r in results]
@since('2.0.6')
def test_paging_using_secondary_indexes_with_static_cols(self):
session = self.prepare()
create_ks(session, 'test_paging_size', 2)
session.execute("CREATE TABLE paging_test ( id int, s1 int static, s2 int static, mybool boolean, sometext text, PRIMARY KEY (id, sometext) )")
session.execute("CREATE INDEX ON paging_test(mybool)")
def random_txt(text):
return str(uuid.uuid4())
def bool_from_str_int(text):
return bool(int(text))
data = """
| id | s1 | s2 | mybool| sometext |
+----+----+----+-------+----------+
*100| 1 | 1 | 4 | 1 | [random] |
*300| 2 | 2 | 3 | 0 | [random] |
*500| 3 | 3 | 2 | 1 | [random] |
*400| 4 | 4 | 1 | 0 | [random] |
"""
all_data = create_rows(
data, session, 'paging_test', cl=CL.ALL,
format_funcs={'id': int, 'mybool': bool_from_str_int, 'sometext': random_txt, 's1': int, 's2': int}
)
future = session.execute_async(
SimpleStatement("select * from paging_test where mybool = true", fetch_size=400, consistency_level=CL.ALL)
)
pf = PageFetcher(future).request_all()
# the query only searched for True rows, so let's pare down the expectations for comparison
expected_data = [x for x in all_data if x.get('mybool') is True]
assert pf.pagecount() == 2
assert pf.num_results_all() == [400, 200]
assert_lists_equal_ignoring_order(expected_data, pf.all_data(), sort_key="sometext")
def test_static_columns_with_empty_non_static_columns_paging(self):
"""
@jira_ticket CASSANDRA-10381.
"""
session = self.prepare(row_factory=named_tuple_factory)
create_ks(session, 'test_paging_static_cols', 2)
session.execute("CREATE TABLE test (a int, b int, c int, s int static, PRIMARY KEY (a, b))")
for i in range(10):
session.execute("UPDATE test SET s = {} WHERE a = {}".format(i, i))
session.default_fetch_size = 2
results = list(session.execute("SELECT * FROM test"))
assert [0, 1, 2, 3, 4, 5, 6, 7, 8, 9] == sorted([r.s for r in results])
results = list(session.execute("SELECT * FROM test WHERE a IN (0, 1, 2, 3, 4)"))
assert [0, 1, 2, 3, 4] == sorted([r.s for r in results])
def test_select_in_clause_with_duplicate_keys(self):
"""
@jira_ticket CASSANDRA-12420
avoid duplicated result when key is duplicated in IN clause
"""
session = self.prepare(row_factory=named_tuple_factory)
create_ks(session, 'test_paging_static_cols', 2)
session.execute("CREATE TABLE test (a int, b int, c int, v int, PRIMARY KEY ((a, b),c))")
for i in range(3):
for j in range(3):
for k in range(3):
session.execute("INSERT INTO test (a, b, c, v) VALUES ({}, {}, {}, {})".format(i, j, k, k))
# based on partition key's token order instead of provided order and no duplication
for i in range(6):
session.default_fetch_size = i
results = rows_to_list(session.execute("SELECT * FROM test WHERE a = 1 AND b in (2, 2, 1, 1, 1)"))
assert results == [[1, 1, 0, 0],
[1, 1, 1, 1],
[1, 1, 2, 2],
[1, 2, 0, 0],
[1, 2, 1, 1],
[1, 2, 2, 2]]
@since('3.0.0')
def test_paging_with_filtering(self):
"""
@jira_ticket CASSANDRA-6377
"""
session = self.prepare(row_factory=tuple_factory)
create_ks(session, 'test_paging_with_filtering', 2)
session.execute("CREATE TABLE test (a int, b int, s int static, c int, d int, primary key (a, b))")
for i in range(5):
session.execute("INSERT INTO test (a, s) VALUES ({}, {})".format(i, i))
# Lets a row with only static values
if i != 2:
for j in range(4):
session.execute("INSERT INTO test (a, b, c, d) VALUES ({}, {}, {}, {})".format(i, j, j, i + j))
for page_size in (2, 3, 4, 5, 7, 10):
session.default_fetch_size = page_size
# Range queries
assert_all(session, "SELECT * FROM test WHERE c = 2 ALLOW FILTERING", [[1, 2, 1, 2, 3],
[0, 2, 0, 2, 2],
[4, 2, 4, 2, 6],
[3, 2, 3, 2, 5]])
assert_all(session, "SELECT * FROM test WHERE c > 1 AND c <= 2 ALLOW FILTERING", [[1, 2, 1, 2, 3],
[0, 2, 0, 2, 2],
[4, 2, 4, 2, 6],
[3, 2, 3, 2, 5]])
assert_all(session, "SELECT * FROM test WHERE c = 2 AND d > 4 ALLOW FILTERING", [[4, 2, 4, 2, 6],
[3, 2, 3, 2, 5]])
assert_all(session, "SELECT * FROM test WHERE c = 2 AND s > 1 ALLOW FILTERING", [[4, 2, 4, 2, 6],
[3, 2, 3, 2, 5]])
# Range queries with LIMIT
assert_all(session, "SELECT * FROM test WHERE c = 2 LIMIT 2 ALLOW FILTERING", [[1, 2, 1, 2, 3],
[0, 2, 0, 2, 2]])
assert_all(session, "SELECT * FROM test WHERE c = 2 AND s >= 1 LIMIT 2 ALLOW FILTERING", [[1, 2, 1, 2, 3],
[4, 2, 4, 2, 6]])
# Range query with DISTINCT
assert_all(session, "SELECT DISTINCT a, s FROM test WHERE s >= 1 ALLOW FILTERING", [[1, 1],
[2, 2],
[4, 4],
[3, 3]])
# Range query with DISTINCT and LIMIT
assert_all(session, "SELECT DISTINCT a, s FROM test WHERE s >= 1 LIMIT 2 ALLOW FILTERING", [[1, 1],
[2, 2]])
# Single partition queries
assert_all(session, "SELECT * FROM test WHERE a = 0 AND c >= 1 ALLOW FILTERING", [[0, 1, 0, 1, 1],
[0, 2, 0, 2, 2],
[0, 3, 0, 3, 3]])
assert_all(session, "SELECT * FROM test WHERE a= 0 AND c >= 1 AND c <=2 ALLOW FILTERING", [[0, 1, 0, 1, 1],
[0, 2, 0, 2, 2]])
assert_one(session, "SELECT * FROM test WHERE a = 0 AND c >= 1 AND d = 1 ALLOW FILTERING", [0, 1, 0, 1, 1])
assert_all(session, "SELECT * FROM test WHERE a = 3 AND c >= 1 AND s > 1 ALLOW FILTERING", [[3, 1, 3, 1, 4],
[3, 2, 3, 2, 5],
[3, 3, 3, 3, 6]])
# Single partition queries with LIMIT
assert_all(session, "SELECT * FROM test WHERE a = 0 AND c >= 1 LIMIT 2 ALLOW FILTERING", [[0, 1, 0, 1, 1],
[0, 2, 0, 2, 2]])
assert_all(session, "SELECT * FROM test WHERE a = 3 AND c >= 1 AND s > 1 LIMIT 2 ALLOW FILTERING", [[3, 1, 3, 1, 4],
[3, 2, 3, 2, 5]])
# Single partition query with DISTINCT
assert_one(session, "SELECT DISTINCT a, s FROM test WHERE a = 2 AND s >= 1 ALLOW FILTERING", [2, 2])
# Single partition query with ORDER BY
assert_all(session, "SELECT * FROM test WHERE a = 0 AND c >= 1 ORDER BY b DESC ALLOW FILTERING", [[0, 3, 0, 3, 3],
[0, 2, 0, 2, 2],
[0, 1, 0, 1, 1]])
# Single partition query with ORDER BY and LIMIT
assert_all(session, "SELECT * FROM test WHERE a = 0 AND c >= 1 ORDER BY b DESC LIMIT 2 ALLOW FILTERING", [[0, 3, 0, 3, 3],
[0, 2, 0, 2, 2]])
# Multi-partitions queries
assert_all(session, "SELECT * FROM test WHERE a IN (0, 1, 2, 3, 4) AND c = 2 ALLOW FILTERING", [[0, 2, 0, 2, 2],
[1, 2, 1, 2, 3],
[3, 2, 3, 2, 5],
[4, 2, 4, 2, 6]])
assert_all(session, "SELECT * FROM test WHERE a IN (0, 1, 2, 3, 4) AND c > 1 AND c <=2 ALLOW FILTERING", [[0, 2, 0, 2, 2],
[1, 2, 1, 2, 3],
[3, 2, 3, 2, 5],
[4, 2, 4, 2, 6]])
assert_all(session, "SELECT * FROM test WHERE a IN (0, 1, 2, 3, 4) AND c = 2 AND d > 4 ALLOW FILTERING", [[3, 2, 3, 2, 5],
[4, 2, 4, 2, 6]])
assert_all(session, "SELECT * FROM test WHERE a IN (0, 1, 2, 3, 4) AND c = 2 AND s > 1 ALLOW FILTERING", [[3, 2, 3, 2, 5],
[4, 2, 4, 2, 6]])
# Multi-partitions queries with LIMIT
assert_all(session, "SELECT * FROM test WHERE a IN (0, 1, 2, 3, 4) AND c = 2 LIMIT 2 ALLOW FILTERING", [[0, 2, 0, 2, 2],
[1, 2, 1, 2, 3]])
assert_all(session, "SELECT * FROM test WHERE a IN (0, 1, 2, 3, 4) AND c = 2 AND s >= 1 LIMIT 2 ALLOW FILTERING", [[1, 2, 1, 2, 3],
[3, 2, 3, 2, 5]])
# Multi-partitions query with DISTINCT
assert_all(session, "SELECT DISTINCT a, s FROM test WHERE a IN (0, 1, 2, 3, 4) AND s >= 1 ALLOW FILTERING", [[1, 1],
[2, 2],
[3, 3],
[4, 4]])
# Multi-partitions query with DISTINCT and LIMIT
assert_all(session, "SELECT DISTINCT a, s FROM test WHERE a IN (0, 1, 2, 3, 4) AND s >= 1 LIMIT 2 ALLOW FILTERING", [[1, 1],
[2, 2]])
def _test_paging_with_filtering_on_counter_columns(self, session, with_compact_storage):
if with_compact_storage:
create_ks(session, 'test_flt_counter_columns_compact_storage', 2)
session.execute("CREATE TABLE test (a int, b int, c int, cnt counter, PRIMARY KEY (a, b, c)) WITH COMPACT STORAGE")
else:
create_ks(session, 'test_flt_counter_columns', 2)
session.execute("CREATE TABLE test (a int, b int, c int, cnt counter, PRIMARY KEY (a, b, c))")
for i in range(5):
for j in range(10):
session.execute("UPDATE test SET cnt = cnt + {} WHERE a={} AND b={} AND c={}".format(j + 2, i, j, j + 1))
self.longMessage = True
for page_size in (2, 3, 4, 5, 7, 10, 20):
session.default_fetch_size = page_size
page_size_error_msg = "Query failed with page size {}".format(page_size)
# single partition
res = rows_to_list(session.execute("SELECT * FROM test WHERE a = 4 AND b > 3 AND c > 3 AND cnt > 8 ALLOW FILTERING"))
assert res == [[4, 7, 8, 9],
[4, 8, 9, 10],
[4, 9, 10, 11]], \
page_size_error_msg
res = rows_to_list(session.execute("SELECT * FROM test WHERE a = 4 AND b > 3 AND c > 3 AND cnt >= 8 ALLOW FILTERING"))
assert res == [[4, 6, 7, 8],
[4, 7, 8, 9],
[4, 8, 9, 10],
[4, 9, 10, 11]], \
page_size_error_msg
res = rows_to_list(session.execute("SELECT * FROM test WHERE a = 4 AND b > 3 AND c > 3 AND cnt >= 8 AND cnt < 10 ALLOW FILTERING"))
assert res == [[4, 6, 7, 8],
[4, 7, 8, 9]], \
page_size_error_msg
res = rows_to_list(session.execute("SELECT * FROM test WHERE a = 4 AND b > 3 AND c > 3 AND cnt >= 8 AND cnt <= 10 ALLOW FILTERING"))
assert res == [[4, 6, 7, 8],
[4, 7, 8, 9],
[4, 8, 9, 10]], \
page_size_error_msg
res = rows_to_list(session.execute("SELECT * FROM test WHERE cnt = 5 ALLOW FILTERING"))
self.assertEqualIgnoreOrder(res, [[0, 3, 4, 5],
[1, 3, 4, 5],
[2, 3, 4, 5],
[3, 3, 4, 5],
[4, 3, 4, 5]])
res = rows_to_list(session.execute("SELECT * FROM test WHERE a IN (1,2,3) AND cnt = 5 ALLOW FILTERING"))
self.assertEqualIgnoreOrder(res, [[1, 3, 4, 5],
[2, 3, 4, 5],
[3, 3, 4, 5]])
@since('3.6')
def test_paging_with_filtering_on_counter_columns(self):
"""
test paging, when filtering on counter columns
@jira_ticket CASSANDRA-11629
"""
session = self.prepare(row_factory=tuple_factory)
self._test_paging_with_filtering_on_counter_columns(session, False)
@since("3.6", max_version="3.X") # Compact Storage
def test_paging_with_filtering_on_counter_columns_compact(self):
"""
test paging, when filtering on counter columns with compact storage
@jira_ticket CASSANDRA-11629
"""
session = self.prepare(row_factory=tuple_factory)
self._test_paging_with_filtering_on_counter_columns(session, True)
def _test_paging_with_filtering_on_clustering_columns(self, session, with_compact_storage):
if with_compact_storage:
create_ks(session, 'test_flt_clustering_columns_compact_storage', 2)
session.execute("CREATE TABLE test (a int, b int, c int, d int, PRIMARY KEY (a, b, c)) WITH COMPACT STORAGE")
else:
create_ks(session, 'test_flt_clustering_columns', 2)
session.execute("CREATE TABLE test (a int, b int, c int, d int, PRIMARY KEY (a, b, c))")
for i in range(5):
for j in range(10):
session.execute("INSERT INTO test (a,b,c,d) VALUES ({},{},{},{})".format(i, j, j + 1, j + 2))
for page_size in (2, 3, 4, 5, 7, 10, 20):
session.default_fetch_size = page_size
# single partition
assert_all(session, "SELECT * FROM test WHERE a = 4 AND b > 3 AND c > 3 ALLOW FILTERING", [[4, 4, 5, 6],
[4, 5, 6, 7],
[4, 6, 7, 8],
[4, 7, 8, 9],
[4, 8, 9, 10],
[4, 9, 10, 11]])
assert_all(session, "SELECT * FROM test WHERE a = 4 AND b > 3 AND c > 3 LIMIT 4 ALLOW FILTERING", [[4, 4, 5, 6],
[4, 5, 6, 7],
[4, 6, 7, 8],
[4, 7, 8, 9]])
assert_all(session, "SELECT * FROM test WHERE a = 4 AND b > 3 AND c > 3 ORDER BY b DESC ALLOW FILTERING", [[4, 9, 10, 11],
[4, 8, 9, 10],
[4, 7, 8, 9],
[4, 6, 7, 8],
[4, 5, 6, 7],
[4, 4, 5, 6]])
assert_all(session, "SELECT * FROM test WHERE b > 7 AND c > 9 ALLOW FILTERING", [[0, 9, 10, 11],
[1, 9, 10, 11],
[2, 9, 10, 11],
[3, 9, 10, 11],
[4, 9, 10, 11]], ignore_order=True)
assert_all(session, "SELECT * FROM test WHERE b > 4 AND b < 6 AND c > 3 ALLOW FILTERING", [[0, 5, 6, 7],
[1, 5, 6, 7],
[2, 5, 6, 7],
[3, 5, 6, 7],
[4, 5, 6, 7]], ignore_order=True)
assert_all(session, "SELECT * FROM test WHERE d = 5 ALLOW FILTERING", [[0, 3, 4, 5],
[1, 3, 4, 5],
[2, 3, 4, 5],
[3, 3, 4, 5],
[4, 3, 4, 5]], ignore_order=True)
assert_all(session, "SELECT * FROM test WHERE (b, c) > (4, 3) AND (b, c) < (5, 6) ALLOW FILTERING", [[0, 4, 5, 6],
[1, 4, 5, 6],
[2, 4, 5, 6],
[3, 4, 5, 6],
[4, 4, 5, 6]], ignore_order=True)
assert_all(session, "SELECT * FROM test WHERE (b, c) > (2, 3) AND b < 4 ALLOW FILTERING", [[0, 3, 4, 5],
[1, 3, 4, 5],
[2, 3, 4, 5],
[3, 3, 4, 5],
[4, 3, 4, 5]], ignore_order=True)
assert_all(session, "SELECT * FROM test where (b, c) > (2, 2) AND b < 8 AND d = 5 ALLOW FILTERING", [[0, 3, 4, 5],
[1, 3, 4, 5],
[2, 3, 4, 5],
[3, 3, 4, 5],
[4, 3, 4, 5]], ignore_order=True)
@since('3.6')
def test_paging_with_filtering_on_clustering_columns(self):
"""
test paging, when filtering on clustering columns
@jira_ticket CASSANDRA-11310
"""
session = self.prepare(row_factory=tuple_factory)
self._test_paging_with_filtering_on_clustering_columns(session, False)
@since('3.6', max_version="3.X") # Compact Storage
def test_paging_with_filtering_on_clustering_columns_compact(self):
"""
test paging, when filtering on clustering columns with compact storage
@jira_ticket CASSANDRA-11310
"""
session = self.prepare(row_factory=tuple_factory)
self._test_paging_with_filtering_on_clustering_columns(session, True)
@since('3.6')
def test_paging_with_filtering_on_clustering_columns_with_contains(self):
"""
test paging, when filtering on clustering columns (frozen collections) with CONTAINS statement
@jira_ticket CASSANDRA-11310
"""
session = self.prepare(row_factory=tuple_factory)
create_ks(session, 'test_paging_flt_clustering_clm_contains', 2)
session.execute("CREATE TABLE test_list (a int, b int, c frozen<list<int>>, d int, PRIMARY KEY (a, b, c))")
session.execute("CREATE TABLE test_map (a int, b int, c frozen<map<int, int>>, d int, PRIMARY KEY (a, b, c))")
for i in range(5):
for j in range(10):
session.execute("INSERT INTO test_list (a,b,c,d) VALUES ({},{},[{}, {}],{})".format(i, j, j + 1, j + 2, j + 3, j + 4))
session.execute("INSERT INTO test_map (a,b,c,d) VALUES ({},{},{{ {}: {} }},{})".format(i, j, j + 1, j + 2, j + 3, j + 4))
for page_size in (2, 3, 4, 5, 7, 10, 20):
session.default_fetch_size = page_size
assert_all(session, "SELECT * FROM test_list WHERE c CONTAINS 11 ALLOW FILTERING", [[0, 9, [10, 11], 12],
[1, 9, [10, 11], 12],
[2, 9, [10, 11], 12],
[3, 9, [10, 11], 12],
[4, 9, [10, 11], 12]], ignore_order=True)
assert_all(session, "SELECT * FROM test_map WHERE c CONTAINS KEY 10 ALLOW FILTERING", [[0, 9, {10: 11}, 12],
[1, 9, {10: 11}, 12],
[2, 9, {10: 11}, 12],
[3, 9, {10: 11}, 12],
[4, 9, {10: 11}, 12]], ignore_order=True)
assert_all(session, "SELECT * FROM test_list WHERE c CONTAINS 2 AND c CONTAINS 3 ALLOW FILTERING", [[0, 1, [2, 3], 4],
[1, 1, [2, 3], 4],
[2, 1, [2, 3], 4],
[3, 1, [2, 3], 4],
[4, 1, [2, 3], 4]], ignore_order=True)
assert_all(session, "SELECT * FROM test_map WHERE c CONTAINS KEY 2 AND c CONTAINS 3 ALLOW FILTERING", [[0, 1, {2: 3}, 4],
[1, 1, {2: 3}, 4],
[2, 1, {2: 3}, 4],
[3, 1, {2: 3}, 4],
[4, 1, {2: 3}, 4]], ignore_order=True)
assert_all(session, "SELECT * FROM test_list WHERE c CONTAINS 2 AND d = 4 ALLOW FILTERING", [[0, 1, [2, 3], 4],
[1, 1, [2, 3], 4],
[2, 1, [2, 3], 4],
[3, 1, [2, 3], 4],
[4, 1, [2, 3], 4]], ignore_order=True)
assert_all(session, "SELECT * FROM test_map WHERE c CONTAINS KEY 2 AND d = 4 ALLOW FILTERING", [[0, 1, {2: 3}, 4],
[1, 1, {2: 3}, 4],
[2, 1, {2: 3}, 4],
[3, 1, {2: 3}, 4],
[4, 1, {2: 3}, 4]], ignore_order=True)
assert_all(session, "SELECT * FROM test_list WHERE c CONTAINS 2 AND d = 4 ALLOW FILTERING", [[0, 1, [2, 3], 4],
[1, 1, [2, 3], 4],
[2, 1, [2, 3], 4],
[3, 1, [2, 3], 4],
[4, 1, [2, 3], 4]], ignore_order=True)
assert_all(session, "SELECT * FROM test_map WHERE c CONTAINS KEY 2 AND d = 4 ALLOW FILTERING", [[0, 1, {2: 3}, 4],
[1, 1, {2: 3}, 4],
[2, 1, {2: 3}, 4],
[3, 1, {2: 3}, 4],
[4, 1, {2: 3}, 4]], ignore_order=True)
assert_all(session, "SELECT * FROM test_list WHERE c CONTAINS 2 AND d < 4 ALLOW FILTERING", [[0, 0, [1, 2], 3],
[1, 0, [1, 2], 3],
[2, 0, [1, 2], 3],
[3, 0, [1, 2], 3],
[4, 0, [1, 2], 3]], ignore_order=True)
assert_all(session, "SELECT * FROM test_map WHERE c CONTAINS KEY 1 AND d < 4 ALLOW FILTERING", [[0, 0, {1: 2}, 3],
[1, 0, {1: 2}, 3],
[2, 0, {1: 2}, 3],
[3, 0, {1: 2}, 3],
[4, 0, {1: 2}, 3]], ignore_order=True)
@since('3.6')
def test_paging_with_filtering_on_static_columns(self):
"""
test paging, when filtering on static columns
@jira_ticket CASSANDRA-11310
"""
session = self.prepare(row_factory=tuple_factory)
create_ks(session, 'test_paging_with_filtering_on_static_columns', 2<