Re: JDBC String to Bool spec

On Jan 16, 2017, at 12:16 PM, Jorge Solórzano <jorsol@gmail.com> wrote:

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@me.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@gmail.com> wrote:

BTW, the JDBC specs (or at least the javadocs) 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@gmail.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 to keep track of it?

Jorge Solórzano

me.jorsol.com

On Sun, Jan 15, 2017 at 5:54 PM, Kevin Wooten <kdubb@me.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@fastcrypt.com> wrote:

On 15 January 2017 at 18:33, Kevin Wooten <kdubb@me.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@postgresintl.com

www.postgresintl.com

 

On Jan 15, 2017, at 4:29 PM, Kevin Wooten <kdubb@me.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@fastcrypt.com> wrote:

http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf?AuthParam=1484522754_78e98a772cf0c1f6c7f18ab76e324ff5 

Has a table on page 211 if that is what you are looking for

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 15 January 2017 at 18:20, Kevin Wooten <kdubb@me.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc