MySQL Bugs: #22774: Incorrectly reported data truncation with server prepared statements

Bug #22774	Incorrectly reported data truncation with server prepared statements
Submitted:	28 Sep 2006 10:24	Modified:	22 Jan 2014 20:02
Reporter:	Alexander Hristov (Candidate Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	5.0.24a	OS:	Linux (Fedora Core 5 and Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	data truncation, decimal, floating, numeric, prepared statement

Description:
Server version: 5.0.22 on Fedora Core 5 AND 5.0.24a on Windows XP. Both servers operating in STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION mode. 

Using server-side prepared statements to insert or update values in tables with decimal(p,q) fields sometimes causes a data truncation note. This does not happen if the same SQL sentence is prepared and filled by the client.

The warning does NOT happen when inserting the value using a plain insert statement using any client console. 

It also does NOT happen when the server does not operate in strict mode.

How to repeat:
A single table with a single column of type numeric(6,2)

Create table foo (cost decimal(6,2) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8

when trying to insert *some* values (-855.2 or 8517.45 or others) using server-side prepared statements, the server returns a

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

Initially I had this issue with Connector/J, so I reported the bug there, but Connector/NET has this, too. I also wrote a quick "proxy" for monitoring what stuff was being sent over the wire and they send the same packets, so it doesn't seem a client side issue.

FROM CLIENT TO SERVER: 31 bytes
1b 01 01 01 16 69 6e 73 65 72 74 20 69 6e 74 6f .....insert into
20 66 6f 6f 20 76 61 6c 75 65 73 20 28 3f 29 foo values (?)
FROM SERVER TO CLIENT: 52 bytes
01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 ................
17 01 01 01 01 64 65 66 01 01 01 01 3f 01 01 3f .....def....?..?
01 01 01 01 01 fd 80 01 01 01 01 01 01 01 01 fe .....ý?........þ
01 01 01 01 ....
FROM CLIENT TO SERVER: 26 bytes
16 01 01 01 17 01 01 01 01 01 01 01 01 01 01 01 ................
01 01 9a 99 99 99 b9 a2 c0 40 ..????¹¢À@
FROM SERVER TO CLIENT: 11 bytes
01 01 01 01 01 01 01 01 01 01 01 ...........
FROM CLIENT TO SERVER: 18 bytes
01 01 01 01 01 53 48 4f 57 20 57 41 52 4e 49 4e .....SHOW WARNIN
47 53 GS
FROM SERVER TO CLIENT: 173 bytes
01 01 01 01 01 1b 01 01 01 01 64 65 66 01 01 01 ..........def...
01 4c 65 76 65 6c 01 01 3f 01 01 01 01 01 fd 81 .Level..?.....ý?
01 1f 01 01 1a 01 01 01 01 64 65 66 01 01 01 01 .........def....
43 6f 64 65 01 01 3f 01 01 01 01 01 01 a1 01 01 Code..?......¡..
01 01 1d 01 01 01 01 64 65 66 01 01 01 01 4d 65 .......def....Me
73 73 61 67 65 01 01 3f 01 01 01 01 01 fd 81 01 ssage..?.....ý?.
1f 01 01 01 01 01 01 fe 01 01 01 01 34 01 01 01 .......þ....4...
01 4e 6f 74 65 01 31 32 36 35 29 44 61 74 61 20 .Note.1265)Data
74 72 75 6e 63 61 74 65 64 20 66 6f 72 20 63 6f truncated for co
6c 75 6d 6e 20 27 63 6f 73 74 27 20 61 74 20 72 lumn 'cost' at r
6f 77 20 31 01 01 01 01 fe 01 01 01 01 ow 1....þ....

Now 40C0A2B99999999A is an exact 64-bit representation of 8517.45 (not an approximation), so there's really no reason for a data truncation note when inserted in a decimal(6,2) field.

Suggested fix:
disable strict mode (not very appopriate)
use client-side prepared statements (has performance issues)

Fixed category from "Server" to the more specific "Prepared Statements"

Thank you for a preoblem report. I was not able to repeat the behaviour described with 5.0.26-BK on Linux using mysql comand line client, as follows:

mysql> Create table foo (cost decimal(6,2) NOT NULL) ENGINE=InnoDB DEFAULT CHAR
SET=utf8;
Query OK, 0 rows affected (0.08 sec)

mysql> prepare stmt from 'insert into foo values(?)';
Query OK, 0 rows affected (0.02 sec)
Statement prepared

mysql> set @@sql_mode=strict_trans_tables;
Query OK, 0 rows affected (0.01 sec)

mysql> set @a=-855.2;
Query OK, 0 rows affected (0.02 sec)

mysql> set @b=8517.45;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a;
Query OK, 1 row affected (0.00 sec)

mysql> execute stmt using @b;
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;
+---------+
| cost    |
+---------+
| -855.20 |
| 8517.45 |
| -855.20 |
| 8517.45 |
+---------+
4 rows in set (0.01 sec)

Please, run this test and inform about the results.

Works fine, and SHOW WARNINGS doesn't report any data truncation after each of the  executes

OK. Do you have a complete C API (or Connector/J) test case that demonstrates this behaviour?

Yes, but the Connector/J couldn't reproduce the bug. Here it is, anyway:

import java.sql.*;
import java.util.Properties;

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:3306/test",p);
    Statement sql = con.createStatement();
    sql.executeUpdate("drop table if exists foo ");
    sql.executeUpdate("Create table foo (`cost` decimal(6,2) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8");
    double value= 8517.42;
    PreparedStatement ps = con.prepareStatement("insert into foo values (?)");
    ps.setDouble(1,value);
    ps.executeUpdate();
  }
}

In the comment above, I meant to say that "the Connector/J _team_ couldn't reproduce the bug". In my setup fails always, on both platforms.

Sorry, the line above should be

double value= 8517.45;

as for

double value= 8517.42;

it doesn't fail. I forgot that I had been making some aditional tests.

Hi. I've downloaded and compiled for Windows the mysql-5.0.27-nightly-20061009 version, and it fails on it too. If I can do anything to help you track the bug, now that I have a compilable version with source which exhibits this behaviour, don't hesitate to ask.

Hi Valeriy.
I believe this bug is *in essence* unfixable.
Please see the results of my investigations here 

http://www.ahristov.com/tutorial/Blog/MySQL-and-Data-Truncation-%3A-A-descent-into-IEEE-he...

What *can* be fixed, however, is the way floats are compared in Field_new_decimal::store(double nr)

Thank you for a great article, http://www.ahristov.com/tutorial/Blog/MySQL-and-Data-Truncation-%3A-A-descent-into-IEEE-he....

I think that string '8517.44' should be just converted to DECIMAL without float/double in the middle. See bug #23260 for a related discussion.