def test_bad_token(self):
with pytest.raises(ValueError) as err:
QueryParser().parse_only("SELECT * FROM FOO WHENCE ZIP ZAG")
err.match("^Bad token")
def runValidate(self):
for qs in self.queries:
q = QueryParser(metadata).query(qs)
self.validateSingle(q)
def test_unsupported(self):
with pytest.raises(ValueError) as err:
qb = QueryParser().query(
"SELECT * FROM FOO UNION ALL SELECT * FROM BAR", True)
def runValidate(self):
for qs in self.queries:
with pytest.raises(ValueError):
q = QueryParser(metadata).query(qs)
self.validateSingle(q)
def test_two_tables(self):
query = 'SELECT "Temperature" AS temp, AVG(Weekly_Sales) AS sales FROM features, sales'
q = QueryParser(metadata).query(query)
assert (q._named_symbols['temp'].expression.tablename == 'features')
assert (q._named_symbols['sales'].expression.xpath_first(
'//TableColumn').tablename == 'sales')
def runParse(self):
for query in self.queries:
try:
QueryParser().parse_only(query)
except Exception as e:
raise ValueError(f"Parse error for {str(query)}: {str(e)}")
def test_truncate_parse(self):
frag = 'TRUNCATE(15 * 5, -1)'
expr = QueryParser().parse_expression(frag)
assert(frag.replace(' ', '') == str(expr).replace(' ', ''))
v = expr.evaluate({})
assert(v == 70)
def test_extract_hours(self):
frag = "EXTRACT(HOUR FROM '09:15:07')"
expr = QueryParser().parse_expression(frag)
assert(frag.replace(' ', '') == str(expr).replace(' ', ''))
v = expr.evaluate({})
assert(v == 9)
def test_extract_weekday(self):
frag = "EXTRACT(WEEKDAY FROM '2017-05-20')"
expr = QueryParser().parse_expression(frag)
assert(frag.replace(' ', '') == str(expr).replace(' ', ''))
v = expr.evaluate({})
assert(v == 5)
def test_extract_year(self):
frag = "EXTRACT(YEAR FROM CURRENT_DATE)"
expr = QueryParser().parse_expression(frag)
assert(frag.replace(' ', '') == str(expr).replace(' ', ''))
v = expr.evaluate({})
assert(v > 2020)
def test_cur_date(self):
frag = "CURRENT_DATE"
expr = QueryParser().parse_expression(frag)
assert(frag.replace(' ', '') == str(expr).replace(' ', ''))
v = expr.evaluate({})
assert(isinstance(v, datetime.date))
def test_extract_second(self):
frag = "EXTRACT(SECOND FROM '2017-05-20 09:15:07')"
expr = QueryParser().parse_expression(frag)
assert(frag.replace(' ', '') == str(expr).replace(' ', ''))
v = expr.evaluate({})
assert(v == 7)
def test_extract_null(self):
frag = "EXTRACT(DAY FROM '09:15:07')"
expr = QueryParser().parse_expression(frag)
assert(frag.replace(' ', '') == str(expr).replace(' ', ''))
v = expr.evaluate({})
assert(v is None)
def test_day_name(self):
frag = "DAYNAME('2017-05-20 09:15:07')"
expr = QueryParser().parse_expression(frag)
assert(frag.replace(' ', '') == str(expr).replace(' ', ''))
v = expr.evaluate({})
assert(v == calendar.day_name[5])
def test_batch13(self):
qb = QueryParser().queries(
open(testpath + "parse/" + "test.sql").read())
assert len(qb) == 13
def test_extract_micro_date(self):
frag = "EXTRACT(MICROSECOND FROM '2017-05-20')"
expr = QueryParser().parse_expression(frag)
assert(frag.replace(' ', '') == str(expr).replace(' ', ''))
v = expr.evaluate({})
assert(v == 0)
def test_tsql_escaped_error(self):
with pytest.raises(ValueError) as err:
QueryParser().parse_only("SELECT [FOO.BAR] FROM HR;")
err.match("^Lexer error")
def test_extract_micro_time_no_micro(self):
frag = "EXTRACT(MICROSECOND FROM '09:12:32')"
expr = QueryParser().parse_expression(frag)
assert(frag.replace(' ', '') == str(expr).replace(' ', ''))
v = expr.evaluate({})
assert(v == 0)
def test_truncate_int(self):
frag = '15 * 5'
expr = QueryParser().parse_expression(frag)
s = TruncFunction(expr, Literal(-1))
v = s.evaluate({})
assert(v == 70)
def test_extract_micro(self):
frag = "EXTRACT(MICROSECOND FROM CURRENT_TIMESTAMP)"
expr = QueryParser().parse_expression(frag)
assert(frag.replace(' ', '') == str(expr).replace(' ', ''))
v = expr.evaluate({})
assert(v > 0)
def test_truncate_float(self):
frag = '15.3333333333333 * 5'
expr = QueryParser().parse_expression(frag)
s = TruncFunction(expr, Literal(2))
v = s.evaluate({})
assert(v == 76.66)
def runParseAgain(self, q):
""" Converts AST to text, re-parses to AST, and compares the two ASTs"""
repeat = QueryParser().query(str(q))
assert q == repeat
def test_group_column_shared(self):
query = 'SELECT "IsHoliday" As col, SUM(features."Store") FROM features GROUP BY col'
q = QueryParser(metadata).query(query)
assert (q._named_symbols['col'].expression ==
q._grouping_symbols[0].expression)
def test_simple(self):
query = "SELECT * FROM FOO;"
QueryParser().parse_only(query) # try parsing without building
qb = QueryParser().query(query)
def test_position_neg(self):
frag = "SUBSTRING('Hello world!' FROM -4 FOR 2)"
expr = QueryParser().parse_expression(frag)
assert (frag.replace(' ', '') == str(expr).replace(' ', ''))
v = expr.evaluate({})
assert (v == 'rl')
def test_join_query(self):
query = 'SELECT COUNT(sales.Store) FROM sales JOIN features ON sales.Store = features.Store'
_ = QueryParser(schema).query(query)
query = 'SELECT COUNT(table1.Store) FROM sales AS table1 JOIN features ON table1.Store = features.Store'
_ = QueryParser(schema).query(query)