Bug #108981 function NULLIF(ASCII()) acts abnormally in select statements

• Submitted: 3 Nov 2022 10:35
• Modified: 3 Nov 2022 14:18
• Reporter: Brian Yue (OCA)
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any (rhel-7.4)
• CPU Architecture: Any (x86-64)

[3 Nov 2022 10:35] Brian Yue

Description:
Hello,
  Recently we find a case that result of a function produces different value when it's placed in select_list or where_clause of select statements.

 

How to repeat:
  (1) create a table
  CREATE TABLE `t1` (   `id` int NOT NULL AUTO_INCREMENT,   `c0` decimal(10,0) unsigned zerofill DEFAULT '1240363572',   `c1` varbinary(78) DEFAULT NULL,   `c2` varchar(83) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,   `c3` tinyint(1) DEFAULT '1',   `c4` int DEFAULT NULL,   `GDB_BID` int NOT NULL DEFAULT '-1',   `GTID` bigint unsigned NOT NULL DEFAULT '0',   PRIMARY KEY (`id`),   UNIQUE KEY `c3` (`c3`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

  (2) insert a record
insert into t1 values (1, 0498879434, NULL, 'xxxx', -128, NULL, -1, 4295476708);

  (3) do some test about function NULLIF(ASCII))
mysql> select  ((NULLIF(ASCII(-327253980), c2)) is NULL) expr from t1;
+------+
| expr |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

# well, now we can conclude that expression `(NULLIF(ASCII(-327253980), c2))` is not null

mysql> select 1 from t1 where (NULLIF(ASCII(-327253980), c2)) is NULL;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

# however, now it seems that expression `(NULLIF(ASCII(-327253980), c2))` produce a null result, conflicted with result of previous query

Suggested fix:
something is wrong with Item::cache_const_expr_transformer in sql optimizer

[3 Nov 2022 14:18] MySQL Verification Team

Hi Mr. Yue,

Thank you for your bug report.

We tested  it fully and got the same results.

Verified as reported.

[14 Nov 5:59] Xinhao Zhao

This modification may help.

diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 0c055cd89ad..fceeeac56fc 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1269,8 +1269,8 @@ bool Arg_comparator::set_cmp_func(Item_result_field *owner_arg, Item **left_arg,
   }

   THD *thd = current_thd;
-  left = cache_converted_constant(thd, left, &left_cache, type);
-  right = cache_converted_constant(thd, right, &right_cache, type);
+  *left = *cache_converted_constant(thd, left, &left_cache, type);
+  *right = *cache_converted_constant(thd, right, &right_cache, type);
   return set_compare_func(owner_arg, type);
 }