Opened 3 years ago
Closed 3 years ago
#33055 closed Bug (invalid)
Django - Custom Join's F('') includes single quotes in column reference, causing Postgres to think it's string and not a column reference
Reported by: | TeMcGee7 | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 3.2 |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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 ""?
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.
Closing per TicketClosingReasons/UseSupportChannels.