def test_table_functions():
ast = query_parser.parse_statement('''
select *
from flatten(docs, 'scripts')
''')
op = Function('flatten', LoadOp('docs'), StringConst('scripts'))
eq_(ast, op)
ast = query_parser.parse_statement('''
select *
from flatten(select * from docs, "scripts")
''')
eq_(ast, op)
def test_table_functions():
ast = query_parser.parse_statement('''
select *
from flatten(docs, 'scripts')
''')
op = Function('flatten', LoadOp('docs'), StringConst('scripts'))
eq_(ast,op)
ast = query_parser.parse_statement('''
select *
from flatten(select * from docs, "scripts")
''')
eq_(ast,op)
def test_table_functions():
# TODO: figure out if this is valid syntax
ast = query_parser.parse_statement('''
select *
from flatten(docs, 'scripts')
''')
op = Function('flatten', LoadOp('docs'), StringConst('scripts'))
eq_(ast, op)
ast = query_parser.parse_statement('''
select *
from flatten( (select * from docs), "scripts")
''')
eq_(ast, op)
def test_left_join():
ast = query_parser.parse_statement('''
SELECT *
FROM company_raw
JOIN (
SELECT MAX(dt) dt FROM company_raw
) max_dt ON company_raw.dt = max_dt.dt
LEFT JOIN geonames.distinct_alternate_country_name countries
ON TRIM(UPPER(company_raw.country)) = TRIM(UPPER(countries.alternate_name))
''')
def test_union_all():
ast = query_parser.parse_statement('''
select field1, field2 from table1
union all
select field1, field2 from table2
''')
compare(
ast,
UnionAllOp(
ProjectionOp(LoadOp('table1'), Var('field1'), Var('field2')),
ProjectionOp(LoadOp('table2'), Var('field1'), Var('field2'))))
def test_union_all():
ast = query_parser.parse_statement('''
select field1, field2 from table1
union all
select field1, field2 from table2
''')
compare(
ast,
UnionAllOp(
ProjectionOp(
LoadOp('table1'),
Var('field1'),
Var('field2')
),
ProjectionOp(
LoadOp('table2'),
Var('field1'),
Var('field2')
)
)
)
def test_parse_statement():
ast = query_parser.parse_statement('select 1')
eq_(
ast,
ProjectionOp(LoadOp(''), NumberConst(1))
)
ast = query_parser.parse_statement('select 1 from employees')
eq_(
ast,
ProjectionOp(LoadOp('employees'), NumberConst(1))
)
ast = query_parser.parse_statement('select full_name from employees')
eq_(
ast,
ProjectionOp(LoadOp('employees'), Var('full_name'))
)
ast = query_parser.parse_statement('''
select full_name from employees as employee
''')
eq_(
ast,
ProjectionOp(
AliasOp('employee',LoadOp('employees')),
Var('full_name')
)
)
ast = query_parser.parse_statement('''
select full_name
from employees as employee, employees as manager
''')
eq_(
ast,
ProjectionOp(
JoinOp(
AliasOp('employee',LoadOp('employees')),
AliasOp('manager',LoadOp('employees'))
),
Var('full_name')
)
)
ast = query_parser.parse_statement('''
select full_name
from employees
where manager_id is not null
''')
eq_(
ast,
ProjectionOp(
SelectionOp(
LoadOp('employees'),
IsNotOp(Var('manager_id'), NullConst())
),
Var('full_name')
)
)
ast = query_parser.parse_statement('''
select full_name
from employees as employee, employees as managers on employee.manager_id = managers.employee_id
''')
ast = query_parser.parse_statement('''
select manager_id, count() from employees
''')
ast = query_parser.parse_statement('''
select manager_id, count() from
employees
group by manager_id
''')
ast = query_parser.parse_statement('''
select manager_id, count() from
employees
group by manager_id
order by count
''')
op = OrderByOp(
GroupByOp(
ProjectionOp(
LoadOp('employees'),
Var('manager_id'),
Function('count')
),
Var('manager_id')
),
Var('count')
)
eq_(
ast,
op
)
ast = query_parser.parse_statement('''
select count() from top_10, top_10
''')
ast = query_parser.parse_statement('''
select t1.* from top_10 as t1
''')
ast = query_parser.parse_statement('''
select manager_id, count() from docs group by manager_id order by count desc limit 10
''')
def test_parse_statement():
ast = query_parser.parse_statement('select 1')
eq_(ast, ProjectionOp(LoadOp(''), NumberConst(1)))
ast = query_parser.parse_statement('select 1 from employees')
eq_(ast, ProjectionOp(LoadOp('employees'), NumberConst(1)))
ast = query_parser.parse_statement('select full_name from employees')
eq_(ast, ProjectionOp(LoadOp('employees'), Var('full_name')))
ast = query_parser.parse_statement('''
select full_name from employees as employee
''')
eq_(
ast,
ProjectionOp(AliasOp('employee', LoadOp('employees')),
Var('full_name')))
ast = query_parser.parse_statement('''
select full_name
from employees as employee, employees as manager
''')
eq_(
ast,
ProjectionOp(
JoinOp(AliasOp('employee', LoadOp('employees')),
AliasOp('manager', LoadOp('employees'))), Var('full_name')))
ast = query_parser.parse_statement('''
select full_name
from employees
where manager_id is not null
''')
eq_(
ast,
ProjectionOp(
SelectionOp(LoadOp('employees'),
IsNotOp(Var('manager_id'), NullConst())),
Var('full_name')))
ast = query_parser.parse_statement('''
select full_name
from employees as employee, employees as managers on employee.manager_id = managers.employee_id
''')
ast = query_parser.parse_statement('''
select manager_id, count() from employees
''')
ast = query_parser.parse_statement('''
select manager_id, count() from
employees
group BY manager_id
''')
ast = query_parser.parse_statement('''
select manager_id, count() from
employees
group by manager_id
order by count
''')
op = OrderByOp(
GroupByOp(
ProjectionOp(LoadOp('employees'), Var('manager_id'),
Function('count')), Var('manager_id')), Var('count'))
eq_(ast, op)
ast = query_parser.parse_statement('''
select count() from top_10, top_10
''')
ast = query_parser.parse_statement('''
select t1.* from top_10 as t1
''')
ast = query_parser.parse_statement('''
select manager_id, count() from docs group by manager_id order by count desc limit 10
''')
