def test_filter_expression_and_sort_key_condition(test_table_sn_with_data):
table, p, items = test_table_sn_with_data
i = items[2]['i']
got_items = full_query(table,
KeyConditionExpression='p=:p and c<=:c',
FilterExpression='i>:i',
ExpressionAttributeValues={
':p': p,
':c': 7,
':i': i
})
expected_items = [
item for item in items if item['i'] > i and item['c'] <= 7
]
assert (got_items == expected_items)
def test_key_condition_expression_str_between(
test_table_ss_with_sorted_partition):
table, p, items = test_table_ss_with_sorted_partition
got_items = full_query(
table,
KeyConditionExpression='p=:p AND c BETWEEN :c1 AND :c2',
ExpressionAttributeValues={
':p': p,
':c1': '004',
':c2': '007'
})
expected_items = [
item for item in items if item['c'] >= '004' and item['c'] <= '007'
]
assert (got_items == expected_items)
def test_key_condition_expression_name_ref(
test_table_sn_with_sorted_partition):
table, p, items = test_table_sn_with_sorted_partition
got_items = full_query(table,
KeyConditionExpression='#name1=:p AND #name2=:c',
ExpressionAttributeValues={
':p': p,
':c': 3
},
ExpressionAttributeNames={
'#name1': 'p',
'#name2': 'c'
})
expected_items = [item for item in items if item['c'] == 3]
assert (got_items == expected_items)
def test_key_condition_expression_case_insensitive(
test_table_sn_with_sorted_partition):
table, p, items = test_table_sn_with_sorted_partition
got_items = full_query(
table,
KeyConditionExpression='p=:p AnD c BeTwEeN :c1 aNd :c2',
ExpressionAttributeValues={
':p': p,
':c1': 4,
':c2': 7
})
expected_items = [
item for item in items if item['c'] >= 4 and item['c'] <= 7
]
assert (got_items == expected_items)
def test_filter_expression_and(test_table_sn_with_data):
table, p, items = test_table_sn_with_data
i1 = items[2]['i']
s1 = items[2]['s']
got_items = full_query(table,
KeyConditionExpression='p=:p',
FilterExpression='i=:i1 aND s=:s1',
ExpressionAttributeValues={
':p': p,
':i1': i1,
':s1': s1
})
expected_items = [
item for item in items if item['i'] == i1 and item['s'] == s1
]
assert (got_items == expected_items)
def test_key_condition_expression_bytes_between(
test_table_sb_with_sorted_partition):
table, p, items = test_table_sb_with_sorted_partition
got_items = full_query(
table,
KeyConditionExpression='p=:p AND c BETWEEN :c1 AND :c2',
ExpressionAttributeValues={
':p': p,
':c1': bytearray('004', 'ascii'),
':c2': bytearray('007', 'ascii')
})
expected_items = [
item for item in items if item['c'] >= bytearray('004', 'ascii')
and item['c'] <= bytearray('007', 'ascii')
]
assert (got_items == expected_items)
def test_query_filter_empty_results(test_table_sn_with_data):
table, p, items = test_table_sn_with_data
got_items = full_query(table,
KeyConditions={
'p': {
'AttributeValueList': [p],
'ComparisonOperator': 'EQ'
}
},
QueryFilter={
'non_existent': {
'AttributeValueList': [3],
'ComparisonOperator': 'EQ'
}
})
assert (got_items == [])
def test_query_reverse_paging(test_table_sn):
numbers = [Decimal(i) for i in range(20)]
# Insert these numbers, in random order, into one partition:
p = random_string()
items = [{'p': p, 'c': num} for num in random.sample(numbers, len(numbers))]
with test_table_sn.batch_writer() as batch:
for item in items:
batch.put_item(item)
reversed_numbers = list(reversed(numbers))
# Verify that with ScanIndexForward=False, full_query() returns all
# these numbers in reversed sorted order - getting pages of Limit items
# at a time and resuming the query.
for limit in [1, 2, 3, 7, 10, 17, 100, 10000]:
got_items = full_query(test_table_sn, KeyConditions={'p': {'AttributeValueList': [p], 'ComparisonOperator': 'EQ'}}, ScanIndexForward=False, Limit=limit)
got_sort_keys = [x['c'] for x in got_items]
assert got_sort_keys == reversed_numbers
def test_query_filter_contains_member(test_table_sn_with_data):
table, p, items = test_table_sn_with_data
for op in ['CONTAINS', 'NOT_CONTAINS']:
# Check that we can find members in both lists and sets.
# We also check ss, with string members, to ensure that for a string
# parameter membership check is also done, not just substring check.
for xn in ['ns', 'ss', 'l']:
xv = next(iter(items[2][xn]))
got_items = full_query(table,
KeyConditions={ 'p': { 'AttributeValueList': [p], 'ComparisonOperator': 'EQ' }},
QueryFilter={ xn: { 'AttributeValueList': [xv], 'ComparisonOperator': op }})
if op == 'CONTAINS':
expected_items = [item for item in items if xv in item[xn]]
else:
expected_items = [item for item in items if not xv in item[xn]]
assert(got_items == expected_items)
def test_key_condition_expression_parser(test_table_sn_with_sorted_partition):
table, p, items = test_table_sn_with_sorted_partition
# The operators "<>" and "IN" are parsed, but not allowed:
with pytest.raises(ClientError, match='ValidationException.*operator'):
full_query(table, KeyConditionExpression='p=:p AND c<>:c',
ExpressionAttributeValues={':p': p, ':c': 5})
with pytest.raises(ClientError, match='ValidationException.*operator'):
full_query(table, KeyConditionExpression='p=:p AND c IN (:c)',
ExpressionAttributeValues={':p': p, ':c': 5})
# The "OR" or "NOT" operators are parsed, but not allowed:
with pytest.raises(ClientError, match='ValidationException.*OR'):
full_query(table, KeyConditionExpression='c=:c OR p=:p',
ExpressionAttributeValues={':p': p, ':c': 3})
with pytest.raises(ClientError, match='ValidationException.*NOT'):
full_query(table, KeyConditionExpression='NOT c=:c AND p=:p',
ExpressionAttributeValues={':p': p, ':c': 3})
# Unnecessary parentheses are allowed around the entire expression,
# and on each primitive condition in it:
got_items = full_query(table, KeyConditionExpression='((c=:c) AND (p=:p))',
ExpressionAttributeValues={':p': p, ':c': 3})
expected_items = [item for item in items if item['c'] == 3]
assert(got_items == expected_items)
def test_query_sort_order_bytes(test_table_sb):
# Insert a lot of random items in one new partition:
# We arbitrarily use random_bytes with a random length.
p = random_string()
items = [{'p': p, 'c': random_bytes(10)} for i in range(128)]
with test_table_sb.batch_writer() as batch:
for item in items:
batch.put_item(item)
got_items = full_query(test_table_sb, KeyConditions={'p': {'AttributeValueList': [p], 'ComparisonOperator': 'EQ'}})
assert len(items) == len(got_items)
sort_keys = [x['c'] for x in items]
got_sort_keys = [x['c'] for x in got_items]
# Boto3's "Binary" objects are sorted as if bytes are signed integers.
# This isn't the order that DynamoDB itself uses (byte 0 should be first,
# not byte -128). Sorting the byte array ".value" works.
assert sorted(got_sort_keys, key=lambda x: x.value) == got_sort_keys
assert sorted(sort_keys) == got_sort_keys
def test_query_sort_order_string(test_table):
# Insert a lot of random items in one new partition:
# str(i) has a non-obvious sort order (e.g., "100" comes before "2") so is a nice test.
p = random_string()
items = [{'p': p, 'c': str(i)} for i in range(128)]
with test_table.batch_writer() as batch:
for item in items:
batch.put_item(item)
got_items = full_query(test_table, KeyConditions={'p': {'AttributeValueList': [p], 'ComparisonOperator': 'EQ'}})
assert len(items) == len(got_items)
# Extract just the sort key ("c") from the items
sort_keys = [x['c'] for x in items]
got_sort_keys = [x['c'] for x in got_items]
# Verify that got_sort_keys are already sorted (in string order)
assert sorted(got_sort_keys) == got_sort_keys
# Verify that got_sort_keys are a sorted version of the expected sort_keys
assert sorted(sort_keys) == got_sort_keys
def test_key_conditions_str_between(test_table_ss_with_sorted_partition):
table, p, items = test_table_ss_with_sorted_partition
got_items = full_query(table,
KeyConditions={
'p': {
'AttributeValueList': [p],
'ComparisonOperator': 'EQ'
},
'c': {
'AttributeValueList': ['004', '007'],
'ComparisonOperator': 'BETWEEN'
}
})
expected_items = [
item for item in items if item['c'] >= '004' and item['c'] <= '007'
]
assert (got_items == expected_items)
def test_query_paging_string(test_table_ss):
p = random_string()
items = [{'p': p, 'c': random_string()} for i in range(10)]
with test_table_ss.batch_writer() as batch:
for item in items:
batch.put_item(item)
got_items = full_query(test_table_ss,
Limit=1,
KeyConditions={
'p': {
'AttributeValueList': [p],
'ComparisonOperator': 'EQ'
}
})
got_sort_keys = [x['c'] for x in got_items]
expected_sort_keys = sorted(x['c'] for x in items)
assert got_sort_keys == expected_sort_keys
def test_filter_expression_and_projection_expression(test_table):
p = random_string()
test_table.put_item(Item={'p': p, 'c': 'hi', 'x': 'dog', 'y': 'cat'})
test_table.put_item(Item={'p': p, 'c': 'yo', 'x': 'mouse', 'y': 'horse'})
# Note that the filter is on the column x, but x is not included in the
# results because of ProjectionExpression. The filter should still work.
got_items = full_query(test_table,
KeyConditionExpression='p=:p',
FilterExpression='x=:x',
ProjectionExpression='y',
ExpressionAttributeValues={':p': p, ':x': 'mouse'})
# Note that:
# 1. Exactly one item matches the filter on x
# 2. The returned record for that item will include *only* the attribute y
# as requestd by ProjectionExpression. It won't include x - it was just
# needed for the filter, but didn't appear in ProjectionExpression.
expected_items = [{'y': 'horse'}]
assert(got_items == expected_items)
def test_filter_expression_string_size(test_table_sn_with_data):
table, p, items = test_table_sn_with_data
s = items[2]['s']
got_items = full_query(table, KeyConditionExpression='p=:p', FilterExpression='size(s) < :i',
ExpressionAttributeValues={':p': p, ':i': len(s)})
expected_items = [item for item in items if len(item['s']) < len(s)]
assert(got_items == expected_items)
# Test the rest of the operations: <=, >, >=, =, <>, BETWEEN, IN
got_items = full_query(table, KeyConditionExpression='p=:p', FilterExpression='size(s) <= :i',
ExpressionAttributeValues={':p': p, ':i': len(s)})
expected_items = [item for item in items if len(item['s']) <= len(s)]
assert(got_items == expected_items)
got_items = full_query(table, KeyConditionExpression='p=:p', FilterExpression='size(s) > :i',
ExpressionAttributeValues={':p': p, ':i': len(s)})
expected_items = [item for item in items if len(item['s']) > len(s)]
assert(got_items == expected_items)
got_items = full_query(table, KeyConditionExpression='p=:p', FilterExpression='size(s) >= :i',
ExpressionAttributeValues={':p': p, ':i': len(s)})
expected_items = [item for item in items if len(item['s']) >= len(s)]
assert(got_items == expected_items)
got_items = full_query(table, KeyConditionExpression='p=:p', FilterExpression='size(s) = :i',
ExpressionAttributeValues={':p': p, ':i': len(s)})
expected_items = [item for item in items if len(item['s']) == len(s)]
assert(got_items == expected_items)
got_items = full_query(table, KeyConditionExpression='p=:p', FilterExpression='size(s) <> :i',
ExpressionAttributeValues={':p': p, ':i': len(s)})
expected_items = [item for item in items if len(item['s']) != len(s)]
assert(got_items == expected_items)
s2 = items[3]['s']
len1 = len(s)
len2 = len(s2)
if len1 > len2:
len1, len2 = len2, len1
got_items = full_query(table, KeyConditionExpression='p=:p', FilterExpression='size(s) BETWEEN :i1 AND :i2',
ExpressionAttributeValues={':p': p, ':i1': len1, ':i2': len2})
expected_items = [item for item in items if len(item['s']) >= len1 and len(item['s']) <= len2]
assert(got_items == expected_items)
got_items = full_query(table, KeyConditionExpression='p=:p', FilterExpression='size(s) IN (:i1, :i2)',
ExpressionAttributeValues={':p': p, ':i1': len1, ':i2': len2})
expected_items = [item for item in items if len(item['s']) == len1 or len(item['s']) == len2]
assert(got_items == expected_items)
def test_key_conditions_bytes_begins_with(test_table_sb_with_sorted_partition):
table, p, items = test_table_sb_with_sorted_partition
got_items = full_query(table,
KeyConditions={
'p': {
'AttributeValueList': [p],
'ComparisonOperator': 'EQ'
},
'c': {
'AttributeValueList':
[bytearray('00', 'ascii')],
'ComparisonOperator': 'BEGINS_WITH'
}
})
expected_items = [
item for item in items
if item['c'].startswith(bytearray('00', 'ascii'))
]
assert (got_items == expected_items)
def test_query_filter_r_eq(test_table_sn_with_data):
table, p, items = test_table_sn_with_data
# note that random_item() guarantees the first item has an 'r':
r = items[0]['r']
got_items = full_query(table,
KeyConditions={
'p': {
'AttributeValueList': [p],
'ComparisonOperator': 'EQ'
}
},
QueryFilter={
'r': {
'AttributeValueList': [r],
'ComparisonOperator': 'EQ'
}
})
expected_items = [item for item in items if 'r' in item and item['r'] == r]
assert (got_items == expected_items)
def test_filter_expression_or(test_table_sn_with_data):
table, p, items = test_table_sn_with_data
i1 = items[2]['i']
i2 = items[5]['i']
i3 = items[7]['i']
got_items = full_query(table,
KeyConditionExpression='p=:p',
FilterExpression='i=:i1 OR i=:i2 oR i=:i3',
ExpressionAttributeValues={
':p': p,
':i1': i1,
':i2': i2,
':i3': i3
})
expected_items = [
item for item in items
if item['i'] == i1 or item['i'] == i2 or item['i'] == i3
]
assert (got_items == expected_items)
def test_query_paging_bytes(test_table_sb):
p = random_string()
items = [{'p': p, 'c': random_bytes()} for i in range(10)]
with test_table_sb.batch_writer() as batch:
for item in items:
batch.put_item(item)
# Deliberately pass Limit=1 to enforce paging even though we have
# just 10 items in the partition.
got_items = full_query(test_table_sb,
Limit=1,
KeyConditions={
'p': {
'AttributeValueList': [p],
'ComparisonOperator': 'EQ'
}
})
got_sort_keys = [x['c'] for x in got_items]
expected_sort_keys = sorted(x['c'] for x in items)
assert got_sort_keys == expected_sort_keys
def test_query_filter_ge(test_table_sn_with_data):
table, p, items = test_table_sn_with_data
for xn in ['i', 's', 'b']:
xv = items[2][xn]
got_items = full_query(table,
KeyConditions={
'p': {
'AttributeValueList': [p],
'ComparisonOperator': 'EQ'
}
},
QueryFilter={
xn: {
'AttributeValueList': [xv],
'ComparisonOperator': 'GE'
}
})
expected_items = [item for item in items if item[xn] >= xv]
assert (got_items == expected_items)
def test_key_condition_expression_bad_compare(
test_table_sn_with_sorted_partition):
table, p, items = test_table_sn_with_sorted_partition
with pytest.raises(ClientError, match='ValidationException'):
full_query(table, KeyConditionExpression='p=c')
with pytest.raises(ClientError, match='ValidationException'):
full_query(table, KeyConditionExpression='p=a')
with pytest.raises(ClientError, match='ValidationException'):
full_query(table,
KeyConditionExpression=':p=:a',
ExpressionAttributeValues={
':p': p,
':a': 3
})
def test_filter_expression_between(test_table_sn_with_data):
table, p, items = test_table_sn_with_data
for xn in ['i', 's', 'b']:
xv1 = items[2][xn]
xv2 = items[3][xn]
if xv1 > xv2:
xv1, xv2 = xv2, xv1
got_items = full_query(table,
KeyConditionExpression='p=:p',
FilterExpression=(xn +
' BetWEeN :xv1 AnD :xv2'),
ExpressionAttributeValues={
':p': p,
':xv1': xv1,
':xv2': xv2
})
expected_items = [
item for item in items if item[xn] >= xv1 and item[xn] <= xv2
]
assert (got_items == expected_items)
def test_filter_expression_in(test_table_sn_with_data):
table, p, items = test_table_sn_with_data
for xn in ['i', 's', 'b']:
xv1 = items[2][xn]
xv2 = items[7][xn]
xv3 = items[4][xn]
got_items = full_query(table,
KeyConditionExpression='p=:p',
FilterExpression=(xn +
' In (:xv1, :xv2, :xv3)'),
ExpressionAttributeValues={
':p': p,
':xv1': xv1,
':xv2': xv2,
':xv3': xv3
})
expected_items = [
item for item in items
if item[xn] == xv1 or item[xn] == xv2 or item[xn] == xv3
]
assert (got_items == expected_items)
def test_query_sort_order_number(test_table_sn):
# This is a list of numbers, sorted in correct order, and each suitable
# for accurate representation by Alternator's number type.
numbers = [
Decimal("-2e10"),
Decimal("-7.1e2"),
Decimal("-4.1"),
Decimal("-0.1"),
Decimal("-1e-5"),
Decimal("0"),
Decimal("2e-5"),
Decimal("0.15"),
Decimal("1"),
Decimal("1.00000000000000000000000001"),
Decimal("3.14159"),
Decimal("3.1415926535897932384626433832795028841"),
Decimal("31.4"),
Decimal("1.4e10"),
]
# Insert these numbers, in random order, into one partition:
p = random_string()
items = [{
'p': p,
'c': num
} for num in random.sample(numbers, len(numbers))]
with test_table_sn.batch_writer() as batch:
for item in items:
batch.put_item(item)
# Finally, verify that we get back exactly the same numbers (with identical
# precision), and in their original sorted order.
got_items = full_query(test_table_sn,
KeyConditions={
'p': {
'AttributeValueList': [p],
'ComparisonOperator': 'EQ'
}
})
got_sort_keys = [x['c'] for x in got_items]
assert got_sort_keys == numbers
def test_key_conditions_bytes_between(test_table_sb_with_sorted_partition):
table, p, items = test_table_sb_with_sorted_partition
got_items = full_query(
table,
KeyConditions={
'p': {
'AttributeValueList': [p],
'ComparisonOperator': 'EQ'
},
'c': {
'AttributeValueList':
[bytearray('004', 'ascii'),
bytearray('007', 'ascii')],
'ComparisonOperator':
'BETWEEN'
}
})
expected_items = [
item for item in items if item['c'] >= bytearray('004', 'ascii')
and item['c'] <= bytearray('007', 'ascii')
]
assert (got_items == expected_items)
def test_query_filter_null(test_table_sn_with_data):
table, p, items = test_table_sn_with_data
for op in ['NULL', 'NOT_NULL']:
# Note the "r" attribute is missing from some items, so is useful
# for this test.
got_items = full_query(table,
KeyConditions={
'p': {
'AttributeValueList': [p],
'ComparisonOperator': 'EQ'
}
},
QueryFilter={
'r': {
'AttributeValueList': [],
'ComparisonOperator': op
}
})
if op == 'NULL':
expected_items = [item for item in items if not 'r' in item]
else:
expected_items = [item for item in items if 'r' in item]
assert (got_items == expected_items)
def test_key_conditions_sort_unsupported(test_table_sn_with_sorted_partition):
table, p, items = test_table_sn_with_sorted_partition
# The operators 'NULL', 'NOT_NULL', 'IN', 'CONTAINS', 'NOT_CONTAINS', 'NE'
# exist for ComparisonOperator and are allowed in filters, but not allowed
# in key conditions:
for op in ['IN', 'CONTAINS', 'NOT_CONTAINS', 'NE']:
with pytest.raises(ClientError, match='ValidationException'):
full_query(table,
KeyConditions={
'p': {
'AttributeValueList': [p],
'ComparisonOperator': 'EQ'
},
'c': {
'AttributeValueList': [3],
'ComparisonOperator': op
}
})
for op in ['NULL', 'NOT_NULL']:
with pytest.raises(ClientError, match='ValidationException'):
full_query(table,
KeyConditions={
'p': {
'AttributeValueList': [p],
'ComparisonOperator': 'EQ'
},
'c': {
'AttributeValueList': [],
'ComparisonOperator': op
}
})
# An unknown operator, e.g., "DOG", is also not allowed.
with pytest.raises(ClientError, match='ValidationException.*DOG'):
full_query(table,
KeyConditions={
'p': {
'AttributeValueList': [p],
'ComparisonOperator': 'EQ'
},
'c': {
'AttributeValueList': [p],
'ComparisonOperator': 'DOG'
}
})
def test_query_projection_expression_path(test_table):
p = random_string()
items = [{
'p': p,
'c': str(i),
'a': {
'x': str(i * 10),
'y': 'hi'
},
'b': 'hello'
} for i in range(10)]
with test_table.batch_writer() as batch:
for item in items:
batch.put_item(item)
got_items = full_query(test_table,
KeyConditions={
'p': {
'AttributeValueList': [p],
'ComparisonOperator': 'EQ'
}
},
ProjectionExpression="a.x")
expected_items = [{'a': {'x': x['a']['x']}} for x in items]
assert multiset(expected_items) == multiset(got_items)
def test_query_filter_between(test_table_sn_with_data):
table, p, items = test_table_sn_with_data
for xn in ['i', 's', 'b']:
xv1 = items[2][xn]
xv2 = items[3][xn]
if xv1 > xv2:
xv1, xv2 = xv2, xv1
got_items = full_query(table,
KeyConditions={
'p': {
'AttributeValueList': [p],
'ComparisonOperator': 'EQ'
}
},
QueryFilter={
xn: {
'AttributeValueList': [xv1, xv2],
'ComparisonOperator': 'BETWEEN'
}
})
expected_items = [
item for item in items if item[xn] >= xv1 and item[xn] <= xv2
]
assert (got_items == expected_items)
