def test_cross_join_with_subquery():
query = """
select K.a,K.b
from (
select H.b
from (
select G.c
from (
select F.d
from (
select E.e
from A, B, C, D, E
), F
), G
), H
), I, J, K
"""
expectation = {
"K": {"a", "b"},
"H": {"b"},
"G": {"c"},
"F": {"d"},
"E": {"e"}
}
assert SQLQuery(query).get_targets() == expectation
def test_inner_join_with_subquery_alias():
query = """
select AB.b from (select A.a, B.b from A, B) AS AB join C on AB.a = C.a
"""
with raises(Exception,
match="Alias of complex tables is not supported yet."):
SQLQuery(query)
def test_in_operator():
query = """
SELECT student.Id
FROM student
WHERE student.Ethnicity IN ("Asian", "Hispanic")
"""
expectation = {"student": {"Id", "Ethnicity"}}
assert SQLQuery(query).get_targets() == expectation
def test_std_functions_modifier():
query = """
SELECT COUNT(ALL exam.CourseId)
FROM student JOIN exam
ON student.Id = exam.StudentId
"""
expectation = {"student": {"Id"}, "exam": {"StudentId", "CourseId"}}
assert SQLQuery(query).get_targets() == expectation
def test_inner_join():
query = """
SELECT student.Sex, exam.Grade
FROM student INNER JOIN exam
ON student.Id = exam.StudentId
"""
expectation = {"student": {"Sex", "Id"}, "exam": {"StudentId", "Grade"}}
assert SQLQuery(query).get_targets() == expectation
def test_group_by():
query = """
SELECT AVG(exam.Grade)
FROM exam
GROUP BY exam.CourseId, exam.Date
"""
expectation = {"exam": {"CourseId", "Date", "Grade"}}
assert SQLQuery(query).get_targets() == expectation
def test_cross_join_alias():
query = """
SELECT SE.Id, SE.Date, SE.CourseId, SE.StudentId
FROM (student, exam) SE
"""
with raises(Exception,
match="Alias of complex tables is not supported yet."):
SQLQuery(query)
def test_is_operator():
query = """
SELECT student.Id
FROM student
WHERE student.Ethnicity IS NOT NULL
"""
expectation = {"student": {"Id", "Ethnicity"}}
assert SQLQuery(query).get_targets() == expectation
def test_order_by():
query = """
SELECT student.Id
FROM student
ORDER BY student.BirthDate DESC NULLS LAST,
student.Income ASC NULLS FIRST
"""
expectation = {"student": {"Id", "BirthDate", "Income"}}
assert SQLQuery(query).get_targets() == expectation
def test_group_by_having():
query = """
SELECT AVG(exam.Grade)
FROM exam
GROUP BY exam.CourseId, exam.Date
HAVING COUNT(exam.StudentId) > 100
"""
expectation = {"exam": {"StudentId", "CourseId", "Date", "Grade"}}
assert SQLQuery(query).get_targets() == expectation
def test_inner_join_with_subquery():
query = """
select A.a
from (select A.a from A)
join B on A.a = B.a
join (select C.c from C) on B.c = C.c
"""
expectation = {"A": {"a"}, "B": {"a", "c"}, "C": {"c"}}
assert SQLQuery(query).get_targets() == expectation
def test_inner_join_alias():
query = """
SELECT SE.Sex, SE.Grade
FROM (student INNER JOIN exam
ON student.Id = exam.StudentId) SE
"""
with raises(Exception,
match="Alias of complex tables is not supported yet."):
SQLQuery(query)
def test_select_with_subquery():
query = """
SELECT student.Id, (SELECT MAX(exam.Grade)
FROM exam
WHERE exam.StudentId = student.Id)
FROM student
"""
expectation = {"student": {"Id"}, "exam": {"StudentId", "Grade"}}
assert SQLQuery(query).get_targets() == expectation
def test_aliases_everywhere():
query = """
select C.c AS CC
from (select A_T.a AS AA, B_T.b AS BB from A AS A_T, B AS B_T)
join C on A.a = C.c
where EXISTS(select D_T.d AS DD, E_T.e AS BB from D AS D_T, E AS E_T)
"""
expectation = {"A": {"a"}, "B": {"b"}, "C": {"c"}, "D": {"d"}, "E": {"e"}}
assert SQLQuery(query).get_targets() == expectation
def test_table_alias():
query = """
SELECT S.Id, E.Date, E.CourseId, E.StudentId
FROM student S, exam E
"""
expectation = {
"student": {"Id"},
"exam": {"Date", "CourseId", "StudentId"}
}
assert SQLQuery(query).get_targets() == expectation
def test_cross_join():
query = """
SELECT student.Id, exam.Date, exam.CourseId, exam.StudentId
FROM student, exam
"""
expectation = {
"student": {"Id"},
"exam": {"Date", "CourseId", "StudentId"}
}
assert SQLQuery(query).get_targets() == expectation
def test_order_by_case():
query = """
SELECT student.Id
FROM student
ORDER BY (CASE
WHEN student.BirthDate IS NULL THEN student.Id
ELSE student.BirthDate
END) DESC NULLS LAST, student.Income ASC NULLS FIRST
"""
expectation = {"student": {"Id", "BirthDate", "Income"}}
assert SQLQuery(query).get_targets() == expectation
def test_std_functions():
query = """
SELECT COUNT(exam.CourseId)
FROM student JOIN exam
ON student.Id = exam.StudentId
WHERE student.Id = "12345678" AND DATEDIFF(NOW(), exam.Date) < 365
"""
expectation = {
"student": {"Id"},
"exam": {"StudentId", "CourseId", "Date"}
}
assert SQLQuery(query).get_targets() == expectation
def test_where():
query = """
SELECT student.Id, exam.CourseId
FROM student JOIN exam
ON student.Id = exam.StudentId
WHERE exam.Grade > 27
"""
expectation = {
"student": {"Id"},
"exam": {"StudentId", "CourseId", "Grade"}
}
assert SQLQuery(query).get_targets() == expectation
def test_case_when():
query = """
SELECT student.Id,
CASE
WHEN student.Income < 1000 THEN 'Nothing'
WHEN student.Income < 1500 THEN 'Some'
ELSE 'Maximum'
END
FROM student
"""
expectation = {"student": {"Id", "Income"}}
assert SQLQuery(query).get_targets() == expectation
def test_case():
query = """
SELECT student.Id,
CASE student.Ethnicity
WHEN 'Asian' THEN 'Asia'
WHEN 'Hispanic' THEN 'Latin America'
ELSE 'Somewhere'
END
FROM student
"""
expectation = {"student": {"Id", "Ethnicity"}}
assert SQLQuery(query).get_targets() == expectation
def test_where_with_subquery():
query = """
SELECT exam.CourseId, student.Id
FROM student JOIN exam as outer_exam
ON student.Id = exam.StudentId
WHERE exam.Grade = (SELECT MAX(exam.Grade)
FROM exam
WHERE exam.CourseId = outer_exam.CourseId)
"""
expectation = {
"student": {"Id"},
"exam": {"StudentId", "CourseId", "Grade"}
}
assert SQLQuery(query).get_targets() == expectation
def test_mosaicrown_functions():
query = """
SELECT TOKENIZE(student.Id),
L_DIVERSITY(student.Sex, student.Ethnicity, exam.Grade)
FROM student JOIN exam
ON student.Id = exam.StudentId
WHERE exam.CourseId = "DB" AND DATEDIFF(NOW(), exam.Date) < 365
"""
expectation = {
"student":
{"Id", "Id/tokenize", "Sex/l_diversity", "Ethnicity/l_diversity"},
"exam": {"StudentId", "CourseId", "Date", "Grade/l_diversity"}
}
assert SQLQuery(query).get_targets() == expectation
def test_operations():
query = """
SELECT "CF:"||professor.Cf,
1.5*(professor.Salary+100)-(3)*student.Income
FROM student
JOIN exam ON student.Id = exam.StudentId
JOIN course ON exam.CourseId = course.Cid
JOIN professor ON course.ProfCf = professor.Cf
WHERE student.Id = "12345678"
"""
expectation = {
"student": {"Id", "Income"},
"exam": {"StudentId", "CourseId"},
"course": {"Cid", "ProfCf"},
"professor": {"Cf", "Salary"}
}
assert SQLQuery(query).get_targets() == expectation
def test_multiple_inner_join():
query = """
select A.a
from ((A join B on A.id = B.id) join C on A.id = C.id)
join (D join (E join F on E.id = F.id) on D.id = E.id)
on A.id = F.id
join G on A.id = G.id
"""
expectation = {
"A": {"id", "a"},
"B": {"id"},
"C": {"id"},
"D": {"id"},
"E": {"id"},
"F": {"id"},
"G": {"id"}
}
assert SQLQuery(query).get_targets() == expectation
def test_select_with_alias():
query = """
SELECT student.Id BadgeNumber,
12.546 AS F,
"something" AS S,
'' AS EMP, NULL AS N,
student.Income / 12 AS Monthly,
YEAR(student.BirthDate) AS YearOfBirth,
CASE student.Sex
WHEN 'M' THEN 'Male'
WHEN 'F' < 1500 THEN 'Female'
ELSE 'Other'
END AS FullSex,
(SELECT MAX(exam.Grade)
FROM exam
WHERE exam.StudentId = student.Id) AS MaxGrade
FROM student
"""
expectation = {
"student": {"Id", "Income", "BirthDate", "Sex"},
"exam": {"StudentId", "Grade"}
}
assert SQLQuery(query).get_targets() == expectation
def test_where_with_alias():
query = """
SELECT student.Id BadgeNumber,
student.Income / 12 AS Monthly,
YEAR(student.BirthDate) AS YearOfBirth,
CASE student.Sex
WHEN 'M' THEN 'Male'
WHEN 'F' < 1500 THEN 'Female'
ELSE 'Other'
END AS FullSex,
(SELECT MAX(exam.Grade)
FROM exam
WHERE exam.StudentId = student.Id) AS MaxGrade
FROM student
WHERE BadgeNumber > "12345678" AND Monthly > 1000 AND
YearOfBirth < 20 AND FullSex IN ('Female', 'Other') AND
MaxGrade = 30
"""
expectation = {
"student": {"Id", "Income", "BirthDate", "Sex"},
"exam": {"StudentId", "Grade"}
}
assert SQLQuery(query).get_targets() == expectation
def test_select_modifier():
query = """
SELECT DISTINCT student.Ethnicity
FROM student
"""
assert SQLQuery(query).get_targets() == {"student": {"Ethnicity"}}
def test_select():
query = """
SELECT student.Id, student.Sex
FROM student
"""
assert SQLQuery(query).get_targets() == {"student": {"Id", "Sex"}}
def test_useless_table():
query = """
SELECT student.Id
FROM student, exam
"""
assert SQLQuery(query).get_targets() == {"student": {"Id"}}