Bug #8448 Precision math/Traditional: Too-long decimal value not rejected

• Submitted: 11 Feb 2005 18:23
• Modified: 8 Jun 2005 10:28
• Reporter: Trudy Pelzer
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.3-alpha-debug
• OS: Linux (SuSE 9.1)
• Assigned to: Alexey Botchkov
• CPU Architecture: Any

[11 Feb 2005 18:23] Trudy Pelzer

Description:
When sql_mode=traditional, all numbers that are out of
range for the defined data type must be rejected. But
we have some problems with large decimals:
(1) Although a definition up to DECIMAL(38,38) should be 
allowed, the server changes anything over DECIMAL(38,30)
down to DECIMAL(38,30).
(2) It makes this data type change silently, without even
returning a warning.
(3) It appears to accept a value with more than 30 post-decimal
digits, even under traditional mode.

Problems #1 and #2 are reported in other bug reports. This
report deals with problem #3.

How to repeat:
mysql> set sql_mode=traditional;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (col1 decimal(38,38));
Query OK, 0 rows affected (0.01 sec)
-- This is the incorrect response. Since we currently
do not accept definitions greater than DECIMAL(38,30),
the CREATE should fail with SQLSTATE 42000 Incorrect 
column specifier for column 'col1'. The preferable
action is to accept the data type definition properly,
though.

mysql> insert into t2 values(.12345678901234567890123456789012345678);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Note  | 1265 | Data truncated for column 'col1' at row 1 |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)
-- This is the incorrect response. Under traditional mode,
since the inserted value was too big to fit, the INSERT
should fail with SQLSTATE 22003 Out of range 
value adjusted for column 'col1' at row 1

mysql> select * from t2;
+----------------------------------+
| col1                             |
+----------------------------------+
| 0.123456789012345678901234567890 |
+----------------------------------+
1 row in set (0.00 sec)
-- Adjusted values such as these are never legitimate
when sql_mode=traditional.

[11 Feb 2005 18:51] Aleksey Kishkin

in windows I got little bit different results 

mysql> create table t1 (col1 decimal(38,38));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t2 values(.12345678901234567890123456789012345678);
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
mysql> insert into t1 values(.12345678901234567890123456789012345678);
Query OK, 1 row affected (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> select * from t1;
+----------------------------------+
| col1                             |
+----------------------------------+
| 0.123456789012345680000000000000 |
+----------------------------------+
1 row in set (0.02 sec)

[11 Feb 2005 19:05] Trudy Pelzer

Sorry, cut and paste problem. The test case should be:

mysql> set sql_mode=traditional;

mysql> create table t1 (col1 decimal(38,38));

mysql> insert into t1 values(.12345678901234567890123456789012345678);

mysql> show warnings;

mysql> select * from t1;

[11 Feb 2005 19:34] Trudy Pelzer

I was asleep at the wheel! There is no bug under Linux after all,
since it is perfectly fine to round a number with too many post-
decimal digits, and that is happening in my example. But Aleksey's
result under Windows needs to be examined: if that is happening
under Windows with the precision math patch pushed, then we're
seeing an incorrect result. The server should do the same thing
under both OSs.