From 889ce031087dbe44cdbdbd2c93a7983667aec536 Mon Sep 17 00:00:00 2001
From: "Norvald H. Ryeng" <norvald.ryeng@oracle.com>
Date: Wed, 23 May 2012 12:27:32 +0200
Subject: WL#6311 Remove --safe-mode

Print deprecation warning if the --safe-mode command line option is
used.
---
 mysql-test/r/mysqld--help-notwin.result | 2 +-
 mysql-test/r/mysqld--help-win.result    | 2 +-
 2 files changed, 2 insertions(+), 2 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/mysqld--help-notwin.result b/mysql-test/r/mysqld--help-notwin.result
index 36649cf761d..72f89ae3bc1 100644
--- a/mysql-test/r/mysqld--help-notwin.result
+++ b/mysql-test/r/mysqld--help-notwin.result
@@ -595,7 +595,7 @@ The following options may be given as the first argument:
  master during slave registration
  --rpl-recovery-rank=# 
  Unused, will be removed
- --safe-mode         Skip some optimize stages (for testing).
+ --safe-mode         Skip some optimize stages (for testing). Deprecated.
  --safe-user-create  Don't allow new user creation by the user who has no
  write privileges to the mysql.user table.
  --secure-auth       Disallow authentication for accounts that have old
diff --git a/mysql-test/r/mysqld--help-win.result b/mysql-test/r/mysqld--help-win.result
index db7dd264b76..2ab41913427 100644
--- a/mysql-test/r/mysqld--help-win.result
+++ b/mysql-test/r/mysqld--help-win.result
@@ -595,7 +595,7 @@ The following options may be given as the first argument:
  master during slave registration
  --rpl-recovery-rank=# 
  Unused, will be removed
- --safe-mode         Skip some optimize stages (for testing).
+ --safe-mode         Skip some optimize stages (for testing). Deprecated.
  --safe-user-create  Don't allow new user creation by the user who has no
  write privileges to the mysql.user table.
  --secure-auth       Disallow authentication for accounts that have old
-- 
cgit v1.2.1


From 1c0388a5be8346a0a2c0e00caaf2a9c11f26830c Mon Sep 17 00:00:00 2001
From: Jon Olav Hauglid <jon.hauglid@oracle.com>
Date: Fri, 1 Jun 2012 09:31:24 +0200
Subject: Bug#13982017: ALTER TABLE RENAME ENDS UP WITH ERROR 1050 (42S01)

Fixed by backport of:
    ------------------------------------------------------------
    revno: 3402.50.156
    committer: Jon Olav Hauglid <jon.hauglid@oracle.com>
    branch nick: mysql-trunk-test
    timestamp: Wed 2012-02-08 14:10:23 +0100
    message:
      Bug#13417754 ASSERT IN ROW_DROP_DATABASE_FOR_MYSQL DURING DROP SCHEMA

      This assert could be triggered if an InnoDB table was being moved
      to a different database using ALTER TABLE ... RENAME, while this
      database concurrently was being dropped by DROP DATABASE.

      The reason for the problem was that no metadata lock was taken
      on the target database by ALTER TABLE ... RENAME.
      DROP DATABASE was therefore not blocked and could remove
      the database while ALTER TABLE ... RENAME was executing. This
      could cause the assert in InnoDB to be triggered.

      This patch fixes the problem by taking a IX metadata lock on
      the target database before ALTER TABLE ... RENAME starts
      moving a table to a different database.

      Note that this problem did not occur with RENAME TABLE which
      already takes the correct metadata locks.

      Also note that this patch slightly changes the behavior of
      ALTER TABLE ... RENAME. Before, the statement would abort and
      return an error if a lock on the target table name could not
      be taken immediately. With this patch, ALTER TABLE ... RENAME
      will instead block and wait until the lock can be taken
      (or until we get a lock timeout). This also means that it is
      possible to get ER_LOCK_DEADLOCK errors in this situation
      since we allow ALTER TABLE ... RENAME to wait and not just
      abort immediately.
---
 mysql-test/r/create-big.result | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/create-big.result b/mysql-test/r/create-big.result
index 34293d7e5cd..4cce5d8618c 100644
--- a/mysql-test/r/create-big.result
+++ b/mysql-test/r/create-big.result
@@ -53,8 +53,8 @@ set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
 create table t1 select 1 as i;;
 set debug_sync='now WAIT_FOR parked';
 alter table t3 rename to t1;
-ERROR 42S01: Table 't1' already exists
 set debug_sync='now SIGNAL go';
+ERROR 42S01: Table 't1' already exists
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -65,8 +65,8 @@ set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
 create table t1 select 1 as i;;
 set debug_sync='now WAIT_FOR parked';
 alter table t3 rename to t1, add k int;
-ERROR 42S01: Table 't1' already exists
 set debug_sync='now SIGNAL go';
+ERROR 42S01: Table 't1' already exists
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-- 
cgit v1.2.1


From 60561ae6133cf40f4fc445e1d6e8f395a20b2573 Mon Sep 17 00:00:00 2001
From: unknown <sanja@montyprogram.com>
Date: Thu, 21 Jun 2012 18:47:13 +0300
Subject: Fix for LP bug#1001505 and LP bug#1001510

We set correct cmp_context during preparation to avoid changing it later by Item_field::equal_fields_propagator.
(see mysql bugs #57135 #57692 during merging)
---
 mysql-test/r/case.result    | 4 ++++
 mysql-test/r/compare.result | 4 ++++
 2 files changed, 8 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result
index 0c0e2d623c8..811bbee1d91 100644
--- a/mysql-test/r/case.result
+++ b/mysql-test/r/case.result
@@ -218,3 +218,7 @@ a	d
 3	11120436154190595086
 drop table t1, t2;
 End of 5.0 tests
+CREATE TABLE t1(a YEAR);
+SELECT 1 FROM t1 WHERE a=1 AND CASE 1 WHEN a THEN 1 ELSE 1 END;
+1
+DROP TABLE t1;
diff --git a/mysql-test/r/compare.result b/mysql-test/r/compare.result
index 796821a87bd..5168eb38586 100644
--- a/mysql-test/r/compare.result
+++ b/mysql-test/r/compare.result
@@ -96,3 +96,7 @@ SELECT * FROM t1 WHERE a > '2008-01-01' AND a = '0000-00-00';
 a
 DROP TABLE t1;
 End of 5.0 tests
+CREATE TABLE t1(a INT ZEROFILL);
+SELECT 1 FROM t1 WHERE t1.a IN (1, t1.a) AND t1.a=2;
+1
+DROP TABLE t1;
-- 
cgit v1.2.1


From 1b84c0cfee4db6f0a6c97459a47444ed1f0d6ce1 Mon Sep 17 00:00:00 2001
From: unknown <sanja@montyprogram.com>
Date: Tue, 26 Jun 2012 21:43:34 +0300
Subject: Fix for LP bug#1007622

TABLE_LIST::check_single_table made aware about fact that now if table attached to a merged view it can be (unopened) temporary table
(in 5.2 it was always leaf table or non (in case of several tables)).
---
 mysql-test/r/view.result | 13 +++++++++++++
 1 file changed, 13 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 86ebc9a8ee7..56598583bb6 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -4472,6 +4472,19 @@ INSERT INTO t2 VALUES (1);
 DROP TRIGGER tr;
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
+#
+# LP bug#1007622 Server crashes in handler::increment_statistics on
+# inserting into a view over a view
+#
+CREATE TABLE t1 (a INT);
+CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a1.* FROM t1 AS a1, t1 AS a2;
+CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM v1;
+INSERT INTO v2 (a) VALUES (1) ;
+select * from t1;
+a
+1
+drop view v2,v1;
+drop table t1;
 # -----------------------------------------------------------------
 # -- End of 5.3 tests.
 # -----------------------------------------------------------------
-- 
cgit v1.2.1


From 767501fb54f19adf3c136e5131daf11e34b3f039 Mon Sep 17 00:00:00 2001
From: Gleb Shchepa <gleb.shchepa@oracle.com>
Date: Fri, 29 Jun 2012 12:55:45 +0400
Subject: Backport of the deprecation warning from WL#6219: "Deprecate and
 remove YEAR(2) type"

Print the warning(note):

 YEAR(x) is deprecated and will be removed in a future release. Please use YEAR(4) instead

on "CREATE TABLE ... YEAR(x)" or "ALTER TABLE MODIFY ... YEAR(x)", where x != 4
---
 mysql-test/r/func_group.result |  2 ++
 mysql-test/r/type_blob.result  |  4 ++++
 mysql-test/r/type_year.result  | 15 +++++++++++++++
 3 files changed, 21 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index b90eb2a4c0f..74899a22636 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1524,6 +1524,8 @@ DROP TABLE t1;
 # Bug#43668: Wrong comparison and MIN/MAX for YEAR(2)
 #
 create table t1 (f1 year(2), f2 year(4), f3 date, f4 datetime);
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 insert into t1 values
 (98,1998,19980101,"1998-01-01 00:00:00"),
 (00,2000,20000101,"2000-01-01 00:00:01"),
diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result
index e6fd49b4247..8b4b6438842 100644
--- a/mysql-test/r/type_blob.result
+++ b/mysql-test/r/type_blob.result
@@ -878,6 +878,8 @@ ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT
 CREATE TABLE b15776 (a char(4294967296));
 ERROR 42000: Display width out of range for column 'a' (max = 4294967295)
 CREATE TABLE b15776 (a year(4294967295));
+Warnings:
+Note	1287	'YEAR(4294967295)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 INSERT INTO b15776 VALUES (42);
 SELECT * FROM b15776;
 a
@@ -886,6 +888,8 @@ DROP TABLE b15776;
 CREATE TABLE b15776 (a year(4294967296));
 ERROR 42000: Display width out of range for column 'a' (max = 4294967295)
 CREATE TABLE b15776 (a year(0));
+Warnings:
+Note	1287	'YEAR(0)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 DROP TABLE b15776;
 CREATE TABLE b15776 (a year(-2));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2))' at line 1
diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result
index 2dc491c6166..e00569c93c1 100644
--- a/mysql-test/r/type_year.result
+++ b/mysql-test/r/type_year.result
@@ -1,5 +1,7 @@
 drop table if exists t1;
 create table t1 (y year,y2 year(2));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 insert into t1 values (0,0),(1999,1999),(2000,2000),(2001,2001),(70,70),(69,69);
 select * from t1;
 y	y2
@@ -50,6 +52,8 @@ End of 5.0 tests
 # Bug #49480: WHERE using YEAR columns returns unexpected results
 #
 CREATE TABLE t2(yy YEAR(2), c2 CHAR(4));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 CREATE TABLE t4(yyyy YEAR(4), c4 CHAR(4));
 INSERT INTO t2 (c2) VALUES (NULL),(1970),(1999),(2000),(2001),(2069);
 INSERT INTO t4 (c4) SELECT c2 FROM t2;
@@ -355,4 +359,15 @@ total_rows	min_value	MAX(c1)
 3	0	2155
 DROP TABLE t1;
 #
+# WL#6219: Deprecate and remove YEAR(2) type
+#
+CREATE TABLE t1 (c1 YEAR(2), c2 YEAR(4));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+ALTER TABLE t1 MODIFY COLUMN c2 YEAR(2);
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+DROP TABLE t1;
+#
 End of 5.1 tests
-- 
cgit v1.2.1


From 46525c35b084a8af4dc99c8da78cf9e40e69dee1 Mon Sep 17 00:00:00 2001
From: Sergey Petrunya <psergey@askmonty.org>
Date: Wed, 4 Jul 2012 14:34:45 +0400
Subject: MDEV-376: Wrong result (missing rows) with
 index_merge+index_merge_intersection, join - Let
 QUICK_RANGE_SELECT::init_ror_merged_scan() call  quick->reset() only   after
 we've set the column read bitmaps.

---
 mysql-test/r/index_merge_innodb.result | 15 +++++++++++++++
 1 file changed, 15 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result
index b8eda092291..92bcb2e88f0 100644
--- a/mysql-test/r/index_merge_innodb.result
+++ b/mysql-test/r/index_merge_innodb.result
@@ -777,4 +777,19 @@ commit;
 select * from t1 where t1.zone_id=830 AND modified=9;
 pk	zone_id	modified
 drop table t0, t1;
+#
+# MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join
+#
+CREATE TABLE t1 (
+a INT, b CHAR(1), c CHAR(1), KEY(a), KEY(b)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (8,'v','v'),(8,'m','m'),(9,'d','d');
+SELECT ta.* FROM t1 AS ta, t1 AS tb
+WHERE ( tb.b != ta.b OR tb.a = ta.a )
+AND ( tb.b = ta.c OR tb.b = ta.b );
+a	b	c
+8	v	v
+8	m	m
+9	d	d
+DROP TABLE t1;
 set optimizer_switch= @optimizer_switch_save;
-- 
cgit v1.2.1


From 9ce35ffc8677ff3c7171576497ed6c7330000b72 Mon Sep 17 00:00:00 2001
From: Georgi Kodinov <Georgi.Kodinov@Oracle.com>
Date: Wed, 4 Jul 2012 17:48:58 +0300
Subject: Bug #11753490: 44939: sql dumps containing broad views fail when
 executing

The problem is that mysql lacks information about the objects a view
depends on so it can't dump views and tables in the proper order.
Thus it needs to create "stand-in" myisam tables for each view while
dumping the tables that it later drops and replaces with the actual view
view definition.
But since views can have much more columns than an actual table creating
these stand-in tables may be problematic.

There's no way to portably find out how many columns an mysiam table
can have. It's a complicated formula depending on internal server constants.
Thus we can't have a reliable error check without repeating the logic and
the formula inside mysqldump.

1. Changed the type of the columns of the stand-in tables mysqldump
makes to satisfy view dependencies from the original type to smallint
to save on row space.

2. Added a warning on the mysqldump's standard error for a possible
problems replaying the dump file if the columns of a view exceed 1000.

3. Added a test case.
---
 mysql-test/r/mysqldump.result | 46 +++++++++++++++++++++----------------------
 1 file changed, 23 insertions(+), 23 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index 15fdddb18be..5f3b29f5f7c 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -1988,7 +1988,7 @@ DROP TABLE IF EXISTS `v2`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v2` (
-  `a` varchar(30)
+  `a` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 /*!50001 DROP TABLE IF EXISTS `v2`*/;
@@ -2082,7 +2082,7 @@ DROP TABLE IF EXISTS `v1`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v1` (
-  `a` int(11)
+  `a` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 /*!50001 DROP TABLE IF EXISTS `v1`*/;
@@ -2156,7 +2156,7 @@ DROP TABLE IF EXISTS `v2`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v2` (
-  `a` varchar(30)
+  `a` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 /*!50001 DROP TABLE IF EXISTS `v2`*/;
@@ -2270,9 +2270,9 @@ DROP TABLE IF EXISTS `v1`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v1` (
-  `a` int(11),
-  `b` int(11),
-  `c` varchar(30)
+  `a` tinyint NOT NULL,
+  `b` tinyint NOT NULL,
+  `c` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 DROP TABLE IF EXISTS `v2`;
@@ -2280,7 +2280,7 @@ DROP TABLE IF EXISTS `v2`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v2` (
-  `a` int(11)
+  `a` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 DROP TABLE IF EXISTS `v3`;
@@ -2288,9 +2288,9 @@ DROP TABLE IF EXISTS `v3`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v3` (
-  `a` int(11),
-  `b` int(11),
-  `c` varchar(30)
+  `a` tinyint NOT NULL,
+  `b` tinyint NOT NULL,
+  `c` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 /*!50001 DROP TABLE IF EXISTS `v1`*/;
@@ -3027,9 +3027,9 @@ DROP TABLE IF EXISTS `v0`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v0` (
-  `a` int(11),
-  `b` varchar(32),
-  `c` varchar(32)
+  `a` tinyint NOT NULL,
+  `b` tinyint NOT NULL,
+  `c` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 DROP TABLE IF EXISTS `v1`;
@@ -3037,9 +3037,9 @@ DROP TABLE IF EXISTS `v1`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v1` (
-  `a` int(11),
-  `b` varchar(32),
-  `c` varchar(32)
+  `a` tinyint NOT NULL,
+  `b` tinyint NOT NULL,
+  `c` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 DROP TABLE IF EXISTS `v2`;
@@ -3047,9 +3047,9 @@ DROP TABLE IF EXISTS `v2`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v2` (
-  `a` int(11),
-  `b` varchar(32),
-  `c` varchar(32)
+  `a` tinyint NOT NULL,
+  `b` tinyint NOT NULL,
+  `c` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 
@@ -3429,7 +3429,7 @@ DROP TABLE IF EXISTS `v1`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v1` (
-  `id` int(11)
+  `id` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 
@@ -3489,7 +3489,7 @@ USE `mysqldump_views`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `nasishnasifu` (
-  `id` bigint(20) unsigned
+  `id` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 
@@ -3882,7 +3882,7 @@ DROP TABLE IF EXISTS `v2`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v2` (
-  `c` int(11)
+  `c` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 /*!50001 DROP TABLE IF EXISTS `v2`*/;
@@ -4299,7 +4299,7 @@ DROP TABLE IF EXISTS `v1`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v1` (
-  `id` int(11)
+  `id` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 
-- 
cgit v1.2.1


From 9f6a1c5842fc8f71463d48e4da4ec692aadeacc1 Mon Sep 17 00:00:00 2001
From: unknown <sanja@montyprogram.com>
Date: Fri, 13 Jul 2012 22:17:32 +0300
Subject: fixed MySQL bug#53775: Now partition engine adds underlying tables to
 the QC and ask underlying tables engine permittion to cache the query and
 return result of the query.

Incorrect QC cleanup in case of table registration failure fixe.

Unified interface for myisammrg & partitioned engnes for QC.
---
 mysql-test/r/partition_cache.result        |  18 ++--
 mysql-test/r/partition_cache_innodb.result | 151 ++++++++++++++++++++++++++++
 mysql-test/r/partition_cache_myisam.result | 153 +++++++++++++++++++++++++++++
 3 files changed, 313 insertions(+), 9 deletions(-)
 create mode 100644 mysql-test/r/partition_cache_innodb.result
 create mode 100644 mysql-test/r/partition_cache_myisam.result

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/partition_cache.result b/mysql-test/r/partition_cache.result
index ac2da9bb78a..cd579d00952 100644
--- a/mysql-test/r/partition_cache.result
+++ b/mysql-test/r/partition_cache.result
@@ -27,7 +27,7 @@ a
 3
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	1
 drop table t1;
 commit;
 create table t1 (a int not null) PARTITION BY KEY (a) PARTITIONS 3;
@@ -50,7 +50,7 @@ a
 2
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	3
 show status like "Qcache_hits";
 Variable_name	Value
 Qcache_hits	0
@@ -69,7 +69,7 @@ a
 2
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	6
 show status like "Qcache_hits";
 Variable_name	Value
 Qcache_hits	0
@@ -93,14 +93,14 @@ a
 2
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	2
 show status like "Qcache_hits";
 Variable_name	Value
-Qcache_hits	0
+Qcache_hits	1
 commit;
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	2
 drop table t3,t2,t1;
 CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) PARTITION BY HASH (id) PARTITIONS 3;
 select count(*) from t1;
@@ -164,7 +164,7 @@ count(*)
 2
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	1
 connection connection1
 SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
 count(*)
@@ -197,9 +197,9 @@ count(*)
 2
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	1
 show status like "Qcache_hits";
 Variable_name	Value
-Qcache_hits	0
+Qcache_hits	1
 set @@global.query_cache_size = @save_query_cache_size;
 drop table t2;
diff --git a/mysql-test/r/partition_cache_innodb.result b/mysql-test/r/partition_cache_innodb.result
new file mode 100644
index 00000000000..0d0abbb096c
--- /dev/null
+++ b/mysql-test/r/partition_cache_innodb.result
@@ -0,0 +1,151 @@
+SET SESSION STORAGE_ENGINE = innodb;
+drop table if exists t1;
+set @save_query_cache_size = @@global.query_cache_size;
+# Test that partitions works with query cache
+flush query cache;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+);
+ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  `created_at` datetime NOT NULL,
+  `cool` tinyint(4) DEFAULT '0'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
+(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = InnoDB,
+ PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = InnoDB,
+ PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
+INSERT INTO t1 VALUES (1, now(), 0);
+flush status;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+# Test that sub-partitions works with query cache
+flush query cache;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+)
+PARTITION BY RANGE (TO_DAYS(created_at))
+subpartition by hash(cool) subpartitions 3 (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  `created_at` datetime NOT NULL,
+  `cool` tinyint(4) DEFAULT '0'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
+SUBPARTITION BY HASH (cool)
+SUBPARTITIONS 3
+(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = InnoDB,
+ PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = InnoDB,
+ PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
+INSERT INTO t1 VALUES (1, now(), 0);
+flush status;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+#
+# MySQL bug#53775 Query on partitioned table returns cached result
+# from previous transaction
+#
+flush query cache;
+flush status;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+);
+ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+INSERT INTO t1 VALUES (1, now(), 0);
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+BEGIN;
+UPDATE `t1` SET `cool` = 1 WHERE `id` = 1;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+ROLLBACK;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+BEGIN;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+ROLLBACK;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	2
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	1
+drop table t1;
+set @@global.query_cache_size = @save_query_cache_size;
diff --git a/mysql-test/r/partition_cache_myisam.result b/mysql-test/r/partition_cache_myisam.result
new file mode 100644
index 00000000000..0b617c03590
--- /dev/null
+++ b/mysql-test/r/partition_cache_myisam.result
@@ -0,0 +1,153 @@
+SET SESSION STORAGE_ENGINE = myisam;
+drop table if exists t1;
+set @save_query_cache_size = @@global.query_cache_size;
+# Test that partitions works with query cache
+flush query cache;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+);
+ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  `created_at` datetime NOT NULL,
+  `cool` tinyint(4) DEFAULT '0'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
+(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = MyISAM,
+ PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = MyISAM,
+ PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
+INSERT INTO t1 VALUES (1, now(), 0);
+flush status;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+# Test that sub-partitions works with query cache
+flush query cache;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+)
+PARTITION BY RANGE (TO_DAYS(created_at))
+subpartition by hash(cool) subpartitions 3 (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  `created_at` datetime NOT NULL,
+  `cool` tinyint(4) DEFAULT '0'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
+SUBPARTITION BY HASH (cool)
+SUBPARTITIONS 3
+(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = MyISAM,
+ PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = MyISAM,
+ PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
+INSERT INTO t1 VALUES (1, now(), 0);
+flush status;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+#
+# MySQL bug#53775 Query on partitioned table returns cached result
+# from previous transaction
+#
+flush query cache;
+flush status;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+);
+ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+INSERT INTO t1 VALUES (1, now(), 0);
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+BEGIN;
+UPDATE `t1` SET `cool` = 1 WHERE `id` = 1;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+ROLLBACK;
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+BEGIN;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+ROLLBACK;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	2
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+set @@global.query_cache_size = @save_query_cache_size;
-- 
cgit v1.2.1


From 403cac0fe710bbd65a65f5b409cff6194ce6bace Mon Sep 17 00:00:00 2001
From: Elena Stepanova <elenst@ubuntu11.home>
Date: Mon, 16 Jul 2012 06:14:53 +0400
Subject: Allow multiple error codes inside a variable in --error command

---
 mysql-test/r/mysqltest.result | 1 +
 1 file changed, 1 insertion(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result
index 25345227d59..fdb3029059f 100644
--- a/mysql-test/r/mysqltest.result
+++ b/mysql-test/r/mysqltest.result
@@ -690,6 +690,7 @@ Got one of the listed errors
 insert into t1 values ("Abcd");
 Got one of the listed errors
 garbage;
+SELECT * FROM non_existing_table;
 drop table t2;
 create table t1 ( f1 char(10));
 insert into t1 values ("Abcd");
-- 
cgit v1.2.1


From 55597a48698b267b966873727b079cd3ac0d1c18 Mon Sep 17 00:00:00 2001
From: Sergey Petrunya <psergey@askmonty.org>
Date: Wed, 25 Jul 2012 20:41:48 +0400
Subject: MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat
 show full table scan is used - Make Item_subselect::fix_fields() ignore
 UNCACHEABLE_EXPLAIN flag when deciding whether   the subquery item should be
 marked as constant.

---
 mysql-test/r/func_group.result              |  2 +-
 mysql-test/r/subselect.result               | 38 +++++++++++++
 mysql-test/r/subselect2.result              | 12 ++---
 mysql-test/r/subselect4.result              | 84 ++++++++++++++---------------
 mysql-test/r/subselect_mat.result           |  2 +-
 mysql-test/r/subselect_mat_cost_bugs.result |  2 +-
 mysql-test/r/subselect_no_mat.result        | 38 +++++++++++++
 mysql-test/r/subselect_no_opts.result       | 38 +++++++++++++
 mysql-test/r/subselect_no_scache.result     | 38 +++++++++++++
 mysql-test/r/subselect_no_semijoin.result   | 38 +++++++++++++
 10 files changed, 241 insertions(+), 51 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index c38201f6d9f..3b47227a261 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1838,7 +1838,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	range	a	a	4	NULL	4	100.00	Using where; Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where (<expr_cache><<cache>(1),<cache>(2)>(<in_optimizer>(<cache>((1,2)),<exists>(select 3,4 having (((1 = 3) or isnull(3)) and ((2 = 4) or isnull(4)) and <is_not_null_test>(3) and <is_not_null_test>(4))))) and (`test`.`t1`.`a` < 10))
+Note	1003	select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where (<cache>(<in_optimizer>((1,2),<exists>(select 3,4 having (((1 = 3) or isnull(3)) and ((2 = 4) or isnull(4)) and <is_not_null_test>(3) and <is_not_null_test>(4))))) and (`test`.`t1`.`a` < 10))
 SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10;
 MAX(a)
 NULL
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 1863cd09c38..3ea6c0be398 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6768,5 +6768,43 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1));
 a
 drop table t1,t2;
+#
+# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used
+#
+CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('USA');
+CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia');
+CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (12),(22),(9),(45);
+create table t4 like t3;
+insert into t4 select * from t3;
+# This should not show range access for table t2
+explain
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ref	d	d	5	test.t2.b	2	Using where; Using index
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+set @tmp_mdev410=@@global.userstat;
+set global userstat=on;
+flush table_statistics;
+flush index_statistics;
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+MIN(b)
+NULL
+# The following shows that t2 was indeed scanned with a full scan.
+show table_statistics;
+Table_schema	Table_name	Rows_read	Rows_changed	Rows_changed_x_#indexes
+test	t1	2	0	0
+test	t2	3	0	0
+show index_statistics;
+Table_schema	Table_name	Index_name	Rows_read
+test	t2	b	1
+set global userstat=@tmp_mdev410;
+DROP TABLE t1,t2,t3,t4;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result
index 41c445329cb..ed00e4ef684 100644
--- a/mysql-test/r/subselect2.result
+++ b/mysql-test/r/subselect2.result
@@ -161,18 +161,18 @@ SET optimizer_switch='materialization=on,in_to_exists=on';
 EXPLAIN
 SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM t1) OR a = b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	2	Using index
-1	PRIMARY	t3	index	b	b	5	NULL	4	Using where; Using index; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t1	index	PRIMARY,a	a	5	NULL	2	Using index
+1	PRIMARY	t2	index	a	a	5	NULL	2	Using where; Using index
+1	PRIMARY	t3	ref	b	b	5	test.t2.a	2	Using index
+2	SUBQUERY	t1	index_subquery	PRIMARY,a	a	5	const	0	Using index; Using where
 SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM t1) OR a = b;
 pk	a	b
 0	4	4
 EXPLAIN
 SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM v1) OR a = b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	2	Using index
-1	PRIMARY	t3	index	b	b	5	NULL	4	Using where; Using index; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t1	index	PRIMARY,a	a	5	NULL	2	Using index
+1	PRIMARY	t2	index	a	a	5	NULL	2	Using where; Using index
+1	PRIMARY	t3	ref	b	b	5	test.t2.a	2	Using index
+2	SUBQUERY	t1	index_subquery	PRIMARY,a	a	5	const	0	Using index; Using where
 SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM v1) OR a = b;
 pk	a	b
 0	4	4
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 06e746f3238..0f9912fc1f3 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -627,51 +627,51 @@ SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off';
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	MATERIALIZED	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	MATERIALIZED	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	MATERIALIZED	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in;
 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	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in;
 not_in
 NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	MATERIALIZED	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	MATERIALIZED	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in;
 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	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in;
 not_in
 NULL
@@ -679,7 +679,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in;
 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	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in;
 not_in
 1
@@ -687,7 +687,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in;
 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	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in;
 not_in
 NULL
@@ -695,7 +695,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in;
 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	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in;
 not_in
 NULL
@@ -703,7 +703,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in;
 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	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in;
 not_in
 NULL
@@ -718,51 +718,51 @@ SET @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off';
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in;
 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	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in;
 not_in
 NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in;
 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	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in;
 not_in
 NULL
@@ -770,7 +770,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in;
 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	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in;
 not_in
 1
@@ -778,7 +778,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in;
 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	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in;
 not_in
 NULL
@@ -786,7 +786,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in;
 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	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in;
 not_in
 NULL
@@ -794,7 +794,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in;
 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	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in;
 not_in
 NULL
@@ -813,21 +813,21 @@ SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off';
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10);
 f1	f2
@@ -842,14 +842,14 @@ NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10);
 f1	f2
@@ -904,21 +904,21 @@ SET @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off';
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10);
 f1	f2
@@ -933,14 +933,14 @@ NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10);
 f1	f2
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 45d0b9ee519..9bb26e8a6e0 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2183,7 +2183,7 @@ NULL
 EXPLAIN
 SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1);
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index 5a38fe7ca72..1d03f2e9fe6 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -162,7 +162,7 @@ EXPLAIN
 SELECT * FROM (SELECT * FROM t2) AS a2
 WHERE (SELECT distinct SUM(distinct f3 ) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
 3	SUBQUERY	t1	index	NULL	f3	5	NULL	2	Using index
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 insert into t2 values (1),(2);
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 78ab87dbfd6..8ce097787f1 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6766,6 +6766,44 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1));
 a
 drop table t1,t2;
+#
+# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used
+#
+CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('USA');
+CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia');
+CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (12),(22),(9),(45);
+create table t4 like t3;
+insert into t4 select * from t3;
+# This should not show range access for table t2
+explain
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ref	d	d	5	test.t2.b	2	Using where; Using index
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+set @tmp_mdev410=@@global.userstat;
+set global userstat=on;
+flush table_statistics;
+flush index_statistics;
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+MIN(b)
+NULL
+# The following shows that t2 was indeed scanned with a full scan.
+show table_statistics;
+Table_schema	Table_name	Rows_read	Rows_changed	Rows_changed_x_#indexes
+test	t1	2	0	0
+test	t2	3	0	0
+show index_statistics;
+Table_schema	Table_name	Index_name	Rows_read
+test	t2	b	1
+set global userstat=@tmp_mdev410;
+DROP TABLE t1,t2,t3,t4;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index f822a4500f4..466d8c8fd06 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6763,6 +6763,44 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1));
 a
 drop table t1,t2;
+#
+# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used
+#
+CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('USA');
+CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia');
+CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (12),(22),(9),(45);
+create table t4 like t3;
+insert into t4 select * from t3;
+# This should not show range access for table t2
+explain
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ref	d	d	5	test.t2.b	2	Using where; Using index
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+set @tmp_mdev410=@@global.userstat;
+set global userstat=on;
+flush table_statistics;
+flush index_statistics;
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+MIN(b)
+NULL
+# The following shows that t2 was indeed scanned with a full scan.
+show table_statistics;
+Table_schema	Table_name	Rows_read	Rows_changed	Rows_changed_x_#indexes
+test	t1	2	0	0
+test	t2	3	0	0
+show index_statistics;
+Table_schema	Table_name	Index_name	Rows_read
+test	t2	b	1
+set global userstat=@tmp_mdev410;
+DROP TABLE t1,t2,t3,t4;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 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 cdad363313d..1fde0d08d7e 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6774,6 +6774,44 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1));
 a
 drop table t1,t2;
+#
+# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used
+#
+CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('USA');
+CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia');
+CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (12),(22),(9),(45);
+create table t4 like t3;
+insert into t4 select * from t3;
+# This should not show range access for table t2
+explain
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ref	d	d	5	test.t2.b	2	Using where; Using index
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+set @tmp_mdev410=@@global.userstat;
+set global userstat=on;
+flush table_statistics;
+flush index_statistics;
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+MIN(b)
+NULL
+# The following shows that t2 was indeed scanned with a full scan.
+show table_statistics;
+Table_schema	Table_name	Rows_read	Rows_changed	Rows_changed_x_#indexes
+test	t1	2	0	0
+test	t2	3	0	0
+show index_statistics;
+Table_schema	Table_name	Index_name	Rows_read
+test	t2	b	1
+set global userstat=@tmp_mdev410;
+DROP TABLE t1,t2,t3,t4;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 8e6d4bfccc2..ba50b8522eb 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6763,6 +6763,44 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1));
 a
 drop table t1,t2;
+#
+# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used
+#
+CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('USA');
+CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia');
+CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (12),(22),(9),(45);
+create table t4 like t3;
+insert into t4 select * from t3;
+# This should not show range access for table t2
+explain
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ref	d	d	5	test.t2.b	2	Using where; Using index
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+set @tmp_mdev410=@@global.userstat;
+set global userstat=on;
+flush table_statistics;
+flush index_statistics;
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+MIN(b)
+NULL
+# The following shows that t2 was indeed scanned with a full scan.
+show table_statistics;
+Table_schema	Table_name	Rows_read	Rows_changed	Rows_changed_x_#indexes
+test	t1	2	0	0
+test	t2	3	0	0
+show index_statistics;
+Table_schema	Table_name	Index_name	Rows_read
+test	t2	b	1
+set global userstat=@tmp_mdev410;
+DROP TABLE t1,t2,t3,t4;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set @optimizer_switch_for_subselect_test=null;
-- 
cgit v1.2.1


From 42ea25d4c51b5466b4b03a29abe480e763732cf2 Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <sergii@pisem.net>
Date: Tue, 31 Jul 2012 18:32:46 +0200
Subject: MDEV-375 Server crashes in THD::print_aborted_warning with
 log_warnings > 3

Don't use ER(xxx) in THD::close_connection(), when current_thd is already reset to NULL.
Prefer ER_THD() or ER_DEFAULT() instead.
---
 mysql-test/r/mdev375.result | 14 ++++++++++++++
 1 file changed, 14 insertions(+)
 create mode 100644 mysql-test/r/mdev375.result

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/mdev375.result b/mysql-test/r/mdev375.result
new file mode 100644
index 00000000000..32580804686
--- /dev/null
+++ b/mysql-test/r/mdev375.result
@@ -0,0 +1,14 @@
+SET GLOBAL log_warnings=4;
+SET GLOBAL max_connections=2;
+SELECT 1;
+1
+1
+SELECT 2;
+2
+2
+ERROR HY000: Too many connections
+SELECT 0;
+0
+0
+SET GLOBAL log_warnings=default;
+SET GLOBAL max_connections=default;
-- 
cgit v1.2.1


From 7cc2f8decabe83fb304458748ba907f011848309 Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <sergii@pisem.net>
Date: Thu, 9 Aug 2012 18:25:47 +0200
Subject: fix val_str_ascii to return a string in the ascii-compatible charset.
 two items didn't do that properly, one was exploitable, the other was not,
 but fixed anyway.

---
 mysql-test/r/ctype_utf32.result | 6 ++++++
 1 file changed, 6 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result
index 03f60261450..6cf24d768f7 100644
--- a/mysql-test/r/ctype_utf32.result
+++ b/mysql-test/r/ctype_utf32.result
@@ -1182,5 +1182,11 @@ a	256
 Warnings:
 Warning	1260	Row 1 was cut by GROUP_CONCAT()
 #
+# incorrect charset for val_str_ascii
+#
+SELECT '2010-10-10 10:10:10' + INTERVAL GeometryType(GeomFromText('POINT(1 1)')) hour_second;
+'2010-10-10 10:10:10' + INTERVAL GeometryType(GeomFromText('POINT(1 1)')) hour_second
+2010-10-10 10:10:10
+#
 # End of 5.5 tests
 #
-- 
cgit v1.2.1


From d07b179fd2397bd78dd4f9ba7c54dda38fc1ce8c Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Mon, 13 Aug 2012 21:13:14 -0700
Subject: Fixed bug mdev-449. The bug could caused a crash when the server
 executed a query with ORDER by and sort_buffer_size was set to a small enough
 number. It happened because the small sort buffer did not allow to allocate
 all merge buffers in it. Made sure that the allocated sort buffer would be
 big enough to contain all possible merge buffers.

---
 mysql-test/r/order_by.result | 132 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 132 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index c0a54817b4c..8fa1006b8fc 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -1783,3 +1783,135 @@ Warnings:
 Note	1003	select `test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`b`) group by `test`.`t2`.`b` order by `test`.`t1`.`b`
 drop table t1,t2;
 End of 5.2 tests
+#
+# Bug mdev-449: ORDER BY with small sort_buffer_size
+#
+CREATE TABLE t1(f0 int auto_increment primary key, f1 int, f2 varchar(200));
+INSERT INTO t1(f1, f2) VALUES 
+(0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"),
+(6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"),
+(11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"),
+(16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"),
+(21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"),
+(26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"),
+(31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"),
+(36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"),
+(41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"),
+(46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"),
+(51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"),
+(56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"),
+(61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"),
+(66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"),
+(71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"),
+(76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"),
+(81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"),
+(86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"),
+(91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"),
+(96,"96"),(97,"97"),(98,"98"),(99,"99");
+set @save_sort_buffer_size= @@sort_buffer_size;
+set sort_buffer_size= 2000;
+SELECT * FROM t1 ORDER BY f1 DESC, f0;
+f0	f1	f2
+100	99	99
+99	98	98
+98	97	97
+97	96	96
+96	95	95
+95	94	94
+94	93	93
+93	92	92
+92	91	91
+91	90	90
+90	89	89
+89	88	88
+88	87	87
+87	86	86
+86	85	85
+85	84	84
+84	83	83
+83	82	82
+82	81	81
+81	80	80
+80	79	79
+79	78	78
+78	77	77
+77	76	76
+76	75	75
+75	74	74
+74	73	73
+73	72	72
+72	71	71
+71	70	70
+70	69	69
+69	68	68
+68	67	67
+67	66	66
+66	65	65
+65	64	64
+64	63	63
+63	62	62
+62	61	61
+61	60	60
+60	59	59
+59	58	58
+58	57	57
+57	56	56
+56	55	55
+55	54	54
+54	53	53
+53	52	52
+52	51	51
+51	50	50
+50	49	49
+49	48	48
+48	47	47
+47	46	46
+46	45	45
+45	44	44
+44	43	43
+43	42	42
+42	41	41
+41	40	40
+40	39	39
+39	38	38
+38	37	37
+37	36	36
+36	35	35
+35	34	34
+34	33	33
+33	32	32
+32	31	31
+31	30	30
+30	29	29
+29	28	28
+28	27	27
+27	26	26
+26	25	25
+25	24	24
+24	23	23
+23	22	22
+22	21	21
+21	20	20
+20	19	19
+19	18	18
+18	17	17
+17	16	16
+16	15	15
+15	14	14
+14	13	13
+13	12	12
+12	11	11
+11	10	10
+10	9	9
+9	8	8
+8	7	7
+7	6	6
+6	5	5
+5	4	4
+4	3	3
+3	2	2
+2	1	1
+1	0	0
+set sort_buffer_size= @save_sort_buffer_size;
+DROP TABLE t1;
+End of 5.3 tests
-- 
cgit v1.2.1


From fbe5ac4e24ceaa9f75f52fba62f8c53e172e5c11 Mon Sep 17 00:00:00 2001
From: Michael Widenius <monty@askmonty.org>
Date: Wed, 15 Aug 2012 13:33:37 +0300
Subject: Fixed MDEV-365 "Got assertion when doing alter table on a partition"

mysql-test/r/partition.result:
  Added test case
mysql-test/t/partition.test:
  Added test case
sql/sql_partition.cc:
  Do mysql_trans_prepare_alter_copy_data() after all original tables are locked.
  (We don't want to disable transactions for the original tables, that still may be in the cache)
sql/sql_table.cc:
  Fixed two wrong DBUG_ENTER
---
 mysql-test/r/partition.result | 14 ++++++++++++++
 1 file changed, 14 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index 431c5dda116..40586b8d54b 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -2473,3 +2473,17 @@ SELECT * FROM vtmp;
 1
 DROP VIEW vtmp;
 DROP TABLE t1;
+#
+# MDEV-365 "Got assertion when doing alter table on a partition"
+#
+CREATE TABLE t1 ( i INT ) ENGINE=Aria PARTITION BY HASH(i) PARTITIONS 2;
+INSERT INTO t1 VALUES (1),(2),(2),(3),(4);
+ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
+SELECT * from t1 order by i;
+i
+1
+2
+2
+3
+4
+DROP TABLE t1;
-- 
cgit v1.2.1


From 80b3f7470536503ffe1103dda3dea3f29b7c9641 Mon Sep 17 00:00:00 2001
From: unknown <timour@askmonty.org>
Date: Tue, 21 Aug 2012 15:24:43 +0300
Subject: Fix bug mdev-447: Wrong output from the EXPLAIN command of the test
 case for lp bug #714999

The fix backports from MWL#182: Explain running statements the logic that
saves the original JOIN_TAB array of a query plan after optimization. This
array is later used during EXPLAIN to iterate over the original JOIN plan
nodes in the cases when this plan could be changed by early subquery
execution during the optimization phase of the outer query.
---
 mysql-test/r/subselect.result               | 2 +-
 mysql-test/r/subselect_innodb.result        | 4 ++--
 mysql-test/r/subselect_mat_cost_bugs.result | 2 +-
 mysql-test/r/subselect_no_mat.result        | 2 +-
 mysql-test/r/subselect_no_opts.result       | 2 +-
 mysql-test/r/subselect_no_scache.result     | 2 +-
 mysql-test/r/subselect_no_semijoin.result   | 2 +-
 7 files changed, 8 insertions(+), 8 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 3ea6c0be398..8c71c09c1ac 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using index
-2	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	1	Using temporary; Using filesort
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 INSERT INTO t1 VALUES
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
index a0f05a26a46..a4670872fad 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -333,7 +333,7 @@ WHERE (SELECT DISTINCT b FROM t3) > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	Using where; Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-3	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	0	Using temporary
+3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	1	Using temporary
 SELECT *
 FROM t1
 WHERE t1.a = (
@@ -386,7 +386,7 @@ select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 gr
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	1	
-3	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	1	Using temporary; Using filesort
+3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
 select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 group by a1));
 1
 1
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index 1d03f2e9fe6..8c95c8637aa 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -148,7 +148,7 @@ FROM t2 GROUP BY f1
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 2	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
-3	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	1	Using temporary; Using filesort
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 drop table t1, t2, t3;
 #
 # LP BUG#715034 Item_sum_distinct::clear(): Assertion `tree != 0' failed
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 8ce097787f1..48ab96dcaaa 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -4200,7 +4200,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using index
-2	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	1	Using temporary; Using filesort
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 INSERT INTO t1 VALUES
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 466d8c8fd06..9ab4e23b091 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using index
-2	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	1	Using temporary; Using filesort
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 INSERT INTO t1 VALUES
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 1fde0d08d7e..d47aa956ead 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -4202,7 +4202,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using index
-2	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	1	Using temporary; Using filesort
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 INSERT INTO t1 VALUES
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index ba50b8522eb..96e6c2182fa 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using index
-2	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	1	Using temporary; Using filesort
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 INSERT INTO t1 VALUES
-- 
cgit v1.2.1


From cdeabcfd436c65e0a97e74b1722d0259ba907541 Mon Sep 17 00:00:00 2001
From: unknown <knielsen@knielsen-hq.org>
Date: Fri, 24 Aug 2012 10:06:16 +0200
Subject: MDEV-382: Incorrect quoting

Various places in the server replication code was incorrectly quoting
strings, which could lead to incorrect SQL on the slave/mysqlbinlog.
---
 mysql-test/r/func_compress.result          |  4 +-
 mysql-test/r/mysqlbinlog.result            | 32 +++++++-------
 mysql-test/r/mysqlbinlog2.result           | 70 +++++++++++++++---------------
 mysql-test/r/mysqlbinlog_row.result        |  2 +-
 mysql-test/r/mysqlbinlog_row_innodb.result |  8 ++--
 mysql-test/r/mysqlbinlog_row_myisam.result |  8 ++--
 mysql-test/r/mysqlbinlog_row_trans.result  |  2 +-
 mysql-test/r/user_var-binlog.result        |  2 +-
 mysql-test/r/variables.result              |  2 +-
 9 files changed, 65 insertions(+), 65 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result
index b4e61d0e4fc..37f7c475148 100644
--- a/mysql-test/r/func_compress.result
+++ b/mysql-test/r/func_compress.result
@@ -11,7 +11,7 @@ explain extended select uncompress(compress(@test_compress_string));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select uncompress(compress((@test_compress_string))) AS `uncompress(compress(@test_compress_string))`
+Note	1003	select uncompress(compress((@`test_compress_string`))) AS `uncompress(compress(@test_compress_string))`
 select uncompressed_length(compress(@test_compress_string))=length(@test_compress_string);
 uncompressed_length(compress(@test_compress_string))=length(@test_compress_string)
 1
@@ -19,7 +19,7 @@ explain extended select uncompressed_length(compress(@test_compress_string))=len
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select (uncompressed_length(compress((@test_compress_string))) = length((@test_compress_string))) AS `uncompressed_length(compress(@test_compress_string))=length(@test_compress_string)`
+Note	1003	select (uncompressed_length(compress((@`test_compress_string`))) = length((@`test_compress_string`))) AS `uncompressed_length(compress(@test_compress_string))=length(@test_compress_string)`
 select uncompressed_length(compress(@test_compress_string));
 uncompressed_length(compress(@test_compress_string))
 117
diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result
index 45068ddfaec..540ecd99479 100644
--- a/mysql-test/r/mysqlbinlog.result
+++ b/mysql-test/r/mysqlbinlog.result
@@ -18,7 +18,7 @@ flush logs;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -64,7 +64,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -97,7 +97,7 @@ Warning: The option '--position' is deprecated and will be removed in a future r
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -119,7 +119,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -165,7 +165,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -198,7 +198,7 @@ Warning: The option '--position' is deprecated and will be removed in a future r
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -220,7 +220,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1108844556/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
@@ -239,7 +239,7 @@ Warning: The option '--position' is deprecated and will be removed in a future r
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1108844556/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
@@ -299,7 +299,7 @@ ERROR 42000: PROCEDURE test.p1 does not exist
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -349,7 +349,7 @@ flush logs;
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -484,7 +484,7 @@ FLUSH LOGS;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1253783037/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -581,22 +581,22 @@ SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1266652094/*!*/;
 SavePoint mixed_cases
 /*!*/;
-use db1/*!*/;
+use `db1`/*!*/;
 SET TIMESTAMP=1266652094/*!*/;
 INSERT INTO db1.t2 VALUES("in savepoint mixed_cases")
 /*!*/;
 SET TIMESTAMP=1266652094/*!*/;
 INSERT INTO db1.t1 VALUES(40)
 /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1266652094/*!*/;
 ROLLBACK TO mixed_cases
 /*!*/;
-use db1/*!*/;
+use `db1`/*!*/;
 SET TIMESTAMP=1266652094/*!*/;
 INSERT INTO db1.t2 VALUES("after rollback to")
 /*!*/;
@@ -624,7 +624,7 @@ SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1266652094/*!*/;
 SavePoint mixed_cases
 /*!*/;
diff --git a/mysql-test/r/mysqlbinlog2.result b/mysql-test/r/mysqlbinlog2.result
index dba9bdc9d70..ab97f0fe51b 100644
--- a/mysql-test/r/mysqlbinlog2.result
+++ b/mysql-test/r/mysqlbinlog2.result
@@ -19,7 +19,7 @@ insert into t1 values(null, "f");
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -62,7 +62,7 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
 SET INSERT_ID=1/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -101,7 +101,7 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
 SET INSERT_ID=4/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -127,7 +127,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -162,7 +162,7 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
 SET INSERT_ID=4/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -185,7 +185,7 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
 SET INSERT_ID=3/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -215,7 +215,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -246,7 +246,7 @@ flush logs;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -281,7 +281,7 @@ insert into t1 values(null, "e")
 DELIMITER ;
 DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -304,7 +304,7 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
 SET INSERT_ID=1/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -335,7 +335,7 @@ insert into t1 values(null, "e")
 DELIMITER ;
 DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -358,7 +358,7 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
 SET INSERT_ID=4/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -377,7 +377,7 @@ insert into t1 values(null, "e")
 DELIMITER ;
 DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -399,7 +399,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -445,7 +445,7 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
 SET INSERT_ID=3/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -468,7 +468,7 @@ insert into t1 values(null, "e")
 DELIMITER ;
 DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -490,7 +490,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -520,7 +520,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -563,7 +563,7 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
 SET INSERT_ID=1/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -601,7 +601,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 SET INSERT_ID=4/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -627,7 +627,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -661,7 +661,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 SET INSERT_ID=4/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -684,7 +684,7 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
 SET INSERT_ID=3/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -714,7 +714,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -744,7 +744,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -779,7 +779,7 @@ insert into t1 values(null, "e")
 DELIMITER ;
 DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -802,7 +802,7 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
 SET INSERT_ID=1/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -833,7 +833,7 @@ insert into t1 values(null, "e")
 DELIMITER ;
 DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -855,7 +855,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 SET INSERT_ID=4/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -874,7 +874,7 @@ insert into t1 values(null, "e")
 DELIMITER ;
 DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -896,7 +896,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -942,7 +942,7 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
 SET INSERT_ID=3/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -965,7 +965,7 @@ insert into t1 values(null, "e")
 DELIMITER ;
 DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -987,7 +987,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -1017,7 +1017,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
diff --git a/mysql-test/r/mysqlbinlog_row.result b/mysql-test/r/mysqlbinlog_row.result
index 9b562ac0fff..862aa4048fa 100644
--- a/mysql-test/r/mysqlbinlog_row.result
+++ b/mysql-test/r/mysqlbinlog_row.result
@@ -336,7 +336,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
diff --git a/mysql-test/r/mysqlbinlog_row_innodb.result b/mysql-test/r/mysqlbinlog_row_innodb.result
index ee448311278..d670acfdf75 100644
--- a/mysql-test/r/mysqlbinlog_row_innodb.result
+++ b/mysql-test/r/mysqlbinlog_row_innodb.result
@@ -2253,7 +2253,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -3876,7 +3876,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -4243,7 +4243,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -4804,7 +4804,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
diff --git a/mysql-test/r/mysqlbinlog_row_myisam.result b/mysql-test/r/mysqlbinlog_row_myisam.result
index b9366d941f8..8e809463042 100644
--- a/mysql-test/r/mysqlbinlog_row_myisam.result
+++ b/mysql-test/r/mysqlbinlog_row_myisam.result
@@ -2253,7 +2253,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -3898,7 +3898,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -4271,7 +4271,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -4842,7 +4842,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
diff --git a/mysql-test/r/mysqlbinlog_row_trans.result b/mysql-test/r/mysqlbinlog_row_trans.result
index 9c3348a9e76..d76a7884d92 100644
--- a/mysql-test/r/mysqlbinlog_row_trans.result
+++ b/mysql-test/r/mysqlbinlog_row_trans.result
@@ -132,7 +132,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
diff --git a/mysql-test/r/user_var-binlog.result b/mysql-test/r/user_var-binlog.result
index 05efea79fe7..c1effffde53 100644
--- a/mysql-test/r/user_var-binlog.result
+++ b/mysql-test/r/user_var-binlog.result
@@ -19,7 +19,7 @@ flush logs;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
 SET @`a b`:=_latin1 0x68656C6C6F COLLATE `latin1_swedish_ci`/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=10000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
index b62fc7e0a8c..c0c8329e3a7 100644
--- a/mysql-test/r/variables.result
+++ b/mysql-test/r/variables.result
@@ -76,7 +76,7 @@ explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select (@t1:=((@t2:=1) + (@t3:=4))) AS `@t1:=(@t2:=1)+@t3:=4`,(@t1) AS `@t1`,(@t2) AS `@t2`,(@t3) AS `@t3`
+Note	1003	select (@t1:=((@t2:=1) + (@t3:=4))) AS `@t1:=(@t2:=1)+@t3:=4`,(@`t1`) AS `@t1`,(@`t2`) AS `@t2`,(@`t3`) AS `@t3`
 select @t5;
 @t5
 1.23456
-- 
cgit v1.2.1


From 4d2b05b7d795bfa980065b4f4465444549401c9f Mon Sep 17 00:00:00 2001
From: unknown <sanja@montyprogram.com>
Date: Sat, 25 Aug 2012 09:15:57 +0300
Subject: fix for MDEV-367

The problem was that was_null and null_value variables was reset in each reexecution of IN subquery, but engine rerun only for non-constant subqueries.

Fixed checking constant in Item_equal sort.
Fix constant reporting in Item_subselect.
---
 mysql-test/r/subselect.result             | 26 ++++++++++++++++++++++++++
 mysql-test/r/subselect_no_mat.result      | 26 ++++++++++++++++++++++++++
 mysql-test/r/subselect_no_opts.result     | 26 ++++++++++++++++++++++++++
 mysql-test/r/subselect_no_scache.result   | 26 ++++++++++++++++++++++++++
 mysql-test/r/subselect_no_semijoin.result | 26 ++++++++++++++++++++++++++
 5 files changed, 130 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 024dca5d1bb..5126e6b8d5b 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6137,5 +6137,31 @@ NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL
 SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
 min_a	a
 drop table t1;
+#
+# MDEV-367: Different results with and without subquery_cache on
+# a query with a constant NOT IN condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+set @mdev367_optimizer_switch = @@optimizer_switch;
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+DROP TABLE t1;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index e8c084f9700..ad56c1c2f49 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6136,6 +6136,32 @@ NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL
 SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
 min_a	a
 drop table t1;
+#
+# MDEV-367: Different results with and without subquery_cache on
+# a query with a constant NOT IN condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+set @mdev367_optimizer_switch = @@optimizer_switch;
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+DROP TABLE t1;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index ace1f198853..92bd97593b3 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6132,6 +6132,32 @@ NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL
 SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
 min_a	a
 drop table t1;
+#
+# MDEV-367: Different results with and without subquery_cache on
+# a query with a constant NOT IN condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+set @mdev367_optimizer_switch = @@optimizer_switch;
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+DROP TABLE t1;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 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 32f24f9cf14..7e2ae9606ef 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6143,6 +6143,32 @@ NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL
 SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
 min_a	a
 drop table t1;
+#
+# MDEV-367: Different results with and without subquery_cache on
+# a query with a constant NOT IN condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+set @mdev367_optimizer_switch = @@optimizer_switch;
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+DROP TABLE t1;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 3860bb9fb18..53b914a5af0 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6132,6 +6132,32 @@ NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL
 SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
 min_a	a
 drop table t1;
+#
+# MDEV-367: Different results with and without subquery_cache on
+# a query with a constant NOT IN condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+set @mdev367_optimizer_switch = @@optimizer_switch;
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+DROP TABLE t1;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set @optimizer_switch_for_subselect_test=null;
-- 
cgit v1.2.1


From 2d99ea454fa684dc2aeeecd1a978728482bf34b3 Mon Sep 17 00:00:00 2001
From: Sergey Petrunya <psergey@askmonty.org>
Date: Tue, 28 Aug 2012 15:15:05 +0400
Subject: MDEV-430: Server crashes in select_describe on EXPLAIN with
 materialization+semijoin, etc - Don't do early cleanup of uncorrelated
 subqueries if we're running an EXPLAIN.

---
 mysql-test/r/subselect.result             | 18 ++++++++++++++++--
 mysql-test/r/subselect_no_mat.result      | 18 ++++++++++++++++--
 mysql-test/r/subselect_no_opts.result     | 18 ++++++++++++++++--
 mysql-test/r/subselect_no_scache.result   | 18 ++++++++++++++++--
 mysql-test/r/subselect_no_semijoin.result | 18 ++++++++++++++++--
 5 files changed, 80 insertions(+), 10 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 8c71c09c1ac..73af2119ddd 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -5966,7 +5966,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6127,7 +6127,7 @@ set optimizer_switch=@tmp_optimizer_switch;
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 
 DROP TABLE t1;
 #
@@ -6806,5 +6806,19 @@ Table_schema	Table_name	Index_name	Rows_read
 test	t2	b	1
 set global userstat=@tmp_mdev410;
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-430: Server crashes in select_describe on EXPLAIN with 
+#    materialization+semijoin, 2 nested subqueries, aggregate functions
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 48ab96dcaaa..f8d41890c7b 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -5967,7 +5967,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6126,7 +6126,7 @@ set optimizer_switch=@tmp_optimizer_switch;
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 
 DROP TABLE t1;
 #
@@ -6804,6 +6804,20 @@ Table_schema	Table_name	Index_name	Rows_read
 test	t2	b	1
 set global userstat=@tmp_mdev410;
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-430: Server crashes in select_describe on EXPLAIN with 
+#    materialization+semijoin, 2 nested subqueries, aggregate functions
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 9ab4e23b091..e65a5485caf 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -5963,7 +5963,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6122,7 +6122,7 @@ set optimizer_switch=@tmp_optimizer_switch;
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 
 DROP TABLE t1;
 #
@@ -6801,6 +6801,20 @@ Table_schema	Table_name	Index_name	Rows_read
 test	t2	b	1
 set global userstat=@tmp_mdev410;
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-430: Server crashes in select_describe on EXPLAIN with 
+#    materialization+semijoin, 2 nested subqueries, aggregate functions
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 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 d47aa956ead..bee41745efd 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -5972,7 +5972,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6133,7 +6133,7 @@ set optimizer_switch=@tmp_optimizer_switch;
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 
 DROP TABLE t1;
 #
@@ -6812,6 +6812,20 @@ Table_schema	Table_name	Index_name	Rows_read
 test	t2	b	1
 set global userstat=@tmp_mdev410;
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-430: Server crashes in select_describe on EXPLAIN with 
+#    materialization+semijoin, 2 nested subqueries, aggregate functions
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 96e6c2182fa..886e6882a6f 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -5963,7 +5963,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6122,7 +6122,7 @@ set optimizer_switch=@tmp_optimizer_switch;
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 
 DROP TABLE t1;
 #
@@ -6801,6 +6801,20 @@ Table_schema	Table_name	Index_name	Rows_read
 test	t2	b	1
 set global userstat=@tmp_mdev410;
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-430: Server crashes in select_describe on EXPLAIN with 
+#    materialization+semijoin, 2 nested subqueries, aggregate functions
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set @optimizer_switch_for_subselect_test=null;
-- 
cgit v1.2.1


From 97bd8d89098897ffc33a063c8ab8a31ee5ef412e Mon Sep 17 00:00:00 2001
From: Sergey Petrunya <psergey@askmonty.org>
Date: Tue, 28 Aug 2012 15:40:38 +0400
Subject: MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with
 GROUP BY and HAVING in EXISTS subquery - Testcase

---
 mysql-test/r/subselect.result             | 13 +++++++++++++
 mysql-test/r/subselect_no_mat.result      | 13 +++++++++++++
 mysql-test/r/subselect_no_opts.result     | 13 +++++++++++++
 mysql-test/r/subselect_no_scache.result   | 13 +++++++++++++
 mysql-test/r/subselect_no_semijoin.result | 13 +++++++++++++
 5 files changed, 65 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 510555d2e50..f775336299b 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6846,3 +6846,16 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
+#
+# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index b165226aeb0..e72d25fdafa 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6844,6 +6844,19 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
+#
+# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+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 f8230baec1e..9030265356b 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6841,4 +6841,17 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
+#
+# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+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 22b148e7615..e68f5990c08 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6852,6 +6852,19 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
+#
+# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+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 81aa60df4d7..f0ce541294a 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6841,5 +6841,18 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
+#
+# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 set @optimizer_switch_for_subselect_test=null;
 set @join_cache_level_for_subselect_test=NULL;
-- 
cgit v1.2.1


From b6eccf51c05105fb56275b1fa2055c4a14fe7459 Mon Sep 17 00:00:00 2001
From: Sergey Petrunya <psergey@askmonty.org>
Date: Tue, 28 Aug 2012 16:03:22 +0400
Subject: Update test results (checked)

---
 mysql-test/r/explain.result             | 10 +++++-----
 mysql-test/r/group_min_max.result       | 10 +++++-----
 mysql-test/r/key.result                 |  2 +-
 mysql-test/r/limit_rows_examined.result |  2 +-
 4 files changed, 12 insertions(+), 12 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index e081b56e6ce..919e29f185e 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -287,7 +287,7 @@ WHERE t1.f1 GROUP BY t1.f1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
 2	SUBQUERY	a	system	NULL	NULL	NULL	NULL	1	
-2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	
+2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	Using where
 PREPARE stmt FROM
 'EXPLAIN SELECT 1 FROM t1
  WHERE 1 > ALL((SELECT t1.f1 FROM t1 RIGHT OUTER JOIN t1 a
@@ -297,12 +297,12 @@ EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
 2	SUBQUERY	a	system	NULL	NULL	NULL	NULL	1	
-2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	
+2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	Using where
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
 2	SUBQUERY	a	system	NULL	NULL	NULL	NULL	1	
-2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	
+2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	Using where
 DEALLOCATE PREPARE stmt;
 PREPARE stmt FROM
 'EXPLAIN SELECT 1 FROM t1
@@ -313,12 +313,12 @@ EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
 2	SUBQUERY	a	system	NULL	NULL	NULL	NULL	1	
-2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	
+2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	Using where
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
 2	SUBQUERY	a	system	NULL	NULL	NULL	NULL	1	
-2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	
+2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	Using where
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
 End of 5.1 tests.
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 95caa6853b8..ce0793b70a6 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -2403,7 +2403,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	NULL	a	10	NULL	15	Using index
+2	SUBQUERY	t1	range	NULL	a	5	NULL	8	Using index for group-by
 EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
 a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -2421,7 +2421,7 @@ AND t1_outer1.b = t1_outer2.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1_outer1	ref	a	a	5	const	1	Using where; Using index
 1	PRIMARY	t1_outer2	index	NULL	a	10	NULL	15	Using where; Using index; Using join buffer (flat, BNL join)
-2	SUBQUERY	t1	index	NULL	a	10	NULL	15	Using index
+2	SUBQUERY	t1	range	NULL	a	5	NULL	8	Using index for group-by
 EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
 FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -2757,7 +2757,7 @@ EXPLAIN
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
-x	x	x	x	x	x	x	x	x	Using index
+x	x	x	x	x	x	x	x	x	Using where; Using index
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 MIN( a )
 NULL
@@ -2829,7 +2829,7 @@ EXPLAIN
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
-x	x	x	x	x	x	x	x	x	Using index
+x	x	x	x	x	x	x	x	x	Using where; Using index
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 MIN( a )
 NULL
@@ -2908,7 +2908,7 @@ EXPLAIN
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
-x	x	x	x	x	x	x	x	x	Using index
+x	x	x	x	x	x	x	x	x	Using where; Using index
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 MIN( a )
 NULL
diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result
index e215caadb1c..e63afeab126 100644
--- a/mysql-test/r/key.result
+++ b/mysql-test/r/key.result
@@ -599,7 +599,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	NULL	a	10	NULL	15	Using index
+2	SUBQUERY	t1	range	NULL	a	5	NULL	8	Using index for group-by
 SELECT 1 as RES FROM t1 AS t1_outer WHERE 
 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
 RES
diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result
index 3a5212818ef..f4242f17a14 100644
--- a/mysql-test/r/limit_rows_examined.result
+++ b/mysql-test/r/limit_rows_examined.result
@@ -679,7 +679,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	Using temporary
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Distinct
 2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	
-2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
 SELECT DISTINCT a AS field1 FROM t1, t2 
 WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d) 
 HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20;
-- 
cgit v1.2.1