def test_multiple_table_select(self):
self.assert_parsed_select(
'SELECT foo FROM table1, table2',
tq_ast.Select(
[tq_ast.SelectField(tq_ast.ColumnId('foo'), None)],
tq_ast.TableUnion([
tq_ast.TableId('table1', None),
tq_ast.TableId('table2', None),
]),
None,
None,
None,
None,
None
)
)
def test_aggregates(self):
self.assert_parsed_select(
'SELECT MAX(foo) FROM bar',
tq_ast.Select(
[tq_ast.SelectField(
tq_ast.FunctionCall('max', [tq_ast.ColumnId('foo')]),
None
)],
tq_ast.TableId('bar', None),
None,
None,
None,
None,
None
)
)
def test_order_by(self):
self.assert_parsed_select(
'SELECT foo, bar, baz FROM table ORDER BY foo DESC, bar, baz ASC,',
tq_ast.Select([
tq_ast.SelectField(tq_ast.ColumnId('foo'), None),
tq_ast.SelectField(tq_ast.ColumnId('bar'), None),
tq_ast.SelectField(tq_ast.ColumnId('baz'), None)
], tq_ast.TableId('table', None), None, None, [
tq_ast.Ordering(tq_ast.ColumnId('foo'), False),
tq_ast.Ordering(tq_ast.ColumnId('bar'), True),
tq_ast.Ordering(tq_ast.ColumnId('baz'), True)
], None, None))
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_single_clause_case(self):
self.assert_parsed_select(
'SELECT CASE WHEN x = 4 THEN 16 END',
tq_ast.Select(
[
tq_ast.SelectField(
tq_ast.CaseExpression([
tq_ast.CaseClause(
tq_ast.BinaryOperator('=',
tq_ast.ColumnId('x'),
tq_ast.Literal(4)),
tq_ast.Literal(16)
),
]),
None
)
],
None, None, None, None, None, None
)
)
def expand_select_fields(self, select_fields, table_expr):
"""Expand any stars into a list of all context columns.
Arguments:
select_fields: A list of uncompiled select fields, some of which
can be tq_ast.Star.
table_expr: The compiled table expression to reference, if
necessary.
"""
table_ctx = table_expr.type_ctx
star_select_fields = []
for table_name, col_name in table_ctx.columns.iterkeys():
if table_name is not None:
col_ref = table_name + '.' + col_name
else:
col_ref = col_name
# Joins are special: the aliases default to a fully-qualified name.
if isinstance(table_expr, typed_ast.Join):
alias = table_name + '.' + col_name
else:
alias = col_name
star_select_fields.append(
tq_ast.SelectField(tq_ast.ColumnId(col_ref), alias, None))
result_fields = []
for field in select_fields:
if isinstance(field, tq_ast.Star):
result_fields.extend(star_select_fields)
elif (field.expr and isinstance(field.expr, tq_ast.ColumnId)
and field.expr.name.endswith('.*')):
prefix = field.expr.name[:-len('.*')]
record_star_fields = filter(
lambda f: f.alias.startswith(prefix), star_select_fields)
result_fields.extend(record_star_fields)
else:
result_fields.append(field)
return result_fields
def test_select_from_table(self):
self.assert_parsed_select(
'SELECT foo FROM bar',
tq_ast.Select([tq_ast.SelectField(tq_ast.ColumnId('foo'), None)],
tq_ast.TableId('bar', None), None, None, None, None,
None))
def test_group_each_by(self):
self.assert_parsed_select(
'SELECT 0 FROM table GROUP EACH BY foo',
tq_ast.Select([tq_ast.SelectField(tq_ast.Literal(0), None)],
tq_ast.TableId('table', None), None,
[tq_ast.ColumnId('foo')], None, None, None))
def test_dot_separated_table_name(self):
self.assert_parsed_select(
'SELECT foo FROM dataset.table',
tq_ast.Select([tq_ast.SelectField(tq_ast.ColumnId('foo'), None)],
tq_ast.TableId('dataset.table', None), None, None,
None, None, None))
def test_fully_qualified_name(self):
self.assert_parsed_select(
'SELECT table.foo FROM table',
tq_ast.Select(
[tq_ast.SelectField(tq_ast.ColumnId('table.foo'), None)],
tq_ast.TableId('table', None), None, None, None, None, None))
def test_group_by(self):
self.assert_parsed_select(
'SELECT foo FROM bar GROUP BY baz',
tq_ast.Select([tq_ast.SelectField(tq_ast.ColumnId('foo'), None)],
tq_ast.TableId('bar', None), None,
[tq_ast.ColumnId('baz')], None, None, None))
def p_column_id(p):
"""column_id : id_component_list"""
p[0] = tq_ast.ColumnId(p[1])