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".

13 files changed, 494 insertions, 90 deletions

diff --git a/mysql-test/main/func_debug.result b/mysql-test/main/func_debug.result
index 6a33557dca3..c0c6e8c6a7e 100644
--- a/mysql-test/main/func_debug.result
+++ b/mysql-test/main/func_debug.result
@@ -1821,3 +1821,117 @@ Warnings:
 Note	1105	eq=0 a=(varchar)'a' b=(hex_hybrid)'a'
 DROP TABLE t1;
 SET SESSION debug_dbug="-d,Item_basic_value";
+#
+# MDEV-16454 Bad results for IN with ROW
+#
+SET SESSION debug_dbug="+d,cmp_item";
+SET SESSION debug_dbug="+d,Item_func_in";
+SET SESSION debug_dbug="+d,Predicant_to_list_comparator";
+SELECT (18446744073709551615,0) IN ((18446744073709551614,0),(-1,0));
+(18446744073709551615,0) IN ((18446744073709551614,0),(-1,0))
+0
+Warnings:
+Note	1105	DBUG: [0] arg=1 handler=0 (row)
+Note	1105	DBUG: [1] arg=2 handler=0 (row)
+Note	1105	DBUG: ROW(3 args) level=0
+Note	1105	DBUG:  [0,0] handler=bigint
+Note	1105	DBUG:  [0,1] handler=bigint
+Note	1105	DBUG:  [0,2] handler=int
+Note	1105	DBUG:  => handler=decimal
+Note	1105	DBUG:  [1,0] handler=int
+Note	1105	DBUG:  [1,1] handler=int
+Note	1105	DBUG:  [1,2] handler=int
+Note	1105	DBUG:  => handler=bigint
+Note	1105	DBUG: types_compatible=yes bisect=yes
+SELECT (1,(0,0)) IN ((1,(1,0)),(0,(0,0)));
+(1,(0,0)) IN ((1,(1,0)),(0,(0,0)))
+0
+Warnings:
+Note	1105	DBUG: [0] arg=1 handler=0 (row)
+Note	1105	DBUG: [1] arg=2 handler=0 (row)
+Note	1105	DBUG: ROW(3 args) level=0
+Note	1105	DBUG:  [0,0] handler=int
+Note	1105	DBUG:  [0,1] handler=int
+Note	1105	DBUG:  [0,2] handler=int
+Note	1105	DBUG:  => handler=bigint
+Note	1105	DBUG:  [1,0] handler=row
+Note	1105	DBUG:  [1,1] handler=row
+Note	1105	DBUG:  [1,2] handler=row
+Note	1105	DBUG:  => handler=row
+Note	1105	DBUG:  ROW(3 args) level=1
+Note	1105	DBUG:   [0,0] handler=int
+Note	1105	DBUG:   [0,1] handler=int
+Note	1105	DBUG:   [0,2] handler=int
+Note	1105	DBUG:   => handler=bigint
+Note	1105	DBUG:   [1,0] handler=int
+Note	1105	DBUG:   [1,1] handler=int
+Note	1105	DBUG:   [1,2] handler=int
+Note	1105	DBUG:   => handler=bigint
+Note	1105	DBUG: types_compatible=yes bisect=yes
+SELECT (1,(0,0),3) IN ((1,(1,0),3),(0,(0,0),3));
+(1,(0,0),3) IN ((1,(1,0),3),(0,(0,0),3))
+0
+Warnings:
+Note	1105	DBUG: [0] arg=1 handler=0 (row)
+Note	1105	DBUG: [1] arg=2 handler=0 (row)
+Note	1105	DBUG: ROW(3 args) level=0
+Note	1105	DBUG:  [0,0] handler=int
+Note	1105	DBUG:  [0,1] handler=int
+Note	1105	DBUG:  [0,2] handler=int
+Note	1105	DBUG:  => handler=bigint
+Note	1105	DBUG:  [1,0] handler=row
+Note	1105	DBUG:  [1,1] handler=row
+Note	1105	DBUG:  [1,2] handler=row
+Note	1105	DBUG:  => handler=row
+Note	1105	DBUG:  ROW(3 args) level=1
+Note	1105	DBUG:   [0,0] handler=int
+Note	1105	DBUG:   [0,1] handler=int
+Note	1105	DBUG:   [0,2] handler=int
+Note	1105	DBUG:   => handler=bigint
+Note	1105	DBUG:   [1,0] handler=int
+Note	1105	DBUG:   [1,1] handler=int
+Note	1105	DBUG:   [1,2] handler=int
+Note	1105	DBUG:   => handler=bigint
+Note	1105	DBUG:  [2,0] handler=int
+Note	1105	DBUG:  [2,1] handler=int
+Note	1105	DBUG:  [2,2] handler=int
+Note	1105	DBUG:  => handler=bigint
+Note	1105	DBUG: types_compatible=yes bisect=yes
+SELECT '0x' IN (0);
+'0x' IN (0)
+1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: '0x'
+SELECT '0x' IN (0,1);
+'0x' IN (0,1)
+1
+Warnings:
+Note	1105	DBUG: [0] arg=1 handler=0 (double)
+Note	1105	DBUG: [1] arg=2 handler=0 (double)
+Note	1105	DBUG: types_compatible=yes bisect=yes
+Warning	1292	Truncated incorrect DOUBLE value: '0x'
+SELECT ('0x',1) IN ((0,1));
+('0x',1) IN ((0,1))
+1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: '0x'
+SELECT ('0x',1) IN ((0,1),(1,1));
+('0x',1) IN ((0,1),(1,1))
+1
+Warnings:
+Note	1105	DBUG: [0] arg=1 handler=0 (row)
+Note	1105	DBUG: [1] arg=2 handler=0 (row)
+Note	1105	DBUG: ROW(3 args) level=0
+Note	1105	DBUG:  [0,0] handler=varchar
+Note	1105	DBUG:  [0,1] handler=int
+Note	1105	DBUG:  [0,2] handler=int
+Note	1105	DBUG:  => handler=double
+Note	1105	DBUG:  [1,0] handler=int
+Note	1105	DBUG:  [1,1] handler=int
+Note	1105	DBUG:  [1,2] handler=int
+Note	1105	DBUG:  => handler=bigint
+Note	1105	DBUG: types_compatible=yes bisect=yes
+Warning	1292	Truncated incorrect DOUBLE value: '0x'
+SET SESSION debug_dbug="-d,Predicant_to_list_comparator";
+SET SESSION debug_dbug="-d,Item_func_in";
+SET SESSION debug_dbug="-d,cmp_item";
diff --git a/mysql-test/main/func_debug.test b/mysql-test/main/func_debug.test
index 9237561500d..f3ae0c67181 100644
--- a/mysql-test/main/func_debug.test
+++ b/mysql-test/main/func_debug.test
@@ -555,3 +555,25 @@ EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN ''a'' AND ?' USING 0x61;
 DROP TABLE t1;
 
 SET SESSION debug_dbug="-d,Item_basic_value";
+
+
+--echo #
+--echo # MDEV-16454 Bad results for IN with ROW
+--echo #
+
+SET SESSION debug_dbug="+d,cmp_item";
+SET SESSION debug_dbug="+d,Item_func_in";
+SET SESSION debug_dbug="+d,Predicant_to_list_comparator";
+
+SELECT (18446744073709551615,0) IN ((18446744073709551614,0),(-1,0));
+SELECT (1,(0,0)) IN ((1,(1,0)),(0,(0,0)));
+SELECT (1,(0,0),3) IN ((1,(1,0),3),(0,(0,0),3));
+
+SELECT '0x' IN (0);
+SELECT '0x' IN (0,1);
+SELECT ('0x',1) IN ((0,1));
+SELECT ('0x',1) IN ((0,1),(1,1));
+
+SET SESSION debug_dbug="-d,Predicant_to_list_comparator";
+SET SESSION debug_dbug="-d,Item_func_in";
+SET SESSION debug_dbug="-d,cmp_item";
diff --git a/mysql-test/main/func_in.result b/mysql-test/main/func_in.result
index 65313148bf8..a86781b156c 100644
--- a/mysql-test/main/func_in.result
+++ b/mysql-test/main/func_in.result
@@ -909,3 +909,38 @@ Warnings:
 Warning	1292	Truncated incorrect time value: ''
 Warning	1292	Truncated incorrect time value: ''
 Warning	1292	Truncated incorrect time value: ''
+#
+# End of 10.3 tests
+#
+#
+# Start of 10.4 tests
+#
+#
+# MDEV-16454 Bad results for IN with ROW
+#
+SELECT (18446744073709551615,0) IN ((18446744073709551614,0),(-1,0));
+(18446744073709551615,0) IN ((18446744073709551614,0),(-1,0))
+0
+SELECT '0x' IN (0);
+'0x' IN (0)
+1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: '0x'
+SELECT '0x' IN (0,1);
+'0x' IN (0,1)
+1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: '0x'
+SELECT ('0x',1) IN ((0,1));
+('0x',1) IN ((0,1))
+1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: '0x'
+SELECT ('0x',1) IN ((0,1),(1,1));
+('0x',1) IN ((0,1),(1,1))
+1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: '0x'
+#
+# End of 10.4 tests
+#
diff --git a/mysql-test/main/func_in.test b/mysql-test/main/func_in.test
index b99fad159c2..fb6f2036f20 100644
--- a/mysql-test/main/func_in.test
+++ b/mysql-test/main/func_in.test
@@ -690,3 +690,27 @@ SELECT
   TIME'00:00:00'='' AS c1_true,
   TIME'00:00:00' IN ('', TIME'10:20:30') AS c2_true,
   TIME'00:00:00' NOT IN ('', TIME'10:20:30') AS c3_false;
+
+
+--echo #
+--echo # End of 10.3 tests
+--echo #
+
+--echo #
+--echo # Start of 10.4 tests
+--echo #
+
+--echo #
+--echo # MDEV-16454 Bad results for IN with ROW
+--echo #
+SELECT (18446744073709551615,0) IN ((18446744073709551614,0),(-1,0));
+
+SELECT '0x' IN (0);
+SELECT '0x' IN (0,1);
+SELECT ('0x',1) IN ((0,1));
+SELECT ('0x',1) IN ((0,1),(1,1));
+
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
diff --git a/mysql-test/main/gis-debug.result b/mysql-test/main/gis-debug.result
index be4145f2236..60954993a5e 100644
--- a/mysql-test/main/gis-debug.result
+++ b/mysql-test/main/gis-debug.result
@@ -405,3 +405,95 @@ ERROR HY000: Illegal parameter data types varchar and geometry for operation '/'
 CREATE TABLE t1 AS SELECT '0' MOD POINT(0,0) LIMIT 0;
 ERROR HY000: Illegal parameter data types varchar and geometry for operation 'MOD'
 SET debug_dbug='-d,num_op';
+#
+# End of 10.3 tests
+#
+#
+# Start of 10.4 tests
+#
+#
+# MDEV-16454 Bad results for IN with ROW
+#
+SET SESSION debug_dbug="+d,cmp_item";
+SET SESSION debug_dbug="+d,Item_func_in";
+SET SESSION debug_dbug="+d,Predicant_to_list_comparator";
+SELECT (POINT(1,1),0) IN ((POINT(1,1),0),((POINT(1,1)),1));
+(POINT(1,1),0) IN ((POINT(1,1),0),((POINT(1,1)),1))
+1
+Warnings:
+Note	1105	DBUG: [0] arg=1 handler=0 (row)
+Note	1105	DBUG: [1] arg=2 handler=0 (row)
+Note	1105	DBUG: ROW(3 args) level=0
+Note	1105	DBUG:  [0,0] handler=geometry
+Note	1105	DBUG:  [0,1] handler=geometry
+Note	1105	DBUG:  [0,2] handler=geometry
+Note	1105	DBUG:  => handler=geometry
+Note	1105	DBUG:  [1,0] handler=int
+Note	1105	DBUG:  [1,1] handler=int
+Note	1105	DBUG:  [1,2] handler=int
+Note	1105	DBUG:  => handler=bigint
+Note	1105	DBUG: types_compatible=yes bisect=no
+SELECT (1,(POINT(1,1),0)) IN ((1,(POINT(1,1),0)),(0,(POINT(1,1),0)));
+(1,(POINT(1,1),0)) IN ((1,(POINT(1,1),0)),(0,(POINT(1,1),0)))
+1
+Warnings:
+Note	1105	DBUG: [0] arg=1 handler=0 (row)
+Note	1105	DBUG: [1] arg=2 handler=0 (row)
+Note	1105	DBUG: ROW(3 args) level=0
+Note	1105	DBUG:  [0,0] handler=int
+Note	1105	DBUG:  [0,1] handler=int
+Note	1105	DBUG:  [0,2] handler=int
+Note	1105	DBUG:  => handler=bigint
+Note	1105	DBUG:  [1,0] handler=row
+Note	1105	DBUG:  [1,1] handler=row
+Note	1105	DBUG:  [1,2] handler=row
+Note	1105	DBUG:  => handler=row
+Note	1105	DBUG:  ROW(3 args) level=1
+Note	1105	DBUG:   [0,0] handler=geometry
+Note	1105	DBUG:   [0,1] handler=geometry
+Note	1105	DBUG:   [0,2] handler=geometry
+Note	1105	DBUG:   => handler=geometry
+Note	1105	DBUG:   [1,0] handler=int
+Note	1105	DBUG:   [1,1] handler=int
+Note	1105	DBUG:   [1,2] handler=int
+Note	1105	DBUG:   => handler=bigint
+Note	1105	DBUG: types_compatible=yes bisect=no
+SELECT (1,0) IN ((POINT(1,1),0),(0,0));
+ERROR HY000: Illegal parameter data types int and geometry for operation 'in'
+SHOW WARNINGS;
+Level	Code	Message
+Note	1105	DBUG: [0] arg=1 handler=0 (row)
+Note	1105	DBUG: [1] arg=2 handler=0 (row)
+Note	1105	DBUG: ROW(3 args) level=0
+Note	1105	DBUG:  [0,0] handler=int
+Note	1105	DBUG:  [0,1] handler=geometry
+Note	1105	DBUG:  [0,2] handler=int
+Error	4078	Illegal parameter data types int and geometry for operation 'in'
+Note	1105	DBUG: types_compatible=yes bisect=yes
+SELECT (1,(0,0)) IN ((1,(POINT(1,1),0)),(0,(0,0)));
+ERROR HY000: Illegal parameter data types int and geometry for operation 'in'
+SHOW WARNINGS;
+Level	Code	Message
+Note	1105	DBUG: [0] arg=1 handler=0 (row)
+Note	1105	DBUG: [1] arg=2 handler=0 (row)
+Note	1105	DBUG: ROW(3 args) level=0
+Note	1105	DBUG:  [0,0] handler=int
+Note	1105	DBUG:  [0,1] handler=int
+Note	1105	DBUG:  [0,2] handler=int
+Note	1105	DBUG:  => handler=bigint
+Note	1105	DBUG:  [1,0] handler=row
+Note	1105	DBUG:  [1,1] handler=row
+Note	1105	DBUG:  [1,2] handler=row
+Note	1105	DBUG:  => handler=row
+Note	1105	DBUG:  ROW(3 args) level=1
+Note	1105	DBUG:   [0,0] handler=int
+Note	1105	DBUG:   [0,1] handler=geometry
+Note	1105	DBUG:   [0,2] handler=int
+Error	4078	Illegal parameter data types int and geometry for operation 'in'
+Note	1105	DBUG: types_compatible=yes bisect=yes
+SET SESSION debug_dbug="-d,Predicant_to_list_comparator";
+SET SESSION debug_dbug="-d,Item_func_in";
+SET SESSION debug_dbug="-d,cmp_item";
+#
+# End of 10.4 tests
+#
diff --git a/mysql-test/main/gis-debug.test b/mysql-test/main/gis-debug.test
index 588bc706370..dd64ce0f04c 100644
--- a/mysql-test/main/gis-debug.test
+++ b/mysql-test/main/gis-debug.test
@@ -111,3 +111,39 @@ CREATE TABLE t1 AS SELECT '0'/POINT(0,0) LIMIT 0;
 CREATE TABLE t1 AS SELECT '0' MOD POINT(0,0) LIMIT 0;
 
 SET debug_dbug='-d,num_op';
+
+--echo #
+--echo # End of 10.3 tests
+--echo #
+
+
+--echo #
+--echo # Start of 10.4 tests
+--echo #
+
+--echo #
+--echo # MDEV-16454 Bad results for IN with ROW
+--echo #
+
+SET SESSION debug_dbug="+d,cmp_item";
+SET SESSION debug_dbug="+d,Item_func_in";
+SET SESSION debug_dbug="+d,Predicant_to_list_comparator";
+
+SELECT (POINT(1,1),0) IN ((POINT(1,1),0),((POINT(1,1)),1));
+SELECT (1,(POINT(1,1),0)) IN ((1,(POINT(1,1),0)),(0,(POINT(1,1),0)));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT (1,0) IN ((POINT(1,1),0),(0,0));
+SHOW WARNINGS;
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT (1,(0,0)) IN ((1,(POINT(1,1),0)),(0,(0,0)));
+SHOW WARNINGS;
+
+SET SESSION debug_dbug="-d,Predicant_to_list_comparator";
+SET SESSION debug_dbug="-d,Item_func_in";
+SET SESSION debug_dbug="-d,cmp_item";
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
diff --git a/mysql-test/main/gis.result b/mysql-test/main/gis.result
index c79699bf8cf..01e724eed87 100644
--- a/mysql-test/main/gis.result
+++ b/mysql-test/main/gis.result
@@ -4964,5 +4964,12 @@ ERROR HY000: Illegal parameter data types bigint and geometry for operation '+'
 SELECT POINT(1,1)+0x60;
 ERROR HY000: Illegal parameter data types geometry and bigint for operation '+'
 #
+# MDEV-16454 Bad results for IN with ROW
+#
+SELECT (1,0) IN ((POINT(1,1),0),(0,0));
+ERROR HY000: Illegal parameter data types int and geometry for operation 'in'
+SELECT (1,(0,0)) IN ((1,(POINT(1,1),0)),(0,(0,0)));
+ERROR HY000: Illegal parameter data types int and geometry for operation 'in'
+#
 # End of 10.4 tests
 #
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
diff --git a/mysql-test/main/row.result b/mysql-test/main/row.result
index 7483f37970f..2d4ebc35b84 100644
--- a/mysql-test/main/row.result
+++ b/mysql-test/main/row.result
@@ -15,28 +15,50 @@ select row('a',1.5,3) IN (row(1,2,3), row('a',1.5,3), row('a','a','a'));
 row('a',1.5,3) IN (row(1,2,3), row('a',1.5,3), row('a','a','a'))
 1
 Warnings:
-Warning	1292	Truncated incorrect DECIMAL value: 'a'
-Warning	1292	Truncated incorrect INTEGER value: 'a'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 select row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3));
 row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3))
 1
 Warnings:
-Warning	1292	Truncated incorrect INTEGER value: 'a'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 select row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3));
 row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3))
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 select row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3));
 row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3))
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 select row('b',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3));
 row('b',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3))
-0
+1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 select row('b',1.5,3) IN (row('b',NULL,3), row('a',1.5,3), row(1,3,3));
 row('b',1.5,3) IN (row('b',NULL,3), row('a',1.5,3), row(1,3,3))
-NULL
+1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 select row('b',1.5,3) IN (row('b',NULL,4), row('a',1.5,3), row(1,3,3));
 row('b',1.5,3) IN (row('b',NULL,4), row('a',1.5,3), row(1,3,3))
-0
+1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 select (1,2,(3,4)) IN ((3,2,(3,4)), (1,2,(3,4)));
 (1,2,(3,4)) IN ((3,2,(3,4)), (1,2,(3,4)))
 1
diff --git a/sql/item.h b/sql/item.h
index 0df9a7c60e1..f6f4684f643 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -2520,6 +2520,30 @@ public:
   {
     args[arg_count++]= item;
   }
+  /**
+    Extract row elements from the given position.
+    For example, for this input:  (1,2),(3,4),(5,6)
+      pos=0 will extract  (1,3,5)
+      pos=1 will extract  (2,4,6)
+    @param  thd  - current thread, to allocate memory on its mem_root
+    @param  rows - an array of compatible ROW-type items
+    @param  pos  - the element position to extract
+  */
+  bool alloc_and_extract_row_elements(THD *thd, const Item_args *rows, uint pos)
+  {
+    DBUG_ASSERT(rows->argument_count() > 0);
+    DBUG_ASSERT(rows->arguments()[0]->cols() > pos);
+    if (alloc_arguments(thd, rows->argument_count()))
+      return true;
+    for (uint i= 0; i < rows->argument_count(); i++)
+    {
+      DBUG_ASSERT(rows->arguments()[0]->cols() == rows->arguments()[i]->cols());
+      Item *arg= rows->arguments()[i]->element_index(pos);
+      add_argument(arg);
+    }
+    DBUG_ASSERT(argument_count() == rows->argument_count());
+    return false;
+  }
   inline Item **arguments() const { return args; }
   inline uint argument_count() const { return arg_count; }
   inline void remove_arguments() { arg_count=0; }
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index ca43213672a..4fa3cdce618 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -34,29 +34,6 @@
 #include "sql_time.h"                  // make_truncated_value_warning
 #include "sql_base.h"                  // dynamic_column_error_message
 
-/**
-  find an temporal type (item) that others will be converted to
-  for the purpose of comparison.
-
-  this is the type that will be used in warnings like
-  "Incorrect <<TYPE>> value".
-*/
-static Item *find_date_time_item(Item **args, uint nargs, uint col)
-{
-  Item *date_arg= 0, **arg, **arg_end;
-  for (arg= args, arg_end= args + nargs; arg != arg_end ; arg++)
-  {
-    Item *item= arg[0]->element_index(col);
-    if (item->cmp_type() != TIME_RESULT)
-      continue;
-    if (item->field_type() == MYSQL_TYPE_DATETIME)
-      return item;
-    if (!date_arg)
-      date_arg= item;
-  }
-  return date_arg;
-}
-
 
 /*
   Compare row signature of two expressions
@@ -3882,39 +3859,15 @@ bool cmp_item_row::alloc_comparators(THD *thd, uint cols)
 void cmp_item_row::store_value(Item *item)
 {
   DBUG_ENTER("cmp_item_row::store_value");
-  THD *thd= current_thd;
-  if (!alloc_comparators(thd, item->cols()))
+  DBUG_ASSERT(comparators);
+  DBUG_ASSERT(n == item->cols());
+  item->bring_value();
+  item->null_value= 0;
+  for (uint i=0; i < n; i++)
   {
-    item->bring_value();
-    item->null_value= 0;
-    for (uint i=0; i < n; i++)
-    {
-      if (!comparators[i])
-      {
-        /**
-          Comparators for the row elements that have temporal data types
-          are installed at initialization time by prepare_comparators().
-          Here we install comparators for the other data types.
-          There is a bug in the below code. See MDEV-11511.
-          When performing:
-           (predicant0,predicant1) IN ((value00,value01),(value10,value11))
-          It uses only the data type and the collation of the predicant
-          elements only. It should be fixed to aggregate the data type and
-          the collation for all elements at the N-th positions of the
-          predicate and all values:
-          - predicate0, value00, value01
-          - predicate1, value10, value11
-        */
-        Item *elem= item->element_index(i);
-        const Type_handler *handler= elem->type_handler();
-        DBUG_ASSERT(elem->cmp_type() != TIME_RESULT);
-        if (!(comparators[i]=
-              handler->make_cmp_item(thd, elem->collation.collation)))
-	  break;					// new failed
-      }
-      comparators[i]->store_value(item->element_index(i));
-      item->null_value|= item->element_index(i)->null_value;
-    }
+    DBUG_ASSERT(comparators[i]);
+    comparators[i]->store_value(item->element_index(i));
+    item->null_value|= item->element_index(i)->null_value;
   }
   DBUG_VOID_RETURN;
 }
@@ -4283,25 +4236,84 @@ bool Item_func_in::value_list_convert_const_to_int(THD *thd)
 }
 
 
-/**
-  Historically this code installs comparators at initialization time
-  for temporal ROW elements only. All other comparators are installed later,
-  during the first store_value(). This causes the bug MDEV-11511.
-  See also comments in cmp_item_row::store_value().
-*/
-bool cmp_item_row::prepare_comparators(THD *thd, Item **args, uint arg_count)
+bool cmp_item_row::
+      aggregate_row_elements_for_comparison(THD *thd,
+                                            Type_handler_hybrid_field_type *cmp,
+                                            Item_args *tmp,
+                                            const char *funcname,
+                                            uint col,
+                                            uint level)
 {
+  DBUG_EXECUTE_IF("cmp_item",
+  {
+    for (uint i= 0 ; i < tmp->argument_count(); i++)
+    {
+      Item *arg= tmp->arguments()[i];
+      push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
+                          ER_UNKNOWN_ERROR, "DBUG: %s[%d,%d] handler=%s",
+                          String_space(level).c_ptr(), col, i,
+                          arg->type_handler()->name().ptr());
+    }
+  }
+  );
+  bool err= cmp->aggregate_for_comparison(funcname, tmp->arguments(),
+                                          tmp->argument_count(), true);
+  DBUG_EXECUTE_IF("cmp_item",
+  {
+    if (!err)
+      push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
+                          ER_UNKNOWN_ERROR, "DBUG: %s=> handler=%s",
+                          String_space(level).c_ptr(),
+                          cmp->type_handler()->name().ptr());
+  }
+  );
+  return err;
+}
+
+
+bool cmp_item_row::prepare_comparators(THD *thd, const char *funcname,
+                                       const Item_args *args, uint level)
+{
+  DBUG_EXECUTE_IF("cmp_item",
+                  push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
+                  ER_UNKNOWN_ERROR, "DBUG: %sROW(%d args) level=%d",
+                  String_space(level).c_ptr(),
+                  args->argument_count(), level););
+  DBUG_ASSERT(args->argument_count() > 0);
+  if (alloc_comparators(thd, args->arguments()[0]->cols()))
+    return true;
+  DBUG_ASSERT(n == args->arguments()[0]->cols());
   for (uint col= 0; col < n; col++)
   {
-    Item *date_arg= find_date_time_item(args, arg_count, col);
-    if (date_arg)
+    Item_args tmp;
+    Type_handler_hybrid_field_type cmp;
+
+    if (tmp.alloc_and_extract_row_elements(thd, args, col) ||
+        aggregate_row_elements_for_comparison(thd, &cmp, &tmp,
+                                              funcname, col, level + 1))
+      return true;
+
+    /*
+      There is a legacy bug (MDEV-11511) in the code below,
+      which should be fixed eventually.
+      When performing:
+       (predicant0,predicant1) IN ((value00,value01),(value10,value11))
+      It uses only the data type and the collation of the predicant
+      elements only. It should be fixed to take into account the data type and
+      the collation for all elements at the N-th positions of the
+      predicate and all values:
+      - predicate0, value00, value01
+      - predicate1, value10, value11
+    */
+    Item *item0= args->arguments()[0]->element_index(col);
+    CHARSET_INFO *collation= item0->collation.collation;
+    if (!(comparators[col]= cmp.type_handler()->make_cmp_item(thd, collation)))
+      return true;
+    if (cmp.type_handler() == &type_handler_row)
     {
-      // TODO: do like the scalar comparators do
-      const Type_handler *h= date_arg->type_handler();
-      comparators[col]= h->field_type() == MYSQL_TYPE_TIME ?
-                        (cmp_item *) new (thd->mem_root) cmp_item_time() :
-                        (cmp_item *) new (thd->mem_root) cmp_item_datetime();
-      if (!comparators[col])
+      // Prepare comparators for ROW elements recursively
+      cmp_item_row *row= static_cast<cmp_item_row*>(comparators[col]);
+      if (row->prepare_comparators(thd, funcname, &tmp, level + 1))
         return true;
     }
   }
@@ -4311,19 +4323,10 @@ bool cmp_item_row::prepare_comparators(THD *thd, Item **args, uint arg_count)
 
 bool Item_func_in::fix_for_row_comparison_using_bisection(THD *thd)
 {
-  uint cols= args[0]->cols();
   if (unlikely(!(array= new (thd->mem_root) in_row(thd, arg_count-1, 0))))
     return true;
   cmp_item_row *cmp= &((in_row*)array)->tmp;
-  if (cmp->alloc_comparators(thd, cols) ||
-      cmp->prepare_comparators(thd, args, arg_count))
-    return true;
-  /*
-    Only DATETIME items comparators were initialized.
-    Call store_value() to setup others.
-  */
-  cmp->store_value(args[0]);
-  if (unlikely(thd->is_fatal_error))            // OOM
+  if (cmp->prepare_comparators(thd, func_name(), this, 0))
     return true;
   fix_in_vector();
   return false;
@@ -4362,8 +4365,7 @@ bool Item_func_in::fix_for_row_comparison_using_cmp_items(THD *thd)
   DBUG_ASSERT(get_comparator_type_handler(0) == &type_handler_row);
   DBUG_ASSERT(get_comparator_cmp_item(0));
   cmp_item_row *cmp_row= (cmp_item_row*) get_comparator_cmp_item(0);
-  return cmp_row->alloc_comparators(thd, args[0]->cols()) ||
-         cmp_row->prepare_comparators(thd, args, arg_count);
+  return cmp_row->prepare_comparators(thd, func_name(), this, 0);
 }
 
 
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 64500009bb7..08a462b0903 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -2415,12 +2415,19 @@ class cmp_item_row :public cmp_item
 {
   cmp_item **comparators;
   uint n;
+  bool alloc_comparators(THD *thd, uint n);
+  bool aggregate_row_elements_for_comparison(THD *thd,
+                                             Type_handler_hybrid_field_type *cmp,
+                                             Item_args *tmp,
+                                             const char *funcname,
+                                             uint col,
+                                             uint level);
 public:
   cmp_item_row(): comparators(0), n(0) {}
   ~cmp_item_row();
   void store_value(Item *item);
-  bool alloc_comparators(THD *thd, uint n);
-  bool prepare_comparators(THD *, Item **args, uint arg_count);
+  bool prepare_comparators(THD *, const char *funcname,
+                           const Item_args *args, uint level);
   int cmp(Item *arg);
   int cmp_not_null(const Value *val)
   {
diff --git a/sql/sql_string.h b/sql/sql_string.h
index d110e10647a..d9d3f10777c 100644
--- a/sql/sql_string.h
+++ b/sql/sql_string.h
@@ -761,6 +761,17 @@ public:
 };
 
 
+class String_space: public String
+{
+public:
+  String_space(uint n)
+  {
+    if (fill(n, ' '))
+      set("", 0, &my_charset_bin);
+  }
+};
+
+
 static inline bool check_if_only_end_space(CHARSET_INFO *cs,
                                            const char *str, 
                                            const char *end)