def test_get_combinator_sql_empty_queryset_raises_exception(self):
"""
Tries sql generator with empty queryset.
"""
compiler = SQLCompiler(QuerySet().query, self.connection, "default")
with self.assertRaises(EmptyResultSet):
compiler.get_combinator_sql("union", False)
def test_get_combinator_sql_parentheses_in_compound_not_supported(self):
"""
Tries sql generator with union of queryset with queryset of difference,
adding support for parentheses in compound sql statement.
"""
qs1 = Number.objects.filter(num__lte=1).values("num")
qs2 = Number.objects.filter(num__gte=8).values("num")
qs3 = Number.objects.filter(num__exact=10).values("num")
qs4 = qs1.union(qs2.difference(qs3))
compiler = SQLCompiler(qs4.query, self.connection, "default")
compiler.connection.features.supports_parentheses_in_compound = False
sql_compiled, params = compiler.get_combinator_sql("union", False)
self.assertEqual(
sql_compiled,
[
"SELECT tests_number.num FROM tests_number WHERE " +
"tests_number.num <= %s UNION DISTINCT SELECT * FROM (" +
"SELECT tests_number.num FROM tests_number WHERE " +
"tests_number.num >= %s EXCEPT DISTINCT " +
"SELECT tests_number.num FROM tests_number " +
"WHERE tests_number.num = %s)"
],
)
self.assertEqual(params, [1, 8, 10])
def test_order_by_sql_query_with_order_by_name(self):
qs1 = Report.objects.values("name")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, _ = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_report.name FROM tests_report ORDER BY " +
"tests_report.name ASC",
)
def test_order_by_sql_query_with_order_by_null_first(self):
qs1 = Report.objects.values("name").order_by(
F("name").desc(nulls_first=True))
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, _ = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_report.name FROM tests_report ORDER BY " +
"tests_report.name IS NOT NULL, tests_report.name DESC",
)
def test_startswith_endswith_sql_query_case_insensitive(self):
qs1 = Author.objects.filter(name__istartswith="abc").values("num")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_author.num FROM tests_author WHERE "
+ "REGEXP_CONTAINS(CAST(tests_author.name AS STRING), %s)",
)
self.assertEqual(params, ("(?i)^abc",))
def test_cast_param_to_float_with_no_params_query(self):
qs1 = Number.objects.filter(item_id__exact=F("num")).values("num")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_number.num FROM tests_number WHERE "
+ "tests_number.item_id = (tests_number.num)",
)
self.assertEqual(params, ())
def test_cast_param_to_float_for_foreign_key_field_query(self):
qs1 = Number.objects.filter(item_id__exact="10").values("num")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_number.num FROM tests_number WHERE "
+ "tests_number.item_id = %s",
)
self.assertEqual(params, (10,))
def test_cast_param_to_float_lte_sql_query(self):
qs1 = Number.objects.filter(
decimal_num__lte=Decimal("1.1")).values("decimal_num")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_number.decimal_num FROM tests_number WHERE " +
"tests_number.decimal_num <= %s",
)
self.assertEqual(params, (Decimal("1.1"), ))
def test_cast_param_to_float_for_int_field_query(self):
qs1 = Number.objects.filter(num__lte=1.1).values("num")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_number.num FROM tests_number WHERE "
+ "tests_number.num <= %s",
)
self.assertEqual(params, (1,))
def test_regex_sql_query_case_insensitive_with_transform(self):
qs1 = Author.objects.filter(name__upper__iregex="abc").values("num")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_author.num FROM tests_author WHERE "
+ "REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), "
+ "CONCAT('(?i)', (UPPER(%s))))",
)
self.assertEqual(params, ("abc",))
def test_contains_sql_query_case_sensitive_transform(self):
qs1 = Author.objects.filter(name__upper__contains="abc").values("name")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_author.name FROM tests_author WHERE "
+ "REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), "
+ 'REPLACE(REPLACE(REPLACE((UPPER(%s)), "\\\\", "\\\\\\\\"), '
+ '"%%", r"\\%%"), "_", r"\\_"))',
)
self.assertEqual(params, ("abc",))
def test_iexact_sql_query_case_insensitive_value_match(self):
qs1 = Author.objects.filter(name__upper__iexact="abc").values("name")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_author.name FROM tests_author WHERE "
+ "REGEXP_CONTAINS((UPPER(CONCAT('^(?i)', "
+ "CAST(UPPER(tests_author.name) AS STRING), '$'))), %s)",
)
self.assertEqual(params, ("abc",))
def test_startswith_endswith_sql_query_with_bileteral_transform(self):
qs1 = Author.objects.filter(name__upper__startswith="abc").values(
"name"
)
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_author.name FROM tests_author WHERE "
+ "REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), "
+ "REPLACE(REPLACE(REPLACE(CONCAT('^', (UPPER(%s))), "
+ '"\\\\", "\\\\\\\\"), "%%", r"\\%%"), "_", r"\\_"))',
)
self.assertEqual(params, ("abc",))
def test_regex_sql_query_case_sensitive_with_transform(self):
qs1 = Author.objects.filter(name__upper__regex="abc").values("num")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
if USING_DJANGO_3:
expected_sql = (
"SELECT tests_author.num FROM tests_author WHERE " +
"REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), " +
"UPPER(%s))")
else:
expected_sql = (
"SELECT tests_author.num FROM tests_author WHERE " +
"REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), " +
"(UPPER(%s)))")
self.assertEqual(sql_compiled, expected_sql)
self.assertEqual(params, ("abc", ))
def test_get_combinator_sql_difference_all_sql_generated(self):
"""
Tries difference sql generator.
"""
qs1 = Number.objects.filter(num__lte=1).values("num")
qs2 = Number.objects.filter(num__gte=8).values("num")
qs4 = qs1.difference(qs2)
compiler = SQLCompiler(qs4.query, self.connection, "default")
sql_compiled, params = compiler.get_combinator_sql("difference", True)
self.assertEqual(
sql_compiled,
[
"SELECT tests_number.num FROM tests_number WHERE " +
"tests_number.num <= %s EXCEPT ALL SELECT tests_number.num " +
"FROM tests_number WHERE tests_number.num >= %s"
],
)
self.assertEqual(params, [1, 8])
def test_contains_sql_query_case_insensitive_transform(self):
qs1 = Author.objects.filter(
name__upper__icontains="abc").values("name")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
if USING_DJANGO_3:
expected_sql = (
"SELECT tests_author.name FROM tests_author WHERE " +
"REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), " +
"REPLACE(REPLACE(REPLACE(CONCAT('(?i)', UPPER(%s)), " +
'"\\\\", "\\\\\\\\"), "%%", r"\\%%"), "_", r"\\_"))')
else:
expected_sql = (
"SELECT tests_author.name FROM tests_author WHERE " +
"REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), " +
"REPLACE(REPLACE(REPLACE(CONCAT('(?i)', (UPPER(%s))), " +
'"\\\\", "\\\\\\\\"), "%%", r"\\%%"), "_", r"\\_"))')
self.assertEqual(sql_compiled, expected_sql)
self.assertEqual(params, ("abc", ))
def test_get_combinator_sql_distinct_union_sql_generated(self):
"""
Tries union sql generator with distinct.
"""
qs1 = Number.objects.filter(num__lte=1).values("num")
qs2 = Number.objects.filter(num__gte=8).values("num")
qs4 = qs1.union(qs2)
compiler = SQLCompiler(qs4.query, self.connection, "default")
sql_compiled, params = compiler.get_combinator_sql("union", False)
self.assertEqual(
sql_compiled,
[
"SELECT tests_number.num FROM tests_number WHERE " +
"tests_number.num <= %s UNION DISTINCT SELECT " +
"tests_number.num FROM tests_number WHERE " +
"tests_number.num >= %s"
],
)
self.assertEqual(params, [1, 8])
def test_iexact_sql_query_case_insensitive_function_transform(self):
qs1 = Author.objects.filter(
name__upper__iexact=F("last_name")).values("name")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
if USING_DJANGO_3:
expected_sql = (
"SELECT tests_author.name FROM tests_author WHERE " +
"REGEXP_CONTAINS(UPPER(tests_author.last_name), " +
"CONCAT('^(?i)', CAST(UPPER(tests_author.name) AS STRING), '$'))"
)
else:
expected_sql = (
"SELECT tests_author.name FROM tests_author WHERE " +
"REGEXP_CONTAINS((UPPER(tests_author.last_name)), " +
"CONCAT('^(?i)', CAST(UPPER(tests_author.name) AS STRING), '$'))"
)
self.assertEqual(sql_compiled, expected_sql)
self.assertEqual(params, ())
def test_cot(self):
"""
Tests cot function on a column.
"""
q1 = Author.objects.values("num").annotate(num_cot=Cot("num"), )
compiler = SQLCompiler(q1.query, self.connection, "default")
sql_query, params = compiler.query.as_sql(compiler, self.connection)
self.assertEqual(
sql_query,
"SELECT tests_author.num, (1 / TAN(tests_author.num)) AS num_cot "
+ "FROM tests_author",
)
self.assertEqual(params, ())
def test_pi(self):
"""
Tests pi function applied to a column.
"""
q1 = Author.objects.filter(num=Pi()).values("num")
compiler = SQLCompiler(q1.query, self.connection, "default")
sql_query, params = compiler.query.as_sql(compiler, self.connection)
self.assertEqual(
sql_query,
"SELECT tests_author.num FROM tests_author WHERE tests_author.num "
+ "= (3.141592653589793)",
)
self.assertEqual(params, ())
def test_get_combinator_sql_union_and_difference_query_together(self):
"""
Tries sql generator with union of queryset with queryset of difference.
"""
qs1 = Number.objects.filter(num__lte=1).values("num")
qs2 = Number.objects.filter(num__gte=8).values("num")
qs3 = Number.objects.filter(num__exact=10).values("num")
qs4 = qs1.union(qs2.difference(qs3))
compiler = SQLCompiler(qs4.query, self.connection, "default")
sql_compiled, params = compiler.get_combinator_sql("union", False)
self.assertEqual(
sql_compiled,
[
"SELECT tests_number.num FROM tests_number WHERE " +
"tests_number.num <= %s UNION DISTINCT SELECT * FROM (" +
"SELECT tests_number.num FROM tests_number WHERE " +
"tests_number.num >= %s EXCEPT DISTINCT " +
"SELECT tests_number.num FROM tests_number " +
"WHERE tests_number.num = %s)"
],
)
self.assertEqual(params, [1, 8, 10])
def test_left(self):
"""
Tests left function applied to a column.
"""
q1 = Author.objects.values("num").annotate(first_initial=Left(
"name", 1), )
compiler = SQLCompiler(q1.query, self.connection, "default")
sql_query, params = compiler.query.as_sql(compiler, self.connection)
self.assertEqual(
sql_query,
"SELECT tests_author.num, SUBSTR(tests_author.name, %s, %s) AS " +
"first_initial FROM tests_author",
)
self.assertEqual(params, (1, 1))
def test_degrees(self):
"""
Tests degrees function on a column.
"""
q1 = Author.objects.values("num").annotate(
num_degrees=Degrees("num"), )
compiler = SQLCompiler(q1.query, self.connection, "default")
sql_query, params = compiler.query.as_sql(compiler, self.connection)
self.assertEqual(
sql_query,
"SELECT tests_author.num, ((tests_author.num) * 180 / " +
"3.141592653589793) AS num_degrees FROM tests_author",
)
self.assertEqual(params, ())
def test_right(self):
"""
Tests right function applied to a column.
"""
q1 = Author.objects.values("num").annotate(last_letter=Right(
"name", 1), )
compiler = SQLCompiler(q1.query, self.connection, "default")
sql_query, params = compiler.query.as_sql(compiler, self.connection)
self.assertEqual(
sql_query,
"SELECT tests_author.num, SUBSTR(tests_author.name, (%s * %s)) " +
"AS last_letter FROM tests_author",
)
self.assertEqual(params, (1, -1))
def test_cast_without_max_length(self):
"""
Tests cast field without max length.
"""
q1 = Author.objects.values("num").annotate(num_as_float=Cast(
"num", output_field=FloatField()), )
compiler = SQLCompiler(q1.query, self.connection, "default")
sql_query, params = compiler.query.as_sql(compiler, self.connection)
self.assertEqual(
sql_query,
"SELECT tests_author.num, CAST(tests_author.num AS FLOAT64) " +
"AS num_as_float FROM tests_author",
)
self.assertEqual(params, ())
def test_log(self):
"""
Tests log function applied to a column.
"""
q1 = Author.objects.values("num").annotate(log=Log("num", Value(10)))
compiler = SQLCompiler(q1.query, self.connection, "default")
sql_query, params = compiler.query.as_sql(compiler, self.connection)
self.assertEqual(
sql_query,
"SELECT tests_author.num, LOG(%s, tests_author.num) AS log FROM " +
"tests_author",
)
self.assertEqual(params, (10, ))
def test_radians(self):
"""
Tests radians function applied to a column.
"""
q1 = Author.objects.values("num").annotate(num_radians=Radians("num"))
compiler = SQLCompiler(q1.query, self.connection, "default")
sql_query, params = compiler.query.as_sql(compiler, self.connection)
self.assertEqual(
sql_query,
"SELECT tests_author.num, ((tests_author.num) * 3.141592653589793 "
"/ 180) AS num_radians FROM tests_author",
)
self.assertEqual(params, ())
def test_cast_with_max_length(self):
"""
Tests cast field with max length.
"""
q1 = Author.objects.values("name").annotate(name_as_prefix=Cast(
"name", output_field=CharField(max_length=10)), )
compiler = SQLCompiler(q1.query, self.connection, "default")
sql_query, params = compiler.query.as_sql(compiler, self.connection)
self.assertEqual(
sql_query,
"SELECT tests_author.name, SUBSTR(CAST(tests_author.name AS " +
"STRING), 0, 10) AS name_as_prefix FROM tests_author",
)
self.assertEqual(params, ())
def test_substr(self):
"""
Tests substr function applied to a column.
"""
q1 = Author.objects.values("name").annotate(
name_prefix=Substr("name", 1, 5))
compiler = SQLCompiler(q1.query, self.connection, "default")
sql_query, params = compiler.query.as_sql(compiler, self.connection)
self.assertEqual(
sql_query,
"SELECT tests_author.name, SUBSTR(tests_author.name, %s, %s) AS " +
"name_prefix FROM tests_author",
)
self.assertEqual(params, (1, 5))
def test_concatpair(self):
"""
Tests concatinating pair of columns.
"""
q1 = Author.objects.values("name").annotate(full_name=Concat(
"name", Value(" "), "last_name", output_field=CharField()), )
compiler = SQLCompiler(q1.query, self.connection, "default")
sql_query, params = compiler.query.as_sql(compiler, self.connection)
self.assertEqual(
sql_query,
"SELECT tests_author.name, CONCAT(IFNULL(tests_author.name, %s), "
+ "IFNULL(CONCAT(IFNULL(%s, %s), IFNULL(tests_author.last_name, " +
"%s)), %s)) AS full_name FROM tests_author",
)
self.assertEqual(params, ("", " ", "", "", ""))