Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
3.1.3
-
None
-
MariaDB 10.11
Description
I found that when I create a table with a column type of FLOAT and have a stored procedure that selects all records in the table, if I call this stored procedure using CallableStatement.execute() and Statement.execute(), the precision of the returned results is different.
In the test case I provided, CallableStatement.execute() returned `1.8989696E9`, while Statement.execute() returned `1898970000`. However, when I accessed it directly using the command-line client, it returned `1898970000`.
The test case:
@Test
|
public void test() throws SQLException { |
String url = "jdbc:mariadb://localhost:3306?user=user&password=password"; |
Connection con = DriverManager.getConnection(url);
|
|
|
execute(con, "DROP DATABASE IF EXISTS test"); |
execute(con, "CREATE DATABASE test"); |
execute(con, "USE test"); |
execute(con, "CREATE OR REPLACE TABLE t0(c0 FLOAT UNIQUE)"); |
execute(con, "CREATE PROCEDURE `t0_select_all`() BEGIN SELECT * FROM t0; END;"); |
execute(con, "INSERT INTO t0 VALUES (1898969556)"); |
|
|
// use CallableStatement |
CallableStatement cstmt = con.prepareCall("call `t0_select_all`()"); |
if (cstmt.execute()) { |
ResultSet rs = cstmt.getResultSet();;
|
ResultSetMetaData rsMetaData = rs.getMetaData();
|
int count = rsMetaData.getColumnCount(); |
StringBuffer sb = new StringBuffer(); |
|
|
while (rs.next()) { |
sb.setLength(0); |
for (int i = 1; i <= count; i++) { |
sb.append("* " + rs.getString(i) + " *"); |
}
|
System.out.println(sb);
|
}
|
}
|
|
|
// use Statement |
Statement stmt = con.createStatement();
|
if (stmt.execute("call `t0_select_all`()")) { |
ResultSet rs = stmt.getResultSet();
|
ResultSetMetaData rsMetaData = rs.getMetaData();
|
int count = rsMetaData.getColumnCount(); |
StringBuffer sb = new StringBuffer(); |
|
|
while (rs.next()) { |
sb.setLength(0); |
for (int i = 1; i <= count; i++) { |
sb.append("* " + rs.getString(i) + " *"); |
}
|
System.out.println(sb);
|
}
|
}
|
|
|
con.close();
|
}
|
|
|
public void execute(Connection con, String sql) { |
try { |
Statement statement = con.createStatement();
|
statement.execute(sql);
|
statement.close();
|
} catch (SQLException e) { |
e.printStackTrace();
|
}
|
}
|
the reason behind that is that CallableStatement use binary protocol while statement use text protocol => float will be send in 4 bytes using binary protocol and with text representation ('1898969556' in you example).
Floating-point value (FLOAT and DOUBLE) that are represented approximately. If you want exact precision, you need to use DECIMAL datatype, then all value will then be exactly 1898969556.