Обсуждение: [JDBC] JDBC String to Bool spec

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.

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

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.

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

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

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

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

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

On 2017-01-16 20:16, Jorge Solórzano 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.

Interestingly, the JDBC 4.2 specification documentation allows more types than the apidoc (see my previous email for a quote of the apidoc). I will see if I can get Lance Andersen to amend the apidoc.

The JDBC specification mentions conversion for: TINYINT, SMALLINT, INTEGER, BIGINT, REAL, FLOAT, DOUBLE, DECIMAL, NUMERIC, BIT, BOOLEAN, CHAR, VARCHAR, LONGVARCHAR (and vice versa), the apidoc doesn't mention the fixed and floating point types. Presumably this was added because you can also call getInt on a double, etc.

@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.

You're right, but the question is: will you break existing code if you apply this change?

On 2017-01-16 20:16, Jorge Solórzano 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.

Interestingly, the JDBC 4.2 specification documentation allows more types than the apidoc (see my previous email for a quote of the apidoc). I will see if I can get Lance Andersen to amend the apidoc.

The JDBC specification mentions conversion for: TINYINT, SMALLINT, INTEGER, BIGINT, REAL, FLOAT, DOUBLE, DECIMAL, NUMERIC, BIT, BOOLEAN, CHAR, VARCHAR, LONGVARCHAR (and vice versa), the apidoc doesn't mention the fixed and floating point types. Presumably this was added because you can also call getInt on a double, etc.

@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.

You're right, but the question is: will you break existing code if you apply this change?

You're right, but the question is: will you break existing code if you apply this change?

Mark

You're right, but the question is: will you break existing code if you apply this change?

Mark

I agree it should support all the types the JDBC spec says it should; and it does.  I’m focused on the CHAR/VARCHAR  to Boolean conversion.

To add to your example

CREATE TABLE testboolstring (a varchar(30));

INSERT INTO testboolstring VALUES('this is not true’);

>> ResultSet.getBoolean(1) returns FALSE

INSERT INTO testboolstring VALUES(‘1.0’);

>> ResultSet.getBoolean(1) returns TRUE

INSERT INTO testboolstring VALUES(2.2’);

>> ResultSet.getBoolean(1) returns FALSE

Since we now know the JDBC spec doesn’t support this (thanks to all of Mark Rotteveel’s sleuthing) & we know Postgres doesn’t support this.  It seems the “feature" should be removed or placed behind an option.  Postgres results in an error here and considering this is a driver tied to that database I look to that to clarify ambiguity. These examples should all result in an error.

I agree it should support all the types the JDBC spec says it should; and it does.  I’m focused on the CHAR/VARCHAR  to Boolean conversion.

To add to your example

CREATE TABLE testboolstring (a varchar(30));

INSERT INTO testboolstring VALUES('this is not true’);

>> ResultSet.getBoolean(1) returns FALSE

INSERT INTO testboolstring VALUES(‘1.0’);

>> ResultSet.getBoolean(1) returns TRUE

INSERT INTO testboolstring VALUES(2.2’);

>> ResultSet.getBoolean(1) returns FALSE

Since we now know the JDBC spec doesn’t support this (thanks to all of Mark Rotteveel’s sleuthing) & we know Postgres doesn’t support this.  It seems the “feature" should be removed or placed behind an option.  Postgres results in an error here and considering this is a driver tied to that database I look to that to clarify ambiguity. These examples should all result in an error.