Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.7
-
None
-
None
Description
The server has different limitations for decimal value in a variable and in a field. For variables it's (seemed to be) 81, while for field it is 65(https://mariadb.com/kb/en/decimal/)
Some illustration for variables length
(btw, notethat if variable is set to a number with precision higher than 81 and of total length more 83, it gets silently truncated to unexpected 74 characters)
MariaDB [test]> set @a=-111111111111111111111111111111111111111111111111111111111111111111111111.1111111111; |
Query OK, 0 rows affected (0.002 sec) |
|
|
MariaDB [test]> select @a; |
Field 1: `@a` |
Org_field: ``
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: NEWDECIMAL
|
Collation: binary (63) |
Length: 83 |
Max_length: 83 |
Decimals: 38 |
Flags: BINARY NUM
|
|
|
|
|
+-------------------------------------------------------------------------------------+
|
| @a | |
+-------------------------------------------------------------------------------------+
|
| -111111111111111111111111111111111111111111111111111111111111111111111111.111111111 | |
+-------------------------------------------------------------------------------------+
|
1 row in set (0.001 sec) |
|
|
MariaDB [test]> select length(@a); |
+------------+
|
| length(@a) | |
+------------+
|
| 83 | |
+------------+
|
1 row in set (0.000 sec) |
|
|
MariaDB [test]> set @a=-1111111111111111111111111111111111111111111111111111111111111111111111111.1111111111; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> select length(@a); |
+------------+
|
| length(@a) | |
+------------+
|
| 74 | |
+------------+
|
1 row in set (0.000 sec) |
|
|
MariaDB [test]> select @a; |
Field 1: `@a` |
Org_field: ``
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: NEWDECIMAL
|
Collation: binary (63) |
Length: 83 |
Max_length: 74 |
Decimals: 38 |
Flags: BINARY NUM
|
|
|
+----------------------------------------------------------------------------+
|
| @a | |
+----------------------------------------------------------------------------+
|
| -1111111111111111111111111111111111111111111111111111111111111111111111111 | |
+----------------------------------------------------------------------------+
|
1 row in set (0.000 sec) |
Now, for field length
MariaDB [test]> CREATE TABLE dect1 (decval decimal(65,5) not null); |
Query OK, 0 rows affected (0.027 sec) |
|
|
MariaDB [test]> drop table dect1;
|
Query OK, 0 rows affected (0.050 sec) |
|
|
MariaDB [test]> CREATE TABLE dect1 (decval decimal(67,7) not null); |
ERROR 1426 (42000): Too big precision specified for 'decval'. Maximum is 65 |
Now, some consequences of that inconcistency
MariaDB [test]> create temporary table t1 as select @a; |
ERROR 1264 (22003): Out of range value for column '@a' at row 1 |
|
|
MariaDB [test]> SET @testDECIMAL = CAST(1.5 AS DECIMAL(15,2)); |
Query OK, 0 rows affected (0.001 sec) |
|
|
MariaDB [test]> SELECT @testDECIMAL; |
Field 1: `@testDECIMAL` |
Org_field: ``
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: NEWDECIMAL
|
Collation: binary (63) |
Length: 83 |
Max_length: 4 |
Decimals: 38 |
Flags: BINARY NUM
|
|
|
+--------------+
|
| @testDECIMAL | |
+--------------+
|
| 1.50 | |
+--------------+
|
1 row in set (0.001 sec) |
|
|
MariaDB [test]> SELECT CAST(1.5 AS DECIMAL(15,2)); |
Field 1: `CAST(1.5 AS DECIMAL(15,2))` |
Org_field: ``
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: NEWDECIMAL
|
Collation: binary (63) |
Length: 17 |
Max_length: 4 |
Decimals: 2 |
Flags: NOT_NULL BINARY NUM
|
|
|
+----------------------------+
|
| CAST(1.5 AS DECIMAL(15,2)) | |
+----------------------------+
|
| 1.50 | |
+----------------------------+
|
1 row in set (0.000 sec) |
The last 3 queried are to demonstrate, that variable and field are seen by client application as same MYSQL_TYPE_NEWDECIMAL type.
Another example. ODBC have API function describing types - sizes, native names etc. So, if we give 83 as max precision for DECIMAL, and app is going to create table based on that info, it will fail. if we give 65, and it allocates buffer according to this information, and then tries to read - it might be surprised in that or other way.
We can limit precision we give to app to 65. but then, it can get surprised once the length variable is higher, and that makes the higher limit for variable a little bit useless. If inconsistency is not enough
Attachments
Issue Links
- causes
-
-
- Closed
-