Re: MERGE and parsing with prepared statements

On Thu, 14 Jul 2022, 18:26 Justin Pryzby, <pryzby(at)telsasoft(dot)com> wrote:
>
> We've used INSERT ON CONFLICT for a few years (with partitions as the target).
> That's also combined with prepared statements, for bulk loading.
>
> I was looking to see if we should use MERGE (probably not, but looking anyway).
> And came across this behavior. I'm not sure if it's any issue.
>
> CREATE TABLE CustomerAccount (CustomerId int, Balance float);
>
> PREPARE p AS
> MERGE INTO CustomerAccount CA
> USING (SELECT $1 AS CustomerId, $2 AS TransactionValue) AS T
> ON CA.CustomerId = T.CustomerId
> WHEN NOT MATCHED THEN
> INSERT (CustomerId, Balance)
> VALUES (T.CustomerId, T.TransactionValue)
> WHEN MATCHED THEN
> UPDATE SET Balance = Balance + TransactionValue;
>
> ERROR: operator does not exist: integer = text
> LINE 3: ON CA.CustomerId = T.CustomerId
>
> Why is $1 construed to be of type text ?

The select statement that generates the row type of T `(select $1 CID,
$2 TxV) AS T` does not put type bounds on the input parameters, so it
remains `unknown` for the scope of that subselect. Once stored into
the row type, the type of that column defaults to text, as a row type
should not have 'unknown'-typed columns.

You'll see the same issue with other subselects that select input
parameters without casts, such as `select a from (select $1 a) A where
A.a = 1;`. It's a pre-existing issue that has popped up earlier, and I
think it's not something we've planned to fix in backbranches.

Kind regards,

Matthias van de Meent