MDEV-23054 Assertion `!item->null_value' failed in Type_handler_inet6::make_sort_key_part (#2)

IFNULL(inet6_not_null_expr, 'foo') erroneously set its nullability to NOT NULL.

Fix:
- Moving the line "maybe_null= args[1]->maybe_null" before the call
  of fix_length_and_dec2(), so the call of Type_handler method
  Item_hybrid_func_fix_attributes() can reset it when desired.

- Fixing Type_handler_inet6::Item_hybrid_func_fix_attributes()
  to ignore args[0] when detecting nullability of IFNULL().

4 files changed, 87 insertions, 2 deletions

diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6.result b/plugin/type_inet/mysql-test/type_inet/type_inet6.result
index ecf64d8484a..da949481337 100644
--- a/plugin/type_inet/mysql-test/type_inet/type_inet6.result
+++ b/plugin/type_inet/mysql-test/type_inet/type_inet6.result
@@ -2120,3 +2120,42 @@ t2	CREATE TABLE `t2` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;
+#
+# MDEV-22758 Assertion `!item->null_value' failed in Type_handler_inet6::make_sort_key_part
+#
+CREATE TABLE t1 (c INET6);
+INSERT INTO t1 VALUES ('::'),(NULL);
+SELECT * FROM t1 ORDER BY IFNULL(c, 'foo');
+c
+NULL
+::
+Warnings:
+Warning	1292	Incorrect inet6 value: 'foo'
+DROP TABLE t1;
+CREATE TABLE t1 (c INET6);
+INSERT INTO t1 VALUES ('::'),(NULL);
+CREATE TABLE t2 AS SELECT IFNULL(c, 'foo') FROM t1;
+Warnings:
+Warning	1292	Incorrect inet6 value: 'foo'
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IFNULL(c, 'foo')` inet6 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t2;
+IFNULL(c, 'foo')
+::
+NULL
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT IFNULL(c, '::1') FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IFNULL(c, '::1')` inet6 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t2;
+IFNULL(c, '::1')
+::
+::1
+DROP TABLE t2;
+DROP TABLE t1;
diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6.test b/plugin/type_inet/mysql-test/type_inet/type_inet6.test
index 69583286380..ad4cfe57986 100644
--- a/plugin/type_inet/mysql-test/type_inet/type_inet6.test
+++ b/plugin/type_inet/mysql-test/type_inet/type_inet6.test
@@ -1560,3 +1560,29 @@ SHOW CREATE TABLE t2;
 DROP TABLE t2;
 
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-22758 Assertion `!item->null_value' failed in Type_handler_inet6::make_sort_key_part
+--echo #
+
+CREATE TABLE t1 (c INET6);
+INSERT INTO t1 VALUES ('::'),(NULL);
+SELECT * FROM t1 ORDER BY IFNULL(c, 'foo');
+DROP TABLE t1;
+
+CREATE TABLE t1 (c INET6);
+INSERT INTO t1 VALUES ('::'),(NULL);
+
+# Expect a NULL column
+CREATE TABLE t2 AS SELECT IFNULL(c, 'foo') FROM t1;
+SHOW CREATE TABLE t2;
+SELECT * FROM t2;
+DROP TABLE t2;
+
+# Expect a NOT NULL column
+CREATE TABLE t2 AS SELECT IFNULL(c, '::1') FROM t1;
+SHOW CREATE TABLE t2;
+SELECT * FROM t2;
+DROP TABLE t2;
+
+DROP TABLE t1;
diff --git a/plugin/type_inet/sql_type_inet.h b/plugin/type_inet/sql_type_inet.h
index e924e966357..8c42431ccaa 100644
--- a/plugin/type_inet/sql_type_inet.h
+++ b/plugin/type_inet/sql_type_inet.h
@@ -720,7 +720,17 @@ public:
   {
     attr->Type_std_attributes::operator=(Type_std_attributes_inet6());
     h->set_handler(this);
-    for (uint i= 0; i < nitems; i++)
+    /*
+      If some of the arguments cannot be safely converted to "INET6 NOT NULL",
+      then mark the entire function nullability as NULL-able.
+      Otherwise, keep the generic nullability calculated by earlier stages:
+      - either by the most generic way in Item_func::fix_fields()
+      - or by Item_func_xxx::fix_length_and_dec() before the call of
+        Item_hybrid_func_fix_attributes()
+      IFNULL() is special. It does not need to test args[0].
+    */
+    uint first= dynamic_cast<Item_func_ifnull*>(attr) ? 1 : 0;
+    for (uint i= first; i < nitems; i++)
     {
       if (Inet6::fix_fields_maybe_null_on_conversion_to_inet6(items[i]))
       {
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 40728f244f3..fe6cba607a7 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1117,9 +1117,19 @@ public:
   bool native_op(THD *thd, Native *to);
   bool fix_length_and_dec()
   {
+    /*
+      Set nullability from args[1] by default.
+      Note, some type handlers may reset maybe_null
+      in Item_hybrid_func_fix_attributes() if args[1]
+      is NOT NULL but cannot always be converted to
+      the data type of "this" safely.
+      E.g. Type_handler_inet6 does:
+        IFNULL(inet6_not_null_expr, 'foo') -> INET6 NULL
+        IFNULL(inet6_not_null_expr, '::1') -> INET6 NOT NULL
+    */
+    maybe_null= args[1]->maybe_null;
     if (Item_func_case_abbreviation2::fix_length_and_dec2(args))
       return TRUE;
-    maybe_null= args[1]->maybe_null;
     return FALSE;
   }
   const char *func_name() const { return "ifnull"; }