def test_implicitly_accessed_column(self):
self.assert_compiled_select(
'SELECT table1.value FROM (SELECT value + 1 AS foo FROM table1)',
typed_ast.Select([
typed_ast.SelectField(
typed_ast.ColumnRef('table1', 'value', tq_types.INT),
'table1.value')],
typed_ast.Select([
typed_ast.SelectField(
typed_ast.FunctionCall(
runtime.get_binary_op('+'), [
typed_ast.ColumnRef('table1', 'value',
tq_types.INT),
typed_ast.Literal(1, tq_types.INT)
],
tq_types.INT
),
'foo')],
typed_ast.Table('table1', self.table1_type_ctx),
typed_ast.Literal(True, tq_types.BOOL),
None,
None,
self.make_type_context(
[(None, 'foo', tq_types.INT)],
self.make_type_context(
[('table1', 'value', tq_types.INT)]))),
typed_ast.Literal(True, tq_types.BOOL),
None,
None,
self.make_type_context(
[(None, 'table1.value', tq_types.INT)],
self.make_type_context(
[('table1', 'value', tq_types.INT)]
)))
)
def test_subquery_aliases(self):
self.assert_compiled_select(
'SELECT t.value FROM (SELECT value FROM table1) t',
typed_ast.Select([
typed_ast.SelectField(
typed_ast.ColumnRef('t', 'value', tq_types.INT),
't.value')],
typed_ast.Select([
typed_ast.SelectField(
typed_ast.ColumnRef('table1', 'value', tq_types.INT),
'value')],
typed_ast.Table('table1', self.table1_type_ctx),
typed_ast.Literal(True, tq_types.BOOL),
None,
None,
self.make_type_context(
[(None, 'value', tq_types.INT)],
self.make_type_context(
[('t', 'value', tq_types.INT)]))
),
typed_ast.Literal(True, tq_types.BOOL),
None,
None,
self.make_type_context(
[(None, 't.value', tq_types.INT)],
self.make_type_context(
[('t', 'value', tq_types.INT)]))
)
)
def compile_select(self, select):
assert isinstance(select, tq_ast.Select)
table_expr = self.compile_table_expr(select.table_expr)
table_ctx = table_expr.type_ctx
where_expr = self.compile_where_expr(select.where_expr, table_ctx)
select_fields = self.expand_select_fields(select.select_fields,
table_expr)
aliases = self.get_aliases(select_fields)
group_set = self.compile_groups(select.groups, select_fields, aliases,
table_ctx)
compiled_field_dict, aggregate_context = self.compile_group_fields(
select_fields, aliases, group_set, table_ctx)
# Implicit columns can only show up in non-aggregate select fields.
implicit_column_context = self.find_used_column_context(
compiled_field_dict.values())
for alias, select_field in zip(aliases, select_fields):
if group_set is not None and alias not in group_set.alias_groups:
compiled_field_dict[alias] = self.compile_select_field(
select_field.expr, alias, aggregate_context)
# Put the compiled select fields in the proper order.
select_fields = [compiled_field_dict[alias] for alias in aliases]
result_context = type_context.TypeContext.from_table_and_columns(
None,
collections.OrderedDict(
(field.alias, field.expr.type) for field in select_fields),
implicit_column_context=implicit_column_context)
return typed_ast.Select(select_fields, table_expr, where_expr,
group_set, select.limit, result_context)
def test_select_grouped_and_non_grouped_fields(self):
self.assert_compiled_select(
'SELECT value, SUM(value2) FROM table1 GROUP BY value',
typed_ast.Select([
typed_ast.SelectField(
typed_ast.ColumnRef('table1', 'value', tq_types.INT),
'value'),
typed_ast.SelectField(
typed_ast.FunctionCall(
runtime.get_func('sum'),
[typed_ast.ColumnRef('table1', 'value2',
tq_types.INT)],
tq_types.INT),
'f0_')],
typed_ast.Table('table1', self.table1_type_ctx),
typed_ast.Literal(True, tq_types.BOOL),
typed_ast.GroupSet(
alias_groups={'value'},
field_groups=[]
),
None,
self.make_type_context(
[(None, 'value', tq_types.INT),
(None, 'f0_', tq_types.INT)],
self.make_type_context(
[('table1', 'value', tq_types.INT)]))
)
)
def test_record_star(self):
self.assert_compiled_select(
'SELECT r1.* FROM record_table',
typed_ast.Select(
select_fields=[
typed_ast.SelectField(
typed_ast.ColumnRef('record_table', 'r1.i',
tq_types.INT),
'r1.i', None),
typed_ast.SelectField(
typed_ast.ColumnRef('record_table', 'r1.s',
tq_types.STRING),
'r1.s', None),
],
table=typed_ast.Table('record_table',
self.record_table_type_ctx),
where_expr=typed_ast.Literal(True, tq_types.BOOL),
group_set=None,
having_expr=typed_ast.Literal(True, tq_types.BOOL),
orderings=None,
limit=None,
type_ctx=self.make_type_context(
[(None, 'r1.i', tq_types.INT),
(None, 'r1.s', tq_types.STRING)],
self.make_type_context([
('record_table', 'r1.i', tq_types.INT),
('record_table', 'r1.s', tq_types.STRING)]))))
def test_case(self):
self.assert_compiled_select(
'SELECT CASE WHEN TRUE THEN 1 WHEN FALSE THEN 2 END',
typed_ast.Select(
select_fields=[
typed_ast.SelectField(
typed_ast.FunctionCall(
runtime.get_func('if'),
[
typed_ast.Literal(True, tq_types.BOOL),
typed_ast.Literal(1, tq_types.INT),
typed_ast.FunctionCall(
runtime.get_func('if'),
[
typed_ast.Literal(False, tq_types.BOOL),
typed_ast.Literal(2, tq_types.INT),
typed_ast.Literal(None, tq_types.NONETYPE),
],
tq_types.INT)
],
tq_types.INT),
'f0_', None)
],
table=typed_ast.NoTable(),
where_expr=typed_ast.Literal(True, tq_types.BOOL),
group_set=None,
having_expr=typed_ast.Literal(True, tq_types.BOOL),
orderings=None,
limit=None,
type_ctx=self.make_type_context(
[(None, 'f0_', tq_types.INT)],
self.make_type_context([]))))
def test_aggregates(self):
self.assert_compiled_select(
'SELECT MAX(value), MIN(value) FROM table1',
typed_ast.Select([
typed_ast.SelectField(
typed_ast.AggregateFunctionCall(
runtime.get_func('max'),
[typed_ast.ColumnRef('table1', 'value', tq_types.INT)],
tq_types.INT
),
'f0_'),
typed_ast.SelectField(
typed_ast.AggregateFunctionCall(
runtime.get_func('min'),
[typed_ast.ColumnRef('table1', 'value', tq_types.INT)],
tq_types.INT
),
'f1_')],
typed_ast.Table('table1', self.table1_type_ctx),
typed_ast.Literal(True, tq_types.BOOL),
typed_ast.GroupSet(set(), []),
None,
self.make_type_context([
(None, 'f0_', tq_types.INT),
(None, 'f1_', tq_types.INT)],
self.make_type_context([]))))
def test_within_clause(self):
self.assert_compiled_select(
'SELECT r1.s, COUNT(r1.s) WITHIN r1 AS num_s_in_r1 '
'FROM record_table',
typed_ast.Select(
select_fields=[
typed_ast.SelectField(
typed_ast.ColumnRef('record_table', 'r1.s',
tq_types.STRING),
'r1.s', None),
typed_ast.SelectField(typed_ast.FunctionCall(
runtime.get_func('count'),
[typed_ast.ColumnRef('record_table', 'r1.s',
tq_types.STRING)],
tq_types.INT
), 'num_s_in_r1', 'r1')],
table=typed_ast.Table('record_table',
self.record_table_type_ctx),
where_expr=typed_ast.Literal(True, tq_types.BOOL),
group_set=typed_ast.GroupSet(set(), []),
having_expr=typed_ast.Literal(True, tq_types.BOOL),
orderings=None,
limit=None,
type_ctx=self.make_type_context(
[(None, 'r1.s', tq_types.STRING),
(None, 'num_s_in_r1', tq_types.INT)],
self.make_type_context([]))))
def test_subquery(self):
self.assert_compiled_select(
'SELECT foo, foo + 1 FROM (SELECT value + 1 AS foo FROM table1)',
typed_ast.Select([
typed_ast.SelectField(
typed_ast.ColumnRef(None, 'foo', tq_types.INT), 'foo',
None),
typed_ast.SelectField(
typed_ast.FunctionCall(
runtime.get_binary_op('+'), [
typed_ast.ColumnRef(None, 'foo', tq_types.INT),
typed_ast.Literal(1, tq_types.INT)],
tq_types.INT),
'f0_', None
)],
typed_ast.Select(
[typed_ast.SelectField(
typed_ast.FunctionCall(
runtime.get_binary_op('+'), [
typed_ast.ColumnRef('table1', 'value',
tq_types.INT),
typed_ast.Literal(1, tq_types.INT)],
tq_types.INT),
'foo', None
)],
typed_ast.Table('table1', self.table1_type_ctx),
typed_ast.Literal(True, tq_types.BOOL),
None,
typed_ast.Literal(True, tq_types.BOOL),
None,
None,
self.make_type_context(
[(None, 'foo', tq_types.INT)],
self.make_type_context(
[('table1', 'value', tq_types.INT)]
))
),
typed_ast.Literal(True, tq_types.BOOL),
None,
typed_ast.Literal(True, tq_types.BOOL),
None,
None,
self.make_type_context(
[(None, 'foo', tq_types.INT), (None, 'f0_', tq_types.INT)],
self.make_type_context([(None, 'foo', tq_types.INT)]))
)
)
def test_multi_way_join(self):
self.assert_compiled_select(
'SELECT 0 '
'FROM table1 t1 JOIN table2 t2 ON t1.value = t2.value '
'LEFT JOIN table3 t3 ON t2.value3 = t3.value',
typed_ast.Select(
select_fields=[
typed_ast.SelectField(
typed_ast.Literal(0, tq_types.INT), 'f0_', None)],
table=typed_ast.Join(
base=typed_ast.Table('table1',
self.make_type_context([
('t1', 'value', tq_types.INT),
('t1', 'value2', tq_types.INT),
])),
tables=[
(typed_ast.Table(
'table2',
self.make_type_context([
('t2', 'value', tq_types.INT),
('t2', 'value3', tq_types.INT),
])),
tq_ast.JoinType.INNER),
(typed_ast.Table(
'table3',
self.make_type_context([
('t3', 'value', tq_types.INT)
])),
tq_ast.JoinType.LEFT_OUTER
)],
conditions=[
[typed_ast.JoinFields(
typed_ast.ColumnRef('t1', 'value', tq_types.INT),
typed_ast.ColumnRef('t2', 'value', tq_types.INT)
)], [typed_ast.JoinFields(
typed_ast.ColumnRef('t2', 'value3', tq_types.INT),
typed_ast.ColumnRef('t3', 'value', tq_types.INT)
)]],
type_ctx=self.make_type_context([
('t1', 'value', tq_types.INT),
('t1', 'value2', tq_types.INT),
('t2', 'value', tq_types.INT),
('t2', 'value3', tq_types.INT),
('t3', 'value', tq_types.INT),
])
),
where_expr=typed_ast.Literal(True, tq_types.BOOL),
group_set=None,
having_expr=typed_ast.Literal(True, tq_types.BOOL),
orderings=None,
limit=None,
type_ctx=self.make_type_context(
[(None, 'f0_', tq_types.INT)],
self.make_type_context([]))
)
)
def compile_select(self, select):
assert isinstance(select, tq_ast.Select)
table_expr = self.compile_table_expr(select.table_expr)
table_ctx = table_expr.type_ctx
where_expr = self.compile_filter_expr(select.where_expr, table_ctx)
select_fields = self.expand_select_fields(select.select_fields,
table_expr)
aliases = self.get_aliases(select_fields)
within_clauses = self.get_within_clauses(select_fields)
group_set = self.compile_groups(select.groups, select_fields, aliases,
table_ctx)
compiled_field_dict, aggregate_context = self.compile_group_fields(
select_fields, aliases, within_clauses, group_set, table_ctx)
is_scoped_aggregation = any(clause is not None
for clause in within_clauses)
# Implicit columns can only show up in non-aggregate select fields.
implicit_column_context = self.find_used_column_context(
compiled_field_dict.values())
for alias, within_clause, select_field in zip(aliases, within_clauses,
select_fields):
if group_set is not None and alias not in group_set.alias_groups:
if is_scoped_aggregation is False:
compiled_field_dict[alias] = self.compile_select_field(
select_field.expr, alias, within_clause,
aggregate_context)
else:
aggregate_context_not_within = (
aggregate_context.aggregate_context)
if select_field.within_record is not None:
compiled_field_dict[alias] = self.compile_select_field(
select_field.expr, alias, within_clause,
aggregate_context)
else:
compiled_field_dict[alias] = self.compile_select_field(
select_field.expr, alias, within_clause,
aggregate_context_not_within)
# Put the compiled select fields in the proper order.
select_fields = [compiled_field_dict[alias] for alias in aliases]
result_context = type_context.TypeContext.from_table_and_columns(
None,
collections.OrderedDict(
(field.alias, field.expr.type) for field in select_fields),
implicit_column_context=implicit_column_context)
having_expr = self.compile_filter_expr(select.having_expr,
result_context)
return typed_ast.Select(select_fields, table_expr, where_expr,
group_set, having_expr, select.orderings,
select.limit, result_context)
def test_multiple_select(self):
self.assert_compiled_select(
'SELECT value * 3 AS foo, value, value + 1, value bar, value - 1 '
'FROM table1',
typed_ast.Select(
[typed_ast.SelectField(
typed_ast.FunctionCall(
runtime.get_binary_op('*'),
[typed_ast.ColumnRef('table1', 'value', tq_types.INT),
typed_ast.Literal(3, tq_types.INT)],
tq_types.INT),
'foo', None),
typed_ast.SelectField(
typed_ast.ColumnRef('table1', 'value', tq_types.INT),
'value', None),
typed_ast.SelectField(
typed_ast.FunctionCall(
runtime.get_binary_op('+'),
[typed_ast.ColumnRef('table1', 'value', tq_types.INT),
typed_ast.Literal(1, tq_types.INT)],
tq_types.INT),
'f0_', None),
typed_ast.SelectField(
typed_ast.ColumnRef('table1', 'value', tq_types.INT),
'bar', None),
typed_ast.SelectField(
typed_ast.FunctionCall(
runtime.get_binary_op('-'),
[typed_ast.ColumnRef('table1', 'value', tq_types.INT),
typed_ast.Literal(1, tq_types.INT)],
tq_types.INT),
'f1_', None)],
typed_ast.Table('table1', self.table1_type_ctx),
typed_ast.Literal(True, tq_types.BOOL),
None,
typed_ast.Literal(True, tq_types.BOOL),
None,
None,
self.make_type_context([
(None, 'foo', tq_types.INT),
(None, 'value', tq_types.INT),
(None, 'f0_', tq_types.INT), (None, 'bar', tq_types.INT),
(None, 'f1_', tq_types.INT)],
self.make_type_context(
[('table1', 'value', tq_types.INT)]
))
)
)
def test_order_by_field(self):
self.assert_compiled_select(
'SELECT value FROM table1 ORDER BY value2 DESC',
typed_ast.Select(
select_fields=[typed_ast.SelectField(
typed_ast.ColumnRef('table1', 'value', tq_types.INT),
'value', None)],
table=typed_ast.Table('table1', self.table1_type_ctx),
where_expr=typed_ast.Literal(True, tq_types.BOOL),
group_set=None,
having_expr=typed_ast.Literal(True, tq_types.BOOL),
orderings=[tq_ast.Ordering(tq_ast.ColumnId('value2'), False)],
limit=None,
type_ctx=self.make_type_context(
[(None, 'value', tq_types.INT)],
self.make_type_context([('table1', 'value',
tq_types.INT)]))
))
def test_compile_simple_select(self):
self.assert_compiled_select(
'SELECT value FROM table1',
typed_ast.Select(
[typed_ast.SelectField(
typed_ast.ColumnRef('table1', 'value', tq_types.INT),
'value', None)],
typed_ast.Table('table1', self.table1_type_ctx),
typed_ast.Literal(True, tq_types.BOOL),
None,
typed_ast.Literal(True, tq_types.BOOL),
None,
None,
self.make_type_context(
[(None, 'value', tq_types.INT)],
self.make_type_context([('table1', 'value', tq_types.INT)])
))
)
def test_group_by_alias(self):
self.assert_compiled_select(
'SELECT 0 AS foo FROM table1 GROUP BY foo',
typed_ast.Select(
[typed_ast.SelectField(
typed_ast.Literal(0, tq_types.INT), 'foo')],
typed_ast.Table('table1', self.table1_type_ctx),
typed_ast.Literal(True, tq_types.BOOL),
typed_ast.GroupSet(
alias_groups={'foo'},
field_groups=[]
),
None,
self.make_type_context(
[(None, 'foo', tq_types.INT)],
self.make_type_context([]))
)
)
def test_simple_join(self):
self.assert_compiled_select(
'SELECT value2 '
'FROM table1 t1 JOIN table2 t2 ON t1.value = t2.value',
typed_ast.Select([
typed_ast.SelectField(
typed_ast.ColumnRef('t1', 'value2', tq_types.INT),
'value2', None
)],
typed_ast.Join(
typed_ast.Table('table1',
self.make_type_context([
('t1', 'value', tq_types.INT),
('t1', 'value2', tq_types.INT),
])),
[(typed_ast.Table('table2',
self.make_type_context([
('t2', 'value', tq_types.INT),
('t2', 'value3', tq_types.INT),
])),
tq_ast.JoinType.INNER)],
[[typed_ast.JoinFields(
typed_ast.ColumnRef('t1', 'value', tq_types.INT),
typed_ast.ColumnRef('t2', 'value', tq_types.INT)
)]],
self.make_type_context([
('t1', 'value', tq_types.INT),
('t1', 'value2', tq_types.INT),
('t2', 'value', tq_types.INT),
('t2', 'value3', tq_types.INT),
])
),
typed_ast.Literal(True, tq_types.BOOL),
None,
typed_ast.Literal(True, tq_types.BOOL),
None,
None,
self.make_type_context(
[(None, 'value2', tq_types.INT)],
self.make_type_context([('t1', 'value2', tq_types.INT)])
)
)
)
def test_join_multiple_fields(self):
self.assert_compiled_select(
'SELECT 0 '
'FROM table1 t1 JOIN table2 t2 '
'ON t1.value = t2.value AND t2.value3 = t1.value2',
typed_ast.Select(
[typed_ast.SelectField(
typed_ast.Literal(0, tq_types.INT), 'f0_')],
typed_ast.Join(
typed_ast.Table('table1',
self.make_type_context([
('t1', 'value', tq_types.INT),
('t1', 'value2', tq_types.INT),
])),
typed_ast.Table('table2',
self.make_type_context([
('t2', 'value', tq_types.INT),
('t2', 'value3', tq_types.INT),
])),
[typed_ast.JoinFields(
typed_ast.ColumnRef('t1', 'value', tq_types.INT),
typed_ast.ColumnRef('t2', 'value', tq_types.INT)
), typed_ast.JoinFields(
typed_ast.ColumnRef('t1', 'value2', tq_types.INT),
typed_ast.ColumnRef('t2', 'value3', tq_types.INT)
)],
False,
self.make_type_context([
('t1', 'value', tq_types.INT),
('t1', 'value2', tq_types.INT),
('t2', 'value', tq_types.INT),
('t2', 'value3', tq_types.INT),
])
),
typed_ast.Literal(True, tq_types.BOOL),
None,
None,
self.make_type_context(
[(None, 'f0_', tq_types.INT)],
self.make_type_context([]))
)
)
def test_unary_operator(self):
self.assert_compiled_select(
'SELECT -5',
typed_ast.Select(
[typed_ast.SelectField(
typed_ast.FunctionCall(
runtime.get_unary_op('-'),
[typed_ast.Literal(5, tq_types.INT)],
tq_types.INT),
'f0_'
)],
typed_ast.NoTable(),
typed_ast.Literal(True, tq_types.BOOL),
None,
None,
self.make_type_context(
[(None, 'f0_', tq_types.INT)],
self.make_type_context([]))
)
)
def test_function_calls(self):
self.assert_compiled_select(
'SELECT ABS(-3), POW(2, 3), NOW()',
typed_ast.Select([
typed_ast.SelectField(
typed_ast.FunctionCall(
runtime.get_func('abs'),
[typed_ast.FunctionCall(
runtime.get_unary_op('-'),
[typed_ast.Literal(3, tq_types.INT)],
tq_types.INT
)],
tq_types.INT),
'f0_', None),
typed_ast.SelectField(
typed_ast.FunctionCall(
runtime.get_func('pow'), [
typed_ast.Literal(2, tq_types.INT),
typed_ast.Literal(3, tq_types.INT)],
tq_types.INT
),
'f1_', None
),
typed_ast.SelectField(
typed_ast.FunctionCall(
runtime.get_func('now'), [], tq_types.INT
),
'f2_', None
)],
typed_ast.NoTable(),
typed_ast.Literal(True, tq_types.BOOL),
None,
typed_ast.Literal(True, tq_types.BOOL),
None,
None,
self.make_type_context([
(None, 'f0_', tq_types.INT), (None, 'f1_', tq_types.INT),
(None, 'f2_', tq_types.INT)],
self.make_type_context([]))
)
)
def test_where(self):
self.assert_compiled_select(
'SELECT value FROM table1 WHERE value > 3',
typed_ast.Select(
[typed_ast.SelectField(
typed_ast.ColumnRef('table1', 'value', tq_types.INT),
'value')],
typed_ast.Table('table1', self.table1_type_ctx),
typed_ast.FunctionCall(
runtime.get_binary_op('>'),
[typed_ast.ColumnRef('table1', 'value', tq_types.INT),
typed_ast.Literal(3, tq_types.INT)],
tq_types.BOOL),
None,
None,
self.make_type_context(
[(None, 'value', tq_types.INT)],
self.make_type_context(
[('table1', 'value', tq_types.INT)]))
)
)
def test_select_multiple_tables(self):
# Union of columns should be taken, with no aliases.
unioned_type_ctx = self.make_type_context(
[(None, 'value', tq_types.INT), (None, 'value2', tq_types.INT),
(None, 'value3', tq_types.INT)])
self.assert_compiled_select(
'SELECT value, value2, value3 FROM table1, table2',
typed_ast.Select([
typed_ast.SelectField(
typed_ast.ColumnRef(None, 'value', tq_types.INT),
'value', None),
typed_ast.SelectField(
typed_ast.ColumnRef(None, 'value2', tq_types.INT),
'value2', None),
typed_ast.SelectField(
typed_ast.ColumnRef(None, 'value3', tq_types.INT),
'value3', None)],
typed_ast.TableUnion([
typed_ast.Table('table1', self.table1_type_ctx),
typed_ast.Table('table2', self.table2_type_ctx)],
unioned_type_ctx
),
typed_ast.Literal(True, tq_types.BOOL),
None,
typed_ast.Literal(True, tq_types.BOOL),
None,
None,
self.make_type_context(
[(None, 'value', tq_types.INT),
(None, 'value2', tq_types.INT),
(None, 'value3', tq_types.INT)],
self.make_type_context(
[(None, 'value', tq_types.INT),
(None, 'value2', tq_types.INT),
(None, 'value3', tq_types.INT)]))
)
)
