| 1 |
import json |
| 2 |
import unittest |
| 3 |
import xml.etree.ElementTree |
| 4 |
|
| 5 |
from django.db import NotSupportedError, connection, transaction |
| 6 |
from django.db.models import Count |
| 7 |
from django.test import TestCase, skipIfDBFeature, skipUnlessDBFeature |
| 8 |
from django.test.utils import CaptureQueriesContext |
| 9 |
|
| 10 |
from .models import Tag |
| 11 |
|
| 12 |
|
| 13 |
@skipUnlessDBFeature("supports_explaining_query_execution") |
| 14 |
class ExplainTests(TestCase): |
| 15 |
def test_basic(self): |
| 16 |
querysets = [ |
| 17 |
Tag.objects.filter(name="test"), |
| 18 |
Tag.objects.filter(name="test").select_related("parent"), |
| 19 |
Tag.objects.filter(name="test").prefetch_related("children"), |
| 20 |
Tag.objects.filter(name="test").annotate(Count("children")), |
| 21 |
Tag.objects.filter(name="test").values_list("name"), |
| 22 |
] |
| 23 |
if connection.features.supports_select_union: |
| 24 |
querysets.append( |
| 25 |
Tag.objects.order_by().union(Tag.objects.order_by().filter(name="test")) |
| 26 |
) |
| 27 |
if connection.features.has_select_for_update: |
| 28 |
querysets.append(Tag.objects.select_for_update().filter(name="test")) |
| 29 |
supported_formats = connection.features.supported_explain_formats |
| 30 |
all_formats = ( |
| 31 |
(None,) |
| 32 |
+ tuple(supported_formats) |
| 33 |
+ tuple(f.lower() for f in supported_formats) |
| 34 |
) |
| 35 |
for idx, queryset in enumerate(querysets): |
| 36 |
for format in all_formats: |
| 37 |
with self.subTest(format=format, queryset=idx): |
| 38 |
with self.assertNumQueries(1) as captured_queries: |
| 39 |
result = queryset.explain(format=format) |
| 40 |
self.assertTrue( |
| 41 |
captured_queries[0]["sql"].startswith( |
| 42 |
connection.ops.explain_prefix |
| 43 |
) |
| 44 |
) |
| 45 |
self.assertIsInstance(result, str) |
| 46 |
self.assertTrue(result) |
| 47 |
if not format: |
| 48 |
continue |
| 49 |
if format.lower() == "xml": |
| 50 |
try: |
| 51 |
xml.etree.ElementTree.fromstring(result) |
| 52 |
except xml.etree.ElementTree.ParseError as e: |
| 53 |
self.fail( |
| 54 |
f"QuerySet.explain() result is not valid XML: {e}" |
| 55 |
) |
| 56 |
elif format.lower() == "json": |
| 57 |
try: |
| 58 |
json.loads(result) |
| 59 |
except json.JSONDecodeError as e: |
| 60 |
self.fail( |
| 61 |
f"QuerySet.explain() result is not valid JSON: {e}" |
| 62 |
) |
| 63 |
|
| 64 |
def test_unknown_options(self): |
| 65 |
with self.assertRaisesMessage(ValueError, "Unknown options: TEST, TEST2"): |
| 66 |
Tag.objects.explain(**{"TEST": 1, "TEST2": 1}) |
| 67 |
|
| 68 |
def test_unknown_format(self): |
| 69 |
msg = "DOES NOT EXIST is not a recognized format." |
| 70 |
if connection.features.supported_explain_formats: |
| 71 |
msg += " Allowed formats: %s" % ", ".join( |
| 72 |
sorted(connection.features.supported_explain_formats) |
| 73 |
) |
| 74 |
else: |
| 75 |
msg += f" {connection.display_name} does not support any formats." |
| 76 |
with self.assertRaisesMessage(ValueError, msg): |
| 77 |
Tag.objects.explain(format="does not exist") |
| 78 |
|
| 79 |
@unittest.skipUnless(connection.vendor == "postgresql", "PostgreSQL specific") |
| 80 |
def test_postgres_options(self): |
| 81 |
qs = Tag.objects.filter(name="test") |
| 82 |
test_options = [ |
| 83 |
{"COSTS": False, "BUFFERS": True, "ANALYZE": True}, |
| 84 |
{"costs": False, "buffers": True, "analyze": True}, |
| 85 |
{"verbose": True, "timing": True, "analyze": True}, |
| 86 |
{"verbose": False, "timing": False, "analyze": True}, |
| 87 |
{"summary": True}, |
| 88 |
{"settings": True}, |
| 89 |
{"analyze": True, "wal": True}, |
| 90 |
] |
| 91 |
if connection.features.is_postgresql_16: |
| 92 |
test_options.append({"generic_plan": True}) |
| 93 |
if connection.features.is_postgresql_17: |
| 94 |
test_options.append({"memory": True}) |
| 95 |
test_options.append({"serialize": "TEXT", "analyze": True}) |
| 96 |
test_options.append({"serialize": "text", "analyze": True}) |
| 97 |
test_options.append({"serialize": "BINARY", "analyze": True}) |
| 98 |
test_options.append({"serialize": "binary", "analyze": True}) |
| 99 |
for options in test_options: |
| 100 |
with self.subTest(**options), transaction.atomic(): |
| 101 |
with CaptureQueriesContext(connection) as captured_queries: |
| 102 |
qs.explain(format="text", **options) |
| 103 |
self.assertEqual(len(captured_queries), 1) |
| 104 |
for name, value in options.items(): |
| 105 |
if isinstance(value, str): |
| 106 |
option = "{} {}".format(name.upper(), value.upper()) |
| 107 |
else: |
| 108 |
option = "{} {}".format( |
| 109 |
name.upper(), "true" if value else "false" |
| 110 |
) |
| 111 |
self.assertIn(option, captured_queries[0]["sql"]) |
| 112 |
|
| 113 |
@skipUnlessDBFeature("supports_select_union") |
| 114 |
def test_multi_page_text_explain(self): |
| 115 |
if "TEXT" not in connection.features.supported_explain_formats: |
| 116 |
self.skipTest("This backend does not support TEXT format.") |
| 117 |
|
| 118 |
base_qs = Tag.objects.order_by() |
| 119 |
qs = base_qs.filter(name="test").union(*[base_qs for _ in range(100)]) |
| 120 |
result = qs.explain(format="text") |
| 121 |
self.assertGreaterEqual(result.count("\n"), 100) |
| 122 |
|
| 123 |
def test_option_sql_injection(self): |
| 124 |
qs = Tag.objects.filter(name="test") |
| 125 |
options = {"SUMMARY true) SELECT 1; --": True} |
| 126 |
msg = "Invalid option name: 'SUMMARY true) SELECT 1; --'" |
| 127 |
with self.assertRaisesMessage(ValueError, msg): |
| 128 |
qs.explain(**options) |
| 129 |
|
| 130 |
def test_invalid_option_names(self): |
| 131 |
qs = Tag.objects.filter(name="test") |
| 132 |
tests = [ |
| 133 |
'opt"ion', |
| 134 |
"o'ption", |
| 135 |
"op`tion", |
| 136 |
"opti on", |
| 137 |
"option--", |
| 138 |
"optio\tn", |
| 139 |
"o\nption", |
| 140 |
"option;", |
| 141 |
"你 好", |
| 142 |
# [] are used by MSSQL. |
| 143 |
"option[", |
| 144 |
"option]", |
| 145 |
] |
| 146 |
for invalid_option in tests: |
| 147 |
with self.subTest(invalid_option): |
| 148 |
msg = f"Invalid option name: {invalid_option!r}" |
| 149 |
with self.assertRaisesMessage(ValueError, msg): |
| 150 |
qs.explain(**{invalid_option: True}) |
| 151 |
|
| 152 |
@unittest.skipUnless(connection.vendor == "mysql", "MySQL specific") |
| 153 |
def test_mysql_text_to_traditional(self): |
| 154 |
# Ensure these cached properties are initialized to prevent queries for |
| 155 |
# the MariaDB or MySQL version during the QuerySet evaluation. |
| 156 |
connection.features.supported_explain_formats |
| 157 |
with CaptureQueriesContext(connection) as captured_queries: |
| 158 |
Tag.objects.filter(name="test").explain(format="text") |
| 159 |
self.assertEqual(len(captured_queries), 1) |
| 160 |
self.assertIn("FORMAT=TRADITIONAL", captured_queries[0]["sql"]) |
| 161 |
|
| 162 |
@unittest.skipUnless( |
| 163 |
connection.vendor == "mysql", "MariaDB and MySQL >= 8.0.18 specific." |
| 164 |
) |
| 165 |
def test_mysql_analyze(self): |
| 166 |
qs = Tag.objects.filter(name="test") |
| 167 |
with CaptureQueriesContext(connection) as captured_queries: |
| 168 |
qs.explain(analyze=True) |
| 169 |
self.assertEqual(len(captured_queries), 1) |
| 170 |
prefix = "ANALYZE " if connection.mysql_is_mariadb else "EXPLAIN ANALYZE " |
| 171 |
self.assertTrue(captured_queries[0]["sql"].startswith(prefix)) |
| 172 |
with CaptureQueriesContext(connection) as captured_queries: |
| 173 |
qs.explain(analyze=True, format="JSON") |
| 174 |
self.assertEqual(len(captured_queries), 1) |
| 175 |
if connection.mysql_is_mariadb: |
| 176 |
self.assertIn("FORMAT=JSON", captured_queries[0]["sql"]) |
| 177 |
else: |
| 178 |
self.assertNotIn("FORMAT=JSON", captured_queries[0]["sql"]) |
| 179 |
|
| 180 |
|
| 181 |
@skipIfDBFeature("supports_explaining_query_execution") |
| 182 |
class ExplainUnsupportedTests(TestCase): |
| 183 |
def test_message(self): |
| 184 |
msg = "This backend does not support explaining query execution." |
| 185 |
with self.assertRaisesMessage(NotSupportedError, msg): |
| 186 |
Tag.objects.filter(name="test").explain() |