Debian Codegraph @

mysql-test/suite/maria/maria2.test

raw
1 --source include/have_maria.inc
2
3 # Test for BUG#36319
4 # "Aria: table is not empty but DELETE and SELECT find no rows"
5
6 CREATE TABLE t1 (
7 line BLOB,
8 kind ENUM('po', 'pp', 'rr', 'dr', 'rd', 'ts', 'cl') NOT NULL DEFAULT 'po',
9 name VARCHAR(32)
10 ) transactional=0 row_format=page engine=aria;
11
12 let $query= INSERT INTO t1 (name, kind, line) VALUES
13 ("Aadaouane", "pp", GeomFromText("POINT(32.816667 35.983333)")),
14 ("Aadassiye", "pp", GeomFromText("POINT(35.816667 36.216667)")),
15 ("Aadbel", "pp", GeomFromText("POINT(34.533333 36.100000)")),
16 ("Aadchit", "pp", GeomFromText("POINT(33.347222 35.423611)")),
17 ("Aadchite", "pp", GeomFromText("POINT(33.347222 35.423611)")),
18 ("Aadchit el Qoussair", "pp", GeomFromText("POINT(33.283333 35.483333)")),
19 ("Aaddaye", "pp", GeomFromText("POINT(36.716667 40.833333)")),
20 ("'Aadeissa", "pp", GeomFromText("POINT(32.823889 35.698889)")),
21 ("Aaderup", "pp", GeomFromText("POINT(55.216667 11.766667)")),
22 ("Qalaat Aades", "pp", GeomFromText("POINT(33.503333 35.377500)")),
23 ("A ad'ino", "pp", GeomFromText("POINT(54.812222 38.209167)")),
24 ("Aadi Noia", "pp", GeomFromText("POINT(13.800000 39.833333)")),
25 ("Aad La Macta", "pp", GeomFromText("POINT(35.779444 -0.129167)")),
26 ("Aadland", "pp", GeomFromText("POINT(60.366667 5.483333)")),
27 ("Aadliye", "pp", GeomFromText("POINT(33.366667 36.333333)")),
28 ("Aadloun", "pp", GeomFromText("POINT(33.403889 35.273889)")),
29 ("Aadma", "pp", GeomFromText("POINT(58.798333 22.663889)")),
30 ("Aadma Asundus", "pp", GeomFromText("POINT(58.798333 22.663889)")),
31 ("Aadmoun", "pp", GeomFromText("POINT(34.150000 35.650000)")),
32 ("Aadneram", "pp", GeomFromText("POINT(59.016667 6.933333)")),
33 ("Aadneskaar", "pp", GeomFromText("POINT(58.083333 6.983333)")),
34 ("Aadorf", "pp", GeomFromText("POINT(47.483333 8.900000)")),
35 ("Aadorp", "pp", GeomFromText("POINT(52.366667 6.633333)")),
36 ("Aadouane", "pp", GeomFromText("POINT(32.816667 35.983333)")),
37 ("Aadoui", "pp", GeomFromText("POINT(34.450000 35.983333)")),
38 ("Aadouiye", "pp", GeomFromText("POINT(34.583333 36.183333)")),
39 ("Aadouss", "pp", GeomFromText("POINT(33.512500 35.601389)")),
40 ("Aadra", "pp", GeomFromText("POINT(33.616667 36.500000)")),
41 ("Aadzi", "pp", GeomFromText("POINT(38.100000 64.850000)"));
42
43 --disable_query_log
44 let $1=90;
45 while($1)
46 {
47 eval $query;
48 dec $1;
49 }
50 let $1=90;
51 while($1)
52 {
53 delete from t1 limit 1;
54 delete from t1 limit 10;
55 delete from t1 limit 7;
56 delete from t1 limit 2;
57 dec $1;
58 }
59 --enable_query_log
60
61 select count(*) from t1;
62 delete from t1 limit 1000;
63 select count(*) from t1;
64 select name from t1;
65 check table t1 extended;
66 drop table t1;
67
68 #
69 # Testing of ALTER TABLE under lock tables
70 #
71
72 create table t1 (i int) engine=aria;
73 create table t2 (j int) engine=aria;
74 lock table t1 write, t2 read;
75 alter table t1 modify i int default 1;
76 insert into t1 values (2);
77 # This caused a core dump
78 alter table t1 modify i bigint default 1;
79 select count(*) from t1;
80 select * from t1;
81 unlock tables;
82 drop table t1,t2;
83
84 #
85 # test INSERT ON DUPLICATE KEY UPDATE
86 #
87
88 create table t1(id int, s char(1), unique(s)) engine=aria;
89 insert into t1 values(1,"a") on duplicate key update t1.id=t1.id+1;
90 insert into t1 values(1,"a") on duplicate key update t1.id=t1.id+1;
91 insert into t1 select 1,"a" on duplicate key update t1.id=t1.id+1;
92 select * from t1;
93
94 # test REPLACE SELECT
95 replace into t1 select 1,"a";
96 select * from t1;
97 drop table t1;
98
99 # test LOAD DATA INFILE REPLACE
100 create table t1 (pk int primary key, apk int unique, data int) engine=aria;
101 insert into t1 values (1, 1, 1), (4, 4, 4), (6, 6, 6);
102 load data concurrent infile '../../std_data/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk);
103 select * from t1 order by pk;
104 load data infile '../../std_data/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk);
105 select * from t1 order by pk;
106 drop table t1;
107
108 --echo #
109 --echo # End of 5.5 tests
110 --echo #
111
112 --echo #
113 --echo # MDEV-27303 Table corruption after insert into a non-InnoDB table with DESC index
114 --echo #
115 create table t1 (
116 a bigint default 0,
117 b bigint default 0,
118 c binary(128) not null,
119 d datetime default '0000-00-00 00:00:00',
120 key (c desc,b,d,a)
121 ) engine=aria;
122 insert into t1 (c) values
123 ('xx'),('bb'),('tt'),('pp'),('mm'),('yy'),('rr'),('bb'),('yy'),('gg'),
124 ('dd'),('fx'),('wi'),('ix'),('ox'),('mu'),('ux'),('pm'),('mx'),('xu'),
125 ('ul'),('lp'),('px'),('lp'),('xx'),('pq'),('qs'),('se'),('ee'),('xx'),
126 ('rv'),('ff'),('vj'),('jy'),('yn'),('nc'),('nx'),('hj'),('ji'),('ik'),
127 ('kk'),('ww'),('xx'),('yd'),('dw'),('wk'),('kr'),('dd'),('rj'),('jf'),
128 ('bx'),('fc'),('cp'),('pm'),('mw'),('wy'),('yl'),('li'),('ic'),('he'),
129 ('ci'),('il'),('lz'),('zd'),('gz'),('xd'),('ze'),('dm'),('ms'),('xd'),
130 ('sw'),('we'),('nb'),('tx'),('vr'),('xw'),('aa'),('ah'),('hd'),('jl'),
131 ('lf'),('fw'),('wx'),('xh'),('hr'),('zx'),('vw'),('rm'),('mx'),('xt'),
132 ('tp'),('ps'),('sh'),('ga'),('df'),('as'),('gz'),('xd'),('yy'),('xr');
133 check table t1 extended;
134 drop table t1;
135
136 --echo #
137 --echo # MDEV-27309 Server crash or ASAN memcpy-param-overlap upon INSERT into Aria/MyISAM table with DESC key
138 --echo #
139 CREATE TABLE t1 (id INT, c BINARY(80), PRIMARY KEY(id));
140 ALTER TABLE t1 ADD KEY(c DESC, id);
141 INSERT INTO t1 VALUES (1,NULL),(2,''),(3,'');
142 DROP TABLE t1;
143
144 --echo #
145 --echo # MDEV-27330 Wrong sorting order with DESC index and empty strings in MyISAM/Aria table
146 --echo #
147 create table t (id int, c char(128) not null, key (c desc)) engine=aria;
148 insert into t values (1,''),(2,'foo'),(3,''),(4,'bar');
149 select c from t order by c;
150 drop table t;
151
152 --echo #
153 --echo # MDEV-27340 NULL gets lost (becomes empty string), SELECT hangs with DESC index on MyISAM/Aria table
154 --echo #
155 create table t (c char(8), key(c desc)) engine=aria character set utf8mb4;
156 insert into t values (''),('foo'),(null),(''),('bar');
157 check table t;
158 check table t extended;
159 select distinct c from t;
160 select c from t;
161 drop table t;
162
163 --echo #
164 --echo # MDEV-27434 DESC attribute does not work with auto-increment on secondary column of multi-part index
165 --echo #
166 create table t (a int auto_increment, b int, unique(b,a desc)) engine=aria;
167 insert ignore into t (b) values (10),(10),(10);
168 select * from t;
169 drop table t;
170
171 --echo #
172 --echo # MDEV-27585 Auto-increment on secondary column increments unexpectedly with DESC key
173 --echo #
174 create table t (c char(16), i int auto_increment, index (c,i desc)) engine=aria collate latin1_swedish_ci;
175 insert into t (c) values ('รค'),('a');
176 select hex(c),c,i from t order by c, i;
177 drop table t;
178
179 --echo #
180 --echo # End of 10.8 tests
181 --echo #