5.5.38 merge

18 files changed, 658 insertions, 31 deletions

diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index 8ae61881c07..c81af134add 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -78,7 +78,7 @@ cast(cast(20010203101112.121314 as double) as datetime)
 2001-02-03 10:11:12
 select cast(cast(010203101112.12 as double) as datetime);
 cast(cast(010203101112.12 as double) as datetime)
-0001-02-03 10:11:12
+2001-02-03 10:11:12
 select cast(cast(20010203101112.121314 as decimal(32,6)) as datetime);
 cast(cast(20010203101112.121314 as decimal(32,6)) as datetime)
 2001-02-03 10:11:12
@@ -87,10 +87,10 @@ cast(20010203101112.121314 as datetime)
 2001-02-03 10:11:12
 select cast(110203101112.121314 as datetime);
 cast(110203101112.121314 as datetime)
-0011-02-03 10:11:12
+2011-02-03 10:11:12
 select cast(cast(010203101112.12 as double) as datetime);
 cast(cast(010203101112.12 as double) as datetime)
-0001-02-03 10:11:12
+2001-02-03 10:11:12
 select cast("2011-02-03 10:11:12.123456" as datetime);
 cast("2011-02-03 10:11:12.123456" as datetime)
 2011-02-03 10:11:12
@@ -111,7 +111,7 @@ cast(cast(20010203101112.5 as double) as datetime(1))
 2001-02-03 10:11:12.5
 select cast(cast(010203101112.12 as double) as datetime(2));
 cast(cast(010203101112.12 as double) as datetime(2))
-0001-02-03 10:11:12.12
+2001-02-03 10:11:12.12
 select cast(cast(20010203101112.121314 as decimal(32,6)) as datetime(6));
 cast(cast(20010203101112.121314 as decimal(32,6)) as datetime(6))
 2001-02-03 10:11:12.121314
@@ -120,10 +120,10 @@ cast(20010203101112.121314 as datetime(6))
 2001-02-03 10:11:12.121314
 select cast(110203101112.121314 as datetime(6));
 cast(110203101112.121314 as datetime(6))
-0011-02-03 10:11:12.121314
+2011-02-03 10:11:12.121314
 select cast(cast(010203101112.12 as double) as datetime(6));
 cast(cast(010203101112.12 as double) as datetime(6))
-0001-02-03 10:11:12.120000
+2001-02-03 10:11:12.120000
 select cast("2011-02-03 10:11:12.123456" as time);
 cast("2011-02-03 10:11:12.123456" as time)
 10:11:12
@@ -266,7 +266,7 @@ Warnings:
 Warning	1264	Out of range value for column 'cast(-1000 as double(5,2))' at row 1
 select cast(010203101112.121314 as datetime);
 cast(010203101112.121314 as datetime)
-0001-02-03 10:11:12
+2001-02-03 10:11:12
 select cast(120010203101112.121314 as datetime);
 cast(120010203101112.121314 as datetime)
 NULL
@@ -327,7 +327,7 @@ cast(cast(120010203101112.121314 as double) as datetime)
 NULL
 select cast(cast(1.1 as double) as datetime);
 cast(cast(1.1 as double) as datetime)
-0000-00-00 00:00:01
+NULL
 select cast(cast(-1.1 as double) as datetime);
 cast(cast(-1.1 as double) as datetime)
 NULL
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 3a3b69f1fc7..d0f42c3d3ec 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -539,6 +539,7 @@ n	d1	d2	result
 2085	2012-01-01 00:00:00	2013-01-01 00:00:00	0
 2084	2012-02-01 00:00:00	2013-01-01 00:00:00	0
 drop table t1;
+set @save_derived_optimizer_switch_bug=@@optimizer_switch;
 SET optimizer_switch = 'derived_merge=on,derived_with_keys=on,in_to_exists=on';
 CREATE TABLE t1 (a INT) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (8);
@@ -553,4 +554,17 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 Warnings:
 Note	1249	Select 4 was reduced during optimization
 DROP TABLE t1, t2;
+set optimizer_switch=@save_derived_optimizer_switch_bug;
+#
+# MDEV-6163: Error while executing an update query that has the
+# same table in a sub-query
+#
+set @save_derived_optimizer_switch_bug=@@optimizer_switch;
+SET optimizer_switch = 'derived_merge=on';
+create table t1 (balance float, accountId varchar(64), primary key (accountId));
+insert into t1 (accountId,balance) values 
+('dealer-1',199354.0),('dealer-2',0),('dealer-3',0),('dealer-5',0),('FINANCE',-200000),('OPERATOR',0);
+update t1 set balance=(select sum(balance) from (SELECT balance FROM t1 where accountId like 'dealer%') AS copied) where accountId = 'OPERATOR';
+set optimizer_switch=@save_derived_optimizer_switch_bug;
+drop table t1;
 set optimizer_switch=@save_derived_optimizer_switch;
diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result
index 5646de88d26..4753728793a 100644
--- a/mysql-test/r/dyncol.result
+++ b/mysql-test/r/dyncol.result
@@ -1001,29 +1001,29 @@ Warnings:
 Warning	1292	Truncated incorrect time value: '1223.5aa'
 select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as time);
 column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as time)
-NULL
+838:59:59
 Warnings:
-Warning	1292	Incorrect datetime value: '1.8446744073709552e19'
+Warning	1292	Truncated incorrect time value: '1.8446744073709552e19'
 select column_get(column_create(1, 9223372036854775807 AS int), 1 as time);
 column_get(column_create(1, 9223372036854775807 AS int), 1 as time)
-NULL
+838:59:59
 Warnings:
-Warning	1292	Incorrect datetime value: '9223372036854775807'
+Warning	1292	Truncated incorrect time value: '9223372036854775807'
 select column_get(column_create(1, -9223372036854775808 AS int), 1 as time);
 column_get(column_create(1, -9223372036854775808 AS int), 1 as time)
-NULL
+-838:59:59
 Warnings:
-Warning	1292	Incorrect datetime value: '-9223372036854775808'
+Warning	1292	Truncated incorrect time value: '-9223372036854775808'
 select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as time);
 column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as time)
-NULL
+838:59:59
 Warnings:
-Warning	1292	Incorrect datetime value: '99999999999999999999999999999'
+Warning	1292	Truncated incorrect time value: '99999999999999999999999999999'
 select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as time);
 column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as time)
-NULL
+838:59:59
 Warnings:
-Warning	1292	Incorrect datetime value: '1e29'
+Warning	1292	Truncated incorrect time value: '1e29'
 select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as time);
 column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as time)
 NULL
@@ -1435,6 +1435,17 @@ Warning	1265	Data truncated for column 'dyncol' at row 1
 SELECT COLUMN_ADD( dyncol, 'a', '22:22:22', 'c', REPEAT('x',270) AS CHAR ) FROM t1;
 DROP table t1;
 #
+# MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column
+#
+SELECT
+column_get(column_create(1, -999999999999999 AS int), 1 AS TIME) AS t1,
+column_get(column_create(1, -9223372036854775808 AS int), 1 AS TIME) AS t2;
+t1	t2
+-838:59:59	-838:59:59
+Warnings:
+Warning	1292	Truncated incorrect time value: '-999999999999999'
+Warning	1292	Truncated incorrect time value: '-9223372036854775808'
+#
 # end of 5.3 tests
 #
 select column_get(column_create(1, "18446744073709552001" as char), 1 as int);
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index cebba082ea4..c7643f79779 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -1091,9 +1091,9 @@ NULL
 select isnull(week(now() + 0)), isnull(week(now() + 0.2)),
 week(20061108), week(20061108.01), week(20061108085411.000002);
 isnull(week(now() + 0))	isnull(week(now() + 0.2))	week(20061108)	week(20061108.01)	week(20061108085411.000002)
-0	0	45	NULL	45
+0	0	45	45	45
 Warnings:
-Warning	1292	Incorrect datetime value: '20061108.01'
+Warning	1292	Truncated incorrect datetime value: '20061108.01'
 End of 4.1 tests
 select time_format('100:00:00', '%H %k %h %I %l');
 time_format('100:00:00', '%H %k %h %I %l')
@@ -2483,6 +2483,14 @@ SELECT 1 FROM t1 GROUP BY MONTHNAME(0) WITH ROLLUP;
 1
 DROP TABLE t1;
 #
+# MDEV-6099 Bad results for DATE_ADD(.., INTERVAL 2000000000000000000.0 SECOND)
+#
+SELECT DATE_ADD('2001-01-01 10:20:30',INTERVAL 250000000000.0 SECOND) AS c1, DATE_ADD('2001-01-01 10:20:30',INTERVAL 2000000000000000000.0 SECOND) AS c2;
+c1	c2
+9923-03-10 22:47:10.0	NULL
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '2000000000000000000.0'
+#
 # MDEV-4838 Wrong metadata for DATE_ADD('string', INVERVAL)
 #
 SELECT DATE_ADD('2011-01-02 12:13:14', INTERVAL 1 MINUTE);
diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result
index 932c1c76027..2d67d24bedd 100644
--- a/mysql-test/r/loaddata.result
+++ b/mysql-test/r/loaddata.result
@@ -520,3 +520,15 @@ LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug11735141.txt' INTO TABLE t1;
 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
 drop table t1;
 End of 5.1 tests
+#
+# Bug#11759519 INFINITE HANG WITH 100% CPU USAGE WITH LOAD DATA LOCAL AND IMPORT ERRORS
+#
+SET @old_mode= @@sql_mode;
+CREATE TABLE t1 (fld1 INT);
+SET sql_mode='strict_all_tables';
+# Without fix, load data hangs forever.
+LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/mysql' REPLACE INTO TABLE t1
+FIELDS TERMINATED BY 't' LINES TERMINATED BY '';
+Got one of the listed errors
+SET @@sql_mode= @old_mode;
+DROP TABLE t1;
diff --git a/mysql-test/r/myisampack.result b/mysql-test/r/myisampack.result
index a2ce12bffc1..13321695360 100644
--- a/mysql-test/r/myisampack.result
+++ b/mysql-test/r/myisampack.result
@@ -150,6 +150,60 @@ CHECK TABLE t1;
 Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
 DROP TABLE t1;
+create table `t1` (`id` varchar(15) DEFAULT NULL) ENGINE=MyISAM ROW_FORMAT=FIXED;
+insert into t1 values ('aaa'),('bbb'),('ccc'),('ddd'),('eee');
+insert into t1 (select * from t1);
+insert into t1 (select * from t1);
+insert into t1 (select * from t1);
+insert into t1 (select * from t1);
+checksum table t1;
+Table	Checksum
+test.t1	2696656816
+insert into t1 values(NULL);
+checksum table t1;
+Table	Checksum
+test.t1	2679879600
+flush table t1;
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+checksum table t1;
+Table	Checksum
+test.t1	2679879600
+alter table t1 checksum=1 row_format=fixed;
+checksum table t1;
+Table	Checksum
+test.t1	2679879600
+flush table t1;
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+checksum table t1;
+Table	Checksum
+test.t1	2679879600
+alter table t1 row_format=dynamic checksum=0;
+checksum table t1;
+Table	Checksum
+test.t1	2330021136
+flush table t1;
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+checksum table t1;
+Table	Checksum
+test.t1	2330021136
+alter table t1 checksum=1 row_format=dynamic;
+checksum table t1;
+Table	Checksum
+test.t1	2330021136
+flush table t1;
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+checksum table t1;
+Table	Checksum
+test.t1	2330021136
+drop table t1;
 #
 # BUG#11751736: DROP DATABASE STATEMENT SHOULD REMOVE .OLD SUFFIX FROM 
 # DATABASE DIRECTORY 
diff --git a/mysql-test/r/outfile_loaddata.result b/mysql-test/r/outfile_loaddata.result
index e91855b8dcd..ca3a42c087c 100644
--- a/mysql-test/r/outfile_loaddata.result
+++ b/mysql-test/r/outfile_loaddata.result
@@ -147,6 +147,14 @@ SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
 a	b	c
 1	ABC-АБВ	DEF-ÂÃÄ
 2	NULL	NULL
+SELECT * FROM t1;
+a	b	c
+1	ABC-АБВ	DEF-ÂÃÄ
+2	NULL	NULL
+SELECT * FROM t2;
+a	b	c
+1	ABC-АБВ	DEF-ÂÃÄ
+2	NULL	NULL
 SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' LINES STARTING BY 'ъ';
 Warnings:
 Warning	1638	Non-ASCII separator arguments are not fully supported
@@ -175,6 +183,14 @@ SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
 a	b	c
 1	ABC-АБВ	DEF-ÂÃÄ
 2	NULL	NULL
+SELECT * FROM t1;
+a	b	c
+1	ABC-АБВ	DEF-ÂÃÄ
+2	NULL	NULL
+SELECT * FROM t2;
+a	b	c
+1	ABC-АБВ	DEF-ÂÃÄ
+2	NULL	NULL
 # Default (binary) charset:
 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FROM t1;
 ##################################################
diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result
index ec7fd798d4c..0a4cf9932c0 100644
--- a/mysql-test/r/partition_pruning.result
+++ b/mysql-test/r/partition_pruning.result
@@ -3301,3 +3301,55 @@ explain partitions select * from t1 where a between 10 and 10+33;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p0,p1,p2	ALL	NULL	NULL	NULL	NULL	100	Using where
 drop table t0, t1;
+#
+# MDEV-6239: Partition pruning is not working as expected in an inner query
+#
+create table t1
+(
+company_id bigint(5),
+dept_id bigint(5),
+emp_id bigint(5),
+emp_name varchar(100),
+primary key (company_id, emp_id)
+) partition by list (company_id) (
+partition p_1000 values in (1000),
+partition p_2000 values in (2000),
+partition p_3000 values in (3000)
+);
+create table t2
+(
+company_id bigint(5),
+dept_id bigint(5),
+dept_name varchar(100),
+primary key (company_id, dept_id)
+) partition by list (company_id) (
+partition p_1000 values in (1000),
+partition p_2000 values in (2000),
+partition p_3000 values in (3000)
+);
+insert into t2 values 
+(1000, 10, 'Engineering'),
+(1000, 20, 'Product Management'),
+(1000, 30, 'QA'),
+(2000, 40, 'Support'),
+(2000, 50, 'Professional Services');
+insert into t1 values 
+(1000, 10, 1, 'John'),
+(1000, 10, 2, 'Smith'),
+(1000, 20, 3, 'Jacob'),
+(1000, 20, 4, 'Brian'),
+(1000, 30, 5, 'Chris'),
+(1000, 30, 6, 'Ryan'),
+(2000, 40, 7, 'Karin'),
+(2000, 40, 8, 'Jay'),
+(2000, 50, 9, 'Ana'),
+(2000, 50, 10, 'Jessica');
+# Table t2 should have only partition p_1000.
+explain partitions
+select * from t1
+where company_id = 1000
+and dept_id in (select dept_id from t2 where COMPANY_ID = 1000);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	p_1000	ref	PRIMARY	PRIMARY	8	const	2	Using index
+1	PRIMARY	t1	p_1000	ALL	PRIMARY	NULL	NULL	NULL	6	Using where; Using join buffer (flat, BNL join)
+drop table t1,t2;
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 01be3cbfe2c..f2ad42ebc8d 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -2047,6 +2047,60 @@ f1	f2	f3	f4
 DROP TABLE t1;
 DROP VIEW v3;
 #
+# MDEV-6105: Emoji unicode character string search query makes mariadb performance down
+#
+SET NAMES utf8;
+DROP TABLE IF EXISTS t1;
+Warnings:
+Note	1051	Unknown table 'test.t1'
+CREATE TABLE t1(
+id int AUTO_INCREMENT,
+fd varchar(20),
+primary key(id),
+index ix_fd(fd)
+)engine=innodb default charset=UTF8;
+INSERT INTO t1(id, fd) VALUES (null, ''),(null, 'matt'),(null, 'pitt'),(null, 'lee'),(null, 'kim');
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+# The following should show "Impossible WHERE" :
+explain
+SELECT * FROM t1 WHERE fd='😁';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+SELECT * FROM t1 WHERE fd='😁';
+id	fd
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+# The following must not use range access:
+explain select count(*) from t1 where fd <'😁';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	ix_fd	ix_fd	63	NULL	#	Using where; Using index
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+select count(*) from t1 where fd <'😁';
+count(*)
+40960
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+select count(*) from t1 ignore index (ix_fd) where fd <'😁';
+count(*)
+40960
+drop table t1;
+set names default;
+#
 # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE
 #
 CREATE TABLE t1 (pk INT PRIMARY KEY);
diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result
index dc6bed5fd98..16b35448c50 100644
--- a/mysql-test/r/range_mrr_icp.result
+++ b/mysql-test/r/range_mrr_icp.result
@@ -2049,6 +2049,60 @@ f1	f2	f3	f4
 DROP TABLE t1;
 DROP VIEW v3;
 #
+# MDEV-6105: Emoji unicode character string search query makes mariadb performance down
+#
+SET NAMES utf8;
+DROP TABLE IF EXISTS t1;
+Warnings:
+Note	1051	Unknown table 'test.t1'
+CREATE TABLE t1(
+id int AUTO_INCREMENT,
+fd varchar(20),
+primary key(id),
+index ix_fd(fd)
+)engine=innodb default charset=UTF8;
+INSERT INTO t1(id, fd) VALUES (null, ''),(null, 'matt'),(null, 'pitt'),(null, 'lee'),(null, 'kim');
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+# The following should show "Impossible WHERE" :
+explain
+SELECT * FROM t1 WHERE fd='😁';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+SELECT * FROM t1 WHERE fd='😁';
+id	fd
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+# The following must not use range access:
+explain select count(*) from t1 where fd <'😁';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	ix_fd	ix_fd	63	NULL	#	Using where; Using index
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+select count(*) from t1 where fd <'😁';
+count(*)
+40960
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+select count(*) from t1 ignore index (ix_fd) where fd <'😁';
+count(*)
+40960
+drop table t1;
+set names default;
+#
 # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE
 #
 CREATE TABLE t1 (pk INT PRIMARY KEY);
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index 2975176c64a..43ba6ead575 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -1148,5 +1148,35 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
 1	PRIMARY	t4	index	NULL	PRIMARY	59	NULL	2	Using where; Using index; End temporary
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-6263: Wrong result when using IN subquery with order by
+#
+CREATE TABLE t1 (
+id int(11) NOT NULL,
+nombre varchar(255) NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t1 (id, nombre) VALUES
+(1, 'row 1'),(2, 'row 2'),(3, 'row 3'),
+(4, 'row 4'),(5, 'row 5'),(6, 'row 6');
+CREATE TABLE t2 (
+id_algo int(11) NOT NULL,
+id_agente int(11) NOT NULL,
+PRIMARY KEY (id_algo,id_agente), 
+KEY another_data (id_agente)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2 (id_algo, id_agente) VALUES
+(1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1);
+SELECT * FROM t1 WHERE id in (select distinct id_agente from t2) ORDER BY nombre ASC;
+id	nombre
+1	row 1
+2	row 2
+3	row 3
+SELECT * FROM t1 WHERE id in (select distinct id_agente from t2);
+id	nombre
+1	row 1
+2	row 2
+3	row 3
+DROP TABLE t1, t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index ff97882c469..c45ab02a646 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -1163,6 +1163,36 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (incremental, BNL join)
 1	PRIMARY	t4	hash_index	NULL	#hash#$hj:PRIMARY	55:59	test.t3.t3_c	2	Using where; End temporary; Using join buffer (incremental, BNLH join)
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-6263: Wrong result when using IN subquery with order by
+#
+CREATE TABLE t1 (
+id int(11) NOT NULL,
+nombre varchar(255) NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t1 (id, nombre) VALUES
+(1, 'row 1'),(2, 'row 2'),(3, 'row 3'),
+(4, 'row 4'),(5, 'row 5'),(6, 'row 6');
+CREATE TABLE t2 (
+id_algo int(11) NOT NULL,
+id_agente int(11) NOT NULL,
+PRIMARY KEY (id_algo,id_agente), 
+KEY another_data (id_agente)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2 (id_algo, id_agente) VALUES
+(1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1);
+SELECT * FROM t1 WHERE id in (select distinct id_agente from t2) ORDER BY nombre ASC;
+id	nombre
+1	row 1
+2	row 2
+3	row 3
+SELECT * FROM t1 WHERE id in (select distinct id_agente from t2);
+id	nombre
+1	row 1
+2	row 2
+3	row 3
+DROP TABLE t1, t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
 #
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index f4d7d986a9d..0ff366b0d7a 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -1150,6 +1150,36 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
 1	PRIMARY	t4	index	NULL	PRIMARY	59	NULL	2	Using where; Using index; End temporary
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-6263: Wrong result when using IN subquery with order by
+#
+CREATE TABLE t1 (
+id int(11) NOT NULL,
+nombre varchar(255) NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t1 (id, nombre) VALUES
+(1, 'row 1'),(2, 'row 2'),(3, 'row 3'),
+(4, 'row 4'),(5, 'row 5'),(6, 'row 6');
+CREATE TABLE t2 (
+id_algo int(11) NOT NULL,
+id_agente int(11) NOT NULL,
+PRIMARY KEY (id_algo,id_agente), 
+KEY another_data (id_agente)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2 (id_algo, id_agente) VALUES
+(1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1);
+SELECT * FROM t1 WHERE id in (select distinct id_agente from t2) ORDER BY nombre ASC;
+id	nombre
+1	row 1
+2	row 2
+3	row 3
+SELECT * FROM t1 WHERE id in (select distinct id_agente from t2);
+id	nombre
+1	row 1
+2	row 2
+3	row 3
+DROP TABLE t1, t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
 set optimizer_switch=default;
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 5e5b37f480a..2ba7606b663 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -722,6 +722,15 @@ dt
 2013-09-21 08:20:30
 drop procedure test5041;
 SET @@timestamp=DEFAULT;
+#
+# MDEV-6097 Inconsistent results for CAST(int,decimal,double AS DATETIME)
+#
+SELECT
+CAST(010203101112 AS DATETIME(1)) AS c1,
+CAST(010203101112.2 AS DATETIME(1)) AS c2,
+CAST(010203101112.2+0e0 AS DATETIME(1)) AS c3;
+c1	c2	c3
+2001-02-03 10:11:12.0	2001-02-03 10:11:12.2	2001-02-03 10:11:12.2
 End of 5.3 tests
 #
 # Start of 5.5 tests
diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result
index 00a7a9b783e..aaf9819d79a 100644
--- a/mysql-test/r/type_time.result
+++ b/mysql-test/r/type_time.result
@@ -301,6 +301,34 @@ Field	Type	Null	Key	Default	Extra
 MAX(t1)+1	decimal(22,1)	YES		NULL	
 DROP TABLE t2,t1;
 #
+# MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column
+#
+SET sql_mode=traditional;
+CREATE TABLE t1 (a TIME(6));
+INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED));
+ERROR 22007: Incorrect time value: '18446744069414584320' for column 'a' at row 1
+SET sql_mode=DEFAULT;
+INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED));
+Warnings:
+Warning	1264	Out of range value for column 'a' at row 1
+SELECT * FROM t1;
+a
+838:59:59.999999
+DROP TABLE t1;
+SELECT TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED);
+TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED)
+0
+Warnings:
+Warning	1292	Truncated incorrect time value: '18446744069414584320'
+#
+# MDEV-6100 No warning on CAST(9000000 AS TIME)
+#
+SELECT CAST(9000000 AS TIME);
+CAST(9000000 AS TIME)
+838:59:59
+Warnings:
+Warning	1292	Truncated incorrect time value: '9000000'
+#
 # End of 5.3 tests
 #
 CREATE TABLE t1 (f1 TIME);
diff --git a/mysql-test/r/type_time_hires.result b/mysql-test/r/type_time_hires.result
index 9c96889c7a0..8096785dcc1 100644
--- a/mysql-test/r/type_time_hires.result
+++ b/mysql-test/r/type_time_hires.result
@@ -17,46 +17,46 @@ Warnings:
 Note	1265	Data truncated for column 'a' at row 1
 insert t1 values (99991231235959e1);
 Warnings:
-Warning	1265	Data truncated for column 'a' at row 1
+Warning	1264	Out of range value for column 'a' at row 1
 select * from t1;
 a
-00:00:00.000
 00:20:03.123
 01:02:03.456
 03:04:05.789
 15:47:11.123
+838:59:59.999
 select truncate(a, 6) from t1;
 truncate(a, 6)
-0.000000
 2003.123000
 10203.456000
 30405.789062
 154711.123000
+8385959.999000
 select a DIV 1 from t1;
 a DIV 1
-0
 2003
 10203
 30405
 154711
+8385959
 select group_concat(distinct a) from t1;
 group_concat(distinct a)
-00:00:00.000,00:20:03.123,01:02:03.456,03:04:05.789,15:47:11.123
+00:20:03.123,01:02:03.456,03:04:05.789,15:47:11.123,838:59:59.999
 alter table t1 engine=innodb;
 select * from t1 order by a;
 a
-00:00:00.000
 00:20:03.123
 01:02:03.456
 03:04:05.789
 15:47:11.123
+838:59:59.999
 select * from t1 order by a+0;
 a
-00:00:00.000
 00:20:03.123
 01:02:03.456
 03:04:05.789
 15:47:11.123
+838:59:59.999
 drop table t1;
 create table t1 (a time(4)) engine=innodb;
 insert t1 values ('2010-12-11 01:02:03.456789');
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index a3ad63c035a..6d99cad30f0 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -1876,8 +1876,8 @@ SELECT(SELECT 1 AS a FROM dual ORDER BY a DESC LIMIT 1) AS dev;
 dev
 1
 #
-# LP bug#1010729: Unexpected syntax error from UNION
-# (bug #54382)    with single-table join nest      
+# lp:1010729: Unexpected syntax error from UNION
+# (bug #54382) with single-table join nest      
 #
 CREATE TABLE t1 (a int);
 CREATE TABLE t2 (b int);
@@ -1892,3 +1892,12 @@ UNION
 SELECT t1.pk FROM t1 LEFT JOIN (t2) ON (t1.pk = t2.fk);
 pk
 DROP TABLE t1,t2;
+create table t1 (a int);
+insert t1 values (1),(2),(3),(1);
+explain select 1 from dual where exists (select max(a) from t1 group by a union select a+2 from t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
+3	UNION	t1	ALL	NULL	NULL	NULL	NULL	4	
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+drop table t1;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 1ea30658561..64b329e9e01 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -1,5 +1,5 @@
-drop table if exists t1,t2,t3,t4,t9,`t1a``b`,v1,v2,v3,v4,v5,v6;
-drop view if exists t1,t2,`t1a``b`,v1,v2,v3,v4,v5,v6;
+drop table if exists t1,t2,t3,t4,t5,t6,t9,`t1a``b`,v1,v2,v3,v4,v5,v6;
+drop view if exists t1,t2,t3,t4,t5,t6,t9,`t1a``b`,v1,v2,v3,v4,v5,v6;
 drop database if exists mysqltest;
 use test;
 SET @save_optimizer_switch=@@optimizer_switch;
@@ -4114,6 +4114,114 @@ a
 20
 DROP VIEW v1,v2,v3;
 DROP TABLE t1,t2;
+#
+# MDEV-6251: SIGSEGV in query optimizer (in set_check_materialized 
+# with MERGE view)
+#
+CREATE TABLE t1 (a1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t2 (b1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t3 (c1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t4 (d1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t5 (e1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t6 (f1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
+CREATE OR REPLACE view v1 AS
+SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+;
+SELECT 1
+FROM ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t1)
+LEFT OUTER JOIN ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t2) ON 1=1
+LEFT OUTER JOIN ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t3) ON 1=1
+LEFT OUTER JOIN ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t4) ON 1=1
+LEFT OUTER JOIN ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t5) ON 1=1
+LEFT OUTER JOIN ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t6) ON 1=1
+LEFT OUTER JOIN ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t7) ON 1=1
+LEFT OUTER JOIN ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t8) ON 1=1
+;
+1
+SELECT 1
+FROM (v1 t1)
+LEFT OUTER JOIN (v1 t2) ON 1=1
+LEFT OUTER JOIN (v1 t3) ON 1=1
+LEFT OUTER JOIN (v1 t4) ON 1=1
+LEFT OUTER JOIN (v1 t5) ON 1=1
+LEFT OUTER JOIN (v1 t6) ON 1=1
+LEFT OUTER JOIN (v1 t7) ON 1=1
+LEFT OUTER JOIN (v1 t8) ON 1=1
+;
+1
+drop view v1;
+drop table t1,t2,t3,t4,t5,t6;
 # -----------------------------------------------------------------
 # -- End of 5.2 tests.
 # -----------------------------------------------------------------
@@ -5052,6 +5160,114 @@ execute stmt1;
 deallocate prepare stmt1;
 drop view v1,v2;
 drop table t1,t2;
+#
+# MDEV-6251: SIGSEGV in query optimizer (in set_check_materialized 
+# with MERGE view)
+#
+CREATE TABLE t1 (a1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t2 (b1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t3 (c1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t4 (d1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t5 (e1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t6 (f1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
+CREATE OR REPLACE view v1 AS
+SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+;
+SELECT 1
+FROM ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t1)
+LEFT OUTER JOIN ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t2) ON 1=1
+LEFT OUTER JOIN ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t3) ON 1=1
+LEFT OUTER JOIN ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t4) ON 1=1
+LEFT OUTER JOIN ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t5) ON 1=1
+LEFT OUTER JOIN ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t6) ON 1=1
+LEFT OUTER JOIN ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t7) ON 1=1
+LEFT OUTER JOIN ((  SELECT 1
+FROM t1 a_alias_1
+LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
+LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
+LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
+LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
+LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
+) t8) ON 1=1
+;
+1
+SELECT 1
+FROM (v1 t1)
+LEFT OUTER JOIN (v1 t2) ON 1=1
+LEFT OUTER JOIN (v1 t3) ON 1=1
+LEFT OUTER JOIN (v1 t4) ON 1=1
+LEFT OUTER JOIN (v1 t5) ON 1=1
+LEFT OUTER JOIN (v1 t6) ON 1=1
+LEFT OUTER JOIN (v1 t7) ON 1=1
+LEFT OUTER JOIN (v1 t8) ON 1=1
+;
+1
+drop view v1;
+drop table t1,t2,t3,t4,t5,t6;
 # -----------------------------------------------------------------
 # -- End of 5.3 tests.
 # -----------------------------------------------------------------