Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 418 insertions, 0 deletions

diff --git a/mysql-test/main/case.test b/mysql-test/main/case.test
new file mode 100644
index 00000000..10d8aa18
--- /dev/null
+++ b/mysql-test/main/case.test
@@ -0,0 +1,418 @@
+
+# Testing of CASE
+#
+
+
+--disable_warnings
+drop table if exists t1, t2;
+--enable_warnings
+
+select CASE "b" when "a" then 1 when "b" then 2 END as exp;
+select CASE "c" when "a" then 1 when "b" then 2 END as exp;
+select CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END as exp;
+select CASE BINARY "b" when "a" then 1 when "B" then 2 WHEN "b" then "ok" END as exp;
+select CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" END as exp;
+select CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end as exp;
+select CASE when 1=0 then "true" else "false" END as exp;
+select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END as exp;
+explain extended select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END as exp;
+select CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END as exp;
+select (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0 as exp;
+select (CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0 as exp;
+select case 1/0 when "a" then "true" else "false" END as exp;
+select case 1/0 when "a" then "true" END as exp;
+select (case 1/0 when "a" then "true" END) | 0 as exp;
+select (case 1/0 when "a" then "true" END) + 0.0 as exp;
+select case when 1>0 then "TRUE" else "FALSE" END as exp;
+select case when 1<0 then "TRUE" else "FALSE" END as exp;
+
+#
+# Test bug when using GROUP BY on CASE
+#
+create table t1 (a int);
+insert into t1 values(1),(2),(3),(4);
+select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase;
+explain extended select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase;
+select case a when 1 then "one" when 2 then "two" else "nothing" end as fcase, count(*) from t1 group by fcase;
+drop table t1;
+
+#
+# Test MAX(CASE ... ) that can return null
+#
+
+create table t1 (row int not null, col int not null, val varchar(255) not null);
+insert into t1 values (1,1,'orange'),(1,2,'large'),(2,1,'yellow'),(2,2,'medium'),(3,1,'green'),(3,2,'small');
+select max(case col when 1 then val else null end) as color from t1 group by row;
+drop table t1;
+
+SET NAMES latin1;
+
+#
+# CASE and argument types/collations aggregation into result 
+#
+CREATE TABLE t1 SELECT 
+ CASE WHEN 1 THEN _latin1'a' COLLATE latin1_danish_ci ELSE _latin1'a' END AS c1,
+ CASE WHEN 1 THEN _latin1'a' ELSE _latin1'a' COLLATE latin1_danish_ci END AS c2,
+ CASE WHEN 1 THEN 'a' ELSE  1  END AS c3,
+ CASE WHEN 1 THEN  1  ELSE 'a' END AS c4,
+ CASE WHEN 1 THEN 'a' ELSE 1.0 END AS c5,
+ CASE WHEN 1 THEN 1.0 ELSE 'a' END AS c6,
+ CASE WHEN 1 THEN  1  ELSE 1.0 END AS c7,
+ CASE WHEN 1 THEN 1.0 ELSE  1  END AS c8,
+ CASE WHEN 1 THEN 1.0 END AS c9,
+ CASE WHEN 1 THEN 0.1e1 else 0.1 END AS c10,
+ CASE WHEN 1 THEN 0.1e1 else 1 END AS c11,
+ CASE WHEN 1 THEN 0.1e1 else '1' END AS c12
+;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--error 1267
+SELECT CASE 
+  WHEN 1 
+  THEN _latin1'a' COLLATE latin1_danish_ci 
+  ELSE _latin1'a' COLLATE latin1_swedish_ci
+  END;
+
+--error 1270
+SELECT CASE _latin1'a' COLLATE latin1_general_ci
+  WHEN _latin1'a' COLLATE latin1_danish_ci  THEN 1
+  WHEN _latin1'a' COLLATE latin1_swedish_ci THEN 2
+  END;
+
+SELECT 
+CASE _latin1'a' COLLATE latin1_general_ci  WHEN _latin1'A' THEN '1' ELSE 2 END as e1,
+CASE _latin1'a' COLLATE latin1_bin         WHEN _latin1'A' THEN '1' ELSE 2 END as e2,
+CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_swedish_ci THEN '1' ELSE 2 END as e3,
+CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_bin        THEN '1' ELSE 2 END as e4
+;
+
+#
+# COALESCE is a CASE abbrevation:
+#
+# COALESCE(v1,v2) == CASE WHEN v1 IS NOT NULL THEN v1 ELSE v2 END
+#
+# COALESCE(V1, V2, . . . ,Vn ) =  
+#     CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, . . . ,Vn) END
+#
+# Check COALESCE argument types aggregation
+
+--error 1267
+CREATE TABLE t1 SELECT COALESCE(_latin1'a',_latin2'a');
+--error 1267
+CREATE TABLE t1 SELECT COALESCE('a' COLLATE latin1_swedish_ci,'b' COLLATE latin1_bin);
+CREATE TABLE t1 SELECT 
+ COALESCE(1), COALESCE(1.0),COALESCE('a'),
+ COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'),
+ COALESCE('a' COLLATE latin1_bin,'b');
+explain extended SELECT 
+ COALESCE(1), COALESCE(1.0),COALESCE('a'),
+ COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'),
+ COALESCE('a' COLLATE latin1_bin,'b');
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--error 1267
+CREATE TABLE t1 SELECT IFNULL('a' COLLATE latin1_swedish_ci, 'b' COLLATE latin1_bin);
+
+# Test for BUG#10151
+SELECT 'case+union+test'
+UNION 
+SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END;
+
+SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END;
+
+SELECT 'case+union+test'
+UNION 
+SELECT CASE '1' WHEN '2' THEN 'BUG' ELSE 'nobug' END;
+
+#
+# Bug #17896: problem with MIN(CASE...)
+#
+
+create table t1(a float, b int default 3);
+insert into t1 (a) values (2), (11), (8);
+select min(a), min(case when 1=1 then a else NULL end),
+  min(case when 1!=1 then NULL else a end) 
+from t1 where b=3 group by b;
+drop table t1;
+
+
+#
+# Tests for bug #9939: conversion of the arguments for COALESCE and IFNULL
+#
+
+CREATE TABLE t1 (EMPNUM INT);
+INSERT INTO t1 VALUES (0), (2);
+CREATE TABLE t2 (EMPNUM DECIMAL (4, 2));
+INSERT INTO t2 VALUES (0.0), (9.0);
+
+SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM,
+               t1.EMPNUM AS EMPMUM1, t2.EMPNUM AS EMPNUM2
+  FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM;
+
+SELECT IFNULL(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM,
+               t1.EMPNUM AS EMPMUM1, t2.EMPNUM AS EMPNUM2
+  FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM;
+
+DROP TABLE t1,t2;
+
+--echo End of 4.1 tests
+
+#
+# #30782: Truncated UNSIGNED BIGINT columns 
+#
+create table t1 (a int, b bigint unsigned);
+create table t2 (c int);
+insert into t1 (a, b) values (1,4572794622775114594), (2,18196094287899841997),
+  (3,11120436154190595086);
+insert into t2 (c) values (1), (2), (3);
+select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 
+  join t2 on t1.a=t2.c order by d;
+select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 
+  join t2 on t1.a=t2.c where b=11120436154190595086 order by d;
+drop table t1, t2;
+
+--echo End of 5.0 tests
+
+--echo #
+--echo # Bug#19875294 ASSERTION `SRC' FAILED IN MY_STRNXFRM_UNICODE
+--echo #              (SIG 6 -STRINGS/CTYPE-UTF8.C:5151)
+--echo #
+
+set @@sql_mode='';
+CREATE TABLE t1(c1 SET('','')CHARACTER SET ucs2);
+INSERT INTO t1 VALUES(990101.102);
+SELECT COALESCE(c1)FROM t1 ORDER BY 1;
+DROP TABLE t1;
+set @@sql_mode=default;
+
+#
+# lp:1001510
+# Bug #11764313 57135: CRASH IN ITEM_FUNC_CASE::FIND_ITEM WITH CASE WHEN
+# ELSE CLAUSE
+#
+
+CREATE TABLE t1(a YEAR);
+SELECT 1 FROM t1 WHERE a=1 AND CASE 1 WHEN a THEN 1 ELSE 1 END;
+DROP TABLE t1;
+
+#
+# lp:839387 Assertion `(Item_result)i != TIME_RESULT' failed with CASE + datetime
+#
+
+create table t1 (f1 time);
+insert t1 values ('00:00:00'),('00:01:00');
+select case t1.f1 when '00:00:00' then 1 end from t1;
+drop table t1;
+
+--echo #
+--echo # MDEV-9745 Crash with CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 4 END
+--echo #
+CREATE TABLE t1 SELECT CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 4 END AS a;
+DESCRIBE t1;
+DROP TABLE t1;
+CREATE TABLE t1 SELECT CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 40 END AS a;
+DESCRIBE t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Start of 10.1 test
+--echo #
+
+--echo #
+--echo # MDEV-8752 Wrong result for SELECT..WHERE CASE enum_field WHEN 1 THEN 1 ELSE 0 END AND a='5'
+--echo #
+CREATE TABLE t1 (a ENUM('5','6') CHARACTER SET BINARY);
+INSERT INTO t1 VALUES ('5'),('6');
+SELECT * FROM t1 WHERE a='5';
+SELECT * FROM t1 WHERE a=1;
+SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END;
+SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END AND a='5';
+--echo # Multiple comparison types in CASE, not Ok to propagate
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END AND a='5';
+DROP TABLE t1;
+
+CREATE TABLE t1 (a ENUM('a','b','100'));
+INSERT INTO t1 VALUES ('a'),('b'),('100');
+SELECT * FROM t1 WHERE a='a';
+SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END;
+SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END AND a='a';
+--echo # String comparison in CASE and in the equality, ok to propagate
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END AND a='a';
+
+SELECT * FROM t1 WHERE a=3;
+SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END;
+SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a=3;
+--echo # Integer comparison in CASE and in the equality, not ok to propagate
+--echo # ENUM does not support this type of propagation yet.
+--echo # This can change in the future. See MDEV-8748.
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a=3;
+
+SELECT * FROM t1 WHERE a=3;
+SELECT * FROM t1 WHERE CASE a WHEN '100' THEN 1 ELSE 0 END;
+SELECT * FROM t1 WHERE CASE a WHEN '100' THEN 1 ELSE 0 END AND a=3;
+--echo # String comparison in CASE, integer comparison in the equality, not Ok to propagate
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE CASE a WHEN '100' THEN 1 ELSE 0 END AND a=3;
+
+SELECT * FROM t1 WHERE a='100';
+SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END;
+SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a='100';
+--echo # Integer comparison in CASE, string comparison in the equality, not Ok to propagate
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a='100';
+
+SELECT * FROM t1 WHERE a='100';
+SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END;
+SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a='100';
+--echo # Multiple type comparison in CASE, string comparison in the equality, not Ok to propagate
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a='100';
+
+SELECT * FROM t1 WHERE a=3;
+SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END;
+SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a=3;
+--echo # Multiple type comparison in CASE, integer comparison in the equality, not Ok to propagate
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a=3;
+
+DROP TABLE t1;
+--echo #
+--echo # End of MDEV-8752
+--echo #
+
+--echo #
+--echo # End of 10.1 test
+--echo #
+
+#
+# caching of first argument in CASE/IN for temporal types
+#
+#
+
+# should not convert all values to time
+select case 'foo' when time'10:00:00' then 'never' when '0' then 'bug' else 'ok' end as exp;
+select 'foo' in (time'10:00:00','0');
+
+create table t1 (a time);
+insert t1 values (100000), (102030), (203040);
+# only one warning, TIME('foo') should be cached
+select case 'foo' when a then 'never' when '0' then 'bug' else 'ok' end from t1;
+select 'foo' in (a,'0') from t1;
+drop table t1;
+
+# first comparison should be as date, second as time
+select case '20:10:05' when date'2020-10-10' then 'never' when time'20:10:5' then 'ok' else 'bug' end as exp;
+
+--echo #
+--echo # End of 10.2 test
+--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;
+
+PREPARE stmt FROM "SELECT
+  CASE -1
+    WHEN -9223372036854775808 THEN 'one'
+    WHEN 18446744073709551615 THEN 'two'
+  END AS c";
+EXECUTE stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+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;
+
+PREPARE stmt FROM "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";
+EXECUTE stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+--echo #
+--echo # MDEV-13864 Change Item_func_case to store the predicant in args[0]
+--echo #
+
+SET NAMES latin1;
+CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1);
+INSERT INTO t1 VALUES ('a'),('b'),('c');
+
+# should propagate the predicant and the WHEN arguments (they are in comparison and use ANY_SUBST)
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE a WHEN 'a' THEN 'a' ELSE 'a' END='a';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN a THEN 'a' ELSE 'a' END='a';
+
+# should not propagate the THEN and the ELSE arguments (they are not in comparison and use IDENTITY_SUBST)
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN a ELSE 'a' END='a';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN 'a' ELSE a END='a';
+
+ALTER TABLE t1 MODIFY a VARBINARY(10);
+
+# with VARBINARY it should propagate all arguments
+# as IDENTITY_SUBST for VARBINARY allows substitution
+# of even those arguments that are not in comparison
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE a WHEN 'a' THEN 'a' ELSE 'a' END='a';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN a THEN 'a' ELSE 'a' END='a';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN a ELSE 'a' END='a';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN 'a' ELSE a END='a';
+
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-17411 Wrong WHERE optimization with simple CASE and searched CASE
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
+SELECT * FROM t1 WHERE CASE a WHEN b THEN 1 END=1;
+SELECT * FROM t1 WHERE CASE WHEN a THEN b ELSE 1 END=3;
+
+SELECT * FROM t1 WHERE
+  CASE      a WHEN b THEN 1 END=1 AND
+  CASE WHEN a THEN b ELSE 1 END=3;
+
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE
+  CASE      a WHEN b THEN 1 END=1 AND
+  CASE WHEN a THEN b ELSE 1 END=3;
+
+DROP TABLE t1;
+
+
+--echo #
+--echo # End of 10.3 test
+--echo #