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

Thanks

Le mar. 3 avr. 2018 à 12:43, Dave Cramer <davecramer@gmail.com> a écrit :

Hi Remi,

https://github.com/pgjdbc/pgjdbc/milestones approximately.

Dave Cramer

On 3 April 2018 at 06:29, Rémi Aubel <remi.aubel@gmail.com> wrote:

Hello,

Do you know when the next driver release (including fix https://github.com/pgjdbc/pgjdbc/pull/1160) is expected?

Rémi 

Le mer. 28 mars 2018 à 17:08, Rémi Aubel <remi.aubel@gmail.com> a écrit :

@Dave,

I have just tested it. It works fine with setNull(_, 1111, "uuid").

Thanks again :-)

Rémi

Le mer. 28 mars 2018 à 16:38, Dave Cramer <davecramer@gmail.com> a écrit :

On 28 March 2018 at 10:30, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Rémi Aubel <remi.aubel@gmail.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

--

--

--