# This Tests Bug fixes of Myisam engine specific, Bug#56592
# BUG#11783262,Bug#11808582,Bug#12726927
# All tests are required to run with Myisam.
# Hence MTR starts mysqld with MyISAM as default

--source include/force_myisam_default.inc
--source include/have_myisam.inc

# Initialize
--disable_warnings
drop table if exists t1,t2,t3;
--enable_warnings

select * from (select 2 from DUAL) b;
-- error 1054
SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b;
-- error 1054
SELECT 1 as a FROM (SELECT a UNION SELECT 1) b;
CREATE TABLE t1 (a int not null, b char (10) not null);
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
CREATE TABLE t2 (a int not null, b char (10) not null);
insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');
select t1.a,t3.y from t1,(select a as y from t2  where b='c') as t3  where t1.a = t3.y;
select t1.a,t3.a from t1,(select * from t2  where b='c') as t3  where t1.a = t3.a;
CREATE TABLE t3 (a int not null, b char (10) not null);
insert into t3 values (3,'f'),(4,'y'),(5,'z'),(6,'c');
select t1.a,t4.y from t1,(select t2.a as y from t2,(select t3.b from t3 where t3.a>3) as t5  where t2.b=t5.b) as t4  where t1.a = t4.y;
--error 1054
SELECT a FROM (SELECT 1 FROM (SELECT 1) a HAVING a=1) b;
--error 1052
SELECT a,b as a FROM (SELECT '1' as a,'2' as b) b  HAVING a=1;
SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=2;
SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=1;
--error 1054
SELECT 1 FROM (SELECT 1) a WHERE a=2;
--error 1054
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1  HAVING a=1) as a;
--sorted_result
select * from t1 as x1, (select * from t1) as x2;
explain select * from t1 as x1, (select * from t1) as x2;
drop table if exists  t2,t3;
select * from (select 1) as a;
select a from (select 1 as a) as b;
select 1 from (select 1) as a;
select * from (select * from t1 union select * from t1) a;
select * from (select * from t1 union all select * from t1) a;
select * from (select * from t1 union all select * from t1 limit 2) a;
select * from (select * from t1 intersect all select * from t1 order by a,b limit 2) a;
select * from (select * from t1 except all select * from t1 limit 2) a;
explain select * from (select * from t1 union select * from t1) a;
explain select * from (select * from t1 union all select * from t1) a;
CREATE TABLE t2 (a int not null);
insert into t2 values(1);
select * from (select * from t1 where t1.a=(select a from t2 where t2.a=t1.a)) a;
select * from (select * from t1 where t1.a=(select t2.a from t2 where t2.a=t1.a) union select t1.a, t1.b from t1) a;
explain select * from (select t1.*, t2.a as t2a from t1,t2 where t1.a=t2.a) t1;
drop table t1, t2;
create table t1(a int not null, t char(8), index(a));
disable_query_log;
let $1 = 10000;
while ($1)
 {
  eval insert into t1 values ($1,'$1');
  dec $1;
 }
enable_query_log;
SELECT * FROM (SELECT * FROM t1) as b ORDER BY a  ASC LIMIT 0,20;
explain select count(*) from t1 as tt1, (select * from t1) as tt2;
drop table t1;
SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b;
select * from (select 1 as a) b  left join (select 2 as a) c using(a);
--error 1054
SELECT * FROM (SELECT 1 UNION SELECT a) b;
--error 1054
SELECT 1 as a FROM (SELECT a UNION SELECT 1) b;
--error 1054
SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b;
--error 1054
select 1 from  (select 2) a order by 0;

#
# Test of explain (bug #251)
#

create table t1 (id int);
insert into t1 values (1),(2),(3);
describe select * from (select * from t1 group by id) bar;
drop table t1;

#
# test->used_keys test for derived tables
#
create table t1 (mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL) charset utf8mb4;
create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED NOT NULL);
insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), (NULL, 'i', 9);
insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);

analyze table t1, t2;

--sorted_result
SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
--sorted_result
SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2  INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;

explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2  INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
drop table t1,t2;

#
# derived table reference
#
SELECT a.x FROM (SELECT 1 AS x) AS a HAVING a.x = 1;

#
# Test for select if database is not selected.
#
# Connect without a database as user mysqltest_1
create user mysqltest_1;
create table t1 select 1 as a;
connect (con1,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK);
connection con1;
--error 1046
select 2 as a from (select * from t1) b;
use test;
select 2 as a from (select * from t1) b;
drop table t1;
--error 1064
select mail_id,  if(folder.f_description!='', folder.f_description, folder.f_name) as folder_name,  date, address_id, phrase, address,  subject from folder, (select  mail.mail_id as mail_id,  date_format(mail.h_date, '%b %e, %Y %h:%i') as date,  mail.folder_id,  sender.address_id as address_id,  sender.phrase as phrase, sender.address as address,    mail.h_subject as subject from    mail left join mxa as mxa_sender on mail.mail_id=mxa_sender.mail_id and mxa_sender.type='from' left join address as sender on mxa_sender.address_id=sender.address_id  mxa as mxa_recipient,   address as recipient, where 1     and mail.mail_id=mxa_recipient.mail_id   and mxa_recipient.address_id=recipient.address_id   and mxa_recipient.type='to'  and  match(sender.phrase, sender.address, sender.comment) against ('jeremy' in boolean mode)   and  match(recipient.phrase, recipient.address, recipient.comment) against ('monty' in boolean mode) order by mail.h_date desc limit 0, 25 ) as query where query.folder_id=folder.folder_id;

#
# UPDATE/DELETE/INSERT of derived tables
#
create table t1 (a int);
insert into t1 values (1),(2),(3);
-- error 1288
update (select * from t1) as t1 set a = 5;
-- error 1288
update (select * from t1) as t1, t1 as t2 set t1.a = 5;
-- error 1064
delete from (select * from t1);
-- error 1064
delete from (select * from t1) as t1, t1 as t2;

-- error 1064
insert into  (select * from t1) values (5);
drop table t1;

#
# deived tables with subquery inside all by one table
#
create table t1 (E1 INTEGER UNSIGNED NOT NULL, E2 INTEGER UNSIGNED NOT NULL, E3 INTEGER UNSIGNED NOT NULL, PRIMARY KEY(E1)
);
insert into t1 VALUES(1,1,1), (2,2,1);
select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS themax ON t1.E1 = themax.E2 AND t1.E1 = t1.E2;
explain select count(*) from t1 INNER JOIN (SELECT a.E1, a.E2, a.E3 FROM t1 AS a WHERE a.E3 = (SELECT max(b.E3) FROM t1 AS b WHERE a.E2 = b.E2)) AS themax ON t1.E1 = themax.E2 AND t1.E1 = t1.E2;
drop table t1;

create table t1 (a int);
insert into t1 values (1),(2);
--sorted_result
select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
drop table t1;

#
# multi-update & multi-delete with derived tables
#
CREATE TABLE `t1` (
  `N` int(11) unsigned NOT NULL default '0',
  `M` tinyint(1) default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `t1` (N, M) VALUES (1, 0),(1, 0),(1, 0),(2, 0),(2, 0),(3, 0);
UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2;
select * from t1;
-- error 1288
UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2, P2.N = 2;
-- error 1054
UPDATE `t1` AS P1 INNER JOIN (SELECT aaaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2;
delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
select * from t1;
--replace_result P2 p2
--error ER_NON_UPDATABLE_TABLE
delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
-- error 1054
delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
drop table t1;

#
# correct lex->current_select
#
CREATE TABLE t1 (
  OBJECTID int(11) NOT NULL default '0',
  SORTORDER int(11) NOT NULL auto_increment,
  KEY t1_SortIndex (SORTORDER),
  KEY t1_IdIndex (OBJECTID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE t2 (
  ID int(11) default NULL,
  PARID int(11) default NULL,
  UNIQUE KEY t2_ID_IDX (ID),
  KEY t2_PARID_IDX (PARID)
) engine=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
CREATE TABLE t3 (
  ID int(11) default NULL,
  DATA decimal(10,2) default NULL,
  UNIQUE KEY t3_ID_IDX (ID)
) engine=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
select 497, TMP.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA      from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as TMP;
drop table t1, t2, t3;

#
# explain derived
#
CREATE TABLE t1 (name char(1) default NULL, val int(5) default NULL);
INSERT INTO t1 VALUES ('a',1),  ('a',2),  ('a',2),  ('a',2),  ('a',3),  ('a',6), ('a',7), ('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20), ('b',2), ('b',3), ('b',4), ('b',5);
SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
drop table t1;

#
# "Using index" in explain
#
create table t2 (a int, b int, primary key (a));
insert into t2 values (1,7),(2,7);
explain select a from t2 where a>1;
explain select a from (select a from t2 where a>1) tt;
drop table t2;

#
# select list counter
#
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE `t1` ( `itemid` int(11) NOT NULL default '0', `grpid` varchar(15) NOT NULL default '', `vendor` int(11) NOT NULL default '0', `date_` date NOT NULL default '0000-00-00', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY  (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`));
insert into t1 values (128, 'rozn', 2, curdate(), 10),
  (128, 'rozn', 1, curdate(), 10);
SELECT MIN(price) min, MAX(price) max, AVG(price) avg FROM (SELECT SUBSTRING( MAX(concat(date_,";",price)), 12) price FROM t1 WHERE itemid=128 AND  grpid='rozn' GROUP BY itemid, grpid, vendor) lastprices;
DROP TABLE t1;
SET sql_mode = default;
#
# DISTINCT over grouped select on subquery in the FROM clause
#
create table t1 (a integer, b integer);
insert into t1 values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1);
select distinct sum(b) from t1 group by a;
select distinct sum(b) from (select a,b from t1) y group by a;
drop table t1;


#
# Test for bug #7413 "Subquery with non-scalar results participating in
# select list of derived table crashes server" aka "VIEW with sub query can
# cause the MySQL server to crash". If we have encountered problem during
# filling of derived table we should report error and perform cleanup
# properly.
#
CREATE TABLE t1 (a char(10), b char(10));
INSERT INTO t1 VALUES ('root','localhost'), ('root','%');
--error 1242
SELECT * FROM (SELECT (SELECT a.a FROM t1 AS a WHERE a.a = b.a) FROM t1 AS b) AS c;
DROP TABLE t1;
#
# test of union subquery in the FROM clause with complex distinct/all (BUG#6565)
#
create table t1(a int);
create table t2(a int);
create table t3(a int);
insert into t1 values(1),(1);
insert into t2 values(2),(2);
insert into t3 values(3),(3);
select * from t1 union distinct select * from t2 union all select * from t3;
select * from (select * from t1 union distinct select * from t2 union all select * from t3) X;
drop table t1, t2, t3;

#
# Bug #11864 non unique names are allowed in subquery
#
create table t1 (a int);
create table t2 (a int);
--error 1060
select * from (select * from t1,t2) foo;
drop table t1,t2;

#
# Bug#10586 - query works with 4.1.8, but not with 4.1.11
#
create table t1 (ID int unsigned not null auto_increment,
                 DATA varchar(5) not null, primary key (ID));
create table t2 (ID int unsigned not null auto_increment,
                 DATA varchar(5) not null, FID int unsigned not null,
                 primary key (ID));
select A.* from (t1 inner join (select * from t2) as A on t1.ID = A.FID);
select t2.* from ((select * from t1) as A inner join t2 on A.ID = t2.FID);
select t2.* from (select * from t1) as A inner join t2 on A.ID = t2.FID;
drop table t1, t2;

connection con1;
disconnect con1;
--source include/wait_until_disconnected.inc
connection default;
drop user mysqltest_1;

--echo # End of 4.1 tests

#
# Bug #41156: List of derived tables acts like a chain of
#             mutually-nested subqueries
#

SELECT 0 FROM
(SELECT 0) t01, (SELECT 0) t02, (SELECT 0) t03, (SELECT 0) t04, (SELECT 0) t05,
(SELECT 0) t06, (SELECT 0) t07, (SELECT 0) t08, (SELECT 0) t09, (SELECT 0) t10,
(SELECT 0) t11, (SELECT 0) t12, (SELECT 0) t13, (SELECT 0) t14, (SELECT 0) t15,
(SELECT 0) t16, (SELECT 0) t17, (SELECT 0) t18, (SELECT 0) t19, (SELECT 0) t20,
(SELECT 0) t21, (SELECT 0) t22, (SELECT 0) t23, (SELECT 0) t24, (SELECT 0) t25,
(SELECT 0) t26, (SELECT 0) t27, (SELECT 0) t28, (SELECT 0) t29, (SELECT 0) t30,
(SELECT 0) t31, (SELECT 0) t32, (SELECT 0) t33, (SELECT 0) t34, (SELECT 0) t35,
(SELECT 0) t36, (SELECT 0) t37, (SELECT 0) t38, (SELECT 0) t39, (SELECT 0) t40,
(SELECT 0) t41, (SELECT 0) t42, (SELECT 0) t43, (SELECT 0) t44, (SELECT 0) t45,
(SELECT 0) t46, (SELECT 0) t47, (SELECT 0) t48, (SELECT 0) t49, (SELECT 0) t50,
(SELECT 0) t51, (SELECT 0) t52, (SELECT 0) t53, (SELECT 0) t54, (SELECT 0) t55,
(SELECT 0) t56, (SELECT 0) t57, (SELECT 0) t58, (SELECT 0) t59, (SELECT 0) t60,
(SELECT 0) t61; # 61 == MAX_TABLES

--echo #
--echo #  A nested materialized derived table is used before being populated.
--echo #  (addon for bug#19077)
--echo #

CREATE TABLE t1 (i INT, j BIGINT);
INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2);
SELECT * FROM (SELECT MIN(i) FROM t1
WHERE j = SUBSTRING('12', (SELECT * FROM (SELECT MIN(j) FROM t1) t2))) t3;
DROP TABLE t1;

--echo # End of 5.0 tests

--echo #
--echo # Bug#55586: Crash JOIN of two subqueries in FROM + ORDER BY and GROUP BY
--echo #
CREATE TABLE C (
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
);

INSERT INTO C VALUES (2,'w','w');
INSERT INTO C VALUES (2,'d','d');
--sorted_result
SELECT SUM(DISTINCT table2.col_int_key) field1,
       table1.col_varchar_key field2
FROM
  (SELECT * FROM C  ) table1
  JOIN (SELECT * FROM C  ) table2
  ON table2 .`col_varchar_key` = table1 .`col_varchar_nokey`
GROUP  BY field2
ORDER  BY field1;

DROP TABLE C;
--echo # End of test for bug#55586

--echo #
--echo # Bug#55561: Crash on JOIN with 2 FROM subqueries
--echo #
CREATE TABLE C (
  col_int int DEFAULT NULL,
  col_varchar varchar(1) DEFAULT NULL
);

INSERT INTO `C` VALUES (0,NULL);
INSERT INTO `C` VALUES (5,'y');

SELECT table1.col_varchar
FROM
 ( SELECT * FROM C  ) table1
 JOIN ( SELECT * FROM C  ) table2  ON table2.col_varchar = table1.col_varchar
WHERE
  table2.col_varchar < table2.col_varchar
  AND table1.col_varchar != 'k'
LIMIT  1;
DROP TABLE C;
--echo # End on bug#55561

--echo #
--echo # Bug#56233: Hang during key generation for derived tables
--echo #
CREATE TABLE C (
  col_varchar_10_key varchar(10) DEFAULT NULL,
  col_int_key int DEFAULT NULL,
  pk int NOT NULL AUTO_INCREMENT,
  col_date_key date DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_varchar_10_key` (`col_varchar_10_key`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_date_key` (`col_date_key`)
);

INSERT INTO C VALUES ('ok',3,1,'2003-04-02');

CREATE ALGORITHM=TEMPTABLE VIEW viewC AS SELECT * FROM C;

SELECT  table1.col_date_key AS field1
FROM
  C AS table1
WHERE
  (table1.col_int_key <=ANY
    ( SELECT SUBQUERY1_t1.col_int_key
      FROM viewC AS SUBQUERY1_t1
      WHERE SUBQUERY1_t1.col_varchar_10_key <= table1.col_varchar_10_key
    )
  )
;
DROP TABLE C;
DROP VIEW viewC;
--echo #

--echo #
--echo # Bug#55950: FROM Subquery joined by 2 varchar fields returns empty
--echo #            set
--echo #
CREATE TABLE `cc` (
  `i1` varchar(1) DEFAULT NULL,
  `i2` varchar(1) DEFAULT NULL
) charset utf8mb4;

INSERT INTO `cc` VALUES ('m','m');
INSERT INTO `cc` VALUES ('c','c');

CREATE TABLE `C` (
  `o1` varchar(1) DEFAULT NULL
) charset utf8mb4;

INSERT INTO `C` VALUES ('m');

SELECT table1 . o1
FROM C table1
  JOIN ( C table2
    JOIN ( SELECT * FROM cc ) table3
    ON table3 .`i1`  = table2 .o1
  ) ON table3 .`i2`  = table2 .o1
;

SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";

--echo # Ref access to the derived table should be used.
EXPLAIN SELECT table1 . o1
FROM C table1
  JOIN ( C table2
    JOIN ( SELECT * FROM cc ) table3
    ON table3 .`i1`  = table2 .o1
  ) ON table3 .`i2`  = table2 .o1
;

# Test EXPLAIN on MaterializeIterator.
--skip_if_hypergraph  # Depends on the query plan.
EXPLAIN FORMAT=tree SELECT table1 . o1
FROM C table1
  JOIN ( C table2
    JOIN ( SELECT * FROM cc ) table3
    ON table3 .`i1`  = table2 .o1
  ) ON table3 .`i2`  = table2 .o1
;

SET @@optimizer_switch=@optimizer_switch_saved;

DROP TABLE cc;
DROP TABLE C;
--echo # End of test for bug#55950

--echo #
--echo # Bug#56592: Subquery with DISTINCT in FROM clause returns only partial
--echo # result
--echo #
CREATE TABLE `t1` (
  `pk` int(11) NOT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (2, 9, NULL), (3, 3, '1900-01-01 00:00:00'),
(8, 8, '1900-01-01 00:00:00'), (15, 0, '2007-12-15 12:39:34');

--sorted_result
SELECT * FROM (
  SELECT DISTINCT tableb.col_datetime_key
  FROM t1 tablea LEFT JOIN t1 tableb ON tablea.pk < tableb.col_int_key
) AS from_subquery;

EXPLAIN SELECT * FROM (
  SELECT DISTINCT tableb.col_datetime_key
  FROM t1 tablea LEFT JOIN t1 tableb ON tablea.pk < tableb.col_int_key
) AS from_subquery;

EXPLAIN SELECT * FROM (
  SELECT DISTINCT tablea.col_datetime_key
  FROM t1 tablea LEFT JOIN t1 tableb ON tablea.pk < tableb.col_int_key
) AS from_subquery;

DROP TABLE t1;

--echo #
--echo # Bug#58730 Assertion failed: table->key_read == 0 in close_thread_table,
--echo #           temptable views
--echo #

CREATE TABLE t1 (a INT);
CREATE TABLE t2 (b INT, KEY (b));
INSERT INTO t1 VALUES (1),(1);
INSERT INTO t2 VALUES (1),(1);

CREATE algorithm=temptable VIEW v1 AS
  SELECT 1 FROM t1 LEFT JOIN t1 t3 ON 1 > (SELECT 1 FROM t1);
CREATE algorithm=temptable VIEW v2 AS SELECT 1 FROM t2;

# This caused the assert to be triggered.
--error ER_SUBQUERY_NO_1_ROW
EXPLAIN SELECT 1 FROM t1 JOIN v1 ON 1 > (SELECT 1 FROM v2);

DROP TABLE t1, t2;
DROP VIEW v1, v2;

--echo #
--echo # WL#5274: Postpone materialization of views/subqueries in FROM clause.
--echo #          Additional tests.
--echo #

CREATE TABLE t1(f1 int, f11 int);
CREATE TABLE t2(f2 int, f22 int);
INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(5,5),(9,9),(7,7);
INSERT INTO t2 VALUES(1,1),(3,3),(2,2),(4,4),(8,8),(6,6);

--echo for merged derived tables
--echo  explain for simple derived
EXPLAIN SELECT * FROM (SELECT * FROM t1) tt;
SELECT * FROM (SELECT * FROM t1) tt;
--echo  explain for multitable derived
EXPLAIN SELECT * FROM (SELECT * FROM t1 JOIN t2 ON f1=f2) tt;
--sorted_result
SELECT * FROM (SELECT * FROM t1 JOIN t2 ON f1=f2) tt;
--echo  explain for derived with where
FLUSH STATUS;
EXPLAIN
  SELECT * FROM (SELECT * FROM t1 WHERE f1 IN (2,3)) tt WHERE f11=2;
--skip_if_hypergraph  # Depends on the query plan.
SHOW STATUS LIKE 'Handler_read%';
FLUSH STATUS;
SELECT * FROM (SELECT * FROM t1 WHERE f1 IN (2,3)) tt WHERE f11=2;
--skip_if_hypergraph  # Depends on the query plan.
SHOW STATUS LIKE 'Handler_read%';

--echo join of derived
EXPLAIN
  SELECT * FROM (SELECT * FROM t1 WHERE f1 IN (2,3)) tt JOIN
  (SELECT * FROM t1 WHERE f1 IN (1,2)) aa ON tt.f1=aa.f1;
SELECT * FROM (SELECT * FROM t1 WHERE f1 IN (2,3)) tt JOIN
  (SELECT * FROM t1 WHERE f1 IN (1,2)) aa ON tt.f1=aa.f1;

--echo for merged views
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE VIEW v2 AS SELECT * FROM t1 JOIN t2 ON f1=f2;
CREATE VIEW v3 AS SELECT * FROM t1 WHERE f1 IN (2,3);
CREATE VIEW v4 AS SELECT * FROM t2 WHERE f2 IN (2,3);
--echo  explain for simple views
EXPLAIN SELECT * FROM v1;
--sorted_result
SELECT * FROM v1;
--echo  explain for multitable views
EXPLAIN SELECT * FROM v2;
--sorted_result
SELECT * FROM v2;
--echo  explain for views with where
EXPLAIN SELECT * FROM v3 WHERE f11 IN (1,3);
SELECT * FROM v3 WHERE f11 IN (1,3);
--echo explain for joined views
EXPLAIN
  SELECT * FROM v3 JOIN v4 ON f1=f2;
--sorted_result
SELECT * FROM v3 JOIN v4 ON f1=f2;

FLUSH STATUS;
EXPLAIN SELECT * FROM v4 WHERE f2 IN (1,3);
--skip_if_hypergraph  # Depends on the query plan.
SHOW STATUS LIKE 'Handler_read%';
FLUSH STATUS;
SELECT * FROM v4 WHERE f2 IN (1,3);
--skip_if_hypergraph  # Depends on the query plan.
SHOW STATUS LIKE 'Handler_read%';

# Lots of "SELECT * GROUP BY non-unique-field" below, so:
--source include/turn_off_only_full_group_by.inc

--echo for materialized derived tables
--echo  explain for simple derived
EXPLAIN SELECT * FROM (SELECT * FROM t1 GROUP BY f1) tt;
SELECT * FROM (SELECT * FROM t1 HAVING f1=f1) tt;
--echo  explain showing created indexes and late materialization
FLUSH STATUS;
EXPLAIN
  SELECT * FROM t1 JOIN (SELECT * FROM t2 GROUP BY f2) tt ON f1=f2;
--skip_if_hypergraph  # Depends on the query plan.
SHOW STATUS LIKE 'Handler_read%';
FLUSH STATUS;
SELECT * FROM t1 JOIN (SELECT * FROM t2 GROUP BY f2) tt ON f1=f2;
--skip_if_hypergraph  # Depends on the query plan.
SHOW STATUS LIKE 'Handler_read%';

--echo for materialized views
DROP VIEW v1,v2,v3;
CREATE VIEW v1 AS SELECT * FROM t1 GROUP BY f1;
CREATE VIEW v2 AS SELECT * FROM t2 GROUP BY f2;
CREATE VIEW v3 AS SELECT t1.f1,t1.f11 FROM t1 JOIN t1 AS t11 HAVING t1.f1<100;
--echo ensure that view definitions are cached in the data-dictionary cache.
--disable_result_log
--disable_query_log
SHOW CREATE VIEW v1;
SHOW CREATE VIEW v2;
SHOW CREATE VIEW v3;
--enable_query_log
--enable_result_log
--echo  explain for simple derived
EXPLAIN SELECT * FROM v1;
--sorted_result
SELECT * FROM v1;
--echo  explain showing created indexes and late materialization for views
FLUSH STATUS;
EXPLAIN SELECT * FROM t1 JOIN v2 ON f1=f2;
--skip_if_hypergraph  # Depends on the query plan.
SHOW STATUS LIKE 'Handler_read%';
FLUSH STATUS;
SELECT * FROM t1 JOIN v2 ON f1=f2;
--skip_if_hypergraph  # Depends on the query plan.
SHOW STATUS LIKE 'Handler_read%';

EXPLAIN
  SELECT * FROM t1,v3 AS v31,v3 WHERE t1.f1=v31.f1 and t1.f1=v3.f1;
FLUSH STATUS;
SELECT * FROM t1,v3 AS v31,v3 WHERE t1.f1=v31.f1 and t1.f1=v3.f1;
--skip_if_hypergraph  # Depends on the query plan.
SHOW STATUS LIKE 'Handler_read%';
--echo  explain showing late materialization for views
EXPLAIN SELECT * FROM v1 JOIN v4 ON f1=f2;
--sorted_result
SELECT * FROM v1 JOIN v4 ON f1=f2;

--echo merged derived in merged derived
EXPLAIN SELECT * FROM (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7) tt WHERE f1 > 2) zz;
--sorted_result
SELECT * FROM (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7) tt WHERE f1 > 2) zz;

--echo materialized derived in merged derived
EXPLAIN  SELECT * FROM (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2) zz;
--sorted_result
SELECT * FROM (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2) zz;

--echo merged derived in materialized derived
EXPLAIN SELECT * FROM (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7) tt WHERE f1 > 2 GROUP BY f1) zz;
--sorted_result
SELECT * FROM (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7) tt WHERE f1 > 2 GROUP BY f1) zz;

--echo materialized derived in materialized derived
EXPLAIN  SELECT * FROM (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2 GROUP BY f1) zz;
--sorted_result
SELECT * FROM (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2 GROUP BY f1) zz;

--echo mat in merged derived join mat in merged derived
EXPLAIN  SELECT * FROM
 (SELECT * FROM (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2) x
JOIN
 (SELECT * FROM (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2) z
 ON x.f1 = z.f1;

FLUSH STATUS;
SELECT * FROM
 (SELECT * FROM (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2) x
JOIN
 (SELECT * FROM (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2) z
 ON x.f1 = z.f1;
--skip_if_hypergraph  # Depends on the query plan.
SHOW STATUS LIKE 'Handler_read%';
FLUSH STATUS;

--echo merged in merged derived join merged in merged derived
EXPLAIN  SELECT * FROM
 (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7 ) tt WHERE f1 > 2 ) x
JOIN
 (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7 ) tt WHERE f1 > 2 ) z
 ON x.f1 = z.f1;

SELECT * FROM
 (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7 ) tt WHERE f1 > 2 ) x
JOIN
 (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7 ) tt WHERE f1 > 2 ) z
 ON x.f1 = z.f1;

--echo materialized in materialized derived join
--echo   materialized in materialized derived
EXPLAIN  SELECT * FROM
 (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2 GROUP BY f1) x
JOIN
 (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2 GROUP BY f1) z
 ON x.f1 = z.f1;

SELECT * FROM
 (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2 GROUP BY f1) x
JOIN
 (SELECT * FROM
  (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2 GROUP BY f1) z
 ON x.f1 = z.f1;

--echo merged view in materialized derived
EXPLAIN
SELECT * FROM (SELECT * FROM v4 GROUP BY 1) tt;
--sorted_result
SELECT * FROM (SELECT * FROM v4 GROUP BY 1) tt;

--echo materialized view in merged derived
# Condition f1 < 7 gets pushed down to view v1 here. However when the
# tranformed query is printed in "NOTE", it does not display the pushed
# condition in the view definition. It just chooses to print the original view
# definition only.
EXPLAIN
SELECT * FROM ( SELECT * FROM v1 WHERE f1 < 7) tt;
SELECT * FROM ( SELECT * FROM v1 WHERE f1 < 7) tt;

--echo merged view in a merged view in a merged derived
CREATE VIEW v6 AS SELECT * FROM v4 WHERE f2 < 7;
EXPLAIN SELECT * FROM (SELECT * FROM v6) tt;
SELECT * FROM (SELECT * FROM v6) tt;

--echo materialized view in a merged view in a materialized derived
CREATE VIEW v7 AS SELECT * FROM v1;
EXPLAIN SELECT * FROM (SELECT * FROM v7 GROUP BY 1) tt;
--sorted_result
SELECT * FROM (SELECT * FROM v7 GROUP BY 1) tt;

--echo JOIN of above two
EXPLAIN SELECT * FROM v6 JOIN v7 ON f2=f1;
--sorted_result
SELECT * FROM v6 JOIN v7 ON f2=f1;

--echo test two keys
CREATE TABLE t3(f3 INT, f33 INT);
INSERT INTO t1 VALUES(6,6),(8,8);
INSERT INTO t3 VALUES(1,1),(2,2),(3,3),(5,5);
let $query= SELECT * FROM t1 JOIN (SELECT * FROM t2) tt ON t1.f1=tt.f2
  JOIN t3 ON tt.f22=t3.f3;
eval EXPLAIN $query;
--sorted_result
eval $query;

--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
DROP TABLE t1,t2,t3;
DROP VIEW v1,v2,v3,v4,v6,v7;

--echo #

--echo #
--echo # BUG#11783262: CRASH IN ITEM_FIELD::ITEM_FIELD IN ITEM.CC ON SUBQUERY
--echo #               IN FROM WITH WL5274
--echo #

CREATE TABLE t1 (
  col_int_key INT,
  col_time_key time,
  col_varchar_key VARCHAR(1),
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=INNODB;
SELECT alias1.col_time_key AS field1
FROM ( ( SELECT SQ1_alias1.* FROM t1 AS SQ1_alias1 ) AS alias1
  INNER JOIN t1 AS alias2
  ON (alias2.col_int_key = alias1.col_int_key)
  )
WHERE alias1.col_int_key = 207
ORDER BY alias1.col_varchar_key, field1;

DROP TABLE t1;
--echo #
--echo # Bug#11807437: VALGRIND WARNING IN MYSQL_DERIVED_OPTIMIZE() LINE 293
--echo #
CREATE TABLE t1 (
  f1 int(11) DEFAULT NULL
);

SELECT 1
FROM (
  SELECT 1, 2 FROM DUAL
  WHERE EXISTS  (
    SELECT f1
    FROM  t1
    )) AS tt
;

DROP TABLE t1;
--echo #

--echo #
--echo # Bug#11808582: VALGRIND ON WL#5274: INVALID WRITE IN MC_REPLACE_STRMEM.C:493)
--echo #
CREATE TABLE t1 (
  pk INT NOT NULL AUTO_INCREMENT,
  col_int_key INT,
  col_time_key time,
  col_varchar_key VARCHAR(1),
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=InnoDB;
SELECT tt.col_time_key
FROM ( ( SELECT * FROM t1 ) AS tt
  INNER JOIN t1
  ON (t1.col_int_key = tt.col_int_key)
  )
WHERE tt.col_int_key = 207
ORDER BY tt.col_varchar_key, tt.pk ASC, 1;

DROP TABLE t1;
--echo #
--echo # Bug#11791677 - ASSERTION FAILED IN JOIN_MATERIALIZE_TABLE IN
--echo #                SQL_SELECT.CC ON NESTED SUBQUERY
--echo #
CREATE TABLE t1 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_varchar_key (col_varchar_key,col_int_key)
);

INSERT INTO t1 VALUES (10,8,'v'), (29,4,'c');

CREATE TABLE t2 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_nokey int(11) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk)
);

INSERT INTO t2 VALUES (16,1,'c'), (20,4,'d');

CREATE TABLE t3 (
  `field1` varchar(1) DEFAULT NULL,
  `field2` int(11) DEFAULT NULL
);

INSERT INTO t3 VALUES ('m',6),('c',4);

SELECT *
FROM t3
WHERE (field1, field2) IN (
  SELECT t1.col_varchar_key AS field1,
         t1.col_int_key AS field2
  FROM ( t1 INNER JOIN (
      SELECT t2.*
      FROM t2
      WHERE t2.col_int_nokey < t2.pk ) AS alias2
    ON (alias2.col_varchar_key = t1.col_varchar_key ) )
  GROUP BY field1, field2
  ORDER BY t1.col_int_key, t1 .pk DESC )
;

DROP TABLE t1,t2,t3;
--echo #

--echo #
--echo # Bug#11791705 - CRASH IN JOIN_MATERIALIZE_TABLE OR ASSERTION FAIL:
--echo #                !TAB->SAVE_READ_FIRST_RECORD
--echo #
CREATE TABLE t1 (a INTEGER);
INSERT INTO t1 VALUES (NULL),(NULL);

SELECT * FROM t1
WHERE (a, a) NOT IN
  (SELECT * FROM (SELECT 8, 4 UNION SELECT 2, 3) tt) ;

DROP TABLE t1;
--echo #
--echo # Bug#11791649 - ASSERT: FIXED == 0, IN ITEM.CC ON EXPLAIN WITH VIEW
--echo #                IN SUBQUERY
--echo #
CREATE TABLE t1 (pk int);
INSERT INTO t1 VALUES (1);

CREATE TABLE t2 (col_varchar_nokey varchar(1));
INSERT INTO t2 VALUES ('m'), ('f');

EXPLAIN SELECT pk
FROM t1
WHERE (2) IN
  ( SELECT *
     FROM (SELECT COUNT(col_varchar_nokey) FROM t2) d
  )
;
DROP TABLE t1,t2;
--echo #

--echo #
--echo # Bug#12735934 - Lost LIMIT clause caused wrong result.
--echo #
CREATE TABLE t1 (f1 VARCHAR(1), key(f1));
INSERT INTO t1 VALUES ('a');
CREATE VIEW v1 AS SELECT f1 FROM t1 ORDER BY 1 LIMIT 0;
SELECT * FROM v1;
DROP VIEW v1;
DROP TABLE t1;

--echo #

--echo #
--echo # Bug#12726927: An outdated assertion caused server failure.
--echo #
CREATE TABLE t1 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_nokey int(11) NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  PRIMARY KEY (pk),
  KEY col_varchar_key (col_varchar_key)
) ENGINE=MyISAM;

INSERT INTO t1 VALUES (10,1,'v'), (24,18,'h');

CREATE TABLE t2 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_date_key date NOT NULL,
  col_date_nokey date NOT NULL,
  col_time_nokey time NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  col_varchar_nokey varchar(1) NOT NULL,
  PRIMARY KEY (pk),
  KEY col_date_key (col_date_key),
  KEY col_varchar_key (col_varchar_key)
) ENGINE=MyISAM;

INSERT INTO t2 VALUES (1,'1900-01-01','1900-01-01','00:00:00','k','k');

SELECT OUTR.col_date_key
FROM t2 AS OUTR2
  LEFT JOIN t2 AS OUTR ON OUTR2.pk < OUTR.pk
WHERE ( OUTR.col_varchar_nokey , OUTR.col_varchar_key )
  IN (
    SELECT DISTINCT col_varchar_key , col_varchar_key
    FROM t1
    WHERE col_int_nokey XOR OUTR.col_time_nokey
  )
  XOR OUTR.col_date_nokey IS NULL
;

DROP TABLE t1,t2;
--echo #

--echo #
--echo # Bug#12799731 - CRASH IN END_READ_RECORD.
--echo #
create table t1(f1 char(255) charset utf8);
insert into t1 values('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('0');
# We only need the temp SE to overflow here, so use 'memory' for this purpose.
set @save_internal_tmp_mem_storage_engine= @@internal_tmp_mem_storage_engine;
set session internal_tmp_mem_storage_engine='memory';
set @save_heap_size= @@max_heap_table_size;
set @@max_heap_table_size= 1;
SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";

flush status;

select count(*) from t1 join (
  select t1.f1 from t1 join t1 as t2 join t1 as t3) tt on t1.f1 = tt.f1;
# The query should write more than 1000 rows since it also includes
# rows dumped from memory to disk when the temporary table overflows.
# The hypergraph optimizer creates slightly more compact rows on
# 32-bit platforms, and can write some more rows to the temporary
# table before it overflows.
--replace_result 1031 1021
show status like 'Handler_write';
set @@max_heap_table_size= @save_heap_size;
set session internal_tmp_mem_storage_engine= @save_internal_tmp_mem_storage_engine;
SET @@optimizer_switch= @optimizer_switch_saved;

drop table t1;

--echo #

--echo #
--echo # Bug#12896124: Crash on rqg_mdl_stability test
--echo #
CREATE TABLE t1(f1 INT);
INSERT INTO t1 VALUES (1),(2),(3);
CREATE FUNCTION func1 (param1 INTEGER) RETURNS INT NOT DETERMINISTIC
return param1;
CREATE FUNCTION func2 (param1 INTEGER) RETURNS INT
return param1;
SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";
SELECT * FROM (SELECT * FROM t1) tt WHERE f1 = func1(f1);
EXPLAIN SELECT * FROM (SELECT * FROM t1) tt WHERE f1 = func1(f1);
SELECT * FROM (SELECT * FROM t1) tt WHERE f1 = func2(f1);
EXPLAIN SELECT * FROM (SELECT * FROM t1) tt WHERE f1 = func2(f1);
SET @@optimizer_switch= @optimizer_switch_saved;
DROP FUNCTION func1;
DROP FUNCTION func2;
DROP TABLE t1;
--echo #

--echo #
--echo # Bug#12909844: Missing type cast caused false assertion
--echo #
CREATE TABLE t1 ( fk INT) ENGINE=INNODB;
CREATE TABLE t2 (
f1 INT,  f2 INT,  f3 INT,  f4 INT,  f5 INT,  f6 INT,
f7 INT,  f8 INT,  f9 INT,  f10 INT, f11 INT, f12 INT,
f13 INT, f14 INT, f15 INT, f16 INT, f17 INT, f18 INT,
f19 INT, f20 INT, f21 INT, f22 INT, f23 INT, f24 INT,
f25 INT, f26 INT, f27 INT, f28 INT, f29 INT, f30 INT,
f31 INT, f32 TEXT, fk INT) ENGINE=INNODB;
SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";
SELECT alias2.fk AS field1 FROM t1 AS alias1 JOIN
  (SELECT * FROM t2 ) AS alias2 ON alias1.fk = alias2.fk;
EXPLAIN
SELECT alias2.fk AS field1 FROM t1 AS alias1 JOIN
  (SELECT * FROM t2 ) AS alias2 ON alias1.fk = alias2.fk;
SET @@optimizer_switch= @optimizer_switch_saved;
DROP TABLE t1, t2;
--echo #

--echo #
--echo # Bug#12910039: Incorrect merge caused segmentation fault.
--echo #
CREATE TABLE t1 (f1 int) ENGINE=myisam;
CREATE TABLE t2 (f1 text) ENGINE=innodb;
SELECT 1 FROM (
  ( SELECT * FROM ( SELECT * FROM t2 ) AS alias1 ) AS alias1,
  ( SELECT * FROM t1 ) AS alias2 );
DROP TABLE t1,t2;
--echo #

--echo #
--echo # Bug#12910006: MRR initialization on a derived table caused crash.
--echo #
SET @save_switch= @@SESSION.optimizer_switch;
SET @@SESSION.optimizer_switch="batched_key_access=on,derived_merge=off";

CREATE TABLE t1 ( pk integer auto_increment,
col_blob_key blob, primary key (pk)) ENGINE=innodb;

CREATE TABLE t2 (col_tinytext tinytext null,
pk integer auto_increment, col_text text,
col_blob blob, primary key (pk)) ENGINE=innodb;

SELECT alias1.col_text AS field1 ,
       alias1.col_tinytext AS field2
FROM t2 AS alias1
  LEFT OUTER JOIN ( SELECT * FROM t1 ) AS alias2 ON alias1.pk = alias2.pk
WHERE alias2.pk >=1  AND alias2.pk < 3
ORDER BY field1,field2 ASC;

SET @@SESSION.optimizer_switch= @save_switch;
DROP TABLE t1, t2;

--echo #
--echo # Bug#13106350: MRR initialization on a derived table caused crash.
--echo #

SET @save_switch= @@optimizer_switch;
SET @@optimizer_switch="materialization=off,derived_merge=off";

CREATE TABLE t1 (pk INTEGER PRIMARY KEY, vc VARCHAR(20)) charset utf8mb4;

INSERT INTO t1 VALUES(7, 'seven'), (13, 'thirteen');

CREATE TABLE t2 (pk INTEGER PRIMARY KEY, vc1 VARCHAR(20), vc2 VARCHAR(20)) charset utf8mb4;

INSERT INTO t2 VALUES(7, 'seven', 's'), (14, 'fourteen', 'f');

CREATE TABLE t3 (pk INTEGER PRIMARY KEY, vc VARCHAR(20)) charset utf8mb4;

INSERT INTO t3 VALUES(5, 'f'), (6, 's'), (7, 's');

let $query=
SELECT derived.vc
FROM (SELECT * FROM t1) AS derived
WHERE derived.vc IN (
  SELECT t2.vc1
  FROM t2 JOIN t3 ON t2.vc2=t3.vc);
eval explain $query;
eval $query;

SET @@optimizer_switch= @save_switch;

DROP TABLE t1, t2, t3;

--echo #

--echo #
--echo # Bug#13107577: Derived table in a semi-join caused failed assertion.
--echo #

SET @save_switch= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";

CREATE TABLE t1 (
  `col_int_key` int(11) NOT NULL,
  `col_varchar_nokey` varchar(1) NOT NULL
) ENGINE=MyISAM;

INSERT INTO t1 VALUES (8,'m'), (4,'b'), (4,'x'), (7,'g'), (4,'p');

CREATE VIEW v1 AS SELECT * FROM t1;

SELECT col_int_key
FROM t1
WHERE ( NOT EXISTS (
  SELECT col_varchar_nokey
  FROM t1
  WHERE ( 7 ) IN (
      SELECT v1.col_int_key
      FROM ( v1 JOIN ( SELECT * FROM t1 ) AS d1
        ON ( d1.col_varchar_nokey = v1.col_varchar_nokey ) )
    )
) )
;

DROP VIEW v1;
DROP TABLE t1;

SET @@optimizer_switch= @save_switch;

--echo #
--echo # Bug#13105833: Crash when using LooseScan sj-strategy for a view.
--echo #
CREATE TABLE t1 (pk int(11)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);

CREATE TABLE t2 (pk int(11)) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1), (2), (3);

CREATE VIEW v1 AS SELECT DISTINCT pk FROM t1;

SELECT pk
FROM t2
WHERE pk IN ( SELECT * FROM v1 ) ;

DROP TABLE t1,t2;
DROP VIEW v1;

--echo #

--echo #
--echo # Bug#13261277: Unchecked key length caused missing records.
--echo #
CREATE TABLE t1 (
  col_varchar varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
  stub1 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
  stub2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
  stub3 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
);

INSERT INTO t1 VALUES
  ('d','d','l','ther'),
  (NULL,'s','NJBIQ','trzetuchv'),
  (-715390976,'coul','MYWFB','cfhtrzetu'),
  (1696792576,'f','i\'s','c'),
  (1,'i','ltpemcfhtr','gsltpemcf'),
  (-663027712,'mgsltpemcf','sa','amgsltpem'),
  (-1686700032,'JPRVK','i','vamgsltpe'),
  (NULL,'STUNB','UNVJV','u'),
  (5,'oka','qyihvamgsl','AXSMD'),
  (NULL,'tqwmqyihva','h','yntqwmqyi'),
  (3,'EGMJN','e','e');

CREATE TABLE t2 (
  col_varchar varchar(10) DEFAULT NULL,
  col_int INT DEFAULT NULL
) charset utf8mb4;

INSERT INTO t2 VALUES ('d',9);

SET @save_heap_size= @@max_heap_table_size;
SET @@max_heap_table_size= 16384;
SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";

SELECT t2.col_int
FROM t2
    RIGHT JOIN ( SELECT * FROM t1 ) AS dt
  ON t2.col_varchar = dt.col_varchar
WHERE t2.col_int IS NOT NULL ;

--echo # Shouldn't use auto_key0 for derived table
EXPLAIN
SELECT t2.col_int
FROM t2
    RIGHT JOIN ( SELECT * FROM t1 ) AS dt
  ON t2.col_varchar = dt.col_varchar
WHERE t2.col_int IS NOT NULL ;

SET @@max_heap_table_size= @save_heap_size;
SET @@optimizer_switch= @optimizer_switch_saved;
DROP TABLE t1,t2;
--echo #

--echo #
--echo # Bug#13383857: Another crash in memcpy from
--echo #               join_cache::write_record_data with semijoin
--echo #

CREATE TABLE t1 (
  col_int_key INT DEFAULT NULL,
  col_time_nokey TIME DEFAULT NULL,
  col_varchar_key VARCHAR(1) DEFAULT NULL,
  col_varchar_nokey VARCHAR(1) DEFAULT NULL,
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key,col_int_key)
) charset latin1;

INSERT INTO t1 VALUES
 (8,'22:55:23','x','x'),
 (7,'10:19:31','d','d'),
 (1,'14:40:36','r','r'),
 (7,'04:37:47','f','f'),
 (9,'19:34:06','y','y'),
 (NULL,'20:35:33','u','u'),
 (1,NULL,'m','m'),
 (9,'14:43:37',NULL,NULL),
 (2,'02:23:09','o','o'),
 (9,'01:22:45','w','w'),
 (2,'00:00:00','m','m'),
 (4,'00:13:25','q','q'),
 (0,'03:47:16',NULL,NULL),
 (4,'01:41:48','d','d'),
 (8,'00:00:00','g','g'),
 (NULL,'22:32:04','x','x'),
 (NULL,'16:44:14','f','f'),
 (0,'17:38:37','p','p'),
 (NULL,'08:46:48','j','j'),
 (8,'14:11:27','c','c');

CREATE TABLE t2 (
  col_int_key INT DEFAULT NULL,
  col_time_nokey TIME DEFAULT NULL,
  col_varchar_key VARCHAR(1) DEFAULT NULL,
  col_varchar_nokey VARCHAR(1) DEFAULT NULL,
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key,col_int_key)
) charset latin1;

INSERT INTO t2 VALUES
 (4,'22:34:09','v','v'),
 (62,'14:26:02','v','v'),
 (7,'14:03:03','c','c'),
 (1,'01:46:09',NULL,NULL),
 (0,'16:21:18','x','x'),
 (7,'18:56:33','i','i'),
 (7,NULL,'e','e'),
 (1,'09:29:08','p','p'),
 (7,'19:11:10','s','s'),
 (1,'11:57:26','j','j'),
 (5,'00:39:46','z','z'),
 (2,'03:28:15','c','c'),
 (0,'06:44:18','a','a'),
 (1,'14:36:39','q','q'),
 (8,'18:42:45','y','y'),
 (1,'02:57:29',NULL,NULL),
 (1,'16:46:13','r','r'),
 (9,'19:39:02','v','v'),
 (1,NULL,NULL,NULL),
 (5,'20:58:33','r','r');

CREATE TABLE t3 (
  col_int_key INT DEFAULT NULL,
  col_time_nokey TIME DEFAULT NULL,
  col_varchar_key VARCHAR(1) DEFAULT NULL,
  col_varchar_nokey VARCHAR(1) DEFAULT NULL,
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key,col_int_key)
) charset latin1;

INSERT INTO t3 VALUES (8,'04:07:22','g','g');

SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";

let $query=
SELECT col_time_nokey AS x
FROM (SELECT * FROM t2) AS outr
WHERE col_varchar_nokey IN (
  SELECT innr.col_varchar_key
  FROM (SELECT * FROM t3) AS innr2
    LEFT JOIN (SELECT * FROM t1) AS innr
    ON innr2.col_varchar_key >= innr.col_varchar_key
  WHERE outr.col_varchar_nokey = 'e'
  )
  AND outr.col_varchar_key <> 'r'
;
eval explain $query;
eval $query;

SET @@optimizer_switch= @optimizer_switch_saved;

DROP TABLE t1, t2, t3;

--echo #
--echo # Bug#13354889: Crash on a derived table with more than 64 fields.
--echo #
create table t1 (
field00 int, field01 int, field02 int, field03 int,
field04 int, field05 int, field06 int, field07 int,
field10 int, field11 int, field12 int, field13 int,
field14 int, field15 int, field16 int, field17 int,
field20 int, field21 int, field22 int, field23 int,
field24 int, field25 int, field26 int, field27 int,
field30 int, field31 int, field32 int, field33 int,
field34 int, field35 int, field36 int, field37 int,
field40 int, field41 int, field42 int, field43 int,
field44 int, field45 int, field46 int, field47 int,
field50 int, field51 int, field52 int, field53 int,
field54 int, field55 int, field56 int, field57 int,
field60 int, field61 int, field62 int, field63 int,
field64 int, field65 int, field66 int, field67 int,
field70 int, field71 int, field72 int, field73 int,
field74 int, field75 int, field76 int, field77 int,
field100 int
);

insert into t1(field100) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;

SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";

select tt.field100 from t1 join (select * from t1) tt where t1.field100=tt.field100
limit 1;
--echo Should use auto_key0 and ref access.
explain
select tt.field100 from t1 join (select * from t1) tt where t1.field100=tt.field100
limit 1;
SET @@optimizer_switch= @optimizer_switch_saved;

drop table t1;
--echo #

--echo #
--echo # Bug#13390138: crash in memcpy from join_cache::write_record_data
--echo #

CREATE TABLE t1 (
  col_varchar_key varchar(1),
  col_varchar_nokey varchar(1),
  KEY col_varchar_key (col_varchar_key)
) charset utf8mb4 ENGINE=MyISAM;

INSERT INTO t1 VALUES ('r','r');

CREATE TABLE t2 (
  col_varchar_key varchar(1),
  col_varchar_nokey varchar(1),
  KEY col_varchar_key (col_varchar_key)
) charset utf8mb4;

INSERT INTO t2 VALUES
 (NULL,NULL),
 ('r','r');

CREATE TABLE t3 (
  col_int_key int,
  col_varchar_key varchar(1),
  col_varchar_nokey varchar(1),
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key, col_int_key)
) charset utf8mb4;

INSERT INTO t3 VALUES
 (9,'f','f'),
 (4,'y','y'),
 (3,'u','u'),
 (2,'m','m'),
 (NULL,NULL,NULL),
 (2,'o','o'),
 (NULL,'r','r'),
 (6,'m','m'),
 (7,'q','q'),
 (6,'c','c');

SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";

let $query=
SELECT grandparent.col_varchar_nokey AS g1
FROM (SELECT * FROM t3) AS grandparent
WHERE grandparent.col_varchar_nokey IN
 (SELECT parent.col_varchar_key AS p1
  FROM (SELECT * FROM t2) AS parent
  WHERE grandparent.col_varchar_key IN (
    SELECT child1.col_varchar_key AS c1
    FROM (SELECT * FROM t1) AS child1
      LEFT JOIN (SELECT * FROM t2) AS child2
      ON child1.col_varchar_nokey <> child2.col_varchar_key
    )
    AND grandparent.col_int_key IS UNKNOWN
  )
ORDER BY grandparent.col_varchar_nokey;

eval explain $query;
eval $query;

SET @@optimizer_switch= @optimizer_switch_saved;

DROP TABLE t1, t2, t3;

--echo #
--echo # Bug#13457552: Crash on instantiating a derived table in a query with
--echo #               empty result.
--echo #
CREATE TABLE t1 ( pk INT, col_blob BLOB ) ENGINE = MyISAM;
CREATE TABLE t2 ( pk INT, col_blob BLOB ) ENGINE = InnoDB;
SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";
FLUSH STATUS;
SELECT pk FROM ( SELECT col_blob, pk FROM t2 ) AS A NATURAL JOIN t1;
SHOW STATUS LIKE 'Created_tmp_tables';
EXPLAIN SELECT pk FROM ( SELECT col_blob, pk FROM t2 ) AS a NATURAL JOIN t1;
SET @@optimizer_switch= @optimizer_switch_saved;
DROP TABLE t1,t2;

--echo #
--echo # Bug #13801019 ASSERTION `0' FAILED IN CREATE_MYISAM_TMP_TABLE
--echo #

CREATE TABLE t1 (a INT, b BLOB) ENGINE=InnoDB;
CREATE TABLE t2 (c INT);
CREATE TABLE t3 (d INT);
INSERT INTO t3 VALUES (0);

SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";

SELECT * FROM (SELECT * FROM t1) AS a1 RIGHT JOIN t3 LEFT JOIN t2 ON d=c ON a=c;

SET @@optimizer_switch= @optimizer_switch_saved;

DROP TABLE t1, t2, t3;

--echo #
--echo # Bug #17814492 - INVALID RESULTS FROM SUBQUERY WITH IN CLAUSE
--echo #

CREATE TABLE t1 (
 a INTEGER NOT NULL,
 b VARCHAR(1000) NOT NULL,
 c TEXT NOT NULL
)ENGINE=InnoDB;

INSERT INTO t1 VALUES (1, 'xxx', 'abc');
INSERT INTO t1 VALUES (2, 'yyy', 'abc');
INSERT INTO t1 SELECT a, b, c FROM t1 WHERE b='yyy';
INSERT INTO t1 SELECT a, b, c FROM t1 WHERE b='yyy';
INSERT INTO t1 SELECT a, b, c FROM t1 WHERE b='yyy';

CREATE TABLE t2 (
 a INTEGER NOT NULL
)ENGINE=InnoDB;

INSERT INTO t2 VALUES (1), (2);

ANALYZE TABLE t1,t2;

SET @save_optimizer_switch= @@optimizer_switch;
SET @@SESSION.optimizer_switch="index_condition_pushdown=on";

EXPLAIN SELECT a1.a, a1.b, a1.c FROM (SELECT a, b, c FROM t1 ) a1
JOIN t2 ON a1.a=t2.a WHERE a1.b='xxx';

SELECT a1.a, a1.b, a1.c FROM (SELECT a, b, c FROM t1 ) a1
JOIN t2 ON a1.a=t2.a WHERE a1.b='xxx';

SET @@SESSION.optimizer_switch= @save_optimizer_switch;

DROP TABLE t2, t1;

--echo # End of test for Bug #17814492

--echo #
--echo # WL#5275 Process subqueries in FROM clause in the same way as view
--echo #

CREATE TABLE t1(a INTEGER, b INTEGER);
CREATE TABLE t2(a INTEGER, b INTEGER);

INSERT INTO t1 VALUES(1, 10), (2, 20);
INSERT INTO t2 VALUES(1, 100), (2, 200);

# Check allowed merge scenarios

# Allowed: Simple selection, joins, WHERE clause

let $query=
SELECT *
FROM t1 JOIN (SELECT * FROM t2) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM t1, (SELECT * FROM t2) AS dt WHERE t1.a=dt.a;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM (t1 JOIN t2 ON t1.a=t2.a) JOIN (SELECT * FROM t2) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM t1 JOIN (SELECT t2.a, t2.b FROM t1 JOIN t2 USING (a)) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM (t1 JOIN t2 USING (a))
     JOIN
     (SELECT t2.a, t2.b FROM t1 JOIN t2 USING (a)) AS dt
     ON t1.a=dt.a;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM (t1 JOIN t2 USING (a))
     JOIN
     (SELECT t1.a, t2.b FROM t1 JOIN t2 USING (a)) AS dt1
     ON t1.a=dt1.a AND t2.b=dt1.b
     JOIN
     (SELECT t2.a, t2.b FROM t1 JOIN t2 USING (a)) AS dt2
     ON dt1.a=dt2.a;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM t1 JOIN (SELECT t2.a, t2.b FROM t1 JOIN t2 USING (a)
              WHERE t1.b > 15) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

# Allowed: Subqueries in WHERE clause

let $query=
SELECT *
FROM t1 JOIN (SELECT * FROM t2
              WHERE a IN (SELECT a FROM t1)) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

# Disallowed: UNION, aggregated queries, LIMIT, OFFSET, table-less, variables:

let $query=
SELECT *
FROM t1 JOIN (SELECT * FROM t2 UNION SELECT * FROM t2) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM t1 JOIN (SELECT * FROM t2 UNION ALL SELECT * FROM t2) AS dt ON t1.a=dt.a;
--sorted_result
eval $query;
eval explain $query;

let $query=
SELECT *
FROM t1 JOIN (SELECT DISTINCT a, b FROM t2) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM t1 JOIN (SELECT SUM(a) AS a, SUM(b) AS b FROM t2) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM t1 JOIN (SELECT a, SUM(b) AS b FROM t2 GROUP BY a) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM t1 JOIN (SELECT 1 AS a FROM t2 HAVING COUNT(*) > 1) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM t1 JOIN (SELECT * FROM t2 LIMIT 1) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM t1 JOIN (SELECT * FROM t2 LIMIT 2 OFFSET 1) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM t1 JOIN (SELECT 1 AS a) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM t1 JOIN (SELECT a, b, @c:= a+b FROM t2) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

# Allowed: ORDER BY list

# Simple outer query: ORDER BY is propagated to outer level

let $query=
SELECT *
FROM (SELECT * FROM t2 ORDER BY t2.a) AS dt;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM (SELECT * FROM t2 ORDER BY t2.a DESC) AS dt;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM (SELECT * FROM t2 ORDER BY t2.a) AS dt
WHERE dt.a > 0;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM (SELECT * FROM t2 ORDER BY t2.a DESC) AS dt
WHERE dt.a > 0;
eval $query;
eval explain $query;

# Joined outer query: Inner ORDER BY is discarded

let $query=
SELECT *
FROM t1 JOIN (SELECT * FROM t2 ORDER BY t2.a) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM t1 JOIN (SELECT * FROM t2 ORDER BY t2.a DESC) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

# Grouped outer query: Inner ORDER BY is discarded

let $query=
SELECT dt.a, COUNT(*)
FROM (SELECT * FROM t2 ORDER BY t2.a) AS dt
GROUP BY dt.a;
eval $query;
eval explain $query;

let $query=
SELECT dt.a, COUNT(*)
FROM (SELECT * FROM t2 ORDER BY t2.a DESC) AS dt
GROUP BY dt.a;
eval $query;
eval explain $query;

let $query=
SELECT COUNT(*)
FROM (SELECT * FROM t2 ORDER BY t2.a) AS dt;
eval $query;
eval explain $query;

let $query=
SELECT COUNT(*)
FROM (SELECT * FROM t2 ORDER BY t2.a DESC) AS dt;
eval $query;
eval explain $query;

let $query=
SELECT DISTINCT *
FROM (SELECT * FROM t2 ORDER BY t2.a) AS dt;
eval $query;
eval explain $query;

let $query=
SELECT DISTINCT *
FROM (SELECT * FROM t2 ORDER BY t2.a DESC) AS dt;
eval $query;
eval explain $query;

# Ordered outer query: Inner ORDER BY is discarded

let $query=
SELECT *
FROM t1 JOIN (SELECT * FROM t2 ORDER BY t2.a) AS dt ON t1.a=dt.a
ORDER BY t1.b;
eval $query;
eval explain $query;

let $query=
SELECT *
FROM t1 JOIN (SELECT * FROM t2 ORDER BY t2.a DESC) AS dt ON t1.a=dt.a
ORDER BY t1.b;
eval $query;
eval explain $query;

# Non-dependent subquery in SELECT list:

let $query=
SELECT *
FROM t1 JOIN (SELECT a, (SELECT COUNT(*) FROM t1) AS b FROM t2) AS dt
     ON t1.a=dt.a;
eval $query;
eval explain $query;

# Disallowed: Dependent subquery in SELECT list:

let $query=
SELECT *
FROM t1 JOIN (SELECT a, (SELECT b) AS b FROM t2) AS dt ON t1.a=dt.a;
eval $query;
eval explain $query;

# Enable and disable derived table merging:

SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";

let $query=
SELECT *
FROM (t1 JOIN t2 USING (a))
     JOIN (SELECT t2.a, t2.b FROM t1 JOIN t2 USING (a)) AS dt
     ON t1.a=dt.a;
eval $query;
eval explain $query;

SET @@optimizer_switch="derived_merge=on";

eval $query;
eval explain $query;

SET @@optimizer_switch= @optimizer_switch_saved;

# Check that derived tables as update targets are rejected:

--error ER_PARSE_ERROR
INSERT INTO (SELECT * FROM t1) AS dt VALUES(9, 99);

--error ER_PARSE_ERROR
INSERT INTO (SELECT * FROM t1) AS dt SELECT 9, 99;

--error ER_NON_UPDATABLE_TABLE
UPDATE (SELECT * FROM t1) AS dt SET b=b+1;

--error ER_NON_UPDATABLE_TABLE
UPDATE t1 JOIN (SELECT * FROM t2) AS dt ON t1.a=dt.a SET dt.b=dt.b+1;

--error ER_PARSE_ERROR
DELETE FROM (SELECT * FROM t1) AS dt WHERE a=1;

--error ER_NON_UPDATABLE_TABLE
DELETE dt FROM t1 JOIN (SELECT * FROM t2) AS dt ON t1.a=dt.a WHERE t1.a=1;

# Multi-table UPDATE and DELETE allows derived tables that are not updated:

UPDATE t1 JOIN (SELECT * FROM t2) AS dt ON t1.a=dt.a SET t1.b=t1.b+1;

DELETE t1 FROM t1 JOIN (SELECT * FROM t2) AS dt ON t1.a=dt.a WHERE t1.a=1;

SELECT * FROM t1;

SELECT * FROM t2;

DROP TABLE t1, t2;

--echo # Bug#19791944: Assert fail in subselect_hash_sj_engine::exec

CREATE TABLE t1 (
  pk int NOT NULL,
  col_int_key int NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  PRIMARY KEY (pk),
  KEY col_varchar_key (col_varchar_key, col_int_key)
) charset utf8mb4;

INSERT INTO t1 VALUES
(1,4,'j'), (2,6,'v'), (3,3,'c'), (4,5,'m'), (5,3,'d'), (6,246,'d'), (7,2,'y'), (8,9,'t'),
(9,3,'d'), (10,8,'s'), (11,1,'r'), (12,8,'m'), (13,8,'b'), (14,5,'x'), (15,7,'g'), (16,5,'p'),
(17,1,'q'), (18,6,'w'), (19,2,'d'), (20,9,'e');

CREATE TABLE t2 (
  pk int NOT NULL,
  col_int_nokey int NOT NULL,
  col_int_key int NOT NULL,
  col_date_key date NOT NULL,
  col_varchar_nokey varchar(1) NOT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_date_key (col_date_key)
) charset utf8mb4;

INSERT INTO t2 VALUES (1,1,7,'1900-01-01','k');

CREATE TABLE t3 (
  pk int NOT NULL ,
  col_date_nokey date NOT NULL,
  col_varchar_nokey varchar(1) NOT NULL,
  PRIMARY KEY (pk)
) charset utf8mb4;

INSERT INTO t3 VALUES (10,'1900-01-01','b');

let $query=
SELECT outr.col_date_key AS x
FROM (SELECT * FROM t1) AS outr2 LEFT JOIN (SELECT * FROM t2) AS outr
     ON outr2.col_varchar_key = outr.col_varchar_nokey
WHERE (outr.col_int_key, outr.col_int_key) IN
       (SELECT innr.pk AS x, innr.pk AS y
        FROM (SELECT * FROM t3) AS innr
        WHERE innr.col_date_nokey IS NOT NULL XOR
              innr.col_varchar_nokey > 'p'
        ORDER BY innr.col_date_nokey) XOR
      (outr.col_int_nokey < 2 OR
       NOT outr.col_int_key IS NULL)
ORDER BY outr.col_int_key,
         outr.pk;

eval EXPLAIN $query;
eval $query;

DROP TABLE t1, t2, t3;

--echo # Bug#20087645: Assert fail in subselect_hash_sj_engine::exec

CREATE TABLE t1 (
  pk int NOT NULL,
  col_int_nokey int NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  PRIMARY KEY (pk),
  KEY col_varchar_key(col_varchar_key)
) charset utf8mb4 ENGINE=InnoDB;

INSERT INTO t1 VALUES
 (10,1,'v'), (11,7,'s'), (12,4,'l'), (13,7,'y'), (14,0,'c'), (15,2,'i');

CREATE TABLE t2 (
  pk int NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  col_varchar_nokey varchar(1) NOT NULL,
  PRIMARY KEY (pk),
  KEY col_varchar_key (col_varchar_key)
) charset utf8mb4 ENGINE=InnoDB;

INSERT INTO t2 VALUES (1,'k','k');

CREATE TABLE t3 (
  pk int NOT NULL,
  col_varchar_nokey varchar(1) NOT NULL,
  PRIMARY KEY (pk)
) charset utf8mb4 ENGINE=InnoDB;

INSERT INTO t3 VALUES
 (1,'j'), (2,'v'), (3,'c'), (4,'m'), (5,'d');

ANALYZE TABLE t1, t2, t3;

let $query=
SELECT table1.pk
FROM (SELECT * FROM t1) AS table1
     LEFT JOIN
       (SELECT * FROM t1) AS table2
       RIGHT OUTER JOIN
       (SELECT * FROM t2) AS table3
       ON table3.col_varchar_nokey = table2.col_varchar_key
     ON table3.pk = table2.col_int_nokey
WHERE table3.col_varchar_key <> ALL
       (SELECT sq1_t1.col_varchar_nokey AS sq1_field1
        FROM (SELECT * FROM t3) AS sq1_t1
             LEFT OUTER JOIN
             (SELECT * FROM t1) AS sq1_t2
             ON sq1_t2.col_varchar_key = sq1_t1.col_varchar_nokey
       ) OR
      table1.pk = 96;

eval EXPLAIN $query;
eval $query;

DROP TABLE t1, t2, t3;

--echo # Bug#19812352: Assert fail in subselect_hash_sj_engine::setup

CREATE TABLE t1 (
  col_time_nokey time NOT NULL
);

INSERT INTO t1 VALUES ('00:00:00');

CREATE TABLE t2 (
  pk int NOT NULL,
  col_int_nokey int NOT NULL,
  col_int_key int NOT NULL,
  col_date_nokey date NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  col_varchar_nokey varchar(1) NOT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key(col_int_key),
  KEY col_varchar_key(col_varchar_key, col_int_key)
) charset utf8mb4;

INSERT INTO t2 VALUES
(1,4,0,'0001-01-01','j','j'), (2,6,8,'2004-09-18','v','v'),
(3,3,1,'2009-12-01','c','c'), (4,5,8,'2004-12-17','m','m'),
(5,3,9,'2000-03-14','d','d'), (6,246,24,'2000-10-08','d','d'),
(7,2,6,'2006-05-25','y','y'), (8,9,1,'2008-01-23','t','t'),
(9,3,6,'2007-06-18','d','d'), (10,8,2,'2002-10-13','s','s'),
(11,1,4,'1900-01-01','r','r'), (12,8,8,'0001-01-01','m','m'),
(13,8,4,'2006-03-09','b','b'), (14,5,4,'2001-06-05','x','x'),
(15,7,7,'2006-05-28','g','g'), (16,5,4,'2001-04-19','p','p'),
(17,1,1,'1900-01-01','q','q'), (18,6,9,'2004-08-20','w','w'),
(19,2,4,'2004-10-10','d','d'), (20,9,8,'2000-04-02','e','e');

CREATE TABLE t3 (
  pk int NOT NULL,
  col_int_key int NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  PRIMARY KEY (pk),
  KEY col_varchar_key (col_varchar_key, col_int_key)
) charset utf8mb4;

INSERT INTO t3 VALUES
(10,1,'v'), (11,7,'s'), (12,4,'l'), (13,7,'y'), (14,0,'c'),
(15,2,'i'), (16,9,'h'), (17,4,'q'), (18,0,'a'), (19,9,'v'),
(20,1,'u'), (21,3,'s'), (22,8,'y'), (23,8,'z'), (24,18,'h'),
(25,84,'p'), (26,6,'e'), (27,3,'i'), (28,6,'y'), (29,6,'w');

CREATE TABLE t4 (
  pk int NOT NULL,
  col_int_nokey int NOT NULL,
  col_int_key int NOT NULL,
  col_varchar_nokey varchar(1) NOT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key)
) charset utf8mb4;

INSERT INTO t4 VALUES (10,8,7,'b');

let $query=
SELECT outr.pk AS x
FROM (SELECT * FROM t1) AS outr2
     LEFT JOIN (SELECT * FROM t2) AS outr
     ON outr2.col_time_nokey >= outr.col_date_nokey
WHERE (outr.col_int_nokey, outr.col_int_key) IN
       (SELECT innr.col_int_key AS x,
               innr.col_int_nokey AS y
        FROM (SELECT * FROM t3) AS innr2
             LEFT JOIN (SELECT * FROM t4) AS innr
             ON innr2.col_varchar_key <> innr.col_varchar_nokey
        WHERE innr.col_int_key <> innr.pk OR innr.pk = 9
       ) AND
       outr.pk < 7 XOR outr.col_varchar_nokey <> 'i'
ORDER BY outr.col_varchar_key, outr.pk;

eval EXPLAIN $query;
eval $query;

DROP TABLE t1, t2, t3, t4;

--echo # Bug#19811762: Assert fail in JOIN::propagate_dependencies

CREATE TABLE t1 (
  pk int NOT NULL
);

CREATE TABLE t2 (
  pk int NOT NULL,
  col_int_key int DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key, col_int_key)
);

CREATE TABLE t3 (
  pk int NOT NULL AUTO_INCREMENT,
  col_int_nokey int DEFAULT NULL,
  col_varchar_nokey varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk)
);

let $query=
SELECT table1.pk
FROM   t1 AS table1
     RIGHT JOIN
         t2 AS table2
       LEFT OUTER JOIN
         (SELECT sq2_t1.*
          FROM t2 AS sq2_t1 INNER JOIN t3 AS sq2_t2
               ON sq2_t2.col_int_nokey = sq2_t1.col_int_key
          WHERE sq2_t2.col_varchar_nokey <= sq2_t1.col_varchar_key OR
                sq2_t2.col_int_nokey <> 2
         ) AS table3
        ON table3.col_int_key = table2.col_int_key
      ON table3.pk = table2.col_int_key
WHERE table3.pk >= 6 OR
      table1.pk > 68 AND
      table1.pk < ( 68 + 76 );

eval EXPLAIN $query;
eval $query;

DROP TABLE t1, t2, t3;

--echo #
--echo # Bug#19793998 ASSERTION FAILED: ROWS >= 0.0 IN
--echo #              HANDLER::INDEX_SCAN_COST()
--echo #

SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";

CREATE TABLE t1 (
  pk INTEGER PRIMARY KEY
);

INSERT INTO t1 VALUES (0),(1),(2);

CREATE TABLE t2 (
  pk INTEGER PRIMARY KEY
);

INSERT INTO t2 VALUES (0),(1),(2);

let query=
SELECT SHA(pk) IN (SELECT * FROM (SELECT '' FROM t2) a) FROM t1;

eval explain $query;
eval $query;

DROP TABLE t1, t2;

SET @@optimizer_switch= @optimizer_switch_saved;

--echo # Bug#20073930 Assert fail in store_top_level_join_columns

CREATE TABLE t1 (
  pk int NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  PRIMARY KEY (pk)
) engine=innodb;

CREATE TABLE t2 (
  pk int NOT NULL,
  col_int_key int NOT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key)
) engine=innodb;

CREATE TABLE t3 (
  pk int NOT NULL,
  PRIMARY KEY (pk)
) engine=innodb;

CREATE TABLE t4 (
  pk int NOT NULL ,
  col_varchar_key varchar(1) NOT NULL,
  PRIMARY KEY (pk)
) engine=innodb;

let query=
SELECT table2.pk AS field2
FROM (SELECT sq1_t2.pk
      FROM t1 AS sq1_t1
           JOIN
            (t1 AS sq1_t2 RIGHT JOIN t2 AS sq1_t3
             ON sq1_t3.pk = sq1_t2.pk
            )
           ON sq1_t3.col_int_key = sq1_t2.pk AND
              sq1_t1.col_varchar_key IN
             (SELECT child_sq1_t2.col_varchar_key AS child_sq1_field1
              FROM t1 AS child_sq1_t2
             )
     ) AS table1
     LEFT JOIN
     t1 AS table2
       JOIN
       (SELECT sq2_t1.* FROM t1 AS sq2_t1) AS table3
       ON table3.col_varchar_key = table2.col_varchar_key
     ON table3.col_varchar_key = table2.col_varchar_key;

eval explain $query;
eval $query;

DROP TABLE t1, t2, t3, t4;

--echo # Bug#20118755 Assert fail in JOIN::propagate_dependencies

CREATE TABLE t1 (
  pk int NOT NULL ,
  col_varchar_key varchar(1) DEFAULT NULL,
  col_varchar_nokey varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk)
);

CREATE TABLE t2 (
  pk int NOT NULL,
  col_int_nokey int DEFAULT NULL,
  col_int_key int DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  col_varchar_nokey varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk)
);

let $query=
SELECT table1.pk AS field1,
       table2.pk field2
FROM (SELECT sq1_t1.*
      FROM t1 AS sq1_t1
     ) AS table1
     RIGHT JOIN
       (SELECT sq2_t1.*
        FROM t2 AS sq2_t1
        WHERE sq2_t1.col_int_key NOT IN
               (SELECT child_sq1_t1.col_int_key AS child_sq2_field1
                FROM t2 AS child_sq1_t1
               )
       ) AS table2
       RIGHT JOIN t2 AS table3
       ON table3.col_varchar_nokey = table2.col_varchar_key
     ON table3.col_varchar_nokey = table2.col_varchar_key;

eval explain $query;
eval $query;

DROP TABLE t1, t2;

--echo # Bug#20073366 Assert fail in
--echo #              Optimize_table_order::Optimize_straight_join

CREATE TABLE t1 (
  pk int NOT NULL,
  col_int_nokey int DEFAULT NULL,
  col_int_key int DEFAULT NULL,
  col_date_key date DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_date_key (col_date_key),
  KEY col_varchar_key (col_varchar_key, col_int_key)
) charset utf8mb4;

CREATE TABLE t2 (
  pk int NOT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk)
) charset utf8mb4;

CREATE TABLE t3 (
  pk int NOT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  col_varchar_nokey varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk)
) charset utf8mb4;

let $query=
SELECT STRAIGHT_JOIN alias1.col_date_key AS field1
FROM t1 AS alias1
     INNER JOIN
     ((SELECT  sq1_alias1.col_varchar_key
       FROM t1 AS sq1_alias1 RIGHT OUTER JOIN t2 AS sq1_alias2
            ON sq1_alias2.pk = sq1_alias1.col_int_key
       WHERE ('n', 'l') IN
          (SELECT
                  c_sq1_alias1.col_varchar_nokey AS c_sq1_field1,
                  c_sq1_alias1.col_varchar_key AS c_sq1_field2
           FROM t3 AS c_sq1_alias1
                INNER JOIN
                 (t1 AS c_sq1_alias2
                  INNER JOIN t1 AS c_sq1_alias3
                  ON c_sq1_alias3.col_varchar_key = c_sq1_alias2.col_varchar_key
                 )
                 ON c_sq1_alias3.pk = c_sq1_alias2.pk
           WHERE c_sq1_alias3.col_int_nokey <> c_sq1_alias2.col_int_nokey
          ) AND
             sq1_alias2.col_varchar_key = 't'
       ) AS alias2
        INNER JOIN t3 AS alias3
        ON alias3.col_varchar_nokey = alias2.col_varchar_key
      )
      ON alias3.col_varchar_key = alias2.col_varchar_key;

eval explain $query;
eval $query;

DROP TABLE t1, t2, t3;

--echo # Bug#20204415 Assertion failure in TABLE_LIST::set_merged()

CREATE TABLE t(a INTEGER);
CREATE VIEW v AS SELECT * FROM t;

SET optimizer_switch='derived_merge=off';
PREPARE s1 FROM 'SELECT * FROM v';
PREPARE s2 FROM 'SELECT * FROM (SELECT * FROM t) AS dt';
SET optimizer_switch='derived_merge=on';
EXECUTE s1;
EXECUTE s2;

DEALLOCATE PREPARE s1;
DEALLOCATE PREPARE s2;
DROP VIEW v;
DROP TABLE t;

--echo # Bug#20487336 Assert length > 0 && keyparts != 0 in calc_length_and_key

CREATE TABLE t1
 (pk INTEGER PRIMARY KEY,
  col_int_nokey INTEGER,
  col_varchar_nokey VARCHAR(1),
  col_varchar_key VARCHAR(1),
  KEY col_varchar_key(col_varchar_key)
) charset utf8mb4 engine=innodb;

INSERT INTO t1 (pk) VALUES
 (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
 (11), (12), (13), (14), (15), (16), (17), (18), (19), (20);

CREATE TABLE t2
 (pk INTEGER PRIMARY KEY,
  col_varchar_key VARCHAR(1),
  KEY col_varchar_key(col_varchar_key)
) charset utf8mb4 engine=innodb;

INSERT INTO t2 (pk) VALUES
 (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
 (11), (12), (13), (14), (15), (16), (17), (18), (19), (20);

CREATE TABLE t3
 (pk INTEGER PRIMARY KEY,
  col_varchar_key VARCHAR(1),
  KEY col_varchar_key(col_varchar_key)
) charset utf8mb4 engine=innodb;

INSERT INTO t3 (pk) VALUES
 (1), (2);

ANALYZE TABLE t1, t2, t3;

let $query=
SELECT MIN(alias1.col_varchar_nokey) AS field1
FROM (SELECT sq1_alias1.*
      FROM t1 AS sq1_alias1, t1 AS sq1_alias2
      WHERE sq1_alias1.col_varchar_nokey IN
                (SELECT c_sq1_alias2.col_varchar_key AS c_sq1_field1
                 FROM t1 AS c_sq1_alias1 INNER JOIN
                      t1 AS c_sq1_alias2
                      ON c_sq1_alias2.col_varchar_nokey = c_sq1_alias1.col_varchar_key
                 WHERE c_sq1_alias1.col_int_nokey <> c_sq1_alias1.col_int_nokey
                ) AND
            sq1_alias2.pk = sq1_alias1.pk
     ) AS alias1,
     (SELECT sq2_alias1.*
      FROM t2 AS sq2_alias1 RIGHT JOIN
           t3 AS sq2_alias2
           ON sq2_alias2.col_varchar_key = sq2_alias1.col_varchar_key
     ) AS alias2;

eval explain $query;
eval $query;

DROP TABLE t1, t2, t3;

--echo #
--echo # Bug #18607971 : 5.5 TO 5.6 REGRESSION WITH A SUBQUERY IN THE FROM
--echo #                 CLAUSE.
--echo #

CREATE TABLE t(id INT PRIMARY KEY,
               c1 INT, c2 INT, key(c2)) engine=InnoDB;
INSERT INTO t(id, c1, c2) VALUES(1, 2, 3), (2, 3, 4), (3, 3, 4), (4, 3, 4);

ANALYZE TABLE t;

EXPLAIN SELECT * FROM
             (SELECT t1.c1
              FROM t t1 INNER JOIN t t2 ON t1.c1= 3
              GROUP BY t1.c1) a, t b
             WHERE b.id BETWEEN 1 AND 10;

EXPLAIN SELECT * FROM
             (SELECT t1.id
              FROM t t1 INNER JOIN t t2 ON t1.id=1 AND t1.c1=t2.id
              GROUP BY t1.id, t2.c2) a, t b
             WHERE b.id BETWEEN 1 AND 10;

EXPLAIN SELECT * FROM
             (SELECT t1.c1
              FROM t t1 INNER JOIN t t2 ON t1.c1= 3 AND t2.c2= 3
              GROUP BY t1.c1) a, t b
             WHERE b.id BETWEEN 1 AND 10;

EXPLAIN SELECT * FROM
             (SELECT t1.c1
              FROM t t1 INNER JOIN t t2 ON t1.c1= 3 AND t2.c2= 3
              GROUP BY t1.c1, t2.c2) a, t b
             WHERE b.id BETWEEN 1 AND 10;

EXPLAIN SELECT * FROM
             (SELECT t1.c1
              FROM t t1 INNER JOIN t t2 ON t1.c1= 3 AND t2.c2= 3
              GROUP BY t1.c1, t1.id) a, t b
             WHERE b.id BETWEEN 1 AND 10;

EXPLAIN SELECT * FROM
             (SELECT t1.id
              FROM t t1 INNER JOIN t t2 ON t1.id=1 AND t1.c1=t2.c1
              GROUP BY t2.c1, t1.id) a, t b
             WHERE b.id BETWEEN 1 AND 10;

EXPLAIN SELECT * FROM
             (SELECT t1.id
              FROM t t1 INNER JOIN t t2 ON t1.id=1 AND t1.c1=t2.id
              GROUP BY t2.c1, t1.id) a, t b
             WHERE b.id BETWEEN 1 AND 10;

EXPLAIN SELECT * FROM
             (SELECT t1.id
              FROM t t1 INNER JOIN t t2 ON t1.id=1 AND t1.c1=t2.id
              GROUP BY t2.c2, t1.id) a, t b
             WHERE b.id BETWEEN 1 AND 10;

EXPLAIN SELECT * FROM
             (SELECT t1.id
              FROM t t1 INNER JOIN t t2 ON t1.id=1 AND t1.c1=t2.id
              GROUP BY t1.id, t2.c2) a, t b
             WHERE b.id BETWEEN 1 AND 10;

EXPLAIN SELECT * FROM
             (SELECT t1.id
              FROM t t1 INNER JOIN t t2 INNER JOIN t t3 ON t1.id=1 AND t1.c1=t2.id AND t2.c1=t3.id
              GROUP BY t1.id, t2.c2, t3.c2) a, t b
             WHERE b.id BETWEEN 1 AND 10;

#distinct
EXPLAIN SELECT * FROM
             (SELECT DISTINCT t1.id
              FROM t t1
              WHERE t1.id= 1) a, t b
             WHERE b.id BETWEEN 1 AND 10;

#group by with expression.
EXPLAIN SELECT * FROM
             (SELECT t1.id + 1
              FROM t t1 INNER JOIN t t2 ON t1.id= 1
              GROUP BY t1.id + 1) a, t b
             WHERE b.id BETWEEN 1 AND 10;

EXPLAIN SELECT * FROM
             (SELECT t1.c1
              FROM t t1 INNER JOIN t t2 ON t1.c1= 3
              GROUP BY 1.5) a, t b
             WHERE b.id BETWEEN 1 AND 10;

EXPLAIN SELECT * FROM
             (SELECT t1.id
              FROM t t1 INNER JOIN t t2 ON mod(t1.id,1000)= 1
              GROUP BY t1.id) a, t b
             WHERE b.id BETWEEN 1 AND 10;

EXPLAIN SELECT * FROM
             (SELECT t1.id + 1
              FROM t t1 INNER JOIN t t2 ON t1.id + 1= 2
              GROUP BY t1.id + 1) a, t b
             WHERE b.id BETWEEN 1 AND 10;

#Test view ref in group by.
CREATE VIEW v1 AS SELECT c1 a FROM t WHERE c1 = 3;
CREATE VIEW v2 AS SELECT c2 b FROM t WHERE c2 > 3;
EXPLAIN SELECT * FROM (SELECT v1.a
               FROM v1 LEFT OUTER JOIN v2 ON v1.a = v2.b
               GROUP BY v1.a) p, t q
               WHERE q.id BETWEEN 1 AND 10;

DROP VIEW v1;
CREATE VIEW v1 AS SELECT c1 a FROM t;

EXPLAIN SELECT * FROM (SELECT v1.a
               FROM v1 LEFT OUTER JOIN v2 ON v1.a = v2.b AND v1.a = 10
               GROUP BY v1.a) p, t q
               WHERE q.id BETWEEN 1 AND 10;

EXPLAIN SELECT * FROM (SELECT v1.a
               FROM v1 LEFT OUTER JOIN v2 ON v1.a = v2.b
               WHERE v1.a = 3
               GROUP BY v1.a) p, t q
               WHERE q.id BETWEEN 1 AND 10;

DROP VIEW v1, v2;
DROP TABLE t;

--echo # Bug #20443457 : Assert fail in calc_length_and_keyparts

CREATE TABLE t1(
  pk INTEGER PRIMARY KEY,
  k INTEGER,
  KEY k(k),
  nk INTEGER);

INSERT INTO t1 VALUES(1, 10, 100), (2, 20, 200);

set @optimizer_switch_saved=@@optimizer_switch;
set @@optimizer_switch='firstmatch=off';

let $query=
SELECT t1.k
FROM t1 RIGHT JOIN
     (SELECT t2.*
      FROM t1 AS t2 JOIN t1 AS t3
           ON TRUE
      WHERE t3.k IN (SELECT k FROM t1 AS t4 WHERE k>1)
     ) AS dt
     ON t1.pk = dt.pk;

eval explain $query;

--sorted_result
eval $query;

SET @@optimizer_switch=@optimizer_switch_saved;

DROP TABLE t1;

--echo # Bug#20733540: Assertion failed: !table || (!table->read_set ...)

CREATE TABLE t1(a INTEGER, b VARCHAR(10));

INSERT INTO t1 VALUES (1,'a');

CREATE TABLE t2(c INTEGER);

INSERT INTO t2 VALUES(0);

SELECT 1
FROM (SELECT b FROM t1 WHERE a) AS dt1
     RIGHT JOIN t2
     ON c NOT BETWEEN 1 AND 2
     NATURAL JOIN t1 AS t3;

DROP TABLE t1, t2;

--echo # Bug#21512842: Sig 11 in check_column_grant_in_table_ref

CREATE TABLE t1 (
 pk INTEGER,
 col_varchar JSON NOT NULL,
 PRIMARY KEY (pk)
);

CREATE TABLE t2 (
 pk INTEGER,
 col_date_key DATE NOT NULL,
 PRIMARY KEY (pk)
);

set sql_mode='';
SELECT *
FROM (SELECT alias2.pk AS field1
      FROM t1 AS alias1
           LEFT OUTER JOIN t2 AS alias2
           ON JSON_UNQUOTE(JSON_EXTRACT(alias1.col_varchar,'$.varc'))
              COLLATE utf8mb4_general_ci = alias2.col_date_key
      GROUP BY JSON_QUOTE(JSON_EXTRACT(field1,'$.intc'))
     ) AS dt;
set sql_mode=default;

DROP TABLE t1, t2;

--echo # Bug#21350125: Wrong results when ORDER BY is removed

CREATE TABLE t1 (
  c1n varchar(1) NOT NULL,
  c1k varchar(2) DEFAULT NULL,
  KEY c1k (c1k)
);

INSERT INTO t1 VALUES ('j','jj'),('r','rr');

CREATE TABLE t2 (
  c2k varchar(1) NOT NULL,
  c2n varchar(2) DEFAULT NULL,
  KEY c2k (c2k)
);

INSERT INTO t2 VALUES
('f','ff'),('t','tt'),('c','cc'),('c','cc'),('r','rr'),('k','kk');

CREATE TABLE `empty` (dummy INTEGER);

CREATE VIEW vr AS
SELECT t2.c2n AS v_field
FROM t1 RIGHT JOIN t2
     ON t2.c2k = t1.c1k;

CREATE VIEW vl AS
SELECT t2.c2n AS v_field
FROM t2 LEFT JOIN t1
     ON t2.c2k = t1.c1k;

# Check with derived tables, and that LEFT JOIN and RIGHT JOIN are equivalent.
# Check prepared statements too.

let $query=
SELECT alias1.dt_field AS field1
FROM (SELECT t2.c2n AS dt_field
      FROM t1 RIGHT JOIN t2
           ON t2.c2k = t1.c1k
     ) AS alias1
     RIGHT JOIN t2 AS alias2
     ON alias2.c2n = alias1.dt_field;

eval $query;
eval prepare s from '$query';

execute s;
deallocate prepare s;

let $query=
SELECT alias1.dt_field AS field1
FROM (SELECT t2.c2n AS dt_field
      FROM t2 LEFT JOIN t1
           ON t2.c2k = t1.c1k
     ) AS alias1
     RIGHT JOIN t2 AS alias2
     ON alias2.c2n = alias1.dt_field;

eval $query;
eval prepare s from '$query';

execute s;
deallocate prepare s;

# Check that use of views give same result as derived tables.

SELECT alias1.v_field AS field1
FROM vl AS alias1
     RIGHT JOIN t2 AS alias2
     ON alias2.c2n = alias1.v_field;

SELECT alias1.v_field AS field1
FROM vr AS alias1
     RIGHT JOIN t2 AS alias2
     ON alias2.c2n = alias1.v_field;

# Check more complicated join nests

SELECT alias1.dt_field AS field1
FROM (SELECT t2.c2n AS dt_field
      FROM t1 RIGHT JOIN
             (`empty` RIGHT JOIN t2
              ON TRUE)
           ON t2.c2k = t1.c1k
     ) AS alias1
     RIGHT JOIN t2 AS alias2
     ON alias2.c2n = alias1.dt_field;

SELECT alias1.dt_field AS field1
FROM (SELECT t2.c2n AS dt_field
      FROM t1 RIGHT JOIN
             (t2 LEFT JOIN `empty`
              ON TRUE)
           ON t2.c2k = t1.c1k
     ) AS alias1
     RIGHT JOIN t2 AS alias2
     ON alias2.c2n = alias1.dt_field;

DROP VIEW vl, vr;
DROP TABLE t1, t2, `empty`;

--echo # Bug#22239474: Unexpected error 1093 on nested subquery for update

CREATE TABLE t1 (id INTEGER PRIMARY KEY, d INTEGER);

INSERT INTO t1 VALUES(1, 10), (2, 20);

let $query=
DELETE FROM t1
WHERE id IN (SELECT * FROM (SELECT id FROM t1) AS dt);

eval explain $query;
eval $query;

INSERT INTO t1 VALUES(1, 10), (2, 20);

let $query=
UPDATE t1 SET d= NULL
WHERE id IN (SELECT * FROM (SELECT id FROM t1) AS dt);

eval explain $query;
eval $query;

let $query=
INSERT INTO t1 SELECT id+10, d FROM t1;

eval explain $query;
eval $query;

let $query=
INSERT INTO t1 SELECT id+20, d FROM (SELECT * FROM t1) AS dt;

eval explain $query;
eval $query;

DROP TABLE t1;

--echo # Bug#22223202: Query with double nested subquery much slower in 5.7

CREATE TABLE t1 (
  t1_rowid bigint unsigned NOT NULL,
  t1_co varchar(1) NOT NULL,
  t1_inv_date char(3) NOT NULL,
  UNIQUE KEY rowid (t1_rowid),
  KEY invdate (t1_co, t1_inv_date)
) charset latin1;

INSERT INTO t1 VALUES (505975,'D','s:1'),(505981,'D','s:1'),(505869,'D','s:3');

CREATE TABLE t2 (
  t2_rowid bigint unsigned NOT NULL,
  t2_end_date char(3) NOT NULL,
  UNIQUE KEY rowid (t2_rowid),
  KEY end_date (t2_end_date)
) charset latin1;

INSERT INTO t2 VALUES (9,'_:L'), (10,'_<2'), (11,'_<N');

let $query=
t1_inv_date
FROM t1 LEFT JOIN
     (SELECT curr.t2_end_date As end_date,
             (SELECT prev.t2_end_date
              FROM t2 AS prev
              WHERE prev.t2_end_date < curr.t2_end_date
              ORDER BY prev.t2_end_date DESC LIMIT 1) AS prev_end_date
      FROM t2 AS curr
      WHERE curr.t2_end_date >= 's:1'
     ) AS periods
     ON periods.prev_end_date < t1_inv_date AND
        periods.end_date >= t1_inv_date;

eval explain SELECT $query;
eval SELECT $query;

# A merge hint overrides the heuristic to not merge.
eval explain SELECT /*+ MERGE(periods) */ $query;
eval SELECT /*+ MERGE(periods) */ $query;

DROP TABLE t1, t2;

--echo # Bug#22176604: Wrong result on outer join with uncorrelated subquery

CREATE TABLE t1 (a INT, KEY(a));
INSERT INTO t1 VALUES  (1), (NULL);

CREATE TABLE t2 (b INT, KEY(b));
INSERT INTO t2 VALUES (7), (NULL), (1), (5), (8), (6), (4), (0), (3), (NULL);

CREATE TABLE t3 (c INT);
INSERT INTO t3 VALUES (NULL), (1), (5);

let $query=
SELECT * FROM t1 LEFT JOIN (
  SELECT t3.* FROM t2 INNER JOIN t3 ON b = c
) AS sq ON a <= sq.c;

eval explain $query;
--sorted_result
eval $query;

DROP TABLE t1, t2, t3;

--echo # Bug#22343301: Error 1093 (HY000): You can't specify target table'.'
--echo #               for update in FROM clause

CREATE TABLE users (
 id int unsigned AUTO_INCREMENT,
 name varchar(255),
 position int DEFAULT NULL,
 PRIMARY KEY (id));

INSERT INTO users (name, position) VALUES
 ('user1','1'), ('user2','2'), ('user3','3'), ('user4','4'), ('user5','5');

--echo # Single-table update with non-mergeable derived table
UPDATE users
SET position = (SELECT COUNT(pos) + 1
                FROM (SELECT DISTINCT position AS pos FROM users) AS t2
                WHERE t2.pos < users.position)
WHERE id = 3;

--echo # Single-table update with mergeable derived table
--echo # (but not merged due to being used in a subquery of an UPDATE statement)
UPDATE users
SET position = (SELECT COUNT(pos) + 1
                FROM (SELECT position AS pos FROM users) AS t2
                WHERE t2.pos < users.position)
WHERE id = 3;

--echo # Multi-table update with non-mergeable derived table
UPDATE users, (SELECT 1) AS dummy
SET position = (SELECT COUNT(pos) + 1
                FROM (SELECT DISTINCT position AS pos FROM users) AS t2
                WHERE t2.pos < users.position)
WHERE id = 3;

--echo # Multi-table update with mergeable derived table
UPDATE users, (SELECT 1) AS dummy
SET position = (SELECT COUNT(pos) + 1
                FROM (SELECT position AS pos FROM users) AS t2
                WHERE t2.pos < users.position)
WHERE id = 3;

--error ER_UPDATE_TABLE_USED
UPDATE users
SET position = (SELECT COUNT(t2.position) + 1
                FROM users AS t2
                WHERE t2.position < users.position)
WHERE id = 3;

--error ER_UPDATE_TABLE_USED
UPDATE users, (SELECT 1) AS dummy
SET position = (SELECT COUNT(t2.position) + 1
                FROM users AS t2
                WHERE t2.position < users.position)
WHERE id = 3;

DROP TABLE users;

--echo # Bug#22783011 assertion failed: base_select != removed_select &&
--echo #              aggr_select != removed_select

CREATE TABLE t(a INTEGER) engine=innodb;
INSERT INTO t VALUES(1);
--echo # Per MySQL rules, SUM(1) is aggregated in top query
--echo # (can't be in WHERE, in FROM)
SELECT (SELECT 1 FROM (SELECT 1 FROM t WHERE SUM(1)) AS t);
SELECT (SELECT 1 FROM (SELECT 1 FROM t WHERE SUM(0)) AS t);
DROP TABLE t;

--echo # Bug#23074801: Delete from joined tables with where using derived table
--echo #               fails with error 1093

CREATE TABLE t1 (id INTEGER, d1 INTEGER);
CREATE TABLE t2 (id INTEGER, d2 INTEGER);
DELETE t1, t2
FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.id IN (SELECT * FROM (SELECT id FROM t1) AS t1sub);
DROP TABLE t1, t2;

--echo # Bug#22967439: Mysql 5.7 vs 5.6 incorrect SELECT statement result

CREATE TABLE t1
(integer1 INTEGER NULL,
 integer2 INTEGER NULL,
 varchar1 VARCHAR(255) NULL);

CREATE TABLE t2
(integer1 INTEGER NULL,
 integer2 INTEGER NULL,
 varchar1 VARCHAR(255) NULL,
 varchar2 VARCHAR(255) NULL);

INSERT INTO t1 VALUES
 (11,12,'test1'), (21,22,'test2'), (31,32,'test3');
INSERT INTO t2 VALUES
 (11,12,'test1','test12'), (21,22,'test2','test22'), (31,32,'test3','test32');

SELECT g_t3.g_f1, g_t3.g_f2, g_t3.g_f3
FROM (SELECT g_t0.integer1 AS g_f1,
             g_t0.integer2 AS g_f2,
             g_t0.varchar1 AS g_f3
      FROM t1 g_t0
     ) g_t3
GROUP BY g_t3.g_f1, g_t3.g_f2, g_t3.g_f3
HAVING g_t3.g_f3 = (select varchar1
                    from t2
                    where integer1 = g_t3.g_f1
                    );

DROP TABLE t1, t2;

--echo # Bug#24364448: Assert m_opr_handle != null in
--echo #               ha_innobase_inmem::keys_to_use_for_scanning

CREATE TABLE K (col_int int(11), col_varchar varchar(255)) charset latin1;
INSERT INTO K VALUES (NULL,'m'), (NULL,'z');

CREATE TABLE H (col_varchar varchar(255)) charset latin1;
INSERT INTO H VALUES ('m'), ('z');

SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";

FLUSH STATUS;

SELECT t1.col_int
FROM (SELECT * FROM K) t1 JOIN (SELECT * FROM H) t2 USING(col_varchar)
WHERE t1.col_int IS NULL
ORDER BY t1.col_int;

SHOW STATUS LIKE 'Created_tmp%';

SET @@optimizer_switch=@optimizer_switch_saved;

DROP TABLE K, H;

--echo #
--echo # Bug #25343335: DERIVED TABLE SELECT LIST'S REFERENCE TO ALIAS CAUSES
--echo #                INCORRECT BEHAVIOR
--echo #

CREATE TABLE t1 (c11 int);
INSERT INTO t1 VALUES (1);

#issue 1
let query1=
SELECT *
FROM (SELECT (1) alias1,
             (SELECT alias1) alias2
      FROM t1) X ;

eval EXPLAIN $query1;
eval $query1;
eval PREPARE xx FROM '$query1';
EXECUTE xx;
EXECUTE xx;
EXECUTE xx;

#issue 2
let query2=
SELECT alias2
FROM (SELECT (1) alias1,
             (SELECT alias1) alias2
      FROM t1) X ;

eval EXPLAIN $query2;
eval $query2;
eval PREPARE yy FROM '$query2';
EXECUTE yy;
EXECUTE yy;
EXECUTE yy;

DROP TABLE t1;


--echo #
--echo # Bug#26596977: ASSERT `!(USED_TABS & (~READ_TABLES & ~FILTER_FOR_TABLE))'
--echo #               FAILED IN ITEM_FUNC
--echo #

CREATE TABLE t1 (
  col_varchar_key varchar(1) ,
  pk int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (pk),
  KEY col_varchar_key (col_varchar_key)
) ENGINE=MyISAM;

CREATE TABLE t2 (
  col_int int(11) ,
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_varchar varchar(1) ,
  col_varchar_key varchar(1) ,
  PRIMARY KEY (pk)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES (4,1,'s','r'), (9,19,'p','a');

CREATE TABLE t3 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int int(11) ,
  col_varchar_key varchar(1) ,
  col_varchar varchar(1) ,
  PRIMARY KEY (pk),
  KEY col_varchar_key (col_varchar_key)
) ENGINE=MyISAM;
INSERT INTO t3 VALUES (100,2,'g',NULL);

let query1=
SELECT table1.col_int
FROM t3 AS table1
  LEFT JOIN (
      SELECT subquery1_t1.*
      FROM t1 AS subquery1_t1
        RIGHT JOIN t2 AS subquery1_t2
        ON (subquery1_t2.col_varchar_key = subquery1_t1.col_varchar_key)
    ) AS table3
    ON (table3.col_varchar_key = table1.col_varchar_key)
WHERE table3.col_varchar_key  IN (
  SELECT subquery2_t3.col_varchar
  FROM t3 AS subquery2_t3
  WHERE subquery2_t3.pk < table3.pk
);

eval EXPLAIN $query1;
eval $query1;

DROP TABLE t1, t2, t3;

--echo #
--echo # Bug #26618455: ASSERT 'KEYPARTS > 0' IN CALC_LENGTH_AND_KEYPARTS()
--echo #                WITHOUT FIRSTMATCH
--echo #


CREATE TABLE t1 (
  col_int int(11) DEFAULT NULL,
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_varchar_key varchar(1) DEFAULT NULL,
  col_varchar varchar(1) DEFAULT NULL,
  col_int_key int(11) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key)
) ENGINE=MyISAM;

INSERT INTO t1 VALUES (0,1,'i','n',125);
INSERT INTO t1 VALUES (3,20,'b','o',0);

CREATE TABLE t2 (
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  col_varchar varchar(1) DEFAULT NULL,
  col_int int(11) DEFAULT NULL,
  pk int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (pk),
  KEY col_varchar_key (col_varchar_key)
) ENGINE=MyISAM;

INSERT INTO t2 VALUES (3,'y','p',4,1);

CREATE TABLE t3 (
  col_int int(11) DEFAULT NULL,
  col_varchar varchar(1) DEFAULT NULL,
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_varchar_key varchar(1) DEFAULT NULL,
  col_int_key int(11) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key)
) ENGINE=MyISAM;

CREATE TABLE t4 (  col_int int(11) DEFAULT NULL,
  col_varchar varchar(1) DEFAULT NULL,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  pk int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (pk),
  KEY col_varchar_key (col_varchar_key)
) ENGINE=MyISAM
;
INSERT INTO t4 VALUES (7,'k',9,'z',20);

let query1=
SELECT
  table2.col_int_key AS field3
FROM
  (
    SELECT subquery2_t2.*
    FROM t1 AS subquery2_t1
      INNER JOIN t2 AS subquery2_t2
      ON (subquery2_t2.pk = subquery2_t1.col_int)
  ) AS table1
    RIGHT OUTER JOIN (
      SELECT subquery3_t1.*
      FROM t2 AS subquery3_t1
    ) AS table2
    ON (table1.col_varchar_key = table2.col_varchar_key)
WHERE table1.col_varchar_key IN (
  SELECT subquery4_t1.col_varchar AS subquery4_field1
  FROM t3 AS subquery4_t1);

SET optimizer_switch = 'firstmatch=off';
eval EXPLAIN $query1;
eval $query1;
SET optimizer_switch = default;

DROP TABLE t1, t2, t3, t4;

--echo # Bug#26798989: Sig 11 in find_table_in_global_list

CREATE TABLE t1(a INTEGER);
CREATE TABLE t2(a INTEGER);
UPDATE t1
SET a=5
WHERE a IN (SELECT a FROM t2
            ORDER BY (SELECT a FROM (SELECT SUM(a) FROM t1) AS dt));
DROP TABLE t1, t2;

--echo #
--echo # Bug#30381092 WRONG BEHAVIOR OF '=' CONDITION WHEN WORKING WITH COMPLEX SUBQUERIES
--echo #

# Parent table
CREATE TABLE t1 (t1_id INT PRIMARY KEY);
# Child table, one-to-many.
CREATE TABLE t2 (t2_id INT, t1_id INT);

INSERT INTO t1 VALUES (-1), (1);
INSERT INTO t2 SELECT t1_id, t1_id FROM t1;
ANALYZE TABLE t1,t2;

# ot.t2_id is a deeply nested outer reference.

# We verify that the pulling-out finds the outer reference. We hide
# this reference in various places :-)

# Inside the WHERE clause of a merged derived table in a scalar subquery.

let $query=
SELECT * FROM t2 AS ot
WHERE
( SELECT t1_id AS it1
  FROM ( SELECT * FROM t1 AS it2
         WHERE t1_id = ( SELECT t1_id
                         FROM ( SELECT * FROM t2 AS it3
                                WHERE t2_id=ot.t2_id
                              ) AS dt1
                       )
       ) AS dt2
) IS NOT NULL;

eval EXPLAIN $query;
eval $query;

# Inside the WHERE clause of a non-merged derived table in a scalar
# subquery.

let $query=
SELECT * FROM t2 AS ot
WHERE
( SELECT t1_id AS it1
  FROM ( SELECT * FROM t1 AS it2
         WHERE t1_id = ( SELECT /*+ NO_MERGE() */ t1_id
                         FROM ( SELECT * FROM t2 AS it3
                                WHERE t2_id=ot.t2_id
                              ) AS dt1
                       )
       ) AS dt2
) IS NOT NULL;

eval EXPLAIN $query;
eval $query;

DROP TABLE t1,t2;

--echo # Bug#30488700: Mysql chooses different execution plan in 5.7

CREATE TABLE tbl1 (
  id BIGINT NOT NULL,
  rec_id INTEGER NOT NULL,
  id_value2 BIGINT DEFAULT NULL,
  PRIMARY KEY (id, rec_id)
) ENGINE=INNODB;

CREATE TABLE tbl2 (
  t_id BIGINT NOT NULL,
  s_date DATETIME DEFAULT NULL,
  PRIMARY KEY (t_id)
) ENGINE=INNODB;

let select_statement =
SELECT t1.rec_id
FROM tbl1 AS t1 INNER JOIN
       (SELECT a.id, a.rec_id,s_date
        FROM tbl2 AS b, tbl1 AS a
        WHERE a.id_value2 = b.t_id AND a.id = 6889877970355107670
        ORDER BY s_date DESC) t2
     ON t1.id = t2.id AND t1.rec_id = t2.rec_id;

let update_statement =
UPDATE tbl1 AS t1 INNER JOIN
       (SELECT a.id, a.rec_id, b.s_date
        FROM tbl2 AS b, tbl1 AS a
        WHERE a.id_value2 = b.t_id AND a.id = 6889877970355107670
        ORDER BY s_date DESC) t2
       ON t1.id = t2.id AND t1.rec_id = t2.rec_id
SET t1.rec_id = @ROWNUM:= @ROWNUM+1;

SET @ROWNUM = 0;
eval $select_statement;
eval $update_statement;
eval explain $select_statement;
eval explain $update_statement;

DROP TABLE tbl1, tbl2;

--echo # Bug#26542829: Assertion !(check_datetime_range(ltime)) failed

CREATE TABLE t1 (
  c1 varchar(10) DEFAULT NULL,
  c2 datetime DEFAULT NULL,
  c3 varchar(255) DEFAULT NULL,
  c4 varchar(255) DEFAULT NULL,
  pk int NOT NULL,
  c5 varchar(255) DEFAULT NULL,
  c6 date DEFAULT NULL,
  c7 int DEFAULT NULL,
  c8 date DEFAULT NULL,
  c9 varchar(255) DEFAULT NULL,
  c10 int DEFAULT NULL,
  c11 varchar(10) DEFAULT NULL,
  c12 varchar(10) DEFAULT NULL,
  c13 datetime DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY k5 (c5)
) engine=innodb;

INSERT INTO t1 VALUES
('gjnhugwevl','1000-01-01 00:00:00','m','UJVFB',2,'j','1000-01-01',208797696,'1000-01-01','LRNWI',NULL,'look','ISVAL','2001-03-02 00:00:00'),
('nhugwevltb','1000-01-01 00:00:00','m','h',3,'can\'t','2004-06-07',2052784128,'2000-12-19','r',NULL,'ugwevltbgy','something','1000-01-01 00:00:00');

CREATE TABLE t2 (
  c1 varchar(255) DEFAULT NULL,
  c2 varchar(10) DEFAULT NULL,
  c3 int DEFAULT NULL,
  c4 datetime DEFAULT NULL,
  c5 varchar(10) DEFAULT NULL,
  c6 varchar(255) DEFAULT NULL,
  c7 varchar(255) DEFAULT NULL,
  c8 varchar(255) DEFAULT NULL,
  c9 varchar(10) DEFAULT NULL,
  c10 datetime DEFAULT NULL,
  pk int NOT NULL,
  c11 date DEFAULT NULL,
  c12 int DEFAULT NULL,
  PRIMARY KEY (pk)
) engine=innodb;

INSERT INTO t2 VALUES
('be','njhqcbglns',NULL,'2002-07-20 19:40:02','YHBGN','go','NSPET','j','MKFNT','2007-08-25 14:41:26',1,'2002-04-21',6),
('b','glnspcqnog',2,'1000-01-01 00:00:00','YEJTV','on','z','PPPCH','YRXFT','1000-01-01 00:00:00',4,'2003-07-25',0),
('c','qnogpabsxs',6,'1000-01-01 00:00:00','nogpabsxsr','b','we','og','e','2000-11-01 15:48:07',7,'1000-01-01',3),
('OFZJT','was',6,'2007-01-14 21:01:33','as','was','r','a','but','2009-01-20 17:58:56',9,'2005-02-14',7),
('d','bsxsrumtna',9,'1000-01-01 00:00:00','would','he','o','VESLR','sxsrumtnab','2007-09-25 12:13:37',10,'2005-02-11',772472832);

ANALYZE TABLE t1, t2;

SELECT field1
FROM (SELECT alias1.c5 AS field1,
             alias1.c13 AS field2,
             alias1.c1 AS field4
      FROM t1 AS alias1 RIGHT JOIN t2 AS alias2
           ON alias1.pk = alias2.c12
      ORDER BY field2
     ) as dt;

SELECT field1
FROM (SELECT *
      FROM (SELECT alias1.c5 AS field1,
                   alias1.c13 AS field2,
                   alias1.c1 AS field4
            FROM t1 AS alias1 RIGHT JOIN t2 AS alias2
                 ON alias1.pk = alias2.c12
            ORDER BY field2
           ) AS dt_inner
     ) AS dt_outer;

DROP TABLE t1, t2;

--echo # Bug#26808862: Execution of prepared statement with ORDER BY
--echo #               in derived table may fail

CREATE TABLE t1 (
  pk INTEGER NOT NULL,
  c1 INTEGER DEFAULT NULL,
  c2 INTEGER DEFAULT NULL,
  PRIMARY KEY (pk)
);

CREATE TABLE t2 (
  c1 integer DEFAULT NULL
);

prepare s from "
SELECT field1
FROM (SELECT alias1.c1 AS field1,
             alias1.c2 AS field2
      FROM t1 AS alias1 RIGHT JOIN t2 AS alias2
           ON alias1.pk = alias2.c1
      ORDER BY field2) as dt";

execute s;
execute s;

DROP TABLE t1, t2;

--echo # Bug#31565009: Simplification of condition when seeing false and
--echo #               <cond> leaks tmp tables opened

CREATE TABLE t1 (i int);

SELECT t1.i
FROM t1
WHERE FALSE AND
      t1.i > (SELECT MAX(a)
              FROM (SELECT 8 AS a UNION SELECT 3) AS tt);

DROP TABLE t1;

--echo # Bug#31401637: Asan heap-buffer-overflow Handler::index_only_read_time

CREATE TABLE t(
  w INTEGER NOT NULL,
  h INTEGER NOT NULL,
  u SMALLINT NOT NULL,
  PRIMARY KEY (u)
);
INSERT INTO t VALUES(1,2,3),(3,4,5),(6,6,7);
PREPARE s FROM "
SELECT dtaa.c AS c
FROM (SELECT 1 AS c
      FROM (SELECT 1 AS c
            FROM t AS ta RIGHT JOIN t AS tb ON ta.u  <= ?
            WHERE 1 OR ?
            GROUP BY ta.u
           ) AS dta
           INNER JOIN t AS tbb
           ON dta.c = tbb.h
     ) AS dtaa
     RIGHT JOIN t AS tbbb
     ON dtaa.c or w";
SET @a:='77777';
EXECUTE s USING @a,@a;
SET @a:='-77777';
EXECUTE s USING @a,@a;
DEALLOCATE PREPARE s;
DROP TABLE t;

--echo #
--echo # Bug #32162862: ORDER BY SUB-SELECT COLUMN SETS ALL VALUES TO NULL
--echo #

CREATE TABLE t1 ( a INTEGER ) Engine=InnoDB;
INSERT INTO t1 VALUES (123);
SELECT * FROM t1 LEFT JOIN ( SELECT 1 FROM t1 ) d1 ON TRUE ORDER BY a;
DROP TABLE t1;

--echo #
--echo # Bug #32164724: HYPERGRAPH: ASSERTION `PATH->COST >= PATH->INIT_COST' FAILED.
--echo #

CREATE TABLE t1 (a INTEGER);
INSERT INTO t1 VALUES (1);
SET sql_mode="";
SELECT (
  SELECT 1 FROM (
    SELECT avg(1) FROM t1
    WHERE dayofyear(1) AND max(1)
  ) d1
  GROUP BY a
) FROM t1;
SET sql_mode=DEFAULT;
DROP TABLE t1;

--echo #
--echo # Bug #32169846: HYPERGRAPH: ASSERTION `M_OPENED_TABLE != NULLPTR' FAILED.
--echo #

CREATE TABLE t1 (a LONGTEXT);
INSERT INTO t1 VALUES ('');
CREATE TABLE t2 (b INTEGER);
INSERT INTO t2 VALUES (0);

# The sort for GROUP BY needs to be on row ID due to the long blob in t1.
# This is incompatible with streaming of d1, so we want to check that it
# uses materialization instead. The rand() is there to force rematerialization
# every time (otherwise, streaming is not considered). The inner GROUP BY
# is there to preclude merging.
SELECT 1 FROM t2, (
  SELECT a, rand() FROM t1 GROUP BY a
) d1
GROUP BY b;

DROP TABLE t1, t2;

--echo # Bug#33856374: Query returns incorrect results in 8.0.22+

CREATE TABLE t1
 (a INTEGER,
  b INTEGER,
  c INTEGER
 );

INSERT INTO t1 VALUES
 (1, 1, 10), (1, 2, 20), (1, 3, 30), (2, 1, 40), (2, 2, 50), (2, 3, 60);

CREATE TABLE t2
 (a INTEGER,
  d INTEGER,
  e INTEGER
 );

INSERT INTO t2 VALUES
 (1, 6, 60), (2, 6, 60), (3, 6, 60);

WITH
cte AS
 (SELECT SUM(c) AS c, SUM(b) AS b, a
  FROM t1
  GROUP BY a)
SELECT t2.a, (SELECT MIN(c) FROM cte AS cte2 WHERE t2.d = cte2.b)
FROM t2 LEFT JOIN cte AS cte1 ON t2.a = cte1.a
        LEFT JOIN t2 AS tx ON tx.e = cte1.c;

DROP TABLE t1, t2;

--echo # Bug#32678303: Assertion 'table_num_to_node_num[table_num] != -1 failed

CREATE TABLE t1 (a INTEGER);

--error ER_BAD_FIELD_ERROR
SELECT *
FROM t1 AS ot1
     LEFT JOIN
     t1 AS ot2 ON ot1.a IN
        (SELECT * FROM (SELECT ot3.a) AS d
        )
     LEFT JOIN t1 AS ot3 ON TRUE;

--error ER_BAD_FIELD_ERROR
SELECT *
FROM t1 AS ot1
     LEFT JOIN
     t1 AS ot2 ON ot1.a IN
        (SELECT ot3.a
        )
     LEFT JOIN t1 AS ot3 ON TRUE;

DROP TABLE t1;

--echo # Bug#34131822: Outer references in INSERT ... VALUES are not recognized

CREATE TABLE t1 (a INTEGER, b INTEGER);

INSERT INTO t1 (a, b) VALUES (777, a);
INSERT INTO t1 (a, b) VALUES (888, (SELECT a));
INSERT INTO t1 (a, b) VALUES (999, (SELECT a UNION SELECT a));
SELECT a, b FROM t1;

DROP TABLE t1;

--echo #
--echo # Bug#33725542: A bug in sql_derived.cc cause mysql-server crash when
--echo #               input some sql statement..

WITH x(y) AS (SELECT 1 UNION SELECT 1)
SELECT (SELECT y FROM x WHERE y = 1) FROM x;

--echo #
--echo # Bug#35410465: Subquery ORDER BY is ignored
--echo #

CREATE TABLE t1 (f1 LONGTEXT, f2 INTEGER);
INSERT INTO t1 VALUES (NULL, NULL), (1,2);
SELECT f1
FROM (SELECT *
      FROM (SELECT t1.*
            FROM t1 LEFT JOIN t1 AS t2
                 ON (t1.f1 = t2.f1)) AS dt1 ORDER BY f1 asc) AS dt2;
DROP TABLE t1;

# Original repro
CREATE TABLE t1
 (a LONGTEXT,
  b BIGINT DEFAULT NULL,
  c DOUBLE DEFAULT NULL,
  d DATETIME DEFAULT NULL)
 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO t1 VALUES
(NULL,NULL,NULL,NULL),('Cat1',0,0.5,'2013-06-10 11:10:10'),
('Cat2',1,1.5,'2013-06-11 12:11:11'),('Cat1',2,2.5,'2013-06-12 13:12:12'),
('Cat2',3,3.5,'2013-06-13 14:13:13'),('Cat1',4,4.5,'2013-06-14 15:14:14'),
('Cat2',5,5.5,'2013-06-15 16:15:15'),('Cat1',6,6.5,'2013-06-16 17:16:16'),
('Cat2',7,7.5,'2013-06-17 18:17:17'),('Cat1',8,8.5,'2013-06-18 19:18:18');

SELECT a, b
FROM (SELECT a, b, count1, count2
      FROM (SELECT dt3.a, dt3.b, dt3.count1, count2
            FROM (SELECT dt1.a, dt1.b, count1
                  FROM (SELECT a, b
                        FROM (SELECT a, b FROM t1) AS dt GROUP BY a, b) AS dt1
                       LEFT JOIN
                       (SELECT a, COUNT(*) AS count1 FROM t1 GROUP BY a) AS dt2
                       ON (dt2.a = dt1.a)) AS dt3
                 LEFT JOIN
                 (SELECT a, COUNT(*) AS count2
                  FROM (SELECT a FROM t1) AS dt5 GROUP BY a) AS dt4
                 ON (dt3.a = dt4.a)) AS dt5
      ORDER BY count1 desc, a, count2 desc, b) AS dt6;

DROP TABLE t1;