← Back to team overview

maria-developers team mailing list archive

Re: MDEV-11503 Introduce Type_handler::make_in_vector() and Item_func_in_fix_comparator_compatible_types()

 

Hello Sanja,

here's a new version, with a better test coverage.


On 12/09/2016 01:44 PM, Alexander Barkov wrote:
> Hello Sanja,
> 
> Please review a patch for MDEV-11503.
> 
> Thanks!
> 
commit 29ec93e1595b019b9ab4f2f440c2b9b45dee928e
Author: Alexander Barkov <bar@xxxxxxxxxxx>
Date:   Sat Dec 10 00:08:32 2016 +0400

    MDEV-11503 Introduce Type_handler::make_in_vector() and Item_func_in_fix_comparator_compatible_types()
    
    This patch implements the task according to the description:
    
    1. The old code from Item_func_in::fix_length_and_dec() was decomposed
    into smaller methods:
    - all_items_are_consts()
    - compatible_types_scalar_bisection_possible()
    - compatible_types_row_bisection_possible()
    - fix_in_vector()
    - fix_for_scalar_comparison_using_bisection()
    - fix_for_scalar_comparison_using_cmp_items()
    - fix_for_row_comparison_using_bisection()
    - fix_for_row_comparison_using_cmp_items()
    
    The data type dependend pieces where moved as methods to Type_handler.
    
    2. Splits in_datetime into separate:
       - in_datetime, for DATETIME and DATE,
       - in_time, for TIME
       to make the code more symmetric across data types.
    
    Additionally:
    - Adds a test func_debug.test to see which calculation strategy
      (bisect or no bisect) is chosen to handle IN with various arguments.
    - Adds a new helper method (to avoid duplicate code):
      cmp_item_rows::prepare_comparators()
    - Changes the propotype for cmp_item_row::alloc_comparators(),
      to avoid duplicate code, and to use less current_thd.
    - Changes "friend" sections in cmp_item_row and in_row from
      an exact Item_func_in method to the entire class Item_func_in,
      as their internals are now needed in multiple Item_func_in methods.
    - Added more comments (e.g. on bisection, on the problem reported in MDEV-11511)

diff --git a/mysql-test/r/func_debug.result b/mysql-test/r/func_debug.result
new file mode 100644
index 0000000..613b458
--- /dev/null
+++ b/mysql-test/r/func_debug.result
@@ -0,0 +1,828 @@
+SET SESSION debug_dbug="+d,Item_func_in";
+# Constant predicant, compatible types, bisect
+SELECT 1 IN (1,2);
+1 IN (1,2)
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 1 IN (1,2,NULL);
+1 IN (1,2,NULL)
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 1 NOT IN (1,2);
+1 NOT IN (1,2)
+0
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 1 NOT IN (1,2,NULL);
+1 NOT IN (1,2,NULL)
+0
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 1.0 IN (1.0,2.0);
+1.0 IN (1.0,2.0)
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 1.0 IN (1.0,2.0,NULL);
+1.0 IN (1.0,2.0,NULL)
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 1.0 NOT IN (1.0,2.0);
+1.0 NOT IN (1.0,2.0)
+0
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 1.0 NOT IN (1.0,2.0,NULL);
+1.0 NOT IN (1.0,2.0,NULL)
+0
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 1e0 IN (1e0,2e0);
+1e0 IN (1e0,2e0)
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 1e0 IN (1e0,2e0,NULL);
+1e0 IN (1e0,2e0,NULL)
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 1e0 NOT IN (1e0,2e0);
+1e0 NOT IN (1e0,2e0)
+0
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 1e0 NOT IN (1e0,2e0,NULL);
+1e0 NOT IN (1e0,2e0,NULL)
+0
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 'a' IN ('a','b');
+'a' IN ('a','b')
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 'a' IN ('a','b',NULL);
+'a' IN ('a','b',NULL)
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 'a' NOT IN ('a','b');
+'a' NOT IN ('a','b')
+0
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 'a' NOT IN ('a','b',NULL);
+'a' NOT IN ('a','b',NULL)
+0
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT TIMESTAMP'2001-01-01 10:20:30' IN ('2001-01-01 10:20:30','2001-02-02 10:20:30');
+TIMESTAMP'2001-01-01 10:20:30' IN ('2001-01-01 10:20:30','2001-02-02 10:20:30')
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT TIMESTAMP'2001-01-01 10:20:30' IN ('2001-01-01 10:20:30','2001-02-02 10:20:30',NULL);
+TIMESTAMP'2001-01-01 10:20:30' IN ('2001-01-01 10:20:30','2001-02-02 10:20:30',NULL)
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT TIMESTAMP'2001-01-01 10:20:30' NOT IN ('2001-01-01 10:20:30','2001-02-02 10:20:30');
+TIMESTAMP'2001-01-01 10:20:30' NOT IN ('2001-01-01 10:20:30','2001-02-02 10:20:30')
+0
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT TIMESTAMP'2001-01-01 10:20:30' NOT IN ('2001-01-01 10:20:30','2001-02-02 10:20:30',NULL);
+TIMESTAMP'2001-01-01 10:20:30' NOT IN ('2001-01-01 10:20:30','2001-02-02 10:20:30',NULL)
+0
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT TIME'10:20:30' IN ('10:20:30','10:20:30');
+TIME'10:20:30' IN ('10:20:30','10:20:30')
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT TIME'10:20:30' IN ('10:20:30','10:20:30',NULL);
+TIME'10:20:30' IN ('10:20:30','10:20:30',NULL)
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT TIME'10:20:30' NOT IN ('10:20:30','10:20:30');
+TIME'10:20:30' NOT IN ('10:20:30','10:20:30')
+0
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT TIME'10:20:30' NOT IN ('10:20:30','10:20:30',NULL);
+TIME'10:20:30' NOT IN ('10:20:30','10:20:30',NULL)
+0
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT DATE'2001-01-01' IN ('2001-01-01','2001-02-02');
+DATE'2001-01-01' IN ('2001-01-01','2001-02-02')
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT DATE'2001-01-01' IN ('2001-01-01','2001-02-02',NULL);
+DATE'2001-01-01' IN ('2001-01-01','2001-02-02',NULL)
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT DATE'2001-01-01' NOT IN ('2001-01-01','2001-02-02');
+DATE'2001-01-01' NOT IN ('2001-01-01','2001-02-02')
+0
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT DATE'2001-01-01' NOT IN ('2001-01-01','2001-02-02',NULL);
+DATE'2001-01-01' NOT IN ('2001-01-01','2001-02-02',NULL)
+0
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+# Column predicant, compatible types, bisect
+CREATE TABLE t1 (a INT);
+SELECT a IN (1,2,3) FROM t1;
+a IN (1,2,3)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a IN (1,2,3,NULL) FROM t1;
+a IN (1,2,3,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a NOT IN (1,2,3) FROM t1;
+a NOT IN (1,2,3)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a NOT IN (1,2,3,NULL) FROM t1;
+a NOT IN (1,2,3,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+CREATE TABLE t1 (a DOUBLE);
+SELECT a IN (1e0,2,3.0) FROM t1;
+a IN (1e0,2,3.0)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a IN (1e0,2,3.0,NULL) FROM t1;
+a IN (1e0,2,3.0,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a NOT IN (1e0,2,3.0) FROM t1;
+a NOT IN (1e0,2,3.0)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a NOT IN (1e0,2,3.0,NULL) FROM t1;
+a NOT IN (1e0,2,3.0,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(10,1));
+SELECT a IN (1,2.0,3.0) FROM t1;
+a IN (1,2.0,3.0)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a IN (1,2.0,3.0,NULL) FROM t1;
+a IN (1,2.0,3.0,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a NOT IN (1,2.0,3.0) FROM t1;
+a NOT IN (1,2.0,3.0)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a NOT IN (1,2.0,3.0,NULL) FROM t1;
+a NOT IN (1,2.0,3.0,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10));
+SELECT a IN ('a','b','c') FROM t1;
+a IN ('a','b','c')
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a IN ('a','b','c',NULL) FROM t1;
+a IN ('a','b','c',NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a NOT IN ('a','b','c') FROM t1;
+a NOT IN ('a','b','c')
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a NOT IN ('a','b','c',NULL) FROM t1;
+a NOT IN ('a','b','c',NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE);
+SELECT a IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0) FROM t1;
+a IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0,NULL) FROM t1;
+a IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a NOT IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0) FROM t1;
+a NOT IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a NOT IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0,NULL) FROM t1;
+a NOT IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+CREATE TABLE t1 (a TIME);
+SELECT a IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0) FROM t1;
+a IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0,NULL) FROM t1;
+a IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a NOT IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0) FROM t1;
+a NOT IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a NOT IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0,NULL) FROM t1;
+a NOT IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME);
+SELECT a IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0) FROM t1;
+a IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0,NULL) FROM t1;
+a IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a NOT IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0) FROM t1;
+a NOT IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT a NOT IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0,NULL) FROM t1;
+a NOT IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+# Constant predicant, compatible types, no bisect
+# Bisect is not used because of non-constant expressions in the list
+CREATE TABLE t1 (a INT);
+SELECT 1 IN (a,1,2,3) FROM t1;
+1 IN (a,1,2,3)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 1 IN (a,1,2,3,NULL) FROM t1;
+1 IN (a,1,2,3,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 1 NOT IN (a,1,2,3) FROM t1;
+1 NOT IN (a,1,2,3)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 1 NOT IN (a,1,2,3,NULL) FROM t1;
+1 NOT IN (a,1,2,3,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a DOUBLE);
+SELECT 1 IN (a,1e0,2e0,3e0) FROM t1;
+1 IN (a,1e0,2e0,3e0)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 1 IN (a,1e0,2e0,3e0,NULL) FROM t1;
+1 IN (a,1e0,2e0,3e0,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 1 NOT IN (a,1e0,2e0,3e0) FROM t1;
+1 NOT IN (a,1e0,2e0,3e0)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 1 NOT IN (a,1e0,2e0,3e0,NULL) FROM t1;
+1 NOT IN (a,1e0,2e0,3e0,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(10,1));
+SELECT 1 IN (a,1.0,2.0,3.0) FROM t1;
+1 IN (a,1.0,2.0,3.0)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 1 IN (a,1.0,2.0,3.0,NULL) FROM t1;
+1 IN (a,1.0,2.0,3.0,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 1 NOT IN (a,1.0,2.0,3.0) FROM t1;
+1 NOT IN (a,1.0,2.0,3.0)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 1 NOT IN (a,1.0,2.0,3.0,NULL) FROM t1;
+1 NOT IN (a,1.0,2.0,3.0,NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10));
+SELECT 'a' IN (a,'b','c') FROM t1;
+'a' IN (a,'b','c')
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 'a' IN (a,'b','c',NULL) FROM t1;
+'a' IN (a,'b','c',NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 'a' NOT IN (a,'b','c') FROM t1;
+'a' NOT IN (a,'b','c')
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 'a' NOT IN (a,'b','c',NULL) FROM t1;
+'a' NOT IN (a,'b','c',NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE);
+SELECT DATE'2001-01-01' IN (a,'2001-01-01') FROM t1;
+DATE'2001-01-01' IN (a,'2001-01-01')
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT DATE'2001-01-01' IN (a,'2001-01-01',NULL) FROM t1;
+DATE'2001-01-01' IN (a,'2001-01-01',NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT DATE'2001-01-01' NOT IN (a,'2001-01-01') FROM t1;
+DATE'2001-01-01' NOT IN (a,'2001-01-01')
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT DATE'2001-01-01' NOT IN (a,'2001-01-01',NULL) FROM t1;
+DATE'2001-01-01' NOT IN (a,'2001-01-01',NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a TIME);
+SELECT TIME'10:20:30' IN (a,'10:20:30') FROM t1;
+TIME'10:20:30' IN (a,'10:20:30')
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT TIME'10:20:30' IN (a,'10:20:30',NULL) FROM t1;
+TIME'10:20:30' IN (a,'10:20:30',NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT TIME'10:20:30' NOT IN (a,'10:20:30') FROM t1;
+TIME'10:20:30' NOT IN (a,'10:20:30')
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT TIME'10:20:30' NOT IN (a,'10:20:30',NULL) FROM t1;
+TIME'10:20:30' NOT IN (a,'10:20:30',NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME);
+SELECT TIMESTAMP'2001-01-01 10:20:30' IN (a,TIMESTAMP'2001-01-01 10:20:30') FROM t1;
+TIMESTAMP'2001-01-01 10:20:30' IN (a,TIMESTAMP'2001-01-01 10:20:30')
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT TIMESTAMP'2001-01-01 10:20:30' IN (a,TIMESTAMP'2001-01-01 10:20:30',NULL) FROM t1;
+TIMESTAMP'2001-01-01 10:20:30' IN (a,TIMESTAMP'2001-01-01 10:20:30',NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT TIMESTAMP'2001-01-01 10:20:30' NOT IN (a,TIMESTAMP'2001-01-01 10:20:30') FROM t1;
+TIMESTAMP'2001-01-01 10:20:30' NOT IN (a,TIMESTAMP'2001-01-01 10:20:30')
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT TIMESTAMP'2001-01-01 10:20:30' NOT IN (a,TIMESTAMP'2001-01-01 10:20:30',NULL) FROM t1;
+TIMESTAMP'2001-01-01 10:20:30' NOT IN (a,TIMESTAMP'2001-01-01 10:20:30',NULL)
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+DROP TABLE t1;
+# Constant predicant, incompatible types, no bisect
+SELECT 1 IN (1,2e0);
+1 IN (1,2e0)
+1
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT 1 IN (1,2e0,NULL);
+1 IN (1,2e0,NULL)
+1
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT 1 NOT IN (1,2e0);
+1 NOT IN (1,2e0)
+0
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT 1 NOT IN (1,2e0,NULL);
+1 NOT IN (1,2e0,NULL)
+0
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT 1.0 IN (1.0,2e0);
+1.0 IN (1.0,2e0)
+1
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT 1.0 IN (1.0,2e0,NULL);
+1.0 IN (1.0,2e0,NULL)
+1
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT 1.0 NOT IN (1.0,2e0);
+1.0 NOT IN (1.0,2e0)
+0
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT 1.0 NOT IN (1.0,2e0,NULL);
+1.0 NOT IN (1.0,2e0,NULL)
+0
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT 1e0 IN (1.0,TIME'10:20:30');
+1e0 IN (1.0,TIME'10:20:30')
+1
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT 1e0 IN (1.0,TIME'10:20:30',NULL);
+1e0 IN (1.0,TIME'10:20:30',NULL)
+1
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT 1e0 NOT IN (1.0,TIME'10:20:30');
+1e0 NOT IN (1.0,TIME'10:20:30')
+0
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT 1e0 NOT IN (1.0,TIME'10:20:30',NULL);
+1e0 NOT IN (1.0,TIME'10:20:30',NULL)
+0
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT 'a' IN ('a',2);
+'a' IN ('a',2)
+1
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT 'a' IN ('a',2,NULL);
+'a' IN ('a',2,NULL)
+1
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT 'a' NOT IN ('a',2);
+'a' NOT IN ('a',2)
+0
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT 'a' NOT IN ('a',2,NULL);
+'a' NOT IN ('a',2,NULL)
+0
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+# Column predicant, incompatible types, no bisect
+CREATE TABLE t1 (a INT);
+SELECT a IN (1,1e0) FROM t1;
+a IN (1,1e0)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,1e0,NULL) FROM t1;
+a IN (1,1e0,NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,1e0) FROM t1;
+a NOT IN (1,1e0)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,1e0,NULL) FROM t1;
+a NOT IN (1,1e0,NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,1.0) FROM t1;
+a IN (1,1.0)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,1.0,NULL) FROM t1;
+a IN (1,1.0,NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,1.0) FROM t1;
+a NOT IN (1,1.0)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,1.0,NULL) FROM t1;
+a NOT IN (1,1.0,NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,'1') FROM t1;
+a IN (1,'1')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,'1',NULL) FROM t1;
+a IN (1,'1',NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,'1') FROM t1;
+a NOT IN (1,'1')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,'1',NULL) FROM t1;
+a NOT IN (1,'1',NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,TIME'10:20:30') FROM t1;
+a IN (1,TIME'10:20:30')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,TIME'10:20:30',NULL) FROM t1;
+a IN (1,TIME'10:20:30',NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,TIME'10:20:30') FROM t1;
+a NOT IN (1,TIME'10:20:30')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,TIME'10:20:30',NULL) FROM t1;
+a NOT IN (1,TIME'10:20:30',NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(10,0));
+SELECT a IN (1,1e0) FROM t1;
+a IN (1,1e0)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,1e0,NULL) FROM t1;
+a IN (1,1e0,NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,1e0) FROM t1;
+a NOT IN (1,1e0)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,1e0,NULL) FROM t1;
+a NOT IN (1,1e0,NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,'1') FROM t1;
+a IN (1,'1')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,'1',NULL) FROM t1;
+a IN (1,'1',NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,'1') FROM t1;
+a NOT IN (1,'1')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,'1',NULL) FROM t1;
+a NOT IN (1,'1',NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,TIME'10:20:30') FROM t1;
+a IN (1,TIME'10:20:30')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,TIME'10:20:30',NULL) FROM t1;
+a IN (1,TIME'10:20:30',NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,TIME'10:20:30') FROM t1;
+a NOT IN (1,TIME'10:20:30')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,TIME'10:20:30',NULL) FROM t1;
+a NOT IN (1,TIME'10:20:30',NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a DOUBLE);
+SELECT a IN (1,TIME'10:20:30') FROM t1;
+a IN (1,TIME'10:20:30')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,TIME'10:20:30',NULL) FROM t1;
+a IN (1,TIME'10:20:30',NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,TIME'10:20:30') FROM t1;
+a NOT IN (1,TIME'10:20:30')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,TIME'10:20:30',NULL) FROM t1;
+a NOT IN (1,TIME'10:20:30',NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,DATE'2001-01-01') FROM t1;
+a IN (1,DATE'2001-01-01')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,DATE'2001-01-01',NULL) FROM t1;
+a IN (1,DATE'2001-01-01',NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,DATE'2001-01-01') FROM t1;
+a NOT IN (1,DATE'2001-01-01')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,DATE'2001-01-01',NULL) FROM t1;
+a NOT IN (1,DATE'2001-01-01',NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,TIMESTAMP'2001-01-01 10:20:30') FROM t1;
+a IN (1,TIMESTAMP'2001-01-01 10:20:30')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN (1,TIMESTAMP'2001-01-01 10:20:30',NULL) FROM t1;
+a IN (1,TIMESTAMP'2001-01-01 10:20:30',NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,TIMESTAMP'2001-01-01 10:20:30') FROM t1;
+a NOT IN (1,TIMESTAMP'2001-01-01 10:20:30')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN (1,TIMESTAMP'2001-01-01 10:20:30',NULL) FROM t1;
+a NOT IN (1,TIMESTAMP'2001-01-01 10:20:30',NULL)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10));
+SELECT a IN ('a',1) FROM t1;
+a IN ('a',1)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a IN ('a',TIME'10:20:30') FROM t1;
+a IN ('a',TIME'10:20:30')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN ('a',1) FROM t1;
+a NOT IN ('a',1)
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+SELECT a NOT IN ('a',TIME'10:20:30') FROM t1;
+a NOT IN ('a',TIME'10:20:30')
+Warnings:
+Warning	1105	types_compatible=no bisect=no
+DROP TABLE t1;
+# Not top level, negated: cond3 is false
+CREATE TABLE t1 (a INT);
+SELECT ROW(a,a) NOT IN ((1,1),(2,NULL)) FROM t1;
+ROW(a,a) NOT IN ((1,1),(2,NULL))
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT ROW(a,a) NOT IN ((1,1),(2,2)) FROM t1;
+ROW(a,a) NOT IN ((1,1),(2,2))
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL, b INT);
+SELECT ROW(a,a) NOT IN ((1,1),(2,NULL)) FROM t1;
+ROW(a,a) NOT IN ((1,1),(2,NULL))
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT ROW(a,a) NOT IN ((1,1),(2,2)) FROM t1;
+ROW(a,a) NOT IN ((1,1),(2,2))
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+SELECT ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,NULL))) FROM t1;
+ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,NULL)))
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,2))) FROM t1;
+ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,2)))
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL, b INT);
+SELECT ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,NULL))) FROM t1;
+ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,NULL)))
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,2))) FROM t1;
+ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,2)))
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+# Not top level, not negated: cond3 is false
+CREATE TABLE t1 (a INT);
+SELECT ROW(a,a) IN ((1,1),(2,NULL)) FROM t1;
+ROW(a,a) IN ((1,1),(2,NULL))
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT ROW(a,a) IN ((1,1),(2,2)) FROM t1;
+ROW(a,a) IN ((1,1),(2,2))
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL);
+SELECT ROW(a,a) IN ((1,1),(2,NULL)) FROM t1;
+ROW(a,a) IN ((1,1),(2,NULL))
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT ROW(a,a) IN ((1,1),(2,2)) FROM t1;
+ROW(a,a) IN ((1,1),(2,2))
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+SELECT ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,NULL))) FROM t1;
+ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,NULL)))
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,2))) FROM t1;
+ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,2)))
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL);
+SELECT ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,NULL))) FROM t1;
+ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,NULL)))
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,2))) FROM t1;
+ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,2)))
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+# Top level, negated: cond3 is false
+CREATE TABLE t1 (a INT);
+SELECT 1 FROM t1 WHERE ROW(a,a) NOT IN ((1,1),(2,NULL));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 1 FROM t1 WHERE ROW(a,a) NOT IN ((1,1),(2,2));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL);
+SELECT 1 FROM t1 WHERE ROW(a,a) NOT IN ((1,1),(2,NULL));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 1 FROM t1 WHERE ROW(a,a) NOT IN ((1,1),(2,2));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,NULL)));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,2)));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL);
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,NULL)));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=no
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,2)));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+# Top level, not negated: cond3 is true
+CREATE TABLE t1 (a INT);
+SELECT 1 FROM t1 WHERE ROW(a,a) IN ((1,1),(2,NULL));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 1 FROM t1 WHERE ROW(a,a) IN ((1,1),(2,2));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL);
+SELECT 1 FROM t1 WHERE ROW(a,a) IN ((1,1),(2,NULL));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 1 FROM t1 WHERE ROW(a,a) IN ((1,1),(2,2));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,NULL)));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,2)));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL);
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,NULL)));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,2)));
+1
+Warnings:
+Warning	1105	types_compatible=yes bisect=yes
+DROP TABLE t1;
+SET SESSION debug_dbug="-d,Item_func_in";
diff --git a/mysql-test/t/func_debug.test b/mysql-test/t/func_debug.test
new file mode 100644
index 0000000..113fccc
--- /dev/null
+++ b/mysql-test/t/func_debug.test
@@ -0,0 +1,359 @@
+--source include/have_debug.inc
+
+SET SESSION debug_dbug="+d,Item_func_in";
+
+--echo # Constant predicant, compatible types, bisect
+SELECT 1 IN (1,2);
+SELECT 1 IN (1,2,NULL);
+SELECT 1 NOT IN (1,2);
+SELECT 1 NOT IN (1,2,NULL);
+
+SELECT 1.0 IN (1.0,2.0);
+SELECT 1.0 IN (1.0,2.0,NULL);
+SELECT 1.0 NOT IN (1.0,2.0);
+SELECT 1.0 NOT IN (1.0,2.0,NULL);
+
+SELECT 1e0 IN (1e0,2e0);
+SELECT 1e0 IN (1e0,2e0,NULL);
+SELECT 1e0 NOT IN (1e0,2e0);
+SELECT 1e0 NOT IN (1e0,2e0,NULL);
+
+SELECT 'a' IN ('a','b');
+SELECT 'a' IN ('a','b',NULL);
+SELECT 'a' NOT IN ('a','b');
+SELECT 'a' NOT IN ('a','b',NULL);
+
+SELECT TIMESTAMP'2001-01-01 10:20:30' IN ('2001-01-01 10:20:30','2001-02-02 10:20:30');
+SELECT TIMESTAMP'2001-01-01 10:20:30' IN ('2001-01-01 10:20:30','2001-02-02 10:20:30',NULL);
+SELECT TIMESTAMP'2001-01-01 10:20:30' NOT IN ('2001-01-01 10:20:30','2001-02-02 10:20:30');
+SELECT TIMESTAMP'2001-01-01 10:20:30' NOT IN ('2001-01-01 10:20:30','2001-02-02 10:20:30',NULL);
+
+SELECT TIME'10:20:30' IN ('10:20:30','10:20:30');
+SELECT TIME'10:20:30' IN ('10:20:30','10:20:30',NULL);
+SELECT TIME'10:20:30' NOT IN ('10:20:30','10:20:30');
+SELECT TIME'10:20:30' NOT IN ('10:20:30','10:20:30',NULL);
+
+SELECT DATE'2001-01-01' IN ('2001-01-01','2001-02-02');
+SELECT DATE'2001-01-01' IN ('2001-01-01','2001-02-02',NULL);
+SELECT DATE'2001-01-01' NOT IN ('2001-01-01','2001-02-02');
+SELECT DATE'2001-01-01' NOT IN ('2001-01-01','2001-02-02',NULL);
+
+
+--echo # Column predicant, compatible types, bisect
+
+CREATE TABLE t1 (a INT);
+SELECT a IN (1,2,3) FROM t1;
+SELECT a IN (1,2,3,NULL) FROM t1;
+SELECT a NOT IN (1,2,3) FROM t1;
+SELECT a NOT IN (1,2,3,NULL) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DOUBLE);
+SELECT a IN (1e0,2,3.0) FROM t1;
+SELECT a IN (1e0,2,3.0,NULL) FROM t1;
+SELECT a NOT IN (1e0,2,3.0) FROM t1;
+SELECT a NOT IN (1e0,2,3.0,NULL) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(10,1));
+SELECT a IN (1,2.0,3.0) FROM t1;
+SELECT a IN (1,2.0,3.0,NULL) FROM t1;
+SELECT a NOT IN (1,2.0,3.0) FROM t1;
+SELECT a NOT IN (1,2.0,3.0,NULL) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a VARCHAR(10));
+SELECT a IN ('a','b','c') FROM t1;
+SELECT a IN ('a','b','c',NULL) FROM t1;
+SELECT a NOT IN ('a','b','c') FROM t1;
+SELECT a NOT IN ('a','b','c',NULL) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATE);
+SELECT a IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0) FROM t1;
+SELECT a IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0,NULL) FROM t1;
+SELECT a NOT IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0) FROM t1;
+SELECT a NOT IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0,NULL) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a TIME);
+SELECT a IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0) FROM t1;
+SELECT a IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0,NULL) FROM t1;
+SELECT a NOT IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0) FROM t1;
+SELECT a NOT IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0,NULL) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATETIME);
+SELECT a IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0) FROM t1;
+SELECT a IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0,NULL) FROM t1;
+SELECT a NOT IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0) FROM t1;
+SELECT a NOT IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0,NULL) FROM t1;
+DROP TABLE t1;
+
+--echo # Constant predicant, compatible types, no bisect
+--echo # Bisect is not used because of non-constant expressions in the list
+CREATE TABLE t1 (a INT);
+SELECT 1 IN (a,1,2,3) FROM t1;
+SELECT 1 IN (a,1,2,3,NULL) FROM t1;
+SELECT 1 NOT IN (a,1,2,3) FROM t1;
+SELECT 1 NOT IN (a,1,2,3,NULL) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DOUBLE);
+SELECT 1 IN (a,1e0,2e0,3e0) FROM t1;
+SELECT 1 IN (a,1e0,2e0,3e0,NULL) FROM t1;
+SELECT 1 NOT IN (a,1e0,2e0,3e0) FROM t1;
+SELECT 1 NOT IN (a,1e0,2e0,3e0,NULL) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(10,1));
+SELECT 1 IN (a,1.0,2.0,3.0) FROM t1;
+SELECT 1 IN (a,1.0,2.0,3.0,NULL) FROM t1;
+SELECT 1 NOT IN (a,1.0,2.0,3.0) FROM t1;
+SELECT 1 NOT IN (a,1.0,2.0,3.0,NULL) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a VARCHAR(10));
+SELECT 'a' IN (a,'b','c') FROM t1;
+SELECT 'a' IN (a,'b','c',NULL) FROM t1;
+SELECT 'a' NOT IN (a,'b','c') FROM t1;
+SELECT 'a' NOT IN (a,'b','c',NULL) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATE);
+SELECT DATE'2001-01-01' IN (a,'2001-01-01') FROM t1;
+SELECT DATE'2001-01-01' IN (a,'2001-01-01',NULL) FROM t1;
+SELECT DATE'2001-01-01' NOT IN (a,'2001-01-01') FROM t1;
+SELECT DATE'2001-01-01' NOT IN (a,'2001-01-01',NULL) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a TIME);
+SELECT TIME'10:20:30' IN (a,'10:20:30') FROM t1;
+SELECT TIME'10:20:30' IN (a,'10:20:30',NULL) FROM t1;
+SELECT TIME'10:20:30' NOT IN (a,'10:20:30') FROM t1;
+SELECT TIME'10:20:30' NOT IN (a,'10:20:30',NULL) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATETIME);
+SELECT TIMESTAMP'2001-01-01 10:20:30' IN (a,TIMESTAMP'2001-01-01 10:20:30') FROM t1;
+SELECT TIMESTAMP'2001-01-01 10:20:30' IN (a,TIMESTAMP'2001-01-01 10:20:30',NULL) FROM t1;
+SELECT TIMESTAMP'2001-01-01 10:20:30' NOT IN (a,TIMESTAMP'2001-01-01 10:20:30') FROM t1;
+SELECT TIMESTAMP'2001-01-01 10:20:30' NOT IN (a,TIMESTAMP'2001-01-01 10:20:30',NULL) FROM t1;
+DROP TABLE t1;
+
+
+--echo # Constant predicant, incompatible types, no bisect
+SELECT 1 IN (1,2e0);
+SELECT 1 IN (1,2e0,NULL);
+SELECT 1 NOT IN (1,2e0);
+SELECT 1 NOT IN (1,2e0,NULL);
+
+SELECT 1.0 IN (1.0,2e0);
+SELECT 1.0 IN (1.0,2e0,NULL);
+SELECT 1.0 NOT IN (1.0,2e0);
+SELECT 1.0 NOT IN (1.0,2e0,NULL);
+
+SELECT 1e0 IN (1.0,TIME'10:20:30');
+SELECT 1e0 IN (1.0,TIME'10:20:30',NULL);
+SELECT 1e0 NOT IN (1.0,TIME'10:20:30');
+SELECT 1e0 NOT IN (1.0,TIME'10:20:30',NULL);
+
+SELECT 'a' IN ('a',2);
+SELECT 'a' IN ('a',2,NULL);
+SELECT 'a' NOT IN ('a',2);
+SELECT 'a' NOT IN ('a',2,NULL);
+
+--echo # Column predicant, incompatible types, no bisect
+CREATE TABLE t1 (a INT);
+SELECT a IN (1,1e0) FROM t1;
+SELECT a IN (1,1e0,NULL) FROM t1;
+SELECT a NOT IN (1,1e0) FROM t1;
+SELECT a NOT IN (1,1e0,NULL) FROM t1;
+
+SELECT a IN (1,1.0) FROM t1;
+SELECT a IN (1,1.0,NULL) FROM t1;
+SELECT a NOT IN (1,1.0) FROM t1;
+SELECT a NOT IN (1,1.0,NULL) FROM t1;
+
+SELECT a IN (1,'1') FROM t1;
+SELECT a IN (1,'1',NULL) FROM t1;
+SELECT a NOT IN (1,'1') FROM t1;
+SELECT a NOT IN (1,'1',NULL) FROM t1;
+
+SELECT a IN (1,TIME'10:20:30') FROM t1;
+SELECT a IN (1,TIME'10:20:30',NULL) FROM t1;
+SELECT a NOT IN (1,TIME'10:20:30') FROM t1;
+SELECT a NOT IN (1,TIME'10:20:30',NULL) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(10,0));
+SELECT a IN (1,1e0) FROM t1;
+SELECT a IN (1,1e0,NULL) FROM t1;
+SELECT a NOT IN (1,1e0) FROM t1;
+SELECT a NOT IN (1,1e0,NULL) FROM t1;
+
+SELECT a IN (1,'1') FROM t1;
+SELECT a IN (1,'1',NULL) FROM t1;
+SELECT a NOT IN (1,'1') FROM t1;
+SELECT a NOT IN (1,'1',NULL) FROM t1;
+
+SELECT a IN (1,TIME'10:20:30') FROM t1;
+SELECT a IN (1,TIME'10:20:30',NULL) FROM t1;
+SELECT a NOT IN (1,TIME'10:20:30') FROM t1;
+SELECT a NOT IN (1,TIME'10:20:30',NULL) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DOUBLE);
+SELECT a IN (1,TIME'10:20:30') FROM t1;
+SELECT a IN (1,TIME'10:20:30',NULL) FROM t1;
+SELECT a NOT IN (1,TIME'10:20:30') FROM t1;
+SELECT a NOT IN (1,TIME'10:20:30',NULL) FROM t1;
+
+SELECT a IN (1,DATE'2001-01-01') FROM t1;
+SELECT a IN (1,DATE'2001-01-01',NULL) FROM t1;
+SELECT a NOT IN (1,DATE'2001-01-01') FROM t1;
+SELECT a NOT IN (1,DATE'2001-01-01',NULL) FROM t1;
+
+SELECT a IN (1,TIMESTAMP'2001-01-01 10:20:30') FROM t1;
+SELECT a IN (1,TIMESTAMP'2001-01-01 10:20:30',NULL) FROM t1;
+SELECT a NOT IN (1,TIMESTAMP'2001-01-01 10:20:30') FROM t1;
+SELECT a NOT IN (1,TIMESTAMP'2001-01-01 10:20:30',NULL) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a VARCHAR(10));
+SELECT a IN ('a',1) FROM t1;
+SELECT a IN ('a',TIME'10:20:30') FROM t1;
+SELECT a NOT IN ('a',1) FROM t1;
+SELECT a NOT IN ('a',TIME'10:20:30') FROM t1;
+DROP TABLE t1;
+
+#  ((is_top_level_item && not_negated) ||             // 3
+#  (arg0_can_not_be_null && list_does_not_have_nulls) // 4
+
+# tli     - is_top_level_item
+# nneg    - not_negated
+# a0nnul  - arg0_can_not_be_null
+# lnnul   - list_does_not_hace_nulls
+# cond3   - condition 3 is true?
+# cond4   - condition 4 is true?
+# bisect  - bisect is possible (cond3 orded with cond4)
+
+# Note:
+# - using an expression in SELECT list makes top_level_item() to be false
+# - using an expression in WHERE clause makes top_leve_item() to be true
+
+--echo # Not top level, negated: cond3 is false
+
+#  tli nneg a0nnul  lnnul    cond3  cond4 bisect
+#  ---  --- ------  -----    -----  ----- ------
+#    0    0      0      0        0      0      0
+#    0    0      0      1        0      0      0
+#    0    0      1      0        0      0      0
+#    0    0      1      1        0      1      1
+
+CREATE TABLE t1 (a INT);
+SELECT ROW(a,a) NOT IN ((1,1),(2,NULL)) FROM t1;
+SELECT ROW(a,a) NOT IN ((1,1),(2,2)) FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL, b INT);
+SELECT ROW(a,a) NOT IN ((1,1),(2,NULL)) FROM t1;
+SELECT ROW(a,a) NOT IN ((1,1),(2,2)) FROM t1;
+DROP TABLE t1;
+
+# ROW with a nested ROW
+CREATE TABLE t1 (a INT);
+SELECT ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,NULL))) FROM t1;
+SELECT ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,2))) FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL, b INT);
+SELECT ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,NULL))) FROM t1;
+SELECT ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,2))) FROM t1;
+DROP TABLE t1;
+
+--echo # Not top level, not negated: cond3 is false
+
+#  tli nneg a0nnul  lnnul    cond3  cond4 bisect
+#  ---  --- ------  -----    -----  ----- ------
+#    0    1      0      0        0      0      0
+#    0    1      0      1        0      0      0
+#    0    1      1      0        0      0      0
+#    0    1      1      1        0      1      1
+
+CREATE TABLE t1 (a INT);
+SELECT ROW(a,a) IN ((1,1),(2,NULL)) FROM t1;
+SELECT ROW(a,a) IN ((1,1),(2,2)) FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL);
+SELECT ROW(a,a) IN ((1,1),(2,NULL)) FROM t1;
+SELECT ROW(a,a) IN ((1,1),(2,2)) FROM t1;
+DROP TABLE t1;
+
+# ROW with a nested ROW
+CREATE TABLE t1 (a INT);
+SELECT ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,NULL))) FROM t1;
+SELECT ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,2))) FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL);
+SELECT ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,NULL))) FROM t1;
+SELECT ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,2))) FROM t1;
+DROP TABLE t1;
+
+
+--echo # Top level, negated: cond3 is false
+
+#  tli nneg a0nnul  lnnul    cond3  cond4 bisect
+#  ---  --- ------  -----    -----  ----- ------
+#    1    0      0      0        0      0      0
+#    1    0      0      1        0      0      0
+#    1    0      1      0        0      0      0
+#    1    0      1      1        0      1      1
+
+CREATE TABLE t1 (a INT);
+SELECT 1 FROM t1 WHERE ROW(a,a) NOT IN ((1,1),(2,NULL));
+SELECT 1 FROM t1 WHERE ROW(a,a) NOT IN ((1,1),(2,2));
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL);
+SELECT 1 FROM t1 WHERE ROW(a,a) NOT IN ((1,1),(2,NULL));
+SELECT 1 FROM t1 WHERE ROW(a,a) NOT IN ((1,1),(2,2));
+DROP TABLE t1;
+
+# ROW with a nested ROW
+CREATE TABLE t1 (a INT);
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,NULL)));
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,2)));
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL);
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,NULL)));
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,2)));
+DROP TABLE t1;
+
+--echo # Top level, not negated: cond3 is true
+
+#  tli nneg a0nnul  lnnul    cond3  cond4 bisect
+#  ---  --- ------  -----    -----  ----- ------
+#    1    1      0      0        1      0      1
+#    1    1      0      1        1      1      1
+#    1    1      1      0        1      0      1
+#    1    1      1      1        1      1      1
+
+CREATE TABLE t1 (a INT);
+SELECT 1 FROM t1 WHERE ROW(a,a) IN ((1,1),(2,NULL));
+SELECT 1 FROM t1 WHERE ROW(a,a) IN ((1,1),(2,2));
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL);
+SELECT 1 FROM t1 WHERE ROW(a,a) IN ((1,1),(2,NULL));
+SELECT 1 FROM t1 WHERE ROW(a,a) IN ((1,1),(2,2));
+DROP TABLE t1;
+
+# ROW with a nested ROW
+CREATE TABLE t1 (a INT);
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,NULL)));
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,2)));
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL);
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,NULL)));
+SELECT 1 FROM t1 WHERE ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,2)));
+DROP TABLE t1;
+
+
+SET SESSION debug_dbug="-d,Item_func_in";
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 65754d2..61c5f5b 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -3718,16 +3718,22 @@ void in_datetime::set(uint pos,Item *item)
 {
   struct packed_longlong *buff= &((packed_longlong*) base)[pos];
 
-  buff->val= item->val_temporal_packed(warn_item);
+  buff->val= item->val_datetime_packed();
   buff->unsigned_flag= 1L;
 }
 
-uchar *in_datetime::get_value(Item *item)
+void in_time::set(uint pos,Item *item)
+{
+  struct packed_longlong *buff= &((packed_longlong*) base)[pos];
+
+  buff->val= item->val_time_packed();
+  buff->unsigned_flag= 1L;
+}
+
+uchar *in_temporal::get_value_internal(Item *item, enum_field_types f_type)
 {
   bool is_null;
   Item **tmp_item= lval_cache ? &lval_cache : &item;
-  enum_field_types f_type=
-    tmp_item[0]->field_type_for_temporal_comparison(warn_item);
   tmp.val= get_datetime_value(thd, &tmp_item, &lval_cache, f_type, &is_null);
   if (item->null_value)
     return 0;
@@ -3735,7 +3741,7 @@ uchar *in_datetime::get_value(Item *item)
   return (uchar*) &tmp;
 }
 
-Item *in_datetime::create_item(THD *thd)
+Item *in_temporal::create_item(THD *thd)
 { 
   return new (thd->mem_root) Item_datetime(thd);
 }
@@ -3855,19 +3861,22 @@ cmp_item_row::~cmp_item_row()
 }
 
 
-void cmp_item_row::alloc_comparators()
+bool cmp_item_row::alloc_comparators(THD *thd, uint cols)
 {
-  if (!comparators)
-    comparators= (cmp_item **) current_thd->calloc(sizeof(cmp_item *)*n);
+  if (comparators)
+  {
+    DBUG_ASSERT(cols == n);
+    return false;
+  }
+  return
+    !(comparators= (cmp_item **) thd->calloc(sizeof(cmp_item *) * (n= cols)));
 }
 
 
 void cmp_item_row::store_value(Item *item)
 {
   DBUG_ENTER("cmp_item_row::store_value");
-  n= item->cols();
-  alloc_comparators();
-  if (comparators)
+  if (!alloc_comparators(current_thd, item->cols()))
   {
     item->bring_value();
     item->null_value= 0;
@@ -3875,6 +3884,20 @@ void cmp_item_row::store_value(Item *item)
     {
       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
+        */
         DBUG_ASSERT(item->element_index(i)->cmp_type() != TIME_RESULT);
         if (!(comparators[i]=
               cmp_item::get_comparator(item->element_index(i)->result_type(), 0,
@@ -4105,208 +4128,202 @@ void Item_func_in::fix_after_pullout(st_select_lex *new_parent, Item **ref)
   eval_not_null_tables(NULL);
 }
 
-static int srtcmp_in(CHARSET_INFO *cs, const String *x,const String *y)
-{
-  return cs->coll->strnncollsp(cs,
-                               (uchar *) x->ptr(),x->length(),
-                               (uchar *) y->ptr(),y->length());
-}
 
 void Item_func_in::fix_length_and_dec()
 {
-  Item **arg, **arg_end;
-  bool const_itm= 1;
   THD *thd= current_thd;
-  /* TRUE <=> arguments values will be compared as DATETIMEs. */
-  Item *date_arg= 0;
   uint found_types= 0;
   uint type_cnt= 0, i;
   m_comparator.set_handler(&type_handler_varchar);
   left_cmp_type= args[0]->cmp_type();
+  max_length= 1;
   if (!(found_types= collect_cmp_types(args, arg_count, true)))
     return;
   
-  for (arg= args + 1, arg_end= args + arg_count; arg != arg_end ; arg++)
-  {
-    if (!arg[0]->const_item())
-    {
-      const_itm= 0;
-      break;
-    }
-  }
   for (i= 0; i <= (uint)TIME_RESULT; i++)
   {
     if (found_types & (1U << i))
     {
       (type_cnt)++;
-      m_comparator.set_handler_by_cmp_type((Item_result) i);
+      if ((Item_result) i == TIME_RESULT)
+      {
+        Item *date_arg= find_date_time_item(args, arg_count, 0);
+        m_comparator.set_handler(date_arg ? date_arg->type_handler() :
+                                            &type_handler_datetime);
+      }
+      else
+        m_comparator.set_handler_by_cmp_type((Item_result) i);
     }
   }
 
-  /*
-    First conditions for bisection to be possible:
-     1. All types are similar, and
-     2. All expressions in <in value list> are const
-  */
-  bool bisection_possible=
-    type_cnt == 1 &&                                   // 1
-    const_itm;                                         // 2
-  if (bisection_possible)
+  if (type_cnt == 1) // Bisection condition #1
   {
-    /*
-      In the presence of NULLs, the correct result of evaluating this item
-      must be UNKNOWN or FALSE. To achieve that:
-      - If type is scalar, we can use bisection and the "have_null" boolean.
-      - If type is ROW, we will need to scan all of <in value list> when
-        searching, so bisection is impossible. Unless:
-        3. UNKNOWN and FALSE are equivalent results
-        4. Neither left expression nor <in value list> contain any NULL value
-      */
-
-    if (m_comparator.cmp_type() == ROW_RESULT &&
-        ((!is_top_level_item() || negated) &&              // 3
-         (list_contains_null() || args[0]->maybe_null)))   // 4
-      bisection_possible= false;
+    arg_types_compatible= true;
+    m_comparator.type_handler()->
+      Item_func_in_fix_comparator_compatible_types(thd, this);
   }
-
-  if (type_cnt == 1)
+  else
   {
-    if (m_comparator.cmp_type() == STRING_RESULT &&
-        agg_arg_charsets_for_comparison(cmp_collation, args, arg_count))
-      return;
-    arg_types_compatible= TRUE;
+    DBUG_ASSERT(m_comparator.cmp_type() != ROW_RESULT);
+    fix_for_scalar_comparison_using_cmp_items(found_types);
+  }
 
-    if (m_comparator.cmp_type() == ROW_RESULT)
-    {
-      uint cols= args[0]->cols();
-      cmp_item_row *cmp= 0;
+  DBUG_EXECUTE_IF("Item_func_in",
+                  push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+                  ER_UNKNOWN_ERROR, "types_compatible=%s bisect=%s",
+                  arg_types_compatible ? "yes" : "no",
+                  array != NULL ? "yes" : "no"););
+}
 
-      if (bisection_possible)
-      {
-        array= new (thd->mem_root) in_row(thd, arg_count-1, 0);
-        cmp= &((in_row*)array)->tmp;
-      }
-      else
-      {
-        if (!(cmp= new (thd->mem_root) cmp_item_row))
-          return;
-        cmp_items[ROW_RESULT]= cmp;
-      }
-      cmp->n= cols;
-      cmp->alloc_comparators();
 
-      for (uint col= 0; col < cols; col++)
-      {
-        date_arg= find_date_time_item(args, arg_count, col);
-        if (date_arg)
-        {
-          cmp_item **cmp= 0;
-          if (array)
-            cmp= ((in_row*)array)->tmp.comparators + col;
-          else
-            cmp= ((cmp_item_row*)cmp_items[ROW_RESULT])->comparators + col;
-          *cmp= new (thd->mem_root) cmp_item_datetime(date_arg);
-        }
-      }
+/**
+  Populate Item_func_in::array with constant not-NULL arguments and sort them.
+*/
+void Item_func_in::fix_in_vector()
+{
+  DBUG_ASSERT(array);
+  uint j=0;
+  for (uint i=1 ; i < arg_count ; i++)
+  {
+    array->set(j,args[i]);
+    if (!args[i]->null_value)
+      j++; // include this cell in the array.
+    else
+    {
+      /*
+        We don't put NULL values in array, to avoid erronous matches in
+        bisection.
+      */
+      have_null= 1;
     }
   }
+  if ((array->used_count= j))
+    array->sort();
+}
 
-  if (bisection_possible)
-  {
-    /*
-      IN must compare INT columns and constants as int values (the same
-      way as equality does).
-      So we must check here if the column on the left and all the constant 
-      values on the right can be compared as integers and adjust the 
-      comparison type accordingly.
 
-      See the comment about the similar block in Item_bool_func2
-    */  
-    if (args[0]->real_item()->type() == FIELD_ITEM &&
-        !thd->lex->is_view_context_analysis() &&
-        m_comparator.cmp_type() != INT_RESULT)
+/**
+  Convert all items in <in value list> to INT.
+
+  IN must compare INT columns and constants as int values (the same
+  way as equality does).
+  So we must check here if the column on the left and all the constant
+  values on the right can be compared as integers and adjust the
+  comparison type accordingly.
+
+  See the comment about the similar block in Item_bool_func2
+*/
+bool Item_func_in::value_list_convert_const_to_int(THD *thd)
+{
+  if (args[0]->real_item()->type() == FIELD_ITEM &&
+      !thd->lex->is_view_context_analysis())
+  {
+    Item_field *field_item= (Item_field*) (args[0]->real_item());
+    if (field_item->field_type() == MYSQL_TYPE_LONGLONG ||
+        field_item->field_type() == MYSQL_TYPE_YEAR)
     {
-      Item_field *field_item= (Item_field*) (args[0]->real_item());
-      if (field_item->field_type() ==  MYSQL_TYPE_LONGLONG ||
-          field_item->field_type() ==  MYSQL_TYPE_YEAR)
+      bool all_converted= TRUE;
+      Item **arg, **arg_end;
+      for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++)
       {
-        bool all_converted= TRUE;
-        for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++)
-        {
-           if (!convert_const_to_int(thd, field_item, &arg[0]))
-            all_converted= FALSE;
-        }
-        if (all_converted)
-          m_comparator.set_handler(&type_handler_longlong);
+         if (!convert_const_to_int(thd, field_item, &arg[0]))
+          all_converted= FALSE;
       }
+      if (all_converted)
+        m_comparator.set_handler(&type_handler_longlong);
     }
-    switch (m_comparator.cmp_type()) {
-    case STRING_RESULT:
-      array=new (thd->mem_root) in_string(thd, arg_count - 1,
-                                          (qsort2_cmp) srtcmp_in,
-                                          cmp_collation.collation);
-      break;
-    case INT_RESULT:
-      array= new (thd->mem_root) in_longlong(thd, arg_count - 1);
-      break;
-    case REAL_RESULT:
-      array= new (thd->mem_root) in_double(thd, arg_count - 1);
-      break;
-    case ROW_RESULT:
-      /*
-        The row comparator was created at the beginning but only DATETIME
-        items comparators were initialized. Call store_value() to setup
-        others.
-      */
-      ((in_row*)array)->tmp.store_value(args[0]);
-      break;
-    case DECIMAL_RESULT:
-      array= new (thd->mem_root) in_decimal(thd, arg_count - 1);
-      break;
-    case TIME_RESULT:
-      date_arg= find_date_time_item(args, arg_count, 0);
-      array= new (thd->mem_root) in_datetime(thd, date_arg, arg_count - 1);
-      break;
-    }
-    if (!array || thd->is_fatal_error)		// OOM
-      return;
-    uint j=0;
-    for (uint i=1 ; i < arg_count ; i++)
+  }
+  return thd->is_fatal_error; // Catch errrors in convert_const_to_int
+}
+
+
+/**
+  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)
+{
+  for (uint col= 0; col < n; col++)
+  {
+    Item *date_arg= find_date_time_item(args, arg_count, col);
+    if (date_arg)
     {
-      array->set(j,args[i]);
-      if (!args[i]->null_value)
-        j++; // include this cell in the array.
-      else
-      {
-        /*
-          We don't put NULL values in array, to avoid erronous matches in
-          bisection.
-        */
-        have_null= 1;
-      }
+      if (!(comparators[col]= new (thd->mem_root) cmp_item_datetime(date_arg)))
+        return true;
     }
-    if ((array->used_count= j))
-      array->sort();
   }
-  else
+  return false;
+}
+
+
+bool Item_func_in::fix_for_row_comparison_using_bisection(THD *thd)
+{
+  uint cols= args[0]->cols();
+  if (!(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 (thd->is_fatal_error)            // OOM
+    return true;
+  fix_in_vector();
+  return false;
+}
+
+
+/**
+  This method is called for scalar data types when bisection is not possible,
+    for example:
+  - Some of args[1..arg_count] are not constants.
+  - args[1..arg_count] are constants, but pairs {args[0],args[1..arg_count]}
+    are compared by different data types, e.g.:
+      WHERE decimal_expr IN (1, 1e0)
+    The pair {args[0],args[1]} is compared by type_handler_decimal.
+    The pair {args[0],args[2]} is compared by type_handler_double.
+*/
+bool Item_func_in::fix_for_scalar_comparison_using_cmp_items(uint found_types)
+{
+  Item *date_arg;
+  if (found_types & (1U << TIME_RESULT))
+    date_arg= find_date_time_item(args, arg_count, 0);
+  if (found_types & (1U << STRING_RESULT) &&
+      agg_arg_charsets_for_comparison(cmp_collation, args, arg_count))
+    return true;
+  for (uint i= 0; i <= (uint) TIME_RESULT; i++)
   {
-    if (found_types & (1U << TIME_RESULT))
-      date_arg= find_date_time_item(args, arg_count, 0);
-    if (found_types & (1U << STRING_RESULT) &&
-        agg_arg_charsets_for_comparison(cmp_collation, args, arg_count))
-      return;
-    for (i= 0; i <= (uint) TIME_RESULT; i++)
+    if (found_types & (1U << i) && !cmp_items[i])
     {
-      if (found_types & (1U << i) && !cmp_items[i])
-      {
-        if (!cmp_items[i] && !(cmp_items[i]=
-            cmp_item::get_comparator((Item_result)i, date_arg,
-                                     cmp_collation.collation)))
-          return;
-      }
+      if (!cmp_items[i] && !(cmp_items[i]=
+          cmp_item::get_comparator((Item_result)i, date_arg,
+                                   cmp_collation.collation)))
+        return true;
     }
   }
-  max_length= 1;
+  return false;
+}
+
+
+/**
+  This method is called for the ROW data type when bisection is not possible.
+*/
+bool Item_func_in::fix_for_row_comparison_using_cmp_items(THD *thd)
+{
+  uint cols= args[0]->cols();
+  cmp_item_row *cmp_row;
+  if (!(cmp_row= new (thd->mem_root) cmp_item_row) ||
+      cmp_row->alloc_comparators(thd, cols) ||
+      cmp_row->prepare_comparators(thd, args, arg_count))
+    return true;
+  cmp_items[ROW_RESULT]= cmp_row;
+  return false;
 }
 
 
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index bf7f8fe..6a59754 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1260,20 +1260,18 @@ class in_longlong :public in_vector
   If the left item is a constant one then its value is cached in the
   lval_cache variable.
 */
-class in_datetime :public in_longlong
+class in_temporal :public in_longlong
 {
+protected:
+  uchar *get_value_internal(Item *item, enum_field_types f_type);
 public:
   THD *thd;
-  /* An item used to issue warnings. */
-  Item *warn_item;
   /* Cache for the left item. */
   Item *lval_cache;
 
-  in_datetime(THD *thd, Item *warn_item_arg, uint elements)
-    :in_longlong(thd, elements), thd(current_thd), warn_item(warn_item_arg),
+  in_temporal(THD *thd, uint elements)
+    :in_longlong(thd, elements), thd(current_thd),
      lval_cache(0) {};
-  void set(uint pos,Item *item);
-  uchar *get_value(Item *item);
   Item *create_item(THD *thd);
   void value_to_item(uint pos, Item *item)
   {
@@ -1285,6 +1283,30 @@ class in_datetime :public in_longlong
 };
 
 
+class in_datetime :public in_temporal
+{
+public:
+  in_datetime(THD *thd, uint elements)
+   :in_temporal(thd, elements)
+  {}
+  void set(uint pos,Item *item);
+  uchar *get_value(Item *item)
+  { return get_value_internal(item, MYSQL_TYPE_DATETIME); }
+};
+
+
+class in_time :public in_temporal
+{
+public:
+  in_time(THD *thd, uint elements)
+   :in_temporal(thd, elements)
+  {}
+  void set(uint pos,Item *item);
+  uchar *get_value(Item *item)
+  { return get_value_internal(item, MYSQL_TYPE_TIME); }
+};
+
+
 class in_double :public in_vector
 {
   double tmp;
@@ -1597,12 +1619,24 @@ class Item_func_case :public Item_func_hybrid_field_type
 
   The current implementation distinguishes 2 cases:
   1) all items in <in value list> are constants and have the same
-    result type. This case is handled by in_vector class.
+    result type. This case is handled by in_vector class,
+    implementing fast bisection search.
   2) otherwise Item_func_in employs several cmp_item objects to perform
     comparisons of in_expr and an item from <in value list>. One cmp_item
     object for each result type. Different result types are collected in the
     fix_length_and_dec() member function by means of collect_cmp_types()
     function.
+
+  Bisection is possible when:
+  1. All types are similar
+  2. All expressions in <in value list> are const
+  In the presence of NULLs, the correct result of evaluating this item
+  must be UNKNOWN or FALSE. To achieve that:
+  - If type is scalar, we can use bisection and the "have_null" boolean.
+  - If type is ROW, we will need to scan all of <in value list> when
+    searching, so bisection is impossible. Unless:
+  3. UNKNOWN and FALSE are equivalent results
+  4. Neither left expression nor <in value list> contain any NULL value
 */
 class Item_func_in :public Item_func_opt_neg
 {
@@ -1612,6 +1646,15 @@ class Item_func_in :public Item_func_opt_neg
      IN ( (-5, (12,NULL)), ... ).
   */
   bool list_contains_null();
+  bool all_items_are_consts(Item **items, uint nitems) const
+  {
+    for (uint i= 0; i < nitems; i++)
+    {
+      if (!items[i]->const_item())
+        return false;
+    }
+    return true;
+  }
 protected:
   SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param,
                              Field *field, Item *value);
@@ -1643,6 +1686,38 @@ class Item_func_in :public Item_func_opt_neg
   longlong val_int();
   bool fix_fields(THD *, Item **);
   void fix_length_and_dec();
+  bool compatible_types_scalar_bisection_possible()
+  {
+    DBUG_ASSERT(m_comparator.cmp_type() != ROW_RESULT);
+    return all_items_are_consts(args + 1, arg_count - 1);     // Bisection #2
+  }
+  bool compatible_types_row_bisection_possible()
+  {
+    DBUG_ASSERT(m_comparator.cmp_type() == ROW_RESULT);
+    return all_items_are_consts(args + 1, arg_count - 1) &&   // Bisection #2
+           ((is_top_level_item() && !negated) ||              // Bisection #3
+            (!list_contains_null() && !args[0]->maybe_null)); // Bisection #4
+
+  }
+  bool agg_all_arg_charsets_for_comparison()
+  {
+    return agg_arg_charsets_for_comparison(cmp_collation, args, arg_count);
+  }
+  void fix_in_vector();
+  bool value_list_convert_const_to_int(THD *thd);
+  bool fix_for_scalar_comparison_using_bisection(THD *thd)
+  {
+    array= m_comparator.type_handler()->make_in_vector(thd, this, arg_count - 1);
+    if (!array)      // OOM
+      return true;
+    fix_in_vector();
+    return false;
+  }
+  bool fix_for_scalar_comparison_using_cmp_items(uint found_types);
+
+  bool fix_for_row_comparison_using_cmp_items(THD *thd);
+  bool fix_for_row_comparison_using_bisection(THD *thd);
+
   void cleanup()
   {
     uint i;
@@ -1705,12 +1780,13 @@ class cmp_item_row :public cmp_item
   cmp_item_row(): comparators(0), n(0) {}
   ~cmp_item_row();
   void store_value(Item *item);
-  inline void alloc_comparators();
+  bool alloc_comparators(THD *thd, uint n);
+  bool prepare_comparators(THD *, Item **args, uint arg_count);
   int cmp(Item *arg);
   int compare(cmp_item *arg);
   cmp_item *make_same();
   void store_value_by_template(THD *thd, cmp_item *tmpl, Item *);
-  friend void Item_func_in::fix_length_and_dec();
+  friend class Item_func_in;
 };
 
 
@@ -1722,7 +1798,7 @@ class in_row :public in_vector
   ~in_row();
   void set(uint pos,Item *item);
   uchar *get_value(Item *item);
-  friend void Item_func_in::fix_length_and_dec();
+  friend class Item_func_in;
   Item_result result_type() { return ROW_RESULT; }
 };
 
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index 1e6ed4d..fc34c7c 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -32,7 +32,6 @@ static Type_handler_time        type_handler_time;
 static Type_handler_time2       type_handler_time2;
 static Type_handler_date        type_handler_date;
 static Type_handler_newdate     type_handler_newdate;
-static Type_handler_datetime    type_handler_datetime;
 static Type_handler_datetime2   type_handler_datetime2;
 static Type_handler_timestamp   type_handler_timestamp;
 static Type_handler_timestamp2  type_handler_timestamp2;
@@ -54,6 +53,7 @@ Type_handler_row         type_handler_row;
 Type_handler_varchar     type_handler_varchar;
 Type_handler_longlong    type_handler_longlong;
 Type_handler_newdecimal  type_handler_newdecimal;
+Type_handler_datetime    type_handler_datetime;
 
 
 /**
@@ -1231,3 +1231,143 @@ longlong Type_handler_decimal_result::
 }
 
 /***************************************************************************/
+
+static int srtcmp_in(CHARSET_INFO *cs, const String *x,const String *y)
+{
+  return cs->coll->strnncollsp(cs,
+                               (uchar *) x->ptr(),x->length(),
+                               (uchar *) y->ptr(),y->length());
+}
+
+in_vector *Type_handler_string_result::make_in_vector(THD *thd,
+                                                      const Item_func_in *func,
+                                                      uint nargs) const
+{
+  return new (thd->mem_root) in_string(thd, nargs, (qsort2_cmp) srtcmp_in,
+                                       func->compare_collation());
+
+}
+
+
+in_vector *Type_handler_int_result::make_in_vector(THD *thd,
+                                                   const Item_func_in *func,
+                                                   uint nargs) const
+{
+  return new (thd->mem_root) in_longlong(thd, nargs);
+}
+
+
+in_vector *Type_handler_real_result::make_in_vector(THD *thd,
+                                                    const Item_func_in *func,
+                                                    uint nargs) const
+{
+  return new (thd->mem_root) in_double(thd, nargs);
+}
+
+
+in_vector *Type_handler_decimal_result::make_in_vector(THD *thd,
+                                                       const Item_func_in *func,
+                                                       uint nargs) const
+{
+  return new (thd->mem_root) in_decimal(thd, nargs);
+}
+
+
+in_vector *Type_handler_time_common::make_in_vector(THD *thd,
+                                                    const Item_func_in *func,
+                                                    uint nargs) const
+{
+  return new (thd->mem_root) in_time(thd, nargs);
+}
+
+
+in_vector *
+Type_handler_temporal_with_date::make_in_vector(THD *thd,
+                                                const Item_func_in *func,
+                                                uint nargs) const
+{
+  return new (thd->mem_root) in_datetime(thd, nargs);
+}
+
+
+in_vector *Type_handler_row::make_in_vector(THD *thd,
+                                            const Item_func_in *func,
+                                            uint nargs) const
+{
+  return new (thd->mem_root) in_row(thd, nargs, 0);
+}
+
+/***************************************************************************/
+
+bool Type_handler_string_result::
+       Item_func_in_fix_comparator_compatible_types(THD *thd,
+                                                    Item_func_in *func) const
+{
+  if (func->agg_all_arg_charsets_for_comparison())
+    return true;
+  if (func->compatible_types_scalar_bisection_possible())
+  {
+    return func->value_list_convert_const_to_int(thd) ||
+           func->fix_for_scalar_comparison_using_bisection(thd);
+  }
+  return
+    func->fix_for_scalar_comparison_using_cmp_items(1U << (uint) STRING_RESULT);
+}
+
+
+bool Type_handler_int_result::
+       Item_func_in_fix_comparator_compatible_types(THD *thd,
+                                                    Item_func_in *func) const
+{
+  /*
+     Does not need to call value_list_convert_const_to_int()
+     as already handled by int handler.
+  */
+  return func->compatible_types_scalar_bisection_possible() ?
+    func->fix_for_scalar_comparison_using_bisection(thd) :
+    func->fix_for_scalar_comparison_using_cmp_items(1U << (uint) INT_RESULT);
+}
+
+
+bool Type_handler_real_result::
+       Item_func_in_fix_comparator_compatible_types(THD *thd,
+                                                    Item_func_in *func) const
+{
+  return func->compatible_types_scalar_bisection_possible() ?
+    (func->value_list_convert_const_to_int(thd) ||
+     func->fix_for_scalar_comparison_using_bisection(thd)) :
+    func->fix_for_scalar_comparison_using_cmp_items(1U << (uint) REAL_RESULT);
+}
+
+
+bool Type_handler_decimal_result::
+       Item_func_in_fix_comparator_compatible_types(THD *thd,
+                                                    Item_func_in *func) const
+{
+  return func->compatible_types_scalar_bisection_possible() ?
+    (func->value_list_convert_const_to_int(thd) ||
+     func->fix_for_scalar_comparison_using_bisection(thd)) :
+    func->fix_for_scalar_comparison_using_cmp_items(1U << (uint) DECIMAL_RESULT);
+}
+
+
+bool Type_handler_temporal_result::
+       Item_func_in_fix_comparator_compatible_types(THD *thd,
+                                                    Item_func_in *func) const
+{
+  return func->compatible_types_scalar_bisection_possible() ?
+    (func->value_list_convert_const_to_int(thd) ||
+     func->fix_for_scalar_comparison_using_bisection(thd)) :
+    func->fix_for_scalar_comparison_using_cmp_items(1U << (uint) TIME_RESULT);
+}
+
+
+bool Type_handler_row::Item_func_in_fix_comparator_compatible_types(THD *thd,
+                                              Item_func_in *func) const
+{
+  return func->compatible_types_row_bisection_possible() ?
+         func->fix_for_row_comparison_using_bisection(thd) :
+         func->fix_for_row_comparison_using_cmp_items(thd);
+}
+
+/***************************************************************************/
diff --git a/sql/sql_type.h b/sql/sql_type.h
index 9086e60..6a694df 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -30,6 +30,8 @@ class Item_sum_hybrid;
 class Item_func_hex;
 class Item_func_hybrid_field_type;
 class Item_func_between;
+class Item_func_in;
+class in_vector;
 class Type_std_attributes;
 class Sort_param;
 class Arg_comparator;
@@ -317,6 +319,13 @@ class Type_handler
 
   virtual longlong
   Item_func_between_val_int(Item_func_between *func) const= 0;
+
+  virtual in_vector *
+  make_in_vector(THD *thd, const Item_func_in *func, uint nargs) const= 0;
+
+  virtual bool
+  Item_func_in_fix_comparator_compatible_types(THD *thd, Item_func_in *)
+                                                               const= 0;
 };
 
 
@@ -414,6 +423,9 @@ class Type_handler_row: public Type_handler
   }
 
   longlong Item_func_between_val_int(Item_func_between *func) const;
+  in_vector *make_in_vector(THD *thd, const Item_func_in *f, uint nargs) const;
+  bool Item_func_in_fix_comparator_compatible_types(THD *thd,
+                                                    Item_func_in *) const;
 };
 
 
@@ -462,6 +474,10 @@ class Type_handler_real_result: public Type_handler_numeric
                                             MYSQL_TIME *,
                                             ulonglong fuzzydate) const;
   longlong Item_func_between_val_int(Item_func_between *func) const;
+  in_vector *make_in_vector(THD *, const Item_func_in *, uint nargs) const;
+  bool Item_func_in_fix_comparator_compatible_types(THD *thd,
+                                                    Item_func_in *) const;
+
 };
 
 
@@ -495,6 +511,9 @@ class Type_handler_decimal_result: public Type_handler_numeric
                                             MYSQL_TIME *,
                                             ulonglong fuzzydate) const;
   longlong Item_func_between_val_int(Item_func_between *func) const;
+  in_vector *make_in_vector(THD *, const Item_func_in *, uint nargs) const;
+  bool Item_func_in_fix_comparator_compatible_types(THD *thd,
+                                                    Item_func_in *) const;
 };
 
 
@@ -528,6 +547,9 @@ class Type_handler_int_result: public Type_handler_numeric
                                             MYSQL_TIME *,
                                             ulonglong fuzzydate) const;
   longlong Item_func_between_val_int(Item_func_between *func) const;
+  in_vector *make_in_vector(THD *, const Item_func_in *, uint nargs) const;
+  bool Item_func_in_fix_comparator_compatible_types(THD *thd,
+                                                    Item_func_in *) const;
 };
 
 
@@ -559,6 +581,8 @@ class Type_handler_temporal_result: public Type_handler
                                             MYSQL_TIME *,
                                             ulonglong fuzzydate) const;
   longlong Item_func_between_val_int(Item_func_between *func) const;
+  bool Item_func_in_fix_comparator_compatible_types(THD *thd,
+                                                    Item_func_in *) const;
 };
 
 
@@ -594,6 +618,9 @@ class Type_handler_string_result: public Type_handler
                                             MYSQL_TIME *,
                                             ulonglong fuzzydate) const;
   longlong Item_func_between_val_int(Item_func_between *func) const;
+  in_vector *make_in_vector(THD *, const Item_func_in *, uint nargs) const;
+  bool Item_func_in_fix_comparator_compatible_types(THD *thd,
+                                                    Item_func_in *) const;
 };
 
 
@@ -714,6 +741,7 @@ class Type_handler_time_common: public Type_handler_temporal_result
   virtual ~Type_handler_time_common() { }
   enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
   int Item_save_in_field(Item *item, Field *field, bool no_conversions) const;
+  in_vector *make_in_vector(THD *, const Item_func_in *, uint nargs) const;
 };
 
 
@@ -741,6 +769,7 @@ class Type_handler_temporal_with_date: public Type_handler_temporal_result
 public:
   virtual ~Type_handler_temporal_with_date() {}
   int Item_save_in_field(Item *item, Field *field, bool no_conversions) const;
+  in_vector *make_in_vector(THD *, const Item_func_in *, uint nargs) const;
 };
 
 
@@ -1014,5 +1043,6 @@ extern Type_handler_null  type_handler_null;
 extern Type_handler_varchar type_handler_varchar;
 extern Type_handler_longlong type_handler_longlong;
 extern Type_handler_newdecimal type_handler_newdecimal;
+extern Type_handler_datetime type_handler_datetime;
 
 #endif /* SQL_TYPE_H_INCLUDED */

References