Error Code: 1265. Data truncated for column

Question

I have a table named pt_products with the following fields

id  int(11) NO  PRI     auto_increment
merchant    varchar(64) NO  MUL     
filename    varchar(255)    NO  MUL     
name    varchar(255)    NO  MUL     
description text    NO          
image_url   varchar(255)    NO          
buy_url text    NO          
price   decimal(10,2)   NO      0.00    
category    varchar(64) NO  MUL     
brand   varchar(64) NO  MUL     
rating  int(11) NO      0   
reviews int(11) NO      0   
search_name varchar(255)    NO  MUL     
normalised_name varchar(255)    NO  MUL     
original_name   varchar(255)    NO          
voucher_code    varchar(255)    NO          
categoryid  int(11) NO      0   
dupe_hash   varchar(32) NO  UNI     
ean varchar(255)    NO  MUL 0   
asin    char(10)    NO          
Amazon_Top_Sales    bigint(7)   NO      0   
availability    varchar(255)    NO          
mpn varchar(255)    NO  MUL     
delivery_cost   decimal(10,2)   NO      0.00

I create a new table

create table graph (
    id int primary key auto_increment,
    ean varchar(255) unsigned not null,
    avg_price decimal(10,2) not null,
   created_at timestamp default current_timestamp
);

Then, I want to insert fields from the table pt_products to the graph table created above

insert into graph(ean, avg_price)
select ean, avg(price) avg_price
from pt_products
group by ean;

And that's when I get the error

4566 row(s) affected, 64 warning(s): 1265 Data truncated for column 'avg_price' at row 8438 1265 Data truncated for column 'avg_price' at row 8439 1265 Data truncated for column 'avg_price' at row 8440 1265 Data truncated for column 'avg_price' at row 9235 1265 Data truncated for column 'avg_price' at row 9300 1265 Data truncated for column 'avg_price' at row 9333 1265 Data truncated for column 'avg_price' at row 9337 1265 Data truncated for column 'avg_price' at row 9338 1265 Data truncated for column 'avg_price' at row 9339 1265 Data truncated for column 'avg_price' at row 9341 1265 Data truncated for column 'avg_price' at row 9369 1265 Data truncated for column 'avg_price' at row 9370 1265 Data truncated for column 'avg_price' at row 9371 1265 Data truncated for column 'avg_price' at row 9375 1265 Data truncated for column 'avg_price' at row 9376 1265 Data truncated for column 'avg_price' at row 9382 1265 Data truncated for column 'avg_price' at row 9385 1265 Data truncated for column 'avg_price' at row 9386 1265 Data truncated for column 'avg_price' at row 9389 1265 Data truncated for column 'avg_price' at row 9393 1265 Data truncated for column 'avg_price' at row 9401 1265 Data truncated for column 'avg_price' at row 9459 1265 Data truncated for column 'avg_price' at row 9460 1265 Data truncated for column 'avg_price' at row 9532 1265 Data truncated for column 'avg_price' at row 9596 1265 Data truncated for column 'avg_price' at row 9608 1265 Data truncated for column 'avg_price' at row 9617 1265 Data truncated for column 'avg_price' at row 9618 1265 Data truncated for column 'avg_price' at row 9626 1265 Data truncated for column 'avg_price' at row 9669 1265 Data truncated for column 'avg_price' at row 9675 1265 Data truncated for column 'avg_price' at row 9677 1265 Data truncated for column 'avg_price' at row 9678 1265 Data truncated for column 'avg_price' at row 9680 1265 Data truncated for column 'avg_price' at row 9683 1265 Data truncated for column 'avg_price' at row 9695 1265 Data truncated for column 'avg_price' at row 9710 1265 Data truncated for column 'avg_price' at row 9711 1265 Data truncated for column 'avg_price' at row 9739 1265 Data truncated for column 'avg_price' at row 9759 1265 Data truncated for column 'avg_price' at row 9760 1265 Data truncated for column 'avg_price' at row 9768 1265 Data truncated for column 'avg_price' at row 9775 1265 Data truncated for column 'avg_price' at row 9777 1265 Data truncated for column 'avg_price' at row 9780 1265 Data truncated for column 'avg_price' at row 9789 1265 Data truncated for column 'avg_price' at row 9797 1265 Data truncated for column 'avg_price' at row 9829 1265 Data truncated for column 'avg_price' at row 9856 1265 Data truncated for column 'avg_price' at row 9874 1265 Data truncated for column 'avg_price' at row 9897 1265 Data truncated for column 'avg_price' at row 9902 1265 Data truncated for column 'avg_price' at row 9917 1265 Data truncated for column 'avg_price' at row 9936 1265 Data truncated for column 'avg_price' at row 9949 1265 Data truncated for column 'avg_price' at row 9961 1265 Data truncated for column 'avg_price' at row 9976 1265 Data truncated for column 'avg_price' at row 9985 1265 Data truncated for column 'avg_price' at row 9991 1265 Data truncated for column 'avg_price' at row 9995 1265 Data truncated for column 'avg_price' at row 10003 1265 Data truncated for column 'avg_price' at row 10005 1265 Data truncated for column 'avg_price' at row 10007 1265 Data truncated for column 'avg_price' at row 10010 Records: 4566  Duplicates: 0  Warnings: 215

Price field is

price   decimal(10,2)

and avg_price from the destination table graph is the same.

avg_price decimal(10,2) not null,

Shouldn't the data fit there?

I tried to change avg_price from graph table to decimal(20,4), with no luck.

I really can't see what is going on.

example tests here

Thanks

Bill_Karwin_20860 · Answer

The AVG() function on a NUMERIC(10,2) column like your price column produces a result with the type NUMERIC(14,6).

Demo:

mysql> create table t ( price numeric(10,2));

mysql> create table t2 as select avg(price) avg_price from t;

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `avg_price` decimal(14,6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

(NUMERIC and DECIMAL are synonyms.)

When you try to stuff values with six digits of scale into a column with two digits of scale, MySQL must assume that it's possible that it will lose some information along the way.

Demo: Storing a number with two digits of scale is okay, and causes no warnings:

mysql> insert into t select 3.14;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

But a number with greater scale results in a warning:

mysql> insert into t select 3.1415927;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Note  | 1265 | Data truncated for column 'price' at row 1 |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)

You can explicitly cast the numeric expression to the same data type, to avoid the warning:

mysql> insert into t select cast(3.1415927 as decimal(10,2));
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

user_7158959 · Answer

Your select is wrong. for:

select ean, avg(price) avg_price
from pt_products
group by ean;

You get:

ean     avg_price
10086100372     929.500000
10343943704     3697.333333
10343943707     3834.000000
10343943708     9999.990000
884116311874    10499.500000
884116311875    10499.500000
884116321378    14789.000000

and that gets trounced.

You need to round your avg number to 2 places as you use decimal with 2 places.

select ean, round(avg(price),2) avg_price

This will give you:

10086100372     929.50
10343943704     3697.33
10343943707     3834.00
10343943708     9999.99
884116311874    10499.50
884116311875    10499.50
884116321378    14789.00

Error Code: 1265. Data truncated for column

2 Answers