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)