id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
33055	Django - Custom Join's F('') includes single quotes in column reference, causing Postgres to think it's string and not a column reference	TeMcGee7	nobody	"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 """"?"	Bug	closed	Database layer (models, ORM)	3.2	Normal	invalid			Unreviewed	0	0	0	0	0	0