MDEV-16454 Bad results for IN with ROW

Consider an IN predicate with ROW-type arguments:
  predicant IN (value1, ..., valueM)
where predicant and all values consist of N elements.

When performing IN for these arguments, at every position i (1..N)
only data type of i-th element of predicant was taken into account,
while data types on i-th elements of value1..valueM were not taken.

These led to bad comparison data type detection, e.g. when
mixing unsigned and signed integer values.

After this change all element data types are taken into account.
So, for example, a mixture of unsigned and signed values is
now calculated using decimal and does not overflow any more.

Detailed changes:
1. All comparators for ROW elements are now created recursively
   at fix_fields() time, inside  cmp_item_row::prepare_comparators().

   Previously prepare_comparators() installed comparators only
   for temporal data types, while comparators for other types were
   installed at execution time, in cmp_item_row::store_value().

2. Removing comparator creating code from cmp_item_row::store_value().
   It was responsible for non-temporal data types.

3. Removing find_date_time_item(). It's not needed any more.
   All ROW-element data types are now covered by
   cmp_item_row::prepare_comparators().

4. Adding a helper method Item_args::alloc_and_extract_row_elements()
   to extract elements from an array of ROW-type Items, from the given
   position. Using this method to collect elements from the i-th
   position and further pass them to
   Type_handler_hybrid_field_type::aggregate_for_comparison().

5. Moving the call for alloc_comparators() inside
   cmp_item_row::prepare_comparators(). This helps
   to call prepare_comparators() for ROW elements recursively
   (if elements appear to be ROWs again).
   Moving alloc_comparators() from "public" to "private".

1 files changed, 8 insertions, 0 deletions

diff --git a/mysql-test/main/gis.test b/mysql-test/main/gis.test
index aacb9a6c653..97a655bf51e 100644
--- a/mysql-test/main/gis.test
+++ b/mysql-test/main/gis.test
@@ -3032,6 +3032,14 @@ SELECT 0x60+POINT(1,1);
 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
 SELECT POINT(1,1)+0x60;
 
+--echo #
+--echo # MDEV-16454 Bad results for IN with ROW
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT (1,0) IN ((POINT(1,1),0),(0,0));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT (1,(0,0)) IN ((1,(POINT(1,1),0)),(0,(0,0)));
 
 --echo #
 --echo # End of 10.4 tests