[#MDEV-10171] Wrong field type for COALESCE with unsigned int

[MDEV-10171] Wrong field type for COALESCE with unsigned int Created: 2016-06-02 Updated: 2023-04-27
Status:	Open
Project:	MariaDB Server
Component/s:	Data types
Affects Version/s:	10.1, 10.2, 10.3
Fix Version/s:	10.4

Type:

Bug

Priority:

Major

Reporter:

javipolo

Assignee:

Alexander Barkov

Resolution:

Unresolved

Votes:

Labels:

None

Environment:

Debian Jessie
Linux mariadb 3.16.0-4-amd64 #1 SMP Debian 3.16.7-ckt25-1 (2016-03-06) x86_64 GNU/Linux

Attachments:

check_coalesce.py

Description

It seems that mariadb returns a wrong datatype when dealing with COALESCE and unsigned int/tinyint

Here are some python tests I ran against our current mysql-5-5.31 and the newer mariadb-1.1.14

You can see the definition of the fields is exactly the same, but the datatype returned when dealing with unsigned numbers is NEWDECIMAL(246) and not LONG(3) or LONGLONG(8)

============

mysql-5.5.31

============

Field                     Type                 Null  Key  Default  Extra

i_id                      int(11) unsigned     NO    PRI  NULL     auto_increment

i_version                 tinyint(4)           NO         1

active                    tinyint(4) unsigned  NO         1

1-SELECT COALESCE(i_id,NULL) FROM test_table WHERE i_id = 596686

  type = 3 (LONG)

  value = 596686

2-SELECT COALESCE(i_version,1) FROM test_table WHERE i_id = 596686

  type = 8 (LONGLONG)

  value = 0

3-SELECT COALESCE(active, 1) FROM test_table WHERE i_id = 596686

  type = 8 (LONGLONG)

  value = 1

==============

mariadb-1.1.14

==============

Field                     Type                 Null  Key  Default  Extra

i_id                      int(11) unsigned     NO    PRI  NULL  auto_increment

i_version                 tinyint(4)           NO         1

active                    tinyint(4) unsigned  NO         1

1-SELECT COALESCE(i_id,NULL) FROM test_table WHERE i_id = 596686

  type = 246 (NEWDECIMAL)

  value = 596686

2-SELECT COALESCE(i_version,1) FROM test_table WHERE i_id = 596686

  type = 8 (LONGLONG)

  value = 0

3-SELECT COALESCE(active, 1) FROM test_table WHERE i_id = 596686

  type = 246 (NEWDECIMAL)

  value = 1

Here's the sql to reproduce it:

create table test_table (

  i_id int(11) unsigned not null auto_increment,

  i_version tinyint(4) not null default 1,

  active tinyint(4) unsigned not null default 1,

  primary key(i_id)

);

insert into test_table values ( 596686, 0,1 );

Comments

Comment by javipolo [ 2016-06-02 ]

I also attach the python script I used to test the data types returned

Comment by Sergei Golubchik [ 2017-10-06 ]

MariaDB [test]> SELECT COALESCE(i_id,NULL),COALESCE(i_version,1),COALESCE(active, 1) FROM test_table WHERE i_id = 596686;

Field   1:  `COALESCE(i_id,NULL)`

Catalog:    `def`

Database:   ``

Table:      ``

Org_table:  ``

Type:       NEWDECIMAL

Collation:  binary (63)

Length:     12

Max_length: 6

Decimals:   0

Flags:      BINARY NUM

Field   2:  `COALESCE(i_version,1)`

Catalog:    `def`

Database:   ``

Table:      ``

Org_table:  ``

Type:       LONGLONG

Collation:  binary (63)

Length:     4

Max_length: 1

Decimals:   0

Flags:      NOT_NULL BINARY NUM

Field   3:  `COALESCE(active, 1)`

Catalog:    `def`

Database:   ``

Table:      ``

Org_table:  ``

Type:       NEWDECIMAL

Collation:  binary (63)

Length:     5

Max_length: 1

Decimals:   0

Flags:      NOT_NULL BINARY NUM

+---------------------+-----------------------+---------------------+

| COALESCE(i_id,NULL) | COALESCE(i_version,1) | COALESCE(active, 1) |

+---------------------+-----------------------+---------------------+

|              596686 |                     0 |                   1 |

+---------------------+-----------------------+---------------------+

1 row in set (0.00 sec)

Comment by Sergei Golubchik [ 2017-10-06 ]

MariaDB [test]> create table t1 as select COALESCE(i_id,NULL),COALESCE(i_version,1),COALESCE(active, 1) FROM test_table WHERE i_id = 596686;

MariaDB [test]> show create table t1;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                            |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| t1    | CREATE TABLE `t1` (

  `COALESCE(i_id,NULL)` decimal(11,0) DEFAULT NULL,

  `COALESCE(i_version,1)` int(4) NOT NULL,

  `COALESCE(active, 1)` decimal(4,0) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Comment by Alexander Barkov [ 2017-10-06 ]

There are two ways to mix "unsigned ?int" to "signed ?int":
1. either upgrade to the next bigger INT data type
2. or use a minimal DECIMAL(N,0)

From the storage point of view, #2 is more efficient.
For example, when mixing two 4-byte INTs of different signess, unsigned INT + INT, MariaDB creates a DECIMAL(10,0) column, which:

covers the range -2147483648..4294967295
needs 5 bytes on disk
The approach #1 with upgrade to the next bigger xINT would create a BIGINT column, which would need 8 bytes on disk.

This is OK from the SQL standard point of view:

sql:2016, 9.5 Result of data type combinations
Syntax Rules, 3) c) If any data type in DTS is numeric, then ... 3) Otherwise, exact numeric with implementation-defined precision and with scale equal to the maximum of the scales of the data types in DTS.

Comment by Alexander Barkov [ 2017-10-06 ]

Mixing literals, like in the above examples, can be optimized.

NULL can be completely ignored during data type detection
Integer literals can be checked for their range. So, for example, a column of type "TINYINT UNSIGNED" mixed with the literal 1 can choose "TINYINT UNSIGNED", because it fits the literal. Only "TINYINT UNSIGNED" mixed with the literal 128 and bigger needs some data type transformation.

We are thinking of adding this kind of optimization. But it can be done in 10.3 only, which implements a more convenient way to handle data types comparing to 10.2 (and earlier versions).

Generated at Thu Feb 08 07:40:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-10171] Wrong field type for COALESCE with unsigned int Created: 2016-06-02 Updated: 2023-04-27