Debian Codegraph @

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

raw
1 create table t (id int primary key, s date, e date, period for mytime(s,e));
2 # CONSTRAINT CHECK (s < e) is added implicitly, and shouldn't be shown
3 # this is important for correct command-based replication
4 show create table t;
5 Table Create Table
6 t CREATE TABLE `t` (
7 `id` int(11) NOT NULL,
8 `s` date NOT NULL,
9 `e` date NOT NULL,
10 PERIOD FOR `mytime` (`s`, `e`),
11 PRIMARY KEY (`id`)
12 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
13 create view v as select * from t;
14 select * from information_schema.periods;
15 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
16 def test t mytime s e
17 Warnings:
18 Warning 1286 Unknown storage engine 'InnoDB'
19 Warning 1286 Unknown storage engine 'InnoDB'
20 Warning 1286 Unknown storage engine 'InnoDB'
21 select * from information_schema.key_period_usage;
22 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD_NAME
23 Warnings:
24 Warning 1286 Unknown storage engine 'InnoDB'
25 Warning 1286 Unknown storage engine 'InnoDB'
26 Warning 1286 Unknown storage engine 'InnoDB'
27 drop view v;
28 create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
29 period for mytime(s,e));
30 show create table t;
31 Table Create Table
32 t CREATE TABLE `t` (
33 `id` int(11) NOT NULL,
34 `s` timestamp(6) NOT NULL,
35 `e` timestamp(6) NOT NULL,
36 PERIOD FOR `mytime` (`s`, `e`),
37 PRIMARY KEY (`id`)
38 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
39 # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)a)
40 # 2) If a <table period definition> TPD is specified, then:
41 # a) <table scope> shall not be specified.
42 create or replace temporary table t (s date, e date, period for mytime(s,e));
43 ERROR HY000: Application-time period table cannot be temporary
44 # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)iii)
45 # The <data type or domain name> contained in CD1 is either DATE or a
46 # timestamp type and it is equivalent to the <data type or domain name>
47 # contained in CD2.
48 create or replace table t (id int primary key, s datetime, e date,
49 period for mytime(s,e));
50 ERROR HY000: Fields of PERIOD FOR `mytime` have different types
51 create or replace table t (s timestamp(2), e timestamp(6),
52 period for mytime(s,e));
53 ERROR HY000: Fields of PERIOD FOR `mytime` have different types
54 create or replace table t (id int primary key, s int, e date,
55 period for mytime(s,e));
56 ERROR 42000: Incorrect column specifier for column 's'
57 create or replace table t (id int primary key, s time, e time,
58 period for mytime(s,e));
59 ERROR 42000: Incorrect column specifier for column 's'
60 create or replace table t (id int primary key, s date, e date,
61 period for mytime(s,x));
62 ERROR 42S22: Unknown column 'x' in 'mytime'
63 # MDEV-18842: Unfortunate error message when the same column is used
64 # for application period start and end
65 create or replace table t (s date, t date, period for apt(s,s));
66 ERROR 42000: Column 's' specified twice
67 create or replace table t (id int primary key, s date, e date,
68 period for mytime(s,e),
69 period for mytime2(s,e));
70 ERROR HY000: Cannot specify more than one application-time period
71 # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)d)
72 # No <column name> in any <column definition> shall be equivalent to PN.
73 create or replace table t (mytime int, s date, e date,
74 period for mytime(s,e));
75 ERROR 42S21: Duplicate column name 'mytime'
76 # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)v)2)A)
77 # Neither CD1 nor CD2 shall contain an <identity column specification>, a
78 # <generation clause>, a <system time period start column specification>,
79 # or a <system time period end column specification>.
80 create or replace table t (id int primary key,
81 s date,
82 e date generated always as (s+1),
83 period for mytime(s,e));
84 ERROR HY000: Period field `e` cannot be GENERATED ALWAYS AS
85 create or replace table t (id int primary key,
86 s date,
87 e date as (s+1) VIRTUAL,
88 period for mytime(s,e));
89 ERROR HY000: Period field `e` cannot be GENERATED ALWAYS AS
90 create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
91 st timestamp(6) as row start,
92 en timestamp(6) as row end,
93 period for system_time (st, en),
94 period for mytime(st,e)) with system versioning;
95 ERROR HY000: Period field `st` cannot be GENERATED ALWAYS AS
96 # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)
97 # Let IDCN be an implementation-dependent <constraint name> that is not
98 # equivalent to the <constraint name> of any table constraint descriptor
99 # included in S.
100 create or replace table t (x int, s date, e date,
101 period for mytime(s, e),
102 constraint mytime check (x > 1));
103 show create table t;
104 Table Create Table
105 t CREATE TABLE `t` (
106 `x` int(11) DEFAULT NULL,
107 `s` date NOT NULL,
108 `e` date NOT NULL,
109 PERIOD FOR `mytime` (`s`, `e`),
110 CONSTRAINT `mytime` CHECK (`x` > 1)
111 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
112 insert t values (2, '2001-01-01', '2001-01-01');
113 ERROR 23000: CONSTRAINT `mytime_1` failed for `test`.`t`
114 show status like "Feature_application_time_periods";
115 Variable_name Value
116 Feature_application_time_periods 6
117 drop table t;
118 # MDEV-29387: Period name with more than 32 symbols crashes the server
119 #
120 # test 34 symbols
121 create table t2 (s date, e date,
122 period for `abcd123456789012345678901234567890` (s,e));
123 drop table t2;
124 # test 64 symbols
125 create table t2 (s date, e date, period for
126 `abcd123456789012345678901234567890123456789012345678901234567890`
127 (s,e));
128 drop table t2;
129 # MDEV-32205 Server crashes in get_schema_key_period_usage_record on
130 # server without InnoDB
131 # Make sure innodb id disabled, but there's at least one innodb table
132 select "yes" from information_schema.tables where engine="innodb" limit 1;
133 yes
134 yes
135 select plugin_status from information_schema.all_plugins where plugin_name = "innodb";
136 plugin_status
137 DISABLED
138 select * from information_schema.periods;
139 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
140 select * from information_schema.key_period_usage;
141 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD_NAME
142 # [DUPLICATE] MDEV-32204 Server crashes in
143 # get_schema_key_period_usage_record
144 create table t (a date) engine=myisam;
145 create table t1 (a int) engine=merge union = (t) ;
146 select 1 from information_schema.key_period_usage;
147 1
148 Warnings:
149 Warning 1168 Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
150 Warning 1286 Unknown storage engine 'InnoDB'
151 Warning 1286 Unknown storage engine 'InnoDB'
152 Warning 1286 Unknown storage engine 'InnoDB'
153 drop table t1;
154 drop table t;
155 create view v1 as select 1;
156 create view v2 as select * from v1;
157 drop view v1;
158 select * from information_schema.key_period_usage;
159 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD_NAME
160 Warnings:
161 Warning 1286 Unknown storage engine 'InnoDB'
162 Warning 1286 Unknown storage engine 'InnoDB'
163 Warning 1286 Unknown storage engine 'InnoDB'
164 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
165 drop view v2;