Re: JDBC String to Bool spec

I guess you are right, the driver currently blindly converts incorrect
values to false, IMO it should accept only values allowed in postgresql
server since this is the PgJBDC driver, but is should also support a cast
of float, double and decimal as this is required by the jdbc spec.

@vlsi, @davecramer, what is your opinion about blindly convert incorrect
values to false? Should the driver accept this kind of behaviour?

For instance something like this is allowed (and there is even a test for
this):

CREATE TABLE testboolstring (a varchar(30));
INSERT INTO testboolstring VALUES('this is not true');

when execute rs.getBoolean(1), it gets false, but it should be an
cannotCastException.

Jorge Solórzano
me.jorsol.com

On Sun, Jan 15, 2017 at 7:25 PM, Kevin Wooten <kdubb(at)me(dot)com> wrote:

> Good find on this text. That’s what I couldn’t seem to find in the spec.
>
> Yes these cases are covered… but so are many other cases outside the JDBC
> spec; some that even Postgres itself doesn’t support. That’s kind of what
> I’m getting at. It seems that currently we’re just attempting to “convert
> everything” and I’m not sure that’s the appropriate approach when
> implementing a strict spec like JDBC.
>
> Also, the current conversion set isn’t normalized at all. When all else
> fails the “string -> boolean” conversion code then attempts a “string ->
> double” conversion and if that succeeds it then tests against “1.0” for
> true and everything else false. JDBC does seem to give at least some
> guidance here, suggesting that it should be a strict “1.0 -> true” or “0.0
> -> false” mapping as is implemented for “1 -> true” or “0 -> false”.
>
> I will reiterate that Postgres doesn’t support anything of the sort. It
> has a strict set of true and strict set of false values when converting
> from “string -> bool” anything else results in an error. Although, it does
> support more values than outlined in the text quoted below.
>
>
> On Jan 15, 2017, at 6:14 PM, Jorge Solórzano <jorsol(at)gmail(dot)com> wrote:
>
> BTW, the JDBC specs (or at least the javadocs
> <http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getBoolean%28int%29>)
> reads this:
>
> If the designated column has a datatype of CHAR or VARCHAR and contains a
>> "0" or has a datatype of BIT, TINYINT, SMALLINT, INTEGER or BIGINT and
>> contains a 0, a value of false is returned. If the designated column has
>> a datatype of CHAR or VARCHAR and contains a "1" or has a datatype of BIT,
>> TINYINT, SMALLINT, INTEGER or BIGINT and contains a 1, a value of true
>> is returned.
>>
>
> So this use case is already covered, but I think that having full postgres
> conversion is a better approach.
>
>
> Jorge Solórzano
> me.jorsol.com
>
> On Sun, Jan 15, 2017 at 6:59 PM, Jorge Solórzano <jorsol(at)gmail(dot)com> wrote:
>
>> Maybe we should support postgres conversion (
>> https://www.postgresql.org/docs/current/static/datatype-boolean.html)
>> similar to what is already on PgPreparedStatement and setObject.
>>
>> Can you please open an issue on github
>> <https://github.com/pgjdbc/pgjdbc/issues> to keep track of it?
>>
>> Jorge Solórzano
>> me.jorsol.com
>>
>> On Sun, Jan 15, 2017 at 5:54 PM, Kevin Wooten <kdubb(at)me(dot)com> wrote:
>>
>>> It’s not my use case. It seems to me the driver should either support
>>> JDBC/Java conversions (true/yes => true, everything else false) or Postgres
>>> conversions (true/on/t/1 => true, false/off/f/0 => false, everything else
>>> error).
>>>
>>> Currently it seems theres a hodgepodge of supported conversion not
>>> really defined by the spec or server support; unless they are defined
>>> somewhere that I’m not aware off.
>>>
>>> On Jan 15, 2017, at 4:42 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>>>
>>>
>>> On 15 January 2017 at 18:33, Kevin Wooten <kdubb(at)me(dot)com> wrote:
>>>
>>>> Actually this table isn’t what I’m looking for. Related to example I
>>>> provided below, there doesn’t seem to be a list of “acceptable values” when
>>>> converting a string to a boolean; only that “getBoolean” must support
>>>> conversion from VARCHAR/CHAR.
>>>>
>>>>
>>>>
>>> Ya I thought you were looking for something else. Not sure how you deal
>>> with your exact use case
>>>
>>>
>>>
>>> Dave Cramer
>>>
>>> davec(at)postgresintl(dot)com
>>> www.postgresintl.com
>>>
>>>
>>>> On Jan 15, 2017, at 4:29 PM, Kevin Wooten <kdubb(at)me(dot)com> wrote:
>>>>
>>>> Thanks… is there a reason those tables were dropped from the 4.2 spec
>>>> PDF?
>>>>
>>>> On Jan 15, 2017, at 4:26 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>>>>
>>>> http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jd
>>>> bc4.1-fr-spec.pdf?AuthParam=1484522754_78e98a772cf0c1f6c7f18ab76e324ff5
>>>>
>>>>
>>>> Has a table on page 211 if that is what you are looking for
>>>>
>>>> Dave Cramer
>>>>
>>>> davec(at)postgresintl(dot)com
>>>> www.postgresintl.com
>>>>
>>>> On 15 January 2017 at 18:20, Kevin Wooten <kdubb(at)me(dot)com> wrote:
>>>>
>>>>> Does anybody know where in the specification it details the
>>>>> required/acceptable conversions from string values to boolean? I cannot
>>>>> seem to find it in the PDF for 4.2.
>>>>>
>>>>> I am curious about some of the conversions that are done. For
>>>>> example, calling “ResultSet.getBoolean” on a text/varchar column with the
>>>>> value “1.0”. This conversion succeeds because the driver (both pgjdbc &
>>>>> ng) fallback to decoding the column as a double then converting that by
>>>>> testing it “== 1”; which seems valid but questionable since “!= 0” would
>>>>> also be valid, but vastly different.
>>>>>
>>>>> This is not allowed by Postgres (e.g. “SELECT “1.0”::bool;” results in
>>>>> an error) and I cannot find anything in JDBC as of yet.
>>>>>
>>>>> --
>>>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
>>>>> To make changes to your subscription:
>>>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>
>