Types pollution with unknown oids and server-side parameters binding

From: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
To: pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Types pollution with unknown oids and server-side parameters binding
Date: 2022-05-03 19:10:20
Message-ID: CA+mi_8bQPwf8zwg7GeoqhvccNuze-hyDODACCL0Dj=x_kaE7Bw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello,

A problem shown in https://github.com/psycopg/psycopg/discussions/289

In the query:

UPDATE test289 SET num = $1, name = $2, ts = $3 WHERE ts::date = $3::date

passing a string with unknown oid as param $3 results in the column ts
receiving only the date part. Looks like the cast needed on the param
in the WHERE gets propagated to the other occurrences of the same
parameter.

Repro:

```psql
piro=# create table test289 (num int, name text, ts timestamp);
CREATE TABLE
piro=# insert into test289 values (300, 'Fred', '2022-03-03 11:00:00');
INSERT 0 1
piro=# insert into test289 values (200, 'Barney', '2022-03-02 11:00:00');
INSERT 0 1
piro=# select * from test289;
┌─────┬────────┬─────────────────────┐
│ num │ name │ ts │
├─────┼────────┼─────────────────────┤
│ 300 │ Fred │ 2022-03-03 11:00:00 │
│ 200 │ Barney │ 2022-03-02 11:00:00 │
└─────┴────────┴─────────────────────┘
(2 rows)
```

```python
import psycopg
conn = psycopg.connect(DSN, autocommit=True)
conn.pgconn.exec_params(
b'UPDATE test289 SET num = $1, name = $2, ts = $3 WHERE ts::date =
$3::date',
[b"301", b"Fred2", b"2022-03-03 20:00:00"], [21, 0, 0])
```

```psql
piro=# select * from test289;
┌─────┬────────┬─────────────────────┐
│ num │ name │ ts │
├─────┼────────┼─────────────────────┤
│ 200 │ Barney │ 2022-03-02 11:00:00 │
│ 301 │ Fred2 │ 2022-03-03 00:00:00 │ <<< should have been time 20:00
└─────┴────────┴─────────────────────┘
(2 rows)
```

This doesn't happen if the parameter type is specified (e.g. using
[21, 0, 1114] as OIDs array) or if the type of param 3 is made
understood as timestamp, e.g. with $3::timestamp::date in the WHERE
condition, or if the timestamp value is copied and used in separate
placeholders $3 and $4).

I see why it happens... I don't think it's the right behaviour though.

-- Daniele

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2022-05-03 19:55:22 Re: Types pollution with unknown oids and server-side parameters binding
Previous Message Van Droogenbroeck David 2022-05-03 08:41:01 Fw: bug in postgres 14.2