def test_bad_rewrites_ignored(self):
assert (rewrite_query(
"SELECT col_a FROM sometable WHERE (/*QueryRewrite':STRAY_JOIN*/1)"
) == "SELECT col_a FROM sometable WHERE (1)")
assert (rewrite_query(
"SELECT col_a FROM sometable WHERE (/*QueryRewrite':*/1)") ==
"SELECT col_a FROM sometable WHERE (1)")
assert (rewrite_query("UPDATE col_a SET pants='onfire' WHERE " +
"(/*QueryRewrite':STRAIGHT_JOIN*/1)") ==
"UPDATE col_a SET pants='onfire' WHERE (1)")
def test_labelling(self):
assert (
rewrite_query(
"SELECT col_a FROM sometable WHERE (/*QueryRewrite':label=himum*/1)"
)
== "SELECT /*himum*/ col_a FROM sometable WHERE (1)"
)
def test_2_straight_joins_dont_affect_all_and_highpriority(self):
assert rewrite_query(
"SELECT ALL HIGH_PRIORITY col_a FROM sometable " +
"WHERE (/*QueryRewrite':STRAIGHT_JOIN*/1) AND " +
"(/*QueryRewrite':STRAIGHT_JOIN*/1)") == (
"SELECT ALL HIGH_PRIORITY STRAIGHT_JOIN col_a FROM sometable "
+ "WHERE (1) AND (1)")
def test_not_case_sensitive(self):
assert (
rewrite_query(
"select col_a from sometable where (/*QueryRewrite':label=himum*/1)"
)
== "select /*himum*/ col_a from sometable where (1)"
)
def execute(self, sql, args=None):
if (
getattr(settings, "DJANGO_MYSQL_REWRITE_QUERIES", False)
and REWRITE_MARKER in sql
):
sql = rewrite_query(sql)
return orig_execute(self, sql, args)
def test_labelling_mysql_57_hint(self):
assert rewrite_query(
"SELECT col_a FROM t1 " +
"WHERE (/*QueryRewrite':label=+ NO_RANGE_OPTIMIZATION(t1 " +
"PRIMARY) */1)") == (
"SELECT /*+ NO_RANGE_OPTIMIZATION(t1 PRIMARY) */ col_a FROM " +
"t1 WHERE (1)")
def test_multiple_hints(self):
assert rewrite_query(
"SELECT col_a FROM sometable " +
"WHERE (/*QueryRewrite':STRAIGHT_JOIN*/1) AND " +
"(/*QueryRewrite':SQL_NO_CACHE*/1)") == (
"SELECT STRAIGHT_JOIN SQL_NO_CACHE col_a FROM sometable " +
"WHERE (1) AND (1)")
def test_index_hint_for_group_by(self):
assert rewrite_query(
"SELECT col_a, SUM(col_b) FROM `sometable` " +
"WHERE (/*QueryRewrite':index=`sometable` FORCE FOR GROUP BY " +
"`idx`*/1) GROUP BY col_a") == (
"SELECT col_a, SUM(col_b) FROM `sometable` FORCE INDEX FOR " +
"GROUP BY (`idx`) WHERE (1) GROUP BY col_a")
def test_index_hint_multiple_hints(self):
assert rewrite_query(
"SELECT col_a FROM `sometable` " +
"WHERE (/*QueryRewrite':index=`sometable` IGNORE `idx1`*/1) " +
"AND (/*QueryRewrite':index=`sometable` IGNORE `idx2`*/1)") == (
"SELECT col_a FROM `sometable` IGNORE INDEX (`idx2`) " +
"IGNORE INDEX (`idx1`) WHERE (1) AND (1)")
def test_index_hint_for_order_by(self):
assert rewrite_query(
"SELECT col_a FROM `sometable` " +
"WHERE (/*QueryRewrite':index=`sometable` USE FOR ORDER BY " +
"`idx` */1) ORDER BY col_a") == (
"SELECT col_a FROM `sometable` USE INDEX FOR ORDER BY (`idx`) "
+ "WHERE (1) ORDER BY col_a")
def test_straight_join_idempotent(self):
assert rewrite_query(
"SELECT col_a, col_b FROM sometable " +
"WHERE nothing() AND (/*QueryRewrite':STRAIGHT_JOIN*/1) " +
"AND (/*QueryRewrite':STRAIGHT_JOIN*/1)") == (
"SELECT STRAIGHT_JOIN col_a, col_b FROM sometable " +
"WHERE nothing() AND (1) AND (1)")
def test_straight_join_doesnt_affect_all_and_highpriority(self):
assert (
rewrite_query(
"SELECT ALL HIGH_PRIORITY col_a FROM sometable WHERE " +
"(/*QueryRewrite':STRAIGHT_JOIN*/1)") ==
"SELECT ALL HIGH_PRIORITY STRAIGHT_JOIN col_a FROM sometable WHERE (1)"
)
def test_index_nonsense_does_nothing(self):
assert (
rewrite_query(
"SELECT col_a FROM `sometable` WHERE "
"(/*QueryRewrite':index=`sometable` MAHOGANY `col_a_idx`*/1)"
) ==
"SELECT col_a FROM `sometable` WHERE (1)"
)
def test_index_hint_use(self):
assert (
rewrite_query(
"SELECT col_a FROM `sometable` WHERE "
"(/*QueryRewrite':index=`sometable` USE `col_a_idx`*/1)"
) ==
"SELECT col_a FROM `sometable` USE INDEX (`col_a_idx`) WHERE (1)"
)
def test_labelling(self):
assert (
rewrite_query(
"SELECT col_a FROM sometable "
"WHERE (/*QueryRewrite':label=himum*/1)"
) ==
"SELECT /*himum*/ col_a FROM sometable WHERE (1)"
)
def test_index_hint_multiple_indexes(self):
assert (
rewrite_query(
"SELECT col_a FROM `tabl` WHERE "
"(/*QueryRewrite':index=`tabl` IGNORE `idx1`,`idx2`*/1)"
) ==
"SELECT col_a FROM `tabl` IGNORE INDEX (`idx1`,`idx2`) WHERE (1)"
)
def test_straight_join_doesnt_affect_distinct(self):
assert (
rewrite_query(
"SELECT DISTINCT col_a FROM sometable "
"WHERE (/*QueryRewrite':STRAIGHT_JOIN*/1)"
) ==
"SELECT DISTINCT STRAIGHT_JOIN col_a FROM sometable WHERE (1)"
)
def test_index_hint_multiple_indexes(self):
assert (
rewrite_query(
"SELECT col_a FROM `tabl` WHERE "
"(/*QueryRewrite':index=`tabl` IGNORE `idx1`,`idx2`*/1)"
) ==
"SELECT col_a FROM `tabl` IGNORE INDEX (`idx1`,`idx2`) WHERE (1)"
)
def test_straight_join_with_spaceless_comment(self):
assert (
rewrite_query(
"SELECT/* this*/col_a FROM sometable "
"WHERE (/*QueryRewrite':STRAIGHT_JOIN*/1)"
) ==
"SELECT /* this*/ STRAIGHT_JOIN col_a FROM sometable WHERE (1)"
)
def test_straight_join_with_repeat_comments(self):
assert rewrite_query(
"SELECT /* A */ /* B */ /* C */ col_a FROM sometable "
+ "WHERE (/*QueryRewrite':STRAIGHT_JOIN*/1)"
) == (
"SELECT /* A */ /* B */ /* C */ STRAIGHT_JOIN col_a FROM "
+ "sometable WHERE (1)"
)
def test_straight_join_with_spaceless_comment(self):
assert (
rewrite_query(
"SELECT/* this*/col_a FROM sometable "
+ "WHERE (/*QueryRewrite':STRAIGHT_JOIN*/1)"
)
== "SELECT /* this*/ STRAIGHT_JOIN col_a FROM sometable WHERE (1)"
)
def test_straight_join_doesnt_affect_distinct(self):
assert (
rewrite_query(
"SELECT DISTINCT col_a FROM sometable WHERE "
+ "(/*QueryRewrite':STRAIGHT_JOIN*/1)"
)
== "SELECT DISTINCT STRAIGHT_JOIN col_a FROM sometable WHERE (1)"
)
def test_straight_join_preceeding_whitespace(self):
assert rewrite_query(
" SELECT col_a, col_b FROM sometable WHERE nothing() AND "
+ "(/*QueryRewrite':STRAIGHT_JOIN*/1)"
) == (
"SELECT STRAIGHT_JOIN col_a, col_b FROM sometable WHERE "
+ "nothing() AND (1)"
)
def test_index_nonsense_does_nothing(self):
assert (
rewrite_query(
"SELECT col_a FROM `sometable` WHERE "
+ "(/*QueryRewrite':index=`sometable` MAHOGANY `col_a_idx`*/1)"
)
== "SELECT col_a FROM `sometable` WHERE (1)"
)
def test_index_hint_use_secondary(self):
assert rewrite_query(
"SELECT col_a, col_b FROM `sometable` INNER JOIN `othertable` "
+ "WHERE (/*QueryRewrite':index=`othertable` USE `myindex`*/1)"
) == (
"SELECT col_a, col_b FROM `sometable` INNER JOIN `othertable` "
+ "USE INDEX (`myindex`) WHERE (1)"
)
def test_bad_query_not_rewritten(self):
assert (
rewrite_query(
"SELECTSTRAIGHT_JOIN col_a FROM sometable WHERE "
+ "(/*QueryRewrite':label=hi*/1)"
)
== "SELECTSTRAIGHT_JOIN col_a FROM sometable WHERE (1)"
)
def test_index_hint_use(self):
assert (
rewrite_query(
"SELECT col_a FROM `sometable` WHERE "
+ "(/*QueryRewrite':index=`sometable` USE `col_a_idx`*/1)"
)
== "SELECT col_a FROM `sometable` USE INDEX (`col_a_idx`) WHERE (1)"
)
def test_straight_join_with_comments(self):
assert (
rewrite_query(
"SELECT /* GOODBYE */ col_a FROM sometable WHERE "
+ "(/*QueryRewrite':STRAIGHT_JOIN*/1)"
)
== "SELECT /* GOODBYE */ STRAIGHT_JOIN col_a FROM sometable WHERE (1)"
)
def test_index_hint_for_join(self):
assert rewrite_query(
"SELECT `sometable`.col_a, `sometable2`.col_b " +
"FROM `sometable` NATURAL JOIN `sometable2` " +
"WHERE (/*QueryRewrite':index=`sometable` IGNORE FOR JOIN " +
"`idx`*/1)") == (
"SELECT `sometable`.col_a, `sometable2`.col_b FROM " +
"`sometable` IGNORE INDEX FOR JOIN (`idx`) NATURAL JOIN " +
"`sometable2` WHERE (1)")
def test_mutually_exclusive_latest_wins(self):
assert (
rewrite_query(
"SELECT col_a FROM sometable "
"WHERE (/*QueryRewrite':SQL_CACHE*/1) AND "
"(/*QueryRewrite':SQL_NO_CACHE*/1)"
) ==
"SELECT SQL_NO_CACHE col_a FROM sometable WHERE (1) AND (1)"
)
def test_index_hint_use_secondary(self):
assert (
rewrite_query(
"SELECT col_a, col_b FROM `sometable` INNER JOIN `othertable` "
" WHERE (/*QueryRewrite':index=`othertable` USE `myindex`*/1)"
) ==
"SELECT col_a, col_b FROM `sometable` INNER JOIN `othertable` "
"USE INDEX (`myindex`) WHERE (1)"
)
def test_mutually_exclusive_latest_wins(self):
assert (
rewrite_query(
"SELECT col_a FROM sometable "
+ "WHERE (/*QueryRewrite':SQL_CACHE*/1) AND "
+ "(/*QueryRewrite':SQL_NO_CACHE*/1)"
)
== "SELECT SQL_NO_CACHE col_a FROM sometable WHERE (1) AND (1)"
)
def test_index_force(self):
assert (
rewrite_query(
"SELECT col_a FROM `sometable` WHERE "
"(/*QueryRewrite':index=`sometable` FORCE `col_a_idx`*/1)"
) ==
"SELECT col_a FROM `sometable` FORCE INDEX (`col_a_idx`) "
"WHERE (1)"
)
def test_straight_join_with_repeat_comments(self):
assert (
rewrite_query(
"SELECT /* A */ /* B */ /* C */ col_a FROM sometable "
"WHERE (/*QueryRewrite':STRAIGHT_JOIN*/1)"
) ==
"SELECT /* A */ /* B */ /* C */ STRAIGHT_JOIN col_a FROM "
"sometable WHERE (1)"
)
def test_straight_join_preceeding_whitespace(self):
assert (
rewrite_query(
" SELECT col_a, col_b FROM sometable "
"WHERE nothing() AND (/*QueryRewrite':STRAIGHT_JOIN*/1)"
) ==
"SELECT STRAIGHT_JOIN col_a, col_b FROM sometable "
"WHERE nothing() AND (1)"
)
def test_straight_join_doesnt_affect_all_and_highpriority(self):
assert (
rewrite_query(
"SELECT ALL HIGH_PRIORITY col_a FROM sometable "
"WHERE (/*QueryRewrite':STRAIGHT_JOIN*/1)"
) ==
"SELECT ALL HIGH_PRIORITY STRAIGHT_JOIN col_a FROM sometable "
"WHERE (1)"
)
def test_straight_join(self):
assert (
rewrite_query(
"SELECT col_a, col_b FROM sometable "
"WHERE nothing() AND (/*QueryRewrite':STRAIGHT_JOIN*/1)"
) ==
"SELECT STRAIGHT_JOIN col_a, col_b FROM sometable "
"WHERE nothing() AND (1)"
)
def test_index_force(self):
assert (
rewrite_query(
"SELECT col_a FROM `sometable` WHERE "
"(/*QueryRewrite':index=`sometable` FORCE `col_a_idx`*/1)"
) ==
"SELECT col_a FROM `sometable` FORCE INDEX (`col_a_idx`) "
"WHERE (1)"
)
def test_not_case_sensitive(self):
assert (
rewrite_query(
"select col_a from sometable "
"where (/*QueryRewrite':label=himum*/1)"
) ==
"select /*himum*/ col_a from sometable "
"where (1)"
)
def test_bad_query_not_rewritten(self):
assert (
rewrite_query(
"SELECTSTRAIGHT_JOIN col_a FROM sometable"
"WHERE (/*QueryRewrite':label=hi*/1)"
) ==
"SELECTSTRAIGHT_JOIN col_a FROM sometable"
"WHERE (1)"
)
def test_labelling_mysql_57_hint(self):
assert (
rewrite_query(
"SELECT col_a FROM t1 "
"WHERE (/*QueryRewrite':label=+ NO_RANGE_OPTIMIZATION(t1 "
"PRIMARY) */1)"
) ==
"SELECT /*+ NO_RANGE_OPTIMIZATION(t1 PRIMARY) */ col_a FROM t1 "
"WHERE (1)"
)
def test_index_hint_for_group_by(self):
assert (
rewrite_query(
"SELECT col_a, SUM(col_b) FROM `sometable` "
"WHERE (/*QueryRewrite':index=`sometable` FORCE FOR GROUP BY "
"`idx`*/1) GROUP BY col_a"
) ==
"SELECT col_a, SUM(col_b) FROM `sometable` FORCE INDEX FOR GROUP "
"BY (`idx`) WHERE (1) GROUP BY col_a"
)
def test_index_hint_for_order_by(self):
assert (
rewrite_query(
"SELECT col_a FROM `sometable` "
"WHERE (/*QueryRewrite':index=`sometable` USE FOR ORDER BY "
"`idx` */1) ORDER BY col_a"
) ==
"SELECT col_a FROM `sometable` USE INDEX FOR ORDER BY (`idx`) "
"WHERE (1) ORDER BY col_a"
)
def test_index_hint_multiple_hints(self):
assert (
rewrite_query(
"SELECT col_a FROM `sometable` "
"WHERE (/*QueryRewrite':index=`sometable` IGNORE `idx1`*/1) "
"AND (/*QueryRewrite':index=`sometable` IGNORE `idx2`*/1)"
) ==
"SELECT col_a FROM `sometable` IGNORE INDEX (`idx2`) "
"IGNORE INDEX (`idx1`) WHERE (1) AND (1)"
)
def test_straight_join_idempotent(self):
assert (
rewrite_query(
"SELECT col_a, col_b FROM sometable "
"WHERE nothing() AND (/*QueryRewrite':STRAIGHT_JOIN*/1) "
"AND (/*QueryRewrite':STRAIGHT_JOIN*/1)"
) ==
"SELECT STRAIGHT_JOIN col_a, col_b FROM sometable "
"WHERE nothing() AND (1) AND (1)"
)
def test_multiple_hints(self):
assert (
rewrite_query(
"SELECT col_a FROM sometable "
"WHERE (/*QueryRewrite':STRAIGHT_JOIN*/1) AND "
"(/*QueryRewrite':SQL_NO_CACHE*/1)"
) ==
"SELECT STRAIGHT_JOIN SQL_NO_CACHE col_a FROM sometable "
"WHERE (1) AND (1)"
)
def test_index_hint_for_join(self):
assert (
rewrite_query(
"SELECT `sometable`.col_a, `sometable2`.col_b "
"FROM `sometable` NATURAL JOIN `sometable2` "
"WHERE (/*QueryRewrite':index=`sometable` IGNORE FOR JOIN "
"`idx`*/1)"
) ==
"SELECT `sometable`.col_a, `sometable2`.col_b FROM `sometable` "
"IGNORE INDEX FOR JOIN (`idx`) "
"NATURAL JOIN `sometable2` WHERE (1)"
)
def rewrite_hook(
execute: Callable[[str, str, bool, dict[str, Any]], Any],
sql: str,
params: str,
many: bool,
context: dict[str, Any],
) -> Any:
if (
getattr(settings, "DJANGO_MYSQL_REWRITE_QUERIES", False)
and REWRITE_MARKER in sql
):
sql = rewrite_query(sql)
return execute(sql, params, many, context)
def test_bad_rewrites_ignored(self):
assert (
rewrite_query(
"SELECT col_a FROM sometable "
"WHERE (/*QueryRewrite':STRAY_JOIN*/1)"
) ==
"SELECT col_a FROM sometable WHERE (1)"
)
assert (
rewrite_query(
"SELECT col_a FROM sometable "
"WHERE (/*QueryRewrite':*/1)"
) ==
"SELECT col_a FROM sometable WHERE (1)"
)
assert (
rewrite_query(
"UPDATE col_a SET pants='onfire' "
"WHERE (/*QueryRewrite':STRAIGHT_JOIN*/1)"
) ==
"UPDATE col_a SET pants='onfire' WHERE (1)"
)
def test_non_select_update_deletes_ignored(self):
assert (
rewrite_query("SHOW TABLES /*QueryRewrite':STRAIGHT_JOIN*/") ==
"SHOW TABLES "
)
def check_identity(self, query):
assert rewrite_query(query) == query
def test_non_select_update_deletes_ignored(self):
assert (rewrite_query("SHOW TABLES /*QueryRewrite':STRAIGHT_JOIN*/") ==
"SHOW TABLES ")
def check_identity(self, query):
assert rewrite_query(query) == query
def test_index_hint_with_alias(self):
assert rewrite_query(
"SELECT col_a, col_b FROM `sometable` U1 " +
"WHERE (/*QueryRewrite':index=`sometable` USE `col_a_idx`*/1)"
) == ("SELECT col_a, col_b FROM `sometable` U1 " +
"USE INDEX (`col_a_idx`) WHERE (1)")