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


  • 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.
  • 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
  • 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.
  • 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();     }  }    
  • 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.
  • 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
  • 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