maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #10190
MDEV-11514, MDEV-11497, MDEV-11554, MDEV-11555 - IN and CASE type aggregation problems
Hello Sanja,
Please review a patch fixing a number of problems in IN and CASE.
This is a per-requisite for further Type_handler related work,
to clean-up the code before moving it to more IN and CASE related
methods in Type_handler.
Thanks!
commit 7170957a90568bfc5ea0451a7aca9b5d11f50331
Author: Alexander Barkov <bar@xxxxxxxxxxx>
Date: Thu Dec 15 11:15:52 2016 +0400
MDEV-11514, MDEV-11497, MDEV-11554, MDEV-11555 - IN and CASE type aggregation problems
This patch fixes a number of data type aggregation problem in IN and CASE:
- MDEV-11497 Wrong result for (int_expr IN (mixture of signed and unsigned expressions))
- MDEV-11514 IN with a mixture of TIME and DATETIME returns a wrong result
- MDEV-11554 Wrong result for CASE on a mixture of signed and unsigned expressions
- MDEV-11555 CASE with a mixture of TIME and DATETIME returns a wrong result
1. The problem reported in MDEV-11514 and MDEV-11555 was in the wrong assumption
that items having the same cmp_type() can reuse the same cmp_item instance.
So Item_func_case and Item_func_in used a static array of cmp_item*,
one element per one XXX_RESULT.
TIME and DATETIME cannot result the same cmp_item, because arguments of
these types are compared very differently. TIME and DATETIME must have
different instances in the cmp_item* array. Reusing the same cmp_item
for TIME and DATETIME leads to unexpected result and unexpected warnings.
Note, after adding more data types soon (e.g. INET6), the problem would
become more serious, as INET6 will most likely have STRING_RESULT, but
it cannot reuse the same cmp_item with VARCHAR/TEXT.
This patch introduces a new class Predicant_to_list_comparator,
which maintaince an array of cmp_items, one element per distinct
Type_handler rather than one element per XXX_RESULT.
2. The problem reported in MDEV-11497 and MDEV-11554 happened because
Item_func_in and Item_func_case did not take into account the fact
that UNSIGNED and SIGNED values must be compared as DECIMAL rather than INT,
because they used item_cmp_type() to aggregate the arguments.
The relevant code now resides in Predicant_to_list_comparator::add_value()
and uses Type_handler_hybrid_field_type::aggregate_for_comparison(),
like Item_func_between does.
diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result
index 9ceb7ef..ba38f26 100644
--- a/mysql-test/r/case.result
+++ b/mysql-test/r/case.result
@@ -379,3 +379,34 @@ DROP TABLE t1;
#
# End of 10.1 test
#
+#
+# Start of 10.3 tests
+#
+#
+# MDEV-11554 Wrong result for CASE on a mixture of signed and unsigned expressions
+#
+CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615);
+SELECT
+CASE -1
+WHEN -9223372036854775808 THEN 'one'
+ WHEN 18446744073709551615 THEN 'two'
+ END AS c;
+c
+NULL
+DROP TABLE t1;
+#
+# MDEV-11555 CASE with a mixture of TIME and DATETIME returns a wrong result
+#
+SELECT
+CASE TIME'10:20:30'
+ WHEN 102030 THEN 'one'
+ WHEN TIME'10:20:31' THEN 'two'
+ END AS good,
+CASE TIME'10:20:30'
+ WHEN 102030 THEN 'one'
+ WHEN TIME'10:20:31' THEN 'two'
+ WHEN TIMESTAMP'2001-01-01 10:20:32' THEN 'three'
+ END AS was_bad_now_good;
+good was_bad_now_good
+one one
diff --git a/mysql-test/r/func_debug.result b/mysql-test/r/func_debug.result
index 662c7cd..b1a3260 100644
--- a/mysql-test/r/func_debug.result
+++ b/mysql-test/r/func_debug.result
@@ -1,270 +1,475 @@
SET SESSION debug_dbug="+d,Item_func_in";
+SET SESSION debug_dbug="+d,Predicant_to_list_comparator";
# Constant predicant, compatible types, bisect
SELECT 1 IN (1,2);
1 IN (1,2)
1
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
SELECT 1 IN (1,2,NULL);
1 IN (1,2,NULL)
1
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
SELECT 1 NOT IN (1,2);
1 NOT IN (1,2)
0
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
SELECT 1 NOT IN (1,2,NULL);
1 NOT IN (1,2,NULL)
0
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
SELECT 1e0 IN (1e0,2e0);
1e0 IN (1e0,2e0)
1
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
SELECT 1e0 IN (1e0,2e0,NULL);
1e0 IN (1e0,2e0,NULL)
1
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
SELECT 1e0 NOT IN (1e0,2e0);
1e0 NOT IN (1e0,2e0)
0
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
SELECT 1e0 NOT IN (1e0,2e0,NULL);
1e0 NOT IN (1e0,2e0,NULL)
0
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
SELECT 'a' IN ('a','b');
'a' IN ('a','b')
1
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
SELECT 'a' IN ('a','b',NULL);
'a' IN ('a','b',NULL)
1
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
SELECT 'a' NOT IN ('a','b');
'a' NOT IN ('a','b')
0
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
SELECT 'a' NOT IN ('a','b',NULL);
'a' NOT IN ('a','b',NULL)
0
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
# Column predicant, compatible types, bisect
+CREATE TABLE t1 (a INT UNSIGNED);
+SELECT a IN (1.0, 1) FROM t1;
+a IN (1.0, 1)
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 types_compatible=yes bisect=yes
+DROP TABLE t1;
CREATE TABLE t1 (a INT);
SELECT a IN (1,2,3) FROM t1;
a IN (1,2,3)
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 types_compatible=yes bisect=yes
+SELECT a IN (1.0, CAST(1 AS UNSIGNED)) FROM t1;
+a IN (1.0, CAST(1 AS UNSIGNED))
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 types_compatible=yes bisect=yes
+SELECT a IN (1.0, CAST(1 AS UNSIGNED),NULL) FROM t1;
+a IN (1.0, CAST(1 AS UNSIGNED),NULL)
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 types_compatible=yes bisect=yes
+SELECT a NOT IN (1.0, CAST(1 AS UNSIGNED)) FROM t1;
+a NOT IN (1.0, CAST(1 AS UNSIGNED))
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 types_compatible=yes bisect=yes
+SELECT a NOT IN (1.0, CAST(1 AS UNSIGNED),NULL) FROM t1;
+a NOT IN (1.0, CAST(1 AS UNSIGNED),NULL)
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
+Warning 1105 [4] arg=5 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
+Warning 1105 [4] arg=5 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
+Warning 1105 [4] arg=5 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
+Warning 1105 [4] arg=5 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
+Warning 1105 [4] arg=5 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
+Warning 1105 [4] arg=5 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
+Warning 1105 [4] arg=5 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
+Warning 1105 [4] arg=5 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
+Warning 1105 [4] arg=5 handler=0
+Warning 1105 [5] arg=6 handler=0
+Warning 1105 [6] arg=7 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
+Warning 1105 [4] arg=5 handler=0
+Warning 1105 [5] arg=6 handler=0
+Warning 1105 [6] arg=7 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
+Warning 1105 [4] arg=5 handler=0
+Warning 1105 [5] arg=6 handler=0
+Warning 1105 [6] arg=7 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
+Warning 1105 [4] arg=5 handler=0
+Warning 1105 [5] arg=6 handler=0
+Warning 1105 [6] arg=7 handler=0
Warning 1105 types_compatible=yes bisect=yes
DROP TABLE t1;
# Constant predicant, compatible types, no bisect
@@ -273,126 +478,210 @@ CREATE TABLE t1 (a INT);
SELECT 1 IN (a,1,2,3) FROM t1;
1 IN (a,1,2,3)
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=no
DROP TABLE t1;
# Constant predicant, incompatible types, no bisect
@@ -400,265 +689,595 @@ SELECT 1 IN (1,2e0);
1 IN (1,2e0)
1
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT 1 IN (1,2e0,NULL);
1 IN (1,2e0,NULL)
1
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT 1 NOT IN (1,2e0);
1 NOT IN (1,2e0)
0
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT 1 NOT IN (1,2e0,NULL);
1 NOT IN (1,2e0,NULL)
0
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT 1.0 IN (1.0,2e0);
1.0 IN (1.0,2e0)
1
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT 'a' IN ('a',2);
'a' IN ('a',2)
1
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT 'a' IN ('a',2,NULL);
'a' IN ('a',2,NULL)
1
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT 'a' NOT IN ('a',2);
'a' NOT IN ('a',2)
0
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT 'a' NOT IN ('a',2,NULL);
'a' NOT IN ('a',2,NULL)
0
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
+Warning 1105 types_compatible=no bisect=no
+SELECT TIME'10:20:30' IN (1,TIME'10:20:30');
+TIME'10:20:30' IN (1,TIME'10:20:30')
+1
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 types_compatible=yes bisect=yes
+SELECT TIME'10:20:30' IN (1,TIME'10:20:30',NULL);
+TIME'10:20:30' IN (1,TIME'10:20:30',NULL)
+1
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 types_compatible=yes bisect=yes
+SELECT TIME'10:20:30' IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32');
+TIME'10:20:30' IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32')
+1
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=2
+Warning 1105 types_compatible=no bisect=no
+SELECT TIME'10:20:30' IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL);
+TIME'10:20:30' IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL)
+1
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=2
+Warning 1105 types_compatible=no bisect=no
+SELECT TIME'10:20:30' IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32');
+TIME'10:20:30' IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32')
+1
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=3
+Warning 1105 types_compatible=no bisect=no
+SELECT TIME'10:20:30' IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL);
+TIME'10:20:30' IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL)
+1
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=3
+Warning 1105 types_compatible=no bisect=no
+SELECT TIME'10:20:30' NOT IN (1,TIME'10:20:30');
+TIME'10:20:30' NOT IN (1,TIME'10:20:30')
+0
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 types_compatible=yes bisect=yes
+SELECT TIME'10:20:30' NOT IN (1,TIME'10:20:30',NULL);
+TIME'10:20:30' NOT IN (1,TIME'10:20:30',NULL)
+0
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 types_compatible=yes bisect=yes
+SELECT TIME'10:20:30' NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32');
+TIME'10:20:30' NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32')
+0
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=2
+Warning 1105 types_compatible=no bisect=no
+SELECT TIME'10:20:30' NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL);
+TIME'10:20:30' NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL)
+0
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=2
+Warning 1105 types_compatible=no bisect=no
+SELECT TIME'10:20:30' NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32');
+TIME'10:20:30' NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32')
+0
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=3
+Warning 1105 types_compatible=no bisect=no
+SELECT TIME'10:20:30' NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL);
+TIME'10:20:30' NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL)
+0
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=3
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT a IN (1,1e0,NULL) FROM t1;
a IN (1,1e0,NULL)
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
+Warning 1105 types_compatible=no bisect=no
+SELECT a IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED)) FROM t1;
+a IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED))
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
+Warning 1105 types_compatible=no bisect=no
+SELECT a IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL) FROM t1;
+a IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL)
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
+Warning 1105 types_compatible=no bisect=no
+SELECT a IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED)) FROM t1;
+a IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED))
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 types_compatible=no bisect=no
+SELECT a IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL) FROM t1;
+a IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL)
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
+Warning 1105 [2] arg=3 handler=0
Warning 1105 types_compatible=no bisect=no
SELECT a NOT IN (1,1e0) FROM t1;
a NOT IN (1,1e0)
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
+Warning 1105 types_compatible=no bisect=no
+SELECT a NOT IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED)) FROM t1;
+a NOT IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED))
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
+Warning 1105 types_compatible=no bisect=no
+SELECT a NOT IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL) FROM t1;
+a NOT IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL)
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
+Warning 1105 types_compatible=no bisect=no
+SELECT a NOT IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED)) FROM t1;
+a NOT IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED))
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 types_compatible=no bisect=no
+SELECT a NOT IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL) FROM t1;
+a NOT IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL)
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
+Warning 1105 [2] arg=3 handler=0
Warning 1105 types_compatible=no bisect=no
SELECT a IN (1,1.0) FROM t1;
a IN (1,1.0)
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT a IN (1,'1') FROM t1;
a IN (1,'1')
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT a IN (1,'1',NULL) FROM t1;
a IN (1,'1',NULL)
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT a NOT IN (1,'1') FROM t1;
a NOT IN (1,'1')
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT a IN (1,1e0,NULL) FROM t1;
a IN (1,1e0,NULL)
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT a NOT IN (1,1e0) FROM t1;
a NOT IN (1,1e0)
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT a IN (1,'1') FROM t1;
a IN (1,'1')
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT a IN (1,'1',NULL) FROM t1;
a IN (1,'1',NULL)
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT a NOT IN (1,'1') FROM t1;
a NOT IN (1,'1')
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
Warning 1105 types_compatible=no bisect=no
SELECT a NOT IN ('a',1) FROM t1;
a NOT IN ('a',1)
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
+Warning 1105 types_compatible=no bisect=no
+DROP TABLE t1;
+CREATE TABLE t1 (a TIME);
+SELECT a IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32') FROM t1;
+a IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32')
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=2
+Warning 1105 types_compatible=no bisect=no
+SELECT a IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL) FROM t1;
+a IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL)
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=2
+Warning 1105 types_compatible=no bisect=no
+SELECT a IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32') FROM t1;
+a IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32')
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=3
+Warning 1105 types_compatible=no bisect=no
+SELECT a IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL) FROM t1;
+a IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL)
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=3
+Warning 1105 types_compatible=no bisect=no
+SELECT a NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32') FROM t1;
+a NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32')
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=2
+Warning 1105 types_compatible=no bisect=no
+SELECT a NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL) FROM t1;
+a NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL)
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=2
+Warning 1105 types_compatible=no bisect=no
+SELECT a NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32') FROM t1;
+a NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32')
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=3
+Warning 1105 types_compatible=no bisect=no
+SELECT a NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL) FROM t1;
+a NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL)
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=0
+Warning 1105 [3] arg=4 handler=3
Warning 1105 types_compatible=no bisect=no
DROP TABLE t1;
# Not top level, negated: cond3 is false
@@ -666,40 +1285,56 @@ 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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=no
DROP TABLE t1;
CREATE TABLE t1 (a INT NOT NULL);
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
DROP TABLE t1;
# Not top level, not negated: cond3 is false
@@ -707,40 +1342,56 @@ 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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
DROP TABLE t1;
# Top level, negated: cond3 is false
@@ -748,40 +1399,56 @@ CREATE TABLE t1 (a INT);
SELECT 1 FROM t1 WHERE ROW(a,a) NOT IN ((1,1),(2,NULL));
1
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
DROP TABLE t1;
# Top level, not negated: cond3 is true
@@ -789,40 +1456,178 @@ CREATE TABLE t1 (a INT);
SELECT 1 FROM t1 WHERE ROW(a,a) IN ((1,1),(2,NULL));
1
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
SELECT 1 FROM t1 WHERE ROW(a,a) IN ((1,1),(2,2));
1
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
SELECT 1 FROM t1 WHERE ROW(a,a) IN ((1,1),(2,2));
1
Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
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 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
Warning 1105 types_compatible=yes bisect=yes
DROP TABLE t1;
+#
+# MDEV-11514 IN with a mixture of TIME and DATETIME returns a wrong result
+#
+SELECT TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32');
+TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32')
+1
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=3 handler=2
+Warning 1105 types_compatible=no bisect=no
+CREATE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('A'),('B'),('A');
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (b,'A');
+a b
+A NULL
+B NULL
+Warnings:
+Warning 1105 [0] arg=2 handler=0
+Warning 1105 types_compatible=yes bisect=yes
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN ('A',b);
+a b
+A NULL
+B NULL
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 types_compatible=yes bisect=yes
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (b,a);
+a b
+A NULL
+Warnings:
+Warning 1105 [0] arg=2 handler=0
+Warning 1105 types_compatible=yes bisect=no
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (a,b);
+a b
+A NULL
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 types_compatible=yes bisect=no
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (b,'A',10);
+a b
+A NULL
+B NULL
+Warnings:
+Warning 1105 [0] arg=2 handler=0
+Warning 1105 [1] arg=3 handler=1
+Warning 1105 types_compatible=no bisect=no
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN ('A',b,10);
+a b
+A NULL
+B NULL
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=3 handler=1
+Warning 1105 types_compatible=no bisect=no
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (b,a,10);
+a b
+A NULL
+Warnings:
+Warning 1105 [0] arg=2 handler=0
+Warning 1105 [1] arg=3 handler=1
+Warning 1105 types_compatible=no bisect=no
+Warning 1292 Truncated incorrect DOUBLE value: 'A'
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (a,b,10);
+a b
+A NULL
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=3 handler=1
+Warning 1105 types_compatible=no bisect=no
+Warning 1292 Truncated incorrect DOUBLE value: 'A'
+DROP TABLE t1;
+#
+# MDEV-11497 Wrong result for (int_expr IN (mixture of signed and unsigned expressions))
+#
+CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615);
+SELECT * FROM t1 WHERE -1 IN (a,b);
+a b
+Warnings:
+Warning 1105 [0] arg=1 handler=0
+Warning 1105 [1] arg=2 handler=1
+Warning 1105 types_compatible=no bisect=no
+DROP TABLE t1;
+#
+# MDEV-11554 Wrong result for CASE on a mixture of signed and unsigned expressions
+#
+CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615);
+SELECT
+CASE -1
+WHEN -9223372036854775808 THEN 'one'
+ WHEN 18446744073709551615 THEN 'two'
+ END AS c;
+c
+NULL
+Warnings:
+Warning 1105 [0] arg=0 handler=0
+Warning 1105 [1] arg=2 handler=1
+DROP TABLE t1;
+#
+# MDEV-11555 CASE with a mixture of TIME and DATETIME returns a wrong result
+#
+SELECT
+CASE TIME'10:20:30'
+ WHEN 102030 THEN 'one'
+ WHEN TIME'10:20:31' THEN 'two'
+ END AS good,
+CASE TIME'10:20:30'
+ WHEN 102030 THEN 'one'
+ WHEN TIME'10:20:31' THEN 'two'
+ WHEN TIMESTAMP'2001-01-01 10:20:32' THEN 'three'
+ END AS was_bad_now_good;
+good was_bad_now_good
+one one
+Warnings:
+Warning 1105 [0] arg=0 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [0] arg=0 handler=0
+Warning 1105 [1] arg=2 handler=0
+Warning 1105 [2] arg=4 handler=2
+SET SESSION debug_dbug="-d,Predicant_to_list_comparator";
SET SESSION debug_dbug="-d,Item_func_in";
diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index 1855fa7..e0aa8e8 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -855,3 +855,20 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`a` in (1,2,'3')))
DROP TABLE t1;
+#
+# Start of 10.3 tests
+#
+#
+# MDEV-11514 IN with a mixture of TIME and DATETIME returns a wrong result
+#
+SELECT TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32');
+TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32')
+1
+#
+# MDEV-11497 Wrong result for (int_expr IN (mixture of signed and unsigned expressions))
+#
+CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615);
+SELECT * FROM t1 WHERE -1 IN (a,b);
+a b
+DROP TABLE t1;
diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result
index e4525f3..f0c392a 100644
--- a/mysql-test/r/type_time.result
+++ b/mysql-test/r/type_time.result
@@ -1246,3 +1246,25 @@ DROP TABLE t1,t2;
#
# End of 10.2 tests
#
+#
+# Start of 10.3 tests
+#
+#
+# MDEV-11514 IN with a mixture of TIME and DATETIME returns a wrong result
+#
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00');
+SELECT TIME'10:20:30' IN (102030,TIME'10:20:31');
+TIME'10:20:30' IN (102030,TIME'10:20:31')
+1
+SELECT TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32');
+TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32')
+1
+CREATE TABLE t1 (a TIME);
+INSERT INTO t1 VALUES ('10:20:30'),('10:20:31'),('10:20:32');
+SELECT a FROM t1 WHERE a IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32') ORDER BY a;
+a
+10:20:30
+10:20:31
+10:20:32
+DROP TABLE t1;
+SET timestamp=DEFAULT;
diff --git a/mysql-test/t/case.test b/mysql-test/t/case.test
index 7cc74c5..9ff2df4 100644
--- a/mysql-test/t/case.test
+++ b/mysql-test/t/case.test
@@ -275,3 +275,34 @@ DROP TABLE t1;
--echo #
--echo # End of 10.1 test
--echo #
+
+--echo #
+--echo # Start of 10.3 tests
+--echo #
+
+--echo #
+--echo # MDEV-11554 Wrong result for CASE on a mixture of signed and unsigned expressions
+--echo #
+
+CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615);
+SELECT
+ CASE -1
+ WHEN -9223372036854775808 THEN 'one'
+ WHEN 18446744073709551615 THEN 'two'
+ END AS c;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-11555 CASE with a mixture of TIME and DATETIME returns a wrong result
+--echo #
+SELECT
+ CASE TIME'10:20:30'
+ WHEN 102030 THEN 'one'
+ WHEN TIME'10:20:31' THEN 'two'
+ END AS good,
+ CASE TIME'10:20:30'
+ WHEN 102030 THEN 'one'
+ WHEN TIME'10:20:31' THEN 'two'
+ WHEN TIMESTAMP'2001-01-01 10:20:32' THEN 'three'
+ END AS was_bad_now_good;
diff --git a/mysql-test/t/func_debug.test b/mysql-test/t/func_debug.test
index 10eec49..9178902 100644
--- a/mysql-test/t/func_debug.test
+++ b/mysql-test/t/func_debug.test
@@ -1,6 +1,7 @@
--source include/have_debug.inc
SET SESSION debug_dbug="+d,Item_func_in";
+SET SESSION debug_dbug="+d,Predicant_to_list_comparator";
--echo # Constant predicant, compatible types, bisect
SELECT 1 IN (1,2);
@@ -41,11 +42,22 @@ SELECT DATE'2001-01-01' NOT IN ('2001-01-01','2001-02-02',NULL);
--echo # Column predicant, compatible types, bisect
+# Special case: mixture of unsigned, signed and decimal
+CREATE TABLE t1 (a INT UNSIGNED);
+# a=1.0 is done as decimal, because decimal bits int
+# a=1 is done as decimal as well, because of different unsigned flag
+SELECT a IN (1.0, 1) FROM t1;
+DROP TABLE t1;
+
CREATE TABLE t1 (a INT);
SELECT a IN (1,2,3) FROM t1;
SELECT a IN (1,2,3,NULL) FROM t1;
+SELECT a IN (1.0, CAST(1 AS UNSIGNED)) FROM t1;
+SELECT a IN (1.0, CAST(1 AS UNSIGNED),NULL) FROM t1;
SELECT a NOT IN (1,2,3) FROM t1;
SELECT a NOT IN (1,2,3,NULL) FROM t1;
+SELECT a NOT IN (1.0, CAST(1 AS UNSIGNED)) FROM t1;
+SELECT a NOT IN (1.0, CAST(1 AS UNSIGNED),NULL) FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (a DOUBLE);
@@ -163,12 +175,33 @@ SELECT 'a' IN ('a',2,NULL);
SELECT 'a' NOT IN ('a',2);
SELECT 'a' NOT IN ('a',2,NULL);
+SELECT TIME'10:20:30' IN (1,TIME'10:20:30');
+SELECT TIME'10:20:30' IN (1,TIME'10:20:30',NULL);
+SELECT TIME'10:20:30' IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32');
+SELECT TIME'10:20:30' IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL);
+SELECT TIME'10:20:30' IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32');
+SELECT TIME'10:20:30' IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL);
+SELECT TIME'10:20:30' NOT IN (1,TIME'10:20:30');
+SELECT TIME'10:20:30' NOT IN (1,TIME'10:20:30',NULL);
+SELECT TIME'10:20:30' NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32');
+SELECT TIME'10:20:30' NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL);
+SELECT TIME'10:20:30' NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32');
+SELECT TIME'10:20:30' NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',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 IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED)) FROM t1;
+SELECT a IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL) FROM t1;
+SELECT a IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED)) FROM t1;
+SELECT a IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL) FROM t1;
SELECT a NOT IN (1,1e0) FROM t1;
SELECT a NOT IN (1,1e0,NULL) FROM t1;
+SELECT a NOT IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED)) FROM t1;
+SELECT a NOT IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL) FROM t1;
+SELECT a NOT IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED)) FROM t1;
+SELECT a NOT IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL) FROM t1;
SELECT a IN (1,1.0) FROM t1;
SELECT a IN (1,1.0,NULL) FROM t1;
@@ -227,6 +260,17 @@ SELECT a NOT IN ('a',1) FROM t1;
SELECT a NOT IN ('a',TIME'10:20:30') FROM t1;
DROP TABLE t1;
+CREATE TABLE t1 (a TIME);
+SELECT a IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32') FROM t1;
+SELECT a IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL) FROM t1;
+SELECT a IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32') FROM t1;
+SELECT a IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL) FROM t1;
+SELECT a NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32') FROM t1;
+SELECT a NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL) FROM t1;
+SELECT a NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32') FROM t1;
+SELECT a NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL) FROM t1;
+DROP TABLE t1;
+
#
# ROW tests
#
@@ -366,5 +410,62 @@ 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;
+--echo #
+--echo # MDEV-11514 IN with a mixture of TIME and DATETIME returns a wrong result
+--echo #
+SELECT TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32');
+
+CREATE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('A'),('B'),('A');
+# Compatible types
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (b,'A');
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN ('A',b);
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (b,a);
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (a,b);
+# Incompatible types
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (b,'A',10);
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN ('A',b,10);
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (b,a,10);
+SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (a,b,10);
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-11497 Wrong result for (int_expr IN (mixture of signed and unsigned expressions))
+--echo #
+CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615);
+SELECT * FROM t1 WHERE -1 IN (a,b);
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-11554 Wrong result for CASE on a mixture of signed and unsigned expressions
+--echo #
+
+CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615);
+SELECT
+ CASE -1
+ WHEN -9223372036854775808 THEN 'one'
+ WHEN 18446744073709551615 THEN 'two'
+ END AS c;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-11555 CASE with a mixture of TIME and DATETIME returns a wrong result
+--echo #
+SELECT
+ CASE TIME'10:20:30'
+ WHEN 102030 THEN 'one'
+ WHEN TIME'10:20:31' THEN 'two'
+ END AS good,
+ CASE TIME'10:20:30'
+ WHEN 102030 THEN 'one'
+ WHEN TIME'10:20:31' THEN 'two'
+ WHEN TIMESTAMP'2001-01-01 10:20:32' THEN 'three'
+ END AS was_bad_now_good;
+
+
+SET SESSION debug_dbug="-d,Predicant_to_list_comparator";
SET SESSION debug_dbug="-d,Item_func_in";
diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test
index 9e848aa..a957c51 100644
--- a/mysql-test/t/func_in.test
+++ b/mysql-test/t/func_in.test
@@ -641,3 +641,21 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND 1 IN (1,a,'3');
--echo # Not Ok to propagate equalities into the left IN argument in case of multiple comparison types
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a IN (1,2,'3');
DROP TABLE t1;
+
+--echo #
+--echo # Start of 10.3 tests
+--echo #
+
+--echo #
+--echo # MDEV-11514 IN with a mixture of TIME and DATETIME returns a wrong result
+--echo #
+
+SELECT TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32');
+
+--echo #
+--echo # MDEV-11497 Wrong result for (int_expr IN (mixture of signed and unsigned expressions))
+--echo #
+CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615);
+SELECT * FROM t1 WHERE -1 IN (a,b);
+DROP TABLE t1;
diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test
index c4b93d6..af4e328 100644
--- a/mysql-test/t/type_time.test
+++ b/mysql-test/t/type_time.test
@@ -748,3 +748,19 @@ DROP TABLE t1,t2;
--echo #
--echo # End of 10.2 tests
--echo #
+
+--echo #
+--echo # Start of 10.3 tests
+--echo #
+
+--echo #
+--echo # MDEV-11514 IN with a mixture of TIME and DATETIME returns a wrong result
+--echo #
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00');
+SELECT TIME'10:20:30' IN (102030,TIME'10:20:31');
+SELECT TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32');
+CREATE TABLE t1 (a TIME);
+INSERT INTO t1 VALUES ('10:20:30'),('10:20:31'),('10:20:32');
+SELECT a FROM t1 WHERE a IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32') ORDER BY a;
+DROP TABLE t1;
+SET timestamp=DEFAULT;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 61c5f5b..1a450ab 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -122,11 +122,18 @@ bool Type_handler_hybrid_field_type::aggregate_for_comparison(Item **items,
uint nitems)
{
uint unsigned_count= items[0]->unsigned_flag;
- set_handler(items[0]->type_handler());
+ /*
+ Convert sub-type to super-type (e.g. DATE to DATETIME, INT to BIGINT, etc).
+ Otherwise Predicant_to_list_comparator will treat sub-types of the same
+ super-type as different data types and won't be able to use bisection in
+ many cases.
+ */
+ set_handler(items[0]->type_handler()->type_handler_for_comparison());
for (uint i= 1 ; i < nitems ; i++)
{
unsigned_count+= items[i]->unsigned_flag;
- aggregate_for_comparison(items[i]->type_handler());
+ aggregate_for_comparison(items[i]->type_handler()->
+ type_handler_for_comparison());
/*
When aggregating types of two row expressions we have to check
that they have the same cardinality and that each component
@@ -2878,8 +2885,10 @@ Item_func_nullif::is_null()
Item_func_case::Item_func_case(THD *thd, List<Item> &list,
Item *first_expr_arg, Item *else_expr_arg):
- Item_func_hybrid_field_type(thd), first_expr_num(-1), else_expr_num(-1),
- left_cmp_type(INT_RESULT), case_item(0), m_found_types(0)
+ Item_func_hybrid_field_type(thd),
+ Predicant_to_list_comparator(thd, list.elements/*QQ*/),
+ first_expr_num(-1), else_expr_num(-1),
+ left_cmp_type(INT_RESULT), m_found_types(0)
{
ncases= list.elements;
if (first_expr_arg)
@@ -2893,7 +2902,6 @@ Item_func_case::Item_func_case(THD *thd, List<Item> &list,
list.push_back(else_expr_arg, thd->mem_root);
}
set_arguments(thd, list);
- bzero(&cmp_items, sizeof(cmp_items));
}
/**
@@ -2919,8 +2927,6 @@ Item_func_case::Item_func_case(THD *thd, List<Item> &list,
Item *Item_func_case::find_item(String *str)
{
- uint value_added_map= 0;
-
if (first_expr_num == -1)
{
for (uint i=0 ; i < ncases ; i+=2)
@@ -2934,23 +2940,9 @@ Item *Item_func_case::find_item(String *str)
else
{
/* Compare every WHEN argument with it and return the first match */
- for (uint i=0 ; i < ncases ; i+=2)
- {
- if (args[i]->real_item()->type() == NULL_ITEM)
- continue;
- cmp_type= item_cmp_type(left_cmp_type, args[i]);
- DBUG_ASSERT(cmp_type != ROW_RESULT);
- DBUG_ASSERT(cmp_items[(uint)cmp_type]);
- if (!(value_added_map & (1U << (uint)cmp_type)))
- {
- cmp_items[(uint)cmp_type]->store_value(args[first_expr_num]);
- if ((null_value=args[first_expr_num]->null_value))
- return else_expr_num != -1 ? args[else_expr_num] : 0;
- value_added_map|= 1U << (uint)cmp_type;
- }
- if (cmp_items[(uint)cmp_type]->cmp(args[i]) == FALSE)
- return args[i + 1];
- }
+ uint idx;
+ if (!Predicant_to_list_comparator::cmp(this, &idx, NULL))
+ return args[idx + 1];
}
// No, WHEN clauses all missed, return ELSE expression
return else_expr_num != -1 ? args[else_expr_num] : 0;
@@ -3088,6 +3080,25 @@ static void change_item_tree_if_needed(THD *thd,
}
+bool Item_func_case::prepare_predicant_and_values(THD *thd, uint *found_types)
+{
+ bool have_null= false;
+ uint type_cnt;
+ Type_handler_hybrid_field_type tmp;
+ add_predicant(this, (uint) first_expr_num);
+ for (uint i= 0 ; i < ncases / 2; i++)
+ {
+ if (add_value_skip_null(this, i * 2, &have_null))
+ return true;
+ }
+ all_values_added(&tmp, &type_cnt, &m_found_types);
+#ifndef DBUG_OFF
+ Predicant_to_list_comparator::debug_print(thd);
+#endif
+ return false;
+}
+
+
void Item_func_case::fix_length_and_dec()
{
Item **agg= arg_buffer;
@@ -3136,7 +3147,17 @@ void Item_func_case::fix_length_and_dec()
*/
if (first_expr_num != -1)
{
- uint i;
+ if (prepare_predicant_and_values(thd, &m_found_types))
+ {
+ /*
+ If Predicant_to_list_comparator() fails to prepare components,
+ it must put an error into the diagnostics area. This is needed
+ to make fix_fields() catches such errors.
+ */
+ DBUG_ASSERT(thd->is_error());
+ return;
+ }
+
agg[0]= args[first_expr_num];
left_cmp_type= agg[0]->cmp_type();
@@ -3152,10 +3173,6 @@ void Item_func_case::fix_length_and_dec()
if (!(m_found_types= collect_cmp_types(agg, nagg)))
return;
- Item *date_arg= 0;
- if (m_found_types & (1U << TIME_RESULT))
- date_arg= find_date_time_item(args, arg_count, 0);
-
if (m_found_types & (1U << STRING_RESULT))
{
/*
@@ -3193,20 +3210,11 @@ void Item_func_case::fix_length_and_dec()
for (nagg= 0; nagg < ncases / 2; nagg++)
change_item_tree_if_needed(thd, &args[nagg * 2], agg[nagg + 1]);
- }
-
- for (i= 0; i <= (uint)TIME_RESULT; i++)
- {
- if (m_found_types & (1U << i) && !cmp_items[i])
- {
- DBUG_ASSERT((Item_result)i != ROW_RESULT);
- if (!(cmp_items[i]=
- cmp_item::get_comparator((Item_result)i, date_arg,
- cmp_collation.collation)))
- return;
- }
}
+
+ if (make_unique_cmp_items(thd, cmp_collation.collation))
+ return;
}
}
@@ -3337,20 +3345,6 @@ void Item_func_case::print(String *str, enum_query_type query_type)
}
-void Item_func_case::cleanup()
-{
- uint i;
- DBUG_ENTER("Item_func_case::cleanup");
- Item_func::cleanup();
- for (i= 0; i <= (uint)TIME_RESULT; i++)
- {
- delete cmp_items[i];
- cmp_items[i]= 0;
- }
- DBUG_VOID_RETURN;
-}
-
-
/**
Coalesce - return first not NULL argument.
*/
@@ -3802,6 +3796,93 @@ Item *in_decimal::create_item(THD *thd)
}
+bool Predicant_to_list_comparator::alloc_comparators(THD *thd, uint nargs)
+{
+ size_t nbytes= sizeof(Predicant_to_value_comparator) * nargs;
+ if (!(m_comparators= (Predicant_to_value_comparator *) thd->alloc(nbytes)))
+ return true;
+ memset(m_comparators, 0, nbytes);
+ return false;
+}
+
+
+bool Predicant_to_list_comparator::add_value(Item_args *args,
+ uint value_index)
+{
+ DBUG_ASSERT(m_predicant_index < args->argument_count());
+ DBUG_ASSERT(value_index < args->argument_count());
+ Type_handler_hybrid_field_type tmp;
+ Item *tmpargs[2];
+ tmpargs[0]= args->arguments()[m_predicant_index];
+ tmpargs[1]= args->arguments()[value_index];
+ if (tmp.aggregate_for_comparison(tmpargs, 2))
+ return true;
+ m_comparators[m_comparator_count].m_handler= tmp.type_handler();
+ m_comparators[m_comparator_count].m_arg_index= value_index;
+ m_comparator_count++;
+ return false;
+}
+
+
+bool Predicant_to_list_comparator::add_value_skip_null(Item_args *args,
+ uint value_index,
+ bool *nulls_found)
+{
+ /*
+ Skip explicit NULL constant items.
+ Using real_item() to correctly detect references to explicit NULLs
+ in HAVING clause, e.g. in this example "b" is skipped:
+ SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (b,'A');
+ */
+ if (args->arguments()[value_index]->real_item()->type() == Item::NULL_ITEM)
+ {
+ *nulls_found= true;
+ return false;
+ }
+ return add_value(args, value_index);
+}
+
+
+void Predicant_to_list_comparator::
+ detect_unique_handlers(Type_handler_hybrid_field_type *compatible,
+ uint *unique_count,
+ uint *found_types)
+{
+ *unique_count= 0;
+ *found_types= 0;
+ for (uint i= 0; i < m_comparator_count; i++)
+ {
+ uint idx;
+ if (find_handler(&idx, m_comparators[i].m_handler, i))
+ {
+ m_comparators[i].m_handler_index= i; // New unique handler
+ (*unique_count)++;
+ (*found_types)|= 1U << m_comparators[i].m_handler->cmp_type();
+ compatible->set_handler(m_comparators[i].m_handler);
+ }
+ else
+ {
+ m_comparators[i].m_handler_index= idx; // Non-unique handler
+ }
+ }
+}
+
+
+bool Predicant_to_list_comparator::make_unique_cmp_items(THD *thd,
+ CHARSET_INFO *cs)
+{
+ for (uint i= 0; i < m_comparator_count; i++)
+ {
+ if (m_comparators[i].m_handler && // Skip implicit NULLs
+ m_comparators[i].m_handler_index == i && // Skip non-unuque
+ !(m_comparators[i].m_cmp_item=
+ m_comparators[i].m_handler->make_cmp_item(thd, cs)))
+ return true;
+ }
+ return false;
+}
+
+
cmp_item* cmp_item::get_comparator(Item_result type, Item *warn_item,
CHARSET_INFO *cs)
{
@@ -3818,7 +3899,9 @@ cmp_item* cmp_item::get_comparator(Item_result type, Item *warn_item,
return new cmp_item_decimal;
case TIME_RESULT:
DBUG_ASSERT(warn_item);
- return new cmp_item_datetime(warn_item);
+ return warn_item->field_type() == MYSQL_TYPE_TIME ?
+ (cmp_item *) new cmp_item_time() :
+ (cmp_item *) new cmp_item_datetime();
}
return 0; // to satisfy compiler :)
}
@@ -4010,12 +4093,11 @@ cmp_item* cmp_item_decimal::make_same()
}
-void cmp_item_datetime::store_value(Item *item)
+void cmp_item_temporal::store_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);
value= get_datetime_value(thd, &tmp_item, &lval_cache, f_type, &is_null);
m_null_value= item->null_value;
}
@@ -4023,21 +4105,34 @@ void cmp_item_datetime::store_value(Item *item)
int cmp_item_datetime::cmp(Item *arg)
{
- const bool rc= value != arg->val_temporal_packed(warn_item);
+ const bool rc= value != arg->val_datetime_packed();
return (m_null_value || arg->null_value) ? UNKNOWN : rc;
}
-int cmp_item_datetime::compare(cmp_item *ci)
+int cmp_item_time::cmp(Item *arg)
{
- cmp_item_datetime *l_cmp= (cmp_item_datetime *)ci;
+ const bool rc= value != arg->val_time_packed();
+ return (m_null_value || arg->null_value) ? UNKNOWN : rc;
+}
+
+
+int cmp_item_temporal::compare(cmp_item *ci)
+{
+ cmp_item_temporal *l_cmp= (cmp_item_temporal *)ci;
return (value < l_cmp->value) ? -1 : ((value == l_cmp->value) ? 0 : 1);
}
cmp_item *cmp_item_datetime::make_same()
{
- return new cmp_item_datetime(warn_item);
+ return new cmp_item_datetime();
+}
+
+
+cmp_item *cmp_item_time::make_same()
+{
+ return new cmp_item_time();
}
@@ -4129,43 +4224,49 @@ void Item_func_in::fix_after_pullout(st_select_lex *new_parent, Item **ref)
}
+bool Item_func_in::prepare_predicant_and_values(THD *thd, uint *found_types)
+{
+ uint type_cnt;
+ have_null= false;
+
+ add_predicant(this, 0);
+ for (uint i= 1 ; i < arg_count; i++)
+ {
+ if (add_value_skip_null(this, i, &have_null))
+ return true;
+ }
+ all_values_added(&m_comparator, &type_cnt, found_types);
+ arg_types_compatible= type_cnt < 2;
+
+#ifndef DBUG_OFF
+ Predicant_to_list_comparator::debug_print(thd);
+#endif
+ return false;
+}
+
+
void Item_func_in::fix_length_and_dec()
{
THD *thd= current_thd;
- uint found_types= 0;
- uint type_cnt= 0, i;
- m_comparator.set_handler(&type_handler_varchar);
- left_cmp_type= args[0]->cmp_type();
+ uint found_types;
+ m_comparator.set_handler(type_handler_varchar.type_handler_for_comparison());
max_length= 1;
- if (!(found_types= collect_cmp_types(args, arg_count, true)))
- return;
-
- for (i= 0; i <= (uint)TIME_RESULT; i++)
+
+ if (prepare_predicant_and_values(thd, &found_types))
{
- if (found_types & (1U << i))
- {
- (type_cnt)++;
- 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);
- }
+ DBUG_ASSERT(thd->is_error()); // Must set error
+ return;
}
- if (type_cnt == 1) // Bisection condition #1
+ if (arg_types_compatible) // Bisection condition #1
{
- arg_types_compatible= true;
m_comparator.type_handler()->
Item_func_in_fix_comparator_compatible_types(thd, this);
}
else
{
DBUG_ASSERT(m_comparator.cmp_type() != ROW_RESULT);
- fix_for_scalar_comparison_using_cmp_items(found_types);
+ fix_for_scalar_comparison_using_cmp_items(thd, found_types);
}
DBUG_EXECUTE_IF("Item_func_in",
@@ -4178,6 +4279,11 @@ void Item_func_in::fix_length_and_dec()
/**
Populate Item_func_in::array with constant not-NULL arguments and sort them.
+
+ Sets "have_null" to true if some of the values appeared to be NULL.
+ Note, explicit NULLs were found during prepare_predicant_and_values().
+ So "have_null" can already be true before the fix_in_vector() call.
+ Here we additionally catch implicit NULLs.
*/
void Item_func_in::fix_in_vector()
{
@@ -4250,7 +4356,12 @@ bool cmp_item_row::prepare_comparators(THD *thd, Item **args, uint arg_count)
Item *date_arg= find_date_time_item(args, arg_count, col);
if (date_arg)
{
- if (!(comparators[col]= new (thd->mem_root) cmp_item_datetime(date_arg)))
+ // TODO: do like the scalar comparators do
+ const Type_handler *h= date_arg->type_handler();
+ comparators[col]= h->field_type() == MYSQL_TYPE_TIME ?
+ (cmp_item *) new (thd->mem_root) cmp_item_time() :
+ (cmp_item *) new (thd->mem_root) cmp_item_datetime();
+ if (!comparators[col])
return true;
}
}
@@ -4289,24 +4400,14 @@ bool Item_func_in::fix_for_row_comparison_using_bisection(THD *thd)
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)
+bool Item_func_in::fix_for_scalar_comparison_using_cmp_items(THD *thd,
+ 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 << i) && !cmp_items[i])
- {
- if (!cmp_items[i] && !(cmp_items[i]=
- cmp_item::get_comparator((Item_result)i, date_arg,
- cmp_collation.collation)))
- return true;
- }
- }
+ if (make_unique_cmp_items(thd, cmp_collation.collation))
+ return true;
return false;
}
@@ -4316,14 +4417,13 @@ bool Item_func_in::fix_for_scalar_comparison_using_cmp_items(uint found_types)
*/
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))
+ if (make_unique_cmp_items(thd, cmp_collation.collation))
return true;
- cmp_items[ROW_RESULT]= cmp_row;
- return false;
+ DBUG_ASSERT(get_comparator_type_handler(0) == &type_handler_row);
+ DBUG_ASSERT(get_comparator_cmp_item(0));
+ cmp_item_row *cmp_row= (cmp_item_row*) get_comparator_cmp_item(0);
+ return cmp_row->alloc_comparators(thd, args[0]->cols()) ||
+ cmp_row->prepare_comparators(thd, args, arg_count);
}
@@ -4366,9 +4466,7 @@ void Item_func_in::print(String *str, enum_query_type query_type)
longlong Item_func_in::val_int()
{
- cmp_item *in_item;
DBUG_ASSERT(fixed == 1);
- uint value_added_map= 0;
if (array)
{
bool tmp=array->find(args[0]);
@@ -4386,29 +4484,13 @@ longlong Item_func_in::val_int()
if ((null_value= args[0]->real_item()->type() == NULL_ITEM))
return 0;
- have_null= 0;
- for (uint i= 1 ; i < arg_count ; i++)
+ null_value= have_null;
+ uint idx;
+ if (!Predicant_to_list_comparator::cmp(this, &idx, &null_value))
{
- if (args[i]->real_item()->type() == NULL_ITEM)
- {
- have_null= TRUE;
- continue;
- }
- Item_result cmp_type= item_cmp_type(left_cmp_type, args[i]);
- in_item= cmp_items[(uint)cmp_type];
- DBUG_ASSERT(in_item);
- if (!(value_added_map & (1U << (uint)cmp_type)))
- {
- in_item->store_value(args[0]);
- value_added_map|= 1U << (uint)cmp_type;
- }
- const int rc= in_item->cmp(args[i]);
- if (rc == FALSE)
- return (longlong) (!negated);
- have_null|= (rc == UNKNOWN);
+ null_value= false;
+ return (longlong) (!negated);
}
-
- null_value= have_null;
return (longlong) (!null_value && negated);
}
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 6a59754..2b3ff46 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1458,21 +1458,48 @@ class cmp_item_int : public cmp_item_scalar
If the left item is a constant one then its value is cached in the
lval_cache variable.
*/
-class cmp_item_datetime : public cmp_item_scalar
+class cmp_item_temporal: public cmp_item_scalar
{
+protected:
longlong value;
+ void store_value_internal(Item *item, enum_field_types type);
public:
THD *thd;
- /* Item used for issuing warnings. */
- Item *warn_item;
/* Cache for the left item. */
Item *lval_cache;
- cmp_item_datetime(Item *warn_item_arg)
- :thd(current_thd), warn_item(warn_item_arg), lval_cache(0) {}
- void store_value(Item *item);
- int cmp(Item *arg);
+ cmp_item_temporal()
+ :thd(current_thd), lval_cache(0) {}
int compare(cmp_item *ci);
+};
+
+
+class cmp_item_datetime: public cmp_item_temporal
+{
+public:
+ cmp_item_datetime()
+ :cmp_item_temporal()
+ { }
+ void store_value(Item *item)
+ {
+ store_value_internal(item, MYSQL_TYPE_DATETIME);
+ }
+ int cmp(Item *arg);
+ cmp_item *make_same();
+};
+
+
+class cmp_item_time: public cmp_item_temporal
+{
+public:
+ cmp_item_time()
+ :cmp_item_temporal()
+ { }
+ void store_value(Item *item)
+ {
+ store_value_internal(item, MYSQL_TYPE_TIME);
+ }
+ int cmp(Item *arg);
cmp_item *make_same();
};
@@ -1547,6 +1574,326 @@ class cmp_item_sort_string_in_static :public cmp_item_string
};
+/**
+ A helper class to handle situations when some item "pred" (the predicant)
+ is consequently compared to a list of other items value0..valueN (the values).
+ Currently used to handle:
+ - <in predicate>
+ pred IN (value0, value1, value2)
+ - <simple case>
+ CASE pred WHEN value0 .. WHEN value1 .. WHEN value2 .. END
+
+ Every pair {pred,valueN} can be compared by its own Type_handler.
+ Some pairs can use the same Type_handler.
+ In cases when all pairs use exactly the same Type_handler,
+ we say "all types are compatible".
+
+ For example, for an expression
+ 1 IN (1, 1e0, 1.0, 2)
+ - pred is 1
+ - value0 is 1
+ - value1 is 1e0
+ - value2 is 1.1
+ - value3 is 2
+
+ Pairs (pred,valueN) are compared as follows:
+ N expr1 Type
+ - ----- ----
+ 0 1 INT
+ 1 1e0 DOUBLE
+ 2 1.0 DECIMAL
+ 3 2 INT
+
+ Types are not compatible in this example.
+
+ During add_value() calls, each pair {pred,valueN} is analysed:
+ - If valueN is an explicit NULL, it can be ignored in the caller asks to do so
+ - If valueN is not an explicit NULL (or if the caller didn't ask to skip
+ NULLs), then the value add an element in the array m_comparators[].
+
+ Every element m_comparators[] stores the following information:
+ 1. m_arg_index - the position of the value expression in the original
+ argument array, e.g. in Item_func_in::args[] or Item_func_case::args[].
+
+ 2. m_handler - the pointer to the data type handler that the owner
+ will use to compare the pair {args[m_predicate_index],args[m_arg_index]}.
+
+ 3. m_handler_index - the index of an m_comparators[] element corresponding
+ to the leftmost pair that uses exactly the same Type_handler for
+ comparison. m_handler_index helps to maintain unique data type handlers.
+ - m_comparators[i].m_handler_index==i means that this is the
+ leftmost pair that uses the Type_handler m_handler for comparision.
+ - If m_comparators[i].m_handlex_index!=i, it means that some earlier
+ element m_comparators[j<i] is already using this Type_handler
+ pointed by m_handler.
+
+ 4. m_cmp_item - the pointer to a cmp_item instance to handle comparison
+ for this pair. Only unique type handlers have m_cmp_item!=NULL.
+ Non-unique type handlers share the same cmp_item instance.
+ For all m_comparators[] elements the following assersion it true:
+ (m_handler_index==i) == (m_cmp_item!=NULL)
+*/
+class Predicant_to_list_comparator
+{
+ // Allocate memory on thd memory root for "nvalues" values.
+ bool alloc_comparators(THD *thd, uint nvalues);
+
+ /**
+ Look up m_comparators[] for a comparator using the given data type handler.
+ @param [OUT] idx - the index of the found comparator is returned here
+ @param [IN] handler - the data type handler to find
+ @param [IN] count - search in the range [0,count) only
+ @retval true - this type handler was not found
+ (*idx is not defined in this case).
+ @retval false - this type handler was found (the position of the
+ found handler is returned in idx).
+ */
+ bool find_handler(uint *idx, const Type_handler *handler, uint count)
+ {
+ DBUG_ASSERT(count < m_comparator_count);
+ for (uint i= 0 ; i < count; i++)
+ {
+ if (m_comparators[i].m_handler == handler)
+ {
+ *idx= i;
+ return false;
+ }
+ }
+ return true;
+ }
+
+ /**
+ Populate m_comparators[i].m_handler_index for all elements in
+ m_comparators using the information in m_comparators[i].m_handlers,
+ which was previously populated by a add_predicant() call and a number
+ of add_value() calls.
+ @param [OUT] compatible - If all comparator types are compatible,
+ their data type handler is returned here.
+ @param [OUT] unuque_cnt - The number of unique data type handlers found.
+ If the value returned in *unique_cnt is 0,
+ it means all values were explicit NULLs:
+ expr0 IN (NULL,NULL,..,NULL)
+ @param [OUT] found_type - The bit mask for all found cmp_type()'s.
+ */
+ void detect_unique_handlers(Type_handler_hybrid_field_type *compatible,
+ uint *unique_cnt, uint *found_types);
+ /**
+ Creates a cmp_item instances for all unique handlers and stores
+ them into m_comparators[i].m_cmp_item, using the information previously
+ populated by add_predicant(), add_value(), detect_unque_handlers().
+ */
+
+ /*
+ Compare the predicant to the value pointed by m_comparators[i].
+ @param args - the same argument array which was previously used
+ with add_predicant() and add_value().
+ @param i - which pair to check.
+ @retval true - the predicant is not equal to the value.
+ @retval false - the predicant is equal to the value.
+ @retval UNKNOWN - the result is uncertain yet because the predicant
+ and/or the value returned NULL,
+ more pairs {pred,valueN} should be checked.
+ */
+ int cmp_arg(Item_args *args, uint i)
+ {
+ Predicant_to_value_comparator *cmp=
+ &m_comparators[m_comparators[i].m_handler_index];
+ cmp_item *in_item= cmp->m_cmp_item;
+ DBUG_ASSERT(in_item);
+ /*
+ If this is the leftmost pair that uses the data type handler
+ pointed by m_comparators[i].m_handler, then we need to cache
+ the predicant value representation used by this handler.
+ */
+ if (m_comparators[i].m_handler_index == i)
+ in_item->store_value(args->arguments()[m_predicant_index]);
+ /*
+ If the predicant item has null_value==true then:
+ - In case of scalar expression we can returns UNKNOWN immediately.
+ No needs to check the result of the value item.
+ - In case of ROW, null_value==true means that *some* row elements
+ returned NULL, but *some* elements can still be non-NULL!
+ We need to get the result of the value item and test
+ if non-NULL elements in the predicant and the value produce
+ TRUE (not equal), or UNKNOWN.
+ */
+ if (args->arguments()[m_predicant_index]->null_value &&
+ m_comparators[i].m_handler != &type_handler_row)
+ return UNKNOWN;
+ return in_item->cmp(args->arguments()[m_comparators[i].m_arg_index]);
+ }
+
+ /**
+ Predicant_to_value_comparator - a comparator for one pair (pred,valueN).
+ See comments above.
+ */
+ struct Predicant_to_value_comparator
+ {
+ const Type_handler *m_handler;
+ cmp_item *m_cmp_item;
+ uint m_arg_index;
+ uint m_handler_index;
+ void cleanup()
+ {
+ if (m_cmp_item)
+ delete m_cmp_item;
+ memset(this, 0, sizeof(*this));
+ }
+ };
+
+ Predicant_to_value_comparator *m_comparators; // The comparator array
+ uint m_comparator_count;// The number of elements in m_comparators[]
+ uint m_predicant_index; // The position of the predicant in its argument list,
+ // e.g. for Item_func_in m_predicant_index is 0,
+ // as predicant is stored in Item_func_in::args[0].
+ // For Item_func_case m_predicant_index is
+ // set to Item_func_case::first_expr_num.
+
+public:
+ Predicant_to_list_comparator(THD *thd, uint nvalues)
+ :m_comparator_count(0),
+ m_predicant_index(0)
+ {
+ alloc_comparators(thd, nvalues);
+ }
+
+ uint comparator_count() const { return m_comparator_count; }
+ const Type_handler *get_comparator_type_handler(uint i) const
+ {
+ DBUG_ASSERT(i < m_comparator_count);
+ return m_comparators[i].m_handler;
+ }
+ uint get_comparator_arg_index(uint i) const
+ {
+ DBUG_ASSERT(i < m_comparator_count);
+ return m_comparators[i].m_arg_index;
+ }
+ cmp_item *get_comparator_cmp_item(uint i) const
+ {
+ DBUG_ASSERT(i < m_comparator_count);
+ return m_comparators[i].m_cmp_item;
+ }
+
+#ifndef DBUG_OFF
+ void debug_print(THD *thd)
+ {
+ for (uint i= 0; i < m_comparator_count; i++)
+ {
+ DBUG_EXECUTE_IF("Predicant_to_list_comparator",
+ push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+ ER_UNKNOWN_ERROR, "[%d] arg=%d handler=%d", i,
+ m_comparators[i].m_arg_index,
+ m_comparators[i].m_handler_index););
+ }
+ }
+#endif
+
+ void add_predicant(Item_args *args, uint predicant_index)
+ {
+ DBUG_ASSERT(m_comparator_count == 0); // Set in constructor
+ DBUG_ASSERT(m_predicant_index == 0); // Set in constructor
+ DBUG_ASSERT(predicant_index < args->argument_count());
+ m_predicant_index= predicant_index;
+ }
+ /**
+ Add a new element into m_comparators[], using a {pred,valueN} pair.
+
+ @param args - the owner function's argument list
+ @param value_index - the value position in args
+ @retval true - could not add an element because of non-comparable
+ arguments (e.g. ROWs with size)
+ @retval false - a new element was successfully added.
+ */
+ bool add_value(Item_args *args, uint value_index);
+
+ /**
+ Add a new element into m_comparators[], ignoring explicit NULL values.
+ If the value appeared to be an explicit NULL, nulls_found[0] is set to true.
+ */
+ bool add_value_skip_null(Item_args *args, uint value_index,
+ bool *nulls_found);
+
+ /**
+ Signal "this" that there will be no new add_value*() calls,
+ so it can prepare its internal structures for comparison.
+
+ @param [OUT] compatible - If all comparators are compatible,
+ their data type handler is returned here.
+ @param [OUT] unuque_cnt - The number of unique data type handlers found.
+ If the value returned in *unique_cnt is 0,
+ it means all values were explicit NULLs:
+ expr0 IN (NULL,NULL,..,NULL)
+ @param [OUT] found_type - The bit mask for all found cmp_type()'s.
+ */
+ void all_values_added(Type_handler_hybrid_field_type *compatible,
+ uint *unique_cnt, uint *found_types)
+ {
+ detect_unique_handlers(compatible, unique_cnt, found_types);
+ }
+ /**
+ Creates cmp_item instances for all unique handlers and stores
+ them into m_comparators[].m_cmp_item, using the information previously
+ populated by add_predicant(), add_value() and detect_unque_handlers().
+ */
+ bool make_unique_cmp_items(THD *thd, CHARSET_INFO *cs);
+ void cleanup()
+ {
+ DBUG_ASSERT(m_comparators);
+ for (uint i= 0; i < m_comparator_count; i++)
+ m_comparators[i].cleanup();
+ memset(m_comparators, 0, sizeof(m_comparators[0]) * m_comparator_count);
+ m_comparator_count= 0;
+ m_predicant_index= 0;
+ }
+ bool init_clone(THD *thd, uint nvalues)
+ {
+ m_comparator_count= 0;
+ m_predicant_index= 0;
+ return alloc_comparators(thd, nvalues);
+ }
+ /**
+ @param [IN] args - The argument list that was previously used with
+ add_predicant() and add_value().
+ @param [OUT] idx - In case if a value that is equal to the predicant
+ was found, the index of the matching value is returned
+ here. Otherwise, *idx is not changed.
+ @param [IN/OUT] found_unknown_values - how to handle UNKNOWN results.
+ If found_unknown_values is NULL (e.g. Item_func_case),
+ cmp() returns immediately when the first UNKNOWN
+ result is found.
+ If found_unknown_values is non-NULL (Item_func_in),
+ cmp() does not return when an UNKNOWN result is found,
+ sets *found_unknown_values to true, and continues
+ to compare the remaining pairs to find FALSE
+ (i.e. the value that is equal to the predicant).
+
+ @retval false - Found a value that is equal to the predicant
+ @retval true - Didn't find an equal value
+ */
+ bool cmp(Item_args *args, uint *idx, bool *found_unknown_values)
+ {
+ for (uint i= 0 ; i < m_comparator_count ; i++)
+ {
+ DBUG_ASSERT(m_comparators[i].m_handler != NULL);
+ const int rc= cmp_arg(args, i);
+ if (rc == FALSE)
+ {
+ *idx= m_comparators[i].m_arg_index;
+ return false; // Found a matching value
+ }
+ if (rc == UNKNOWN)
+ {
+ if (!found_unknown_values)
+ return true;
+ *found_unknown_values= true;
+ }
+ }
+ return true; // Not found
+ }
+
+};
+
+
/*
The class Item_func_case is the CASE ... WHEN ... THEN ... END function
implementation.
@@ -1565,18 +1912,17 @@ class cmp_item_sort_string_in_static :public cmp_item_string
function and only comparators for there result types are used.
*/
-class Item_func_case :public Item_func_hybrid_field_type
+class Item_func_case :public Item_func_hybrid_field_type,
+ public Predicant_to_list_comparator
{
int first_expr_num, else_expr_num;
enum Item_result left_cmp_type;
String tmp_value;
uint ncases;
- Item_result cmp_type;
DTCollation cmp_collation;
- cmp_item *cmp_items[6]; /* For all result types */
- cmp_item *case_item;
Item **arg_buffer;
uint m_found_types;
+ bool prepare_predicant_and_values(THD *thd, uint *found_types);
public:
Item_func_case(THD *thd, List<Item> &list, Item *first_expr_arg,
Item *else_expr_arg);
@@ -1593,7 +1939,13 @@ class Item_func_case :public Item_func_hybrid_field_type
virtual void print(String *str, enum_query_type query_type);
Item *find_item(String *str);
CHARSET_INFO *compare_collation() const { return cmp_collation.collation; }
- void cleanup();
+ void cleanup()
+ {
+ DBUG_ENTER("Item_func_case::cleanup");
+ Item_func::cleanup();
+ Predicant_to_list_comparator::cleanup();
+ DBUG_VOID_RETURN;
+ }
Item* propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond);
bool need_parentheses_in_default() { return true; }
Item *get_copy(THD *thd, MEM_ROOT *mem_root)
@@ -1603,9 +1955,9 @@ class Item_func_case :public Item_func_hybrid_field_type
Item_func_case *clone= (Item_func_case *) Item_func::build_clone(thd, mem_root);
if (clone)
{
- clone->case_item= 0;
clone->arg_buffer= 0;
- bzero(&clone->cmp_items, sizeof(cmp_items));
+ if (clone->Predicant_to_list_comparator::init_clone(thd, ncases))
+ return NULL;
}
return clone;
}
@@ -1638,7 +1990,8 @@ class Item_func_case :public Item_func_hybrid_field_type
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
+class Item_func_in :public Item_func_opt_neg,
+ public Predicant_to_list_comparator
{
/**
Usable if <in value list> is made only of constants. Returns true if one
@@ -1655,6 +2008,7 @@ class Item_func_in :public Item_func_opt_neg
}
return true;
}
+ bool prepare_predicant_and_values(THD *thd, uint *found_types);
protected:
SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param,
Field *field, Item *value);
@@ -1673,14 +2027,13 @@ class Item_func_in :public Item_func_opt_neg
and can be used safely as comparisons for key conditions
*/
bool arg_types_compatible;
- Item_result left_cmp_type;
- cmp_item *cmp_items[6]; /* One cmp_item for each result type */
Item_func_in(THD *thd, List<Item> &list):
- Item_func_opt_neg(thd, list), array(0), have_null(0),
+ Item_func_opt_neg(thd, list),
+ Predicant_to_list_comparator(thd, arg_count - 1),
+ array(0), have_null(0),
arg_types_compatible(FALSE)
{
- bzero(&cmp_items, sizeof(cmp_items));
allowed_arg_cols= 0; // Fetch this value from first argument
}
longlong val_int();
@@ -1697,7 +2050,6 @@ class Item_func_in :public Item_func_opt_neg
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()
{
@@ -1713,23 +2065,18 @@ class Item_func_in :public Item_func_opt_neg
fix_in_vector();
return false;
}
- bool fix_for_scalar_comparison_using_cmp_items(uint found_types);
+ bool fix_for_scalar_comparison_using_cmp_items(THD *thd, 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;
DBUG_ENTER("Item_func_in::cleanup");
Item_int_func::cleanup();
delete array;
array= 0;
- for (i= 0; i <= (uint)TIME_RESULT; i++)
- {
- delete cmp_items[i];
- cmp_items[i]= 0;
- }
+ Predicant_to_list_comparator::cleanup();
DBUG_VOID_RETURN;
}
void add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
@@ -1742,13 +2089,20 @@ class Item_func_in :public Item_func_opt_neg
will be replaced to a zero-filled Item_string.
Such a change would require rebuilding of cmp_items.
*/
- Context cmpctx(ANY_SUBST, m_comparator.cmp_type(),
- Item_func_in::compare_collation());
- for (uint i= 0; i < arg_count; i++)
+ if (arg_types_compatible)
+ {
+ Context cmpctx(ANY_SUBST, m_comparator.cmp_type(),
+ Item_func_in::compare_collation());
+ args[0]->propagate_equal_fields_and_change_item_tree(thd, cmpctx,
+ cond, &args[0]);
+ }
+ for (uint i= 0; i < comparator_count(); i++)
{
- if (arg_types_compatible || i > 0)
- args[i]->propagate_equal_fields_and_change_item_tree(thd, cmpctx,
- cond, &args[i]);
+ Context cmpctx(ANY_SUBST, get_comparator_type_handler(i)->cmp_type(),
+ Item_func_in::compare_collation());
+ uint idx= get_comparator_arg_index(i);
+ args[idx]->propagate_equal_fields_and_change_item_tree(thd, cmpctx,
+ cond, &args[idx]);
}
return this;
}
@@ -1766,7 +2120,8 @@ class Item_func_in :public Item_func_opt_neg
if (clone)
{
clone->array= 0;
- bzero(&clone->cmp_items, sizeof(cmp_items));
+ if (clone->Predicant_to_list_comparator::init_clone(thd, arg_count - 1))
+ return NULL;
}
return clone;
}
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index fc34c7c..fbc19cb 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -123,6 +123,49 @@ Type_handler_hybrid_field_type::Type_handler_hybrid_field_type()
}
+/***************************************************************************/
+
+const Type_handler *Type_handler_int_result::type_handler_for_comparison() const
+{
+ return &type_handler_longlong;
+}
+
+
+const Type_handler *Type_handler_string_result::type_handler_for_comparison() const
+{
+ return &type_handler_long_blob;
+}
+
+
+const Type_handler *Type_handler_decimal_result::type_handler_for_comparison() const
+{
+ return &type_handler_newdecimal;
+}
+
+
+const Type_handler *Type_handler_real_result::type_handler_for_comparison() const
+{
+ return &type_handler_double;
+}
+
+
+const Type_handler *Type_handler_time_common::type_handler_for_comparison() const
+{
+ return &type_handler_time;
+}
+
+const Type_handler *Type_handler_temporal_with_date::type_handler_for_comparison() const
+{
+ return &type_handler_datetime;
+}
+
+
+const Type_handler *Type_handler_row::type_handler_for_comparison() const
+{
+ return &type_handler_row;
+}
+
+
/**
Collect built-in data type handlers for comparison.
This method is very similar to item_cmp_type() defined in item.cc.
@@ -135,6 +178,8 @@ Type_handler_hybrid_field_type::Type_handler_hybrid_field_type()
void
Type_handler_hybrid_field_type::aggregate_for_comparison(const Type_handler *h)
{
+ DBUG_ASSERT(m_type_handler == m_type_handler->type_handler_for_comparison());
+ DBUG_ASSERT(h == h->type_handler_for_comparison());
Item_result a= cmp_type();
Item_result b= h->cmp_type();
if (a == STRING_RESULT && b == STRING_RESULT)
@@ -170,9 +215,12 @@ Type_handler_hybrid_field_type::aggregate_for_comparison(const Type_handler *h)
}
else
m_type_handler= &type_handler_double;
+ DBUG_ASSERT(m_type_handler == m_type_handler->type_handler_for_comparison());
}
+/***************************************************************************/
+
const Type_handler *
Type_handler::get_handler_by_field_type(enum_field_types type)
{
@@ -1232,6 +1280,51 @@ longlong Type_handler_decimal_result::
/***************************************************************************/
+cmp_item *Type_handler_int_result::make_cmp_item(THD *thd,
+ CHARSET_INFO *cs) const
+{
+ return new (thd->mem_root) cmp_item_int;
+}
+
+cmp_item *Type_handler_real_result::make_cmp_item(THD *thd,
+ CHARSET_INFO *cs) const
+{
+ return new (thd->mem_root) cmp_item_real;
+}
+
+cmp_item *Type_handler_decimal_result::make_cmp_item(THD *thd,
+ CHARSET_INFO *cs) const
+{
+ return new (thd->mem_root) cmp_item_decimal;
+}
+
+
+cmp_item *Type_handler_string_result::make_cmp_item(THD *thd,
+ CHARSET_INFO *cs) const
+{
+ return new (thd->mem_root) cmp_item_sort_string(cs);
+}
+
+cmp_item *Type_handler_row::make_cmp_item(THD *thd,
+ CHARSET_INFO *cs) const
+{
+ return new (thd->mem_root) cmp_item_row;
+}
+
+cmp_item *Type_handler_time_common::make_cmp_item(THD *thd,
+ CHARSET_INFO *cs) const
+{
+ return new (thd->mem_root) cmp_item_time;
+}
+
+cmp_item *Type_handler_temporal_with_date::make_cmp_item(THD *thd,
+ CHARSET_INFO *cs) const
+{
+ return new (thd->mem_root) cmp_item_datetime;
+}
+
+/***************************************************************************/
+
static int srtcmp_in(CHARSET_INFO *cs, const String *x,const String *y)
{
return cs->coll->strnncollsp(cs,
@@ -1311,7 +1404,8 @@ bool Type_handler_string_result::
func->fix_for_scalar_comparison_using_bisection(thd);
}
return
- func->fix_for_scalar_comparison_using_cmp_items(1U << (uint) STRING_RESULT);
+ func->fix_for_scalar_comparison_using_cmp_items(thd,
+ 1U << (uint) STRING_RESULT);
}
@@ -1325,7 +1419,8 @@ bool Type_handler_int_result::
*/
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);
+ func->fix_for_scalar_comparison_using_cmp_items(thd,
+ 1U << (uint) INT_RESULT);
}
@@ -1336,7 +1431,8 @@ bool Type_handler_real_result::
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);
+ func->fix_for_scalar_comparison_using_cmp_items(thd,
+ 1U << (uint) REAL_RESULT);
}
@@ -1347,7 +1443,8 @@ bool Type_handler_decimal_result::
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);
+ func->fix_for_scalar_comparison_using_cmp_items(thd,
+ 1U << (uint) DECIMAL_RESULT);
}
@@ -1358,7 +1455,8 @@ bool Type_handler_temporal_result::
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);
+ func->fix_for_scalar_comparison_using_cmp_items(thd,
+ 1U << (uint) TIME_RESULT);
}
diff --git a/sql/sql_type.h b/sql/sql_type.h
index 6a694df..eac6fb7 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -31,6 +31,7 @@ class Item_func_hex;
class Item_func_hybrid_field_type;
class Item_func_between;
class Item_func_in;
+class cmp_item;
class in_vector;
class Type_std_attributes;
class Sort_param;
@@ -243,6 +244,7 @@ class Type_handler
virtual enum_field_types real_field_type() const { return field_type(); }
virtual Item_result result_type() const= 0;
virtual Item_result cmp_type() const= 0;
+ virtual const Type_handler *type_handler_for_comparison() const= 0;
virtual const Type_handler*
type_handler_adjusted_to_max_octet_length(uint max_octet_length,
CHARSET_INFO *cs) const
@@ -320,6 +322,9 @@ class Type_handler
virtual longlong
Item_func_between_val_int(Item_func_between *func) const= 0;
+ virtual cmp_item *
+ make_cmp_item(THD *thd, CHARSET_INFO *cs) const= 0;
+
virtual in_vector *
make_in_vector(THD *thd, const Item_func_in *func, uint nargs) const= 0;
@@ -349,6 +354,7 @@ class Type_handler_row: public Type_handler
{
return ROW_RESULT;
}
+ const Type_handler *type_handler_for_comparison() const;
Field *make_num_distinct_aggregator_field(MEM_ROOT *, const Item *) const
{
DBUG_ASSERT(0);
@@ -423,6 +429,7 @@ class Type_handler_row: public Type_handler
}
longlong Item_func_between_val_int(Item_func_between *func) const;
+ cmp_item *make_cmp_item(THD *thd, CHARSET_INFO *cs) 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;
@@ -451,6 +458,7 @@ class Type_handler_real_result: public Type_handler_numeric
Item_result result_type() const { return REAL_RESULT; }
Item_result cmp_type() const { return REAL_RESULT; }
virtual ~Type_handler_real_result() {}
+ const Type_handler *type_handler_for_comparison() const;
void make_sort_key(uchar *to, Item *item, const SORT_FIELD_ATTR *sort_field,
Sort_param *param) const;
void sortlength(THD *thd,
@@ -474,6 +482,7 @@ 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;
+ cmp_item *make_cmp_item(THD *thd, CHARSET_INFO *cs) 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;
@@ -487,6 +496,7 @@ class Type_handler_decimal_result: public Type_handler_numeric
Item_result result_type() const { return DECIMAL_RESULT; }
Item_result cmp_type() const { return DECIMAL_RESULT; }
virtual ~Type_handler_decimal_result() {};
+ const Type_handler *type_handler_for_comparison() const;
Field *make_num_distinct_aggregator_field(MEM_ROOT *, const Item *) const;
void make_sort_key(uchar *to, Item *item, const SORT_FIELD_ATTR *sort_field,
Sort_param *param) const;
@@ -511,6 +521,7 @@ 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;
+ cmp_item *make_cmp_item(THD *thd, CHARSET_INFO *cs) 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;
@@ -523,6 +534,7 @@ class Type_handler_int_result: public Type_handler_numeric
Item_result result_type() const { return INT_RESULT; }
Item_result cmp_type() const { return INT_RESULT; }
virtual ~Type_handler_int_result() {}
+ const Type_handler *type_handler_for_comparison() const;
Field *make_num_distinct_aggregator_field(MEM_ROOT *, const Item *) const;
void make_sort_key(uchar *to, Item *item, const SORT_FIELD_ATTR *sort_field,
Sort_param *param) const;
@@ -547,6 +559,7 @@ 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;
+ cmp_item *make_cmp_item(THD *thd, CHARSET_INFO *cs) 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;
@@ -592,6 +605,7 @@ class Type_handler_string_result: public Type_handler
Item_result result_type() const { return STRING_RESULT; }
Item_result cmp_type() const { return STRING_RESULT; }
virtual ~Type_handler_string_result() {}
+ const Type_handler *type_handler_for_comparison() const;
const Type_handler *
type_handler_adjusted_to_max_octet_length(uint max_octet_length,
CHARSET_INFO *cs) const;
@@ -618,6 +632,7 @@ class Type_handler_string_result: public Type_handler
MYSQL_TIME *,
ulonglong fuzzydate) const;
longlong Item_func_between_val_int(Item_func_between *func) const;
+ cmp_item *make_cmp_item(THD *thd, CHARSET_INFO *cs) 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;
@@ -740,7 +755,9 @@ class Type_handler_time_common: public Type_handler_temporal_result
public:
virtual ~Type_handler_time_common() { }
enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
+ const Type_handler *type_handler_for_comparison() const;
int Item_save_in_field(Item *item, Field *field, bool no_conversions) const;
+ cmp_item *make_cmp_item(THD *thd, CHARSET_INFO *cs) const;
in_vector *make_in_vector(THD *, const Item_func_in *, uint nargs) const;
};
@@ -768,7 +785,9 @@ class Type_handler_temporal_with_date: public Type_handler_temporal_result
{
public:
virtual ~Type_handler_temporal_with_date() {}
+ const Type_handler *type_handler_for_comparison() const;
int Item_save_in_field(Item *item, Field *field, bool no_conversions) const;
+ cmp_item *make_cmp_item(THD *thd, CHARSET_INFO *cs) const;
in_vector *make_in_vector(THD *, const Item_func_in *, uint nargs) const;
};
