1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
CREATE TABLE t1 (
id INT NOT NULL,
store_id INT NOT NULL,
x INT GENERATED ALWAYS AS (id + store_id)
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
);
INSERT t1 (id, store_id) VALUES(1, 2), (3, 4), (3, 12), (4, 18);
CREATE INDEX idx ON t1(x);
SELECT x FROM t1;
x
3
7
15
22
DROP TABLE t1;
create table t1 (i int, v int as (i) virtual)
partition by range columns (i)
subpartition by hash(v) subpartitions 3 (
partition p1 values less than (3),
partition pn values less than (maxvalue));
insert t1 set i= 0;
set statement sql_mode= '' for update t1 set i= 1, v= 2;
Warnings:
Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored
drop table t1;
#
# MDEV-18734 ASAN heap-use-after-free in my_strnxfrm_simple_internal upon update on versioned partitioned table
#
# Cover queue_fix() in ha_partition::handle_ordered_index_scan()
create or replace table t1 (
x int auto_increment primary key,
b text, v mediumtext as (b) virtual,
index (v(10))
) partition by range columns (x) (
partition p1 values less than (3),
partition p2 values less than (6),
partition p3 values less than (9),
partition p4 values less than (12),
partition p5 values less than (15),
partition p6 values less than (17),
partition p7 values less than (19),
partition p8 values less than (21),
partition p9 values less than (23),
partition p10 values less than (25),
partition p11 values less than (27),
partition p12 values less than (29),
partition p13 values less than (31),
partition p14 values less than (33),
partition p15 values less than (35),
partition pn values less than (maxvalue));
insert into t1 (b) values
(repeat('q', 8192)), (repeat('z', 8192)), (repeat('a', 8192)), (repeat('b', 8192)),
(repeat('x', 8192)), (repeat('y', 8192));
insert t1 (b) select b from t1;
insert t1 (b) select b from t1;
insert t1 (b) select b from t1;
insert t1 (b) select b from t1;
select x, left(b, 10), left(v, 10) from t1 where x > 30 and x < 60 order by v;
x left(b, 10) left(v, 10)
33 aaaaaaaaaa aaaaaaaaaa
39 aaaaaaaaaa aaaaaaaaaa
45 aaaaaaaaaa aaaaaaaaaa
51 aaaaaaaaaa aaaaaaaaaa
57 aaaaaaaaaa aaaaaaaaaa
34 bbbbbbbbbb bbbbbbbbbb
40 bbbbbbbbbb bbbbbbbbbb
46 bbbbbbbbbb bbbbbbbbbb
52 bbbbbbbbbb bbbbbbbbbb
58 bbbbbbbbbb bbbbbbbbbb
31 qqqqqqqqqq qqqqqqqqqq
37 qqqqqqqqqq qqqqqqqqqq
43 qqqqqqqqqq qqqqqqqqqq
49 qqqqqqqqqq qqqqqqqqqq
55 qqqqqqqqqq qqqqqqqqqq
35 xxxxxxxxxx xxxxxxxxxx
41 xxxxxxxxxx xxxxxxxxxx
47 xxxxxxxxxx xxxxxxxxxx
53 xxxxxxxxxx xxxxxxxxxx
59 xxxxxxxxxx xxxxxxxxxx
36 yyyyyyyyyy yyyyyyyyyy
42 yyyyyyyyyy yyyyyyyyyy
48 yyyyyyyyyy yyyyyyyyyy
54 yyyyyyyyyy yyyyyyyyyy
32 zzzzzzzzzz zzzzzzzzzz
38 zzzzzzzzzz zzzzzzzzzz
44 zzzzzzzzzz zzzzzzzzzz
50 zzzzzzzzzz zzzzzzzzzz
56 zzzzzzzzzz zzzzzzzzzz
update t1 set b= 'bar' where v > 'a' limit 20;
drop table t1;
# Cover return_top_record() in ha_partition::handle_ordered_index_scan()
create table t1 (x int primary key, b tinytext, v text as (b) virtual)
partition by range columns (x) (
partition p1 values less than (4),
partition pn values less than (maxvalue));
insert into t1 (x, b) values (1, ''), (2, ''), (3, 'a'), (4, 'b');
update t1 set b= 'bar' where x > 0 order by v limit 2;
drop table t1;
#
# MDEV-28127 EXCHANGE PARTITION with non-matching vcol expression segfault
#
set @old_mode= @@sql_mode;
set sql_mode='';
create table t1 (a int, key(a)) partition by range (a) (partition p values less than (1));
create table t (a int generated always as (1) virtual, key(a));
alter table t1 exchange partition p with table t;
ERROR HY000: Tables have different definitions
create or replace table t (a int, key(a));
alter table t1 exchange partition p with table t;
create or replace table t1 (a int generated always as (1) virtual, key(a)) partition by range (a) (partition p values less than (1));
create or replace table t (a int generated always as (1) virtual, key(a));
alter table t1 exchange partition p with table t;
create or replace table t (a int generated always as (1) stored, key(a));
alter table t1 exchange partition p with table t;
ERROR HY000: Tables have different definitions
insert into t values (1);
Warnings:
Warning 1906 The value specified for generated column 'a' in table 't' has been ignored
drop tables t1, t;
set sql_mode= @old_mode;
|
