[JBoss JIRA] (TEIID-3947) Incorrect handling of text cells carrying pure numerals in Excel translator
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3947?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3947:
---------------------------------------
Looking at POI more it won't allow getting the string value for a numeric cell and vice versa, so apparently there is very little we can do for the case of having integral value as a numeric cell in a string column.
> Incorrect handling of text cells carrying pure numerals in Excel translator
> ---------------------------------------------------------------------------
>
> Key: TEIID-3947
> URL: https://issues.jboss.org/browse/TEIID-3947
> Project: Teiid
> Issue Type: Bug
> Reporter: Vijay Bhaskar Chintalapati
> Assignee: Steven Hawkins
> Priority: Minor
>
> When the user intends to retrieve (based on source model) textual data from a column in an Excel spreadsheet and when one or of the cells are pure numbers, what gets returned is the string representation of the double representation of the number.
> For example: If the cell has 1234, instead of returning 1234 Apache POI and DV returns 1234.0
> Adding more detail below from a Email sent as part of client update:
> After a thorough investigation into the issue it was found that JDV relies on Apache POI APIs to get the cell content . According to the link [1], Apache POI will return the cell type as double by default when asked for the cell type and when asked for the value will return the double value. JDV is doing its due diligence of converting the value to String [2] but by then POI has already returned 11.0 for a cell value of 11. And converting 11.0 to String will still keep it at
> 11.0.
> What should ideally happen, in my opinion is, the double should be compared against its Long equivalent and see if they are the same. If Yes, then the cell value should be extracted as string disregarding the type associated with the cell. This is because regardless of how you try to maintain a cell text value of 11.0, Excel always saves (don't confuse this with how it shows) it as 11.
> [1] https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html
> [2] https://goo.gl/nC3XoG
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 1 month
[JBoss JIRA] (TEIID-3947) Incorrect handling of text cells carrying pure numerals in Excel translator
by Vijay Bhaskar Chintalapati (JIRA)
[ https://issues.jboss.org/browse/TEIID-3947?page=com.atlassian.jira.plugin... ]
Vijay Bhaskar Chintalapati commented on TEIID-3947:
---------------------------------------------------
Thanks for the MSDN link, your argument makes sense. Sorry looked at it after I posted the last comment.
> Incorrect handling of text cells carrying pure numerals in Excel translator
> ---------------------------------------------------------------------------
>
> Key: TEIID-3947
> URL: https://issues.jboss.org/browse/TEIID-3947
> Project: Teiid
> Issue Type: Bug
> Reporter: Vijay Bhaskar Chintalapati
> Assignee: Steven Hawkins
> Priority: Minor
>
> When the user intends to retrieve (based on source model) textual data from a column in an Excel spreadsheet and when one or of the cells are pure numbers, what gets returned is the string representation of the double representation of the number.
> For example: If the cell has 1234, instead of returning 1234 Apache POI and DV returns 1234.0
> Adding more detail below from a Email sent as part of client update:
> After a thorough investigation into the issue it was found that JDV relies on Apache POI APIs to get the cell content . According to the link [1], Apache POI will return the cell type as double by default when asked for the cell type and when asked for the value will return the double value. JDV is doing its due diligence of converting the value to String [2] but by then POI has already returned 11.0 for a cell value of 11. And converting 11.0 to String will still keep it at
> 11.0.
> What should ideally happen, in my opinion is, the double should be compared against its Long equivalent and see if they are the same. If Yes, then the cell value should be extracted as string disregarding the type associated with the cell. This is because regardless of how you try to maintain a cell text value of 11.0, Excel always saves (don't confuse this with how it shows) it as 11.
> [1] https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html
> [2] https://goo.gl/nC3XoG
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 1 month
[JBoss JIRA] (TEIID-3947) Incorrect handling of text cells carrying pure numerals in Excel translator
by Vijay Bhaskar Chintalapati (JIRA)
[ https://issues.jboss.org/browse/TEIID-3947?page=com.atlassian.jira.plugin... ]
Vijay Bhaskar Chintalapati commented on TEIID-3947:
---------------------------------------------------
View isn't helping much! The column type on the source model is string and the data is coming in string format but its representation (of double) is unexpected and throws off the user. Are you suggesting that if a trailing '.0' (using regex) is found in a STRING column replace it with '' in a view ? That might work, but it just feels wrong.
> Incorrect handling of text cells carrying pure numerals in Excel translator
> ---------------------------------------------------------------------------
>
> Key: TEIID-3947
> URL: https://issues.jboss.org/browse/TEIID-3947
> Project: Teiid
> Issue Type: Bug
> Reporter: Vijay Bhaskar Chintalapati
> Assignee: Steven Hawkins
> Priority: Minor
>
> When the user intends to retrieve (based on source model) textual data from a column in an Excel spreadsheet and when one or of the cells are pure numbers, what gets returned is the string representation of the double representation of the number.
> For example: If the cell has 1234, instead of returning 1234 Apache POI and DV returns 1234.0
> Adding more detail below from a Email sent as part of client update:
> After a thorough investigation into the issue it was found that JDV relies on Apache POI APIs to get the cell content . According to the link [1], Apache POI will return the cell type as double by default when asked for the cell type and when asked for the value will return the double value. JDV is doing its due diligence of converting the value to String [2] but by then POI has already returned 11.0 for a cell value of 11. And converting 11.0 to String will still keep it at
> 11.0.
> What should ideally happen, in my opinion is, the double should be compared against its Long equivalent and see if they are the same. If Yes, then the cell value should be extracted as string disregarding the type associated with the cell. This is because regardless of how you try to maintain a cell text value of 11.0, Excel always saves (don't confuse this with how it shows) it as 11.
> [1] https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html
> [2] https://goo.gl/nC3XoG
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 1 month
[JBoss JIRA] (TEIID-3947) Incorrect handling of text cells carrying pure numerals in Excel translator
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3947?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3947:
---------------------------------------
This boils down to a limitation with the POI type model - which is mostly consistent with the ODBC mapping https://msdn.microsoft.com/en-us/library/ms712640(v=vs.85).aspx but POI does not have datetime and currency mappings.
Based upon that common convention it doesn't seem appropriate for us to infer integral column types. Since the importer is making a guess off of the first row, this would be pretty tenuous as well.
What about using a view or something like TEIID-3349 to alter the translator type?
> Incorrect handling of text cells carrying pure numerals in Excel translator
> ---------------------------------------------------------------------------
>
> Key: TEIID-3947
> URL: https://issues.jboss.org/browse/TEIID-3947
> Project: Teiid
> Issue Type: Bug
> Reporter: Vijay Bhaskar Chintalapati
> Assignee: Steven Hawkins
> Priority: Minor
>
> When the user intends to retrieve (based on source model) textual data from a column in an Excel spreadsheet and when one or of the cells are pure numbers, what gets returned is the string representation of the double representation of the number.
> For example: If the cell has 1234, instead of returning 1234 Apache POI and DV returns 1234.0
> Adding more detail below from a Email sent as part of client update:
> After a thorough investigation into the issue it was found that JDV relies on Apache POI APIs to get the cell content . According to the link [1], Apache POI will return the cell type as double by default when asked for the cell type and when asked for the value will return the double value. JDV is doing its due diligence of converting the value to String [2] but by then POI has already returned 11.0 for a cell value of 11. And converting 11.0 to String will still keep it at
> 11.0.
> What should ideally happen, in my opinion is, the double should be compared against its Long equivalent and see if they are the same. If Yes, then the cell value should be extracted as string disregarding the type associated with the cell. This is because regardless of how you try to maintain a cell text value of 11.0, Excel always saves (don't confuse this with how it shows) it as 11.
> [1] https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html
> [2] https://goo.gl/nC3XoG
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 1 month
[JBoss JIRA] (TEIID-3947) Incorrect handling of text cells carrying pure numerals in Excel translator
by Vijay Bhaskar Chintalapati (JIRA)
[ https://issues.jboss.org/browse/TEIID-3947?page=com.atlassian.jira.plugin... ]
Vijay Bhaskar Chintalapati commented on TEIID-3947:
---------------------------------------------------
I have a minor disagreement on how this test is conducted https://github.com/teiid/teiid/blob/master/connectors/translator-excel/sr.... If the age is 40, as shown and saved in the Excel file, the returned value should 40 as well and not 40.0. If it is 40.5, I can understand Apache POI and DV returning 40.5 but if the cell's content is an integer or long for that matter the returned value should be a String representation of Integer/Long instead of Double.
I understand POI does String and Double only on numeric content but may be we should have correct it for integer numerals.
> Incorrect handling of text cells carrying pure numerals in Excel translator
> ---------------------------------------------------------------------------
>
> Key: TEIID-3947
> URL: https://issues.jboss.org/browse/TEIID-3947
> Project: Teiid
> Issue Type: Bug
> Reporter: Vijay Bhaskar Chintalapati
> Assignee: Steven Hawkins
> Priority: Minor
>
> When the user intends to retrieve (based on source model) textual data from a column in an Excel spreadsheet and when one or of the cells are pure numbers, what gets returned is the string representation of the double representation of the number.
> For example: If the cell has 1234, instead of returning 1234 Apache POI and DV returns 1234.0
> Adding more detail below from a Email sent as part of client update:
> After a thorough investigation into the issue it was found that JDV relies on Apache POI APIs to get the cell content . According to the link [1], Apache POI will return the cell type as double by default when asked for the cell type and when asked for the value will return the double value. JDV is doing its due diligence of converting the value to String [2] but by then POI has already returned 11.0 for a cell value of 11. And converting 11.0 to String will still keep it at
> 11.0.
> What should ideally happen, in my opinion is, the double should be compared against its Long equivalent and see if they are the same. If Yes, then the cell value should be extracted as string disregarding the type associated with the cell. This is because regardless of how you try to maintain a cell text value of 11.0, Excel always saves (don't confuse this with how it shows) it as 11.
> [1] https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html
> [2] https://goo.gl/nC3XoG
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 1 month
[JBoss JIRA] (TEIID-3947) Incorrect handling of text cells carrying pure numerals in Excel translator
by Vijay Bhaskar Chintalapati (JIRA)
Vijay Bhaskar Chintalapati created TEIID-3947:
-------------------------------------------------
Summary: Incorrect handling of text cells carrying pure numerals in Excel translator
Key: TEIID-3947
URL: https://issues.jboss.org/browse/TEIID-3947
Project: Teiid
Issue Type: Bug
Reporter: Vijay Bhaskar Chintalapati
Assignee: Steven Hawkins
Priority: Minor
When the user intends to retrieve (based on source model) textual data from a column in an Excel spreadsheet and when one or of the cells are pure numbers, what gets returned is the string representation of the double representation of the number.
For example: If the cell has 1234, instead of returning 1234 Apache POI and DV returns 1234.0
Adding more detail below from a Email sent as part of client update:
After a thorough investigation into the issue it was found that JDV relies on Apache POI APIs to get the cell content . According to the link [1], Apache POI will return the cell type as double by default when asked for the cell type and when asked for the value will return the double value. JDV is doing its due diligence of converting the value to String [2] but by then POI has already returned 11.0 for a cell value of 11. And converting 11.0 to String will still keep it at
11.0.
What should ideally happen, in my opinion is, the double should be compared against its Long equivalent and see if they are the same. If Yes, then the cell value should be extracted as string disregarding the type associated with the cell. This is because regardless of how you try to maintain a cell text value of 11.0, Excel always saves (don't confuse this with how it shows) it as 11.
[1] https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html
[2] https://goo.gl/nC3XoG
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 1 month
[JBoss JIRA] (TEIID-3937) Multiplying two columns in CASE statement overrides pushdown (Impala)
by Don Krapohl (JIRA)
[ https://issues.jboss.org/browse/TEIID-3937?page=com.atlassian.jira.plugin... ]
Don Krapohl commented on TEIID-3937:
------------------------------------
Will put it in a forum post. Thanks for quick work on this as always!
> Multiplying two columns in CASE statement overrides pushdown (Impala)
> ---------------------------------------------------------------------
>
> Key: TEIID-3937
> URL: https://issues.jboss.org/browse/TEIID-3937
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.4
> Environment: Ubuntu Trusty
> Reporter: Don Krapohl
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5
>
>
> In the Impala translator, pushdown doesn't happen when two columns are multiplied in the THEN portion of a CASE statement with decimal data type.
> //this should push down but does not
> CASE WHEN column1 IS NOT NULL
> THEN column1 * column2
> ELSE column3 END
> //but you can multiply the columns by a constant and it will push down
> CASE WHEN column1 IS NOT NULL
> THEN column1 * 34567
> ELSE column3 END
> //or
> CASE WHEN column1 IS NOT NULL
> THEN 34567 * column2
> ELSE column3 END
> //or casting to consistent datatypes with DECIMAL output
> CASE WHEN column1 IS NOT NULL
> THEN cast(column1 as DECIMAL ) * cast(column2 as DECIMAL )
> ELSE cast(column3 as DECIMAL ) END
> //BUT output it as double with the cast statement above changed and it does push down (so floating point works)
> CASE WHEN column1 IS NOT NULL
> THEN cast(column1 as double ) * cast(column2 as double)
> ELSE cast(column3 as double) END
> Source data types:
> Column1 is DECIMAL
> Column2 is BIGINT/LONG
> Column3 is DECIMAL
> Output column is DECIMAL
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 1 month
[JBoss JIRA] (TEIID-3946) Version negotiation in JDBC connection
by Marcelo Guimarães (JIRA)
[ https://issues.jboss.org/browse/TEIID-3946?page=com.atlassian.jira.plugin... ]
Marcelo Guimarães commented on TEIID-3946:
------------------------------------------
I'll work on that.
> Version negotiation in JDBC connection
> --------------------------------------
>
> Key: TEIID-3946
> URL: https://issues.jboss.org/browse/TEIID-3946
> Project: Teiid
> Issue Type: Feature Request
> Components: JDBC Driver
> Affects Versions: 8.7.4
> Environment: - JDV 6.2
> Reporter: Hisanobu Okuda
> Assignee: Steven Hawkins
> Fix For: 9.0
>
>
> Our customer is providing a service on top of JDV and some of the external clients are still using outdated teiid drivers and wants something they can make for JDV to block a connection if the JDBC Driver used in a client is outdated. This scenario occurs everytime the customer updates JDV. Since there are so much external clients, some of them don't update their JDBC Drivers and this leads to some errors in JDV.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 1 month
[JBoss JIRA] (TEIID-3937) Multiplying two columns in CASE statement overrides pushdown (Impala)
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3937?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3937:
----------------------------------
Fix Version/s: 8.12.5
> Multiplying two columns in CASE statement overrides pushdown (Impala)
> ---------------------------------------------------------------------
>
> Key: TEIID-3937
> URL: https://issues.jboss.org/browse/TEIID-3937
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.4
> Environment: Ubuntu Trusty
> Reporter: Don Krapohl
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5
>
>
> In the Impala translator, pushdown doesn't happen when two columns are multiplied in the THEN portion of a CASE statement with decimal data type.
> //this should push down but does not
> CASE WHEN column1 IS NOT NULL
> THEN column1 * column2
> ELSE column3 END
> //but you can multiply the columns by a constant and it will push down
> CASE WHEN column1 IS NOT NULL
> THEN column1 * 34567
> ELSE column3 END
> //or
> CASE WHEN column1 IS NOT NULL
> THEN 34567 * column2
> ELSE column3 END
> //or casting to consistent datatypes with DECIMAL output
> CASE WHEN column1 IS NOT NULL
> THEN cast(column1 as DECIMAL ) * cast(column2 as DECIMAL )
> ELSE cast(column3 as DECIMAL ) END
> //BUT output it as double with the cast statement above changed and it does push down (so floating point works)
> CASE WHEN column1 IS NOT NULL
> THEN cast(column1 as double ) * cast(column2 as double)
> ELSE cast(column3 as double) END
> Source data types:
> Column1 is DECIMAL
> Column2 is BIGINT/LONG
> Column3 is DECIMAL
> Output column is DECIMAL
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 1 month
[JBoss JIRA] (TEIID-3946) Version negotiation in JDBC connection
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3946?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3946:
---------------------------------------
Can we get the exact client / server version involved and the relevant server side exceptions?
> Version negotiation in JDBC connection
> --------------------------------------
>
> Key: TEIID-3946
> URL: https://issues.jboss.org/browse/TEIID-3946
> Project: Teiid
> Issue Type: Feature Request
> Components: JDBC Driver
> Affects Versions: 8.7.4
> Environment: - JDV 6.2
> Reporter: Hisanobu Okuda
> Assignee: Steven Hawkins
> Fix For: 9.0
>
>
> Our customer is providing a service on top of JDV and some of the external clients are still using outdated teiid drivers and wants something they can make for JDV to block a connection if the JDBC Driver used in a client is outdated. This scenario occurs everytime the customer updates JDV. Since there are so much external clients, some of them don't update their JDBC Drivers and this leads to some errors in JDV.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 1 month