getBoolean returns false when value is 1

[Nick]

MySQL returns 1 for true, but it appears that ebean's getBoolean does not handle this correctly.

SqlQuery sqlQuery = Ebean.createSqlQuery("SELECT 1 IS NOT NULL AS `ISNT_NULL`");
SqlRow row = sqlQuery.findUnique();
Boolean bool = row.getBoolean("ISNT_NULL");

The boolean is coming back as false. Looks like it is using a transform that is incorrect for value's type.

public static Boolean toBoolean(Object value, String dbTrueValue) {
    if(value == null) {
        return null;
    } else if(value instanceof Boolean) {
        return (Boolean)value;
    } else {
        String s = value.toString();
        return Boolean.valueOf(s.equalsIgnoreCase(dbTrueValue));
    }
}

[Daryl Stultz]

On Fri, Aug 26, 2016 at 12:44 PM, Nick <nicks...@gmail.com> wrote:

MySQL returns 1 for true, but it appears that ebean's getBoolean does not handle this correctly.

The interpretation of true and false can be configured, for example:

ebean.databaseBooleanFalse=0

ebean.databaseBooleanTrue=1

/Daryl

[Nick]

Why not use the connector/driver's ResultSet implementation so that users don't have to specify database-specific properties?

[Daryl Stultz]

On Fri, Aug 26, 2016 at 1:09 PM, Nick <nicks...@gmail.com> wrote:

Why not use the connector/driver's ResultSet implementation so that users don't have to specify database-specific properties?

I've been working on the same project for ten years. It was originally on Oracle and 1 and 0 were used for true/false. I don't know why and I don't know if that particular version of Oracle had a proper boolean data type. We're now on PostgreSQL which does have a proper boolean data type but because we have all of our tables already using 1 and 0 we opted to configure Ebean to use 1 and 0. So Ebean is flexible in its configuration and I'm letting you know that option exists.

Perhaps there is an argument to be made for selecting a default interpretation for a database vendor, but wouldn't go with that of a particular ResultSet implementation. You could be in for a surprise if you changed drivers.

Ok, I've gone back and reread your original post. I was thinking about @Entity mapping, not SqlQuery. It appears that the ResultSet is not returning Boolean. The last part "return Boolean.valueOf(s.equalsIgnoreCase(dbTrueValue));" is the part that is configurable as I described. I don't know what the default it for MySQL.

/Daryl

[Nick]

> https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getBoolean(int)

MySQL uses 0,1 and looks like java's default is 0 and 1 as well. I don't know what the right solution is, but I think it's strange to have to configure that implementation detail, and I was in for a surprise when running the raw query gave me the expected value but my API returned the wrong value. 

Strangely never had an issue with entity mappings though, only with the SqlRow.getBoolean.

I will use the configuration option though, thank you for pointing it out.

[Daryl Stultz]

On Fri, Aug 26, 2016 at 1:33 PM, Nick <nicks...@gmail.com> wrote:

> https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getBoolean(int)

Strangely never had an issue with entity mappings though, only with the SqlRow.getBoolean.

Your subject says "when value is 1" but the expression is  "SELECT 1 IS NOT NULL" which looks like "true". What does MySQL report if you run this query in a console client? 1?

/Daryl

[Nick]

> What does MySQL report if you run this query in a console client? 1?

Yes, MySQL returns `1` for true

[Daryl Stultz]

On Fri, Aug 26, 2016 at 1:52 PM, Nick <nicks...@gmail.com> wrote:

> What does MySQL report if you run this query in a console client? 1?
Yes, MySQL returns `1` for true

Ok, I have to yield to Rob then, he would know better.

/Daryl

[Rob Bygrave]

I'll have a look at this tonight (produce a test case).  

I suspect this is case of whether the number 1 should be treated as boolean true.  For me, yes I think it should and currently is not and hence that sounds like a bug.

Note: There is an additional design question regarding if there should also be the option of using a "RowMapper" approach like Spring JdbcTemplate which gives us programmatic control over reading from the ResultSet (and the potential type conversion at that point). We don't have that level of control using SqlRow at the moment and perhaps it would be good to have that.

Cheers, Rob.

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Rob Bygrave]

Logged as https://github.com/ebean-orm/ebean/issues/817

Treated as a bug (numeric 1 should be treated as boolean true ... for DB's that do not have native boolean type).

Fixed in 8.2.3

Cheers, Rob.