summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/contributors.result2
-rw-r--r--mysql-test/r/ctype_ucs.result17
-rw-r--r--mysql-test/r/errors.result4
-rw-r--r--mysql-test/r/func_concat.result113
-rw-r--r--mysql-test/r/func_crypt.result18
-rw-r--r--mysql-test/r/gis.result18
-rw-r--r--mysql-test/r/mysql.result58
-rw-r--r--mysql-test/r/mysqld--help.result3
-rw-r--r--mysql-test/r/mysqltest.result7
-rw-r--r--mysql-test/r/order_by.result16
-rw-r--r--mysql-test/r/partition_alter.result20
-rw-r--r--mysql-test/r/subselect_mat.result88
-rw-r--r--mysql-test/r/subselect_mat_cost_bugs.result17
-rw-r--r--mysql-test/r/subselect_sj2_mat.result22
-rw-r--r--mysql-test/r/subselect_sj_mat.result88
-rw-r--r--mysql-test/r/view.result80
16 files changed, 569 insertions, 2 deletions
diff --git a/mysql-test/r/contributors.result b/mysql-test/r/contributors.result
index 4a26d0f19dd..5d92184f191 100644
--- a/mysql-test/r/contributors.result
+++ b/mysql-test/r/contributors.result
@@ -2,9 +2,11 @@ SHOW CONTRIBUTORS;
Name Location Comment
Booking.com https://www.booking.com Founding member, Platinum Sponsor of the MariaDB Foundation
Alibaba Cloud https://intl.aliyun.com Platinum Sponsor of the MariaDB Foundation
+Tencent Cloud https://cloud.tencent.com Platinum Sponsor of the MariaDB Foundation
MariaDB Corporation https://mariadb.com Founding member, Gold Sponsor of the MariaDB Foundation
Visma https://visma.com Gold Sponsor of the MariaDB Foundation
DBS https://dbs.com Gold Sponsor of the MariaDB Foundation
+IBM https://www.ibm.com Gold Sponsor of the MariaDB Foundation
Nexedi https://www.nexedi.com Silver Sponsor of the MariaDB Foundation
Acronis http://www.acronis.com Silver Sponsor of the MariaDB Foundation
Auttomattic https://automattic.com Bronze Sponsor of the MariaDB Foundation
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index 389a5d3850a..08af0186080 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -5606,6 +5606,23 @@ SELECT 'a','aa';
a aa
a aa
#
+# MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery
+#
+SET NAMES utf8, character_set_connection=ucs2;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=_utf8'derived_merge=on';
+CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1);
+INSERT INTO t1 VALUES('abcdefghi');
+SET NAMES utf8, character_set_connection=ucs2;
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT HEX(t) t2 FROM t1) sub;
+c2
+616263646566676869-616263646566676869
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TO_BASE64(t) t2 FROM t1) sub;
+c2
+YWJjZGVmZ2hp-YWJjZGVmZ2hp
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
+#
# End of 10.0 tests
#
select collation(cast("a" as char(10) unicode binary));
diff --git a/mysql-test/r/errors.result b/mysql-test/r/errors.result
index cf3e738395a..6e88ad00ad9 100644
--- a/mysql-test/r/errors.result
+++ b/mysql-test/r/errors.result
@@ -172,3 +172,7 @@ UPDATE t1 SET a = 'new'
WHERE COLUMN_CREATE( 1, 'v', 1, 'w' ) IS NULL;
ERROR 22007: Illegal value used as argument of dynamic column function
drop table t1;
+set max_session_mem_used = 8192;
+select * from seq_1_to_1000;
+Got one of the listed errors
+set global max_session_mem_used = default;
diff --git a/mysql-test/r/func_concat.result b/mysql-test/r/func_concat.result
index 925158ab129..b87ee7bfc52 100644
--- a/mysql-test/r/func_concat.result
+++ b/mysql-test/r/func_concat.result
@@ -149,3 +149,116 @@ CALL p1();
########################################40100.000
DROP PROCEDURE p1;
# End of 5.1 tests
+#
+# Start of 10.0 tests
+#
+#
+# MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='derived_merge=on';
+CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1);
+INSERT INTO t1 VALUES('1234567');
+SELECT CONCAT(SUBSTR(t2, 1, 3), SUBSTR(t2, 5)) c1,
+CONCAT(SUBSTR(t2,1,3),'---',SUBSTR(t2,5)) c2
+FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub;
+c1 c2
+123567 123---567
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub;
+c2
+1234567-1234567
+DROP TABLE t1;
+CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1);
+INSERT INTO t1 VALUES('1234567');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub;
+c2
+1234567-1234567
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT REVERSE(t) t2 FROM t1) sub;
+c2
+7654321-7654321
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT SOUNDEX(t) t2 FROM t1) sub;
+c2
+-
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TO_BASE64(t) t2 FROM t1) sub;
+c2
+MTIzNDU2Nw==-MTIzNDU2Nw==
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT WEIGHT_STRING(t) t2 FROM t1) sub;
+c2
+1234567-1234567
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT HEX(t) t2 FROM t1) sub;
+c2
+31323334353637-31323334353637
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT QUOTE(t) t2 FROM t1) sub;
+c2
+'1234567'-'1234567'
+DROP TABLE t1;
+CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1);
+INSERT INTO t1 VALUES(TO_BASE64('abcdefghi'));
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT FROM_BASE64(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+DROP TABLE t1;
+CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1);
+INSERT INTO t1 VALUES(HEX('abcdefghi'));
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UNHEX(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+DROP TABLE t1;
+CREATE TABLE t1 (t VARCHAR(30) CHARSET latin1);
+INSERT INTO t1 VALUES('test');
+SELECT LENGTH(CONCAT(t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub;
+c2
+16
+SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub;
+c2
+33
+SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub;
+c2
+34
+SELECT LENGTH(CONCAT(t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub;
+c2
+4
+SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub;
+c2
+9
+SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub;
+c2
+10
+DROP TABLE t1;
+CREATE TABLE t1 (t VARCHAR(64) CHARSET latin1);
+INSERT INTO t1 VALUES('123456789');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT MD5(t) t2 FROM t1) sub;
+c2
+25f9e794323b453885f5181f1b624d0b-25f9e794323b453885f5181f1b624d0b
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT FORMAT(t,2) t2 FROM t1) sub;
+c2
+123,456,789.00-123,456,789.00
+DROP TABLE t1;
+CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1);
+INSERT INTO t1 VALUES('abcdefghi');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT INSERT(t,3,4,'xxx') t2 FROM t1) sub;
+c2
+abxxxghi-abxxxghi
+DROP TABLE t1;
+CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1);
+INSERT INTO t1 VALUES('abcdefghi');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LEFT(t,10) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT RIGHT(t,10) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT SUBSTR(t,1,10) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LTRIM(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT RTRIM(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TRIM(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/func_crypt.result b/mysql-test/r/func_crypt.result
index c8a621e2fd3..aaa6aa61eae 100644
--- a/mysql-test/r/func_crypt.result
+++ b/mysql-test/r/func_crypt.result
@@ -107,6 +107,24 @@ OLD_PASSWORD(c1) PASSWORD(c1)
DROP TABLE t1;
# End of 5.0 tests
#
+# Start of 10.0 tests
+#
+#
+# MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='derived_merge=on';
+CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1);
+INSERT INTO t1 VALUES('abcdefghi');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT ENCRYPT(t,'aa') t2 FROM t1) sub;
+c2
+aaHHlPHAM4sjs-aaHHlPHAM4sjs
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
+#
+# End of 10.0 tests
+#
+#
# Start of 10.1 tests
#
# Start of func_str_ascii_checksum.inc
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index 31335dc2af6..49618c5c8a6 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -1713,6 +1713,24 @@ AsText(g)
NULL
POINT(1 1)
#
+# MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='derived_merge=on';
+CREATE TABLE t1 (x INT, y INT);
+INSERT INTO t1 VALUES(0,0);
+SELECT LENGTH(t2) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub;
+c2
+25
+SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub;
+c2
+51
+SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub;
+c2
+52
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
+#
# End 10.0 tests
#
SHOW CREATE TABLE information_schema.geometry_columns;
diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result
index 4097a22ea43..09f014da627 100644
--- a/mysql-test/r/mysql.result
+++ b/mysql-test/r/mysql.result
@@ -544,3 +544,61 @@ ERROR 1300 (HY000) at line 2: Invalid utf8 character string: 'test\xF0\x9F\x98\x
set GLOBAL sql_mode=default;
End of tests
+create table `a1\``b1` (a int);
+show tables;
+Tables_in_test
+a1\`b1
+insert `a1\``b1` values (1),(2);
+show create table `a1\``b1`;
+Table Create Table
+a1\`b1 CREATE TABLE `a1\``b1` (
+ `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `a1\``b1` (
+ `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+INSERT INTO `a1\``b1` VALUES (1),(2);
+insert `a1\``b1` values (4),(5);
+show create table `a1\``b1`;
+Table Create Table
+a1\`b1 CREATE TABLE `a1\``b1` (
+ `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from `a1\``b1`;
+a
+1
+2
+drop table `a1\``b1`;
+set sql_mode=ansi_quotes;
+create table "a1\""b1" (a int);
+show tables;
+Tables_in_test
+a1\"b1
+insert "a1\""b1" values (1),(2);
+show create table "a1\""b1";
+Table Create Table
+a1\"b1 CREATE TABLE "a1\""b1" (
+ "a" int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE "a1\""b1" (
+ "a" int(11) DEFAULT NULL
+);
+/*!40101 SET character_set_client = @saved_cs_client */;
+INSERT INTO "a1\""b1" VALUES (1),(2);
+insert "a1\""b1" values (4),(5);
+show create table "a1\""b1";
+Table Create Table
+a1\"b1 CREATE TABLE "a1\""b1" (
+ "a" int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from "a1\""b1";
+a
+1
+2
+drop table "a1\""b1";
+set sql_mode=default;
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index bc4c96ed26f..8716274dd4b 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -1,3 +1,4 @@
+Windows bug: happens when a new line is exactly at the right offset
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file.
@@ -1258,7 +1259,7 @@ ft-query-expansion-limit 20
ft-stopword-file (No default value)
gdb FALSE
general-log FALSE
-getopt-prefix-matching TRUE
+getopt-prefix-matching FALSE
group-concat-max-len 1048576
gtid-domain-id 0
gtid-ignore-duplicates FALSE
diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result
index 237f04d6d28..fe269152357 100644
--- a/mysql-test/r/mysqltest.result
+++ b/mysql-test/r/mysqltest.result
@@ -983,5 +983,10 @@ disconnect con1;
con2
disconnect con2;
-closed_connection-
-End of tests
connection default;
+set sql_mode=no_backslash_escapes;
+select "foo\""bar";
+foo\"bar
+foo\"bar
+set sql_mode=default;
+End of tests
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 02381ebeed5..f43e6ce18af 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -3143,3 +3143,19 @@ id select_type table type possible_keys key key_len ref rows Extra
drop table t0,t1,t2,t3;
set @@optimizer_switch=@tmp_8989;
set optimizer_switch='orderby_uses_equalities=on';
+#
+# MDEV-10880: Assertions `keypart_map' or
+# `prebuilt->search_tuple->n_fields > 0' fail on DISTINCT and
+# GROUP BY constant
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT DISTINCT pk FROM t1 GROUP BY 'foo';
+pk
+1
+SELECT DISTINCT pk FROM t1;
+pk
+1
+2
+3
+DROP TABLE t1;
diff --git a/mysql-test/r/partition_alter.result b/mysql-test/r/partition_alter.result
index df3ed14e10a..6aa0039c8d2 100644
--- a/mysql-test/r/partition_alter.result
+++ b/mysql-test/r/partition_alter.result
@@ -100,3 +100,23 @@ t1 CREATE TABLE `t1` (
insert t1 values (2, '2020-01-03', 20);
ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1`
drop table t1;
+create table t1 (id_1 int auto_increment, id_2 int, id_3 int, d1 date, dt1 datetime default current_timestamp, dt2 datetime default current_timestamp on update current_timestamp, primary key (id_2, id_3), key(id_1)) partition by hash(id_2) partitions 3 (partition p01, partition p02, partition p03);
+insert into t1 values(0, 1, 1, NULL, now(), now());
+alter online table t1 delay_key_write=1;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id_1` int(11) NOT NULL AUTO_INCREMENT,
+ `id_2` int(11) NOT NULL,
+ `id_3` int(11) NOT NULL,
+ `d1` date DEFAULT NULL,
+ `dt1` datetime DEFAULT current_timestamp(),
+ `dt2` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+ PRIMARY KEY (`id_2`,`id_3`),
+ KEY `id_1` (`id_1`)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1
+ PARTITION BY HASH (`id_2`)
+(PARTITION `p01` ENGINE = MyISAM,
+ PARTITION `p02` ENGINE = MyISAM,
+ PARTITION `p03` ENGINE = MyISAM)
+drop table t1;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index ede18083b85..bbcfa7d6980 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2303,6 +2303,94 @@ pk f1 sq
5 3 5
set optimizer_switch= @save_optimizer_switch;
DROP TABLE t1,t2;
+#
+# mdev-12838: scan of materialized of semi-join subquery in join
+#
+set @save_optimizer_switch=@@optimizer_switch;
+CREATE TABLE t1 (
+dispatch_group varchar(32),
+assignment_group varchar(32),
+sys_id char(32),
+PRIMARY KEY (sys_id),
+KEY idx1 (dispatch_group),
+KEY idx2 (assignment_group)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+ugroup varchar(32),
+user varchar(32),
+sys_id char(32),
+PRIMARY KEY (sys_id),
+KEY idx3 (ugroup),
+KEY idx4 (user)
+) ENGINE=MyISAM;
+CREATE TABLE t3 (
+type mediumtext,
+sys_id char(32),
+PRIMARY KEY (sys_id)
+) ENGINE=MyISAM;
+set optimizer_switch='materialization=off';
+explain SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where; Start temporary
+1 PRIMARY t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where
+1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t3_i.sys_id 2 Using index condition; Using where; End temporary
+1 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index
+SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+assignment_group
+df50316637232000158bbfc8bcbe5d23
+e08fad2637232000158bbfc8bcbe5d39
+ec70316637232000158bbfc8bcbe5d60
+7b10fd2637232000158bbfc8bcbe5d30
+ebb4620037332000158bbfc8bcbe5d89
+set optimizer_switch='materialization=on';
+explain SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2
+1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t2.ugroup 2 Using where
+1 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index
+2 MATERIALIZED t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where
+2 MATERIALIZED t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where
+SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+assignment_group
+df50316637232000158bbfc8bcbe5d23
+e08fad2637232000158bbfc8bcbe5d39
+ec70316637232000158bbfc8bcbe5d60
+7b10fd2637232000158bbfc8bcbe5d30
+ebb4620037332000158bbfc8bcbe5d89
+DROP TABLE t1,t2,t3;
+set optimizer_switch=@save_optimizer_switch;
# End of 5.5 tests
#
# MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index 17da3a18b4e..bdbe07cf879 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -485,3 +485,20 @@ FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3
ON (f3 = t2b.f2) );
f1
DROP TABLE t1,t2,t3;
+#
+# MDEV-12963: min/max optimization optimizing away all tables employed
+# for uncorrelated IN subquery used in a disjunct of WHERE
+#
+create table t1 (a int, index idx(a)) engine=myisam;
+insert into t1 values (4),(7),(1),(3),(9);
+select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5;
+a
+3
+7
+9
+explain
+select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index idx idx 5 NULL 5 Using where; Using index
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+drop table t1;
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index f0bf539b2fb..3ff0e619188 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -1648,3 +1648,25 @@ Warnings:
Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join (`test`.`t2`) where rand() < 0
drop table t1,t2;
set optimizer_switch=@save_optimizer_switch;
+#
+# mdev-12855: materialization of a semi-join subquery + ORDER BY
+#
+CREATE TABLE t1 (f1 varchar(8), KEY(f1)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('qux'),('foo');
+CREATE TABLE t2 (f2 varchar(8)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('bar'),('foo'),('qux');
+SELECT f1 FROM t1
+WHERE f1 IN ( SELECT f2 FROM t2 WHERE f2 > 'bar' )
+HAVING f1 != 'foo'
+ORDER BY f1;
+f1
+qux
+explain SELECT f1 FROM t1
+WHERE f1 IN ( SELECT f2 FROM t2 WHERE f2 > 'bar' )
+HAVING f1 != 'foo'
+ORDER BY f1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index f1 f1 11 NULL 2 Using where; Using index
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 11 func 1
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 72f875cf8a7..8109c36441e 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2343,6 +2343,94 @@ pk f1 sq
5 3 5
set optimizer_switch= @save_optimizer_switch;
DROP TABLE t1,t2;
+#
+# mdev-12838: scan of materialized of semi-join subquery in join
+#
+set @save_optimizer_switch=@@optimizer_switch;
+CREATE TABLE t1 (
+dispatch_group varchar(32),
+assignment_group varchar(32),
+sys_id char(32),
+PRIMARY KEY (sys_id),
+KEY idx1 (dispatch_group),
+KEY idx2 (assignment_group)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+ugroup varchar(32),
+user varchar(32),
+sys_id char(32),
+PRIMARY KEY (sys_id),
+KEY idx3 (ugroup),
+KEY idx4 (user)
+) ENGINE=MyISAM;
+CREATE TABLE t3 (
+type mediumtext,
+sys_id char(32),
+PRIMARY KEY (sys_id)
+) ENGINE=MyISAM;
+set optimizer_switch='materialization=off';
+explain SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where; Start temporary
+1 PRIMARY t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where
+1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t3_i.sys_id 2 Using index condition; Using where; End temporary
+1 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index
+SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+assignment_group
+df50316637232000158bbfc8bcbe5d23
+e08fad2637232000158bbfc8bcbe5d39
+ec70316637232000158bbfc8bcbe5d60
+7b10fd2637232000158bbfc8bcbe5d30
+ebb4620037332000158bbfc8bcbe5d89
+set optimizer_switch='materialization=on';
+explain SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2
+1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t2.ugroup 2 Using where
+1 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index
+2 MATERIALIZED t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where
+2 MATERIALIZED t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where
+SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+assignment_group
+df50316637232000158bbfc8bcbe5d23
+e08fad2637232000158bbfc8bcbe5d39
+ec70316637232000158bbfc8bcbe5d60
+7b10fd2637232000158bbfc8bcbe5d30
+ebb4620037332000158bbfc8bcbe5d89
+DROP TABLE t1,t2,t3;
+set optimizer_switch=@save_optimizer_switch;
# End of 5.5 tests
#
# MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index bd0ebb54ee7..1fae4e6ec9f 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -5991,6 +5991,68 @@ use_case_id InitialDeadline
10 2015-12-18
drop view v1;
drop table t1;
+#
+# MDEV-12666: CURRENT_ROLE() and DATABASE() does not work in a view
+#
+# DATABASE() fails only when the initial view creation features a NULL
+# default database.
+#
+# CREATE, USE and DROP database so that we have no "default" database.
+#
+CREATE DATABASE temporary;
+USE temporary;
+DROP DATABASE temporary;
+SELECT DATABASE();
+DATABASE()
+NULL
+CREATE VIEW test.v_no_db AS SELECT DATABASE() = 'temporary_two';
+SHOW CREATE VIEW test.v_no_db;
+View Create View character_set_client collation_connection
+v_no_db CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v_no_db` AS select database() = 'temporary_two' AS `DATABASE() = 'temporary_two'` latin1 latin1_swedish_ci
+PREPARE prepared_no_database FROM "SELECT DATABASE() = 'temporary_two'";
+#
+# All statements should return NULL
+#
+EXECUTE prepared_no_database;
+DATABASE() = 'temporary_two'
+NULL
+SELECT DATABASE() = 'temporary_two';
+DATABASE() = 'temporary_two'
+NULL
+SELECT * FROM test.v_no_db;
+DATABASE() = 'temporary_two'
+NULL
+CREATE DATABASE temporary_two;
+USE temporary_two;
+CREATE VIEW test.v_with_db AS SELECT DATABASE() = 'temporary_two';
+PREPARE prepared_with_database FROM "SELECT DATABASE() = 'temporary_two'";
+#
+# All statements should return 1;
+#
+SELECT DATABASE() = 'temporary_two';
+DATABASE() = 'temporary_two'
+1
+SELECT * FROM test.v_no_db;
+DATABASE() = 'temporary_two'
+1
+SELECT * FROM test.v_with_db;
+DATABASE() = 'temporary_two'
+1
+EXECUTE prepared_with_database;
+DATABASE() = 'temporary_two'
+1
+#
+# Prepared statements maintain default database to be the same
+# during on creation so this should return NULL still.
+# See MySQL bug #25843
+#
+EXECUTE prepared_no_database;
+DATABASE() = 'temporary_two'
+NULL
+DROP DATABASE temporary_two;
+DROP VIEW test.v_no_db;
+DROP VIEW test.v_with_db;
+USE test;
# -----------------------------------------------------------------
# -- End of 10.0 tests.
# -----------------------------------------------------------------
@@ -6080,6 +6142,24 @@ three COUNT(*)
drop view v1;
drop table t1;
#
+# MDEV-12819: order by ordering expression changed to empty string
+# when creatin view with union
+#
+create table t1 (t1col1 int, t1col2 int,t1col3 int );
+create table t2 (t2col1 int, t2col2 int, t2col3 int);
+create view v1 as
+select t1col1,t1col2,t1col3 from t1
+union all
+select t2col1,t2col2,t2col3 from t2
+order by 2,3;
+show create view v1;
+View Create View character_set_client collation_connection
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`t1col1` AS `t1col1`,`t1`.`t1col2` AS `t1col2`,`t1`.`t1col3` AS `t1col3` from `t1` union all select `t2`.`t2col1` AS `t2col1`,`t2`.`t2col2` AS `t2col2`,`t2`.`t2col3` AS `t2col3` from `t2` order by 2,3 latin1 latin1_swedish_ci
+select * from v1;
+t1col1 t1col2 t1col3
+drop view v1;
+drop table t1,t2;
+#
# End of 10.1 tests
#
#
View Lorry Controller Status