Bug #22639 | PreparedStatement and data truncation | ||
---|---|---|---|
Submitted: | 24 Sep 2006 14:28 | Modified: | 25 Sep 2006 21:16 |
Reporter: | Alexander Hristov (Candidate Quality Contributor) | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | Connector / J | Severity: | S2 (Serious) |
Version: | 5.0.3 | OS: | Windows (Windows XP SP2) |
Assigned to: | CPU Architecture: | Any | |
Tags: | data truncation, double, prepared statement |
[24 Sep 2006 14:28]
Alexander Hristov
[25 Sep 2006 13:03]
Tonci Grgin
Alexander, thanks for your problem report. From the manual: "If a numeric expression contains any strings, they are converted to double-precision floating-point values and the expression is approximate." I believe that actual failing value is something like this: 9734.1300000154354212454. Since this behavior was fixed in connector/J please try latest nightly build from our pages. Test case: -Xmx256M -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://localhost:3306/test?user=root&password=&jdbcCompliantTruncation=true&useServerPrepStmts=true package testsuite.simple; import testsuite.BaseTestCase; import java.math.BigDecimal; public class TestBug22290 extends BaseTestCase { public TestBug22290 (String name) { super(name); } /* *Test for Bugs: #22290: Updating value in DECIMAL column with same datatype causes data truncation error, * @throws Exception */ public void testbug22290() throws Exception { createTable("testbug22290", "(`id` int(11) NOT NULL default 1,`cost` decimal(6,2) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8"); assertEquals(this.stmt.executeUpdate("INSERT INTO testbug22290 (`id`,`cost`) VALUES (1,1.00)"),1); try { this.pstmt = this.conn.prepareStatement("update testbug22290 set cost = cost + ? where id = 1"); this.pstmt.setBigDecimal(1, new BigDecimal(1.11)); System.out.println("Query to be executed: "+this.pstmt.toString()); assertEquals(this.pstmt.executeUpdate(),1); this.pstmt.clearParameters(); //BUG#22693 double val = 8517.45; assertEquals(this.stmt.executeUpdate("INSERT INTO testbug22290 (`id`,`cost`) VALUES (2,"+val+")"),1); this.pstmt = this.conn.prepareStatement("INSERT INTO testbug22290 VALUES(3, ?)"); this.pstmt.setDouble(1, val); System.out.println("Query to be executed: "+this.pstmt.toString()); assertEquals(this.pstmt.executeUpdate(),1); } finally { closeMemberJDBCResources(); } } /** * @param args */ public static void main(String[] args) { junit.textui.TestRunner.run(TestBug22290.class); } } Results: Done. Connected to 5.0.24-log Query to be executed: com.mysql.jdbc.ServerPreparedStatement[1] - update testbug22290 set cost = cost + '1.1100000000000000976996261670137755572795867919921875' where id = 1 Query to be executed: com.mysql.jdbc.ServerPreparedStatement[2] - INSERT INTO testbug22290 VALUES(3, 8517.45) Time: 116,282 OK (1 test) C:\mysql507\bin>mysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 to server version: 5.0.24-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select * from testbug22290; +----+---------+ | id | cost | +----+---------+ | 1 | 2.11 | | 2 | 8517.45 | | 3 | 8517.45 | +----+---------+ 3 rows in set (0.00 sec) mysql> 060925 14:53:30 11 Connect root@localhost on test 11 Query SET NAMES utf8 11 Query SET character_set_results = NULL 11 Query SHOW VARIABLES 11 Query SHOW COLLATION 11 Query SET autocommit=1 11 Query SET sql_mode='STRICT_TRANS_TABLES' 060925 14:53:31 11 Query SELECT VERSION() 11 Query DROP TABLE IF EXISTS testbug22290 11 Query CREATE TABLE testbug22290 (`id` int(11) NOT NULL default 1,`cost` decimal(6,2) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 11 Query INSERT INTO testbug22290 (`id`,`cost`) VALUES (1,1.00) 060925 14:53:47 11 Prepare [1] 060925 14:53:50 11 Execute [1] update testbug22290 set cost = cost + '1.1100000000000000976996261670137755572795867919921875' where id = 1 11 Query INSERT INTO testbug22290 (`id`,`cost`) VALUES (2,8517.45) 11 Prepare [2] 11 Execute [2] INSERT INTO testbug22290 VALUES(3, 8517.45) 060925 14:53:53 9 Query select * from testbug22290
[25 Sep 2006 19:38]
Alexander Hristov
I don't quite understand the quote about numeric expressions with strings, as it is the prepared statement with a single double parameter that fails. The parameter is literally assigned, it doesn't come from some computation. Never mind, I upgraded MySQL from 5.0.18 to 5.0.24a and I tried with mysql-connector-java-5.0-nightly-20060925.zip but it is still failing for the same value (when using server prepared statements) Any more information that I can give you in order to pinpoint the problem?
[25 Sep 2006 19:44]
Alexander Hristov
Curiously, it does NOT fail with MySQL Server 5.0.22 running on Linux (Fedora Core 5)
[25 Sep 2006 21:16]
Alexander Hristov
Sorry, the Linux Server wasn´t operating in strict mode. Setting it to strict causes the error to appear in that version too. However, it seems that this issue is not related to Connector/J as with Connector/NET the same thing happens, but Connector/NET chooses not to raise an exception when the warning arrives