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
