Merging upstream version 1:11.4.2.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

6 files changed, 207 insertions, 3 deletions

diff --git a/mysql-test/suite/period/r/create.result b/mysql-test/suite/period/r/create.result
index ad9985d7..8ba5ce31 100644
--- a/mysql-test/suite/period/r/create.result
+++ b/mysql-test/suite/period/r/create.result
@@ -10,6 +10,21 @@ t	CREATE TABLE `t` (
   PERIOD FOR `mytime` (`s`, `e`),
   PRIMARY KEY (`id`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+create view v as select * from t;
+select * from information_schema.periods;
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PERIOD	START_COLUMN_NAME	END_COLUMN_NAME
+def	test	t	mytime	s	e
+Warnings:
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
+select * from information_schema.key_period_usage;
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PERIOD_NAME
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
+Warnings:
+drop view v;
 create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
 period for mytime(s,e));
 show create table t;
@@ -111,3 +126,40 @@ create table t2 (s date, e date, period for
 `abcd123456789012345678901234567890123456789012345678901234567890`
                  (s,e));
 drop table t2;
+# MDEV-32205 Server crashes in get_schema_key_period_usage_record on
+# server without InnoDB
+# Make sure innodb id disabled, but there's at least one innodb table
+select "yes" from information_schema.tables where engine="innodb" limit 1;
+yes
+yes
+select plugin_status from information_schema.all_plugins where plugin_name = "innodb";
+plugin_status
+DISABLED
+select * from information_schema.periods;
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PERIOD	START_COLUMN_NAME	END_COLUMN_NAME
+select * from information_schema.key_period_usage;
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PERIOD_NAME
+# [DUPLICATE] MDEV-32204 Server crashes in
+# get_schema_key_period_usage_record
+create table t (a date) engine=myisam;
+create table  t1 (a int) engine=merge union = (t) ;
+select 1 from information_schema.key_period_usage;
+1
+Warning	1168	Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
+Warnings:
+drop table t1;
+drop table t;
+create view v1 as select 1;
+create view v2 as select * from v1;
+drop view v1;
+select * from information_schema.key_period_usage;
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PERIOD_NAME
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1356	View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+Warnings:
+drop view v2;
diff --git a/mysql-test/suite/period/r/delete,myisam.rdiff b/mysql-test/suite/period/r/delete,myisam.rdiff
index 78fb972b..179f399a 100644
--- a/mysql-test/suite/period/r/delete,myisam.rdiff
+++ b/mysql-test/suite/period/r/delete,myisam.rdiff
@@ -1,5 +1,5 @@
---- suite/period/r/delete.result	2019-02-16 11:14:23.511258191 +0100
-+++ suite/period/r/delete.reject	2019-02-16 11:14:32.869258690 +0100
+--- suite/period/r/delete.result
++++ suite/period/r/delete.reject
 @@ -250,7 +250,6 @@
  ERROR 22003: Out of range value for column 'id' at row 1
  select * from t;
diff --git a/mysql-test/suite/period/r/i_s_notembedded,win.rdiff b/mysql-test/suite/period/r/i_s_notembedded,win.rdiff
new file mode 100644
index 00000000..eee35182
--- /dev/null
+++ b/mysql-test/suite/period/r/i_s_notembedded,win.rdiff
@@ -0,0 +1,20 @@
+--- suite/period/r/i_s_notembedded.result	2024-01-01 19:50:37.000000000 +0100
++++ suite/period/r/i_s_notembedded,win.reject	2024-01-01 19:57:18.888306500 +0100
+@@ -69,13 +69,17 @@
+ set names latin1 collate latin1_general_cs;
+ select table_name from information_schema.periods where table_schema = 'TEST';
+ table_name
++t
+ select table_name from information_schema.key_period_usage where table_schema = 'TEST';
+ table_name
++t
+ set names latin1 collate latin1_general_ci;
+ select table_name from information_schema.periods where table_schema = 'TEST';
+ table_name
++t
+ select table_name from information_schema.key_period_usage where table_schema = 'TEST';
+ table_name
++t
+ # [DUPLICATE] MDEV-32504 Search by I_S.KEY_PERIOD_USAGE.CONSTRAINT_NAME
+ # does not work
+ select constraint_name from information_schema.key_period_usage where table_name = 't';
diff --git a/mysql-test/suite/period/r/i_s_notembedded.result b/mysql-test/suite/period/r/i_s_notembedded.result
new file mode 100644
index 00000000..80070ef6
--- /dev/null
+++ b/mysql-test/suite/period/r/i_s_notembedded.result
@@ -0,0 +1,118 @@
+select * from information_schema.periods;
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PERIOD	START_COLUMN_NAME	END_COLUMN_NAME
+Warnings:
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
+create or replace table t1 (id int primary key, s timestamp(6), e timestamp(6),
+period for mytime(s,e));
+create or replace table t2 (id int primary key, s timestamp(6), e timestamp(6),
+period for mytime(s,e),
+vs timestamp(6) as row start,
+ve timestamp(6) as row end,
+period for system_time(vs, ve))
+with system versioning;
+show columns from t1;
+Field	Type	Null	Key	Default	Extra
+id	int(11)	NO	PRI	NULL	
+s	timestamp(6)	NO		NULL	
+e	timestamp(6)	NO		NULL	
+select * from information_schema.periods where table_schema = 'test';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PERIOD	START_COLUMN_NAME	END_COLUMN_NAME
+def	test	t1	mytime	s	e
+def	test	t2	SYSTEM_TIME	vs	ve
+def	test	t2	mytime	s	e
+create user periods_hidden@localhost;
+grant create on test.nonexist to periods_hidden@localhost;
+connect  chopped,localhost,periods_hidden,,test;
+select * from information_schema.periods where table_schema = 'test';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PERIOD	START_COLUMN_NAME	END_COLUMN_NAME
+connection default;
+grant select(id) on test.t1 to periods_hidden@localhost;
+connection chopped;
+connection default;
+revoke select(id) on test.t1 from periods_hidden@localhost;
+connection chopped;
+connection default;
+grant update(id) on test.t1 to periods_hidden@localhost;
+connection chopped;
+select * from information_schema.periods where table_schema = 'test';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PERIOD	START_COLUMN_NAME	END_COLUMN_NAME
+def	test	t1	mytime	NULL	NULL
+connection default;
+grant select(s) on test.t1 to periods_hidden@localhost;
+connection chopped;
+select * from information_schema.periods where table_schema = 'test';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PERIOD	START_COLUMN_NAME	END_COLUMN_NAME
+def	test	t1	mytime	s	NULL
+connection default;
+grant select(e) on test.t2 to periods_hidden@localhost;
+connection chopped;
+select * from information_schema.periods where table_schema = 'test';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PERIOD	START_COLUMN_NAME	END_COLUMN_NAME
+def	test	t1	mytime	s	NULL
+def	test	t2	SYSTEM_TIME	NULL	NULL
+def	test	t2	mytime	NULL	e
+connection default;
+grant update on test.t2 to periods_hidden@localhost;
+connection chopped;
+select * from information_schema.periods where table_schema = 'test';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PERIOD	START_COLUMN_NAME	END_COLUMN_NAME
+def	test	t1	mytime	s	NULL
+def	test	t2	SYSTEM_TIME	vs	ve
+def	test	t2	mytime	s	e
+connection default;
+drop tables t1, t2;
+# MDEV-32503 Queries from KEY_PERIOD_USAGE don't obey case-sensitivity
+create table t (a int, b date, c date, period for app(b,c),
+unique idx(a, app without overlaps));
+set names latin1 collate latin1_general_cs;
+select table_name from information_schema.periods where table_schema = 'TEST';
+table_name
+select table_name from information_schema.key_period_usage where table_schema = 'TEST';
+table_name
+set names latin1 collate latin1_general_ci;
+select table_name from information_schema.periods where table_schema = 'TEST';
+table_name
+select table_name from information_schema.key_period_usage where table_schema = 'TEST';
+table_name
+# [DUPLICATE] MDEV-32504 Search by I_S.KEY_PERIOD_USAGE.CONSTRAINT_NAME
+# does not work
+select constraint_name from information_schema.key_period_usage where table_name = 't';
+constraint_name
+idx
+select constraint_name from information_schema.key_period_usage where constraint_name = 'idx';
+constraint_name
+idx
+drop table t;
+# MDEV-32501 KEY_PERIOD_USAGE reveals information to unprivileged user
+create table t (a int, b date, c date, f int, period for app(b, c),
+primary key(a, app without overlaps));
+grant select (f) on t to periods_hidden@localhost;
+connection chopped;
+select period_name from information_schema.key_period_usage where table_name = 't';
+period_name
+connection default;
+grant update (f) on t to periods_hidden@localhost;
+connection chopped;
+select 'can be seen', constraint_name, period_name from information_schema.key_period_usage where table_name = 't';
+can be seen	constraint_name	period_name
+can be seen	PRIMARY	app
+connection default;
+revoke update (f) on t from periods_hidden@localhost;
+connection chopped;
+update t set f = 1;
+ERROR 42000: UPDATE command denied to user 'periods_hidden'@'localhost' for table `test`.`t`
+select period_name from information_schema.key_period_usage where table_name = 't';
+period_name
+connection default;
+grant alter on t to periods_hidden@localhost;
+connection chopped;
+select 'can be seen', constraint_name, period_name from information_schema.key_period_usage where table_name = 't';
+can be seen	constraint_name	period_name
+can be seen	PRIMARY	app
+connection default;
+drop table t;
+disconnect chopped;
+connection default;
+drop user periods_hidden@localhost;
diff --git a/mysql-test/suite/period/r/overlaps.result b/mysql-test/suite/period/r/overlaps.result
index 36a9086f..1b0f6799 100644
--- a/mysql-test/suite/period/r/overlaps.result
+++ b/mysql-test/suite/period/r/overlaps.result
@@ -12,6 +12,20 @@ t	CREATE TABLE `t` (
   PRIMARY KEY (`id`,`p` WITHOUT OVERLAPS)
 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
  PARTITION BY KEY (`id`)
+select * from information_schema.key_period_usage;
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PERIOD_NAME
+def	test	PRIMARY	def	test	t	p
+create or replace table t_multi(id int, id2 int, s date, e date,
+period for very_interesting_period(s,e),
+primary key(id, very_interesting_period without overlaps),
+unique(id2, very_interesting_period without overlaps)
+);
+select * from information_schema.key_period_usage;
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PERIOD_NAME
+def	test	PRIMARY	def	test	t	p
+def	test	PRIMARY	def	test	t_multi	very_interesting_period
+def	test	id2	def	test	t_multi	very_interesting_period
+drop table t_multi;
 insert into t values (1, '2003-01-01', '2003-03-01'),
 (1, '2003-05-01', '2003-07-01');
 insert into t values (1, '2003-02-01', '2003-04-01');
diff --git a/mysql-test/suite/period/r/update.result b/mysql-test/suite/period/r/update.result
index f726b4c0..004b9976 100644
--- a/mysql-test/suite/period/r/update.result
+++ b/mysql-test/suite/period/r/update.result
@@ -229,8 +229,8 @@ update t for portion of apptime from @s to g() set t.id= t.id + 5;
 ERROR HY000: Expression in FOR PORTION OF must be constant
 # success
 update t for portion of apptime from @s to h() set t.id= t.id + 5;
-# select value is cached
 update t for portion of apptime from (select s from t2 limit 1) to h() set t.id= t.id + 5;
+ERROR HY000: Expression in FOR PORTION OF must be constant
 # auto_inrement field is updated
 create or replace table t (id int primary key auto_increment, x int,
 s date, e date, period for apptime(s, e));