search

adeze / starschema-bigquery-jdbc

Automatically exported from code.google.com/p/starschema-bigquery-jdbc
0 stars 1 forks source link

Java error when returned record has NULL in FLOAT column. #13

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
What steps will reproduce the problem?
1.  Create test_issue_jdbc.csv as: 
CAL_MONTH,OUTLOOK_QTY
2013-MAR03,15.001
2013-FEB,
2013-JAN,20

2.  Create new table in Big Query as:
Create and Import Screen 
- Choose Job Template:  Skip  

- Choose Destination
Dataset ID:  <select any>
Table ID:  test_issue_jdbc

- Select Data
Choose file test_issue_jdbc.csv

- Specify Schema
Schema:  CAL_MONTH: string,FORECAST_QTY:float

- Advanced Options
Field delimiter:  ,
Header rows to skip:  1

Submit

4.  View the Table Definition, should be:
Schema
CAL_MONTH   STRING  NULLABLE    Describe this field...
FORECAST_QTY    FLOAT   NULLABLE    Describe this field...
***  Note that the FLOAT field is NULLABLE.

5.  Run query from BQ Console:
SELECT * FROM [<data_set_id>.test_issue_jdbc] 
and note the results:

Row CAL_MONTH   FORECAST_QTY     
1   2013-MAR03  15.001   
2   2013-FEB    null     
3   2013-JAN    20.0

4.  Run query from SQuireLSQL (or any tool) using starschema as driver and note 
results:
2013-MAR03  15.001
2013-FEB    <Error>
2013-JAN    20.0

Also note from SQuireLSQL logs:
2014-09-10 11:11:47,675 [Thread-7] INFO  
net.sourceforge.squirrel_sql.fw.util.log.SystemOutToLog  - 1620962 [Thread-7] 
INFO net.starschema.clouddb.jdbc.BQStatementRoot  - Executing Query: SELECT 
*FROM [zPlayground.test_issue_jdbc]
2014-09-10 11:11:47,684 [Thread-7] ERROR 
net.sourceforge.squirrel_sql.fw.sql.ResultSetReader  - Error reading column 
data, column index = 2
net.starschema.clouddb.jdbc.BQSQLException: java.lang.NumberFormatException: 
empty String
    at net.starschema.clouddb.jdbc.BQForwardOnlyResultSet.getDouble(BQForwardOnlyResultSet.java:761)
    at net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeDouble.readResultSet(DataTypeDouble.java:413)
    at net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.CellComponentFactory.readResultSet(CellComponentFactory.java:488)
    at net.sourceforge.squirrel_sql.fw.sql.ResultSetReader.doContentTabRead(ResultSetReader.java:611)
    at net.sourceforge.squirrel_sql.fw.sql.ResultSetReader.readRow(ResultSetReader.java:183)
    at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.createRow(ResultSetDataSet.java:213)
    at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet._setResultSet(ResultSetDataSet.java:179)
    at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.setSqlExecutionTabResultSet(ResultSetDataSet.java:105)
    at net.sourceforge.squirrel_sql.client.session.mainpanel.SQLExecutionHandler.sqlResultSetAvailable(SQLExecutionHandler.java:410)
    at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.processResultSet(SQLExecuterTask.java:542)
    at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.processQuery(SQLExecuterTask.java:407)
    at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.run(SQLExecuterTask.java:205)
    at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82)
    at java.lang.Thread.run(Thread.java:695)
Caused by: java.lang.NumberFormatException: empty String
    at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:992)
    at java.lang.Double.parseDouble(Double.java:510)
    at net.starschema.clouddb.jdbc.BQForwardOnlyResultSet.getDouble(BQForwardOnlyResultSet.java:758)
    ... 13 more

What is the expected output? What do you see instead?
1.  Null value for FLOAT should not generate ERROR in Java
2.  Null value should be returned to application

What version of the product are you using? On what operating system?
1.  1.4
2.  MAC and Linux

Please provide any additional information below.

Original issue reported on code.google.com by dougstou...@motorola.com on 10 Sep 2014 at 4:17

GoogleCodeExporter commented 9 years ago 
Hello,

float type can't be null. Float object can be null.
Please refer to: 
http://stackoverflow.com/questions/5107368/how-to-null-a-variable-of-type-float

We return double / int / float types, which can't be null. 
A possible fix could be to convert these to 0 values, which you can do in 
bigquery as well.

Original comment by guni...@starschema.net on 10 Sep 2014 at 10:34

GoogleCodeExporter commented 9 years ago 
BigQuery allows them to be nullable and allows nulls.  Google BQ allows null to 
be loaded, and the BQ Console returns null value when query.  Simba ODBC driver 
returns null as well.

Note:  null does not have the same meaning as 0.  null means no value has been 
attributed to the record whereas 0 means a value has been definitively set.  We 
perform different operations on this record from a reporting perspective if the 
value is null.

Attachment 1:  Screenshot of BQ Schema with column defined as Float and set as 
Nullable.
Attachment 2:  Screenshot of BQ Query returning null for Float values.

Original comment by dougstou...@motorola.com on 10 Sep 2014 at 10:57

Attachments:

GoogleCodeExporter commented 9 years ago 
Update on this issue.  The driver IS able to identify FLOAT records with null 
via a condition statement.  It just won't return it as a result set.  There 
seems to be a Driver side requirement or definition that it is Not Null to be 
returned as a result.

SELECT CAL_MONTH, FORECAST_QTY
FROM [zPlayground.test_issue_jdbc]
where FORECAST_QTY is not null;

2013-MAR03  15.001
2013-FEB    <Error>
2013-JAN    20.0

SELECT CAL_MONTH, FORECAST_QTY
FROM [zPlayground.test_issue_jdbc]
where FORECAST_QTY is not null;

2013-MAR03  15.001
2013-JAN    20.0

Original comment by dougstou...@motorola.com on 11 Sep 2014 at 12:12

GoogleCodeExporter commented 9 years ago 
Hi,

The Google Api IS indeed aware of nulls.
I fixed the bug in the starchema driver on my fork but I do not how to 
integrate it in the main repository.

Fix:
https://github.com/jsiessataccess/starschema-bigquery-jdbc/commit/b17fca333fde0a
305bbc560dc9c2a1f58be85821

Original comment by Johann.S...@theaccessgroup.fr on 2 Apr 2015 at 8:26