`copy_from_query` does not handle `NoneType` parameters correctly

[fobispotc]

• asyncpg version: 0.29
• PostgreSQL version: 14
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?:
• Python version: 3.12.5
• Platform: linux
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: yes
• If you built asyncpg locally, which version of Cython did you use?: n/a
• Can the issue be reproduced under both asyncio and uvloop?: yes

While using copy_from_query command, the handling of None values for the parameters is not processed correctly. The mogrify function in utils looks like it fails to convert the python NoneType into correponding NULL values.

One example could be:

SELECT $1::TEXT IS NULL OR $1 = 'foo'

This would return TRUE if $1 is set to NULL or if $1 is of value foo.. This works well when using fetchrow or similar commands, but fails with the copy_from _query command.

The error reported by Postgres is:

2024-09-08 14:42:51.918 PDT [2489056] srs@srs ERROR:  syntax error at or near "::" at character 19
2024-09-08 14:42:51.918 PDT [2489056] srs@srs STATEMENT:  COPY (
        SELECT ::TEXT IS NULL OR  = 'foo'
        ) TO STDOUT (FORMAT 'csv', HEADER True)

As you can see the parameter is replaced by ::TEXT and the second one to ` when it should've been:NULL` in BOTH cases.

[fobispotc]

The fix is simple, at the end of the _mogrify function in the asyncpg/utils directory, change the return to:

    return re.sub(
        r"\$(\d+)\b",
        lambda m: (
            textified[int(m.group(1)) - 1]
            if textified[int(m.group(1)) - 1] is not None
            else "NULL"
        ),
        query,
    )

[fobispotc]

PR added here: https://github.com/MagicStack/asyncpg/pull/1180