Re: BUG: Unable to bind a null value typed as a UUID in a PreparedStatement

On 28 March 2018 at 10:30, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> =?UTF-8?Q?R=C3=A9mi_Aubel?= <remi(dot)aubel(at)gmail(dot)com> writes:
> > I need to bind a UUID parameter which may be null in a statement like
> > "select * from test table where ? is null or ? = c_uuid".
> > Whatever approach I use, the driver rejects my request with "ERROR: could
> > not determine data type of parameter $1".
>
> Some experimentation suggests that it'd probably work if you wrote the
> clauses in the other order:
>
> regression=# create table test_table (c_uuid uuid);
> CREATE TABLE
> regression=# prepare foo as select * from test_table where $1 is null or
> $1 = c_uuid;
> ERROR: could not determine data type of parameter $1
> LINE 1: prepare foo as select * from test_table where $1 is null or ...
> ^
> regression=# prepare foo as select * from test_table where $1 = c_uuid or
> $1 is null;
> PREPARE
>
> In an ideal world, perhaps the order of the parameter references would not
> matter, but AFAICS making that work would be mighty hard. For now, PG's
> parser wants to resolve the type of an otherwise-unlabeled parameter
> symbol the first time it sees it --- and the context "IS NULL" offers
> no clue what type it should be.
>
> Alternatively, you could force the issue with an explicit cast in the
> text of the query:
>
> regression=# prepare foo2 as select * from test_table where $1::uuid is
> null or $1 = c_uuid;
> PREPARE
>

Tom,

This is just a simple example of a bigger problem. One for which there is a
solution in the driver which was not implemented.

I have implemented it now.

Thanks,

Dave