search

julianhyde / sqlline

Shell for issuing SQL to relational databases via JDBC
BSD 3-Clause "New" or "Revised" License
620 stars 146 forks source link

Boolean values stored as 1 or 0 in database are always `false` when using JSON output format #347

Open 0x2615 opened 5 years ago

0x2615 commented 5 years ago

Example data:

name is_external
one 1
two 0

When we query this data with CSV output format, the results contain 1 or 0 in the is_external field, this is the raw data from the database.

When using the JSON output format, the is_external field is always false.

It looks like the issue is with this code: https://github.com/julianhyde/sqlline/blob/master/src/main/java/sqlline/JsonOutputFormat.java#L70

If value is "1", it will evaluate to false.

snuyanzin commented 5 years ago

@SenatorSupes thanks for the issue. Could you please provide a test to reproduce it or clarify? I tried to reproduce with data from csv sample file with content

NAME,IS_EXTERNAL
"one",1
"two",0

but with any format the behavior is the same (json, csv, table).

I do not understand how you receive 0, 1 with csv format and false with json format.

I also tried a bit different content like

NAME:string,IS_EXTERNAL:boolean
"one",1
"two",0
"three",true
"four",false

here for the row "three",true everywhere (csv, json, table output formants) will be true and for the others false

julianhyde commented 5 years ago

I don't understand how a "boolean value" can be "stored as 1 or 0 in database". And actually it doesn't matter how it is stored.

If the JDBC driver reports that the column has type Types.BOOLEAN then ResultSet.getBoolean should work, ResultSet.getObject should return a java.lang.Boolean value, etc.

If the JDBC driver doesn't say that it has type Types.BOOLEAN then it's not a boolean column as far as sqlline is concerned.

0x2615 commented 5 years ago

Thanks for your replies @snuyanzin and @julianhyde.

I'm using this JDBC driver: https://www.cloudera.com/downloads/connectors/impala/jdbc/2-6-12.html

I'll look further into this to see if I can determine if, as you have suggested, the driver is to blame.

jxnu-liguobin commented 1 year ago

Hi @julianhyde , Why is there no Boolean here?https://github.com/julianhyde/sqlline/blob/ad7985dc050cee0b944de979fcb23c15338c7c9a/src/main/java/sqlline/Rows.java#L260 Did I miss anything? Thank you.

julianhyde commented 1 year ago

I guess that getString works fine for BOOLEAN values.