Prepared_stmt.fetch() should explicitly either return None or an empty list

[jaharnum]

• asyncpg version: 0.18.3
• PostgreSQL version: 11
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?: Install is local
• Python version: 3.7.3
• Platform: Windows 10
• 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?: Have not attempted at this time

This is a great help, thank you for this module!

Since the doc says fetch() returns a list of Records, I initially tried to use it to check if something exists in my database by checking it for a len() of 0. However, this produced unexpected results (but no clear error), so I went to check the documentation again.

While fetchval and fetchrow both explicitly return None if __bind_execute does not return any data, it's not made clear what will be returned if the data is not found in the general fetch() method. Protocol.bind_execute doesn't clearly state this either.

I think it would make most sense to return an empty list, but if it returns None instead that also makes sense with the result of that function 'family.' Either way it would be great to make this explicit in the method itself!

[elprans]

Yeah, this is an oversight. Returning an empty list is better.

[elprans]

I'm struggling to reproduce the None return here. Can you provide a snippet of code that triggers this?

[jaharnum]

You can actually see where this happens in the asyncpg code itself, prepared_stmt.py

@connresource.guarded
async def fetch(self, *args, timeout=None):
    r"""Execute the statement and return a list of :class:`Record` objects.
    :param str query: Query text
    :param args: Query arguments
    :param float timeout: Optional timeout value in seconds.
    :return: A list of :class:`Record` instances.
    """
    data = await self.__bind_execute(args, 0, timeout)
    return data

compare to fetchval which makes it explicit that None should be returned if __bind_execute() isn't successful:

@connresource.guarded
async def fetchval(self, *args, column=0, timeout=None):
    """Execute the statement and return a value in the first row.
    :param args: Query arguments.
    :param int column: Numeric index within the record of the value to
                       return (defaults to 0).
    :param float timeout: Optional timeout value in seconds.
                        If not specified, defaults to the value of
                        ``command_timeout`` argument to the ``Connection``
                        instance constructor.
    :return: The value of the specified column of the first record.
    """
    data = await self.__bind_execute(args, 1, timeout)
    if not data:
        return None
    return data[0][column]

__bind_execute also doesn't make clear what will be returned on failure:

  async def __bind_execute(self, args, limit, timeout):
    protocol = self._connection._protocol
    try:
        data, status, _ = await protocol.bind_execute(
            self._state, args, '', limit, True, timeout)
    except exceptions.OutdatedSchemaCacheError:
        await self._connection.reload_schema_state()
        # We can not find all manually created prepared statements, so just
        # drop known cached ones in the `self._connection`.
        # Other manually created prepared statements will fail and
        # invalidate themselves (unfortunately, clearing caches again).
        self._state.mark_closed()
        raise
    self._last_status = status
    return data

I actually can't recall if it actually returns None or if it's just undefined behaviour, I just know it didn't return an empty list.

[elprans]

__bind_execute always returns a list. fetchval is returning None on empty result intentionally.