MySQL Bugs: #50404: DECIMAL(10,3) and DECIMAL(14,3) takes same space

Bug #50404	DECIMAL(10,3) and DECIMAL(14,3) takes same space
Submitted:	18 Jan 2010 6:30	Modified:	20 Jan 2010 9:03
Reporter:	Udai Gupta	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	Ver 14.14, 5.1.38 for Win32 on ia32	OS:	Windows (XP SP3)
Assigned to:		CPU Architecture:	Any

Description:
Experiment to see how much space DECIMAL takes on file system.

I have enabled per table space to see what size tables takes in my experiment
[mysqld]
innodb_file_per_table

this is my java code http://yourpaste.net/4524/ in which I am creating two tables.

table_1 has DECIMAL(10,3) - 7 before decimal, 3 after decimal
7(4 byte) + 3(2byte) = 10 (6 byte)

table_2 has DECIMAL(14.3) - 11 before decimal, 3 after decimal
9(4) + 2(1) + 3(2) = 14 (7 byte)

But both the tables takes same space which looks not according to the specification http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html

I expect that DECIMAL(14,3) should take 1 byte extra than DECIMAL(10,3)

How to repeat:
explained in Description

Suggested fix:
No Idea

Code I used for the experiment

Attachment: ExperimentDecimal.java (text/x-java), 1.05 KiB.

Thank you for the report.

But these calculations can be applied for MyISAM only, because every storage engie uses own representation of data. With MyISAM I get correct results:

$ls -lah data/test/
....
-rw-rw----    1 apple  apple    68K 19 янв 11:45 table_1.MYD
-rw-rw----    1 apple  apple     1K 19 янв 11:45 table_1.MYI
-rw-rw----    1 apple  apple     8K 19 янв 11:45 table_1.frm
-rw-rw----    1 apple  apple    78K 19 янв 11:45 table_2.MYD
-rw-rw----    1 apple  apple     1K 19 янв 11:45 table_2.MYI
-rw-rw----    1 apple  apple     8K 19 янв 11:45 table_2.frm

Additional explanation to previous comment:

I mean it is easy not see how much space takes a field using only MyISAM storage engines and this space is not exact: see bug #50286 for example how additional byte indicating if column can store NULLs changes data file size. Please note other storage engines can have own row overhead. For MyISAM and InnoDB you can consult MySQL Internals manual located at http://forge.mysql.com/wiki/MySQL_Internals

Thanks Sveta Smirnova, I had this bubble in my mind that the mysql behavior should be same in terms of storage, never thought there could be engine specific storage behavior difference. I will try to understand innodb more thoroughly.