On Tue, 3 May 2022 at 21:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I think it's operating exactly as designed. The parameter can only
> have one type, and the cast is telling it to assume that that type
> is "date".
The same problem was actually reported without that cast. The OP was
using a query such as:
UPDATE test289 SET num = $1, name = $2, ts = $3 WHERE ts::date = $3
$3 is used in two different contexts, a datetime and a date, and it
seems arbitrary that the date wins.
Actually, it doesn't seem to be the cast to decide the type. Adding a
timestamp cast and dropping the date one:
UPDATE test289 SET num = $1, name = $2, ts = $3::timestamp WHERE
ts::date = $3'
reproduces the same artifact. A self-contained, psql-only test is:
=# create table test289 (num int, name text, ts timestamp);
=# insert into test289 values (300, 'Fred', '2022-03-03 11:00:00');
=# prepare tmpstat as update test289 set num = $1, name = $2, ts =
$3::timestamp where ts::date = $3;
=# execute tmpstat ('301', 'Fred2', '2022-03-03 20:00:00');
=# select * from test289;
┌─────┬───────┬─────────────────────┐
│ num │ name │ ts │
├─────┼───────┼─────────────────────┤
│ 301 │ Fred2 │ 2022-03-03 00:00:00 │
└─────┴───────┴─────────────────────┘
It seems that the date type is chosen arbitrarily, possibly depending
on the way the parsed query is traversed building the plan?
> > I see why it happens... I don't think it's the right behaviour though.
>
> What do you think ought to happen? The same parameter somehow having
> different types in different places?
Looking at the above variation of the problem, maybe the right thing
to do would be to throw an error because the parameter can be
interpreted in different ways in different places? I seem to
understand, from David's example, that such an error is thrown, in
other contexts.
Another possibility is to fix the users. Many of them will see a
parametric query more like a C macro than like a C function, so
performing a literal replacement, because this is the behaviour they
get in psql typing:
UPDATE ..., ts = '2022-03-03 11:00:00' WHERE ts::date =
'2022-03-03 11:00:00'::date
In psycopg, we might document this difference as one of the glitches
that can be met moving from client- to server-side binding
(https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#server-side-binding).
-- Daniele