Types pollution with iso-8859-1 oids and server-side parameters binding
От | Daniele Varrazzo |
---|---|
Тема | Types pollution with iso-8859-1 oids and server-side parameters binding |
Дата | |
Msg-id | CA+mi_8bQPwf8zwg7GeoqhvccNuze-hyDODACCL0Dj=x_kaE7Bw@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Types pollution with iso-8859-1 oids and server-side parameters binding
|
Список | 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
В списке pgsql-bugs по дате отправления: