MDEV-11514, MDEV-11497, MDEV-11554, MDEV-11555 - IN and CASE type aggregation problems

This patch fixes a number of data type aggregation problems in IN and CASE:
- MDEV-11497 Wrong result for (int_expr IN (mixture of signed and unsigned expressions))
- MDEV-11514 IN with a mixture of TIME and DATETIME returns a wrong result
- MDEV-11554 Wrong result for CASE on a mixture of signed and unsigned expressions
- MDEV-11555 CASE with a mixture of TIME and DATETIME returns a wrong result

1. The problem reported in MDEV-11514 and MDEV-11555 was in the wrong assumption
that items having the same cmp_type() can reuse the same cmp_item instance.
So Item_func_case and Item_func_in used a static array of cmp_item*,
one element per one XXX_RESULT.

TIME and DATETIME cannot reuse the same cmp_item, because arguments of
these types are compared very differently. TIME and DATETIME must have
different instances in the cmp_item array. Reusing the same cmp_item
for TIME and DATETIME leads to unexpected result and unexpected warnings.

Note, after adding more data types soon (e.g. INET6), the problem would
become more serious, as INET6 will most likely have STRING_RESULT, but
it won't be able to reuse the same cmp_item with VARCHAR/TEXT.

This patch introduces a new class Predicant_to_list_comparator,
which maintains an array of cmp_items, one element per distinct
Type_handler rather than one element per XXX_RESULT.

2. The problem reported in MDEV-11497 and MDEV-11554 happened because
Item_func_in and Item_func_case did not take into account the fact
that UNSIGNED and SIGNED values must be compared as DECIMAL rather than INT,
because they used item_cmp_type() to aggregate the arguments.
The relevant code now resides in Predicant_to_list_comparator::add_value()
and uses Type_handler_hybrid_field_type::aggregate_for_comparison(),
like Item_func_between does.

1 files changed, 60 insertions, 0 deletions

diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result
index 5a02ca22392..b0859cafa6d 100644
--- a/mysql-test/r/case.result
+++ b/mysql-test/r/case.result
@@ -379,3 +379,63 @@ DROP TABLE t1;
 #
 # End of 10.1 test
 #
+#
+# Start of 10.3 tests
+#
+#
+# MDEV-11554 Wrong result for CASE on a mixture of signed and unsigned expressions
+#
+CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615);
+SELECT
+CASE -1
+WHEN -9223372036854775808 THEN 'one'
+    WHEN 18446744073709551615 THEN 'two'
+  END AS c;
+c
+NULL
+PREPARE stmt FROM "SELECT
+  CASE -1
+    WHEN -9223372036854775808 THEN 'one'
+    WHEN 18446744073709551615 THEN 'two'
+  END AS c";
+EXECUTE stmt;
+c
+NULL
+EXECUTE stmt;
+c
+NULL
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1;
+#
+# MDEV-11555 CASE with a mixture of TIME and DATETIME returns a wrong result
+#
+SELECT
+CASE TIME'10:20:30'
+    WHEN 102030 THEN 'one'
+    WHEN TIME'10:20:31' THEN 'two'
+  END AS good,
+CASE TIME'10:20:30'
+    WHEN 102030 THEN 'one'
+    WHEN TIME'10:20:31' THEN 'two'
+    WHEN TIMESTAMP'2001-01-01 10:20:32' THEN 'three'
+  END AS was_bad_now_good;
+good	was_bad_now_good
+one	one
+PREPARE stmt FROM "SELECT
+  CASE TIME'10:20:30'
+    WHEN 102030 THEN 'one'
+    WHEN TIME'10:20:31' THEN 'two'
+  END AS good,
+  CASE TIME'10:20:30'
+    WHEN 102030 THEN 'one'
+    WHEN TIME'10:20:31' THEN 'two'
+    WHEN TIMESTAMP'2001-01-01 10:20:32' THEN 'three'
+  END AS was_bad_now_good";
+EXECUTE stmt;
+good	was_bad_now_good
+one	one
+EXECUTE stmt;
+good	was_bad_now_good
+one	one
+DEALLOCATE PREPARE stmt;