def test_get_query_limit_and_offset():
assert get_query_limit_and_offset(
'SELECT foo_limit FROM bar_offset') is None
assert get_query_limit_and_offset(
'SELECT foo_limit FROM bar_offset /* limit 1000,50 */') is None
assert get_query_limit_and_offset(
'SELECT foo_limit FROM bar_offset LIMIT 50') == (50, 0)
assert get_query_limit_and_offset(
'SELECT foo_limit FROM bar_offset LIMIT 50 OFFSET 1000') == (50, 1000)
assert get_query_limit_and_offset(
'SELECT foo_limit FROM bar_offset Limit 50 offset 1000') == (50, 1000)
assert get_query_limit_and_offset(
'SELECT foo_limit FROM bar_offset LIMIT 1000, 50') == (50, 1000)
assert get_query_limit_and_offset(
'SELECT foo_limit FROM bar_offset LIMIT 1000,50') == (50, 1000)
assert get_query_limit_and_offset(
'SELECT foo_limit FROM bar_offset limit 1000,50') == (50, 1000)
assert get_query_limit_and_offset(
"SELECT /* CategoryPaginationViewer::processSection */ "
"page_namespace,page_title,page_len,page_is_redirect,cl_sortkey_prefix FROM `page` "
"INNER JOIN `categorylinks` FORCE INDEX (cl_sortkey) ON ((cl_from = page_id)) "
"WHERE cl_type = 'page' AND cl_to = 'Spotify/Song' "
"ORDER BY cl_sortkey LIMIT 927600,200") == (200, 927600)
def check_high_offset_selects(_, queries):
"""
:type _ indexdigest.database.Database
:type queries list[str]
:rtype: list[LinterEntry]
"""
for query in queries:
# ignore insert queries (#140)
if not is_select_query(query):
continue
res = get_query_limit_and_offset(query)
if res is None:
continue
(limit, offset) = res
if offset < OFFSET_THRESHOLD:
continue
table_name = get_query_tables(query)[0]
context = OrderedDict()
context['query'] = query
context['limit'] = limit
context['offset'] = offset
yield LinterEntry(linter_type='high_offset_selects', table_name=table_name,
message='"{}" query uses too high offset impacting the performance'.
format(shorten_query(query)),
context=context)