summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
authorMarko Mäkelä <marko.makela@mariadb.com>2022-02-09 16:24:19 +0200
committerMarko Mäkelä <marko.makela@mariadb.com>2022-02-09 16:24:19 +0200
commitc75e3770dc9dc3154538c10c51deb2d095a5e232 (patch)
tree2d45c07ce1e7c60b991fb47c90412b08a61d7c0a /mysql-test
parent12cd3dc78d2a58a15377000a7a8adb92d4fa74fb (diff)
parent70a8875564799c726960cc644dc6eed7ca499e71 (diff)
downloadmariadb-git-c75e3770dc9dc3154538c10c51deb2d095a5e232.tar.gz
Merge 10.7 into 10.8
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/main/backup_locks.result23
-rw-r--r--mysql-test/main/backup_locks.test31
-rw-r--r--mysql-test/main/group_min_max.result37
-rw-r--r--mysql-test/main/group_min_max.test33
-rw-r--r--mysql-test/suite/galera_sr/r/MDEV-27615.result2
-rw-r--r--mysql-test/suite/innodb/r/ibuf_not_empty.result1
-rw-r--r--mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result1
-rw-r--r--mysql-test/suite/innodb/t/ibuf_not_empty.test1
-rw-r--r--mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test1
-rw-r--r--mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result14
-rw-r--r--mysql-test/suite/sys_vars/r/sysvars_innodb.result2
11 files changed, 133 insertions, 13 deletions
diff --git a/mysql-test/main/backup_locks.result b/mysql-test/main/backup_locks.result
index 1505c39f166..1e567c1a58d 100644
--- a/mysql-test/main/backup_locks.result
+++ b/mysql-test/main/backup_locks.result
@@ -39,6 +39,28 @@ MDL_INTENTION_EXCLUSIVE Schema metadata lock test
select * from t1;
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
backup unlock;
+connection con1;
+connection default;
+#
+# Check that BACKUP LOCK blocks some operations
+#
+create sequence seq1;
+create sequence seq2;
+backup lock seq1;
+connection con1;
+CREATE OR REPLACE SEQUENCE seq1 START -28;
+ERROR HY000: Sequence 'test.seq1' values are conflicting
+SET STATEMENT max_statement_time=10 FOR CREATE OR REPLACE SEQUENCE seq1 START 50;
+ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
+SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 NOMAXVALUE;
+ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
+SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 MAXVALUE 1000;
+ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
+SET STATEMENT max_statement_time=10 for rename table seq2 to seq3, seq3 to seq1;
+ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
+connection default;
+backup unlock;
+drop table seq1,seq2;
#
# BACKUP LOCK and BACKUP UNLOCK are not allowed in procedures.
#
@@ -141,7 +163,6 @@ ERROR HY000: Can't execute the given command because you have active locked tabl
SET STATEMENT max_statement_time=180 FOR BACKUP LOCK test.u;
# restart
#
-connection con1;
connection default;
disconnect con1;
show tables;
diff --git a/mysql-test/main/backup_locks.test b/mysql-test/main/backup_locks.test
index d2f3d95d703..1271abfd993 100644
--- a/mysql-test/main/backup_locks.test
+++ b/mysql-test/main/backup_locks.test
@@ -43,10 +43,39 @@ SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.me
--error ER_LOCK_DEADLOCK
select * from t1;
backup unlock;
+connection con1;
+--reap
+connection default;
+
+--echo #
+--echo # Check that BACKUP LOCK blocks some operations
+--echo #
+
+# These test has to be done with timeouts as we want to ensure that the tables
+# doesn't change
+
+create sequence seq1;
+create sequence seq2;
+backup lock seq1;
+connection con1;
+--error ER_SEQUENCE_INVALID_DATA
+CREATE OR REPLACE SEQUENCE seq1 START -28;
+--error ER_STATEMENT_TIMEOUT
+SET STATEMENT max_statement_time=10 FOR CREATE OR REPLACE SEQUENCE seq1 START 50;
+--error ER_STATEMENT_TIMEOUT
+SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 NOMAXVALUE;
+--error ER_STATEMENT_TIMEOUT
+SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 MAXVALUE 1000;
+--error ER_STATEMENT_TIMEOUT
+SET STATEMENT max_statement_time=10 for rename table seq2 to seq3, seq3 to seq1;
+connection default;
+backup unlock;
+drop table seq1,seq2;
--echo #
--echo # BACKUP LOCK and BACKUP UNLOCK are not allowed in procedures.
--echo #
+
delimiter |;
--error ER_SP_BADSTATEMENT
CREATE PROCEDURE p_BACKUP_LOCK()
@@ -162,8 +191,6 @@ SET STATEMENT max_statement_time=180 FOR BACKUP LOCK test.u;
--echo #
-connection con1;
---reap
connection default;
disconnect con1;
show tables;
diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result
index d6a918e686f..0f75103240d 100644
--- a/mysql-test/main/group_min_max.result
+++ b/mysql-test/main/group_min_max.result
@@ -4028,6 +4028,43 @@ drop table t1;
# End of 10.1 tests
#
#
+# MDEV-27442 Wrong result upon query with DISTINCT and EXISTS subquery
+#
+CREATE TABLE t1 (a int, b int, KEY b (b,a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0,100),(2,100),(2,101),(3,102);
+# Must not use Using index for group-by
+explain SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL b 10 NULL 4 Using where; Using index
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 );
+b
+100
+101
+102
+DROP TABLE t1;
+#
+# MDEV-26585 Wrong query results when `using index for group-by`
+#
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL AUTO_INCREMENT,
+`owner_id` int(11) DEFAULT NULL,
+`foo` tinyint(1) DEFAULT 0,
+`whatever` varchar(255) DEFAULT NULL,
+PRIMARY KEY (`id`),
+KEY `index_t1_on_owner_id_and_foo` (`owner_id`,`foo`)
+) engine=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t1 (owner_id, foo, whatever)
+VALUES (1, TRUE, "yello"), (1, FALSE, "yello"), (2, TRUE, "yello"),
+(2, TRUE, "yello"), (2, FALSE, "yello");
+EXPLAIN SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL index_t1_on_owner_id_and_foo 7 NULL 5 Using where; Using index
+SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
+owner_id
+1
+DROP TABLE t1;
+#
# MDEV-24353: Adding GROUP BY slows down a query
#
CREATE TABLE t1 (p int NOT NULL, a int NOT NULL, PRIMARY KEY (p,a));
diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test
index 1db479b1c74..daa407121cf 100644
--- a/mysql-test/main/group_min_max.test
+++ b/mysql-test/main/group_min_max.test
@@ -5,7 +5,7 @@
--source include/default_optimizer_switch.inc
--source include/have_sequence.inc
-
+--source include/have_innodb.inc
#
# TODO:
# Add queries with:
@@ -1692,6 +1692,37 @@ drop table t1;
--echo #
--echo #
+--echo # MDEV-27442 Wrong result upon query with DISTINCT and EXISTS subquery
+--echo #
+
+CREATE TABLE t1 (a int, b int, KEY b (b,a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0,100),(2,100),(2,101),(3,102);
+--echo # Must not use Using index for group-by
+explain SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 );
+SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 );
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-26585 Wrong query results when `using index for group-by`
+--echo #
+
+CREATE TABLE `t1` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `owner_id` int(11) DEFAULT NULL,
+ `foo` tinyint(1) DEFAULT 0,
+ `whatever` varchar(255) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ KEY `index_t1_on_owner_id_and_foo` (`owner_id`,`foo`)
+) engine=InnoDB DEFAULT CHARSET=utf8;
+
+INSERT INTO t1 (owner_id, foo, whatever)
+VALUES (1, TRUE, "yello"), (1, FALSE, "yello"), (2, TRUE, "yello"),
+ (2, TRUE, "yello"), (2, FALSE, "yello");
+EXPLAIN SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
+SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
+DROP TABLE t1;
+
+--echo #
--echo # MDEV-24353: Adding GROUP BY slows down a query
--echo #
diff --git a/mysql-test/suite/galera_sr/r/MDEV-27615.result b/mysql-test/suite/galera_sr/r/MDEV-27615.result
index a3475811285..e3bfd0ed539 100644
--- a/mysql-test/suite/galera_sr/r/MDEV-27615.result
+++ b/mysql-test/suite/galera_sr/r/MDEV-27615.result
@@ -15,7 +15,7 @@ SET DEBUG_SYNC='now WAIT_FOR before_fragment';
SET GLOBAL wsrep_cluster_address = '';
SET DEBUG_SYNC = 'now SIGNAL continue';
connection node_2;
-ERROR HY000: Lost connection to MySQL server during query
+ERROR HY000: Lost connection to server during query
connection node_2a;
SELECT * FROM mysql.wsrep_streaming_log;
node_uuid trx_id seqno flags frag
diff --git a/mysql-test/suite/innodb/r/ibuf_not_empty.result b/mysql-test/suite/innodb/r/ibuf_not_empty.result
index d1b8203b063..f2da89990b0 100644
--- a/mysql-test/suite/innodb/r/ibuf_not_empty.result
+++ b/mysql-test/suite/innodb/r/ibuf_not_empty.result
@@ -5,6 +5,7 @@ c INT,
INDEX(b))
ENGINE=InnoDB STATS_PERSISTENT=0;
SET GLOBAL innodb_change_buffering_debug = 1;
+SET GLOBAL innodb_change_buffering=all;
INSERT INTO t1 SELECT 0,'x',1 FROM seq_1_to_1024;
# restart: --innodb-force-recovery=6 --innodb-change-buffer-dump
check table t1;
diff --git a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result
index e3037847441..670340f3583 100644
--- a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result
+++ b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result
@@ -13,6 +13,7 @@ c INT,
INDEX(b))
ENGINE=InnoDB STATS_PERSISTENT=0;
SET GLOBAL innodb_change_buffering_debug = 1;
+SET GLOBAL innodb_change_buffering = all;
INSERT INTO t1 SELECT 0,'x',1 FROM seq_1_to_8192;
BEGIN;
SELECT b FROM t1 LIMIT 3;
diff --git a/mysql-test/suite/innodb/t/ibuf_not_empty.test b/mysql-test/suite/innodb/t/ibuf_not_empty.test
index 545a78c887e..96ceb81ac00 100644
--- a/mysql-test/suite/innodb/t/ibuf_not_empty.test
+++ b/mysql-test/suite/innodb/t/ibuf_not_empty.test
@@ -24,6 +24,7 @@ ENGINE=InnoDB STATS_PERSISTENT=0;
# change buffering is possible, so that the change buffer will be used
# whenever possible.
SET GLOBAL innodb_change_buffering_debug = 1;
+SET GLOBAL innodb_change_buffering=all;
# Create enough rows for the table, so that the change buffer will be
# used for modifying the secondary index page. There must be multiple
diff --git a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test
index a12ca43cec1..129037e783b 100644
--- a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test
+++ b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test
@@ -33,6 +33,7 @@ ENGINE=InnoDB STATS_PERSISTENT=0;
# change buffering is possible, so that the change buffer will be used
# whenever possible.
SET GLOBAL innodb_change_buffering_debug = 1;
+SET GLOBAL innodb_change_buffering = all;
let SEARCH_FILE = $MYSQLTEST_VARDIR/log/mysqld.1.err;
# Create enough rows for the table, so that the change buffer will be
diff --git a/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result b/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result
index 92e22c6aa34..c11f4ee617c 100644
--- a/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result
+++ b/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result
@@ -1,28 +1,28 @@
SET @start_global_value = @@global.innodb_change_buffering;
SELECT @start_global_value;
@start_global_value
-all
+none
Valid values are 'all', 'deletes', 'changes', 'inserts', 'none', 'purges'
select @@global.innodb_change_buffering in ('all', 'deletes', 'changes', 'inserts', 'none', 'purges');
@@global.innodb_change_buffering in ('all', 'deletes', 'changes', 'inserts', 'none', 'purges')
1
select @@global.innodb_change_buffering;
@@global.innodb_change_buffering
-all
+none
select @@session.innodb_change_buffering;
ERROR HY000: Variable 'innodb_change_buffering' is a GLOBAL variable
show global variables like 'innodb_change_buffering';
Variable_name Value
-innodb_change_buffering all
+innodb_change_buffering none
show session variables like 'innodb_change_buffering';
Variable_name Value
-innodb_change_buffering all
+innodb_change_buffering none
select * from information_schema.global_variables where variable_name='innodb_change_buffering';
VARIABLE_NAME VARIABLE_VALUE
-INNODB_CHANGE_BUFFERING all
+INNODB_CHANGE_BUFFERING none
select * from information_schema.session_variables where variable_name='innodb_change_buffering';
VARIABLE_NAME VARIABLE_VALUE
-INNODB_CHANGE_BUFFERING all
+INNODB_CHANGE_BUFFERING none
set global innodb_change_buffering='none';
select @@global.innodb_change_buffering;
@@global.innodb_change_buffering
@@ -62,4 +62,4 @@ ERROR 42000: Variable 'innodb_change_buffering' can't be set to the value of 'so
SET @@global.innodb_change_buffering = @start_global_value;
SELECT @@global.innodb_change_buffering;
@@global.innodb_change_buffering
-all
+none
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index 2b4d5c504d4..40702bca323 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -227,7 +227,7 @@ READ_ONLY NO
COMMAND_LINE_ARGUMENT OPTIONAL
VARIABLE_NAME INNODB_CHANGE_BUFFERING
SESSION_VALUE NULL
-DEFAULT_VALUE all
+DEFAULT_VALUE none
VARIABLE_SCOPE GLOBAL
VARIABLE_TYPE ENUM
VARIABLE_COMMENT Buffer changes to secondary indexes.