Lists: | pgsql-bugs |
---|
From: | Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG: Unable to bind a null value typed as a UUID in a PreparedStatement |
Date: | 2018-03-28 13:26:48 |
Message-ID: | CAG2M1fdAFD3N8vZxp4TYLEYz4Rwu5f94ghafEErvHv387oWf8g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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".
See the code here: https://ideone.com/DKnqa9
When run, it gives the output below:
JDBC Driver Version: 42.2.1
PostgreSQL version: PostgreSQL 9.5.9 on x86_64-pc-linux-gnu, compiled by
gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
setNull(varchar) + setObject(null) => works
setNull(other) + setNull(other) => fails (ERROR: could not determine data
type of parameter $1)
setNull(other, uuid) + setNull(other, uuid) => fails (ERROR: could not
determine data type of parameter $1)
The third case (last line above) uses method PreparedStatement.setNull(pos,
type, typeName) and should work.
But in the current implementation of the driver, the
PreparedStatement.setNull(int, int, String) delegates to setNull(int, int)
so that specifying the type name has no effect.
--
From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> |
Cc: | Postgres Bug <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG: Unable to bind a null value typed as a UUID in a PreparedStatement |
Date: | 2018-03-28 13:59:26 |
Message-ID: | CAKFQuwYExm4G2Ejn6GDPjKuG-1Ayk8gvBiutrUpKn+QU76oX+Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
On Wed, Mar 28, 2018 at 6:26 AM, Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> wrote:
> 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".
> [...]
> But in the current implementation of the driver, the
> PreparedStatement.setNull(int, int, String) delegates to setNull(int, int)
> so that specifying the type name has no effect.
>
JDBC Driver bugs are off-topic for this list. They should be reported on
the project's GitHub site's Issues List.
David J.
From: | Dave Cramer <davecramer(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Rémi Aubel <remi(dot)aubel(at)gmail(dot)com>, Postgres Bug <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG: Unable to bind a null value typed as a UUID in a PreparedStatement |
Date: | 2018-03-28 14:11:55 |
Message-ID: | CADK3HH+z7hdV-85xwZTc=oKpB01fpdxVzD0XUV1B4KwLF=fVrA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Remi,
See https://github.com/pgjdbc/pgjdbc/pull/1160
Dave Cramer
On 28 March 2018 at 09:59, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:
> On Wed, Mar 28, 2018 at 6:26 AM, Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> wrote:
>
>> 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".
>> [...]
>> But in the current implementation of the driver, the
>> PreparedStatement.setNull(int, int, String) delegates to setNull(int, int)
>> so that specifying the type name has no effect.
>>
>
> JDBC Driver bugs are off-topic for this list. They should be reported on
> the project's GitHub site's Issues List.
>
> David J.
>
>
>
From: | Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> |
---|---|
To: | Dave Cramer <davecramer(at)gmail(dot)com> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Postgres Bug <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG: Unable to bind a null value typed as a UUID in a PreparedStatement |
Date: | 2018-03-28 14:25:25 |
Message-ID: | CAG2M1feYoNnnxNYL0TLDy2-smATrAk6jMHUvdw2u9tE=eVBs7Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Oh Thanks, Dave!
I had created the issue on GitHub (
https://github.com/pgjdbc/pgjdbc/issues/1161) and saw you marked it as
duplicate.
Le mer. 28 mars 2018 à 16:12, Dave Cramer <davecramer(at)gmail(dot)com> a écrit :
> Remi,
>
> See https://github.com/pgjdbc/pgjdbc/pull/1160
>
> Dave Cramer
>
> On 28 March 2018 at 09:59, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
>
>> On Wed, Mar 28, 2018 at 6:26 AM, Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> wrote:
>>
>>> 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".
>>> [...]
>>> But in the current implementation of the driver, the
>>> PreparedStatement.setNull(int, int, String) delegates to setNull(int, int)
>>> so that specifying the type name has no effect.
>>>
>>
>> JDBC Driver bugs are off-topic for this list. They should be reported
>> on the project's GitHub site's Issues List.
>>
>> David J.
>>
>>
>>
> --
From: | Dave Cramer <davecramer(at)gmail(dot)com> |
---|---|
To: | Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Postgres Bug <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG: Unable to bind a null value typed as a UUID in a PreparedStatement |
Date: | 2018-03-28 14:27:05 |
Message-ID: | CADK3HHL04oRUsW1phsqef4LsKnHv7nnR6_UU-FXkF=srwnKnjA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
@Remi,
You are welcome. It would be wonderful it you could test the fix ?
Dave Cramer
On 28 March 2018 at 10:25, Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> wrote:
> Oh Thanks, Dave!
> I had created the issue on GitHub (https://github.com/pgjdbc/
> pgjdbc/issues/1161) and saw you marked it as duplicate.
>
> Le mer. 28 mars 2018 à 16:12, Dave Cramer <davecramer(at)gmail(dot)com> a écrit :
>
>> Remi,
>>
>> See https://github.com/pgjdbc/pgjdbc/pull/1160
>>
>> Dave Cramer
>>
>> On 28 March 2018 at 09:59, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
>> wrote:
>>
>>> On Wed, Mar 28, 2018 at 6:26 AM, Rémi Aubel <remi(dot)aubel(at)gmail(dot)com>
>>> wrote:
>>>
>>>> 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".
>>>> [...]
>>>> But in the current implementation of the driver, the
>>>> PreparedStatement.setNull(int, int, String) delegates to setNull(int, int)
>>>> so that specifying the type name has no effect.
>>>>
>>>
>>> JDBC Driver bugs are off-topic for this list. They should be reported
>>> on the project's GitHub site's Issues List.
>>>
>>> David J.
>>>
>>>
>>>
>> --
>
>
>
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG: Unable to bind a null value typed as a UUID in a PreparedStatement |
Date: | 2018-03-28 14:30:50 |
Message-ID: | 20457.1522247450@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
=?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
regards, tom lane
From: | Dave Cramer <davecramer(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Rémi Aubel <remi(dot)aubel(at)gmail(dot)com>, Postgres Bug <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG: Unable to bind a null value typed as a UUID in a PreparedStatement |
Date: | 2018-03-28 14:38:30 |
Message-ID: | CADK3HHJ8F=9124gKE-FQdL6qBpvGDC2jC7wtCV=W5+jmcxwB2Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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
From: | Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> |
---|---|
To: | Dave Cramer <davecramer(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Postgres Bug <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG: Unable to bind a null value typed as a UUID in a PreparedStatement |
Date: | 2018-03-28 15:08:16 |
Message-ID: | CAG2M1ffsCfk3osHirUqozOMWVqiiiW5LWr_4FS3sc5R6wNthfA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
@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(at)gmail(dot)com> a écrit :
> 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
>
--
From: | Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> |
---|---|
To: | Dave Cramer <davecramer(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Postgres Bug <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG: Unable to bind a null value typed as a UUID in a PreparedStatement |
Date: | 2018-04-03 10:29:34 |
Message-ID: | CAG2M1fcyKG4vhuoRSNjEJvcuOdxGavV3pZDyowO2RttVdpKwbw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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(dot)aubel(at)gmail(dot)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(at)gmail(dot)com> a écrit :
>
>> 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
>>
> --
>
>
> --
From: | Dave Cramer <davecramer(at)gmail(dot)com> |
---|---|
To: | Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Postgres Bug <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG: Unable to bind a null value typed as a UUID in a PreparedStatement |
Date: | 2018-04-03 10:42:52 |
Message-ID: | CADK3HHLQ8Po-9c9YzjSbB3J5KY+ML0C=JQ76DjYgzAK9kk+76w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Hi Remi,
https://github.com/pgjdbc/pgjdbc/milestones approximately.
Dave Cramer
On 3 April 2018 at 06:29, Rémi Aubel <remi(dot)aubel(at)gmail(dot)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(dot)aubel(at)gmail(dot)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(at)gmail(dot)com> a
>> écrit :
>>
>>> 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
>>>
>> --
>>
>>
>> --
>
>
>
From: | Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> |
---|---|
To: | Dave Cramer <davecramer(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Postgres Bug <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG: Unable to bind a null value typed as a UUID in a PreparedStatement |
Date: | 2018-04-03 11:19:47 |
Message-ID: | CAG2M1feA=dA_whoGV=6DVTFCNHw8LCN+PNRDZs8ufAZk=Drktw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Thanks
Le mar. 3 avr. 2018 à 12:43, Dave Cramer <davecramer(at)gmail(dot)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(dot)aubel(at)gmail(dot)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(dot)aubel(at)gmail(dot)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(at)gmail(dot)com> a
>>> écrit :
>>>
>>>> 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
>>>>
>>> --
>>>
>>>
>>> --
>>
>>
>>
> --
From: | Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> |
---|---|
To: | Dave Cramer <davecramer(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Postgres Bug <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG: Unable to bind a null value typed as a UUID in a PreparedStatement |
Date: | 2018-04-25 14:15:36 |
Message-ID: | CAG2M1ffyA0TnSRHgvvcr4N=L37ZDYJzP+r_WThRZ4K=iZZZJ-w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Hi all,
Do you have any update about the release date for driver 42.2.3?
Rémi
Le mar. 3 avr. 2018 à 13:19, Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> a écrit :
> Thanks
>
> Le mar. 3 avr. 2018 à 12:43, Dave Cramer <davecramer(at)gmail(dot)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(dot)aubel(at)gmail(dot)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(dot)aubel(at)gmail(dot)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(at)gmail(dot)com> a
>>>> écrit :
>>>>
>>>>> 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
>>>>>
>>>> --
>>>>
>>>>
>>>> --
>>>
>>>
>>>
>> --
>
>
> --