FLOAT column does not hold 8-byte IEEE 754 float point value

Hi, It appears FLOAT(53) column does not hold IEEE-754 format float point value. Here is the document: https://my.vertica.com/docs/6.1.x/HTML/index.htm#2587.htm Here is my test: dbadmin=> create table test (col1 FLOAT(53)); CREATE TABLE dbadmin=> insert into test values('26906.44048159372'); OUTPUT -------- 1 (1 row) dbadmin=> select * from test; col1 ------------------ 26906.4404815937 <== I expect to see 26906.44048159372 here (1 row) Is this a bug or am I missing anything? Any help is appreciated. Thanks, Stone


  • Options
    I did a test in mySQL, and it preserve all digit: create table test(col1 DOUBLE); insert into test values ('26906.44048159372'); SELECT * FROM test; 26906.44048159372 <== mysql return all digits With vertica, if I have a java double x = 26906.44048159372; when I write to a FLOAT(53) column, and latter read it from that column, it is not the same as the original x, which is a problem for us.
  • Options
    Hi, Hm... I believe you will find that Vertica does in fact hold an 8-byte IEEE 754 float point value: select * from test where col1 = 26906.44048159372; select * from test where col1 = 26906.4404815937; You're just having trouble retrieving it. When Vertica pretty-prints float values in vsql, it truncates off the last digit. Why? Because the last digit isn't exact; there are some bits of information, but not enough to fill out a base-10 value. For example: => select col1::numeric(30,15) from test; f ----------------------- 26906.44048159371959 You may already understand what's going on in the above example; why floats work that way, why they sometimes appear accurate to 16 base-10 digits and sometimes just 15 digits; but many of our users do not, and this caused a lot of confusion. If you try to read the number programmatically as an actual float, rather than from vsql as a pretty-printed string, you should get the full value back. If you don't, let us know. There may also be a way to get Vertica to automatically print that last digit for you. (You can always explicitly cast to a NUMERIC, per the above.) But I don't recall it offhand. Adam
  • Options
    Hi Adam, Thank you for looking into this issue. After the test below, I don't think it is a vsql console print issue. Here is my test java program, the output is
        Dirver = [com.vertica.jdbc.Driver]  Value to insert,  value = 26906.4404815937200000  Value retrived,   value = 26906.4404815937000000  Test failed  Dirver = [com.mysql.jdbc.Driver]  Value to insert,  value = 26906.4404815937200000  Value retrived,   value = 26906.4404815937200000  Test passed    
    It appears even via JDBC, vertica does not preserve all java double value as mySQL did.
  • Options
    Here is my java code
      import java.sql.*;  import java.util.HashMap;  import java.util.Map;  import java.util.Properties;    public class JDBCTest {     public static void main(String[] args) throws Exception {        main1("com.vertica.jdbc.Driver", "dbadmin", "", "jdbc:vertica://BELUS0891.apptio.lan:5433/biit", "demo_apptio_com.mytest");    main1("com.mysql.jdbc.Driver", "szhong", "", "jdbc:mysql://BELUS0891.apptio.lan:3306/test", "mytest");     }     public static void main1(String driver, String username, String password, String url, String tableName) throws Exception {        System.out.printf("Dirver = [%s]\n", driver);    Class.forName( driver );      Properties connProp = new Properties();       connProp.put( "user", username);       connProp.put( "password", password);      Connection conn = DriverManager.getConnection(url, connProp);    conn.setAutoCommit(true);        Statement stmt = conn.createStatement();       try {     stmt.execute("DROP TABLE " + tableName);       } catch (SQLException e) {        System.out.printf("%s\n", e.getMessage());       }    stmt.close();        stmt = conn.createStatement();    stmt.execute("CREATE TABLE " + tableName + " (col1 FLOAT(53))");    stmt.close();        Double v1 = 26906.44048159372;    System.out.printf("Value to insert,  value = %.16f\n", v1);    PreparedStatement stmt2 = conn.prepareStatement("INSERT INTO " + tableName + " VALUES(?)");    stmt2.setDouble(1, v1);    stmt2.execute();    stmt2.close();        PreparedStatement stmt3 = conn.prepareStatement("SELECT col1 FROM " + tableName);    ResultSet rs = stmt3.executeQuery();    rs.next();    Double v2 = rs.getDouble(1);    rs.close();    stmt3.close();    System.out.printf("Value retrived,   value = %.16f\n", v2);        System.out.printf("Test %s\n", (v1.compareTo(v2) == 0)?"passed":"failed");        conn.close();     }  }    
  • Options
    Hi Stone, Are you using a third-party tool or java program to retrieve values from Vertica ? If it is a java program, then you have the option to cast the float column into numeric. That will be one temporary solution.
  • Options
    Hi Satish, Thank you for the help. The source code I posted above is Java code. Yes, we have considered using Numeric, however, since Numeric uses much more storage spaces and require more processing time (for arithmetic operation) -- that make it not a solution but a temporary workaround. Thanks, Stone
  • Options
    Hi Stone, For NUMERIC, you don't have to store the data in that format. As you can see from the above, the storage of and computation on floats within Vertica is correct; the issue is retrieving them via our client drivers. So just cast your stored FLOAT values to NUMERIC for transmission, and convert them back in Java: SELECT myFloat::NUMERIC from table; Vertica's JDBC wire protocol is actually more efficient with NUMERIC types; this shouldn't use too much bandwidth. There is some performance cost to the round-trip conversion, but I think you'll find that it's not too expensive. Adam

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file