Debian Codegraph @

mysql-test/suite/period/r/i_s_notembedded.result

raw
1 select * from information_schema.periods;
2 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
3 Warnings:
4 Warning 1286 Unknown storage engine 'InnoDB'
5 Warning 1286 Unknown storage engine 'InnoDB'
6 Warning 1286 Unknown storage engine 'InnoDB'
7 create or replace table t1 (id int primary key, s timestamp(6), e timestamp(6),
8 period for mytime(s,e));
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 show columns from t1;
16 Field Type Null Key Default Extra
17 id int(11) NO PRI NULL
18 s timestamp(6) NO NULL
19 e timestamp(6) NO NULL
20 select * from information_schema.periods where table_schema = 'test';
21 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
22 def test t1 mytime s e
23 def test t2 SYSTEM_TIME vs ve
24 def test t2 mytime s e
25 create user periods_hidden@localhost;
26 grant create on test.nonexist to periods_hidden@localhost;
27 connect chopped,localhost,periods_hidden,,test;
28 select * from information_schema.periods where table_schema = 'test';
29 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
30 connection default;
31 grant select(id) on test.t1 to periods_hidden@localhost;
32 connection chopped;
33 connection default;
34 revoke select(id) on test.t1 from periods_hidden@localhost;
35 connection chopped;
36 connection default;
37 grant update(id) on test.t1 to periods_hidden@localhost;
38 connection chopped;
39 select * from information_schema.periods where table_schema = 'test';
40 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
41 def test t1 mytime NULL NULL
42 connection default;
43 grant select(s) on test.t1 to periods_hidden@localhost;
44 connection chopped;
45 select * from information_schema.periods where table_schema = 'test';
46 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
47 def test t1 mytime s NULL
48 connection default;
49 grant select(e) on test.t2 to periods_hidden@localhost;
50 connection chopped;
51 select * from information_schema.periods where table_schema = 'test';
52 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
53 def test t1 mytime s NULL
54 def test t2 SYSTEM_TIME NULL NULL
55 def test t2 mytime NULL e
56 connection default;
57 grant update on test.t2 to periods_hidden@localhost;
58 connection chopped;
59 select * from information_schema.periods where table_schema = 'test';
60 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
61 def test t1 mytime s NULL
62 def test t2 SYSTEM_TIME vs ve
63 def test t2 mytime s e
64 connection default;
65 drop tables t1, t2;
66 # MDEV-32503 Queries from KEY_PERIOD_USAGE don't obey case-sensitivity
67 create table t (a int, b date, c date, period for app(b,c),
68 unique idx(a, app without overlaps));
69 set names latin1 collate latin1_general_cs;
70 select table_name from information_schema.periods where table_schema = 'TEST';
71 table_name
72 select table_name from information_schema.key_period_usage where table_schema = 'TEST';
73 table_name
74 set names latin1 collate latin1_general_ci;
75 select table_name from information_schema.periods where table_schema = 'TEST';
76 table_name
77 select table_name from information_schema.key_period_usage where table_schema = 'TEST';
78 table_name
79 # [DUPLICATE] MDEV-32504 Search by I_S.KEY_PERIOD_USAGE.CONSTRAINT_NAME
80 # does not work
81 select constraint_name from information_schema.key_period_usage where table_name = 't';
82 constraint_name
83 idx
84 select constraint_name from information_schema.key_period_usage where constraint_name = 'idx';
85 constraint_name
86 idx
87 drop table t;
88 # MDEV-32501 KEY_PERIOD_USAGE reveals information to unprivileged user
89 create table t (a int, b date, c date, f int, period for app(b, c),
90 primary key(a, app without overlaps));
91 grant select (f) on t to periods_hidden@localhost;
92 connection chopped;
93 select period_name from information_schema.key_period_usage where table_name = 't';
94 period_name
95 connection default;
96 grant update (f) on t to periods_hidden@localhost;
97 connection chopped;
98 select 'can be seen', constraint_name, period_name from information_schema.key_period_usage where table_name = 't';
99 can be seen constraint_name period_name
100 can be seen PRIMARY app
101 connection default;
102 revoke update (f) on t from periods_hidden@localhost;
103 connection chopped;
104 update t set f = 1;
105 ERROR 42000: UPDATE command denied to user 'periods_hidden'@'localhost' for table `test`.`t`
106 select period_name from information_schema.key_period_usage where table_name = 't';
107 period_name
108 connection default;
109 grant alter on t to periods_hidden@localhost;
110 connection chopped;
111 select 'can be seen', constraint_name, period_name from information_schema.key_period_usage where table_name = 't';
112 can be seen constraint_name period_name
113 can be seen PRIMARY app
114 connection default;
115 drop table t;
116 disconnect chopped;
117 connection default;
118 drop user periods_hidden@localhost;