[Django] #33055: Django - Custom Join's F('') includes single quotes in column reference, causing Postgres to think it's string and not a column reference

6 views

Skip to first unread message

Django

unread,

Aug 25, 2021, 1:40:20 PM8/25/21

to django-...@googlegroups.com

#33055: Django - Custom Join's F('') includes single quotes in column reference,
causing Postgres to think it's string and not a column reference
-------------------------------------+-------------------------------------
Reporter: TeMcGee7 | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I've created a custom join that allows a queryset to join onto a custom
Postgres function. The parameters for this function require an outer ref
to the overall query to be passed as part of the parameter list. When
Django does this for an F(''), it includes single quotes around the double
quotes such that postgres believe's the parameter is a string, not a
reference to the outer alias.

Custom Join:

{{{

def join_to_function(table, function_model, table_field, function_field,
queryset, alias, table_function_params):
foreign_object = ForeignObject(to=function_model,
on_delete=DO_NOTHING, from_fields=[None], to_fields=[None], rel=None)
foreign_object.opts = Options(table._meta)
foreign_object.opts.model = table
foreign_object.get_joining_columns = lambda: ((table_field,
function_field),)

# table._meta.db_table is passed twice, once as the base table name,
and once as the parent alias
join = TableToFunctionJoin(
table._meta.db_table, table._meta.db_table,
alias, "LEFT JOIN", foreign_object, True,
table_function_params=table_function_params, queryset=queryset)

queryset.query.join(join)

# hook for set alias
join.table_alias = alias
queryset.query.external_aliases[alias] = alias

return queryset

class TableToFunctionJoin(Join):
def __init__(self, table_name, parent_alias, table_alias, join_type,
join_field, nullable, filtered_relation=None,
table_function_params=None, queryset=None):
super().__init__(table_name, parent_alias, table_alias, join_type,
join_field, nullable, filtered_relation)
self.table_function_params = table_function_params
self.queryset = queryset

def as_sql(self, compiler, connection):
# Sets up the on clause '{join_info} on lhs = rhs'
# lhs / rhs come from a tuple of joinable columns
for (lhs_col, rhs_col) in self.join_cols:
on_clause_sql = '{}.{} = {}.{}'.format(
self.parent_alias,
lhs_col,
self.table_alias,
rhs_col,
)

# Generates specific CAST() methods for function as Postgres
interprets Python types differently
function_placeholders = 'CAST(%s as bigint), %s, CAST(%s as
text[]), %s'

# Resolves any instances of F('table__field')
# Requires queryset object as the Query class
# contains the attribute resolve_ref used to find the alias
resolved_params = []
for param in self.table_function_params:
if isinstance(param, F):
resolved_param =
param.resolve_expression(self.queryset.query)
else:
resolved_param = param
resolved_params.append(resolved_param)

# Create the actual parameters,
table_function_placeholders = []
table_function_params = []
for param in resolved_params:
if hasattr(param, 'as_sql'):
param_sql, param_params = param.as_sql(compiler,
connection)
table_function_params.append(param_sql)
else:
table_function_params.append(param)

sql = '{} {}({}) {} ON ({})'.format(
self.join_type,
compiler.quote_name_unless_alias(self.table_name),
function_placeholders,
self.table_alias,
on_clause_sql
)

return sql, table_function_params
}}}

This function (join_to_function) is given the following function
parameters:

{{{
function_parameters = [
... set of parameters ...
F('item_id'),
]
}}}

This generates the following SQL

{{{
SELECT
... fields to select on ...
FROM "metric_data"
LEFT OUTER JOIN "upwards_link_traversal"(.. other parameters,
'"metric_data"."item_id"') traversal
ON (metric_data.item_id = traversal.child_id)
WHERE
... where clause ...
}}}

The issue is that Django compiles the F('item_id') into
'"generated_metric_data_time_series"."service_item_id"' which includes
single quotes around the column names. This causes Postgres to throw a
type error as the column it should be referencing is an int id field, not
a string being passed in.

The following error is thrown:

{{{
function upwards_link_traversal(bigint, text, text[], integer) does not
exist
}}}

text is Postgres is interpreting instead of the column reference to the
outer queries item_id field.

Is there a solution to remove the quotes without doing something like a
regex match and replacing the '' with ""?

--
Ticket URL: <https://code.djangoproject.com/ticket/33055>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Aug 25, 2021, 2:11:43 PM8/25/21

to django-...@googlegroups.com

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => invalid

Comment:

Thanks for the report, however Trac is not a support channel. By the way,
you'll reach a wider audience if you write to the
https://forum.djangoproject.com/ or the DevelopersMailingList about your
ideas.