Asyncpg: jsonb: Automatic JSON conversion fails when values are None

[feluxe]

• asyncpg version: 0.18.3
• PostgreSQL version: 11.2
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?: I use the latest postgres docker image.
• Python version: 3.7.2
• Platform: linux
• Do you use pgbouncer?: Not that I know of.
• Did you install asyncpg with pip?: With pipenv.
• If you built asyncpg locally, which version of Cython did you use?:
• Can the issue be reproduced under both asyncio and
  uvloop?: I don't think this is related to asyncio/uvloop.

Hi there,

when using automatic JSON conversion, conversions fail for values that are None.

Exaples

I have no issues if I dump the values manually. This test passes:

    from json import dumps

    query = "INSERT INTO records (jsonbcol) VALUES ($1), ($2), ($3)"
    await conn.fetch(query, dumps(True), dumps(None), dumps(3))

    query = "SELECT * FROM records WHERE (jsonbcol = $1)"
    rs = await conn.fetch(query, dumps(None))

    assert len(rs) == 1

It also works if I use conn.set_type_codec() with an int. This test passes as well:

    from json import dumps, loads

    await pg.conn.set_type_codec(
        'jsonb',
        encoder=dumps,
        decoder=loads,
        schema='pg_catalog',
    )

    query = "INSERT INTO records (jsonbcol) VALUES ($1), ($2), ($3)"
    await conn.fetch(query, True, None, 3)

    query = "SELECT * FROM records WHERE (jsonbcol = $1)"
    rs = await conn.fetch(query, 3)

    assert len(rs) == 1

But if I use conn.set_type_codec() combined with a value that is None the test fails:

    from json import dumps, loads

    await pg.conn.set_type_codec(
        'jsonb',
        encoder=dumps,
        decoder=loads,
        schema='pg_catalog',
    )

    query = "INSERT INTO records (jsonbcol) VALUES ($1), ($2), ($3)"
    await pg.conn.fetch(query, True, None, 3)

    query = "SELECT * FROM records WHERE (jsonbcol = $1)"
    rs = await pg.conn.fetch(query, None)

    assert len(rs) == 1

[eirnym]

Have you tried to put type in query?

SELECT * FROM records WHERE (jsonbcol = $1::jsonb)

[elprans]

This is expected. None inputs are stored as NULL in the database. Your WHERE condition is thus false.

[feluxe]

@eirnym I tried, it doesn't help.

@elprans Thanks for your reply. I'm confused...

If I insert True and None into a jsonb column, shouldn't set_type_codec() convert this into json types 'true', 'null'? If this is the case why can't I select the rows that contain 'null' with fetch("SELECT * FROM records WHERE (jsonbcol = $1)", None)? It works in example 1) why doesn't it work in example 3)?

[elprans]

@feluxe None values are not passed to the codec and are always encoded as SQL NULL, whereas when you dump manually you get JSON 'null', which is not the same.