Debian Codegraph @

mysql-test/suite/period/t/i_s_notembedded.test

raw
1 --source include/not_embedded.inc
2 --source include/platform.inc
3
4 select * from information_schema.periods;
5
6 create or replace table t1 (id int primary key, s timestamp(6), e timestamp(6),
7 period for mytime(s,e));
8
9 create or replace table t2 (id int primary key, s timestamp(6), e timestamp(6),
10 period for mytime(s,e),
11 vs timestamp(6) as row start,
12 ve timestamp(6) as row end,
13 period for system_time(vs, ve))
14 with system versioning;
15
16 show columns from t1;
17
18 --sorted_result
19 select * from information_schema.periods where table_schema = 'test';
20
21 create user periods_hidden@localhost;
22 # Give it a privilege to connect
23 grant create on test.nonexist to periods_hidden@localhost;
24
25 --connect (chopped,localhost,periods_hidden,,test)
26 --sorted_result
27 select * from information_schema.periods where table_schema = 'test';
28 --connection default
29 grant select(id) on test.t1 to periods_hidden@localhost;
30 --connection chopped
31 --sorted_result
32 --connection default
33 revoke select(id) on test.t1 from periods_hidden@localhost;
34 --connection chopped
35 --sorted_result
36 --connection default
37 grant update(id) on test.t1 to periods_hidden@localhost;
38 --connection chopped
39 --sorted_result
40 select * from information_schema.periods where table_schema = 'test';
41 --connection default
42 grant select(s) on test.t1 to periods_hidden@localhost;
43 --connection chopped
44 --sorted_result
45 select * from information_schema.periods where table_schema = 'test';
46 --connection default
47 grant select(e) on test.t2 to periods_hidden@localhost;
48 --connection chopped
49 --sorted_result
50 select * from information_schema.periods where table_schema = 'test';
51 --connection default
52 grant update on test.t2 to periods_hidden@localhost;
53 --connection chopped
54 --sorted_result
55 select * from information_schema.periods where table_schema = 'test';
56 --connection default
57 drop tables t1, t2;
58
59 --echo # MDEV-32503 Queries from KEY_PERIOD_USAGE don't obey case-sensitivity
60
61 create table t (a int, b date, c date, period for app(b,c),
62 unique idx(a, app without overlaps));
63
64
65 set names latin1 collate latin1_general_cs;
66
67 select table_name from information_schema.periods where table_schema = 'TEST';
68 select table_name from information_schema.key_period_usage where table_schema = 'TEST';
69 set names latin1 collate latin1_general_ci;
70
71 select table_name from information_schema.periods where table_schema = 'TEST';
72 select table_name from information_schema.key_period_usage where table_schema = 'TEST';
73
74 --echo # [DUPLICATE] MDEV-32504 Search by I_S.KEY_PERIOD_USAGE.CONSTRAINT_NAME
75 --echo # does not work
76 disable_warnings; # storage engine 'Innodb' is not found
77 select constraint_name from information_schema.key_period_usage where table_name = 't';
78 select constraint_name from information_schema.key_period_usage where constraint_name = 'idx';
79 enable_warnings;
80
81 drop table t;
82
83 --echo # MDEV-32501 KEY_PERIOD_USAGE reveals information to unprivileged user
84 create table t (a int, b date, c date, f int, period for app(b, c),
85 primary key(a, app without overlaps));
86
87 grant select (f) on t to periods_hidden@localhost;
88
89 --connection chopped
90 select period_name from information_schema.key_period_usage where table_name = 't';
91
92 --connection default
93 grant update (f) on t to periods_hidden@localhost;
94 --connection chopped
95 select 'can be seen', constraint_name, period_name from information_schema.key_period_usage where table_name = 't';
96
97 --connection default
98 revoke update (f) on t from periods_hidden@localhost;
99 --connection chopped
100 --error ER_TABLEACCESS_DENIED_ERROR
101 update t set f = 1;
102 select period_name from information_schema.key_period_usage where table_name = 't';
103
104 --connection default
105 grant alter on t to periods_hidden@localhost;
106 --connection chopped
107 select 'can be seen', constraint_name, period_name from information_schema.key_period_usage where table_name = 't';
108
109 --connection default
110 drop table t;
111
112 #
113 # End of 11.4 tests
114 #
115
116 # Global cleanup
117 --disconnect chopped
118 --connection default
119 drop user periods_hidden@localhost;