maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #10174
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
