Bug #114983 The calculated return type of set column double instead of int
Submitted: 13 May 12:14 Modified: 14 May 1:38
Reporter: hao chen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.31 OS:Any
Assigned to: CPU Architecture:Any
Tags: column-type-info

[13 May 12:14] hao chen
Description:

mysql> CREATE TABLE `tab5` (
    ->   `a` int NOT NULL,
    ->   `b` set('a','b','c') DEFAULT NULL,                                                                                                                                   ->   `c` set('a','b','c') DEFAULT NULL,
    ->   PRIMARY KEY (`a`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.12 sec)

mysql> select a+b from tab5;
Field   1:  `a+b`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE          <-- Why the retruen type is double instead of int 
Collation:  binary (63)
Length:     23
Max_length: 0
Decimals:   31
Flags:      BINARY NUM

How to repeat:
As above

Suggested fix:
Is the return type reasonable?  Looking forward to your explanation.
[13 May 12:28] MySQL Verification Team
HI Mr. chen,

Thank you very much for your bug report.

The result of the query is very reasonable.

According to SQL Standard, mixing data types in any kind of the expression or comparison, should immediately return the error.

MySQL, however, tries it's best and so we have our own sets of rules when different data types are used in an expression.

When using integers and sets, MySQL chose  that resolving data type is an integer. This is how MySQL is resolving SET internally.

Other RDBMS products may do it otherwise, but nobody is at error here, since mixing data types is strictly forbidden by SQL Standard.

Not a bug.
[14 May 1:38] hao chen
Thank you for your reply.

As mentioned above:
'When using integers and sets, MySQL chose  that resolving data type is an integer.' Why the result of --column-type-info is double?

··· 
Field   1:  `a+b`
Catalog:    `def`
...
Type:       DOUBLE   <---
···
[14 May 1:38] hao chen
Thank you for your reply.

As mentioned above:
'When using integers and sets, MySQL chose  that resolving data type is an integer.' Why the result of --column-type-info is double?

··· 
Field   1:  `a+b`
Catalog:    `def`
...
Type:       DOUBLE   <---
···
[14 May 6:55] Roy Lyseng
As already written, ENUM and SET values are stored internally as integer values, and when used in arithmetic expressions their numeric values are used.
However, the rules for generating a result type is quite simple: If a numeric value and a non-numeric value is combined in an expression, the resulting type is DOUBLE.

You may however perform a CAST of an ENUM or SET value into a SIGNED type when using them in expressions. That will coerce the type into an integer, which will give the result that you want.