diff options
Diffstat (limited to 'mysql-test')
78 files changed, 1563 insertions, 65 deletions
diff --git a/mysql-test/extra/binlog_tests/database.test b/mysql-test/extra/binlog_tests/database.test index 05a00c58840..6b3da087f01 100644 --- a/mysql-test/extra/binlog_tests/database.test +++ b/mysql-test/extra/binlog_tests/database.test @@ -52,7 +52,7 @@ eval SELECT 'hello' INTO OUTFILE 'fake_file.$prefix'; # Use '/' instead of '\' in the error message. On windows platform, dir is # formed with '\'. ---replace_regex /\\testing_1\\*/\/testing_1\// /66/39/ +--replace_regex /\\testing_1\\*/\/testing_1\// /66/39/ /17/39/ /File exists/Directory not empty/ --error 1010 DROP DATABASE testing_1; let $wait_binlog_event= DROP TABLE IF EXIST; diff --git a/mysql-test/include/default_mysqld.cnf b/mysql-test/include/default_mysqld.cnf index a16e82f2364..17b2fd5b2bc 100644 --- a/mysql-test/include/default_mysqld.cnf +++ b/mysql-test/include/default_mysqld.cnf @@ -33,7 +33,6 @@ debug-no-sync # Retry bind as this may fail on busy server port-open-timeout=10 -bind-address=127.0.0.1 log-bin-trust-function-creators=1 key_buffer_size= 1M @@ -42,6 +41,9 @@ max_heap_table_size= 1M loose-aria-pagecache-buffer-size=8M loose-feedback-user-info= mysql-test +loose-feedback-debug-startup-interval=20 +loose-feedback-debug-first-interval=60 +loose-feedback-debug-interval=60 loose-innodb_data_file_path= ibdata1:12M:autoextend loose-innodb_buffer_pool_size= 8M diff --git a/mysql-test/include/show_binlog_events2.inc b/mysql-test/include/show_binlog_events2.inc index 356bf6dc727..eefefe4bfbe 100644 --- a/mysql-test/include/show_binlog_events2.inc +++ b/mysql-test/include/show_binlog_events2.inc @@ -10,7 +10,8 @@ if ($binlog_file) { --let $_in_binlog_file=in '$binlog_file' } ---replace_result $_binlog_start <binlog_start> $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--let $_from_binlog_start=from $_binlog_start +--replace_result "$_from_binlog_start" "from <binlog_start>" $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --replace_column 2 # 5 # --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /file_id=[0-9]+/file_id=#/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ --eval show binlog events $_in_binlog_file from $_binlog_start diff --git a/mysql-test/lib/My/ConfigFactory.pm b/mysql-test/lib/My/ConfigFactory.pm index 8c19ed58665..5b0bc0fd856 100644 --- a/mysql-test/lib/My/ConfigFactory.pm +++ b/mysql-test/lib/My/ConfigFactory.pm @@ -169,6 +169,13 @@ sub fix_log { return "$dir/mysqld.log"; } +sub fix_bind_address { + if (IS_WINDOWS) { + return "*"; + } else { + return "127.0.0.1"; + } +} sub fix_log_slow_queries { my ($self, $config, $group_name, $group)= @_; my $dir= dirname($group->value('datadir')); @@ -251,6 +258,7 @@ my @mysqld_rules= { 'ssl-ca' => \&fix_ssl_ca }, { 'ssl-cert' => \&fix_ssl_server_cert }, { 'ssl-key' => \&fix_ssl_server_key }, + { 'bind-address' => \&fix_bind_address }, ); if (IS_WINDOWS) diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 2c2275462cc..9c0a25f6d61 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -2021,6 +2021,15 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `idx` (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; +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); +Warnings: +Note 1061 Multiple primary key defined +DROP TABLE t1; # # MDEV-7374 : Losing connection to MySQL while running ALTER TABLE # @@ -2045,6 +2054,3 @@ t1 CREATE TABLE `t1` ( KEY `i1` (`a`) COMMENT 'comment2' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; -# -# Start of 10.1 tests -# diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result index 04ab385bca6..62e3b1c7331 100644 --- a/mysql-test/r/dyncol.result +++ b/mysql-test/r/dyncol.result @@ -1805,5 +1805,20 @@ set impressions = column_add(impressions, ); drop table t1; # +# MDEV-8565: COLUMN_CHECK fails on valid data +# +SELECT COLUMN_CHECK(COLUMN_CREATE('a',0,'b','1')); +COLUMN_CHECK(COLUMN_CREATE('a',0,'b','1')) +1 +SELECT COLUMN_CHECK(COLUMN_CREATE('a',1,'b','1')); +COLUMN_CHECK(COLUMN_CREATE('a',1,'b','1')) +1 +SELECT COLUMN_JSON(COLUMN_CREATE('a',0,'b','1')); +COLUMN_JSON(COLUMN_CREATE('a',0,'b','1')) +{"a":0,"b":"1"} +SELECT COLUMN_JSON(COLUMN_CREATE('a',1,'b','1')); +COLUMN_JSON(COLUMN_CREATE('a',1,'b','1')) +{"a":1,"b":"1"} +# # end of 10.0 tests # diff --git a/mysql-test/r/events_restart.result b/mysql-test/r/events_restart.result index ba3aa503b63..0caac907f64 100644 --- a/mysql-test/r/events_restart.result +++ b/mysql-test/r/events_restart.result @@ -18,7 +18,7 @@ change column body body longtext character set utf8 collate utf8_bin; use events_test; select @@event_scheduler; @@event_scheduler -DISABLED +OFF show events; ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start select event_name from information_schema.events; @@ -40,12 +40,12 @@ ERROR HY000: Cannot proceed because system tables used by Event Scheduler were f drop event intact_check; ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start set global event_scheduler=on; -ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start +ERROR HY000: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. set global event_scheduler=off; -ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start +ERROR HY000: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. show variables like 'event_scheduler'; Variable_name Value -event_scheduler DISABLED +event_scheduler OFF Make sure that we still can create and drop databases, and no warnings are produced. drop database if exists mysqltest_database_not_exists; @@ -58,6 +58,22 @@ Error 1545 Failed to open mysql.event Restore the original mysql.event table drop table mysql.event; rename table event_like to mysql.event; +check that we can now enable events without restart +set global event_scheduler=original; +Warnings: +Note 1408 Event Scheduler: Loaded 3 events +select @@global.event_scheduler; +@@global.event_scheduler +ON +set global event_scheduler=on; +select @@global.event_scheduler; +@@global.event_scheduler +ON +show events; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test abc1 root@localhost SYSTEM RECURRING # 1 SECOND # # ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test abc2 root@localhost SYSTEM RECURRING # 1 SECOND # # ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test abc3 root@localhost SYSTEM RECURRING # 1 SECOND # # ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci Now let's restart the server again use events_test; select @@event_scheduler; diff --git a/mysql-test/r/init_file_set_password-7656.result b/mysql-test/r/init_file_set_password-7656.result new file mode 100644 index 00000000000..e5b3fc75706 --- /dev/null +++ b/mysql-test/r/init_file_set_password-7656.result @@ -0,0 +1,8 @@ +create user foo@localhost; +select user,host,password from mysql.user where user='foo'; +user host password +foo localhost +select user,host,password from mysql.user where user='foo'; +user host password +foo localhost *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 +drop user foo@localhost; diff --git a/mysql-test/r/lowercase_fs_off.result b/mysql-test/r/lowercase_fs_off.result index 4c4cdb76615..dea4670d2c7 100644 --- a/mysql-test/r/lowercase_fs_off.result +++ b/mysql-test/r/lowercase_fs_off.result @@ -65,4 +65,9 @@ CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a= 1; RENAME TABLE t1 TO T1; ALTER TABLE T1 RENAME t1; DROP TABLE t1; +create table t1 (a int); +create trigger t1_bi before insert on t1 for each row set new.a= 1; +show triggers like '%T1%'; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +drop table t1; set GLOBAL sql_mode=default; diff --git a/mysql-test/r/mysql_upgrade-6984.result b/mysql-test/r/mysql_upgrade-6984.result index 7890b66d494..6aea4806ddb 100644 --- a/mysql-test/r/mysql_upgrade-6984.result +++ b/mysql-test/r/mysql_upgrade-6984.result @@ -57,3 +57,4 @@ Phase 6/6: Running 'FLUSH PRIVILEGES' OK update mysql.user set password='' where user='root'; flush privileges; +set global event_scheduler=OFF; diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index 8a72d5fe3b1..bfe9dc96bc2 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -767,3 +767,37 @@ f1 f2 f3 f4 f5 f6 INSERT INTO t3 SELECT * FROM t2 WHERE f3 = 'm' AND f2 ='c'; DROP TABLE t1,t2,t3; set global default_storage_engine=default; +# +# Bug#13737949: CRASH IN HA_PARTITION::INDEX_INIT +# Bug#18694052: SERVER CRASH IN HA_PARTITION::INIT_RECORD_PRIORITY_QUEUE +# +CREATE TABLE t1 +(a INT, +b INT, +PRIMARY KEY (a)) +ENGINE = InnoDB +PARTITION BY HASH (a) PARTITIONS 3; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +# con1 +ALTER TABLE t1 ADD INDEX idx1 (b); +# con default +SELECT b FROM t1 WHERE b = 0; +ERROR HY000: Table definition has changed, please retry transaction +SELECT b FROM t1 WHERE b = 0; +ERROR HY000: Table definition has changed, please retry transaction +DROP TABLE t1; +# Same test without partitioning +CREATE TABLE t1 +(a INT, +b INT, +PRIMARY KEY (a)) +ENGINE = InnoDB; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +# con1 +ALTER TABLE t1 ADD INDEX idx1 (b); +# con default +SELECT b FROM t1 WHERE b = 0; +ERROR HY000: Table definition has changed, please retry transaction +SELECT b FROM t1 WHERE b = 0; +ERROR HY000: Table definition has changed, please retry transaction +DROP TABLE t1; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 1bdfaa7cc70..e8a7eb133fb 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -4053,3 +4053,24 @@ SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1; ERROR 22003: BIGINT UNSIGNED value is out of range in '(18446744073709551615 + 1)' drop table t1; # End of 5.3 tests +# +# MDEV-8756: MariaDB 10.0.21 crashes during PREPARE +# +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; +id value +1 1 +2 1 +3 NULL +deallocate prepare stmt; +SET SESSION sql_mode = @save_sql_mode; +DROP TABLE t1,t2; +# End of 10.0 tests diff --git a/mysql-test/r/ps_change_master.result b/mysql-test/r/ps_change_master.result new file mode 100644 index 00000000000..25069a537a5 --- /dev/null +++ b/mysql-test/r/ps_change_master.result @@ -0,0 +1,22 @@ +# +# CHANGE MASTER TO doesn't work with prepared statements +# +CHANGE MASTER TO MASTER_HOST='host1', MASTER_USER='user1'; +# Master_Host : host1 +# Master_User : user1 +SET @s := "CHANGE MASTER TO MASTER_HOST='host2'"; +PREPARE stmt FROM @s; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +# Master_Host : host2 +# Master_User : user1 +SET @s := "CHANGE MASTER TO MASTER_USER='user2'"; +PREPARE stmt FROM @s; +EXECUTE stmt; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +# Master_Host : host2 +# Master_User : user2 +CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='root'; +# End of test diff --git a/mysql-test/r/skip_grants.result b/mysql-test/r/skip_grants.result index 9852d6d12c1..b167a197e50 100644 --- a/mysql-test/r/skip_grants.result +++ b/mysql-test/r/skip_grants.result @@ -59,7 +59,9 @@ DROP FUNCTION f1; DROP FUNCTION f2; DROP FUNCTION f3; set global event_scheduler=1; -ERROR HY000: The MariaDB server is running with the --event-scheduler=DISABLED or --skip-grant-tables option so it cannot execute this statement +Warnings: +Note 1408 Event Scheduler: Loaded 0 events +set global event_scheduler=0; select count(*) from information_schema.COLUMN_PRIVILEGES; count(*) 0 diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index 285284596c4..fcced761283 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -421,4 +421,99 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where DROP TABLE t1,t2; +# +# MDEV-7370: Server deadlocks on renaming a table for which persistent statistics exists +# +drop database if exists db1; +drop database if exists db1; +create database db1; +create database db2; +use db1; +# +# First, run the original testcase: +# +create table t1 (i int); +insert into t1 values (10),(20); +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +db1.t1 analyze status Engine-independent statistics collected +db1.t1 analyze status OK +rename table t1 to db2.t1; +# Verify that stats in the old database are gone: +select * from mysql.column_stats where db_name='db1' and table_name='t1'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +select * from mysql.table_stats where db_name='db1' and table_name='t1'; +db_name table_name cardinality +# Verify that stats are present in the new database: +select * from mysql.column_stats where db_name='db2' and table_name='t1'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +db2 t1 i 10 20 0.0000 4.0000 1.0000 0 NULL NULL +select * from mysql.table_stats where db_name='db2' and table_name='t1'; +db_name table_name cardinality +db2 t1 2 +# +# Now, try with more than one column and with indexes: +# +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; +Table Op Msg_type Msg_text +db1.t2 analyze status Engine-independent statistics collected +db1.t2 analyze status Table is already up to date +alter table t2 rename db2.t2; +# Verify that stats in the old database are gone: +select * from mysql.table_stats where db_name='db1' and table_name='t2'; +db_name table_name cardinality +select * from mysql.column_stats where db_name='db1' and table_name='t2'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +select * from mysql.index_stats where db_name='db1' and table_name='t2'; +db_name table_name index_name prefix_arity avg_frequency +# Verify that stats are present in the new database: +select * from mysql.table_stats where db_name='db2' and table_name='t2'; +db_name table_name cardinality +db2 t2 10 +select * from mysql.column_stats where db_name='db2' and table_name='t2'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +db2 t2 a 0 1 0.0000 4.0000 5.0000 0 NULL NULL +db2 t2 b 0 5 0.0000 4.0000 1.6667 0 NULL NULL +db2 t2 c 0 9 0.0000 4.0000 1.0000 0 NULL NULL +select * from mysql.index_stats where db_name='db2' and table_name='t2'; +db_name table_name index_name prefix_arity avg_frequency +db2 t2 IDX1 1 5.0000 +db2 t2 IDX2 1 5.0000 +db2 t2 IDX2 2 1.6667 +use db2; +# +# Now, rename within the same database and verify: +# +rename table t2 to t3; +# No stats under old name: +select * from mysql.table_stats where db_name='db2' and table_name='t2'; +db_name table_name cardinality +select * from mysql.column_stats where db_name='db2' and table_name='t2'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +select * from mysql.index_stats where db_name='db2' and table_name='t2'; +db_name table_name index_name prefix_arity avg_frequency +# Stats under the new name: +select * from mysql.table_stats where db_name='db2' and table_name='t3'; +db_name table_name cardinality +db2 t3 10 +select * from mysql.column_stats where db_name='db2' and table_name='t3'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +db2 t3 a 0 1 0.0000 4.0000 5.0000 0 NULL NULL +db2 t3 b 0 5 0.0000 4.0000 1.6667 0 NULL NULL +db2 t3 c 0 9 0.0000 4.0000 1.0000 0 NULL NULL +select * from mysql.index_stats where db_name='db2' and table_name='t3'; +db_name table_name index_name prefix_arity avg_frequency +db2 t3 IDX1 1 5.0000 +db2 t3 IDX2 1 5.0000 +db2 t3 IDX2 2 1.6667 +use test; +drop database db1; +drop database db2; +drop table t1; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index 301c093ce9c..0e866755532 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -448,6 +448,101 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where DROP TABLE t1,t2; +# +# MDEV-7370: Server deadlocks on renaming a table for which persistent statistics exists +# +drop database if exists db1; +drop database if exists db1; +create database db1; +create database db2; +use db1; +# +# First, run the original testcase: +# +create table t1 (i int); +insert into t1 values (10),(20); +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +db1.t1 analyze status Engine-independent statistics collected +db1.t1 analyze status OK +rename table t1 to db2.t1; +# Verify that stats in the old database are gone: +select * from mysql.column_stats where db_name='db1' and table_name='t1'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +select * from mysql.table_stats where db_name='db1' and table_name='t1'; +db_name table_name cardinality +# Verify that stats are present in the new database: +select * from mysql.column_stats where db_name='db2' and table_name='t1'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +db2 t1 i 10 20 0.0000 4.0000 1.0000 0 NULL NULL +select * from mysql.table_stats where db_name='db2' and table_name='t1'; +db_name table_name cardinality +db2 t1 2 +# +# Now, try with more than one column and with indexes: +# +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; +Table Op Msg_type Msg_text +db1.t2 analyze status Engine-independent statistics collected +db1.t2 analyze status OK +alter table t2 rename db2.t2; +# Verify that stats in the old database are gone: +select * from mysql.table_stats where db_name='db1' and table_name='t2'; +db_name table_name cardinality +select * from mysql.column_stats where db_name='db1' and table_name='t2'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +select * from mysql.index_stats where db_name='db1' and table_name='t2'; +db_name table_name index_name prefix_arity avg_frequency +# Verify that stats are present in the new database: +select * from mysql.table_stats where db_name='db2' and table_name='t2'; +db_name table_name cardinality +db2 t2 10 +select * from mysql.column_stats where db_name='db2' and table_name='t2'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +db2 t2 a 0 1 0.0000 4.0000 5.0000 0 NULL NULL +db2 t2 b 0 5 0.0000 4.0000 1.6667 0 NULL NULL +db2 t2 c 0 9 0.0000 4.0000 1.0000 0 NULL NULL +select * from mysql.index_stats where db_name='db2' and table_name='t2'; +db_name table_name index_name prefix_arity avg_frequency +db2 t2 IDX1 1 5.0000 +db2 t2 IDX2 1 5.0000 +db2 t2 IDX2 2 1.6667 +use db2; +# +# Now, rename within the same database and verify: +# +rename table t2 to t3; +# No stats under old name: +select * from mysql.table_stats where db_name='db2' and table_name='t2'; +db_name table_name cardinality +select * from mysql.column_stats where db_name='db2' and table_name='t2'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +select * from mysql.index_stats where db_name='db2' and table_name='t2'; +db_name table_name index_name prefix_arity avg_frequency +# Stats under the new name: +select * from mysql.table_stats where db_name='db2' and table_name='t3'; +db_name table_name cardinality +db2 t3 10 +select * from mysql.column_stats where db_name='db2' and table_name='t3'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +db2 t3 a 0 1 0.0000 4.0000 5.0000 0 NULL NULL +db2 t3 b 0 5 0.0000 4.0000 1.6667 0 NULL NULL +db2 t3 c 0 9 0.0000 4.0000 1.0000 0 NULL NULL +select * from mysql.index_stats where db_name='db2' and table_name='t3'; +db_name table_name index_name prefix_arity avg_frequency +db2 t3 IDX1 1 5.0000 +db2 t3 IDX2 1 5.0000 +db2 t3 IDX2 2 1.6667 +use test; +drop database db1; +drop database db2; +drop table t1; set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result index a2d3d392f45..bd6a0849e3d 100644 --- a/mysql-test/r/statistics.result +++ b/mysql-test/r/statistics.result @@ -1622,3 +1622,22 @@ test t2 id 1 1024 0.0000 8.0000 63 SINGLE_PREC_HB 03070B0F13171B1F23272B2F33373B set histogram_size=default; drop table t1, t2; set use_stat_tables=@save_use_stat_tables; +# +# Bug MDEV-7383: min/max value for a column not utf8 compatible +# +create table t1 (a varchar(100)) engine=MyISAM; +insert into t1 values(unhex('D879626AF872675F73E662F8')); +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +show warnings; +Level Code Message +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; +db_name table_name column_name HEX(min_value) HEX(max_value) nulls_ratio avg_frequency hist_size hist_type HEX(histogram) +test t1 a D879626AF872675F73E662F8 D879626AF872675F73E662F8 0.0000 1.0000 0 NULL NULL +drop table t1; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 3f0e6955b67..75c8597590a 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7109,3 +7109,27 @@ sq NULL deallocate prepare stmt; drop table t1,t2,t3,t4; +# +# MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE || +# m_lock_type != 2' failed in handler::ha_index_read_map +# +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; +sq +3 +SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; +f2 +3 +SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); +count(*) +1 +delete from t1; +SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; +sq +NULL +drop view v2; +drop table t1,t2; diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index 34c766ed9b3..e6238af4cdc 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7109,6 +7109,30 @@ sq NULL deallocate prepare stmt; drop table t1,t2,t3,t4; +# +# MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE || +# m_lock_type != 2' failed in handler::ha_index_read_map +# +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; +sq +3 +SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; +f2 +3 +SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); +count(*) +1 +delete from t1; +SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; +sq +NULL +drop view v2; +drop table t1,t2; set optimizer_switch=default; select @@optimizer_switch like '%exists_to_in=off%'; @@optimizer_switch like '%exists_to_in=off%' diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index de21a8dbd96..70edc6463c4 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7102,6 +7102,30 @@ sq NULL deallocate prepare stmt; drop table t1,t2,t3,t4; +# +# MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE || +# m_lock_type != 2' failed in handler::ha_index_read_map +# +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; +sq +3 +SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; +f2 +3 +SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); +count(*) +1 +delete from t1; +SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; +sq +NULL +drop view v2; +drop table t1,t2; set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; @@optimizer_switch like '%materialization=on%' diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 09d8328a639..c89fd132b35 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7100,4 +7100,28 @@ sq NULL deallocate prepare stmt; drop table t1,t2,t3,t4; +# +# MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE || +# m_lock_type != 2' failed in handler::ha_index_read_map +# +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; +sq +3 +SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; +f2 +3 +SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); +count(*) +1 +delete from t1; +SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; +sq +NULL +drop view v2; +drop table t1,t2; set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index a164ae98aa0..b12bf216327 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7115,6 +7115,30 @@ sq NULL deallocate prepare stmt; drop table t1,t2,t3,t4; +# +# MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE || +# m_lock_type != 2' failed in handler::ha_index_read_map +# +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; +sq +3 +SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; +f2 +3 +SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); +count(*) +1 +delete from t1; +SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; +sq +NULL +drop view v2; +drop table t1,t2; set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; @@optimizer_switch like '%subquery_cache=on%' diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index d15a07fffa6..54f145d5c5e 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7100,5 +7100,29 @@ sq NULL deallocate prepare stmt; drop table t1,t2,t3,t4; +# +# MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE || +# m_lock_type != 2' failed in handler::ha_index_read_map +# +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; +sq +3 +SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; +f2 +3 +SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); +count(*) +1 +delete from t1; +SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; +sq +NULL +drop view v2; +drop table t1,t2; set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index 2fe6512eee3..bc4de8b4171 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -290,8 +290,8 @@ column_stats CREATE TABLE `column_stats` ( `db_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `column_name` varchar(64) COLLATE utf8_bin NOT NULL, - `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, - `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `min_value` varbinary(255) DEFAULT NULL, + `max_value` varbinary(255) DEFAULT NULL, `nulls_ratio` decimal(12,4) DEFAULT NULL, `avg_length` decimal(12,4) DEFAULT NULL, `avg_frequency` decimal(12,4) DEFAULT NULL, diff --git a/mysql-test/r/system_mysql_db_fix40123.result b/mysql-test/r/system_mysql_db_fix40123.result index 2fe6512eee3..bc4de8b4171 100644 --- a/mysql-test/r/system_mysql_db_fix40123.result +++ b/mysql-test/r/system_mysql_db_fix40123.result @@ -290,8 +290,8 @@ column_stats CREATE TABLE `column_stats` ( `db_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `column_name` varchar(64) COLLATE utf8_bin NOT NULL, - `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, - `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `min_value` varbinary(255) DEFAULT NULL, + `max_value` varbinary(255) DEFAULT NULL, `nulls_ratio` decimal(12,4) DEFAULT NULL, `avg_length` decimal(12,4) DEFAULT NULL, `avg_frequency` decimal(12,4) DEFAULT NULL, diff --git a/mysql-test/r/system_mysql_db_fix50030.result b/mysql-test/r/system_mysql_db_fix50030.result index 2fe6512eee3..bc4de8b4171 100644 --- a/mysql-test/r/system_mysql_db_fix50030.result +++ b/mysql-test/r/system_mysql_db_fix50030.result @@ -290,8 +290,8 @@ column_stats CREATE TABLE `column_stats` ( `db_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `column_name` varchar(64) COLLATE utf8_bin NOT NULL, - `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, - `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `min_value` varbinary(255) DEFAULT NULL, + `max_value` varbinary(255) DEFAULT NULL, `nulls_ratio` decimal(12,4) DEFAULT NULL, `avg_length` decimal(12,4) DEFAULT NULL, `avg_frequency` decimal(12,4) DEFAULT NULL, diff --git a/mysql-test/r/system_mysql_db_fix50117.result b/mysql-test/r/system_mysql_db_fix50117.result index 2fe6512eee3..bc4de8b4171 100644 --- a/mysql-test/r/system_mysql_db_fix50117.result +++ b/mysql-test/r/system_mysql_db_fix50117.result @@ -290,8 +290,8 @@ column_stats CREATE TABLE `column_stats` ( `db_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `column_name` varchar(64) COLLATE utf8_bin NOT NULL, - `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, - `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `min_value` varbinary(255) DEFAULT NULL, + `max_value` varbinary(255) DEFAULT NULL, `nulls_ratio` decimal(12,4) DEFAULT NULL, `avg_length` decimal(12,4) DEFAULT NULL, `avg_frequency` decimal(12,4) DEFAULT NULL, diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index db7c2380398..a0c35c6e0ca 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -619,6 +619,65 @@ Handler_update 5 ROLLBACK; DROP TABLE t1, t2; # +# MDEV-8938: Server Crash on Update with joins +# +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; +# +#MDEV-8701: Crash on derived query +# +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; +# # MDEV-4410: update does not want to use a covering index, but select uses it. # create table t2(a int); diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 8a502a4a3ba..dec85fbcd4b 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -5507,6 +5507,39 @@ UPDATE t1, t2 SET a = 1 WHERE a IN ( SELECT 0 FROM v3 ); EXECUTE stmt; DROP TABLE t1, t2, t3; DROP VIEW v3; +# +# MDEV-8632: Segmentation fault on INSERT +# +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`; # ----------------------------------------------------------------- # -- End of 5.5 tests. # ----------------------------------------------------------------- @@ -5627,6 +5660,149 @@ count(v3.i) 0 drop table t1, t2; drop view v3; +# +# MDEV-8525: mariadb 10.0.20 crashing when data is read by Kodi +# media center (http://kodi.tv). +# +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; +idAlbum strAlbum strMusicBrainzAlbumID strArtists strGenres iYear strMoods strStyles strThemes strReview strLabel strType strImage iRating bCompilation iTimesPlayed strReleaseType idAlbum idArtist strArtist strMusicBrainzArtistID boolFeatured strJoinPhrase iOrder +1 strAlbum1 strMusicBrainzAlbumID1 strArtists1 strGenres1 2000 NULL NULL NULL NULL NULL NULL NULL NULL 0 0 album 1 1 strArtist1 strMusicBrainzArtistID 0 0 +drop view v1,v2; +drop table t1,t2,t3,t4; +# +# MDEV-8913: Derived queries with same column names as final +# projection causes issues when using Order By +# +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; +f1 f2 +1 3 +1 5 +1 8 +1 10 +1 20 +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; +f1 f2 +1 3 +1 5 +1 8 +1 10 +1 20 +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; +f1 f2 +1 3 +1 5 +1 8 +1 10 +1 20 +drop table t1, t2; +SELECT 1 FROM (SELECT 1 as a) AS b HAVING (SELECT `SOME_GARBAGE`.b.a)=1; +ERROR 42S22: Unknown column 'SOME_GARBAGE.b.a' in 'field list' # ----------------------------------------------------------------- # -- End of 10.0 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql.result b/mysql-test/suite/funcs_1/r/is_columns_mysql.result index d1685f40314..2821e1112e5 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result @@ -16,8 +16,8 @@ def mysql column_stats db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8 def mysql column_stats histogram 11 NULL YES varbinary 255 255 NULL NULL NULL NULL NULL varbinary(255) select,insert,update,references def mysql column_stats hist_size 9 NULL YES tinyint NULL NULL 3 0 NULL NULL NULL tinyint(3) unsigned select,insert,update,references def mysql column_stats hist_type 10 NULL YES enum 14 42 NULL NULL NULL utf8 utf8_bin enum('SINGLE_PREC_HB','DOUBLE_PREC_HB') select,insert,update,references -def mysql column_stats max_value 5 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) select,insert,update,references -def mysql column_stats min_value 4 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) select,insert,update,references +def mysql column_stats max_value 5 NULL YES varbinary 255 255 NULL NULL NULL NULL NULL varbinary(255) select,insert,update,references +def mysql column_stats min_value 4 NULL YES varbinary 255 255 NULL NULL NULL NULL NULL varbinary(255) select,insert,update,references def mysql column_stats nulls_ratio 6 NULL YES decimal NULL NULL 12 4 NULL NULL NULL decimal(12,4) select,insert,update,references def mysql column_stats table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references def mysql db Alter_priv 13 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references @@ -333,8 +333,8 @@ NULL mysql columns_priv Timestamp timestamp NULL NULL NULL NULL timestamp 3.0000 mysql column_stats db_name varchar 64 192 utf8 utf8_bin varchar(64) 3.0000 mysql column_stats table_name varchar 64 192 utf8 utf8_bin varchar(64) 3.0000 mysql column_stats column_name varchar 64 192 utf8 utf8_bin varchar(64) -3.0000 mysql column_stats min_value varchar 255 765 utf8 utf8_bin varchar(255) -3.0000 mysql column_stats max_value varchar 255 765 utf8 utf8_bin varchar(255) +1.0000 mysql column_stats min_value varbinary 255 255 NULL NULL varbinary(255) +1.0000 mysql column_stats max_value varbinary 255 255 NULL NULL varbinary(255) NULL mysql column_stats nulls_ratio decimal NULL NULL NULL NULL decimal(12,4) NULL mysql column_stats avg_length decimal NULL NULL NULL NULL decimal(12,4) NULL mysql column_stats avg_frequency decimal NULL NULL NULL NULL decimal(12,4) diff --git a/mysql-test/suite/innodb/r/innodb-alter-filewrite.result b/mysql-test/suite/innodb/r/innodb-alter-filewrite.result new file mode 100644 index 00000000000..444b09ad440 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-alter-filewrite.result @@ -0,0 +1,30 @@ +CREATE TABLE `test_wo_keys` ( +`f01` int AUTO_INCREMENT, +`f02` bigint, `f03` bigint, `f04` enum('a','b'), +`f05` date, `f06` int, `f07` int, `f08` double, `f09` int, +`f10` bigint, `f11` double, `f12` enum('a','b','c','d','e'), +`f13` int, `f14` int, `f15` varchar(255), `f16` int, `f17` int, `f18` int, +`f19` double, `f20` double, `f21` double, `f22` double, `f23` double, `f24` tinyint, +`f25` double, `f26` double, `f27` double, `f28` double, `f29` int unsigned, +`f30` int unsigned, `f31` bigint, `f32` int unsigned, `f33` bigint, +`f34` int unsigned, `f35` int unsigned, +PRIMARY KEY `f01` (`f01`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +show status like '%merge_buffers%'; +Variable_name Value +Innodb_os_merge_buffers_written 0 +Innodb_os_merge_buffers_read 0 +Innodb_os_merge_buffers_merged 0 +ALTER TABLE test_wo_keys +ADD KEY `f06` (`f06`), ADD KEY `f05` (`f05`), ADD KEY `f04` (`f04`), ADD KEY `f23` (`f23`), +ADD KEY `f10` (`f10`), ADD KEY `f11` (`f11`), ADD KEY `f09` (`f09`), ADD KEY `f22` (`f22`), +ADD KEY `f21` (`f21`), ADD KEY `f07` (`f07`), ADD KEY `f08` (`f08`), ADD KEY `f18` (`f18`), +ADD KEY `f19` (`f19`), ADD KEY `f20` (`f20`), ADD KEY `f29` (`f29`,`f31`,`f33`), +ADD KEY `f35` (`f35`), ADD KEY `f25` (`f25`), ADD KEY `f26` (`f26`), +ADD KEY `f27` (`f27`), ADD KEY `f28` (`f28`); +show status like '%merge_buffers%'; +Variable_name Value +Innodb_os_merge_buffers_written 0 +Innodb_os_merge_buffers_read 0 +Innodb_os_merge_buffers_merged 0 +DROP TABLE test_wo_keys; diff --git a/mysql-test/suite/innodb/r/innodb-fk-warnings.result b/mysql-test/suite/innodb/r/innodb-fk-warnings.result index a022f07936c..eddedfc3620 100644 --- a/mysql-test/suite/innodb/r/innodb-fk-warnings.result +++ b/mysql-test/suite/innodb/r/innodb-fk-warnings.result @@ -25,7 +25,7 @@ create table t2(a int, constraint a foreign key a (a) references t1(a)) engine=i ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Create table '`test`.`t2`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. Error close to foreign key a (a) references t1(a)) engine=innodb. +Warning 150 Create table '`test`.`t2`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near ' foreign key a (a) references t1(a)) engine=innodb'. Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint drop table t1; @@ -42,7 +42,7 @@ alter table t2 add constraint b foreign key (b) references t2(b); ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Alter table '`test`.`t2`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. Error close to foreign key (b) references t2(b). +Warning 150 Alter table '`test`.`t2`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near ' foreign key (b) references t2(b)'. Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint drop table t2, t1; @@ -51,7 +51,7 @@ alter table t1 add constraint c1 foreign key (f1) references t11(f1); ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t11` not found in the data dictionary close to foreign key (f1) references t11(f1). +Warning 150 Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t11` not found in the data dictionary near ' foreign key (f1) references t11(f1)'. Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint drop table t1; @@ -90,14 +90,14 @@ alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update se ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Alter table `test`.`t1` with foreign key constraint failed. You have defined a SET NULL condition but column f1 is defined as NOT NULL in foreign key (f1) references t1(f1) on update set null close to on update set null. +Warning 150 Alter table `test`.`t1` with foreign key constraint failed. You have defined a SET NULL condition but column 'f1' is defined as NOT NULL in ' foreign key (f1) references t1(f1) on update set null' near ' on update set null'. Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint create table t2(a int not null, foreign key(a) references t1(f1) on delete set null) engine=innodb; ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Create table `test`.`t2` with foreign key constraint failed. You have defined a SET NULL condition but column a is defined as NOT NULL in foreign key(a) references t1(f1) on delete set null) engine=innodb close to on delete set null) engine=innodb. +Warning 150 Create table `test`.`t2` with foreign key constraint failed. You have defined a SET NULL condition but column 'a' is defined as NOT NULL in 'foreign key(a) references t1(f1) on delete set null) engine=innodb' near ' on delete set null) engine=innodb'. Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint drop table t1; @@ -106,7 +106,7 @@ create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=inn ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Create table `test`.`t2` with foreign key constraint failed. Field type or character set for column a does not mach referenced column f1 close to foreign key(a) references t1(f1)) engine=innodb +Warning 150 Create table `test`.`t2` with foreign key constraint failed. Field type or character set for column 'a' does not mach referenced column 'f1' near 'foreign key(a) references t1(f1)) engine=innodb'. Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint drop table t1; diff --git a/mysql-test/suite/innodb/r/innodb-fk.result b/mysql-test/suite/innodb/r/innodb-fk.result index dee20d282f7..c916d665bf0 100644 --- a/mysql-test/suite/innodb/r/innodb-fk.result +++ b/mysql-test/suite/innodb/r/innodb-fk.result @@ -50,8 +50,8 @@ CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Create table `test`.`t2` with foreign key constraint failed. Referenced table `test`.`t3` not found in the data dictionary close to FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE -) ENGINE=InnoDB. +Warning 150 Create table `test`.`t2` with foreign key constraint failed. Referenced table `test`.`t3` not found in the data dictionary near ' FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE +) ENGINE=InnoDB'. Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint CREATE TABLE t2 ( @@ -65,7 +65,7 @@ ALTER TABLE t2 ADD CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Alter table `test`.`t2` with foreign key constraint failed. Referenced table `test`.`t3` not found in the data dictionary close to FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE. +Warning 150 Alter table `test`.`t2` with foreign key constraint failed. Referenced table `test`.`t3` not found in the data dictionary near ' FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE'. Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint drop table t2; diff --git a/mysql-test/suite/innodb/r/innodb_monitor.result b/mysql-test/suite/innodb/r/innodb_monitor.result index 8c580348e1a..31c6123c964 100644 --- a/mysql-test/suite/innodb/r/innodb_monitor.result +++ b/mysql-test/suite/innodb/r/innodb_monitor.result @@ -130,6 +130,9 @@ os_log_bytes_written disabled os_log_fsyncs disabled os_log_pending_fsyncs disabled os_log_pending_writes disabled +os_merge_blocks_written disabled +os_merge_blocks_read disabled +os_merge_blocks_merged disabled trx_rw_commits disabled trx_ro_commits disabled trx_nl_ro_commits disabled @@ -278,6 +281,9 @@ lock_row_lock_time disabled lock_row_lock_time_max disabled lock_row_lock_waits disabled lock_row_lock_time_avg disabled +os_merge_blocks_written disabled +os_merge_blocks_read disabled +os_merge_blocks_merged disabled innodb_rwlock_s_spin_waits disabled innodb_rwlock_x_spin_waits disabled innodb_rwlock_s_spin_rounds disabled @@ -316,6 +322,9 @@ os_log_bytes_written disabled os_log_fsyncs disabled os_log_pending_fsyncs enabled os_log_pending_writes enabled +os_merge_blocks_written disabled +os_merge_blocks_read disabled +os_merge_blocks_merged disabled set global innodb_monitor_enable=""; ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '' set global innodb_monitor_enable="_"; diff --git a/mysql-test/suite/innodb/r/snapshot.result b/mysql-test/suite/innodb/r/snapshot.result new file mode 100644 index 00000000000..dcb3d242fc2 --- /dev/null +++ b/mysql-test/suite/innodb/r/snapshot.result @@ -0,0 +1,8 @@ +START TRANSACTION WITH CONSISTENT SNAPSHOT; +CREATE TABLE IF NOT EXISTS t1 (pk INT PRIMARY KEY, i INT, KEY(i)) ENGINE=InnoDB; +UPDATE t1 SET i = 0; +ERROR HY000: Table definition has changed, please retry transaction +UPDATE t1 SET pk = 0; +ERROR HY000: Table definition has changed, please retry transaction +commit; +drop table t1; diff --git a/mysql-test/suite/innodb/t/innodb-alter-filewrite.test b/mysql-test/suite/innodb/t/innodb-alter-filewrite.test new file mode 100644 index 00000000000..d0372056937 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-alter-filewrite.test @@ -0,0 +1,32 @@ +--source include/have_innodb.inc + +# +# MDEV-8696: Adding indexes on empty table is slow with large innodb_sort_buffer_size. +# + +CREATE TABLE `test_wo_keys` ( +`f01` int AUTO_INCREMENT, +`f02` bigint, `f03` bigint, `f04` enum('a','b'), +`f05` date, `f06` int, `f07` int, `f08` double, `f09` int, +`f10` bigint, `f11` double, `f12` enum('a','b','c','d','e'), +`f13` int, `f14` int, `f15` varchar(255), `f16` int, `f17` int, `f18` int, +`f19` double, `f20` double, `f21` double, `f22` double, `f23` double, `f24` tinyint, +`f25` double, `f26` double, `f27` double, `f28` double, `f29` int unsigned, +`f30` int unsigned, `f31` bigint, `f32` int unsigned, `f33` bigint, +`f34` int unsigned, `f35` int unsigned, +PRIMARY KEY `f01` (`f01`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +show status like '%merge_buffers%'; + +ALTER TABLE test_wo_keys +ADD KEY `f06` (`f06`), ADD KEY `f05` (`f05`), ADD KEY `f04` (`f04`), ADD KEY `f23` (`f23`), +ADD KEY `f10` (`f10`), ADD KEY `f11` (`f11`), ADD KEY `f09` (`f09`), ADD KEY `f22` (`f22`), +ADD KEY `f21` (`f21`), ADD KEY `f07` (`f07`), ADD KEY `f08` (`f08`), ADD KEY `f18` (`f18`), +ADD KEY `f19` (`f19`), ADD KEY `f20` (`f20`), ADD KEY `f29` (`f29`,`f31`,`f33`), +ADD KEY `f35` (`f35`), ADD KEY `f25` (`f25`), ADD KEY `f26` (`f26`), +ADD KEY `f27` (`f27`), ADD KEY `f28` (`f28`); + +show status like '%merge_buffers%'; + +DROP TABLE test_wo_keys; diff --git a/mysql-test/suite/innodb/t/snapshot.test b/mysql-test/suite/innodb/t/snapshot.test new file mode 100644 index 00000000000..8e3dc76fe54 --- /dev/null +++ b/mysql-test/suite/innodb/t/snapshot.test @@ -0,0 +1,18 @@ + +--source include/have_log_bin.inc +--source include/have_binlog_format_row.inc +--source include/have_innodb.inc + +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +--connect (con1,localhost,root,,test) +CREATE TABLE IF NOT EXISTS t1 (pk INT PRIMARY KEY, i INT, KEY(i)) ENGINE=InnoDB; + +--connection default +--error 1412 +UPDATE t1 SET i = 0; +--error 1412 +UPDATE t1 SET pk = 0; +commit; + +drop table t1; diff --git a/mysql-test/suite/parts/r/partition_debug.result b/mysql-test/suite/parts/r/partition_debug.result index 3f0f2def790..109072be49c 100644 --- a/mysql-test/suite/parts/r/partition_debug.result +++ b/mysql-test/suite/parts/r/partition_debug.result @@ -1,5 +1,21 @@ DROP TABLE IF EXISTS t1; # +# Bug#13737949: CRASH IN HA_PARTITION::INDEX_INIT +# Bug#18694052: SERVER CRASH IN HA_PARTITION::INIT_RECORD_PRIORITY_QUEUE +# +CREATE TABLE t1 (a INT, b VARCHAR(64), KEY(b,a)) +PARTITION BY HASH (a) PARTITIONS 3; +INSERT INTO t1 VALUES (1, "1"), (2, "2"), (3, "3"), (4, "Four"), (5, "Five"), +(6, "Six"), (7, "Seven"), (8, "Eight"), (9, "Nine"); +SET SESSION debug_dbug="+d,ha_partition_fail_index_init"; +SELECT * FROM t1 WHERE b = "Seven"; +ERROR HY000: Table has no partition for value 0 +SET SESSION debug_dbug="-d,ha_partition_fail_index_init"; +SELECT * FROM t1 WHERE b = "Seven"; +a b +7 Seven +DROP TABLE t1; +# # WL#4445: EXCHANGE PARTITION WITH TABLE # Verify ddl_log in case of crashing. call mtr.add_suppression("Attempting backtrace. You can use the following information to find out"); diff --git a/mysql-test/suite/parts/t/partition_debug.test b/mysql-test/suite/parts/t/partition_debug.test index aa943e5fbe1..11b66e4afd8 100644 --- a/mysql-test/suite/parts/t/partition_debug.test +++ b/mysql-test/suite/parts/t/partition_debug.test @@ -12,6 +12,24 @@ DROP TABLE IF EXISTS t1; --enable_warnings +# Partitioning test that require debug features + +--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 VARCHAR(64), KEY(b,a)) + PARTITION BY HASH (a) PARTITIONS 3; + INSERT INTO t1 VALUES (1, "1"), (2, "2"), (3, "3"), (4, "Four"), (5, "Five"), + (6, "Six"), (7, "Seven"), (8, "Eight"), (9, "Nine"); +SET SESSION debug_dbug="+d,ha_partition_fail_index_init"; +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +SELECT * FROM t1 WHERE b = "Seven"; +SET SESSION debug_dbug="-d,ha_partition_fail_index_init"; +SELECT * FROM t1 WHERE b = "Seven"; +DROP TABLE t1; + + --let $DATADIR= `SELECT @@datadir;` --echo # diff --git a/mysql-test/suite/perfschema/include/have_timer_cycle.inc b/mysql-test/suite/perfschema/include/have_timer_cycle.inc new file mode 100644 index 00000000000..b801ea256d6 --- /dev/null +++ b/mysql-test/suite/perfschema/include/have_timer_cycle.inc @@ -0,0 +1,4 @@ +if (!`SELECT count(*) FROM performance_schema.performance_timers WHERE timer_name='CYCLE' AND timer_frequency IS NOT NULL`) +{ + Skip Need performance timer CYCLE; +} diff --git a/mysql-test/suite/perfschema/r/privilege.result b/mysql-test/suite/perfschema/r/privilege.result index f46c094b5ac..1f806d94991 100644 --- a/mysql-test/suite/perfschema/r/privilege.result +++ b/mysql-test/suite/perfschema/r/privilege.result @@ -557,7 +557,7 @@ ERROR 42000: DROP command denied to user 'pfs_user_4'@'localhost' for table 'eve # # Grant access to change tables with the root account GRANT UPDATE ON performance_schema.setup_consumers TO pfs_user_4; -GRANT UPDATE ON performance_schema.setup_timers TO pfs_user_4; +GRANT UPDATE, SELECT ON performance_schema.setup_timers TO pfs_user_4; GRANT UPDATE, SELECT ON performance_schema.setup_instruments TO pfs_user_4; GRANT DROP ON performance_schema.events_waits_current TO pfs_user_4; GRANT DROP ON performance_schema.events_waits_history TO pfs_user_4; @@ -568,7 +568,7 @@ UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE name LIKE 'wait/synch/mutex/%' OR name LIKE 'wait/synch/rwlock/%'; UPDATE performance_schema.setup_consumers SET enabled = 'YES'; -UPDATE performance_schema.setup_timers SET timer_name = 'TICK'; +UPDATE performance_schema.setup_timers SET timer_name = 'TICK' WHERE name <> "wait"; TRUNCATE TABLE performance_schema.events_waits_history_long; TRUNCATE TABLE performance_schema.events_waits_history; TRUNCATE TABLE performance_schema.events_waits_current; @@ -579,6 +579,5 @@ flush privileges; UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; UPDATE performance_schema.setup_consumers SET enabled = 'YES'; UPDATE performance_schema.setup_timers SET timer_name = 'MICROSECOND' where name="idle"; -UPDATE performance_schema.setup_timers SET timer_name = 'CYCLE' where name="wait"; UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="stage"; UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="statement"; diff --git a/mysql-test/suite/perfschema/t/dml_performance_timers.test b/mysql-test/suite/perfschema/t/dml_performance_timers.test index 2ec37fbe7e9..587c54144aa 100644 --- a/mysql-test/suite/perfschema/t/dml_performance_timers.test +++ b/mysql-test/suite/perfschema/t/dml_performance_timers.test @@ -2,6 +2,7 @@ --source include/not_embedded.inc --source include/have_perfschema.inc +--source include/have_timer_cycle.inc --replace_column 2 <frequency> 3 <resolution> 4 <overhead> select * from performance_schema.performance_timers; diff --git a/mysql-test/suite/perfschema/t/dml_setup_timers.test b/mysql-test/suite/perfschema/t/dml_setup_timers.test index 641ac90b1c5..bd8822d2b19 100644 --- a/mysql-test/suite/perfschema/t/dml_setup_timers.test +++ b/mysql-test/suite/perfschema/t/dml_setup_timers.test @@ -2,6 +2,7 @@ --source include/not_embedded.inc --source include/have_perfschema.inc +--source include/have_timer_cycle.inc # Set to a known state update performance_schema.setup_timers diff --git a/mysql-test/suite/perfschema/t/privilege.test b/mysql-test/suite/perfschema/t/privilege.test index f5bd5cc4036..0e51d161934 100644 --- a/mysql-test/suite/perfschema/t/privilege.test +++ b/mysql-test/suite/perfschema/t/privilege.test @@ -294,7 +294,7 @@ TRUNCATE TABLE performance_schema.events_waits_current; --echo # Grant access to change tables with the root account GRANT UPDATE ON performance_schema.setup_consumers TO pfs_user_4; -GRANT UPDATE ON performance_schema.setup_timers TO pfs_user_4; +GRANT UPDATE, SELECT ON performance_schema.setup_timers TO pfs_user_4; GRANT UPDATE, SELECT ON performance_schema.setup_instruments TO pfs_user_4; GRANT DROP ON performance_schema.events_waits_current TO pfs_user_4; GRANT DROP ON performance_schema.events_waits_history TO pfs_user_4; @@ -311,7 +311,11 @@ WHERE name LIKE 'wait/synch/mutex/%' UPDATE performance_schema.setup_consumers SET enabled = 'YES'; -UPDATE performance_schema.setup_timers SET timer_name = 'TICK'; +# We do not touch "wait", to avoid restoring it at the end of the test, +# as its default value initialized at server startup is ambiguous: +# it can be CYCLE or NANOSECOND depending on platform + +UPDATE performance_schema.setup_timers SET timer_name = 'TICK' WHERE name <> "wait"; TRUNCATE TABLE performance_schema.events_waits_history_long; TRUNCATE TABLE performance_schema.events_waits_history; @@ -327,7 +331,8 @@ flush privileges; UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; UPDATE performance_schema.setup_consumers SET enabled = 'YES'; +# Restore the default values for the timers that we changed. +# Note, we did not touch "wait", see above. UPDATE performance_schema.setup_timers SET timer_name = 'MICROSECOND' where name="idle"; -UPDATE performance_schema.setup_timers SET timer_name = 'CYCLE' where name="wait"; UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="stage"; UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="statement"; diff --git a/mysql-test/suite/plugins/r/feedback_plugin_install.result b/mysql-test/suite/plugins/r/feedback_plugin_install.result index 37d26b48501..c52fdb8f85b 100644 --- a/mysql-test/suite/plugins/r/feedback_plugin_install.result +++ b/mysql-test/suite/plugins/r/feedback_plugin_install.result @@ -3,7 +3,8 @@ select plugin_status from information_schema.plugins where plugin_name='feedback plugin_status ACTIVE select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid'; + and variable_name not like '%_uid' + and variable_name not like '%debug%'; VARIABLE_NAME VARIABLE_VALUE FEEDBACK used 1 FEEDBACK version 1.1 diff --git a/mysql-test/suite/plugins/r/feedback_plugin_load.result b/mysql-test/suite/plugins/r/feedback_plugin_load.result index 9fd488052e4..8770ce19f49 100644 --- a/mysql-test/suite/plugins/r/feedback_plugin_load.result +++ b/mysql-test/suite/plugins/r/feedback_plugin_load.result @@ -6,7 +6,8 @@ SELECT variable_value = @feedback_used + 1 FROM information_schema.feedback wher variable_value = @feedback_used + 1 1 select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used'; + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used' + and variable_name not like '%debug%'; VARIABLE_NAME VARIABLE_VALUE FEEDBACK version 1.1 FEEDBACK_HTTP_PROXY diff --git a/mysql-test/suite/plugins/r/feedback_plugin_send.result b/mysql-test/suite/plugins/r/feedback_plugin_send.result index df55c3fdedb..935ea11d67b 100644 --- a/mysql-test/suite/plugins/r/feedback_plugin_send.result +++ b/mysql-test/suite/plugins/r/feedback_plugin_send.result @@ -6,7 +6,8 @@ SELECT variable_value = @feedback_used + 1 FROM information_schema.feedback wher variable_value = @feedback_used + 1 1 select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used'; + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used' + and variable_name not like '%debug%'; VARIABLE_NAME VARIABLE_VALUE FEEDBACK version 1.1 FEEDBACK_HTTP_PROXY @@ -23,7 +24,6 @@ VARIABLE_VALUE>0 VARIABLE_NAME 1 Collation used latin1_swedish_ci 1 Collation used utf8_bin 1 Collation used utf8_general_ci -feedback plugin: report to 'http://mariadb.org/feedback_plugin/post' was sent -feedback plugin: server replied 'ok' -feedback plugin: report to 'http://mariadb.org/feedback_plugin/post' was sent -feedback plugin: server replied 'ok' +set global sql_mode=ONLY_FULL_GROUP_BY; +6: feedback plugin: report to 'http://mariadb.org/feedback_plugin/post' was sent +6: feedback plugin: server replied 'ok' diff --git a/mysql-test/suite/plugins/t/feedback_plugin_install.test b/mysql-test/suite/plugins/t/feedback_plugin_install.test index 81343c436c3..559dcebfc05 100644 --- a/mysql-test/suite/plugins/t/feedback_plugin_install.test +++ b/mysql-test/suite/plugins/t/feedback_plugin_install.test @@ -10,6 +10,8 @@ select plugin_status from information_schema.plugins where plugin_name='feedback --replace_result https http --sorted_result select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid'; + and variable_name not like '%_uid' + and variable_name not like '%debug%'; + uninstall plugin feedback; diff --git a/mysql-test/suite/plugins/t/feedback_plugin_load.test b/mysql-test/suite/plugins/t/feedback_plugin_load.test index f2f8c1f97a7..8b4aee28362 100644 --- a/mysql-test/suite/plugins/t/feedback_plugin_load.test +++ b/mysql-test/suite/plugins/t/feedback_plugin_load.test @@ -24,7 +24,8 @@ SELECT variable_value = @feedback_used + 1 FROM information_schema.feedback wher --replace_result https http --sorted_result select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used'; + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used' + and variable_name not like '%debug%'; # Embedded server does not use the table mysqld.user and thus # does not automatically use latin1_bin on startup. Use it manually. diff --git a/mysql-test/suite/plugins/t/feedback_plugin_send.test b/mysql-test/suite/plugins/t/feedback_plugin_send.test index 31542c33482..b28f9d4cb38 100644 --- a/mysql-test/suite/plugins/t/feedback_plugin_send.test +++ b/mysql-test/suite/plugins/t/feedback_plugin_send.test @@ -13,7 +13,11 @@ if (!$MTR_FEEDBACK_PLUGIN) { # Let's wait, and hope that mtr is started with --parallel and # is doing some work in other workers. # -sleep 310; + +sleep 100; +set global sql_mode=ONLY_FULL_GROUP_BY; +sleep 210; + # The test expects that the plugin will send a report at least 2 times, # now (5 min after loading) and on server shutdown which happens below. @@ -25,20 +29,15 @@ sleep 310; --let $shutdown_timeout= 60 source include/restart_mysqld.inc; -replace_result https http; +replace_result https http 2 6; perl; $log_error= $ENV{'MYSQLTEST_VARDIR'} . '/log/mysqld.1.err'; open(LOG, '<', $log_error) or die "open(< $log_error): $!"; - # Get the first few rows (as there may be different number rows in the log) - $i= 0; - while ($_=<LOG>) - { - if (/feedback plugin:.*/) - { - print "$&\n"; - break if ($i++ >= 3); - } + %logg=(); + while ($_=<LOG>) { + $logg{$&}++ if /feedback plugin:.*/; } + print "$logg{$_}: $_\n" for sort keys %logg; close LOG; EOF diff --git a/mysql-test/suite/roles/create_and_drop_role.result b/mysql-test/suite/roles/create_and_drop_role.result index 79c6f412111..d565b888c5f 100644 --- a/mysql-test/suite/roles/create_and_drop_role.result +++ b/mysql-test/suite/roles/create_and_drop_role.result @@ -36,6 +36,10 @@ select user, host, is_role from user where user like 'test%'; user host is_role create role ''; ERROR OP000: Invalid role specification ``. +create role ' '; +ERROR OP000: Invalid role specification ``. +create role 'foo '; +drop role foo; create role r1; drop user r1; ERROR HY000: Operation DROP USER failed for 'r1'@'%' diff --git a/mysql-test/suite/roles/create_and_drop_role.test b/mysql-test/suite/roles/create_and_drop_role.test index 0bf5b744e6b..71d6de7053f 100644 --- a/mysql-test/suite/roles/create_and_drop_role.test +++ b/mysql-test/suite/roles/create_and_drop_role.test @@ -53,6 +53,14 @@ connection default; create role ''; # +# MDEV-8609 Server crashes in is_invalid_role_name on reloading ACL with a blank role name +# +--error ER_INVALID_ROLE +create role ' '; +create role 'foo '; +drop role foo; + +# # MDEV-5523 Server crashes on DROP USER <rolename> # create role r1; diff --git a/mysql-test/suite/roles/ip-6401.test b/mysql-test/suite/roles/ip-6401.test index 34d8553afa3..b7d4b168b75 100644 --- a/mysql-test/suite/roles/ip-6401.test +++ b/mysql-test/suite/roles/ip-6401.test @@ -1,3 +1,6 @@ +# +# MDEV-6401 SET ROLE returning ERROR 1959 Invalid role specification for valid role +# --source include/not_embedded.inc create role r1; create user foo@'127.0.0.1'; diff --git a/mysql-test/suite/roles/rebuild_role_grants.result b/mysql-test/suite/roles/rebuild_role_grants.result index 2817c046ae9..bc68d8ddc9f 100644 --- a/mysql-test/suite/roles/rebuild_role_grants.result +++ b/mysql-test/suite/roles/rebuild_role_grants.result @@ -56,3 +56,9 @@ Host User Role Admin_option localhost root r1 Y drop role r1; drop user u2; +create user foo@localhost; +grant create user on *.* to foo@localhost; +create role look, isp, xxx, ppp; +rename user current_user to nnnn@'%'; +drop role look, isp, xxx, ppp; +drop user nnnn@'%'; diff --git a/mysql-test/suite/roles/rebuild_role_grants.test b/mysql-test/suite/roles/rebuild_role_grants.test index 3ec3577317a..84dbdf78fb8 100644 --- a/mysql-test/suite/roles/rebuild_role_grants.test +++ b/mysql-test/suite/roles/rebuild_role_grants.test @@ -54,3 +54,16 @@ select * from mysql.roles_mapping; drop role r1; drop user u2; + +# +# MDEV-8614 Assertion `status == 0' failed in add_role_user_mapping_action on RENAME USER +# +create user foo@localhost; +grant create user on *.* to foo@localhost; +--connect (con1, localhost, foo,,) +create role look, isp, xxx, ppp; +rename user current_user to nnnn@'%'; +drop role look, isp, xxx, ppp; +connection default; +disconnect con1; +drop user nnnn@'%'; diff --git a/mysql-test/suite/rpl/r/rpl_parallel2.result b/mysql-test/suite/rpl/r/rpl_parallel2.result index 2ca73738d84..f79661ee6fb 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel2.result +++ b/mysql-test/suite/rpl/r/rpl_parallel2.result @@ -88,6 +88,12 @@ a b 18 0 19 0 UNLOCK TABLES; +SELECT "after UNLOCK TABLES" as state; +state +after UNLOCK TABLES +SELECT "after reap of STOP SLAVE" as state; +state +after reap of STOP SLAVE include/wait_for_slave_to_stop.inc include/start_slave.inc SELECT * FROM t2 ORDER BY a; diff --git a/mysql-test/suite/rpl/t/rpl_parallel2.test b/mysql-test/suite/rpl/t/rpl_parallel2.test index 50617c63024..3a9c801175f 100644 --- a/mysql-test/suite/rpl/t/rpl_parallel2.test +++ b/mysql-test/suite/rpl/t/rpl_parallel2.test @@ -162,9 +162,13 @@ SELECT * FROM t2 ORDER BY a; --connection s2 UNLOCK TABLES; +SELECT "after UNLOCK TABLES" as state; + --connection s1 reap; +SELECT "after reap of STOP SLAVE" as state; + --connection server_2 --source include/wait_for_slave_to_stop.inc --source include/start_slave.inc diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result index 6c7051dc3d0..d3d36b2d40e 100644 --- a/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result @@ -130,6 +130,9 @@ os_log_bytes_written disabled os_log_fsyncs disabled os_log_pending_fsyncs disabled os_log_pending_writes disabled +os_merge_blocks_written disabled +os_merge_blocks_read disabled +os_merge_blocks_merged disabled trx_rw_commits disabled trx_ro_commits disabled trx_nl_ro_commits disabled @@ -278,6 +281,9 @@ lock_row_lock_time disabled lock_row_lock_time_max disabled lock_row_lock_waits disabled lock_row_lock_time_avg disabled +os_merge_blocks_written disabled +os_merge_blocks_read disabled +os_merge_blocks_merged disabled innodb_rwlock_s_spin_waits disabled innodb_rwlock_x_spin_waits disabled innodb_rwlock_s_spin_rounds disabled @@ -316,6 +322,9 @@ os_log_bytes_written disabled os_log_fsyncs disabled os_log_pending_fsyncs enabled os_log_pending_writes enabled +os_merge_blocks_written disabled +os_merge_blocks_read disabled +os_merge_blocks_merged disabled set global innodb_monitor_enable=""; ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '' set global innodb_monitor_enable="_"; diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result index 6c7051dc3d0..d3d36b2d40e 100644 --- a/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result @@ -130,6 +130,9 @@ os_log_bytes_written disabled os_log_fsyncs disabled os_log_pending_fsyncs disabled os_log_pending_writes disabled +os_merge_blocks_written disabled +os_merge_blocks_read disabled +os_merge_blocks_merged disabled trx_rw_commits disabled trx_ro_commits disabled trx_nl_ro_commits disabled @@ -278,6 +281,9 @@ lock_row_lock_time disabled lock_row_lock_time_max disabled lock_row_lock_waits disabled lock_row_lock_time_avg disabled +os_merge_blocks_written disabled +os_merge_blocks_read disabled +os_merge_blocks_merged disabled innodb_rwlock_s_spin_waits disabled innodb_rwlock_x_spin_waits disabled innodb_rwlock_s_spin_rounds disabled @@ -316,6 +322,9 @@ os_log_bytes_written disabled os_log_fsyncs disabled os_log_pending_fsyncs enabled os_log_pending_writes enabled +os_merge_blocks_written disabled +os_merge_blocks_read disabled +os_merge_blocks_merged disabled set global innodb_monitor_enable=""; ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '' set global innodb_monitor_enable="_"; diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result index 6c7051dc3d0..d3d36b2d40e 100644 --- a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result @@ -130,6 +130,9 @@ os_log_bytes_written disabled os_log_fsyncs disabled os_log_pending_fsyncs disabled os_log_pending_writes disabled +os_merge_blocks_written disabled +os_merge_blocks_read disabled +os_merge_blocks_merged disabled trx_rw_commits disabled trx_ro_commits disabled trx_nl_ro_commits disabled @@ -278,6 +281,9 @@ lock_row_lock_time disabled lock_row_lock_time_max disabled lock_row_lock_waits disabled lock_row_lock_time_avg disabled +os_merge_blocks_written disabled +os_merge_blocks_read disabled +os_merge_blocks_merged disabled innodb_rwlock_s_spin_waits disabled innodb_rwlock_x_spin_waits disabled innodb_rwlock_s_spin_rounds disabled @@ -316,6 +322,9 @@ os_log_bytes_written disabled os_log_fsyncs disabled os_log_pending_fsyncs enabled os_log_pending_writes enabled +os_merge_blocks_written disabled +os_merge_blocks_read disabled +os_merge_blocks_merged disabled set global innodb_monitor_enable=""; ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '' set global innodb_monitor_enable="_"; diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result index 6c7051dc3d0..d3d36b2d40e 100644 --- a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result @@ -130,6 +130,9 @@ os_log_bytes_written disabled os_log_fsyncs disabled os_log_pending_fsyncs disabled os_log_pending_writes disabled +os_merge_blocks_written disabled +os_merge_blocks_read disabled +os_merge_blocks_merged disabled trx_rw_commits disabled trx_ro_commits disabled trx_nl_ro_commits disabled @@ -278,6 +281,9 @@ lock_row_lock_time disabled lock_row_lock_time_max disabled lock_row_lock_waits disabled lock_row_lock_time_avg disabled +os_merge_blocks_written disabled +os_merge_blocks_read disabled +os_merge_blocks_merged disabled innodb_rwlock_s_spin_waits disabled innodb_rwlock_x_spin_waits disabled innodb_rwlock_s_spin_rounds disabled @@ -316,6 +322,9 @@ os_log_bytes_written disabled os_log_fsyncs disabled os_log_pending_fsyncs enabled os_log_pending_writes enabled +os_merge_blocks_written disabled +os_merge_blocks_read disabled +os_merge_blocks_merged disabled set global innodb_monitor_enable=""; ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '' set global innodb_monitor_enable="_"; 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 # ----------------------------------------------------------------- |