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:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.0.3 OS:Microsoft Windows (Windows XP SP2)
Assigned to: CPU Architecture:Any
Tags: data truncation, double, prepared statement

[24 Sep 2006 14:28] Alexander Hristov
Description:
(This bug is very similar to 22290, but it doesn't involve BigDecimals, just ordinary doubles)
I'm using MySQL 5.0.18-nt and MySQL Connector/J 5.0.3, and I'm also having strange issues with prepared statements and numeric (decimal) fields.

A field created as numeric(6,2) will accept some double values and reject others. An example rejected value is 8517.45

Can this be a server issue? Now I have no idea of the format of the packets sent to the server, but in this case the packet

00 00 00 00 17 01 00 00     . . . . . . . . 
00 00 01 00 00 00 00 01     . . . . . . . . 
05 00 9a 99 99 99 b9 a2     . . . . . . . . 
c0 40                       . @ 

seems to have the correct IEEE representation of that value. Nonetheless, the server issues a 

Note	1265	Data truncated for column 'weight' at row 1

Here are some more test cases:

failed for 40c30310a3d70a3d 9734.13
failed for 40c0fc8f5c28f5c3 8697.12
failed for 40c2bf5ae147ae14 9598.71
failed for 40c0aa1851eb851f 8532.19
failed for 40c27ddae147ae14 9467.71
failed for c08ab9999999999a -855.2
failed for 40c0a14666666666 8514.55
failed for 40c2c23c28f5c28f 9604.47
failed for c0c2cb30a3d70a3d -9622.38
failed for 40c2078f5c28f5c3 9231.12
failed for c0c121e666666666 -8771.8
failed for 40c0dcee147ae148 8633.86
failed for c0c2568e147ae148 -9389.11
failed for 40c104a3d70a3d71 8713.28
failed for c0c31810a3d70a3d -9776.13
failed for c0c1dc8666666666 -9145.05
failed for c056c66666666666 -91.1
failed for 40c0448f5c28f5c3 8329.12
failed for 40c08be666666666 8471.8
failed for 40c3569851eb851f 9901.19
failed for 40c28110a3d70a3d 9474.13
failed for c0c16cd1eb851eb8 -8921.64

How to repeat:
public class Test {
  public static void main(String[] args) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Properties p = new Properties();
    p.setProperty("user","...");
    p.setProperty("password","...");
    p.setProperty("useServerPrepStmts","true");
    Connection con = (Connection)DriverManager.getConnection("jdbc:mysql://localhost/test",p);
    Statement sql = con.createStatement();
    sql.executeUpdate("drop table if exists foo ");
    sql.executeUpdate("Create table foo (weight numeric(6,2))");
    double value = 8517.45;

    // This succeeds
    sql.executeUpdate("insert into foo values ("+value+")"); 

    // This fails if useServerPrepStmts is true, suceeds if useServerPrepStmts
    // is false
    PreparedStatement ps = con.prepareStatement("insert into foo values (?)");
    ps.setDouble(1,value);
    ps.executeUpdate();
  }

Suggested fix:
useServerPrepStmts = false
[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