mirror of
https://github.com/MariaDB/server.git
synced 2026-02-16 23:58:42 +01:00
68c1fbfc17
According to the standard, the autoincrement column (i.e. *identity column*) should be advanced each insert implicitly made by UPDATE/DELETE ... FOR PORTION. This is very unconvenient use in several notable cases. Concider a WITHOUT OVERLAPS key with an autoinc column: id int auto_increment, unique(id, p without overlaps) An update or delete with FOR PORTION creates a sense that id will remain unchanged in such case. The standard's IDENTITY reminds MariaDB's AUTO_INCREMENT, however the generation rules differ in many ways. For example, there's also a notion autoincrement index, which is bound to the autoincrement field. We will define our own generation rule for the PORTION OF operations involving AUTO_INCREMENT: * If an autoincrement index contains WITHOUT OVERLAPS specification, then a new value should not be generated, otherwise it should. Apart from WITHOUT OVERLAPS there is also another notable case, referred by the reporter - a unique key that has an autoincrement column and a field from the period specification: id int auto_increment, unique(id, s), period for p(s, e) for this case, no exception is made, and the autoincrementing rules will be proceeded accordung to the standard (i.e. the value will be advanced on implicit inserts).
537 lines
20 KiB
Text
537 lines
20 KiB
Text
--source include/have_partition.inc
|
|
|
|
# Test both myisam and innodb
|
|
--source suite/period/engines.inc
|
|
|
|
let $default_engine= `select @@default_storage_engine`;
|
|
|
|
create or replace table t(id int, s date, e date,
|
|
period for p(s,e),
|
|
primary key(id, p without overlaps)
|
|
) partition by key (id);
|
|
|
|
--replace_result $default_engine DEFAULT_ENGINE
|
|
show create table t;
|
|
|
|
|
|
insert into t values (1, '2003-01-01', '2003-03-01'),
|
|
(1, '2003-05-01', '2003-07-01');
|
|
|
|
--error ER_DUP_ENTRY
|
|
insert into t values (1, '2003-02-01', '2003-04-01');
|
|
--error ER_DUP_ENTRY
|
|
insert into t values (1, '2003-04-01', '2003-06-01');
|
|
--error ER_DUP_ENTRY
|
|
insert into t values (1, '2003-05-15', '2003-06-15');
|
|
--error ER_DUP_ENTRY
|
|
insert into t values (1, '2003-04-01', '2003-08-01');
|
|
|
|
insert into t values (1, '2003-03-01', '2003-05-01');
|
|
|
|
--echo # expand/shrink period
|
|
update t set s= '2002-12-01' where s = '2003-01-01';
|
|
update t set s= '2003-01-01' where s = '2002-12-01';
|
|
|
|
update t set e= '2003-08-01' where s = '2003-05-01';
|
|
update t set e= '2003-07-01' where s = '2003-05-01';
|
|
|
|
--echo # move left/right
|
|
update t set s= '2002-12-15', e= '2003-01-15' where s = '2003-01-01';
|
|
update t set s= '2003-01-01', e= '2003-02-01' where s = '2002-12-15';
|
|
|
|
--echo # diminish/enlarge
|
|
update t set s= '2003-01-10', e= '2003-01-20' where s = '2003-01-01';
|
|
update t set s= '2003-01-01', e= '2003-02-01' where s = '2003-01-10';
|
|
|
|
select * from t;
|
|
|
|
--echo # intersect left/right, strict inclusion/containment
|
|
--error ER_DUP_ENTRY
|
|
update t set e= '2003-04-01' where s = '2003-01-01';
|
|
--error ER_DUP_ENTRY
|
|
update t set s= '2003-04-01' where s = '2003-05-01';
|
|
--error ER_DUP_ENTRY
|
|
update t set s= '2003-03-10', e= '2003-03-20' where s = '2003-01-01';
|
|
--error ER_DUP_ENTRY
|
|
update t set s= '2003-04-01', e= '2003-08-01' where s = '2003-03-01';
|
|
|
|
--echo # inclusion/containment with partial match
|
|
--error ER_DUP_ENTRY
|
|
update t set s= '2003-03-01', e= '2003-04-01' where s = '2003-01-01';
|
|
--error ER_DUP_ENTRY
|
|
update t set s= '2003-04-01', e= '2003-05-01' where s = '2003-01-01';
|
|
--error ER_DUP_ENTRY
|
|
update t set s= '2003-03-01' where s = '2003-05-01';
|
|
--error ER_DUP_ENTRY
|
|
update t set e= '2003-05-01' where s = '2003-01-01';
|
|
|
|
select * from t where year(s) = 2003;
|
|
|
|
--echo # UPDATE ... FOR PORTION test
|
|
insert t values (2, '2003-04-15', '2003-05-01');
|
|
|
|
update t for portion of p from '2003-01-01' to '2003-01-15'
|
|
set id= 2;
|
|
--sorted_result
|
|
select * from t;
|
|
|
|
update t for portion of p from '2003-01-15' to '2003-02-01'
|
|
set id= 2;
|
|
--sorted_result
|
|
select * from t;
|
|
|
|
--echo # Next, test UPDATE ... FOR PORTION resulting with an error
|
|
--echo # Since MyISAM/Aria engines lack atomicity, the results would differ with
|
|
--echo # innodb. So a table is going to be copied to one with explicit engine.
|
|
|
|
create table t_myisam (id int, s date, e date,
|
|
period for p(s,e),
|
|
primary key(id, p without overlaps))
|
|
engine=myisam
|
|
select * from t;
|
|
--error ER_DUP_ENTRY
|
|
update t_myisam for portion of p from '2003-04-01' to '2003-06-01'
|
|
set id= 2 order by s desc;
|
|
--sorted_result
|
|
select * from t_myisam;
|
|
|
|
create table t_innodb (id int, s date, e date,
|
|
period for p(s,e),
|
|
primary key(id, p without overlaps))
|
|
engine=innodb
|
|
select * from t;
|
|
--error ER_DUP_ENTRY
|
|
update t_innodb for portion of p from '2003-04-01' to '2003-06-01'
|
|
set id= 2 order by s desc;
|
|
--sorted_result
|
|
select * from t_innodb;
|
|
|
|
drop table t_myisam, t_innodb;
|
|
|
|
--error ER_PERIOD_NOT_FOUND
|
|
create or replace table t(id int, s date, e date,
|
|
period for p(s,e),
|
|
primary key(id, q without overlaps));
|
|
|
|
--error ER_PERIOD_NOT_FOUND
|
|
create or replace table t(id int, s date, e date,
|
|
primary key(id, p without overlaps));
|
|
|
|
--error ER_KEY_CONTAINS_PERIOD_FIELDS
|
|
create or replace table t(id int, s date, e date,
|
|
period for p(s,e),
|
|
primary key(id, s, p without overlaps));
|
|
|
|
create or replace table t(id int, s date, e date,
|
|
period for p(s,e),
|
|
primary key(id));
|
|
insert into t values (1, '2003-03-01', '2003-05-01');
|
|
--error ER_DUP_ENTRY
|
|
insert into t values (1, '2003-04-01', '2003-05-01');
|
|
|
|
create or replace table t(id int, u int, s date, e date,
|
|
period for p(s,e),
|
|
primary key(id, p without overlaps),
|
|
unique(u));
|
|
--replace_result $default_engine DEFAULT_ENGINE
|
|
show create table t;
|
|
insert into t values (1, 1, '2003-03-01', '2003-05-01');
|
|
insert into t values (1, 2, '2003-05-01', '2003-07-01');
|
|
--error ER_DUP_ENTRY
|
|
insert into t values (1, 3, '2003-04-01', '2003-05-01');
|
|
|
|
|
|
|
|
create or replace table t(id int, u int, s date, e date,
|
|
period for p(s,e),
|
|
primary key(id, p without overlaps),
|
|
unique(u, p without overlaps));
|
|
--replace_result $default_engine DEFAULT_ENGINE
|
|
show create table t;
|
|
|
|
insert into t values (2, NULL, '2003-03-01', '2003-05-01');
|
|
--error ER_DUP_ENTRY
|
|
insert into t values (2, NULL, '2003-03-01', '2003-05-01');
|
|
insert into t values (3, NULL, '2003-03-01', '2003-05-01');
|
|
insert into t values (1, 1, '2003-03-01', '2003-05-01');
|
|
insert into t values (1, 2, '2003-05-01', '2003-07-01');
|
|
insert into t values (4, NULL, '2003-03-01', '2003-05-01');
|
|
|
|
create sequence seq start=5 engine=myisam;
|
|
update t set id= nextval(seq), u= nextval(seq), s='2003-05-01', e='2003-07-01'
|
|
where u is NULL;
|
|
|
|
--sorted_result
|
|
select * from t;
|
|
drop sequence seq;
|
|
|
|
create or replace table t(id int, s date, e date,
|
|
period for p(s,e));
|
|
|
|
insert into t values (1, '2003-01-01', '2003-03-01'),
|
|
(1, '2003-05-01', '2003-07-01'),
|
|
(1, '2003-02-01', '2003-04-01');
|
|
|
|
--replace_regex /#sql-\w+/#sql-temp/
|
|
--error ER_DUP_ENTRY
|
|
alter table t add primary key(id, p without overlaps);
|
|
|
|
--echo # Historical rows are not checked against constraints
|
|
set @@system_versioning_alter_history= keep;
|
|
alter table t add system versioning;
|
|
delete from t;
|
|
alter table t add primary key(id, p without overlaps);
|
|
|
|
insert into t values (1, '2003-01-01', '2003-03-01'),
|
|
(1, '2003-03-01', '2003-05-01');
|
|
|
|
|
|
--echo # `without overlaps` is not lost on alter table
|
|
alter table t add y int;
|
|
--replace_result $default_engine DEFAULT_ENGINE
|
|
show create table t;
|
|
alter table t drop y;
|
|
|
|
create or replace table t1 like t;
|
|
--replace_result $default_engine DEFAULT_ENGINE
|
|
show create table t1;
|
|
|
|
create or replace table t1 (x int, s date, e date,
|
|
period for p(s,e),
|
|
primary key(x, p without overlaps));
|
|
alter table t1 partition by key (x);
|
|
|
|
create or replace table t1 (x int, s date, e date, period for p (s, e))
|
|
partition by hash (x);
|
|
alter table t1 add primary key (x, p without overlaps);
|
|
|
|
--error ER_PARSE_ERROR
|
|
create or replace table t2 (x int, s date, e date,
|
|
period for p (s, e),
|
|
key(x, p without overlaps));
|
|
|
|
--error ER_PARSE_ERROR
|
|
create or replace table t2 (x int, s date, e date,
|
|
period for p (s, e),
|
|
unique(x, p without overlaps, x, p without overlaps));
|
|
|
|
create or replace table t1 (x varchar(100), s date, e date,
|
|
period for p(s,e),
|
|
primary key(x, p without overlaps));
|
|
|
|
--error ER_COMPRESSED_COLUMN_USED_AS_KEY
|
|
create or replace table t1 (x varchar(100) compressed, s date, e date,
|
|
period for p(s,e),
|
|
primary key(x, p without overlaps));
|
|
|
|
--error ER_KEY_CANT_HAVE_WITHOUT_OVERLAPS
|
|
create or replace table t (x int, s date, e date, period for apptime(s,e),
|
|
unique(x, apptime without overlaps) using hash);
|
|
|
|
--error ER_KEY_CANT_HAVE_WITHOUT_OVERLAPS
|
|
create or replace table t (x int, s date, e date, period for apptime(s,e),
|
|
b blob, unique(x, b, apptime without overlaps));
|
|
|
|
create or replace table t (x int, s date, e date, b blob unique,
|
|
period for apptime(s,e),
|
|
unique(x, apptime without overlaps));
|
|
|
|
insert into t values (1, '2020-03-01', '2020-03-05', 'test');
|
|
--error ER_DUP_ENTRY
|
|
insert into t values (1, '2020-03-05', '2020-03-10', 'test');
|
|
insert into t values (1, '2020-03-05', '2020-03-10', 'test2');
|
|
--error ER_DUP_ENTRY
|
|
insert into t values (1, '2020-03-03', '2020-03-10', 'test3');
|
|
|
|
let $MYSQLD_DATADIR= `select @@datadir`;
|
|
create or replace table t (x int, s date, e date, period for apptime(s,e),
|
|
unique(x, apptime without overlaps));
|
|
--error ER_NOT_SUPPORTED_YET
|
|
replace into t values (1, '2020-03-03', '2020-03-10');
|
|
--error ER_NOT_SUPPORTED_YET
|
|
insert into t values (1, '2020-03-03', '2020-03-10')
|
|
on duplicate key update x = 2;
|
|
|
|
select * from t;
|
|
--disable_ps2_protocol
|
|
select * into outfile 'tmp_t.txt' from t;
|
|
--enable_ps2_protocol
|
|
load data infile 'tmp_t.txt' into table t;
|
|
--error ER_NOT_SUPPORTED_YET
|
|
load data infile 'tmp_t.txt' replace into table t;
|
|
remove_file $MYSQLD_DATADIR/test/tmp_t.txt;
|
|
|
|
insert into t values (1, '2020-03-01', '2020-03-05');
|
|
--disable_ps2_protocol
|
|
select * into outfile 'tmp_t.txt' from t;
|
|
--enable_ps2_protocol
|
|
--error ER_DUP_ENTRY
|
|
load data infile 'tmp_t.txt' into table t;
|
|
|
|
load data infile 'tmp_t.txt' ignore into table t;
|
|
|
|
--error ER_NOT_SUPPORTED_YET
|
|
load data infile 'tmp_t.txt' replace into table t;
|
|
|
|
remove_file $MYSQLD_DATADIR/test/tmp_t.txt;
|
|
|
|
|
|
--echo # MDEV-22599 WITHOUT OVERLAPS does not work with prefix indexes
|
|
create or replace table t1 (a varchar(6), s timestamp, e timestamp,
|
|
period for p(s,e),
|
|
unique(a(3), p without overlaps));
|
|
|
|
insert into t1 values ('foo', '2012-01-01', '2015-12-31');
|
|
--error ER_DUP_ENTRY
|
|
insert into t1 values ('foobar', '2013-01-01', '2014-01-01');
|
|
|
|
insert into t1 values ('bar', '2012-01-01', '2015-12-31'),
|
|
('baz', '2013-01-01', '2014-01-01');
|
|
select * from t1;
|
|
|
|
--echo # MDEV-22753 Server crashes in handler::ha_check_overlaps or error 190
|
|
--echo # "Incompatible key or row definition" upon INSERT into versioned
|
|
--echo # partitioned table with WITHOUT OVERLAPS
|
|
create or replace table t1 (f int, s date, e date, period for p(s,e),
|
|
unique(f, p without overlaps)
|
|
) engine=innodb with system versioning
|
|
partition by system_time limit 1000
|
|
(partition p1 history, partition pn current);
|
|
alter table t1 add partition (partition p2 history);
|
|
show create table t1;
|
|
insert into t1 values (1,'2013-01-12','2015-11-04'),
|
|
(2,'2016-03-15','2024-11-09');
|
|
|
|
|
|
--echo # MDEV-22714 Assertion `index->table->is_instant()' failed upon
|
|
--echo # multi-update on table with WITHOUT OVERLAPS
|
|
|
|
create or replace table t (a int);
|
|
insert into t values (0),(null),(0);
|
|
|
|
create or replace table t1 (f int, s date, e date, period for p(s,e),
|
|
unique(f, p without overlaps));
|
|
|
|
insert into t1 values (0,'2026-02-12','2036-09-16'),
|
|
(null,'2025-03-09','2032-12-05');
|
|
|
|
update ignore t join t1 set f = a;
|
|
|
|
--echo # MDEV-22639 Assertion `inited != NONE' failed in
|
|
--echo # handler::ha_check_overlaps upon multi-table update
|
|
|
|
create or replace table t (f int, s date, e date, period for p(s,e),
|
|
unique(f, p without overlaps)) engine=myisam;
|
|
insert into t values (1,'1988-08-25','2024-03-06');
|
|
create or replace table t1 (a int) engine=myisam;
|
|
insert into t1 values (1),(2);
|
|
|
|
update t join t1 set s = '2020-01-01';
|
|
|
|
|
|
--echo # MDEV-22608 ASAN use-after-poison in TABLE::check_period_overlaps
|
|
|
|
create or replace table t1 (s date, e date, b bit, period for p(s,e),
|
|
unique(b, p without overlaps)) engine=myisam;
|
|
insert into t1 values ('2024-12-21','2034-06-29',0),
|
|
('2024-12-21','2034-06-29',1);
|
|
--error ER_DUP_ENTRY
|
|
update t1 set b = 1;
|
|
|
|
|
|
--echo # MDEV-22677 Server crashes in ha_partition::open upon update on
|
|
--echo # partitioned HEAP table with WITHOUT OVERLAPS
|
|
|
|
create or replace table t (id int, s date, e date, period for p(s,e),
|
|
primary key(id, p without overlaps)
|
|
) engine=heap partition by hash(id);
|
|
update t set id = 1;
|
|
|
|
drop table t, t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-30415 PERIOD false positive overlap wtih utf8mb4_unicode_nopad_ci
|
|
--echo #
|
|
|
|
# The originally reported script with a TEXT column (slightly modified)
|
|
CREATE TABLE t1 (
|
|
datetime_column_name_1 DATETIME(6) NOT NULL,
|
|
datetime_column_name_2 DATETIME(6) NOT NULL,
|
|
text_column_name TEXT COLLATE utf8mb4_unicode_nopad_ci NOT NULL,
|
|
PERIOD FOR period_name (datetime_column_name_1, datetime_column_name_2),
|
|
UNIQUE KEY index_name (text_column_name(191),period_name WITHOUT OVERLAPS)
|
|
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_nopad_ci;
|
|
INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, text_column_name)
|
|
VALUES
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc'),
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc ');
|
|
TRUNCATE TABLE t1;
|
|
INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, text_column_name)
|
|
VALUES
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc '),
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc');
|
|
DROP TABLE t1;
|
|
|
|
|
|
# The script reported by Alice with a TEXT column
|
|
CREATE TABLE `t1` (
|
|
datetime_column_name_1 DATETIME(6) NOT NULL,
|
|
datetime_column_name_2 DATETIME(6) NOT NULL,
|
|
text_column_name TEXT COLLATE utf8mb4_unicode_nopad_ci NOT NULL,
|
|
PERIOD FOR period_name (datetime_column_name_1, datetime_column_name_2),
|
|
UNIQUE KEY index_name (text_column_name(191),period_name WITHOUT OVERLAPS)
|
|
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_nopad_ci;
|
|
INSERT INTO t1 VALUES
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'def '),
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'def');
|
|
TRUNCATE TABLE t1;
|
|
INSERT INTO t1 VALUES
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'def'),
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'def '),
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'def ');
|
|
--sorted_result
|
|
SELECT *, LENGTH(text_column_name) FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
# A TEXT column with a short prefix
|
|
CREATE TABLE t1 (
|
|
datetime_column_name_1 DATETIME(6) NOT NULL,
|
|
datetime_column_name_2 DATETIME(6) NOT NULL,
|
|
text_column_name TEXT COLLATE utf8mb4_unicode_nopad_ci NOT NULL,
|
|
PERIOD FOR period_name (datetime_column_name_1, datetime_column_name_2),
|
|
UNIQUE KEY index_name (text_column_name(3),period_name WITHOUT OVERLAPS)
|
|
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_nopad_ci;
|
|
--error ER_DUP_ENTRY
|
|
INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, text_column_name)
|
|
VALUES
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc'),
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc ');
|
|
TRUNCATE TABLE t1;
|
|
--error ER_DUP_ENTRY
|
|
INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, text_column_name)
|
|
VALUES
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc '),
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc');
|
|
DROP TABLE t1;
|
|
|
|
|
|
# A CHAR with a long prefix
|
|
CREATE TABLE t1 (
|
|
datetime_column_name_1 DATETIME(6) NOT NULL,
|
|
datetime_column_name_2 DATETIME(6) NOT NULL,
|
|
char_column_name CHAR(255) COLLATE utf8mb4_unicode_nopad_ci NOT NULL,
|
|
PERIOD FOR period_name (datetime_column_name_1, datetime_column_name_2),
|
|
UNIQUE KEY index_name (char_column_name(191),period_name WITHOUT OVERLAPS)
|
|
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_nopad_ci;
|
|
--error ER_DUP_ENTRY
|
|
INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, char_column_name)
|
|
VALUES
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc'),
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc ');
|
|
TRUNCATE TABLE t1;
|
|
--error ER_DUP_ENTRY
|
|
INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, char_column_name)
|
|
VALUES
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc '),
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc');
|
|
DROP TABLE t1;
|
|
|
|
|
|
# A CHAR column with a short prefix
|
|
CREATE TABLE t1 (
|
|
datetime_column_name_1 DATETIME(6) NOT NULL,
|
|
datetime_column_name_2 DATETIME(6) NOT NULL,
|
|
char_column_name CHAR(255) COLLATE utf8mb4_unicode_nopad_ci NOT NULL,
|
|
PERIOD FOR period_name (datetime_column_name_1, datetime_column_name_2),
|
|
UNIQUE KEY index_name (char_column_name(3),period_name WITHOUT OVERLAPS)
|
|
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_nopad_ci;
|
|
--error ER_DUP_ENTRY
|
|
INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, char_column_name)
|
|
VALUES
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc'),
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc ');
|
|
TRUNCATE TABLE t1;
|
|
--error ER_DUP_ENTRY
|
|
INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, char_column_name)
|
|
VALUES
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc '),
|
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc');
|
|
DROP TABLE t1;
|
|
|
|
--echo # MDEV-25370 Update for portion changes autoincrement key in period table
|
|
create or replace table cars(id int auto_increment,
|
|
price int, s date, e date,
|
|
period for p(s,e),
|
|
primary key(id, p without overlaps));
|
|
|
|
insert into cars(price, s, e) values (1000, '2018-01-01', '2020-01-01');
|
|
select * from cars;
|
|
|
|
update cars for portion of p from '2019-01-01' to '2019-12-01' set price= 1100;
|
|
--sorted_result
|
|
select * from cars;
|
|
|
|
delete from cars for portion of p from '2019-12-10' to '2019-12-20';
|
|
--sorted_result
|
|
select * from cars;
|
|
|
|
--echo # AUTO_INCREMENT field is separate from WITHOUT OVERLAPS
|
|
create or replace table cars(id int primary key auto_increment,
|
|
car_id int,
|
|
price int, s date, e date,
|
|
period for p(s,e),
|
|
unique(car_id, p without overlaps));
|
|
|
|
insert cars(car_id, price, s, e) values (1, 1000, '2018-01-01', '2020-01-01');
|
|
select * from cars;
|
|
|
|
update cars for portion of p from '2019-01-01' to '2019-12-01' set price= 1100;
|
|
--sorted_result
|
|
select * from cars;
|
|
|
|
delete from cars for portion of p from '2019-12-10' to '2019-12-20';
|
|
--sorted_result
|
|
select * from cars;
|
|
|
|
|
|
--echo # AUTO_INCREMENT field is both standalone and in WITHOUT OVERLAPS
|
|
create or replace table cars(id int primary key auto_increment,
|
|
price int, s date, e date,
|
|
period for p(s,e),
|
|
unique(id, p without overlaps));
|
|
|
|
insert cars(price, s, e) values (1000, '2018-01-01', '2020-01-01');
|
|
insert cars(price, s, e) values (1000, '2021-01-01', '2022-01-01');
|
|
|
|
update cars for portion of p from '2019-01-01' to '2019-12-01' set price= 1100;
|
|
--echo # autoincrement index is: id int primary key
|
|
--echo # id increments each time.
|
|
--sorted_result
|
|
select * from cars;
|
|
|
|
truncate cars;
|
|
insert cars(price, s, e) values (1000, '2018-01-01', '2020-01-01');
|
|
|
|
delete from cars for portion of p from '2019-12-10' to '2019-12-20';
|
|
--sorted_result
|
|
select * from cars;
|
|
|
|
create or replace table cars(id int unique auto_increment,
|
|
price int, s date, e date,
|
|
period for p(s,e),
|
|
primary key (id, p without overlaps));
|
|
|
|
insert cars(price, s, e) values (1000, '2018-01-01', '2020-01-01');
|
|
--echo # autoincrement index is: primary key (id, p without overlaps)
|
|
--echo # id is not incremented, hence duplication error
|
|
--error ER_DUP_ENTRY
|
|
update cars for portion of p from '2019-01-01' to '2019-12-01' set price= 1100;
|
|
|
|
truncate cars;
|
|
insert cars(price, s, e) values (1000, '2018-01-01', '2020-01-01');
|
|
|
|
--error ER_DUP_ENTRY
|
|
delete from cars for portion of p from '2019-12-10' to '2019-12-20';
|
|
|
|
drop table cars;
|