summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rwxr-xr-xmysql-test/mysql-test-run.pl5
-rw-r--r--mysql-test/r/binary_to_hex.result117
-rw-r--r--mysql-test/r/count_distinct.result12
-rw-r--r--mysql-test/r/derived_view.result59
-rw-r--r--mysql-test/r/group_by.result27
-rw-r--r--mysql-test/r/join_outer.result95
-rw-r--r--mysql-test/r/join_outer_jcl6.result95
-rw-r--r--mysql-test/r/limit_rows_examined.result5
-rw-r--r--mysql-test/r/loadxml.result16
-rw-r--r--mysql-test/r/mysql.result58
-rw-r--r--mysql-test/r/mysqltest.result5
-rw-r--r--mysql-test/r/read_only.result2
-rw-r--r--mysql-test/r/subselect.result30
-rw-r--r--mysql-test/r/subselect_mat.result88
-rw-r--r--mysql-test/r/subselect_mat_cost_bugs.result102
-rw-r--r--mysql-test/r/subselect_no_mat.result30
-rw-r--r--mysql-test/r/subselect_no_opts.result30
-rw-r--r--mysql-test/r/subselect_no_scache.result30
-rw-r--r--mysql-test/r/subselect_no_semijoin.result30
-rw-r--r--mysql-test/r/subselect_nulls.result6
-rw-r--r--mysql-test/r/subselect_sj.result95
-rw-r--r--mysql-test/r/subselect_sj2_mat.result45
-rw-r--r--mysql-test/r/subselect_sj_jcl6.result95
-rw-r--r--mysql-test/r/subselect_sj_mat.result88
-rw-r--r--mysql-test/r/union.result18
-rw-r--r--mysql-test/r/view.result14
-rw-r--r--mysql-test/std_data/loadxml.dat8
-rw-r--r--mysql-test/suite/parts/r/longname.result33
-rw-r--r--mysql-test/suite/parts/r/quoting.result6
-rw-r--r--mysql-test/suite/parts/t/longname.test29
-rw-r--r--mysql-test/suite/parts/t/quoting.test10
-rw-r--r--mysql-test/suite/perfschema/include/have_timer_cycle.inc4
-rw-r--r--mysql-test/suite/perfschema/r/privilege.result8
-rw-r--r--mysql-test/suite/perfschema/t/dml_performance_timers.test1
-rw-r--r--mysql-test/suite/perfschema/t/dml_setup_timers.test1
-rw-r--r--mysql-test/suite/perfschema/t/privilege.test14
-rw-r--r--mysql-test/suite/rpl/r/rpl_mdev-11092.result17
-rw-r--r--mysql-test/suite/rpl/r/rpl_reset_slave_fail.result29
-rw-r--r--mysql-test/suite/rpl/t/rpl_mdev-11092.opt1
-rw-r--r--mysql-test/suite/rpl/t/rpl_mdev-11092.test53
-rw-r--r--mysql-test/suite/rpl/t/rpl_reset_slave_fail.test91
-rw-r--r--mysql-test/t/binary_to_hex.test76
-rw-r--r--mysql-test/t/bootstrap.test6
-rw-r--r--mysql-test/t/count_distinct.test16
-rw-r--r--mysql-test/t/derived_view.test50
-rw-r--r--mysql-test/t/group_by.test38
-rw-r--r--mysql-test/t/join_outer.test82
-rw-r--r--mysql-test/t/mysql.test30
-rw-r--r--mysql-test/t/mysqltest.test8
-rw-r--r--mysql-test/t/read_only.test3
-rw-r--r--mysql-test/t/subselect.test35
-rw-r--r--mysql-test/t/subselect_mat_cost_bugs.test99
-rw-r--r--mysql-test/t/subselect_nulls.test6
-rw-r--r--mysql-test/t/subselect_sj.test72
-rw-r--r--mysql-test/t/subselect_sj2_mat.test40
-rw-r--r--mysql-test/t/subselect_sj_mat.test207
-rw-r--r--mysql-test/t/union.test15
-rw-r--r--mysql-test/t/view.test16
58 files changed, 2276 insertions, 25 deletions
diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl
index 22ccb7447fc..197127b56ab 100755
--- a/mysql-test/mysql-test-run.pl
+++ b/mysql-test/mysql-test-run.pl
@@ -2,7 +2,7 @@
# -*- cperl -*-
# Copyright (c) 2004, 2014, Oracle and/or its affiliates.
-# Copyright (c) 2009, 2014, Monty Program Ab
+# Copyright (c) 2009, 2017, MariaDB Corporation
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
@@ -6194,7 +6194,8 @@ sub valgrind_arguments {
mtr_add_arg($args, "--num-callers=16");
mtr_add_arg($args, "--suppressions=%s/valgrind.supp", $glob_mysql_test_dir)
if -f "$glob_mysql_test_dir/valgrind.supp";
- my $temp= `ldd $ENV{MTR_BINDIR}/sql/mysqld | grep 'libjemalloc'`;
+ my $exe_mysqld= find_mysqld($bindir) || "";
+ my $temp= `ldd $exe_mysqld | grep 'libjemalloc'`;
if ($temp)
{
mtr_add_arg($args, "--soname-synonyms=somalloc=libjemalloc*");
diff --git a/mysql-test/r/binary_to_hex.result b/mysql-test/r/binary_to_hex.result
new file mode 100644
index 00000000000..a6b68834da8
--- /dev/null
+++ b/mysql-test/r/binary_to_hex.result
@@ -0,0 +1,117 @@
+USE test;
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (c1 TINYBLOB,
+c2 BLOB,
+c3 MEDIUMBLOB,
+c4 LONGBLOB,
+c5 TEXT,
+c6 BIT(1),
+c7 CHAR,
+c8 VARCHAR(10),
+c9 GEOMETRY) CHARACTER SET = binary;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` tinyblob,
+ `c2` blob,
+ `c3` mediumblob,
+ `c4` longblob,
+ `c5` blob,
+ `c6` bit(1) DEFAULT NULL,
+ `c7` binary(1) DEFAULT NULL,
+ `c8` varbinary(10) DEFAULT NULL,
+ `c9` geometry DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=binary
+INSERT INTO t1 VALUES ('tinyblob-text readable', 'blob-text readable',
+'mediumblob-text readable', 'longblob-text readable',
+'text readable', b'1', 'c', 'variable',
+POINT(1, 1));
+CREATE TABLE t2(id int, `col1` binary(10),`col2` blob);
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `id` int(11) DEFAULT NULL,
+ `col1` binary(10) DEFAULT NULL,
+ `col2` blob
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t2 VALUES (1, X'AB1234', X'123ABC'), (2, X'DE1234', X'123DEF');
+#Print the table contents when binary-as-hex option is off.
+SELECT * FROM t1;
+c1 c2 c3 c4 c5 c6 c7 c8 c9
+tinyblob-text readable blob-text readable mediumblob-text readable longblob-text readable text readable # c variable #
+SELECT * FROM t2;
+id col1 col2
+1 # #
+2 # #
+#Print the table contents after turning on the binary-as-hex option
+
+#Print the table contents in tab format
+
+c1 c2 c3 c4 c5 c6 c7 c8 c9
+0x74696E79626C6F622D74657874207265616461626C65 0x626C6F622D74657874207265616461626C65 0x6D656469756D626C6F622D74657874207265616461626C65 0x6C6F6E67626C6F622D74657874207265616461626C65 0x74657874207265616461626C65 0x01 0x63 0x7661726961626C65 0x000000000101000000000000000000F03F000000000000F03F
+id col1 col2
+1 0xAB123400000000000000 0x123ABC
+2 0xDE123400000000000000 0x123DEF
+
+#Print the table contents in table format
+
++------------------------------------------------+----------------------------------------+----------------------------------------------------+------------------------------------------------+------------------------------+------------+------------+--------------------+------------------------------------------------------+
+| c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 |
++------------------------------------------------+----------------------------------------+----------------------------------------------------+------------------------------------------------+------------------------------+------------+------------+--------------------+------------------------------------------------------+
+| 0x74696E79626C6F622D74657874207265616461626C65 | 0x626C6F622D74657874207265616461626C65 | 0x6D656469756D626C6F622D74657874207265616461626C65 | 0x6C6F6E67626C6F622D74657874207265616461626C65 | 0x74657874207265616461626C65 | 0x01 | 0x63 | 0x7661726961626C65 | 0x000000000101000000000000000000F03F000000000000F03F |
++------------------------------------------------+----------------------------------------+----------------------------------------------------+------------------------------------------------+------------------------------+------------+------------+--------------------+------------------------------------------------------+
++------+------------------------+------------+
+| id | col1 | col2 |
++------+------------------------+------------+
+| 1 | 0xAB123400000000000000 | 0x123ABC |
++------+------------------------+------------+
+
+#Print the table contents vertically
+
+*************************** 1. row ***************************
+c1: 0x74696E79626C6F622D74657874207265616461626C65
+c2: 0x626C6F622D74657874207265616461626C65
+c3: 0x6D656469756D626C6F622D74657874207265616461626C65
+c4: 0x6C6F6E67626C6F622D74657874207265616461626C65
+c5: 0x74657874207265616461626C65
+c6: 0x01
+c7: 0x63
+c8: 0x7661726961626C65
+c9: 0x000000000101000000000000000000F03F000000000000F03F
+
+#Print the table contents in xml format
+
+<?xml version="1.0"?>
+
+<resultset statement="SELECT * FROM t1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
+ <row>
+ <field name="c1">0x74696E79626C6F622D74657874207265616461626C65</field>
+ <field name="c2">0x626C6F622D74657874207265616461626C65</field>
+ <field name="c3">0x6D656469756D626C6F622D74657874207265616461626C65</field>
+ <field name="c4">0x6C6F6E67626C6F622D74657874207265616461626C65</field>
+ <field name="c5">0x74657874207265616461626C65</field>
+ <field name="c6">0x01</field>
+ <field name="c7">0x63</field>
+ <field name="c8">0x7661726961626C65</field>
+ <field name="c9">0x000000000101000000000000000000F03F000000000000F03F</field>
+ </row>
+</resultset>
+<?xml version="1.0"?>
+
+<resultset statement="SELECT * FROM t2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
+ <row>
+ <field name="id">1</field>
+ <field name="col1">0xAB123400000000000000</field>
+ <field name="col2">0x123ABC</field>
+ </row>
+
+ <row>
+ <field name="id">2</field>
+ <field name="col1">0xDE123400000000000000</field>
+ <field name="col2">0x123DEF</field>
+ </row>
+</resultset>
+
+#Print the table contents in html format
+
+<TABLE BORDER=1><TR><TH>c1</TH><TH>c2</TH><TH>c3</TH><TH>c4</TH><TH>c5</TH><TH>c6</TH><TH>c7</TH><TH>c8</TH><TH>c9</TH></TR><TR><TD>0x74696E79626C6F622D74657874207265616461626C65</TD><TD>0x626C6F622D74657874207265616461626C65</TD><TD>0x6D656469756D626C6F622D74657874207265616461626C65</TD><TD>0x6C6F6E67626C6F622D74657874207265616461626C65</TD><TD>0x74657874207265616461626C65</TD><TD>0x01</TD><TD>0x63</TD><TD>0x7661726961626C65</TD><TD>0x000000000101000000000000000000F03F000000000000F03F</TD></TR></TABLE><TABLE BORDER=1><TR><TH>id</TH><TH>col1</TH><TH>col2</TH></TR><TR><TD>1</TD><TD>0xAB123400000000000000</TD><TD>0x123ABC</TD></TR><TR><TD>2</TD><TD>0xDE123400000000000000</TD><TD>0x123DEF</TD></TR></TABLE>DROP TABLE t1, t2;
diff --git a/mysql-test/r/count_distinct.result b/mysql-test/r/count_distinct.result
index 3b65dd0e608..d55a232c715 100644
--- a/mysql-test/r/count_distinct.result
+++ b/mysql-test/r/count_distinct.result
@@ -94,3 +94,15 @@ count(distinct i)
2
drop table t1;
drop view v1;
+create table t1 (user_id char(64) character set utf8);
+insert t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17);
+set @@tmp_table_size = 1024;
+select count(distinct user_id) from t1;
+count(distinct user_id)
+17
+alter table t1 modify user_id char(128) character set utf8;
+select count(distinct user_id) from t1;
+count(distinct user_id)
+17
+drop table t1;
+set @@tmp_table_size = default;
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index d993086299e..f7062473a3f 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2579,5 +2579,64 @@ Handler_read_rnd_deleted 0
Handler_read_rnd_next 27
deallocate prepare stmt1;
drop table t1,t2;
+#
+# Bug mdev-12670: mergeable derived / view with subqueries
+# subject to semi-join optimizations
+# (actually this is a 5.3 bug.)
+#
+create table t1 (a int) engine=myisam;
+insert into t1 values (5),(3),(2),(7),(2),(5),(1);
+create table t2 (b int, index idx(b)) engine=myisam;
+insert into t2 values (2),(3),(2),(1),(3),(4);
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+analyze table t1,t2;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+test.t2 analyze status OK
+explain select a from t1 where a in (select b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where
+1 PRIMARY t2 ref idx idx 5 test.t1.a 140 Using index; FirstMatch(t1)
+explain select * from (select a from t1 where a in (select b from t2)) t;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t2 ref idx idx 5 test.t1.a 140 Using index; FirstMatch(t1)
+create view v1 as select a from t1 where a in (select b from t2);
+explain select * from v1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t2 ref idx idx 5 test.t1.a 140 Using index; FirstMatch(t1)
+drop view v1;
+drop table t1,t2;
+#
+# Bug mdev-12812: mergeable derived / view with subqueries
+# NOT subject to semi-join optimizations
+#
+CREATE TABLE t1 (c1 varchar(3)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo'),('foo');
+CREATE TABLE t2 (c2 varchar(3)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('bar'),('qux'),('foo');
+SELECT STRAIGHT_JOIN *
+FROM ( SELECT * FROM t1 WHERE c1 IN ( SELECT c2 FROM t2 ) ) AS sq;
+c1
+foo
+foo
+EXPLAIN EXTENDED SELECT STRAIGHT_JOIN *
+FROM ( SELECT * FROM t1 WHERE c1 IN ( SELECT c2 FROM t2 ) ) AS sq;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select straight_join `test`.`t1`.`c1` AS `c1` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t2`.`c2` from `test`.`t2` where (<cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c2`)))
+DROP TABLE t1, t2;
set optimizer_switch=@exit_optimizer_switch;
set join_cache_level=@exit_join_cache_level;
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 262bb2ebd84..b948c9a6f88 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -2531,3 +2531,30 @@ select a from t1 group by a having a > 1;
a
drop table t1;
set sql_mode= @save_sql_mode;
+create table t1 (f1 int);
+insert into t1 values (5),(9);
+create table t2 (f2 int);
+insert into t2 values (0),(6);
+create table t3 (f3 int);
+insert into t3 values (6),(3);
+create table t4 (f4 int);
+insert into t4 values (1),(0);
+select
+(select min(f1) from t1 where f1 in (select min(f4) from t2)) as field7,
+(select count(*) from t3 where f3 in (select max(f4) from t2 group by field7))
+from t4;
+ERROR 42S22: Reference 'field7' not supported (reference to group function)
+drop table t1, t2, t3, t4;
+create table t1 (i1 int);
+insert into t1 values (1);
+create table t2 (i int);
+insert into t2 values (2);
+select 1 from t1 left join t2 b on b.i = (select max(b.i) from t2);
+1
+1
+drop table t1, t2;
+create table t1 (c1 int, c2 int);
+create table t2 (c1 int, c2 int);
+select t1.c1 as c1, t2.c2 as c1 from t1, t2 where t1.c1 < 20 and t2.c2 > 5 group by t1.c1, t2.c2 having t1.c1 < 3;
+c1 c1
+drop table t1, t2;
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 5dbb0f1d8b6..5611b61a7b0 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -2245,4 +2245,99 @@ SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a
WHERE b IN (1,2,3) OR b = d;
a b c d
DROP TABLE t1,t2,t3;
+#
+# MDEV-11958: LEFT JOIN with stored routine produces incorrect result
+#
+CREATE TABLE t (x INT);
+INSERT INTO t VALUES(1),(NULL);
+CREATE FUNCTION f (val INT, ret INT) RETURNS INT DETERMINISTIC RETURN IFNULL(val, ret);
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+FROM t t1 LEFT JOIN t t2
+ON t1.x = t2.x
+WHERE IFNULL(t2.x,0)=0;
+x x IFNULL(t2.x,0) f(t2.x,0)
+NULL NULL 0 0
+explain extended
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+FROM t t1 LEFT JOIN t t2
+ON t1.x = t2.x
+WHERE IFNULL(t2.x,0)=0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on((`test`.`t2`.`x` = `test`.`t1`.`x`)) where (ifnull(`test`.`t2`.`x`,0) = 0)
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+FROM t t1 LEFT JOIN t t2
+ON t1.x = t2.x
+WHERE f(t2.x,0)=0;
+x x IFNULL(t2.x,0) f(t2.x,0)
+NULL NULL 0 0
+explain extended
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+FROM t t1 LEFT JOIN t t2
+ON t1.x = t2.x
+WHERE f(t2.x,0)=0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on((`test`.`t2`.`x` = `test`.`t1`.`x`)) where (`f`(`test`.`t2`.`x`,0) = 0)
+drop function f;
+drop table t;
+CREATE TABLE t1 (
+col1 DECIMAL(33,5) NULL DEFAULT NULL,
+col2 DECIMAL(33,5) NULL DEFAULT NULL
+);
+CREATE TABLE t2 (
+col1 DECIMAL(33,5) NULL DEFAULT NULL,
+col2 DECIMAL(33,5) NULL DEFAULT NULL,
+col3 DECIMAL(33,5) NULL DEFAULT NULL
+);
+INSERT INTO t1 VALUES (2, 1.1), (2, 2.1);
+INSERT INTO t2 VALUES (3, 3.1, 4), (1, 1, NULL);
+CREATE FUNCTION f1 ( p_num DECIMAL(45,15), p_return DECIMAL(45,15))
+RETURNS decimal(33,5)
+LANGUAGE SQL
+DETERMINISTIC
+CONTAINS SQL
+SQL SECURITY INVOKER
+BEGIN
+IF p_num IS NULL THEN
+RETURN p_return;
+ELSE
+RETURN p_num;
+END IF;
+END |
+SELECT t1.col1, t2.col1, t2.col3
+FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
+WHERE IFNULL(t2.col3,0) = 0;
+col1 col1 col3
+2.00000 NULL NULL
+2.00000 NULL NULL
+EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3
+FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
+WHERE IFNULL(t2.col3,0) = 0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`col2` = `test`.`t1`.`col1`)) where (ifnull(`test`.`t2`.`col3`,0) = 0)
+SELECT t1.col1, t2.col1, t2.col3
+FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
+WHERE f1(t2.col3,0) = 0;
+col1 col1 col3
+2.00000 NULL NULL
+2.00000 NULL NULL
+EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3
+FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
+WHERE f1(t2.col3,0) = 0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`col2` = `test`.`t1`.`col1`)) where (`f1`(`test`.`t2`.`col3`,0) = 0)
+DROP FUNCTION f1;
+DROP TABLE t1,t2;
+# end of 5.5 tests
SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index bdc4be0cb8c..8a1ccc7d5e5 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -2256,6 +2256,101 @@ SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a
WHERE b IN (1,2,3) OR b = d;
a b c d
DROP TABLE t1,t2,t3;
+#
+# MDEV-11958: LEFT JOIN with stored routine produces incorrect result
+#
+CREATE TABLE t (x INT);
+INSERT INTO t VALUES(1),(NULL);
+CREATE FUNCTION f (val INT, ret INT) RETURNS INT DETERMINISTIC RETURN IFNULL(val, ret);
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+FROM t t1 LEFT JOIN t t2
+ON t1.x = t2.x
+WHERE IFNULL(t2.x,0)=0;
+x x IFNULL(t2.x,0) f(t2.x,0)
+NULL NULL 0 0
+explain extended
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+FROM t t1 LEFT JOIN t t2
+ON t1.x = t2.x
+WHERE IFNULL(t2.x,0)=0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on((`test`.`t2`.`x` = `test`.`t1`.`x`)) where (ifnull(`test`.`t2`.`x`,0) = 0)
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+FROM t t1 LEFT JOIN t t2
+ON t1.x = t2.x
+WHERE f(t2.x,0)=0;
+x x IFNULL(t2.x,0) f(t2.x,0)
+NULL NULL 0 0
+explain extended
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+FROM t t1 LEFT JOIN t t2
+ON t1.x = t2.x
+WHERE f(t2.x,0)=0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on((`test`.`t2`.`x` = `test`.`t1`.`x`)) where (`f`(`test`.`t2`.`x`,0) = 0)
+drop function f;
+drop table t;
+CREATE TABLE t1 (
+col1 DECIMAL(33,5) NULL DEFAULT NULL,
+col2 DECIMAL(33,5) NULL DEFAULT NULL
+);
+CREATE TABLE t2 (
+col1 DECIMAL(33,5) NULL DEFAULT NULL,
+col2 DECIMAL(33,5) NULL DEFAULT NULL,
+col3 DECIMAL(33,5) NULL DEFAULT NULL
+);
+INSERT INTO t1 VALUES (2, 1.1), (2, 2.1);
+INSERT INTO t2 VALUES (3, 3.1, 4), (1, 1, NULL);
+CREATE FUNCTION f1 ( p_num DECIMAL(45,15), p_return DECIMAL(45,15))
+RETURNS decimal(33,5)
+LANGUAGE SQL
+DETERMINISTIC
+CONTAINS SQL
+SQL SECURITY INVOKER
+BEGIN
+IF p_num IS NULL THEN
+RETURN p_return;
+ELSE
+RETURN p_num;
+END IF;
+END |
+SELECT t1.col1, t2.col1, t2.col3
+FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
+WHERE IFNULL(t2.col3,0) = 0;
+col1 col1 col3
+2.00000 NULL NULL
+2.00000 NULL NULL
+EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3
+FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
+WHERE IFNULL(t2.col3,0) = 0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`col2` = `test`.`t1`.`col1`)) where (ifnull(`test`.`t2`.`col3`,0) = 0)
+SELECT t1.col1, t2.col1, t2.col3
+FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
+WHERE f1(t2.col3,0) = 0;
+col1 col1 col3
+2.00000 NULL NULL
+2.00000 NULL NULL
+EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3
+FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
+WHERE f1(t2.col3,0) = 0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`col2` = `test`.`t1`.`col1`)) where (`f1`(`test`.`t2`.`col3`,0) = 0)
+DROP FUNCTION f1;
+DROP TABLE t1,t2;
+# end of 5.5 tests
SET optimizer_switch=@save_optimizer_switch;
set join_cache_level=default;
show variables like 'join_cache_level';
diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result
index a51798a5883..4e13e04f961 100644
--- a/mysql-test/r/limit_rows_examined.result
+++ b/mysql-test/r/limit_rows_examined.result
@@ -426,7 +426,7 @@ c1
bb
cc
Warnings:
-Warning 1931 Query execution was interrupted. The query examined at least 18 rows, which exceeds LIMIT ROWS EXAMINED (16). The query result may be incomplete.
+Warning 1931 Query execution was interrupted. The query examined at least 17 rows, which exceeds LIMIT ROWS EXAMINED (16). The query result may be incomplete.
select * from v1 LIMIT ROWS EXAMINED 11;
c1
bb
@@ -439,7 +439,8 @@ from (select * from t1
where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 0)) as tmp
LIMIT ROWS EXAMINED 11;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+1 SIMPLE <subquery3> eq_ref distinct_key distinct_key 2 func 1
3 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where
select *
from (select * from t1
diff --git a/mysql-test/r/loadxml.result b/mysql-test/r/loadxml.result
index 1128caf9122..250037f60b8 100644
--- a/mysql-test/r/loadxml.result
+++ b/mysql-test/r/loadxml.result
@@ -5,10 +5,10 @@ load xml infile '../../std_data/loadxml.dat' into table t1
rows identified by '<row>';
select * from t1 order by a;
a b
-1 b1
-2 b2
-3 b3
-11 b11
+1 b1
+2 b2
+3 b3
+11 b11
111 b111
112 b112 & < > " ' &unknown; -- check entities
212 b212
@@ -81,17 +81,17 @@ LOAD XML INFILE '../../std_data/loadxml.dat' INTO TABLE t1
ROWS IDENTIFIED BY '<row>' (a,@b) SET b=concat('!',@b);
SELECT * FROM t1 ORDER BY a;
a b
-1 !b1
-11 !b11
+1 ! b1
+11 ! b11
111 !b111
112 !b112 & < > " ' &unknown; -- check entities
-2 !b2
+2 ! b2
212 !b212
213 !b213
214 !b214
215 !b215
216 !&bb b;
-3 !b3
+3 ! b3
DROP TABLE t1;
#
# Bug#16171518 LOAD XML DOES NOT HANDLE EMPTY ELEMENTS
diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result
index dd0129df0d9..8a24128daa2 100644
--- a/mysql-test/r/mysql.result
+++ b/mysql-test/r/mysql.result
@@ -529,3 +529,61 @@ a
+-------------------+
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/mysqltest.result b/mysql-test/r/mysqltest.result
index 0ebef585974..9800aed6368 100644
--- a/mysql-test/r/mysqltest.result
+++ b/mysql-test/r/mysqltest.result
@@ -957,4 +957,9 @@ con1
con2
con2
-closed_connection-
+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/read_only.result b/mysql-test/r/read_only.result
index 1ffe2b86f70..807dc426696 100644
--- a/mysql-test/r/read_only.result
+++ b/mysql-test/r/read_only.result
@@ -30,6 +30,8 @@ create temporary table t3 (a int);
create temporary table t4 (a int) select * from t3;
insert into t3 values(1);
insert into t4 select * from t3;
+create table t3 (a int);
+ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a;
ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
update t1,t3 set t3.a=t1.a+1 where t1.a=t3.a;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 789cfe2fdca..8fc9cd38728 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -7116,3 +7116,33 @@ SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 );
f1 f2
foo bar
DROP TABLE t1;
+#
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM;
+INSERT t1 VALUES (4),(8);
+CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM;
+INSERT t2 VALUES (6),(9);
+SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
+(SELECT MAX(sq.f2) FROM t1)
+NULL
+drop table t1, t2;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index ffa37b025eb..d4dc519227b 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2272,6 +2272,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
set @subselect_mat_test_optimizer_switch_value=null;
set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index 24640154c59..57b0526c6a3 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -379,6 +379,7 @@ drop table t3, t4, t5;
#
# LP BUG#858038 The result of a query with NOT IN subquery depends on the state of the optimizer switch
#
+set @optimizer_switch_save= @@optimizer_switch;
create table t1 (c1 char(2) not null, c2 char(2));
create table t2 (c3 char(2), c4 char(2));
insert into t1 values ('a1', 'b1');
@@ -400,3 +401,104 @@ id select_type table type possible_keys key key_len ref rows Extra
select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
c1 c2
drop table t1, t2;
+set optimizer_switch= @optimizer_switch_save;
+#
+# MDEV-12673: cost-based choice between materialization and in-to-exists
+#
+CREATE TABLE t1 (
+pk1 int, a1 varchar(3), b1 varchar(3), PRIMARY KEY (pk1), KEY(a1), KEY(b1)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'foo','bar'),(2,'bar','foo');
+CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 VARCHAR(3), KEY(a2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'abc'),(2,'xyz'),(3,'foo');
+SELECT 'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 )
+0
+SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 )
+1
+EXPLAIN
+SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
+2 SUBQUERY t2 const PRIMARY,a2 PRIMARY 4 const 1
+2 SUBQUERY t1 ref a1,b1 b1 6 const 1 Using where
+DROP TABLE t1,t2;
+CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (i2 int, c2 varchar(3), KEY(i2,c2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'abc'),(2,'foo');
+CREATE TABLE t3 (pk3 int PRIMARY KEY, c3 varchar(3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1,'foo'),(2,'bar');
+SELECT * FROM t1 WHERE i1 NOT IN (
+SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1
+);
+i1
+1
+EXPLAIN
+SELECT * FROM t1 WHERE i1 NOT IN (
+SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY t3 const PRIMARY PRIMARY 4 const 1
+2 DEPENDENT SUBQUERY t2 index NULL i2 11 NULL 2 Using where; Using index
+DROP TABLE t1,t2,t3;
+#
+# MDEV-7599: in-to-exists chosen after min/max optimization
+#
+set @optimizer_switch_save= @@optimizer_switch;
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,6),(2,4), (8,9);
+SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b);
+b c
+EXPLAIN EXTENDED SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 MATERIALIZED t1 index NULL a 5 NULL 2 100.00 Using index
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (not(<expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,`test`.`t2`.`b` in ( <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`c` = `test`.`t2`.`b`) ), <primary_index_lookup>(`test`.`t2`.`b` in <temporary table> on distinct_key where ((`test`.`t2`.`b` = `<subquery2>`.`MIN(a)`))))))))
+set optimizer_switch= 'materialization=off';
+SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b);
+b c
+EXPLAIN EXTENDED SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 DEPENDENT SUBQUERY t1 index NULL a 5 NULL 2 100.00 Using index
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (not(<expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`c` = `test`.`t2`.`b`) having trigcond((<cache>(`test`.`t2`.`b`) = <ref_null_helper>(min(`test`.`t1`.`a`)))))))))
+set optimizer_switch= @optimizer_switch_save;
+DROP TABLE t1,t2;
+CREATE TABLE t1 (f1 varchar(10)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo'),('bar');
+CREATE TABLE t2 (f2 varchar(10), key(f2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('baz'),('qux');
+CREATE TABLE t3 (f3 varchar(10)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('abc'),('def');
+SELECT * FROM t1
+WHERE f1 = ALL( SELECT MAX(t2a.f2)
+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_no_mat.result b/mysql-test/r/subselect_no_mat.result
index c729c17f94f..a71df97e6bc 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -7113,6 +7113,36 @@ SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 );
f1 f2
foo bar
DROP TABLE t1;
+#
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM;
+INSERT t1 VALUES (4),(8);
+CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM;
+INSERT t2 VALUES (6),(9);
+SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
+(SELECT MAX(sq.f2) FROM t1)
+NULL
+drop table t1, t2;
set optimizer_switch=default;
select @@optimizer_switch like '%materialization=on%';
@@optimizer_switch like '%materialization=on%'
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index dc308ea77e5..2f8c67fa167 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -7111,4 +7111,34 @@ SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 );
f1 f2
foo bar
DROP TABLE t1;
+#
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM;
+INSERT t1 VALUES (4),(8);
+CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM;
+INSERT t2 VALUES (6),(9);
+SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
+(SELECT MAX(sq.f2) FROM t1)
+NULL
+drop table t1, t2;
set @optimizer_switch_for_subselect_test=null;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index e7c85c10f2d..8dda0a4fd36 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -7122,6 +7122,36 @@ SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 );
f1 f2
foo bar
DROP TABLE t1;
+#
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM;
+INSERT t1 VALUES (4),(8);
+CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM;
+INSERT t2 VALUES (6),(9);
+SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
+(SELECT MAX(sq.f2) FROM t1)
+NULL
+drop table t1, t2;
set optimizer_switch=default;
select @@optimizer_switch like '%subquery_cache=on%';
@@optimizer_switch like '%subquery_cache=on%'
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index b6261f05098..339e2b89786 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -7111,5 +7111,35 @@ SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 );
f1 f2
foo bar
DROP TABLE t1;
+#
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM;
+INSERT t1 VALUES (4),(8);
+CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM;
+INSERT t2 VALUES (6),(9);
+SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
+(SELECT MAX(sq.f2) FROM t1)
+NULL
+drop table t1, t2;
set @optimizer_switch_for_subselect_test=null;
set @join_cache_level_for_subselect_test=NULL;
diff --git a/mysql-test/r/subselect_nulls.result b/mysql-test/r/subselect_nulls.result
index 584c184870d..08982371269 100644
--- a/mysql-test/r/subselect_nulls.result
+++ b/mysql-test/r/subselect_nulls.result
@@ -115,3 +115,9 @@ k d1 d2
set optimizer_switch= @tmp_subselect_nulls;
drop table x1;
drop table x2;
+select (select 1, 2) in (select 3, 4);
+(select 1, 2) in (select 3, 4)
+0
+select (select NULL, NULL) in (select 3, 4);
+(select NULL, NULL) in (select 3, 4)
+NULL
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 50a70a6614a..74384141998 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -1650,9 +1650,9 @@ CREATE VIEW v1 AS SELECT 1;
EXPLAIN
SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1);
id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived3> system NULL NULL NULL NULL 1
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
-2 MATERIALIZED <derived3> system NULL NULL NULL NULL 1
3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1);
a a
@@ -3060,4 +3060,97 @@ project_number
aaa
drop table t1, t2, t3;
set optimizer_switch= @tmp_mdev6859;
+#
+# MDEV-12675: subquery subject to semi-join optimizations
+# in ON expression of INNER JOIN
+#
+set @tmp_mdev12675=@@optimizer_switch;
+set optimizer_switch=default;
+create table t1 (a int) engine=myisam;
+insert into t1 values (5),(3),(2),(7),(2),(5),(1);
+create table t2 (b int, index idx(b)) engine=myisam;
+insert into t2 values (2),(3),(2),(1),(3),(4);
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+analyze table t1,t2;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+test.t2 analyze status OK
+explain
+select a from t1, t2 where b between 1 and 2 and a in (select b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where
+1 PRIMARY t2 ref idx idx 5 test.t1.a 256 Using index; FirstMatch(t1)
+1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
+explain
+select a from t1 join t2 on b between 1 and 2 and a in (select b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where
+1 PRIMARY t2 ref idx idx 5 test.t1.a 256 Using index; FirstMatch(t1)
+1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
+drop table t1,t2;
+set optimizer_switch= @tmp_mdev12675;
+#
+# MDEV-12817: subquery NOT subject to semi-join optimizations
+# in ON expression of INNER JOIN
+#
+CREATE TABLE t1 (c1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c2 int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3),(4);
+CREATE TABLE t3 (c3 int) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (5),(6);
+CREATE TABLE t4 (c4 int) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (7),(8);
+SELECT c1
+FROM t1
+LEFT JOIN
+( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) )
+ON (c1 = c3);
+c1
+1
+2
+EXPLAIN EXTENDED SELECT c1
+FROM t1
+LEFT JOIN
+( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) )
+ON (c1 = c3);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
+2 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and <cache>(<in_optimizer>(1,<exists>(select `test`.`t4`.`c4` from `test`.`t4` where (1 = `test`.`t4`.`c4`)))))) where 1
+# mdev-12820
+SELECT *
+FROM t1
+LEFT JOIN
+( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 )
+ON (c1 = c2);
+c1 c2 c4
+1 NULL NULL
+2 NULL NULL
+EXPLAIN EXTENDED SELECT *
+FROM t1
+LEFT JOIN
+( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 )
+ON (c1 = c2);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00
+3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t4`.`c4` AS `c4` from `test`.`t1` left join (`test`.`t2` join `test`.`t4`) on(((`test`.`t2`.`c2` = `test`.`t1`.`c1`) and <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t3`.`c3` from `test`.`t3` where (<cache>(`test`.`t2`.`c2`) = `test`.`t3`.`c3`))))) where 1
+DROP TABLE t1,t2,t3,t4;
set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index 4e75aee24a2..835742a3ff4 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -1556,3 +1556,48 @@ i1
DROP TABLE t1,t2,t3;
set join_cache_level= @save_join_cache_level;
set optimizer_switch=@save_optimizer_switch;
+#
+# mdev-7791: materialization of a semi-join subquery +
+# RAND() in WHERE
+# (materialized table is accessed last)
+#
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='materialization=on';
+create table t1(i int);
+insert into t1 values (1), (2), (3), (7), (9), (10);
+create table t2(i int);
+insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+select * from t1 where (rand() < 0) and i in (select i from t2);
+i
+explain extended
+select * from t1 where (rand() < 0) and i in (select i from t2);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 10 100.00
+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 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_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index ccf348fa311..47dbdd782b5 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -1663,9 +1663,9 @@ CREATE VIEW v1 AS SELECT 1;
EXPLAIN
SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1);
id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived3> system NULL NULL NULL NULL 1
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
-2 MATERIALIZED <derived3> system NULL NULL NULL NULL 1
3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1);
a a
@@ -3074,6 +3074,99 @@ project_number
aaa
drop table t1, t2, t3;
set optimizer_switch= @tmp_mdev6859;
+#
+# MDEV-12675: subquery subject to semi-join optimizations
+# in ON expression of INNER JOIN
+#
+set @tmp_mdev12675=@@optimizer_switch;
+set optimizer_switch=default;
+create table t1 (a int) engine=myisam;
+insert into t1 values (5),(3),(2),(7),(2),(5),(1);
+create table t2 (b int, index idx(b)) engine=myisam;
+insert into t2 values (2),(3),(2),(1),(3),(4);
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+analyze table t1,t2;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+test.t2 analyze status OK
+explain
+select a from t1, t2 where b between 1 and 2 and a in (select b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where
+1 PRIMARY t2 ref idx idx 5 test.t1.a 256 Using index; FirstMatch(t1)
+1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
+explain
+select a from t1 join t2 on b between 1 and 2 and a in (select b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where
+1 PRIMARY t2 ref idx idx 5 test.t1.a 256 Using index; FirstMatch(t1)
+1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
+drop table t1,t2;
+set optimizer_switch= @tmp_mdev12675;
+#
+# MDEV-12817: subquery NOT subject to semi-join optimizations
+# in ON expression of INNER JOIN
+#
+CREATE TABLE t1 (c1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c2 int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3),(4);
+CREATE TABLE t3 (c3 int) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (5),(6);
+CREATE TABLE t4 (c4 int) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (7),(8);
+SELECT c1
+FROM t1
+LEFT JOIN
+( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) )
+ON (c1 = c3);
+c1
+1
+2
+EXPLAIN EXTENDED SELECT c1
+FROM t1
+LEFT JOIN
+( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) )
+ON (c1 = c3);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)
+2 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and <cache>(<in_optimizer>(1,<exists>(select `test`.`t4`.`c4` from `test`.`t4` where (1 = `test`.`t4`.`c4`)))))) where 1
+# mdev-12820
+SELECT *
+FROM t1
+LEFT JOIN
+( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 )
+ON (c1 = c2);
+c1 c2 c4
+1 NULL NULL
+2 NULL NULL
+EXPLAIN EXTENDED SELECT *
+FROM t1
+LEFT JOIN
+( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 )
+ON (c1 = c2);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (incremental, BNL join)
+3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t4`.`c4` AS `c4` from `test`.`t1` left join (`test`.`t2` join `test`.`t4`) on(((`test`.`t2`.`c2` = `test`.`t1`.`c1`) and <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t3`.`c3` from `test`.`t3` where (<cache>(`test`.`t2`.`c2`) = `test`.`t3`.`c3`))))) where 1
+DROP TABLE t1,t2,t3,t4;
set optimizer_switch=@subselect_sj_tmp;
#
# BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 47f578fb589..cb5012a91c9 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2312,4 +2312,92 @@ 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
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 5a6cd8907e9..fe2339db471 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -1978,3 +1978,21 @@ d
2016-06-04 00:00:00
drop table t1;
End of 5.0 tests
+create table t1 (a int, b int);
+insert into t1 values (1,1),(2,2),(3,3);
+create table t2 (c varchar(30), d varchar(30));
+insert into t1 values ('1','1'),('2','2'),('4','4');
+create table t3 (e int, f int);
+insert into t3 values (1,1),(2,2),(31,31),(32,32);
+select e,f, (e , f) in (select e,b from t1 union select c,d from t2) as sub from t3;
+e f sub
+1 1 1
+2 2 1
+31 31 0
+32 32 0
+select avg(f), (e , f) in (select e,b from t1 union select c,d from t2) as sub from t3 group by sub;
+avg(f) sub
+31.5000 0
+1.5000 1
+drop table t1,t2,t3;
+End of 5.5 tests
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 414de5662f3..0f8e9999730 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -5618,6 +5618,20 @@ Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t3`.`f` = `test`.`t1`.`a`) and (`test`.`t2`.`d` = `test`.`t3`.`e`) and (`test`.`t1`.`a` is not null) and (`test`.`t1`.`a` is not null) and (`test`.`t1`.`b` is not null))) where (`test`.`t1`.`a` < 5)
drop view v1;
drop table t1,t2,t3;
+#
+# MDEV-11240: Server crashes in check_view_single_update or
+# Assertion `derived->table' failed in mysql_derived_merge_for_insert
+#
+CREATE TABLE t3 (a INT);
+CREATE ALGORITHM = MERGE VIEW v1 AS SELECT t2.a FROM t3 AS t1, t3 AS t2;
+CREATE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM v1;
+PREPARE stmt FROM 'REPLACE INTO v2 SELECT a FROM t3';
+EXECUTE stmt;
+ERROR HY000: Can not insert into join view 'test.v2' without fields list
+EXECUTE stmt;
+ERROR HY000: Can not insert into join view 'test.v2' without fields list
+drop view v1,v2;
+drop table t3;
# -----------------------------------------------------------------
# -- End of 5.5 tests.
# -----------------------------------------------------------------
diff --git a/mysql-test/std_data/loadxml.dat b/mysql-test/std_data/loadxml.dat
index b72fac9da74..d5ccd4a1b13 100644
--- a/mysql-test/std_data/loadxml.dat
+++ b/mysql-test/std_data/loadxml.dat
@@ -9,19 +9,19 @@
<table_data name="t1">
<row>
<field name="a">1</field>
- <field name="b">b1</field>
+ <field name="b"> b1</field>
</row>
<row>
<field name="a">2</field>
- <field name="b">b2</field>
+ <field name="b"> b2</field>
</row>
<row>
<field name="a">3</field>
- <field name="b">b3</field>
+ <field name="b"> b3</field>
</row>
<row>
<field name="a">11</field>
- <field name="b">b11</field>
+ <field name="b"> b11</field>
</row>
<!-- Check field values as tags -->
diff --git a/mysql-test/suite/parts/r/longname.result b/mysql-test/suite/parts/r/longname.result
new file mode 100644
index 00000000000..40097d60d15
--- /dev/null
+++ b/mysql-test/suite/parts/r/longname.result
@@ -0,0 +1,33 @@
+set names utf8;
+create database mysqltest1;
+CREATE TABLE mysqltest1.test_jfg_table_name_with_64_chars_123456789012345678901234567890 (
+id int(10) unsigned NOT NULL,
+id2 int(10) unsigned NOT NULL,
+PRIMARY KEY ( id, id2 )
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
+PARTITION BY RANGE ( id )
+SUBPARTITION BY HASH ( id2 )
+SUBPARTITIONS 2 (
+PARTITION test_jfg_partition_name_with_60_chars_1234567890123456789012 VALUES LESS THAN (1000) ENGINE = InnoDB,
+PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
+Warnings:
+Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
+Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
+Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
+Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
+Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
+Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
+Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
+Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
+CREATE TABLE mysqltest1.éééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééé (
+id int(10) unsigned NOT NULL,
+id2 int(10) unsigned NOT NULL,
+PRIMARY KEY ( id, id2 )
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
+PARTITION BY RANGE ( id )
+SUBPARTITION BY HASH ( id2 )
+SUBPARTITIONS 2 (
+PARTITION çççççççççççççççççççççççççççççççççççççççççççççççççççççççççççç VALUES LESS THAN (1000) ENGINE = InnoDB,
+PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
+ERROR HY000: The path specified for @0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@ is too long.
+drop database mysqltest1;
diff --git a/mysql-test/suite/parts/r/quoting.result b/mysql-test/suite/parts/r/quoting.result
new file mode 100644
index 00000000000..ba6a155e6ac
--- /dev/null
+++ b/mysql-test/suite/parts/r/quoting.result
@@ -0,0 +1,6 @@
+set sql_mode=ansi_quotes;
+create table t1 (i int) partition by range (i) (partition flush values less than maxvalue);
+set sql_mode=default;
+lock tables t1 read local;
+unlock tables;
+drop table t1;
diff --git a/mysql-test/suite/parts/t/longname.test b/mysql-test/suite/parts/t/longname.test
new file mode 100644
index 00000000000..feea5a790f2
--- /dev/null
+++ b/mysql-test/suite/parts/t/longname.test
@@ -0,0 +1,29 @@
+source include/have_innodb.inc;
+source include/have_partition.inc;
+set names utf8;
+
+create database mysqltest1;
+CREATE TABLE mysqltest1.test_jfg_table_name_with_64_chars_123456789012345678901234567890 (
+ id int(10) unsigned NOT NULL,
+ id2 int(10) unsigned NOT NULL,
+ PRIMARY KEY ( id, id2 )
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
+PARTITION BY RANGE ( id )
+ SUBPARTITION BY HASH ( id2 )
+ SUBPARTITIONS 2 (
+ PARTITION test_jfg_partition_name_with_60_chars_1234567890123456789012 VALUES LESS THAN (1000) ENGINE = InnoDB,
+ PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
+
+--error ER_PATH_LENGTH
+CREATE TABLE mysqltest1.éééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééé (
+ id int(10) unsigned NOT NULL,
+ id2 int(10) unsigned NOT NULL,
+ PRIMARY KEY ( id, id2 )
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
+PARTITION BY RANGE ( id )
+ SUBPARTITION BY HASH ( id2 )
+ SUBPARTITIONS 2 (
+ PARTITION çççççççççççççççççççççççççççççççççççççççççççççççççççççççççççç VALUES LESS THAN (1000) ENGINE = InnoDB,
+ PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
+
+drop database mysqltest1;
diff --git a/mysql-test/suite/parts/t/quoting.test b/mysql-test/suite/parts/t/quoting.test
new file mode 100644
index 00000000000..297896fd9cf
--- /dev/null
+++ b/mysql-test/suite/parts/t/quoting.test
@@ -0,0 +1,10 @@
+#
+# MDEV-13017 LOCK TABLE fails with irrelevant error while working with tables affected by ANSI_QUOTES
+#
+--source include/have_partition.inc
+set sql_mode=ansi_quotes;
+create table t1 (i int) partition by range (i) (partition flush values less than maxvalue);
+set sql_mode=default;
+lock tables t1 read local;
+unlock tables;
+drop table t1;
diff --git a/mysql-test/suite/perfschema/include/have_timer_cycle.inc b/mysql-test/suite/perfschema/include/have_timer_cycle.inc
new file mode 100644
index 00000000000..b801ea256d6
--- /dev/null
+++ b/mysql-test/suite/perfschema/include/have_timer_cycle.inc
@@ -0,0 +1,4 @@
+if (!`SELECT count(*) FROM performance_schema.performance_timers WHERE timer_name='CYCLE' AND timer_frequency IS NOT NULL`)
+{
+ Skip Need performance timer CYCLE;
+}
diff --git a/mysql-test/suite/perfschema/r/privilege.result b/mysql-test/suite/perfschema/r/privilege.result
index d1831c9c4be..61eade13bbf 100644
--- a/mysql-test/suite/perfschema/r/privilege.result
+++ b/mysql-test/suite/perfschema/r/privilege.result
@@ -554,7 +554,7 @@ ERROR 42000: DROP command denied to user 'pfs_user_4'@'localhost' for table 'eve
#
# Grant access to change tables with the root account
GRANT UPDATE ON performance_schema.setup_consumers TO pfs_user_4;
-GRANT UPDATE ON performance_schema.setup_timers TO pfs_user_4;
+GRANT UPDATE, SELECT ON performance_schema.setup_timers TO pfs_user_4;
GRANT UPDATE, SELECT ON performance_schema.setup_instruments TO pfs_user_4;
GRANT DROP ON performance_schema.events_waits_current TO pfs_user_4;
GRANT DROP ON performance_schema.events_waits_history TO pfs_user_4;
@@ -565,7 +565,7 @@ UPDATE performance_schema.setup_instruments SET enabled = 'YES'
WHERE name LIKE 'wait/synch/mutex/%'
OR name LIKE 'wait/synch/rwlock/%';
UPDATE performance_schema.setup_consumers SET enabled = 'YES';
-UPDATE performance_schema.setup_timers SET timer_name = 'TICK';
+UPDATE performance_schema.setup_timers SET timer_name = 'TICK' WHERE name <> "wait";
TRUNCATE TABLE performance_schema.events_waits_history_long;
TRUNCATE TABLE performance_schema.events_waits_history;
TRUNCATE TABLE performance_schema.events_waits_current;
@@ -575,4 +575,6 @@ DROP USER pfs_user_4;
flush privileges;
UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
UPDATE performance_schema.setup_consumers SET enabled = 'YES';
-UPDATE performance_schema.setup_timers SET timer_name = 'CYCLE';
+UPDATE performance_schema.setup_timers SET timer_name = 'MICROSECOND' where name="idle";
+UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="stage";
+UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="statement";
diff --git a/mysql-test/suite/perfschema/t/dml_performance_timers.test b/mysql-test/suite/perfschema/t/dml_performance_timers.test
index 2ec37fbe7e9..587c54144aa 100644
--- a/mysql-test/suite/perfschema/t/dml_performance_timers.test
+++ b/mysql-test/suite/perfschema/t/dml_performance_timers.test
@@ -2,6 +2,7 @@
--source include/not_embedded.inc
--source include/have_perfschema.inc
+--source include/have_timer_cycle.inc
--replace_column 2 <frequency> 3 <resolution> 4 <overhead>
select * from performance_schema.performance_timers;
diff --git a/mysql-test/suite/perfschema/t/dml_setup_timers.test b/mysql-test/suite/perfschema/t/dml_setup_timers.test
index 0956ae2cc95..6a6b813ab07 100644
--- a/mysql-test/suite/perfschema/t/dml_setup_timers.test
+++ b/mysql-test/suite/perfschema/t/dml_setup_timers.test
@@ -2,6 +2,7 @@
--source include/not_embedded.inc
--source include/have_perfschema.inc
+--source include/have_timer_cycle.inc
select * from performance_schema.setup_timers;
diff --git a/mysql-test/suite/perfschema/t/privilege.test b/mysql-test/suite/perfschema/t/privilege.test
index 3b4cd1a4d2b..47a68602fa4 100644
--- a/mysql-test/suite/perfschema/t/privilege.test
+++ b/mysql-test/suite/perfschema/t/privilege.test
@@ -291,7 +291,7 @@ TRUNCATE TABLE performance_schema.events_waits_current;
--echo # Grant access to change tables with the root account
GRANT UPDATE ON performance_schema.setup_consumers TO pfs_user_4;
-GRANT UPDATE ON performance_schema.setup_timers TO pfs_user_4;
+GRANT UPDATE, SELECT ON performance_schema.setup_timers TO pfs_user_4;
GRANT UPDATE, SELECT ON performance_schema.setup_instruments TO pfs_user_4;
GRANT DROP ON performance_schema.events_waits_current TO pfs_user_4;
GRANT DROP ON performance_schema.events_waits_history TO pfs_user_4;
@@ -308,7 +308,11 @@ WHERE name LIKE 'wait/synch/mutex/%'
UPDATE performance_schema.setup_consumers SET enabled = 'YES';
-UPDATE performance_schema.setup_timers SET timer_name = 'TICK';
+# We do not touch "wait", to avoid restoring it at the end of the test,
+# as its default value initialized at server startup is ambiguous:
+# it can be CYCLE or NANOSECOND depending on platform
+
+UPDATE performance_schema.setup_timers SET timer_name = 'TICK' WHERE name <> "wait";
TRUNCATE TABLE performance_schema.events_waits_history_long;
TRUNCATE TABLE performance_schema.events_waits_history;
@@ -323,5 +327,9 @@ DROP USER pfs_user_4;
flush privileges;
UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
UPDATE performance_schema.setup_consumers SET enabled = 'YES';
-UPDATE performance_schema.setup_timers SET timer_name = 'CYCLE';
+# Restore the default values for the timers that we changed.
+# Note, we did not touch "wait", see above.
+UPDATE performance_schema.setup_timers SET timer_name = 'MICROSECOND' where name="idle";
+UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="stage";
+UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="statement";
diff --git a/mysql-test/suite/rpl/r/rpl_mdev-11092.result b/mysql-test/suite/rpl/r/rpl_mdev-11092.result
new file mode 100644
index 00000000000..b2de9e5f573
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_mdev-11092.result
@@ -0,0 +1,17 @@
+include/master-slave.inc
+[connection master]
+call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
+call mtr.add_suppression("Slave SQL: The incident LOST_EVENTS occured on the master. .*");
+SET GLOBAL max_binlog_cache_size = 4096;
+SET GLOBAL binlog_cache_size = 4096;
+SET GLOBAL max_binlog_stmt_cache_size = 4096;
+SET GLOBAL binlog_stmt_cache_size = 4096;
+CREATE TABLE t1(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=MYISAM;
+ERROR HY000: Writing one row to the row-based binary log failed
+include/wait_for_slave_sql_error_and_skip.inc [errno=1590]
+SET GLOBAL max_binlog_cache_size= ORIGINAL_VALUE;
+SET GLOBAL binlog_cache_size= ORIGINAL_VALUE;
+SET GLOBAL max_binlog_stmt_cache_size= ORIGINAL_VALUE;
+SET GLOBAL binlog_stmt_cache_size= ORIGINAL_VALUE;
+DROP TABLE t1;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/r/rpl_reset_slave_fail.result b/mysql-test/suite/rpl/r/rpl_reset_slave_fail.result
new file mode 100644
index 00000000000..fdfec62058f
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_reset_slave_fail.result
@@ -0,0 +1,29 @@
+include/master-slave.inc
+[connection master]
+CREATE TABLE t1 (c1 INT);
+INSERT INTO t1 (c1) VALUES (1);
+include/stop_slave_sql.inc
+FLUSH LOGS;
+FLUSH LOGS;
+INSERT INTO t1 (c1) VALUES (2);
+include/sync_slave_io_with_master.inc
+call mtr.add_suppression("File '.*slave-relay-bin.");
+call mtr.add_suppression("Could not open log file");
+call mtr.add_suppression("Failed to open the relay log");
+call mtr.add_suppression("Failed to initialize the master info structure");
+include/rpl_stop_server.inc [server_number=2]
+# Removing file(s)
+include/rpl_start_server.inc [server_number=2]
+START SLAVE;
+ERROR HY000: Could not initialize master info structure; more error messages can be found in the MariaDB error log
+START SLAVE;
+ERROR HY000: Could not initialize master info structure; more error messages can be found in the MariaDB error log
+RESET SLAVE;
+DROP TABLE t1;
+START SLAVE UNTIL MASTER_LOG_FILE= 'MASTER_LOG_FILE', MASTER_LOG_POS= MASTER_LOG_POS;;
+include/wait_for_slave_sql_to_stop.inc
+include/stop_slave_io.inc
+include/start_slave.inc
+include/diff_tables.inc [master:t1, slave:t1]
+DROP TABLE t1;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_mdev-11092.opt b/mysql-test/suite/rpl/t/rpl_mdev-11092.opt
new file mode 100644
index 00000000000..7f1d270d29f
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_mdev-11092.opt
@@ -0,0 +1 @@
+--binlog_checksum=1 --binlog-annotate-row-events=1
diff --git a/mysql-test/suite/rpl/t/rpl_mdev-11092.test b/mysql-test/suite/rpl/t/rpl_mdev-11092.test
new file mode 100644
index 00000000000..c8b2b7f2ad1
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_mdev-11092.test
@@ -0,0 +1,53 @@
+--source include/have_innodb.inc
+--source include/master-slave.inc
+--source include/not_embedded.inc
+--source include/not_windows.inc
+--source include/have_binlog_format_row.inc
+
+########################################################################################
+call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
+call mtr.add_suppression("Slave SQL: The incident LOST_EVENTS occured on the master. .*");
+
+let $old_max_binlog_cache_size= query_get_value(SHOW VARIABLES LIKE "max_binlog_cache_size", Value, 1);
+let $old_binlog_cache_size= query_get_value(SHOW VARIABLES LIKE "binlog_cache_size", Value, 1);
+let $old_max_binlog_stmt_cache_size= query_get_value(SHOW VARIABLES LIKE "max_binlog_stmt_cache_size", Value, 1);
+let $old_binlog_stmt_cache_size= query_get_value(SHOW VARIABLES LIKE "binlog_stmt_cache_size", Value, 1);
+
+SET GLOBAL max_binlog_cache_size = 4096;
+SET GLOBAL binlog_cache_size = 4096;
+SET GLOBAL max_binlog_stmt_cache_size = 4096;
+SET GLOBAL binlog_stmt_cache_size = 4096;
+disconnect master;
+connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);
+
+CREATE TABLE t1(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=MYISAM;
+
+let $data = `select concat('"', repeat('a',2000), '"')`;
+
+connection master;
+
+--disable_query_log
+--error ER_BINLOG_ROW_LOGGING_FAILED
+eval INSERT INTO t1 (a, data) VALUES (2,
+ CONCAT($data, $data, $data, $data, $data, $data));
+--enable_query_log
+
+# Incident event
+# 1590=ER_SLAVE_INCIDENT
+--let $slave_sql_errno= 1590
+--source include/wait_for_slave_sql_error_and_skip.inc
+
+connection master;
+
+--replace_result $old_max_binlog_cache_size ORIGINAL_VALUE
+--eval SET GLOBAL max_binlog_cache_size= $old_max_binlog_cache_size
+--replace_result $old_binlog_cache_size ORIGINAL_VALUE
+--eval SET GLOBAL binlog_cache_size= $old_binlog_cache_size
+--replace_result $old_max_binlog_stmt_cache_size ORIGINAL_VALUE
+--eval SET GLOBAL max_binlog_stmt_cache_size= $old_max_binlog_stmt_cache_size
+--replace_result $old_binlog_stmt_cache_size ORIGINAL_VALUE
+--eval SET GLOBAL binlog_stmt_cache_size= $old_binlog_stmt_cache_size
+
+DROP TABLE t1;
+
+--source include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_reset_slave_fail.test b/mysql-test/suite/rpl/t/rpl_reset_slave_fail.test
new file mode 100644
index 00000000000..021dc76d50c
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_reset_slave_fail.test
@@ -0,0 +1,91 @@
+###############################################################################
+# Bug#24901077: RESET SLAVE ALL DOES NOT ALWAYS RESET SLAVE
+#
+# Problem:
+# =======
+# If you have a relay log index file that has ended up with
+# some relay log files that do not exists, then RESET SLAVE
+# ALL is not enough to get back to a clean state.
+###############################################################################
+# Remove all slave-relay-bin.0* files (do not remove slave-relay-bin.index)
+# During server restart rli initialization will fail as there are no
+# relay logs. In case of bug RESET SLAVE will not do the required clean up
+# as rli is not inited and subsequent START SLAVE will fail.
+# Disable "Warning 1612 Being purged log ./slave-relay-bin.0* was not found"
+# because it is different on Unix and Windows systems.
+
+--source include/have_binlog_format_mixed.inc
+--source include/master-slave.inc
+
+--connection master
+CREATE TABLE t1 (c1 INT);
+INSERT INTO t1 (c1) VALUES (1);
+--sync_slave_with_master
+
+--connection slave
+--source include/stop_slave_sql.inc
+--let $MYSQLD_SLAVE_DATADIR= `select @@datadir`
+
+--connection master
+# Generate more relay logs on slave.
+FLUSH LOGS;
+FLUSH LOGS;
+INSERT INTO t1 (c1) VALUES (2);
+
+--source include/sync_slave_io_with_master.inc
+call mtr.add_suppression("File '.*slave-relay-bin.");
+call mtr.add_suppression("Could not open log file");
+call mtr.add_suppression("Failed to open the relay log");
+call mtr.add_suppression("Failed to initialize the master info structure");
+
+# Stop slave
+--let $rpl_server_number= 2
+--source include/rpl_stop_server.inc
+
+# Delete file(s)
+--echo # Removing $remove_pattern file(s)
+--let $remove_pattern= slave-relay-bin.0*
+--remove_files_wildcard $MYSQLD_SLAVE_DATADIR $remove_pattern
+
+# Start slave
+--let $rpl_server_number= 2
+--source include/rpl_start_server.inc
+
+# Start slave must fail because of the removed file(s).
+--error ER_MASTER_INFO
+START SLAVE;
+
+# Try a second time, it must fail again.
+--error ER_MASTER_INFO
+START SLAVE;
+
+# Retrieve master executed position before reset slave.
+--let $master_exec_file= query_get_value("SHOW SLAVE STATUS", Relay_Master_Log_File, 1)
+--let $master_exec_pos= query_get_value("SHOW SLAVE STATUS", Exec_Master_Log_Pos, 1)
+
+# Reset slave.
+# Disable "Warning 1612 Being purged log ./slave-relay-bin.0* was not found"
+# because it is different on Unix and Windows systems.
+--disable_warnings
+RESET SLAVE;
+--enable_warnings
+DROP TABLE t1;
+--replace_result $master_exec_file MASTER_LOG_FILE $master_exec_pos MASTER_LOG_POS
+--eval START SLAVE UNTIL MASTER_LOG_FILE= '$master_exec_file', MASTER_LOG_POS= $master_exec_pos;
+--source include/wait_for_slave_sql_to_stop.inc
+--source include/stop_slave_io.inc
+
+# Start slave.
+--source include/start_slave.inc
+
+--connection master
+--sync_slave_with_master
+# Check consistency.
+--let $diff_tables= master:t1, slave:t1
+--source include/diff_tables.inc
+
+# Cleanup
+--connection master
+DROP TABLE t1;
+--sync_slave_with_master
+--source include/rpl_end.inc
diff --git a/mysql-test/t/binary_to_hex.test b/mysql-test/t/binary_to_hex.test
new file mode 100644
index 00000000000..8312a246d0c
--- /dev/null
+++ b/mysql-test/t/binary_to_hex.test
@@ -0,0 +1,76 @@
+# === Purpose ===
+# The purpose of this test case is to make
+# sure that the binary data in tables is printed
+# as hex when the option binary-as-hex is enabled.
+#
+# === Related bugs and/or worklogs ===
+# Bug #25340722 - PRINT BINARY DATA AS HEX IN THE MYSQL
+# CLIENT (CONTRIBUTION)
+#
+
+# Save the initial number of concurrent sessions
+--source include/count_sessions.inc
+--source include/not_embedded.inc
+
+USE test;
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+CREATE TABLE t1 (c1 TINYBLOB,
+ c2 BLOB,
+ c3 MEDIUMBLOB,
+ c4 LONGBLOB,
+ c5 TEXT,
+ c6 BIT(1),
+ c7 CHAR,
+ c8 VARCHAR(10),
+ c9 GEOMETRY) CHARACTER SET = binary;
+
+SHOW CREATE TABLE t1;
+
+INSERT INTO t1 VALUES ('tinyblob-text readable', 'blob-text readable',
+ 'mediumblob-text readable', 'longblob-text readable',
+ 'text readable', b'1', 'c', 'variable',
+ POINT(1, 1));
+
+CREATE TABLE t2(id int, `col1` binary(10),`col2` blob);
+
+SHOW CREATE TABLE t2;
+
+INSERT INTO t2 VALUES (1, X'AB1234', X'123ABC'), (2, X'DE1234', X'123DEF');
+
+--echo #Print the table contents when binary-as-hex option is off.
+--replace_column 6 # 9 #
+SELECT * FROM t1;
+
+--replace_column 2 # 3 #
+SELECT * FROM t2;
+
+--echo #Print the table contents after turning on the binary-as-hex option
+--echo
+--echo #Print the table contents in tab format
+--echo
+--exec $MYSQL test --binary-as-hex -e "SELECT * FROM t1; SELECT * FROM t2;"
+--echo
+--echo #Print the table contents in table format
+--echo
+--exec $MYSQL test --binary-as-hex --table -e "SELECT * FROM t1; SELECT * FROM t2 WHERE col2=0x123ABC;"
+--echo
+--echo #Print the table contents vertically
+--echo
+--exec $MYSQL test --binary-as-hex --vertical -e "SELECT * FROM t1;"
+--echo
+--echo #Print the table contents in xml format
+--echo
+--exec $MYSQL test --binary-as-hex --xml -e "SELECT * FROM t1; SELECT * FROM t2;"
+--echo
+--echo #Print the table contents in html format
+--echo
+--exec $MYSQL test --binary-as-hex --html -e "SELECT * FROM t1; SELECT * FROM t2;"
+
+#Cleanup
+DROP TABLE t1, t2;
+
+# Wait till all disconnects are completed
+ --source include/wait_until_count_sessions.inc
diff --git a/mysql-test/t/bootstrap.test b/mysql-test/t/bootstrap.test
index 405c24a6d74..2930d936830 100644
--- a/mysql-test/t/bootstrap.test
+++ b/mysql-test/t/bootstrap.test
@@ -60,4 +60,10 @@ SHOW VARIABLES LIKE 'have_innodb';
SELECT 'bug' as '' FROM INFORMATION_SCHEMA.ENGINES WHERE engine='innodb'
and SUPPORT='YES';
+#
+# MDEV-13063 Server crashes in intern_plugin_lock or assertion `plugin_ptr->ref_count == 1' fails in plugin_init
+#
+--error 1
+--exec $MYSQLD_BOOTSTRAP_CMD --myisam_recover_options=NONE
+
--echo End of 5.5 tests
diff --git a/mysql-test/t/count_distinct.test b/mysql-test/t/count_distinct.test
index 10b4ac6f0e7..a00574b6cba 100644
--- a/mysql-test/t/count_distinct.test
+++ b/mysql-test/t/count_distinct.test
@@ -107,3 +107,19 @@ create view v1 as select * from t1;
select count(distinct i) from v1;
drop table t1;
drop view v1;
+
+#
+# MDEV-12136 SELECT COUNT(DISTINCT) returns the wrong value when tmp_table_size is limited
+#
+create table t1 (user_id char(64) character set utf8);
+insert t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17);
+set @@tmp_table_size = 1024;
+select count(distinct user_id) from t1;
+alter table t1 modify user_id char(128) character set utf8;
+select count(distinct user_id) from t1;
+drop table t1;
+set @@tmp_table_size = default;
+
+#
+# End of 5.5 tests
+#
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index d017f847af9..07fbe4980a5 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -1881,6 +1881,56 @@ deallocate prepare stmt1;
drop table t1,t2;
+--echo #
+--echo # Bug mdev-12670: mergeable derived / view with subqueries
+--echo # subject to semi-join optimizations
+--echo # (actually this is a 5.3 bug.)
+--echo #
+
+create table t1 (a int) engine=myisam;
+insert into t1 values (5),(3),(2),(7),(2),(5),(1);
+create table t2 (b int, index idx(b)) engine=myisam;
+insert into t2 values (2),(3),(2),(1),(3),(4);
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+analyze table t1,t2;
+
+explain select a from t1 where a in (select b from t2);
+explain select * from (select a from t1 where a in (select b from t2)) t;
+create view v1 as select a from t1 where a in (select b from t2);
+explain select * from v1;
+
+drop view v1;
+drop table t1,t2;
+
+--echo #
+--echo # Bug mdev-12812: mergeable derived / view with subqueries
+--echo # NOT subject to semi-join optimizations
+--echo #
+
+CREATE TABLE t1 (c1 varchar(3)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo'),('foo');
+
+CREATE TABLE t2 (c2 varchar(3)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('bar'),('qux'),('foo');
+
+let $q=
+SELECT STRAIGHT_JOIN *
+ FROM ( SELECT * FROM t1 WHERE c1 IN ( SELECT c2 FROM t2 ) ) AS sq;
+
+eval $q;
+eval EXPLAIN EXTENDED $q;
+
+DROP TABLE t1, t2;
+
# The following command must be the last one the file
set optimizer_switch=@exit_optimizer_switch;
set join_cache_level=@exit_join_cache_level;
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 4162e9c67a1..80ecfea539b 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -1703,6 +1703,44 @@ select a as x from t1 group by x having x > 1;
select a from t1 group by a having a > 1;
drop table t1;
set sql_mode= @save_sql_mode;
+
+#
+# MDEV-7826 Server crashes in Item_subselect::enumerate_field_refs_processor
+#
+create table t1 (f1 int);
+insert into t1 values (5),(9);
+create table t2 (f2 int);
+insert into t2 values (0),(6);
+create table t3 (f3 int);
+insert into t3 values (6),(3);
+create table t4 (f4 int);
+insert into t4 values (1),(0);
+--error ER_ILLEGAL_REFERENCE
+select
+(select min(f1) from t1 where f1 in (select min(f4) from t2)) as field7,
+(select count(*) from t3 where f3 in (select max(f4) from t2 group by field7))
+from t4;
+drop table t1, t2, t3, t4;
+
+#
+# MDEV-13180 Unused left join causes server crash
+#
+create table t1 (i1 int);
+insert into t1 values (1);
+create table t2 (i int);
+insert into t2 values (2);
+select 1 from t1 left join t2 b on b.i = (select max(b.i) from t2);
+drop table t1, t2;
+
+
+#
+# MDEV-12489 The select stmt may fail due to "having clause is ambiguous" unexpected
+#
+create table t1 (c1 int, c2 int);
+create table t2 (c1 int, c2 int);
+select t1.c1 as c1, t2.c2 as c1 from t1, t2 where t1.c1 < 20 and t2.c2 > 5 group by t1.c1, t2.c2 having t1.c1 < 3;
+drop table t1, t2;
+
#
# End of MariaDB 5.5 tests
#
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 19c5f64de78..9645d8ad82a 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -1801,4 +1801,86 @@ SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a
DROP TABLE t1,t2,t3;
+--echo #
+--echo # MDEV-11958: LEFT JOIN with stored routine produces incorrect result
+--echo #
+
+CREATE TABLE t (x INT);
+INSERT INTO t VALUES(1),(NULL);
+CREATE FUNCTION f (val INT, ret INT) RETURNS INT DETERMINISTIC RETURN IFNULL(val, ret);
+
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+ FROM t t1 LEFT JOIN t t2
+ ON t1.x = t2.x
+ WHERE IFNULL(t2.x,0)=0;
+explain extended
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+ FROM t t1 LEFT JOIN t t2
+ ON t1.x = t2.x
+ WHERE IFNULL(t2.x,0)=0;
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+ FROM t t1 LEFT JOIN t t2
+ ON t1.x = t2.x
+ WHERE f(t2.x,0)=0;
+explain extended
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+ FROM t t1 LEFT JOIN t t2
+ ON t1.x = t2.x
+ WHERE f(t2.x,0)=0;
+
+drop function f;
+drop table t;
+CREATE TABLE t1 (
+ col1 DECIMAL(33,5) NULL DEFAULT NULL,
+ col2 DECIMAL(33,5) NULL DEFAULT NULL
+);
+
+CREATE TABLE t2 (
+ col1 DECIMAL(33,5) NULL DEFAULT NULL,
+ col2 DECIMAL(33,5) NULL DEFAULT NULL,
+ col3 DECIMAL(33,5) NULL DEFAULT NULL
+);
+
+INSERT INTO t1 VALUES (2, 1.1), (2, 2.1);
+INSERT INTO t2 VALUES (3, 3.1, 4), (1, 1, NULL);
+
+DELIMITER |;
+
+CREATE FUNCTION f1 ( p_num DECIMAL(45,15), p_return DECIMAL(45,15))
+RETURNS decimal(33,5)
+LANGUAGE SQL
+DETERMINISTIC
+CONTAINS SQL
+SQL SECURITY INVOKER
+BEGIN
+ IF p_num IS NULL THEN
+ RETURN p_return;
+ ELSE
+ RETURN p_num;
+ END IF;
+END |
+
+DELIMITER ;|
+
+let $q1=
+SELECT t1.col1, t2.col1, t2.col3
+FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
+WHERE IFNULL(t2.col3,0) = 0;
+
+eval $q1;
+eval EXPLAIN EXTENDED $q1;
+
+let $q2=
+SELECT t1.col1, t2.col1, t2.col3
+FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
+WHERE f1(t2.col3,0) = 0;
+eval $q2;
+eval EXPLAIN EXTENDED $q2;
+
+DROP FUNCTION f1;
+
+DROP TABLE t1,t2;
+
+--echo # end of 5.5 tests
+
SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test
index d59083d66b0..dd964c46420 100644
--- a/mysql-test/t/mysql.test
+++ b/mysql-test/t/mysql.test
@@ -618,3 +618,33 @@ EOF
--echo
--echo End of tests
+
+#
+# MDEV-13187 incorrect backslash parsing in clients
+#
+create table `a1\``b1` (a int);
+show tables;
+insert `a1\``b1` values (1),(2);
+show create table `a1\``b1`;
+--exec $MYSQL_DUMP --compact test
+--exec $MYSQL_DUMP test > $MYSQLTEST_VARDIR/tmp/bug.sql
+insert `a1\``b1` values (4),(5);
+--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug.sql
+show create table `a1\``b1`;
+select * from `a1\``b1`;
+drop table `a1\``b1`;
+
+# same with ansi_quotes
+set sql_mode=ansi_quotes;
+create table "a1\""b1" (a int);
+show tables;
+insert "a1\""b1" values (1),(2);
+show create table "a1\""b1";
+--exec $MYSQL_DUMP --compact --compatible=postgres test
+--exec $MYSQL_DUMP --compatible=postgres test > $MYSQLTEST_VARDIR/tmp/bug.sql
+insert "a1\""b1" values (4),(5);
+--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug.sql
+show create table "a1\""b1";
+select * from "a1\""b1";
+drop table "a1\""b1";
+set sql_mode=default;
diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test
index 6470ede4f14..afe37926591 100644
--- a/mysql-test/t/mysqltest.test
+++ b/mysql-test/t/mysqltest.test
@@ -2942,11 +2942,17 @@ disconnect $x;
# Disconnect the selected connection
disconnect $y;
--echo $CURRENT_CONNECTION
+connection default;
+#
+# MDEV-13187 incorrect backslash parsing in clients
+#
+set sql_mode=no_backslash_escapes;
+select "foo\""bar";
+set sql_mode=default;
--echo End of tests
-connection default;
# Wait till we reached the initial number of concurrent sessions
--source include/wait_until_count_sessions.inc
diff --git a/mysql-test/t/read_only.test b/mysql-test/t/read_only.test
index 52349747883..a0bd7b49273 100644
--- a/mysql-test/t/read_only.test
+++ b/mysql-test/t/read_only.test
@@ -80,6 +80,9 @@ insert into t3 values(1);
insert into t4 select * from t3;
+--error ER_OPTION_PREVENTS_STATEMENT
+create table t3 (a int);
+
# a non-temp table updated:
--error ER_OPTION_PREVENTS_STATEMENT
update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index a8ad3ba52a5..50d9517043a 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -5998,3 +5998,38 @@ INSERT INTO t1 VALUES ('foo','bar');
SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 );
SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 );
DROP TABLE t1;
+
+--echo #
+--echo # MDEV-10146: Wrong result (or questionable result and behavior)
+--echo # with aggregate function in uncorrelated SELECT subquery
+--echo #
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (f2 int);
+
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+
+INSERT INTO t2 VALUES (4);
+
+--error ER_SUBQUERY_NO_1_ROW
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+--error ER_SUBQUERY_NO_1_ROW
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+
+drop view v1;
+drop table t1,t2;
+
+#
+# MDEV-7828 Assertion `key_read == 0' failed in TABLE::enable_keyread with SELECT SQ and WHERE SQ
+#
+CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM;
+INSERT t1 VALUES (4),(8);
+CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM;
+INSERT t2 VALUES (6),(9);
+SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
+drop table t1, t2;
diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test
index 8205e94b203..35f2b9588fe 100644
--- a/mysql-test/t/subselect_mat_cost_bugs.test
+++ b/mysql-test/t/subselect_mat_cost_bugs.test
@@ -406,6 +406,8 @@ drop table t3, t4, t5;
--echo # LP BUG#858038 The result of a query with NOT IN subquery depends on the state of the optimizer switch
--echo #
+set @optimizer_switch_save= @@optimizer_switch;
+
create table t1 (c1 char(2) not null, c2 char(2));
create table t2 (c3 char(2), c4 char(2));
@@ -424,3 +426,100 @@ explain select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
drop table t1, t2;
+
+set optimizer_switch= @optimizer_switch_save;
+
+--echo #
+--echo # MDEV-12673: cost-based choice between materialization and in-to-exists
+--echo #
+
+CREATE TABLE t1 (
+ pk1 int, a1 varchar(3), b1 varchar(3), PRIMARY KEY (pk1), KEY(a1), KEY(b1)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'foo','bar'),(2,'bar','foo');
+
+CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 VARCHAR(3), KEY(a2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'abc'),(2,'xyz'),(3,'foo');
+
+SELECT 'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+EXPLAIN
+SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2 (i2 int, c2 varchar(3), KEY(i2,c2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'abc'),(2,'foo');
+
+CREATE TABLE t3 (pk3 int PRIMARY KEY, c3 varchar(3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1,'foo'),(2,'bar');
+
+SELECT * FROM t1 WHERE i1 NOT IN (
+ SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1
+);
+
+EXPLAIN
+SELECT * FROM t1 WHERE i1 NOT IN (
+ SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1
+);
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # MDEV-7599: in-to-exists chosen after min/max optimization
+--echo #
+
+set @optimizer_switch_save= @@optimizer_switch;
+
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,6),(2,4), (8,9);
+
+let $q=
+SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b);
+
+eval $q;
+eval EXPLAIN EXTENDED $q;
+set optimizer_switch= 'materialization=off';
+eval $q;
+eval EXPLAIN EXTENDED $q;
+set optimizer_switch= @optimizer_switch_save;
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (f1 varchar(10)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo'),('bar');
+
+CREATE TABLE t2 (f2 varchar(10), key(f2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('baz'),('qux');
+
+CREATE TABLE t3 (f3 varchar(10)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('abc'),('def');
+
+SELECT * FROM t1
+ WHERE f1 = ALL( SELECT MAX(t2a.f2)
+ FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3
+ ON (f3 = t2b.f2) );
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # MDEV-12963: min/max optimization optimizing away all tables employed
+--echo # for uncorrelated IN subquery used in a disjunct of WHERE
+--echo #
+
+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;
+explain
+select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5;
+
+drop table t1;
+
+
diff --git a/mysql-test/t/subselect_nulls.test b/mysql-test/t/subselect_nulls.test
index 4b08e773b17..3e7b2189ed5 100644
--- a/mysql-test/t/subselect_nulls.test
+++ b/mysql-test/t/subselect_nulls.test
@@ -97,3 +97,9 @@ set optimizer_switch= @tmp_subselect_nulls;
drop table x1;
drop table x2;
+
+#
+# MDEV-7339 Server crashes in Item_func_trig_cond::val_int
+#
+select (select 1, 2) in (select 3, 4);
+select (select NULL, NULL) in (select 3, 4);
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index 51bed53be17..acee1a67d63 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -2769,5 +2769,77 @@ WHERE ( SELECT z.country
drop table t1, t2, t3;
set optimizer_switch= @tmp_mdev6859;
+--echo #
+--echo # MDEV-12675: subquery subject to semi-join optimizations
+--echo # in ON expression of INNER JOIN
+--echo #
+
+set @tmp_mdev12675=@@optimizer_switch;
+set optimizer_switch=default;
+create table t1 (a int) engine=myisam;
+insert into t1 values (5),(3),(2),(7),(2),(5),(1);
+create table t2 (b int, index idx(b)) engine=myisam;
+insert into t2 values (2),(3),(2),(1),(3),(4);
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+analyze table t1,t2;
+
+explain
+select a from t1, t2 where b between 1 and 2 and a in (select b from t2);
+explain
+select a from t1 join t2 on b between 1 and 2 and a in (select b from t2);
+
+drop table t1,t2;
+set optimizer_switch= @tmp_mdev12675;
+
+--echo #
+--echo # MDEV-12817: subquery NOT subject to semi-join optimizations
+--echo # in ON expression of INNER JOIN
+--echo #
+
+CREATE TABLE t1 (c1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (c2 int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3),(4);
+
+CREATE TABLE t3 (c3 int) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (5),(6);
+
+CREATE TABLE t4 (c4 int) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (7),(8);
+
+let $q1=
+SELECT c1
+FROM t1
+LEFT JOIN
+( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) )
+ON (c1 = c3);
+
+eval $q1;
+eval EXPLAIN EXTENDED $q1;
+
+let $q2=
+SELECT *
+FROM t1
+LEFT JOIN
+( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 )
+ON (c1 = c2);
+
+--echo # mdev-12820
+eval $q2;
+eval EXPLAIN EXTENDED $q2;
+
+DROP TABLE t1,t2,t3,t4;
+
# The following command must be the last one the file
set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test
index 61d9b09edff..cfb6c8c2819 100644
--- a/mysql-test/t/subselect_sj2_mat.test
+++ b/mysql-test/t/subselect_sj2_mat.test
@@ -263,3 +263,43 @@ DROP TABLE t1,t2,t3;
set join_cache_level= @save_join_cache_level;
set optimizer_switch=@save_optimizer_switch;
+--echo #
+--echo # mdev-7791: materialization of a semi-join subquery +
+--echo # RAND() in WHERE
+--echo # (materialized table is accessed last)
+--echo #
+
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='materialization=on';
+
+create table t1(i int);
+insert into t1 values (1), (2), (3), (7), (9), (10);
+create table t2(i int);
+insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+
+select * from t1 where (rand() < 0) and i in (select i from t2);
+explain extended
+select * from t1 where (rand() < 0) and i in (select i from t2);
+
+drop table t1,t2;
+set optimizer_switch=@save_optimizer_switch;
+
+--echo #
+--echo # mdev-12855: materialization of a semi-join subquery + ORDER BY
+--echo #
+
+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');
+
+let $q=
+SELECT f1 FROM t1
+WHERE f1 IN ( SELECT f2 FROM t2 WHERE f2 > 'bar' )
+HAVING f1 != 'foo'
+ORDER BY f1;
+
+eval $q;
+eval explain $q;
+
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index cd71ae5c901..90f63bea561 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1944,4 +1944,211 @@ set optimizer_switch= @save_optimizer_switch;
DROP TABLE t1,t2;
+--echo #
+--echo # mdev-12838: scan of materialized of semi-join subquery in join
+--echo #
+
+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;
+
+--disable_query_log
+
+INSERT INTO t1 VALUES
+('e5d9f63237232000158bbfc8bcbe5dbf','f304ae0037332000158bbfc8bcbe5d4f',
+'5398c0e037003000158bbfc8bcbe5dbb'),
+('69d9f63237232000158bbfc8bcbe5dcb','7172ea0037332000158bbfc8bcbe5db6',
+'5c188ca037003000158bbfc8bcbe5dbc'),
+('577ed708d773020058c92cf65e61037a','699708d4d773020058c92cf65e61037c',
+'623a8cd4d773020058c92cf65e6103ea'),
+('96fb652637232000158bbfc8bcbe5db4','df50316637232000158bbfc8bcbe5d23',
+'6835bd6637232000158bbfc8bcbe5d21'),
+('e1d9f63237232000158bbfc8bcbe5db8','96346e0037332000158bbfc8bcbe5daa',
+'697880e037003000158bbfc8bcbe5dcd'),
+('25d9f63237232000158bbfc8bcbe5dbe','f304ae0037332000158bbfc8bcbe5d4f',
+'6a9804e037003000158bbfc8bcbe5d09'),
+('96fb652637232000158bbfc8bcbe5db4','e08fad2637232000158bbfc8bcbe5d39',
+'6d25f96637232000158bbfc8bcbe5d79'),
+('e9d9f63237232000158bbfc8bcbe5dc6','7172ea0037332000158bbfc8bcbe5db6',
+'702880e037003000158bbfc8bcbe5d94'),
+('a5d9f63237232000158bbfc8bcbe5dca','f304ae0037332000158bbfc8bcbe5d4f',
+'7188c0e037003000158bbfc8bcbe5d75'),
+('65d9f63237232000158bbfc8bcbe5dc4','f304ae0037332000158bbfc8bcbe5d4f',
+'778880e037003000158bbfc8bcbe5d9e'),
+('a1d9f63237232000158bbfc8bcbe5dc3','7172ea0037332000158bbfc8bcbe5db6',
+'7d0840e037003000158bbfc8bcbe5dde'),
+('21d9f63237232000158bbfc8bcbe5db7','96346e0037332000158bbfc8bcbe5daa',
+'7f6880e037003000158bbfc8bcbe5da7'),
+('96fb652637232000158bbfc8bcbe5db4','ec70316637232000158bbfc8bcbe5d60',
+'8025f96637232000158bbfc8bcbe5dd0'),
+('3dd9f63237232000158bbfc8bcbe5dcc','7172ea0037332000158bbfc8bcbe5db6',
+'823880e037003000158bbfc8bcbe5ded'),
+('96fb652637232000158bbfc8bcbe5db4','7b10fd2637232000158bbfc8bcbe5d30',
+'9a353d6637232000158bbfc8bcbe5dee'),
+('75d9f63237232000158bbfc8bcbe5dd0','ebb4620037332000158bbfc8bcbe5d89',
+'a558c0e037003000158bbfc8bcbe5d36'),
+('6dd9f63237232000158bbfc8bcbe5db5','96346e0037332000158bbfc8bcbe5daa',
+'bc78cca037003000158bbfc8bcbe5d74'),
+('add9f63237232000158bbfc8bcbe5dc7','7172ea0037332000158bbfc8bcbe5db6',
+'c53804a037003000158bbfc8bcbe5db8'),
+('fdd9f63237232000158bbfc8bcbe5dcd','7864ae0037332000158bbfc8bcbe5db8',
+'cfe740e037003000158bbfc8bcbe5de8'),
+('96fb652637232000158bbfc8bcbe5db4','3120fd2637232000158bbfc8bcbe5d42',
+'e2257d6637232000158bbfc8bcbe5ded'),
+('3c3725e237232000158bbfc8bcbe5da1','96346e0037332000158bbfc8bcbe5daa',
+'ee78c0e037003000158bbfc8bcbe5db5'),
+('a9d9f63237232000158bbfc8bcbe5dc0','7172ea0037332000158bbfc8bcbe5db6',
+'f00888a037003000158bbfc8bcbe5dd3'),
+('29d9f63237232000158bbfc8bcbe5db9','7172ea0037332000158bbfc8bcbe5db6',
+'fa0880e037003000158bbfc8bcbe5d70'),
+('b1d9f63237232000158bbfc8bcbe5dcf','ebb4620037332000158bbfc8bcbe5d89',
+'fa48c0e037003000158bbfc8bcbe5d28');
+
+INSERT INTO t2 VALUES
+('17801ac21b13200050fdfbcd2c0713e8','8e826bf03710200044e0bfc8bcbe5d86',
+'14c19a061b13200050fdfbcd2c07134b'),
+('577ed708d773020058c92cf65e61037a','931644d4d773020058c92cf65e61034c',
+'339888d4d773020058c92cf65e6103aa'),
+('df50316637232000158bbfc8bcbe5d23','92826bf03710200044e0bfc8bcbe5da9',
+'3682f56637232000158bbfc8bcbe5d44'),
+('b4f342b237232000158bbfc8bcbe5def','86826bf03710200044e0bfc8bcbe5d70',
+'38e4c2b237232000158bbfc8bcbe5dea'),
+('7b10fd2637232000158bbfc8bcbe5d30','8a826bf03710200044e0bfc8bcbe5d72',
+'4442b56637232000158bbfc8bcbe5d43'),
+('3120fd2637232000158bbfc8bcbe5d42','82826bf03710200044e0bfc8bcbe5d89',
+'49d2396637232000158bbfc8bcbe5d12'),
+('96fb652637232000158bbfc8bcbe5db4','86826bf03710200044e0bfc8bcbe5d79',
+'4e3ca52637232000158bbfc8bcbe5d3e'),
+('17801ac21b13200050fdfbcd2c0713e8','824fd523bf4320007a6d257b3f073963',
+'58c19a061b13200050fdfbcd2c07134e'),
+('699708d4d773020058c92cf65e61037c','901784d4d773020058c92cf65e6103da',
+'5bc708d4d773020058c92cf65e6103d5'),
+('75d9f63237232000158bbfc8bcbe5dd0','86826bf03710200044e0bfc8bcbe5d79',
+'6b52cb7237232000158bbfc8bcbe5ded'),
+('f253da061b13200050fdfbcd2c0713ab','8e826bf03710200044e0bfc8bcbe5d86',
+'81045e061b13200050fdfbcd2c071373'),
+('7b10fd2637232000158bbfc8bcbe5d30','8e826bf03710200044e0bfc8bcbe5d74',
+'8c42b56637232000158bbfc8bcbe5d3f'),
+('e5d9f63237232000158bbfc8bcbe5dbf','7a826bf03710200044e0bfc8bcbe5df5',
+'a7acfe3237232000158bbfc8bcbe5d78'),
+('8a5055c9c61122780043563ef53438e3','9ee1b13dc6112271007f9d0efdb69cd0',
+'a9aff553c6112276015a8006174bee21'),
+('8a4dde73c6112278017a6a4baf547aa7','9ee1b13dc6112271007f9d0efdb69cd0',
+'a9b2f526c61122760003ae07349d294f'),
+('aaccc971c0a8001500fe1ff4302de101','9ee1b13dc6112271007f9d0efdb69cd0',
+'aacceed3c0a80015009069bba51c4e21'),
+('65d9f63237232000158bbfc8bcbe5dc4','8d56406a0a0a0a6b004070b354aada28',
+'ac1bfa3237232000158bbfc8bcbe5dc3'),
+('b85d44954a3623120004689b2d5dd60a','97000fcc0a0a0a6e0104ca999f619e5b',
+'b77bc032cbb00200d71cb9c0c24c9c45'),
+('220f8e71c61122840197e57c33464f70','8d56406a0a0a0a6b004070b354aada28',
+'b9b74f080a0a0b343ba75b95bdb27056'),
+('e08fad2637232000158bbfc8bcbe5d39','82826bf03710200044e0bfc8bcbe5d80',
+'be02756637232000158bbfc8bcbe5d8b'),
+('ebb4620037332000158bbfc8bcbe5d89','7682abf03710200044e0bfc8bcbe5d25',
+'c0122f4437732000158bbfc8bcbe5d7d'),
+('96fb652637232000158bbfc8bcbe5db4','7a82abf03710200044e0bfc8bcbe5d27',
+'c23ca52637232000158bbfc8bcbe5d3b'),
+('22122b37c611228400f9ff91c857581d','9ee1b13dc6112271007f9d0efdb69cd0',
+'d23bbf5dac14641866947512bde59dc5'),
+('db53a9290a0a0a650091abebccf833c6','9ee1b13dc6112271007f9d0efdb69cd0',
+'db54a0f60a0a0a65002c54dcb72b4f41'),
+('e08fad2637232000158bbfc8bcbe5d39','8e826bf03710200044e0bfc8bcbe5d86',
+'f602756637232000158bbfc8bcbe5d88'),
+('699708d4d773020058c92cf65e61037c','8d59d601d7b3020058c92cf65e6103c2',
+'f718a241d7b3020058c92cf65e610332'),
+('df50316637232000158bbfc8bcbe5d23','9e826bf03710200044e0bfc8bcbe5da6',
+'fe82f56637232000158bbfc8bcbe5d4e'),
+('f972d6061b13200050fdfbcd2c0713e5','780395f0df031100a9e78b6c3df2631f',
+'ff4395f0df031100a9e78b6c3df2637e');
+
+INSERT INTO t3 VALUES
+('87245e061b13200050fdfbcd2c0713cc','7172ea0037332000158bbfc8bcbe5db6'),
+('74af88c6c611227d0066386e74dc853d','74ad1ff3c611227d01d25feac2af603f'),
+('59e22fb137032000158bbfc8bcbe5d52','75d9f63237232000158bbfc8bcbe5dd0'),
+('98906fb137032000158bbfc8bcbe5d65','781da52637232000158bbfc8bcbe5db8'),
+('87245e061b13200050fdfbcd2c0713cc','7864ae0037332000158bbfc8bcbe5db8'),
+('87245e061b13200050fdfbcd2c0713cc','7b10fd2637232000158bbfc8bcbe5d30'),
+('59e22fb137032000158bbfc8bcbe5d52','81a880e037003000158bbfc8bcbe5df8'),
+('74af88c6c611227d0066386e74dc853d','8a4cb6d4c61122780043b1642efcd52b'),
+('1cb8ab9bff500200158bffffffffff62','8a4dde73c6112278017a6a4baf547aa7'),
+('1cb8ab9bff500200158bffffffffff62','8a5055c9c61122780043563ef53438e3'),
+('87245e061b13200050fdfbcd2c0713cc','96346e0037332000158bbfc8bcbe5daa'),
+('59e22fb137032000158bbfc8bcbe5d52','96fb652637232000158bbfc8bcbe5db4'),
+('59e22fb137032000158bbfc8bcbe5d52','a1d9f63237232000158bbfc8bcbe5dc3'),
+('59e22fb137032000158bbfc8bcbe5d52','a5d9f63237232000158bbfc8bcbe5dca'),
+('1cb8ab9bff500200158bffffffffff62','a715cd759f2002002920bde8132e7018'),
+('59e22fb137032000158bbfc8bcbe5d52','a9d9f63237232000158bbfc8bcbe5dc0'),
+('74af88c6c611227d0066386e74dc853d','aacb62e2c0a80015007f67f752c2b12c'),
+('74af88c6c611227d0066386e74dc853d','aaccc971c0a8001500fe1ff4302de101'),
+('59e22fb137032000158bbfc8bcbe5d52','add9f63237232000158bbfc8bcbe5dbb'),
+('59e22fb137032000158bbfc8bcbe5d52','add9f63237232000158bbfc8bcbe5dc7'),
+('59e22fb137032000158bbfc8bcbe5d52','b1d9f63237232000158bbfc8bcbe5dcf'),
+('1cb8ab9bff500200158bffffffffff62','b85d44954a3623120004689b2d5dd60a'),
+('1cb8ab9bff500200158bffffffffff62','b97e89b94a36231201676b73322a0311'),
+('1cb8ab9bff500200158bffffffffff62','cfcbad03d711110050f5edcb9e61038f'),
+('1cb8ab9bff500200158bffffffffff62','d625dccec0a8016700a222a0f7900d06'),
+('1cb8ab9bff500200158bffffffffff62','db53580b0a0a0a6501aa37c294a2ba6b'),
+('1cb8ab9bff500200158bffffffffff62','db53a9290a0a0a650091abebccf833c6'),
+('1cb8ab9bff500200158bffffffffff62','dc0db135c332010016194ffe5bba8f23'),
+('87245e061b13200050fdfbcd2c0713cc','df50316637232000158bbfc8bcbe5d23'),
+('87245e061b13200050fdfbcd2c0713cc','e08fad2637232000158bbfc8bcbe5d39'),
+('59e22fb137032000158bbfc8bcbe5d52','e1d9f63237232000158bbfc8bcbe5db8'),
+('59e22fb137032000158bbfc8bcbe5d52','e5d9f63237232000158bbfc8bcbe5db4'),
+('59e22fb137032000158bbfc8bcbe5d52','e5d9f63237232000158bbfc8bcbe5dbf'),
+('59e22fb137032000158bbfc8bcbe5d52','e9d9f63237232000158bbfc8bcbe5dba'),
+('59e22fb137032000158bbfc8bcbe5d52','e9d9f63237232000158bbfc8bcbe5dc6'),
+('87245e061b13200050fdfbcd2c0713cc','ebb4620037332000158bbfc8bcbe5d89'),
+('87245e061b13200050fdfbcd2c0713cc','ec70316637232000158bbfc8bcbe5d60'),
+('87245e061b13200050fdfbcd2c0713cc','f253da061b13200050fdfbcd2c0713ab'),
+('87245e061b13200050fdfbcd2c0713cc','f304ae0037332000158bbfc8bcbe5d4f'),
+('98906fb137032000158bbfc8bcbe5d65','f972d6061b13200050fdfbcd2c0713e5'),
+('59e22fb137032000158bbfc8bcbe5d52','fdd9f63237232000158bbfc8bcbe5dcd');
+
+--enable_query_log
+
+let $q=
+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');
+
+set optimizer_switch='materialization=off';
+eval explain $q;
+eval $q;
+
+set optimizer_switch='materialization=on';
+eval explain $q;
+eval $q;
+
+DROP TABLE t1,t2,t3;
+set optimizer_switch=@save_optimizer_switch;
+
--echo # End of 5.5 tests
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index f4dc6a5d449..4a3c19b49ab 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -1370,3 +1370,18 @@ order by d;
drop table t1;
--echo End of 5.0 tests
+
+#
+# Bug #24595639: INCORRECT BEHAVIOR IN QUERY WITH UNION AND GROUP BY
+#
+create table t1 (a int, b int);
+insert into t1 values (1,1),(2,2),(3,3);
+create table t2 (c varchar(30), d varchar(30));
+insert into t1 values ('1','1'),('2','2'),('4','4');
+create table t3 (e int, f int);
+insert into t3 values (1,1),(2,2),(31,31),(32,32);
+select e,f, (e , f) in (select e,b from t1 union select c,d from t2) as sub from t3;
+select avg(f), (e , f) in (select e,b from t1 union select c,d from t2) as sub from t3 group by sub;
+drop table t1,t2,t3;
+
+--echo End of 5.5 tests
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index aece2000cd4..659327eebb1 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -5565,6 +5565,22 @@ select *
drop view v1;
drop table t1,t2,t3;
+--echo #
+--echo # MDEV-11240: Server crashes in check_view_single_update or
+--echo # Assertion `derived->table' failed in mysql_derived_merge_for_insert
+--echo #
+
+CREATE TABLE t3 (a INT);
+CREATE ALGORITHM = MERGE VIEW v1 AS SELECT t2.a FROM t3 AS t1, t3 AS t2;
+CREATE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM v1;
+PREPARE stmt FROM 'REPLACE INTO v2 SELECT a FROM t3';
+--error ER_VIEW_NO_INSERT_FIELD_LIST
+EXECUTE stmt;
+--error ER_VIEW_NO_INSERT_FIELD_LIST
+EXECUTE stmt;
+drop view v1,v2;
+drop table t3;
+
--echo # -----------------------------------------------------------------
--echo # -- End of 5.5 tests.
--echo # -----------------------------------------------------------------