MDEV-24387: Wrong number of decimal digits in certain UNION/Subqery

constellation

Analysis: The decimals is set to NOT_FIXED_DEC for Field_str even if it is
NULL. Unsigned has decimals=0. So Type_std_attributes::decimals is set to 39
(maximum between 0 and 39). This results in incorrect number of decimals
when we have union of unsigned and NULL type.

Fix: Check if the field is created from NULL value. If yes, set decimals to 0
otherwise set it to NOT_FIXED_DEC.

3 files changed, 53 insertions, 1 deletions

diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result
index ea16e621e17..a892f6c9e40 100644
--- a/mysql-test/main/union.result
+++ b/mysql-test/main/union.result
@@ -2601,5 +2601,39 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where `test`.`t2`.`a` < 5 except /* select#2 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where `test`.`t3`.`a` < 5 union all /* select#3 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4
 drop table t1,t2,t3;
 #
+# MDEV-24387: Wrong number of decimal digits in certain UNION/Subqery
+#             constellation
+#
+SELECT CAST(1 AS UNSIGNED) UNION ALL SELECT * from (SELECT NULL) t;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					CAST(1 AS UNSIGNED)	246	2	1	Y	32896	0	63
+CAST(1 AS UNSIGNED)
+1
+NULL
+SELECT CAST(1 AS SIGNED) UNION ALL SELECT * from (SELECT NULL) t;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					CAST(1 AS SIGNED)	3	2	1	Y	32896	0	63
+CAST(1 AS SIGNED)
+1
+NULL
+SELECT CAST(1 AS SIGNED) UNION ALL SELECT * from (SELECT CAST(1 AS UNSIGNED)) t;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					CAST(1 AS SIGNED)	246	11	1	N	32897	0	63
+CAST(1 AS SIGNED)
+1
+1
+SELECT CAST(1 AS UNSIGNED) UNION ALL SELECT NULL;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					CAST(1 AS UNSIGNED)	246	2	1	Y	32896	0	63
+CAST(1 AS UNSIGNED)
+1
+NULL
+SELECT CAST(1 AS UNSIGNED) UNION ALL SELECT CAST(1 AS SIGNED);
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					CAST(1 AS UNSIGNED)	246	2	1	N	32897	0	63
+CAST(1 AS UNSIGNED)
+1
+1
+#
 # End of 10.3 tests
 #
diff --git a/mysql-test/main/union.test b/mysql-test/main/union.test
index 714b6abe131..ab629ce076d 100644
--- a/mysql-test/main/union.test
+++ b/mysql-test/main/union.test
@@ -1856,5 +1856,23 @@ select * from t1 where a > 4;
 drop table t1,t2,t3;
 
 --echo #
+--echo # MDEV-24387: Wrong number of decimal digits in certain UNION/Subqery
+--echo #             constellation
+--echo #
+
+--disable_ps_protocol
+--enable_metadata
+
+SELECT CAST(1 AS UNSIGNED) UNION ALL SELECT * from (SELECT NULL) t;
+SELECT CAST(1 AS SIGNED) UNION ALL SELECT * from (SELECT NULL) t;
+SELECT CAST(1 AS SIGNED) UNION ALL SELECT * from (SELECT CAST(1 AS UNSIGNED)) t;
+
+SELECT CAST(1 AS UNSIGNED) UNION ALL SELECT NULL;
+SELECT CAST(1 AS UNSIGNED) UNION ALL SELECT CAST(1 AS SIGNED);
+
+--disable_metadata
+--enable_ps_protocol
+
+--echo #
 --echo # End of 10.3 tests
 --echo #
diff --git a/sql/field.h b/sql/field.h
index be3a648617b..1344774c189 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -1762,7 +1762,7 @@ public:
 	    uchar null_bit_arg, utype unireg_check_arg,
 	    const LEX_CSTRING *field_name_arg,
 	    const DTCollation &collation);
-  uint decimals() const { return NOT_FIXED_DEC; }
+  uint decimals() const { return is_created_from_null_item ? 0 : NOT_FIXED_DEC; }
   int  save_in_field(Field *to) { return save_in_field_str(to); }
   bool memcpy_field_possible(const Field *from) const
   {