178 lines
6.0 KiB
Plaintext
178 lines
6.0 KiB
Plaintext
# TestDefaultValueIsBinaryString
|
|
drop table if exists t;
|
|
create table t (a char(10) charset gbk default 0xC4E3BAC3);
|
|
insert into t values (default);
|
|
select a from t;
|
|
drop table if exists t;
|
|
create table t (a char(10) charset gbk default '好');
|
|
insert into t values (default);
|
|
select a from t;
|
|
drop table if exists t;
|
|
create table t (a varchar(10) charset gbk default 0xC4E3BAC3);
|
|
insert into t values (default);
|
|
select a from t;
|
|
drop table if exists t;
|
|
create table t (a char(10) charset utf8mb4 default 0xE4BDA0E5A5BD);
|
|
insert into t values (default);
|
|
select a from t;
|
|
drop table if exists t;
|
|
create table t (a char(10) charset utf8mb4 default 0b111001001011100010010110111001111001010110001100);
|
|
insert into t values (default);
|
|
select a from t;
|
|
drop table if exists t;
|
|
create table t (a bit(48) default 0xE4BDA0E5A5BD);
|
|
insert into t values (default);
|
|
select a from t;
|
|
drop table if exists t;
|
|
create table t (a enum('你好') default 0xE4BDA0E5A5BD);
|
|
insert into t values (default);
|
|
select a from t;
|
|
drop table if exists t;
|
|
create table t (a set('你好') default 0xE4BDA0E5A5BD);
|
|
insert into t values (default);
|
|
select a from t;
|
|
drop table if exists t;
|
|
-- error 1067
|
|
create table t (a char(20) charset utf8mb4 default 0xE4BDA0E5A5BD81);
|
|
-- error 1101
|
|
create table t (a blob default 0xE4BDA0E5A5BD81);
|
|
|
|
# TestDefaultValueInEnum
|
|
# https://github.com/pingcap/tidb/issues/30740
|
|
drop table if exists t;
|
|
create table t(a enum('a', 0x91) charset gbk);
|
|
insert into t values (1), (2);
|
|
select a from t;
|
|
drop table t;
|
|
create table t (a enum('a', 0x91)) charset gbk;
|
|
insert into t values (1), (2);
|
|
select a from t;
|
|
drop table t;
|
|
-- error 1291
|
|
create table t(a set('a', 0x91, '?') charset gbk);
|
|
create table t (a enum('a', 0xE4BDA0E5A5BD) charset gbk);
|
|
insert into t values (1), (2);
|
|
select a from t;
|
|
|
|
# TestDDLOnCachedTable
|
|
drop table if exists t;
|
|
create table t (id int, c int, index(c));
|
|
alter table t cache;
|
|
-- error 8242
|
|
drop table t;
|
|
-- error 8242
|
|
create index t_id on t (id);
|
|
-- error 8242
|
|
alter table t drop index c;
|
|
-- error 8242
|
|
alter table t add column (d int);
|
|
-- error 8242
|
|
truncate table t;
|
|
-- error 8242
|
|
rename table t to t1;
|
|
alter table t nocache;
|
|
drop table if exists t;
|
|
|
|
# TestNonStrictCreateTableOverflowError
|
|
drop table if exists t;
|
|
-- error 1067
|
|
create table t (d int default '18446744073709551616' );
|
|
set sql_mode='';
|
|
-- error 1067
|
|
create table t (d int default '18446744073709551616' );
|
|
set sql_mode=DEFAULT;
|
|
|
|
# Test alter non-partition table to partition with global index needed.
|
|
drop table if exists t;
|
|
create table t(a int not null, b int, primary key(a), unique idx_b(b));
|
|
drop table if exists t2;
|
|
create table t2(a int not null, b int, primary key(a) nonclustered, unique idx_b(b));
|
|
drop table if exists t3;
|
|
create table t3(a int not null, b int, primary key(a) nonclustered, unique idx_b(b) global) partition by hash(a) partitions 3;
|
|
drop table if exists t4;
|
|
create table t4(a int not null, b int, primary key(a)) partition by hash(a) partitions 3;
|
|
alter table t partition by hash(a) partitions 3 update indexes (idx_b global);
|
|
alter table t remove partitioning;
|
|
alter table t partition by key() partitions 3 update indexes (idx_b global);
|
|
alter table t remove partitioning;
|
|
-- error 1503
|
|
alter table t partition by hash(b) partitions 3 update indexes (`primary` global);
|
|
alter table t2 partition by hash(b) partitions 3 update indexes (`primary` global);
|
|
alter table t2 remove partitioning;
|
|
alter table t3 partition by key(a) partitions 3;
|
|
alter table t3 remove partitioning;
|
|
-- error 1503
|
|
alter table t4 partition by hash(b) partitions 3 update indexes(`primary` global, idx_b local);
|
|
-- error 8264
|
|
alter table t partition by hash(a) partitions 3;
|
|
-- error 8264
|
|
alter table t partition by key() partitions 3;
|
|
-- error 1503
|
|
alter table t partition by hash(b) partitions 3;
|
|
-- error 8264
|
|
alter table t2 partition by hash(b) partitions 3;
|
|
-- error 8264
|
|
alter table t3 partition by key(a) partitions 3;
|
|
-- error 1503
|
|
alter table t4 partition by hash(b) partitions 3;
|
|
drop table t, t2, t3, t4;
|
|
|
|
# Issue found during code review
|
|
CREATE TABLE `members` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`fname` varchar(255) DEFAULT NULL,
|
|
`lname` varchar(255) DEFAULT NULL,
|
|
`dob` date DEFAULT NULL,
|
|
`data` json DEFAULT NULL,
|
|
UNIQUE KEY `ui` (`id`) GLOBAL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
|
|
PARTITION BY RANGE (YEAR(`dob`))
|
|
(PARTITION `pBefore1950` VALUES LESS THAN (1950),
|
|
PARTITION `p1950` VALUES LESS THAN (1960),
|
|
PARTITION `p1960` VALUES LESS THAN (1970),
|
|
PARTITION `p1970` VALUES LESS THAN (1980),
|
|
PARTITION `p1980` VALUES LESS THAN (1990),
|
|
PARTITION `p1990` VALUES LESS THAN (2000));
|
|
ALTER TABLE members REORGANIZE PARTITION `p1990` INTO (PARTITION p1995 VALUES LESS THAN (1995), PARTITION p2000 VALUES LESS THAN (2010));
|
|
|
|
# TestAvoidDropModifyChangeColumnReferencedByPartialCondition
|
|
create table t (a int, b int, key testidx(b) where a > 2);
|
|
insert into t values (1, 2);
|
|
--error 8272
|
|
alter table t modify column a bigint;
|
|
--error 8272
|
|
alter table t drop column a;
|
|
--error 8272
|
|
alter table t change column a c int;
|
|
alter table t drop index testidx;
|
|
alter table t modify column b bigint;
|
|
alter table t drop column b;
|
|
drop table t;
|
|
|
|
# TestRowIDIsNotAllowedForPartialIndex
|
|
drop table if exists t;
|
|
--error 8200
|
|
create table t (a int, b int, key testidx(b) where _tidb_rowid > 2);
|
|
create table t (a int, b int);
|
|
--error 8200
|
|
alter table t add index testidx(b) where _tidb_rowid > 2;
|
|
|
|
# TestPartitionTableNotSupportedForPartialIndex
|
|
drop table if exists t;
|
|
--error 8200
|
|
create table t (a int, b int, key testidx(b) where a > 5) partition by range (b) (partition p0 values less than (5));
|
|
create table t (a int, b int) partition by range (b) (partition p0 values less than (5));
|
|
--error 8200
|
|
create index testidx on t(b) where a > 5;
|
|
drop table t;
|
|
create table t (a int, b int, key testidx(b) where a > 5);
|
|
--error 8200
|
|
alter table t partition by range (b) (partition p0 values less than (5));
|
|
|
|
# TestUpdateAfterAddColumnForPartialIndex
|
|
drop table if exists t;
|
|
create table t (col2 int, key (col2) where col2 > 0);
|
|
alter table t add column col1 int first;
|
|
insert into t values (1, 1);
|
|
update t set col1 = 5;
|