def test_count_star(self):
self.assert_parsed_select(
'SELECT COUNT(*), COUNT(((*))) FROM table',
tq_ast.Select([
tq_ast.SelectField(
tq_ast.FunctionCall('count', [tq_ast.Literal(1)]), None,
None),
tq_ast.SelectField(
tq_ast.FunctionCall('count', [tq_ast.Literal(1)]), None,
None)
], tq_ast.TableId('table', None), None, None, None, None, None,
None))
def test_function_calls(self):
self.assert_parsed_select(
'SELECT ABS(-3), POW(2, 3), NOW()',
tq_ast.Select([
tq_ast.SelectField(
tq_ast.FunctionCall(
'abs', [tq_ast.UnaryOperator('-', literal(3))]), None,
None),
tq_ast.SelectField(
tq_ast.FunctionCall('pow',
[literal(2), literal(3)]), None, None),
tq_ast.SelectField(tq_ast.FunctionCall('now', []), None, None)
], None, None, None, None, None, None, None))
def p_expression_func_call(p):
"""expression : ID LPAREN arg_list RPAREN
| LEFT LPAREN arg_list RPAREN
"""
# Note: we have to special-case LEFT, since it's both a keyword appearing
# in LEFT JOIN, as well as a function.
p[0] = tq_ast.FunctionCall(p[1].lower(), p[3])
def test_limit(self):
self.assert_parsed_select(
'SELECT SUM(foo) FROM bar GROUP BY baz LIMIT 10',
tq_ast.Select([
tq_ast.SelectField(
tq_ast.FunctionCall('sum', [tq_ast.ColumnId('foo')]), None,
None)
], tq_ast.TableId('bar', None), None, [tq_ast.ColumnId('baz')],
None, None, 10, 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,
None)
], tq_ast.TableId('bar', None), None, None, None, None, None,
None))
def compile_helper(remaining_clauses):
if len(remaining_clauses) == 0:
return tq_ast.Literal(value=None)
clause = remaining_clauses[0]
return tq_ast.FunctionCall(
name='if',
args=[
clause.condition, clause.result_expr,
compile_helper(remaining_clauses[1:])
])
def test_within_clause(self):
self.assert_parsed_select(
'SELECT fullname, COUNT(citiesLived.yearsLived) WITHIN '
'citiesLived AS numberOfTimesInEachCity FROM table',
tq_ast.Select([
tq_ast.SelectField(tq_ast.ColumnId('fullname'), None, None),
tq_ast.SelectField(
tq_ast.FunctionCall(
'count', [tq_ast.ColumnId('citiesLived.yearsLived')]),
'numberOfTimesInEachCity', 'citiesLived')
], tq_ast.TableId('table', None), None, None, None, None, None,
None))
def test_within_record(self):
self.assert_parsed_select(
'SELECT fullname, COUNT(children.name) WITHIN RECORD AS '
'numberOfChildren FROM table',
tq_ast.Select([
tq_ast.SelectField(tq_ast.ColumnId('fullname'), None, None),
tq_ast.SelectField(
tq_ast.FunctionCall('count',
[tq_ast.ColumnId('children.name')]),
'numberOfChildren', 'RECORD')
], tq_ast.TableId('table', None), None, None, None, None, None,
None))
def test_redundant_commas_allowed(self):
# In most cases, a comma at the end of a comma-separated list is OK.
self.assert_parsed_select(
'SELECT foo IN (1, 2, 3,), bar, FROM table1, table2, '
'GROUP BY col1, col2,',
tq_ast.Select([
tq_ast.SelectField(
tq_ast.FunctionCall('in', [
tq_ast.ColumnId('foo'),
tq_ast.Literal(1),
tq_ast.Literal(2),
tq_ast.Literal(3)
]), None, None),
tq_ast.SelectField(tq_ast.ColumnId('bar'), None, None)
],
tq_ast.TableUnion([
tq_ast.TableId('table1', None),
tq_ast.TableId('table2', None)
]), None,
[tq_ast.ColumnId('col1'),
tq_ast.ColumnId('col2')], None, None, None, None))
def p_expression_in(p):
"""expression : expression IN LPAREN constant_list RPAREN"""
p[0] = tq_ast.FunctionCall('in', [p[1]] + p[4])
def p_expression_count_star(p):
"""expression : COUNT LPAREN parenthesized_star RPAREN"""
# Treat COUNT(*) as COUNT(1).
p[0] = tq_ast.FunctionCall('count', [tq_ast.Literal(1)])
def p_expression_count_distinct(p):
"""expression : COUNT LPAREN DISTINCT arg_list RPAREN"""
p[0] = tq_ast.FunctionCall('count_distinct', p[4])
def p_expression_count(p):
"""expression : COUNT LPAREN arg_list RPAREN"""
p[0] = tq_ast.FunctionCall('count', p[3])