def test_parse(self):
""""""
parse("SELECT 1 FROM data LIMIT 1 OFFSET 1")
parse("SELECT 1 FROM data LIMIT 1 OFFSET 1+1")
with self.assertRaises(Exception):
parse("SELECT 1 FROM data LIMIT 1 OFFSET")
with self.assertRaises(Exception):
parse("SELECT 1 FROM data OFFSET 1 LIMIT 1")
with self.assertRaises(Exception):
parse("SELECT a FROM data LIMIT 1 OFFSET -1")
def test_parse_sdss_queries(self):
querytext = """SELECT top 1 p.objID, p.run, p.rerun, p.camcol, p.field, p.obj, p.type, p.ra, p.dec, p.u,p.g,p.r,p.i,p.z, p.Err_u, p.Err_g, p.Err_r,p.Err_i,p.Err_z FROM fGetNearbyObjEq(195,2.5,0.5) n, PhotoPrimary p WHERE n.objID=p.objID
SELECT top 1 p.objID, p.run, p.rerun, p.camcol, p.field, p.obj, p.type, p.ra, p.dec, p.u,p.g,p.r,p.i,p.z, p.Err_u, p.Err_g, p.Err_r,p.Err_i,p.Err_z FROM fGetNearbyObjEq(195,2.5,0.5) n, PhotoPrimary p WHERE n.objID=p.objID
SELECT top 1 p.objID, p.run, p.rerun, p.camcol, p.field, p.obj, p.type, p.ra, p.dec, p.u,p.g,p.r,p.i,p.z, p.Err_u, p.Err_g, p.Err_r,p.Err_i,p.Err_z FROM fGetNearbyObjEq(195,2.5,0.5) n, PhotoPrimary p WHERE n.objID=p.objID
SELECT top 1 p.objID, p.run, p.rerun, p.camcol, p.field, p.obj, p.type, p.ra, p.dec, p.u,p.g,p.r,p.i,p.z, p.Err_u, p.Err_g, p.Err_r,p.Err_i,p.Err_z FROM fGetNearbyObjEq(195,2.5,0.5) n, PhotoPrimary p WHERE n.objID=p.objID
SELECT count(g.objID) FROM Galaxy as g, dbo.fGetNearbyObjEq( 115.866 , 40.5354 , 1.6894005 ) as d WHERE d.objID = g.objID
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725469062987925
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 588007004167733433
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 588007004168192115
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 588007004168192128
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725469063053507
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 588007004168126653
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725469600383058
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725469063446719
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725469063184604
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725469063381160
SELECT count(g.objID) FROM Galaxy as g, dbo.fGetNearbyObjEq( 115.866 , 40.5354 , 1.1262669 ) as d WHERE d.objID = g.objID
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 588007004168061134
SELECT top 1 p.objID, p.run, p.rerun, p.camcol, p.field, p.obj, p.type, p.ra, p.dec, p.u,p.g,p.r,p.i,p.z, p.Err_u, p.Err_g, p.Err_r,p.Err_i,p.Err_z FROM fGetNearbyObjEq(195,2.5,0.5) n, PhotoPrimary p WHERE n.objID=p.objID
SELECT top 1 p.objID, p.run, p.rerun, p.camcol, p.field, p.obj, p.type, p.ra, p.dec, p.u,p.g,p.r,p.i,p.z, p.Err_u, p.Err_g, p.Err_r,p.Err_i,p.Err_z FROM fGetNearbyObjEq(195,2.5,0.5) n, PhotoPrimary p WHERE n.objID=p.objID
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725469063249986
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725468526248106
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725469063184602
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 588007003630796894
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725468526248085
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725468526182582
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 588007003630862447
SELECT count(g.objID) FROM Galaxy as g, dbo.fGetNearbyObjEq( 115.866 , 40.5354 , 0.84470023 ) as d WHERE d.objID = g.objID
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 588007003630862456
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725468526248118
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 588007003631452342
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725469063774212
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725469063184628
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 588007004168192131
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725469063643301
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 587725469063643260
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 588007003631321239
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 588007003631386743
SELECT count(g.objID) FROM Galaxy as g, dbo.fGetNearbyObjEq( 115.866 , 40.5354 , 0.56313347 ) as d WHERE d.objID = g.objID
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 588007003631321255
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 588007003631059105
select rowc_g,colc_g from BESTDR3..PhotoPrimary where objID = 588007003630993526"""
for q in querytext.split("\n"):
try:
plan = parse(q)
except Exception as e:
print q
print
raise e
def test_parse_null_expr(self):
parse("SELECT 1 is null")
parse("SELECT 1 is not null")
parse("SELECT 1 IS null")
parse("SELECT null IS null")
parse("SELECT null IS not null")
parse("SELECT (1 + 2) is null")
parse("SELECT (1 + 2) is not null")
def test_parse_where_expr(self):
parse("SELECT 1 WHERE 1")
parse("SELECT 1 WHERE a+b")
parse("SELECT 1 WHERE a+b and a+b")
parse("SELECT 1 WHERE a+b AND a+b")
parse("SELECT 1 WHERE a+b and a+b and a+b")
parse("SELECT 1 WHERE a+b and a+b AND a+b")
parse("SELECT 1 WHERE a+b or a+b and a+b")
parse("SELECT 1 WHERE a+b or a+b or a+b")
parse("SELECT 1 WHERE a+b or a+b OR a+b")
parse("SELECT 1 WHERE a+b or (a+b or a+b)")
parse("SELECT 1 WHERE (a+b or a+b) or a+b")
parse("SELECT 1 WHERE a like b")
parse("SELECT 1 WHERE a like +b")
parse("SELECT 1 WHERE a like +b and a like b")
parse("SELECT 1 WHERE a like +b and a like +b")
parse("SELECT 1 WHERE a between a and b")
parse("SELECT 1 WHERE a between a and b and x=y")
parse("SELECT 1 WHERE a between a and b and c and d")
def test_parse_table_operators(self):
with self.assertRaises(Exception):
parse("SELECT 1 UNION SELECT 2")
with self.assertRaises(Exception):
parse("SELECT 1 UNION (select 2)")
def test_parse_offset(self):
""""""
parse("SELECT 1 FROM data LIMIT 1")
parse("SELECT 1 FROM data LIMIT 1 OFFSET 1")
parse("SELECT 1 FROM data LIMIT 1 OFFSET 1+1")
parse("SELECT 1 FROM data LIMIT 1 OFFSET 1*1/2")
#various syntactical errors
with self.assertRaises(Exception):
parse("SELECT 1 FROM data LIMIT 1 OFFSET")
with self.assertRaises(Exception):
parse("SELECT 1 FROM data OFFSET 1 LIMIT 1")
with self.assertRaises(Exception):
parse("SELECT 1 FROM data OFFSET 1 LIMIT 1+")
with self.assertRaises(Exception):
parse("SELECT 1 FROM data LIMIT 1 OFFSET 1?")
with self.assertRaises(Exception):
parse("SELECT 1 FROM data OFFSET 1")
with self.assertRaises(Exception):
parse("SELECT a FROM data LIMIT 1 OFFSET -1")
#test expressions in offset
with self.assertRaises(Exception):
parse("SELECT a FROM data LIMIT 1 OFFSET 1-10")
with self.assertRaises(Exception):
parse("SELECT a FROM data LIMIT 1 OFFSET 10-12")
def run_query(self, q): ast = parse(q) plan = opt(ast) return [row for row in interp(plan)]
def queries_not_equal(self, q1, q2): ast1 = parse(q1) ast2 = parse(q2) self.assertNotEqual(str(ast1), str(ast2))
def test_parse_join_subquery(self):
parse("SELECT 1 FROM (select 1)")
parse("SELECT 1 FROM (select 1 from data)")
parse("SELECT 1 FROM (select a,b from data)")
parse("SELECT 1 FROM (select a,b from data) as d")
parse("SELECT 1 FROM (select a,b from (select a, b from data)) as d")
parse("SELECT 1 FROM function() as d")
parse("SELECT 1 FROM function(1,2) as d")
parse("SELECT 1 FROM function(data.*) as d")
def test_parse_join_weird_tablenames(self):
parse("SELECT 1 FROM `a`")
parse("SELECT 1 FROM `a.b`")
parse("SELECT 1 FROM `a.b.c`")
parse("SELECT 1 FROM [a]")
parse("SELECT 1 FROM [a.b]")
parse("SELECT 1 FROM [a.b.c]")
return """print({\n%s\n%s})""" % (",\n".join(dictlines), indent)
def is_ast_valid(ast):
if not isinstance(ast, Project):
return False
if not isinstance(ast.c, Filter):
if isinstance(ast.c, From):
ast.c = Filter(ast.c, "1")
else:
return False
if not isinstance(ast.c.c, From):
return False
isscan = lambda op: isinstance(op, Scan)
if not all(map(isscan, ast.c.c.cs)):
return False
return True
if __name__ == "__main__":
from parse_sql import parse
q = """SELECT t.a, r.b + r.b
FROM data AS t, data AS r, data AS q, data AS p
WHERE t.c = r.b AND p.a = q.a"""
ast = parse(q)
code = loopify(ast)
print(code)
def test_parse_evan_queries_easy(self):
querytext = """
SELECT DISTINCT(httpRequest.status)
FROM `bluecore-qa.app_engine_logs.appengine_googleapis_com_request_log_20170915`
LIMIT 1000
SELECT protoPayload.startTime, protoPayload.method, protoPayload.resource, protoPayload.nickname
FROM [triggeredmail:app_engine_logs.appengine_googleapis_com_request_log_20170912]
WHERE protoPayload.nickname == 'evan.jones'
ORDER BY protoPayload.startTime
LIMIT 1000
SELECT protoPayload.startTime, protoPayload.method, protoPayload.resource, protoPayload.nickname
FROM [triggeredmail:app_engine_logs.appengine_googleapis_com_request_log_20170912]
WHERE protoPayload.resource LIKE '/api/rest/%/tjmaxx%' AND protoPayload.method != 'GET'
ORDER BY protoPayload.startTime
LIMIT 1000
SELECT protoPayload.startTime, lines.logMessage
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_2017082*`, UNNEST(protoPayload.line) AS lines
WHERE lines.logMessage LIKE '%Deadline exceeded%' AND protoPayload.moduleId = 'chrono-gae'
LIMIT 1000
SELECT lines.logMessage
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_2017082*`, UNNEST(protoPayload.line) AS lines
WHERE lines.logMessage LIKE '%Deadline exceeded%' AND protoPayload.moduleId = 'chrono-gae'
LIMIT 1000
SELECT lines.logMessage
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170828`, UNNEST(protoPayload.line) AS lines
WHERE lines.logMessage LIKE '%Deadline exceeded%' AND protoPayload.moduleId = 'chrono-gae'
LIMIT 1000
SELECT lines.logMessage
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170809`, UNNEST(protoPayload.line) AS lines
WHERE lines.logMessage LIKE '%Deadline exceeded%' AND protoPayload.moduleId = 'chrono-gae'
LIMIT 1000
SELECT lines.logMessage
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170829`, UNNEST(protoPayload.line) AS lines
WHERE lines.logMessage LIKE '%Deadline exceeded%' AND protoPayload.moduleId = 'chrono-gae'
LIMIT 1000
SELECT lines.logMessage
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170829`, UNNEST(protoPayload.line) AS lines
WHERE lines.logMessage LIKE '%HTTPException%'
LIMIT 1000
SELECT lines.logMessage
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170829`, UNNEST(protoPayload.line) AS lines
WHERE lines.logMessage LIKE '%HTTPException%' AND protoPayload.moduleId = 'chrono-gae'
LIMIT 1000
SELECT lines.logMessage
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170829`, UNNEST(protoPayload.line) AS lines
WHERE lines.logMessage LIKE '%HTTPException: Deadline exceeded%' AND protoPayload.moduleId = 'chrono-gae'
LIMIT 1000
SELECT lines.logMessage
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170830`, UNNEST(protoPayload.line) AS lines
WHERE lines.logMessage LIKE '%HTTPException: Deadline exceeded%' AND protoPayload.moduleId = 'chrono-gae'
LIMIT 1000
SELECT lines.logMessage
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170830`, UNNEST(protoPayload.line) AS lines
WHERE lines.logMessage LIKE '%{"table_id":%'
SELECT
requestId,
UNIX_MICROS(timestamp) AS timestamp,
timestamp AS timeHuman,
message
FROM (
SELECT
protoPayload.requestId AS requestId,
lines.time AS timestamp,
lines.logMessage AS message
FROM
`triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170830` AS t,
UNNEST(t.protoPayload.line) AS lines
WHERE
protoPayload.moduleId LIKE '%bigquery%')
WHERE
message LIKE 'Ran out of tries%'
OR message LIKE '====%'
OR message LIKE '{"table_id"%'
ORDER BY
requestId
SELECT protoPayload.startTime, protoPayload.latency, protoPayload.resource, protoPayload.moduleId, protoPayload.instanceId
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170822`
WHERE protoPayload.latency>500 AND protoPayload.resource!="/_ah/background"
ORDER BY protoPayload.latency DESC
LIMIT 1000
SELECT protoPayload.startTime, protoPayload.latency, protoPayload.resource, protoPayload.moduleId
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170822`
WHERE protoPayload.latency>500 AND protoPayload.resource!="/_ah/background"
ORDER BY protoPayload.latency DESC
LIMIT 1000
SELECT protoPayload.startTime, protoPayload.latency, protoPayload.resource
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170822`
WHERE protoPayload.latency>500 AND protoPayload.resource!="/_ah/background"
LIMIT 1000
SELECT protoPayload.startTime, protoPayload.latency, protoPayload.resource
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170822`
WHERE protoPayload.latency>500 AND protoPayload.resource!="/background"
LIMIT 1000
SELECT lines.time, lines.logMessage
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_2017081*`, UNNEST(protoPayload.line) as lines
WHERE protoPayload.moduleId = 'integration-track' AND lower(lines.logMessage) LIKE '%overall deadline%'
LIMIT 1000
SELECT protoPayload.resource
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170824`
WHERE protoPayload.resource LIKE '/display_impression/%'
LIMIT 1000
SELECT httpRequest.requestUrl
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170824`
LIMIT 1000
SELECT httpRequest.requestUrl
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170824`
WHERE httpRequest.requestUrl LIKE '/display_impression/%'
LIMIT 1000
SELECT httpRequest.requestUrl
FROM `triggeredmail.app_engine_logs.appengine_googleapis_com_request_log_20170825`
WHERE httpRequest.requestUrl LIKE '/display_impression/%'
LIMIT 1000
SELECT httpRequest.requestUrl
FROM `bluecore-qa.app_engine_logs.appengine_googleapis_com_request_log_20170825`
WHERE httpRequest.requestUrl LIKE '/display_impression/%'
LIMIT 1000
SELECT protoPayload.line.time, LENGTH(protoPayload.line.logMessage)
FROM [bluecore-qa:app_engine_logs.appengine_googleapis_com_request_log_20170823]
WHERE protoPayload.resource='/wtf'
ORDER BY protoPayload.line.time DESC
LIMIT 1000
SELECT protoPayload.line.time, LENGTH(protoPayload.line.logMessage)
FROM [bluecore-qa:app_engine_logs.appengine_googleapis_com_request_log_20170823]
WHERE protoPayload.resource='/wtf'
ORDER BY protoPayload.line.time DESC
LIMIT 1000
SELECT protoPayload.line.time, LENGTH(protoPayload.line.logMessage)
FROM [bluecore-qa:app_engine_logs.appengine_googleapis_com_request_log_20170823]
WHERE protoPayload.resource='/wtf'
ORDER BY protoPayload.line.time
LIMIT 1000
SELECT protoPayload.line.time, LENGTH(protoPayload.line.logMessage)
FROM [bluecore-qa:app_engine_logs.appengine_googleapis_com_request_log_20170823]
WHERE protoPayload.resource='/wtf'
LIMIT 1000
SELECT COUNT(*) FROM `triggeredmail.coach.aggregate_purchase_201708`
WHERE order_id IS NULL OR order_id = ''
SELECT identified.count / aggregate.count AS ratio FROM (
SELECT COUNT(*) AS count FROM `triggeredmail.coach.aggregate_viewed_product_201708`
) AS aggregate, (
SELECT COUNT(*) AS count FROM `triggeredmail.coach.identified_viewed_product_201708`
) AS identified
SELECT COUNT(*) FROM `triggeredmail.coach.aggregate_purchase_201708`
WHERE order_id IS NULL OR order_id = ''
SELECT identified.count / aggregate.count AS ratio FROM (
SELECT COUNT(*) AS count FROM `triggeredmail.coach.aggregate_viewed_product_201708`
) AS aggregate, (
SELECT COUNT(*) AS count FROM `triggeredmail.coach.identified_viewed_product_201708`
) AS identified
SELECT COUNT(*) FROM `triggeredmail.coach.aggregate_purchase_201708`
WHERE order_id IS NULL OR order_id = ''
SELECT identified.count / aggregate.count AS ratio FROM (
SELECT COUNT(*) AS count FROM `triggeredmail.coach.aggregate_viewed_product_201708`
) AS aggregate, (
SELECT COUNT(*) AS count FROM `triggeredmail.coach.identified_viewed_product_201708`
) AS identified
SELECT COUNT(*) FROM `triggeredmail.coach.aggregate_purchase_201708`
WHERE order_id IS NULL OR order_id = ''
SELECT identified.count / aggregate.count AS ratio FROM (
SELECT COUNT(*) AS count FROM `triggeredmail.coach.aggregate_viewed_product_201708`
) AS aggregate, (
SELECT COUNT(*) AS count FROM `triggeredmail.coach.identified_viewed_product_201708`
) AS identified
SELECT identified.count / aggregate.count AS ratio FROM (
SELECT COUNT(*) AS count FROM `triggeredmail.coach.aggregate_viewed_product_201708`
) AS aggregate, (
SELECT COUNT(*) AS count FROM `triggeredmail.coach.identified_viewed_product_201708`
) AS identified
SELECT identified.count / aggregate.count AS ratio FROM (
SELECT COUNT(*) AS count FROM `triggeredmail.coach.aggregate_viewed_product_201708`
) AS aggregate, (
SELECT COUNT(*) AS count FROM `triggeredmail.coach.identified_viewed_product_201708`
) AS identified
SELECT COUNT(*) FROM `triggeredmail.coach.aggregate_purchase_201708`
WHERE order_id IS NULL OR order_id = ''
SELECT identified.count / aggregate.count AS ratio FROM (
SELECT COUNT(*) AS count FROM `triggeredmail.coach.aggregate_viewed_product_201708`
) AS aggregate, (
SELECT COUNT(*) AS count FROM `triggeredmail.coach.identified_viewed_product_201708`
) AS identified
"""
queries = [""]
for l in querytext.split("\n"):
if l.strip() == "":
queries.append("")
continue
queries[-1] += " " + l.strip()
queries = filter(bool, queries)
outputs = []
for q in queries:
try:
plan = parse(q)
except Exception as e:
print q
print
raise e