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', None) ], 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([ ('t', '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, 't.value', tq_types.INT)], self.make_type_context([ ('t', 'value', tq_types.INT) ]))))
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', 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, 'table1.value', 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_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_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_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) ]))))
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_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', None) ], typed_ast.Table('table1', self.table1_type_ctx), typed_ast.Literal(True, tq_types.BOOL), typed_ast.GroupSet(alias_groups={'foo'}, field_groups=[]), typed_ast.Literal(True, tq_types.BOOL), None, None, self.make_type_context([(None, 'foo', 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_', 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)], self.make_type_context([]))))
def test_having(self): self.assert_compiled_select( 'SELECT value FROM table1 HAVING value > 3', 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.FunctionCall(runtime.get_binary_op('>'), [ typed_ast.ColumnRef(None, '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_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_select_star(self): self.assert_compiled_select( 'SELECT * FROM table1', typed_ast.Select( [ typed_ast.SelectField( typed_ast.ColumnRef('table1', 'value', tq_types.INT), 'value', None), typed_ast.SelectField( typed_ast.ColumnRef('table1', 'value2', tq_types.INT), 'value2', 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), (None, 'value2', tq_types.INT)], self.make_type_context([ ('table1', 'value', tq_types.INT), ('table1', 'value2', tq_types.INT) ]))))
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', None), typed_ast.SelectField( typed_ast.FunctionCall(runtime.get_func('sum'), [ typed_ast.ColumnRef('table1', 'value2', tq_types.INT) ], tq_types.INT), 'f0_', None) ], typed_ast.Table('table1', self.table1_type_ctx), typed_ast.Literal(True, tq_types.BOOL), typed_ast.GroupSet(alias_groups={'value'}, field_groups=[]), typed_ast.Literal(True, tq_types.BOOL), None, 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_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_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_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_', None), typed_ast.SelectField( typed_ast.AggregateFunctionCall( runtime.get_func('min'), [typed_ast.ColumnRef('table1', 'value', tq_types.INT)], tq_types.INT), 'f1_', None) ], typed_ast.Table('table1', self.table1_type_ctx), typed_ast.Literal(True, tq_types.BOOL), typed_ast.GroupSet(set(), []), typed_ast.Literal(True, tq_types.BOOL), None, None, self.make_type_context( [(None, 'f0_', tq_types.INT), (None, 'f1_', tq_types.INT)], self.make_type_context([]))))
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([]))))