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

[TeMcGee7]

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 ""?

[Mariusz Felisiak]

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.