def test_complex_query_1(self):
with CaptureLastQuery() as cap:
list(Author.objects.distinct().straight_join().filter(
books__title__startswith="A").exclude(
books__id__lte=1).prefetch_related("tutees").filter(
bio__gt="").exclude(bio__startswith="Once upon"))
assert cap.query.startswith("SELECT DISTINCT STRAIGHT_JOIN ")
def test_straight_join(self):
with CaptureLastQuery() as cap:
list(
Author.objects.filter(
books__title__startswith="A").straight_join())
assert cap.query.startswith("SELECT STRAIGHT_JOIN ")
def test_straight_join_with_distinct(self):
with CaptureLastQuery() as cap:
list(
Author.objects.filter(tutor=None).distinct().values(
"books__title").straight_join())
assert cap.query.startswith("SELECT DISTINCT STRAIGHT_JOIN ")
def test_label_twice(self):
with CaptureLastQuery() as cap:
list(
Author.objects.label("QueryHintTests").label(
"test_label_twice").all())
assert cap.query.startswith(
"SELECT /*QueryHintTests*/ /*test_label_twice*/ ")
def test_label_and_straight_join(self):
with CaptureLastQuery() as cap:
list(
Author.objects.label(
"QueryHintTests.test_label_and").straight_join().all())
assert cap.query.startswith(
"SELECT /*QueryHintTests.test_label_and*/ STRAIGHT_JOIN ")
def test_can_monkey_patch_is_idempotent(self):
patch_CursorWrapper_execute()
with CaptureLastQuery() as cap, connection.cursor() as cursor:
cursor.execute(
"SELECT 1 FROM DUAL WHERE (/*QueryRewrite':label=hi*/1)")
assert cap.query == "SELECT /*hi*/ 1 FROM DUAL WHERE (1)"
def test_use_index(self):
name_idx = index_name(Author, "name")
with CaptureLastQuery() as cap:
list(Author.objects.filter(name__gt="").use_index(name_idx))
assert ("USE INDEX (`" + name_idx + "`)") in cap.query
used = used_indexes(cap.query)
assert len(used) == 0 or name_idx in used
def test_use_index_table_name(self):
extra_table = 'testapp_authorextra'
with CaptureLastQuery() as cap:
list(
Author.objects.select_related('authorextra').use_index(
'PRIMARY', table_name=extra_table))
assert ('`' + extra_table + '` USE INDEX (`PRIMARY`) ') in cap.query
def test_index_hint_force_order_by(self):
name_idx = index_name(Author, "name")
with CaptureLastQuery() as cap:
list(Author.objects.force_index(name_idx, for_="ORDER BY").order_by("name"))
assert ("FORCE INDEX FOR ORDER BY (`" + name_idx + "`)") in cap.query
assert name_idx in used_indexes(cap.query)
def test_use_index_table_name(self):
extra_table = "testapp_authorextra"
with CaptureLastQuery() as cap:
list(
Author.objects.select_related("authorextra").use_index(
"PRIMARY", table_name=extra_table))
assert ("`" + extra_table + "` USE INDEX (`PRIMARY`) ") in cap.query
def test_use_index(self):
name_idx = index_name(Author, 'name')
with CaptureLastQuery() as cap:
list(Author.objects.filter(name__gt='').use_index(name_idx))
assert ('USE INDEX (`' + name_idx + '`)') in cap.query
used = used_indexes(cap.query)
assert len(used) == 0 or name_idx in used
def test_force_index_table_name_doesnt_exist_ignored(self):
with CaptureLastQuery() as cap:
list(
Author.objects.select_related("authorextra").force_index(
"PRIMARY", table_name="nonexistent"
)
)
assert " FORCE INDEX " not in cap.query
def test_adding_many(self):
with CaptureLastQuery() as cap:
list(
Author.objects.straight_join()
.sql_cache()
.sql_big_result()
.sql_buffer_result()
)
assert cap.query.startswith(
"SELECT STRAIGHT_JOIN SQL_BIG_RESULT SQL_BUFFER_RESULT SQL_CACHE "
)
def test_ignore_index_multiple(self):
name_idx = index_name(AuthorMultiIndex, 'name')
name_country_idx = index_name(AuthorMultiIndex, 'name', 'country')
with CaptureLastQuery() as cap:
list(
AuthorMultiIndex.objects.filter(name__gt='').ignore_index(
name_idx, name_country_idx))
assert ('IGNORE INDEX (`' + name_idx + '`,`' + name_country_idx +
'`)') in cap.query
used = used_indexes(cap.query)
assert name_idx not in used
assert name_country_idx not in used
def test_ignore_index_multiple(self):
name_idx = index_name(AuthorMultiIndex, "name")
name_country_idx = index_name(AuthorMultiIndex, "name", "country")
with CaptureLastQuery() as cap:
list(
AuthorMultiIndex.objects.filter(name__gt="").ignore_index(
name_idx, name_country_idx))
assert ("IGNORE INDEX (`" + name_idx + "`,`" + name_country_idx +
"`)") in cap.query
used = used_indexes(cap.query)
assert name_idx not in used
assert name_country_idx not in used
def test_force_index_inner_query(self):
title_idx = index_name(Book, "title")
with CaptureLastQuery() as cap:
list(
Author.objects.annotate(
has_books=Exists(
Book.objects.filter(
author_id=OuterRef("id"), title__gt=""
).force_index(title_idx, table_name="testapp_book")
)
).filter(has_books=True)
)
assert ("FORCE INDEX (`" + title_idx + "`)") in cap.query
used = used_indexes(cap.query)
assert title_idx in used
def test_use_index_none(self):
with CaptureLastQuery() as cap:
list(Author.objects.values_list("name").distinct().use_index())
assert "USE INDEX () " in cap.query
assert used_indexes(cap.query) == set()
def test_ignore_index_primary(self):
with CaptureLastQuery() as cap:
list(Author.objects.filter(name__gt='').ignore_index('PRIMARY'))
assert 'IGNORE INDEX (`PRIMARY`)' in cap.query
assert 'PRIMARY' not in used_indexes(cap.query)
def test_ignore_index_primary(self):
with CaptureLastQuery() as cap:
list(Author.objects.filter(name__gt="").ignore_index("PRIMARY"))
assert "IGNORE INDEX (`PRIMARY`)" in cap.query
assert "PRIMARY" not in used_indexes(cap.query)
def test_ignore_index(self):
name_idx = index_name(Author, "name")
with CaptureLastQuery() as cap:
list(Author.objects.filter(name__gt="").ignore_index(name_idx))
assert ("IGNORE INDEX (`" + name_idx + "`)") in cap.query
assert name_idx not in used_indexes(cap.query)
def test_force_index_primary(self):
with CaptureLastQuery() as cap:
list(Author.objects.force_index("PRIMARY"))
assert ("FORCE INDEX (`PRIMARY`)") in cap.query
used = used_indexes(cap.query)
assert len(used) == 0 or "PRIMARY" in used
def test_force_index_table_name_doesnt_exist_ignored(self):
with CaptureLastQuery() as cap:
list(
Author.objects.select_related('authorextra').force_index(
'PRIMARY', table_name='nonexistent'))
assert ' FORCE INDEX ' not in cap.query
def test_complex_query_2(self):
subq = Book.objects.straight_join().filter(title__startswith="A")
with CaptureLastQuery() as cap:
list(Author.objects.straight_join().filter(books__in=subq))
assert cap.query.startswith("SELECT STRAIGHT_JOIN ")
def test_sql_buffer_result(self):
with CaptureLastQuery() as cap:
list(Author.objects.sql_buffer_result().all())
assert cap.query.startswith("SELECT SQL_BUFFER_RESULT ")
def test_sql_no_cache(self):
with CaptureLastQuery() as cap:
list(Author.objects.sql_no_cache().all())
assert cap.query.startswith("SELECT SQL_NO_CACHE ")
def test_label_update(self):
Author.objects.create(name="UPDATEME")
with CaptureLastQuery() as cap:
Author.objects.label("QueryHintTests").update(name="UPDATED")
assert cap.query.startswith("UPDATE /*QueryHintTests*/ ")
def test_label_star(self):
with CaptureLastQuery() as cap:
list(Author.objects.label("I'ma*").label("*").all())
assert cap.query.startswith("SELECT /*I'ma**/ /***/ ")
def test_instrumentation_can_be_disabled(self):
query = "SELECT 1 FROM DUAL WHERE (/*QueryRewrite':STRAIGHT_JOIN*/1)"
with CaptureLastQuery() as cap, connection.cursor() as cursor:
cursor.execute(query)
assert cap.query == query
def test_label_forced_new_connection(self):
with CaptureLastQuery() as cap:
list(Author.objects.label("QueryHintTests.test_label").all())
assert cap.query.startswith("SELECT /*QueryHintTests.test_label*/ ")
def test_it_is_instrumented(self):
with CaptureLastQuery() as cap, connection.cursor() as cursor:
cursor.execute(
"SELECT 1 FROM DUAL WHERE (/*QueryRewrite':STRAIGHT_JOIN*/1)")
assert cap.query == "SELECT STRAIGHT_JOIN 1 FROM DUAL WHERE (1)"
