Merge branch '10.0' into 10.1

17 files changed, 551 insertions, 7 deletions

diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index bce7f3f95bb..df4958bd2bc 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -1700,6 +1700,18 @@ alter table t1 add unique index if not exists idx(i);
 show create table t1;
 DROP TABLE t1;
 
+#
+# MDEV-8358 ADD PRIMARY KEY IF NOT EXISTS -> ERROR 1068 (42000): Multiple primary key
+#
+
+CREATE TABLE t1 (
+   `event_id` bigint(20) unsigned NOT NULL DEFAULT '0',
+   `market_id` bigint(20) unsigned NOT NULL DEFAULT '0',
+ PRIMARY KEY (`event_id`,`market_id`)
+ );
+ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS event_id (event_id,market_id);
+DROP TABLE t1;
+
 --echo #
 --echo # MDEV-7374 : Losing connection to MySQL while running ALTER TABLE
 --echo #
@@ -1722,6 +1734,3 @@ ALTER TABLE t1 DROP INDEX i1, ADD INDEX i1(a) COMMENT 'comment2';
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
---echo #
---echo # Start of 10.1 tests
---echo #
diff --git a/mysql-test/t/dyncol.test b/mysql-test/t/dyncol.test
index 86dcee8148a..ac55fec3e2b 100644
--- a/mysql-test/t/dyncol.test
+++ b/mysql-test/t/dyncol.test
@@ -872,5 +872,17 @@ set     impressions = column_add(impressions,
 drop table t1;
 
 --echo #
+--echo # MDEV-8565: COLUMN_CHECK fails on valid data
+--echo #
+
+SELECT COLUMN_CHECK(COLUMN_CREATE('a',0,'b','1'));
+
+SELECT COLUMN_CHECK(COLUMN_CREATE('a',1,'b','1'));
+
+SELECT COLUMN_JSON(COLUMN_CREATE('a',0,'b','1'));
+
+SELECT COLUMN_JSON(COLUMN_CREATE('a',1,'b','1'));
+
+--echo #
 --echo # end of 10.0 tests
 --echo #
diff --git a/mysql-test/t/events_restart.test b/mysql-test/t/events_restart.test
index c6152e5d961..7f01859e059 100644
--- a/mysql-test/t/events_restart.test
+++ b/mysql-test/t/events_restart.test
@@ -71,9 +71,9 @@ drop event intact_check_1;
 drop event intact_check_2;
 --error ER_EVENTS_DB_ERROR
 drop event intact_check;
---error ER_EVENTS_DB_ERROR
+--error ER_STARTUP
 set global event_scheduler=on;
---error ER_EVENTS_DB_ERROR
+--error ER_STARTUP
 set global event_scheduler=off;
 show variables like 'event_scheduler';
 --echo Make sure that we still can create and drop databases,
@@ -84,6 +84,16 @@ drop database mysqltest_db1;
 --echo Restore the original mysql.event table
 drop table mysql.event;
 rename table event_like to mysql.event;
+
+--echo check that we can now enable events without restart
+set global event_scheduler=original;
+select @@global.event_scheduler;
+set global event_scheduler=on;
+select @@global.event_scheduler;
+--sorted_result
+--replace_column 6 # 9 # 10 #
+show events;
+
 --echo Now let's restart the server again
 
 --source include/restart_mysqld.inc
diff --git a/mysql-test/t/init_file_set_password-7656.test b/mysql-test/t/init_file_set_password-7656.test
new file mode 100644
index 00000000000..ecee3924355
--- /dev/null
+++ b/mysql-test/t/init_file_set_password-7656.test
@@ -0,0 +1,26 @@
+#
+# MDEV-7656 init_file option does not allow changing passwords
+#
+--source include/not_embedded.inc
+
+create user foo@localhost;
+
+select user,host,password from mysql.user where user='foo';
+
+--write_file $MYSQLTEST_VARDIR/init.file
+grant all on *.* to foo@localhost identified by 'test';
+EOF
+
+--enable_reconnect
+
+--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+
+--shutdown_server 10
+--source include/wait_until_disconnected.inc
+
+--exec echo "restart:--init-file=$MYSQLTEST_VARDIR/init.file  " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+
+--source include/wait_until_connected_again.inc
+select user,host,password from mysql.user where user='foo';
+
+drop user foo@localhost;
diff --git a/mysql-test/t/lowercase_fs_off.test b/mysql-test/t/lowercase_fs_off.test
index cb5cd711c7f..b8a9795db9a 100644
--- a/mysql-test/t/lowercase_fs_off.test
+++ b/mysql-test/t/lowercase_fs_off.test
@@ -105,4 +105,12 @@ RENAME TABLE t1 TO T1;
 ALTER TABLE T1 RENAME t1;
 DROP TABLE t1;
 
+#
+# MDEV-9014 SHOW TRIGGERS not case sensitive
+#
+create table t1 (a int);
+create trigger t1_bi before insert on t1 for each row set new.a= 1;
+show triggers like '%T1%';
+drop table t1;
+
 set GLOBAL sql_mode=default;
diff --git a/mysql-test/t/mysql_upgrade-6984.test b/mysql-test/t/mysql_upgrade-6984.test
index 6f10d3f33e9..9bbfbeb3f87 100644
--- a/mysql-test/t/mysql_upgrade-6984.test
+++ b/mysql-test/t/mysql_upgrade-6984.test
@@ -19,4 +19,5 @@ connect(con1,localhost,root,foo,,,);
 
 update mysql.user set password='' where user='root';
 flush privileges;
-
+# Load event table
+set global event_scheduler=OFF;
diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test
index b982ab8d2e8..d7f683aa9e9 100644
--- a/mysql-test/t/partition_innodb.test
+++ b/mysql-test/t/partition_innodb.test
@@ -858,3 +858,45 @@ INSERT INTO t3 SELECT * FROM t2 WHERE f3 = 'm' AND f2 ='c';
 DROP TABLE t1,t2,t3;
 
 set global default_storage_engine=default;
+
+--echo #
+--echo # Bug#13737949: CRASH IN HA_PARTITION::INDEX_INIT
+--echo # Bug#18694052: SERVER CRASH IN HA_PARTITION::INIT_RECORD_PRIORITY_QUEUE
+--echo #
+CREATE TABLE t1
+(a INT,
+ b INT,
+ PRIMARY KEY (a))
+ ENGINE = InnoDB
+ PARTITION BY HASH (a) PARTITIONS 3;
+ START TRANSACTION WITH CONSISTENT SNAPSHOT;
+ --connect (con1, localhost, root,,)
+ --echo # con1
+ ALTER TABLE t1 ADD INDEX idx1 (b);
+ --connection default
+ --echo # con default
+ --error ER_TABLE_DEF_CHANGED
+ SELECT b FROM t1 WHERE b = 0;
+ --error ER_TABLE_DEF_CHANGED
+ SELECT b FROM t1 WHERE b = 0;
+ --disconnect con1
+ DROP TABLE t1;
+
+--echo # Same test without partitioning
+CREATE TABLE t1
+(a INT,
+ b INT,
+ PRIMARY KEY (a))
+ ENGINE = InnoDB;
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
+--echo # con1
+--connect (con1, localhost, root,,)
+ALTER TABLE t1 ADD INDEX idx1 (b);
+--connection default
+--echo # con default
+--error ER_TABLE_DEF_CHANGED
+SELECT b FROM t1 WHERE b = 0;
+--error ER_TABLE_DEF_CHANGED
+SELECT b FROM t1 WHERE b = 0;
+--disconnect con1
+DROP TABLE t1;
diff --git a/mysql-test/t/partition_myisam.test b/mysql-test/t/partition_myisam.test
index bce0c6f009c..d07637057e0 100644
--- a/mysql-test/t/partition_myisam.test
+++ b/mysql-test/t/partition_myisam.test
@@ -123,6 +123,7 @@ CHECK TABLE t1;
 SELECT * FROM t1;
 --echo # Note that it is currently impossible to drop a partitioned table
 --echo # without the .par file
+--replace_result "Not owner" "Operation not permitted"
 --error ER_GET_ERRNO
 DROP TABLE t1;
 --remove_file $MYSQLD_DATADIR/test/t1.frm
diff --git a/mysql-test/t/partition_not_blackhole.test b/mysql-test/t/partition_not_blackhole.test
index 64d4f3877c8..d9e653b5252 100644
--- a/mysql-test/t/partition_not_blackhole.test
+++ b/mysql-test/t/partition_not_blackhole.test
@@ -19,6 +19,9 @@ SHOW TABLES;
 --replace_result $MYSQLD_DATADIR ./
 --error ER_FAILED_READ_FROM_PAR_FILE
 SHOW CREATE TABLE t1;
+
+# The replace is needed for Solaris
+--replace_result "Not owner" "Operation not permitted"
 --error ER_GET_ERRNO
 DROP TABLE t1;
 --list_files $MYSQLD_DATADIR/test t1*
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index 96dd88f5eb6..3881d522bbf 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -3650,3 +3650,25 @@ SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1;
 drop table t1;
 
 --echo # End of 5.3 tests
+
+--echo #
+--echo # MDEV-8756: MariaDB 10.0.21 crashes during PREPARE
+--echo #
+
+CREATE TABLE t1 ( id INT(10), value INT(10) );
+CREATE TABLE t2 ( id INT(10) );
+SET @save_sql_mode= @@sql_mode;
+SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';
+
+PREPARE stmt FROM 'UPDATE t1 t1 SET value = (SELECT 1 FROM t2 WHERE id = t1.id)'; 
+execute stmt;
+insert into t1 values (1,10),(2,10),(3,10);
+insert into t2 values (1),(2);
+execute stmt;
+select * from t1;
+deallocate prepare stmt;
+SET SESSION sql_mode = @save_sql_mode;
+DROP TABLE t1,t2;
+
+
+--echo # End of 10.0 tests
diff --git a/mysql-test/t/ps_change_master.test b/mysql-test/t/ps_change_master.test
new file mode 100644
index 00000000000..d756b8cd4fb
--- /dev/null
+++ b/mysql-test/t/ps_change_master.test
@@ -0,0 +1,45 @@
+--source include/not_embedded.inc
+--source include/have_log_bin.inc
+
+--echo #
+--echo # CHANGE MASTER TO doesn't work with prepared statements
+--echo #
+
+CHANGE MASTER TO MASTER_HOST='host1', MASTER_USER='user1';
+
+let $master_host= query_get_value(SHOW SLAVE STATUS, Master_Host, 1);
+let $master_user= query_get_value(SHOW SLAVE STATUS, Master_User, 1);
+
+--echo # Master_Host : $master_host
+--echo # Master_User : $master_user
+
+SET @s := "CHANGE MASTER TO MASTER_HOST='host2'";
+PREPARE stmt FROM @s;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+let $master_host= query_get_value(SHOW SLAVE STATUS, Master_Host, 1);
+let $master_user= query_get_value(SHOW SLAVE STATUS, Master_User, 1);
+
+--echo # Master_Host : $master_host
+--echo # Master_User : $master_user
+
+SET @s := "CHANGE MASTER TO MASTER_USER='user2'";
+PREPARE stmt FROM @s;
+EXECUTE stmt;
+# Multiple executions should not hurt.
+EXECUTE stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+let $master_host= query_get_value(SHOW SLAVE STATUS, Master_Host, 1);
+let $master_user= query_get_value(SHOW SLAVE STATUS, Master_User, 1);
+
+--echo # Master_Host : $master_host
+--echo # Master_User : $master_user
+
+
+# Reset
+CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='root';
+
+--echo # End of test
diff --git a/mysql-test/t/skip_grants.test b/mysql-test/t/skip_grants.test
index 2dc64254a9a..5f79404e7e4 100644
--- a/mysql-test/t/skip_grants.test
+++ b/mysql-test/t/skip_grants.test
@@ -112,8 +112,8 @@ DROP FUNCTION f3;
 #
 # Bug #26807 "set global event_scheduler=1" and --skip-grant-tables crashes server
 #
---error ER_OPTION_PREVENTS_STATEMENT
 set global event_scheduler=1;
+set global event_scheduler=0;
 
 #
 # Bug#26285 Selecting information_schema crahes server
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index 25ca322ca0a..4cbaa9e27c8 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -232,4 +232,77 @@ SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # MDEV-7370: Server deadlocks on renaming a table for which persistent statistics exists
+--echo #
+
+--disable_warnings
+drop database if exists db1;
+drop database if exists db1;
+--enable_warnings
+
+create database db1;
+create database db2;
+use db1;
+--echo #
+--echo # First, run the original testcase:
+--echo #
+create table t1 (i int);
+insert into t1 values (10),(20);
+analyze table t1 persistent for all;
+rename table t1 to db2.t1;
+
+--echo # Verify that stats in the old database are gone:
+select * from mysql.column_stats where db_name='db1' and table_name='t1';
+select * from mysql.table_stats where db_name='db1' and table_name='t1';
+
+--echo # Verify that stats are present in the new database:
+select * from mysql.column_stats where db_name='db2' and table_name='t1';
+select * from mysql.table_stats where db_name='db2' and table_name='t1';
+
+
+--echo #
+--echo # Now, try with more than one column and with indexes:
+--echo #
+use test;
+create table t1(a int primary key);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+
+use db1;
+create table t2 (a int, b int, c int, key IDX1(a), key IDX2(a,b));
+insert into t2 select a/10, a/2, a from test.t1;
+analyze table t2 persistent for all;
+
+alter table t2 rename db2.t2;
+
+--echo # Verify that stats in the old database are gone:
+select * from mysql.table_stats where db_name='db1' and table_name='t2';
+select * from mysql.column_stats where db_name='db1' and table_name='t2';
+select * from mysql.index_stats where db_name='db1' and table_name='t2';
+
+--echo # Verify that stats are present in the new database:
+select * from mysql.table_stats where db_name='db2' and table_name='t2';
+select * from mysql.column_stats where db_name='db2' and table_name='t2';
+select * from mysql.index_stats where db_name='db2' and table_name='t2';
+
+use db2;
+--echo #
+--echo # Now, rename within the same database and verify:
+--echo #
+rename table t2 to t3;
+--echo # No stats under old name:
+select * from mysql.table_stats where db_name='db2' and table_name='t2';
+select * from mysql.column_stats where db_name='db2' and table_name='t2';
+select * from mysql.index_stats where db_name='db2' and table_name='t2';
+--echo # Stats under the new name:
+select * from mysql.table_stats where db_name='db2' and table_name='t3';
+select * from mysql.column_stats where db_name='db2' and table_name='t3';
+select * from mysql.index_stats where db_name='db2' and table_name='t3';
+
+use test;
+drop database db1;
+drop database db2;
+drop table t1;
+
 set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/t/statistics.test b/mysql-test/t/statistics.test
index 36e2c5a77bd..2c8dec307cc 100644
--- a/mysql-test/t/statistics.test
+++ b/mysql-test/t/statistics.test
@@ -701,3 +701,19 @@ drop table t1, t2;
 
 set use_stat_tables=@save_use_stat_tables;
 
+--echo #
+--echo # Bug MDEV-7383: min/max value for a column not utf8 compatible
+--echo #
+
+create table t1 (a varchar(100)) engine=MyISAM;
+insert into t1 values(unhex('D879626AF872675F73E662F8'));
+analyze table t1 persistent for all;
+show warnings;
+
+select db_name, table_name, column_name,
+       HEX(min_value), HEX(max_value),
+       nulls_ratio, avg_frequency,
+       hist_size, hist_type, HEX(histogram)
+  FROM mysql.column_stats;
+
+drop table t1;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 0a542ac9888..a862870e9ff 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -5966,3 +5966,26 @@ EXECUTE stmt;
 
 deallocate prepare stmt;
 drop table t1,t2,t3,t4;
+
+--echo #
+--echo # MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE ||
+--echo # m_lock_type != 2' failed in  handler::ha_index_read_map
+--echo #
+
+CREATE TABLE t1 (f1 INT);
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (f2 INT, KEY(f2));
+INSERT INTO t2 VALUES (3);
+
+CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+
+SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
+SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0;
+SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 );
+
+delete from t1;
+SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
+
+drop view v2;
+drop table t1,t2;
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test
index 05f9ce39bec..e5ef0b11127 100644
--- a/mysql-test/t/update.test
+++ b/mysql-test/t/update.test
@@ -560,6 +560,79 @@ ROLLBACK;
 DROP TABLE t1, t2;
 
 --echo #
+--echo # MDEV-8938: Server Crash on Update with joins
+--echo #
+
+CREATE TABLE `t1` (
+  `name` varchar(255) NOT NULL,
+  `value` varchar(4095) DEFAULT NULL,
+  PRIMARY KEY (`name`)
+);
+
+UPDATE `t1` SET value = CONCAT("*.",(SELECT `temptable`.`value` FROM (SELECT * FROM `t1` WHERE `name`="consoleproxy.url.domain") AS `temptable` WHERE `temptable`.`name`="consoleproxy.url.domain")) WHERE `name`="consoleproxy.url.domain";
+
+drop table t1;
+
+CREATE TABLE `t1` (
+  `name` varchar(255) NOT NULL,
+  `value` varchar(4095) DEFAULT NULL,
+  PRIMARY KEY (`name`)
+);
+
+create table t2 (
+  `name` varchar(255) NOT NULL,
+  `value` varchar(4095) DEFAULT NULL,
+  PRIMARY KEY (`name`)
+);
+
+UPDATE t1
+SET value = (SELECT value FROM t2 WHERE `name`= t1.name) 
+WHERE value is null ;
+
+drop table t1,t2;
+
+--echo #
+--echo #MDEV-8701: Crash on derived query
+--echo #
+
+CREATE TABLE t1 (
+  data_exit_entry_id int(11) NOT NULL,
+  data_entry_id int(11) NOT NULL,
+  data_entry_exit_id int(11) NOT NULL,
+  data_exit_entry_quantity double NOT NULL
+) DEFAULT CHARSET=utf8;
+
+CREATE TABLE t2 (
+  data_entry_id int(11) NOT NULL,
+  data_entry_cost double NOT NULL,
+  data_entry_quantity double NOT NULL
+) DEFAULT CHARSET=utf8;
+
+create  algorithm=temptable view v1 as SELECT data_entry_exit_id, data_exit_entry_quantity, data_entry_cost 
+          FROM t1 INNER JOIN t2 as dt ON dt.data_entry_id = t1.data_entry_id;
+
+UPDATE t2 
+SET data_entry_cost
+  = ( ( SELECT SUM(data_exit_entry_quantity * data_entry_cost)
+	FROM 
+        v1 AS query 
+        WHERE data_entry_exit_id = t2.data_entry_id
+      )
+    );
+
+UPDATE t2 
+SET data_entry_cost
+  = ( ( SELECT SUM(data_exit_entry_quantity * data_entry_cost)
+	FROM 
+        ( SELECT data_entry_exit_id, data_exit_entry_quantity, data_entry_cost 
+          FROM t1 INNER JOIN t2 as dt ON dt.data_entry_id = t1.data_entry_id) AS query 
+        WHERE data_entry_exit_id = t2.data_entry_id
+      )
+    );
+
+drop view v1;
+drop table t1, t2; 
+--echo #
 --echo # MDEV-4410: update does not want to use a covering index, but select uses it.
 --echo #
 create table t2(a int);
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index f57025bcccb..b5426ce53f4 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -5456,6 +5456,41 @@ EXECUTE stmt;
 DROP TABLE t1, t2, t3;
 DROP VIEW v3;
 
+--echo #
+--echo # MDEV-8632: Segmentation fault on INSERT
+--echo #
+CREATE TABLE `t1` (
+  `id` int(10) unsigned NOT NULL,
+  `r` float NOT NULL,
+  PRIMARY KEY (`id`)
+) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+create view v1 as select id, if(r=r,1,2) as d from t1;
+create view v2 as
+  select id, 
+  d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d as p
+  from v1;
+insert into t1 (id, r)
+select id,p from
+(
+  select id, 
+  d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d as p
+  from (
+    select id, if(r=r,1,2) as d
+    from t1
+  ) a
+) b
+on duplicate key update r=p;
+insert into t1 (id, r)
+select id,p from v2 
+on duplicate key update r=p;
+
+prepare stmt from "insert into t1 (id, r) select id,p from v2 on duplicate key update r=p";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop view v1,v2;
+drop table `t1`;
 --echo # -----------------------------------------------------------------
 --echo # -- End of 5.5 tests.
 --echo # -----------------------------------------------------------------
@@ -5500,6 +5535,151 @@ execute stmt;
 drop table t1, t2;
 drop view v3;
 
+--echo #
+--echo # MDEV-8525: mariadb 10.0.20 crashing when data is read by Kodi 
+--echo # media center (http://kodi.tv).
+--echo #
+
+CREATE TABLE `t1` (
+  `idSong` int(11) NOT NULL AUTO_INCREMENT,
+  `idAlbum` int(11) DEFAULT NULL,
+  `idPath` int(11) DEFAULT NULL,
+  `strArtists` text,
+  `strGenres` text,
+  `strTitle` varchar(512) DEFAULT NULL,
+  `iTrack` int(11) DEFAULT NULL,
+  `iDuration` int(11) DEFAULT NULL,
+  `iYear` int(11) DEFAULT NULL,
+  `dwFileNameCRC` text,
+  `strFileName` text,
+  `strMusicBrainzTrackID` text,
+  `iTimesPlayed` int(11) DEFAULT NULL,
+  `iStartOffset` int(11) DEFAULT NULL,
+  `iEndOffset` int(11) DEFAULT NULL,
+  `idThumb` int(11) DEFAULT NULL,
+  `lastplayed` varchar(20) DEFAULT NULL,
+  `rating` char(1) DEFAULT '0',
+  `comment` text,
+  `mood` text,
+  PRIMARY KEY (`idSong`),
+  UNIQUE KEY `idxSong7` (`idAlbum`,`strMusicBrainzTrackID`(36)),
+  KEY `idxSong` (`strTitle`(255)),
+  KEY `idxSong1` (`iTimesPlayed`),
+  KEY `idxSong2` (`lastplayed`),
+  KEY `idxSong3` (`idAlbum`),
+  KEY `idxSong6` (`idPath`,`strFileName`(255))
+)  DEFAULT CHARSET=utf8;
+
+INSERT INTO `t1` VALUES (1,1,1,'strArtists1','strGenres1','strTitle1',1,100,2000,NULL,'strFileName1','strMusicBrainzTrackID1',0,0,0,NULL,NULL,'0','',''),(2,2,2,'strArtists2','strGenres2','strTitle2',2,200,2001,NULL,'strFileName2','strMusicBrainzTrackID2',0,0,0,NULL,NULL,'0','','');
+
+CREATE TABLE `t2` (
+  `idAlbum` int(11) NOT NULL AUTO_INCREMENT,
+  `strAlbum` varchar(256) DEFAULT NULL,
+  `strMusicBrainzAlbumID` text,
+  `strArtists` text,
+  `strGenres` text,
+  `iYear` int(11) DEFAULT NULL,
+  `idThumb` int(11) DEFAULT NULL,
+  `bCompilation` int(11) NOT NULL DEFAULT '0',
+  `strMoods` text,
+  `strStyles` text,
+  `strThemes` text,
+  `strReview` text,
+  `strImage` text,
+  `strLabel` text,
+  `strType` text,
+  `iRating` int(11) DEFAULT NULL,
+  `lastScraped` varchar(20) DEFAULT NULL,
+  `dateAdded` varchar(20) DEFAULT NULL,
+  `strReleaseType` text,
+  PRIMARY KEY (`idAlbum`),
+  UNIQUE KEY `idxAlbum_2` (`strMusicBrainzAlbumID`(36)),
+  KEY `idxAlbum` (`strAlbum`(255)),
+  KEY `idxAlbum_1` (`bCompilation`)
+) DEFAULT CHARSET=utf8;
+
+INSERT INTO `t2` VALUES (1,'strAlbum1','strMusicBrainzAlbumID1','strArtists1','strGenres1',2000,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'album');
+
+CREATE TABLE `t3` (
+  `idArtist` int(11) DEFAULT NULL,
+  `idAlbum` int(11) DEFAULT NULL,
+  `strJoinPhrase` text,
+  `boolFeatured` int(11) DEFAULT NULL,
+  `iOrder` int(11) DEFAULT NULL,
+  `strArtist` text,
+  UNIQUE KEY `idxAlbumArtist_1` (`idAlbum`,`idArtist`),
+  UNIQUE KEY `idxAlbumArtist_2` (`idArtist`,`idAlbum`),
+  KEY `idxAlbumArtist_3` (`boolFeatured`)
+) DEFAULT CHARSET=utf8;
+
+INSERT INTO `t3` VALUES (1,1,'',0,0,'strArtist1');
+
+CREATE TABLE `t4` (
+  `idArtist` int(11) NOT NULL AUTO_INCREMENT,
+  `strArtist` varchar(256) DEFAULT NULL,
+  `strMusicBrainzArtistID` text,
+  `strBorn` text,
+  `strFormed` text,
+  `strGenres` text,
+  `strMoods` text,
+  `strStyles` text,
+  `strInstruments` text,
+  `strBiography` text,
+  `strDied` text,
+  `strDisbanded` text,
+  `strYearsActive` text,
+  `strImage` text,
+  `strFanart` text,
+  `lastScraped` varchar(20) DEFAULT NULL,
+  `dateAdded` varchar(20) DEFAULT NULL,
+  PRIMARY KEY (`idArtist`),
+  UNIQUE KEY `idxArtist1` (`strMusicBrainzArtistID`(36)),
+  KEY `idxArtist` (`strArtist`(255))
+) DEFAULT CHARSET=utf8;
+
+INSERT INTO `t4` VALUES (1,'strArtist1','strMusicBrainzArtistID',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
+
+CREATE VIEW `v1` AS select `t2`.`idAlbum` AS `idAlbum`,`t2`.`strAlbum` AS `strAlbum`,`t2`.`strMusicBrainzAlbumID` AS `strMusicBrainzAlbumID`,`t2`.`strArtists` AS `strArtists`,`t2`.`strGenres` AS `strGenres`,`t2`.`iYear` AS `iYear`,`t2`.`strMoods` AS `strMoods`,`t2`.`strStyles` AS `strStyles`,`t2`.`strThemes` AS `strThemes`,`t2`.`strReview` AS `strReview`,`t2`.`strLabel` AS `strLabel`,`t2`.`strType` AS `strType`,`t2`.`strImage` AS `strImage`,`t2`.`iRating` AS `iRating`,`t2`.`bCompilation` AS `bCompilation`,(select min(`t1`.`iTimesPlayed`) from `t1` where (`t1`.`idAlbum` = `t2`.`idAlbum`)) AS `iTimesPlayed`,`t2`.`strReleaseType` AS `strReleaseType` from `t2`;
+
+CREATE VIEW `v2` AS select `t3`.`idAlbum` AS `idAlbum`,`t3`.`idArtist` AS `idArtist`,`t4`.`strArtist` AS `strArtist`,`t4`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,`t3`.`boolFeatured` AS `boolFeatured`,`t3`.`strJoinPhrase` AS `strJoinPhrase`,`t3`.`iOrder` AS `iOrder` from (`t3` join `t4` on((`t3`.`idArtist` = `t4`.`idArtist`)));
+
+SELECT v1.*,v2.* FROM v1 LEFT JOIN v2 ON v1.idAlbum = v2.idAlbum WHERE v1.idAlbum = 1 ORDER BY v2.iOrder;
+
+drop view v1,v2;
+drop table t1,t2,t3,t4;
+
+--echo #
+--echo # MDEV-8913: Derived queries with same column names as final
+--echo # projection causes issues when using Order By
+--echo #
+create table t1 (field int);
+insert into t1 values (10),(5),(3),(8),(20);
+
+SELECT sq.f2 AS f1, sq.f1 AS f2
+FROM ( SELECT field AS f1, 1 AS f2 FROM t1) AS sq
+ORDER BY sq.f1;
+
+create view v1 as SELECT field AS f1, 1 AS f2 FROM t1;
+
+SELECT sq.f2 AS f1, sq.f1 AS f2
+FROM v1 AS sq
+ORDER BY sq.f1;
+
+drop view v1;
+
+create table t2 SELECT field AS f1, 1 AS f2 FROM t1;
+
+SELECT
+  sq.f2 AS f1,
+  sq.f1 AS f2
+FROM t2 AS sq
+ORDER BY sq.f1;
+
+drop table t1, t2;
+
+--error ER_BAD_FIELD_ERROR
+SELECT 1 FROM (SELECT 1 as a) AS b HAVING (SELECT `SOME_GARBAGE`.b.a)=1; 
+
 --echo # -----------------------------------------------------------------
 --echo # -- End of 10.0 tests.
 --echo # -----------------------------------------------------------------