MySQL Bugs: #114983: The calculated return type of set column double instead of int

Bug #114983	The calculated return type of set column double instead of int
Submitted:	13 May 2024 12:14	Modified:	14 May 2024 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

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.

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.

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   <---
···

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   <---
···

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.