ndb_basic.test 8.17 KB
Newer Older
1 2 3
-- source include/have_ndb.inc

--disable_warnings
unknown's avatar
unknown committed
4
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7;
5
drop database if exists mysqltest;
6 7 8 9 10 11 12 13 14 15 16 17
--enable_warnings

#
# Basic test to show that the NDB 
# table handler is working
#

#
# Create a normal table with primary key
#
CREATE TABLE t1 (
  pk1 INT NOT NULL PRIMARY KEY,
18 19 20
  attr1 INT NOT NULL,
  attr2 INT,
  attr3 VARCHAR(10)
21 22
) ENGINE=ndbcluster;

23
INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9411,9413, 17, '9413');
24
  
25 26 27
SELECT pk1 FROM t1 ORDER BY pk1;
SELECT * FROM t1 ORDER BY pk1;
SELECT t1.* FROM t1 ORDER BY pk1;
28

unknown's avatar
unknown committed
29
# Update on record by primary key
30
UPDATE t1 SET attr1=1 WHERE pk1=9410;
31
SELECT * FROM t1 ORDER BY pk1;
32

33
# Update primary key
34
UPDATE t1 SET pk1=2 WHERE attr1=1;
35
SELECT * FROM t1 ORDER BY pk1;
36
UPDATE t1 SET pk1=pk1 + 1;
37
SELECT * FROM t1 ORDER BY pk1;
38 39 40 41 42

# Delete the record
DELETE FROM t1;
SELECT * FROM t1;

unknown's avatar
unknown committed
43
# Insert more records and update them all at once
44 45
INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9408, 8765, NULL, '8765'),
(7,8, NULL, NULL), (8,9, NULL, NULL), (9,10, NULL, NULL), (10,11, NULL, NULL), (11,12, NULL, NULL), (12,13, NULL, NULL), (13,14, NULL, NULL);
unknown's avatar
unknown committed
46 47 48 49 50 51 52 53 54 55
UPDATE t1 SET attr1 = 9999;
SELECT * FROM t1 ORDER BY pk1;

UPDATE t1 SET attr1 = 9998 WHERE pk1 < 1000;
SELECT * FROM t1 ORDER BY pk1;

UPDATE t1 SET attr1 = 9997 WHERE attr1 = 9999;
SELECT * FROM t1 ORDER BY pk1;

# Delete one record by specifying pk
56
DELETE FROM t1 WHERE pk1 = 9410;
unknown's avatar
unknown committed
57
SELECT * FROM t1 ORDER BY pk1;
58

unknown's avatar
unknown committed
59
# Delete all from table
60 61 62 63 64
DELETE FROM t1;
SELECT * FROM t1;

# Insert three records with attr1=4 and two with attr1=5
# Delete all with attr1=4
65
INSERT INTO t1 values (1, 4, NULL, NULL), (2, 4, NULL, NULL), (3, 5, NULL, NULL), (4, 4, NULL, NULL), (5, 5, NULL, NULL);
66 67 68 69 70
DELETE FROM t1 WHERE attr1=4;
SELECT * FROM t1 order by pk1;
DELETE FROM t1;

# Insert two records and delete one
71
INSERT INTO t1 VALUES (9410,9412, NULL, NULL), (9411, 9413, NULL, NULL);
72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
DELETE FROM t1 WHERE pk1 = 9410;
SELECT * FROM t1;
DROP TABLE t1;

#
# Create table without primary key
# a hidden primary key column is created by handler
#
CREATE TABLE t1 (id INT, id2 int) engine=ndbcluster;
INSERT INTO t1 values(3456, 7890);
SELECT * FROM t1;
UPDATE t1 SET id=2 WHERE id2=12;
SELECT * FROM t1;
UPDATE t1 SET id=1234 WHERE id2=7890;
SELECT * FROM t1;
DELETE FROM t1;

89 90
INSERT INTO t1 values(3456, 7890), (3456, 7890), (3456, 7890), (3454, 7890);
SELECT * FROM t1 ORDER BY id;
91
DELETE FROM t1 WHERE id = 3456;
92
SELECT * FROM t1 ORDER BY id;
93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114

DROP TABLE t1;

# test create with the keyword "engine=NDBCLUSTER"
CREATE TABLE t1 (
  pk1 INT NOT NULL PRIMARY KEY,
  attr1 INT NOT NULL
) ENGINE=NDBCLUSTER;

INSERT INTO t1 values(1, 9999);

DROP TABLE t1;

# test create with the keyword "engine=NDB"
CREATE TABLE t1 (
  pk1 INT NOT NULL PRIMARY KEY,
  attr1 INT NOT NULL
) ENGINE=NDB;

INSERT INTO t1 values(1, 9999);

DROP TABLE t1;
unknown's avatar
unknown committed
115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223


#
# A more extensive test with a lot more records
#

CREATE TABLE t2 (
  a bigint unsigned NOT NULL PRIMARY KEY,
  b int unsigned not null,
  c int unsigned
) engine=ndbcluster;

CREATE TABLE t3 (
  a bigint unsigned NOT NULL,
  b bigint unsigned not null,
  c bigint unsigned,
  PRIMARY KEY(a)
) engine=ndbcluster;

CREATE TABLE t4 (
  a bigint unsigned NOT NULL,
  b bigint unsigned not null,
  c bigint unsigned NOT NULL,
  d int unsigned,
  PRIMARY KEY(a, b, c)
) engine=ndbcluster;


#
# insert more records into tables
#
let $1=1000;
disable_query_log;
while ($1)
{
 eval insert into t2 values($1, $1+9, 5);
 eval insert into t3 values($1, $1+9, 5);
 eval insert into t4 values($1, $1+9, 5, $1+26000);
 dec $1;
}
enable_query_log;


#
# delete every other record in the tables
#
let $1=1000;
disable_query_log;
while ($1)
{
 eval delete from t2 where a=$1;
 eval delete from t3 where a=$1;
 eval delete from t4 where a=$1 and b=$1+9 and c=5;
 dec $1;
 dec $1;
}
enable_query_log;


select * from t2 where a = 7 order by b;
select * from t2 where a = 7 order by a;
select * from t2 where a = 7 order by 2;
select * from t2 where a = 7 order by c;

select * from t2 where a = 7 and b = 16 order by b;
select * from t2 where a = 7 and b = 16 order by a;
select * from t2 where a = 7 and b = 17 order by a;
select * from t2 where a = 7 and b != 16 order by b;

select * from t2 where a = 7 and b = 16 and c = 5 order by b;
select * from t2 where a = 7 and b = 16 and c = 5 order by a;
select * from t2 where a = 7 and b = 16 and c = 6 order by a;
select * from t2 where a = 7 and b != 16 and c = 5 order by b;

select * from t3 where a = 7 order by b;
select * from t3 where a = 7 order by a;
select * from t3 where a = 7 order by 2;
select * from t3 where a = 7 order by c;

select * from t3 where a = 7 and b = 16 order by b;
select * from t3 where a = 7 and b = 16 order by a;
select * from t3 where a = 7 and b = 17 order by a;
select * from t3 where a = 7 and b != 16 order by b;

select * from t4 where a = 7 order by b;
select * from t4 where a = 7 order by a;
select * from t4 where a = 7 order by 2;
select * from t4 where a = 7 order by c;

select * from t4 where a = 7 and b = 16 order by b;
select * from t4 where a = 7 and b = 16 order by a;
select * from t4 where a = 7 and b = 17 order by a;
select * from t4 where a = 7 and b != 16 order by b;

#
# update records
#
let $1=1000;
disable_query_log;
while ($1)
{
 eval update t2 set c=$1 where a=$1;
 eval update t3 set c=7 where a=$1 and b=$1+9 and c=5;
 eval update t4 set d=$1+21987 where a=$1 and b=$1+9 and c=5;
 dec $1;
 dec $1;
}
enable_query_log;

unknown's avatar
unknown committed
224 225 226 227 228 229
delete from t2 where a > 5;
select x1.a, x1.b from t2 x1, t2 x2 where x1.b = x2.b order by x1.a;
select a, b FROM t2 outer_table where
a = (select a from t2 where b = outer_table.b ) order by a;


unknown's avatar
unknown committed
230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312
delete from t2;
delete from t3;
delete from t4;

drop table t2;
drop table t3;
drop table t4;

#
# Test delete and update from table with 3 keys
#

CREATE TABLE t5 (
  a bigint unsigned NOT NULL,
  b bigint unsigned not null,
  c bigint unsigned NOT NULL,
  d int unsigned,
  PRIMARY KEY(a, b, c)
) engine=ndbcluster;

insert into t5 values(10, 19, 5, 26010);

delete from t5 where a=10 and b=19 and c=5;

select * from t5;

insert into t5 values(10, 19, 5, 26010);

update t5 set d=21997 where a=10 and b=19 and c=5;

select * from t5;

delete from t5;

drop table t5;

#
# Test using table with a char(255) column first in table
#

CREATE TABLE t6 (
  adress char(255),
  a int NOT NULL PRIMARY KEY,
  b int
) engine = NDB;

insert into t6 values
 ("Nice road 3456", 1, 23),
 ("Street Road 78", 3, 92),
 ("Road street 89C", 5, 71),
 (NULL, 7, NULL);
select * from t6 order by a;
select a, b from t6 order by a;

update t6 set adress="End of road 09" where a=3;
update t6 set b=181, adress="Street 76" where a=7;
select * from t6 order by a;
select * from t6 where a=1;
delete from t6 where a=1;
select * from t6 order by a;
delete from t6 where b=71;
select * from t6 order by a;

drop table t6;

#
# Test using table with a char(255) column first in table and a 
# primary key consisting of two columns
#

CREATE TABLE t7 (
  adress char(255),
  a int NOT NULL,
  b int,
  c int NOT NULL,
  PRIMARY KEY(a, c)	
) engine = NDB;

insert into t7 values
 ("Highway 3456", 1, 23, 2),
 ("Street Road 78", 3, 92, 3),
 ("Main street 89C", 5, 71, 4),
 (NULL, 8, NULL, 12);
313 314
select * from t7 order by a;
select a, b from t7 order by a;
unknown's avatar
unknown committed
315 316 317 318

update t7 set adress="End of road 09" where a=3;
update t7 set adress="Gatuvägen 90C" where a=5 and c=4;
update t7 set adress="No adress" where adress is NULL;
319
select * from t7 order by a;
unknown's avatar
unknown committed
320 321 322 323 324 325 326 327 328
select * from t7 where a=1 and c=2;
delete from t7 where a=1;
delete from t7 where a=3 and c=3;
delete from t7 where a=5 and c=4;
select * from t7;
delete from t7 where b=23;
select * from t7;

drop table t7;
329 330 331 332 333 334 335 336 337 338 339 340 341 342

#
# Test multiple databases in one statement
#

CREATE TABLE t1 (
  pk1 INT NOT NULL PRIMARY KEY,
  attr1 INT NOT NULL,
  attr2 INT,
  attr3 VARCHAR(10)
) ENGINE=ndbcluster;

INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9411,9413, 17, '9413');

343 344
create database mysqltest;
use mysqltest;
345 346 347 348 349 350 351 352 353 354 355 356 357

CREATE TABLE t2 (
  a bigint unsigned NOT NULL PRIMARY KEY,
  b int unsigned not null,
  c int unsigned
) engine=ndbcluster;

insert into t2 select pk1,attr1,attr2 from test.t1;
select * from t2 order by a;
select b from test.t1, t2 where c = test.t1.attr2;
select b,test.t1.attr1 from test.t1, t2 where test.t1.pk1 < a;
 
drop table test.t1, t2;
358
drop database mysqltest;
359