############################################################## # Author: Nikolay # Date: 2006-04-01 # Purpose: Specific Blob and Varchar testing using disk tables. ############################################################## # Create Stored procedures that use disk based tables. # Create function that operate on disk based tables. # Create triggers that operate on disk based tables. # Create views that operate on disk based tables. # Try to create FK constraints on disk based tables. # Create and use disk based table that use auto inc. # Create test that use transaction (commit, rollback) # Create large disk base table, do random queries, check cache hits, do same # query 10 times check cache hits. # Create test that uses COUNT(), SUM(), MAX(), MIN(), NOW(), USER(), ... other built in # SQL functions # Create test that uses locks. # Create test using truncate. ############################################################## -- source include/have_ndb.inc --disable_warnings DROP TABLE IF EXISTS test.t1; DROP TABLE IF EXISTS test.t2; DROP TABLE IF EXISTS test.t3; --enable_warnings #### Copy data from table in one table space to table in different table space. #### --echo ***** --echo **** Copy data from table in one table space to table in different table space --echo ***** CREATE LOGFILE GROUP lg ADD UNDOFILE './lg_group/undofile.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=NDB; CREATE TABLESPACE ts1 ADD DATAFILE './table_space1/datafile.dat' USE LOGFILE GROUP lg INITIAL_SIZE 12M ENGINE NDB; CREATE TABLESPACE ts2 ADD DATAFILE './table_space2/datafile.dat' USE LOGFILE GROUP lg INITIAL_SIZE 12M ENGINE NDB; CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; CREATE TABLE test.t2 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB) TABLESPACE ts2 STORAGE DISK ENGINE=NDB; SHOW CREATE TABLE test.t1; SHOW CREATE TABLE test.t2; INSERT INTO test.t1 VALUES (1,'111111','aaaaaaaa'); INSERT INTO test.t1 VALUES (2,'222222','bbbbbbbb'); SELECT * FROM test.t1 ORDER BY a1; INSERT INTO test.t2(a1,a2,a3) SELECT * FROM test.t1; SELECT * FROM test.t2 ORDER BY a1; DROP TABLE test.t1, test.t2; # populate BLOB field with large data set @vc1 = repeat('a', 200); set @vc2 = repeat('b', 500); set @vc3 = repeat('c', 1000); set @vc4 = repeat('d', 4000); # x0 size 256 set @x0 = '01234567012345670123456701234567'; set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0); # b1 length 2000+256 set @b1 = 'b1'; set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); set @b1 = concat(@b1,@x0); # d1 length 3000 set @d1 = 'dd1'; set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); # b2 length 20000 set @b2 = 'b2'; set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); # d2 length 30000 set @d2 = 'dd2'; set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); select length(@x0),length(@b1),length(@d1) from dual; select length(@x0),length(@b2),length(@d2) from dual; CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(5000), a3 BLOB) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; CREATE TABLE test.t2 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(5000), a3 BLOB) TABLESPACE ts2 STORAGE DISK ENGINE=NDB; SHOW CREATE TABLE test.t1; SHOW CREATE TABLE test.t2; INSERT INTO test.t1 VALUES (1,@vc1,@d1); INSERT INTO test.t1 VALUES (2,@vc2,@b1); INSERT INTO test.t1 VALUES (3,@vc3,@d2); INSERT INTO test.t1 VALUES (4,@vc4,@b2); SELECT a1,length(a2),substr(a2,180,2),length(a3),substr(a3,1+3*900,3) FROM test.t1 WHERE a1=1; SELECT a1,length(a2),substr(a2,480,2),length(a3),substr(a3,1+2*900,3) FROM test.t1 where a1=2; INSERT INTO test.t2(a1,a2,a3) SELECT * FROM test.t1; SELECT a1,length(a2),substr(a2,180,2),length(a3),substr(a3,1+3*900,3) FROM test.t2 WHERE a1=1; SELECT a1,length(a2),substr(a2,480,2),length(a3),substr(a3,1+2*900,3) FROM test.t2 where a1=2; DROP TABLE test.t1, test.t2; ALTER TABLESPACE ts1 DROP DATAFILE './table_space1/datafile.dat' ENGINE NDB; DROP TABLESPACE ts1 ENGINE NDB; ALTER TABLESPACE ts2 DROP DATAFILE './table_space2/datafile.dat' ENGINE NDB; DROP TABLESPACE ts2 ENGINE NDB; DROP LOGFILE GROUP lg ENGINE=NDB; #### Insert, Update, Delete from NDB table with BLOB fields #### --echo ***** --echo **** Insert, Update, Delete from NDB table with BLOB fields --echo ***** CREATE LOGFILE GROUP lg ADD UNDOFILE './lg_group/undofile.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=NDB; CREATE TABLESPACE ts1 ADD DATAFILE './table_space1/datafile.dat' USE LOGFILE GROUP lg INITIAL_SIZE 12M ENGINE NDB; set @vc1 = repeat('a', 200); set @vc2 = repeat('b', 500); set @vc3 = repeat('c', 1000); set @vc4 = repeat('d', 4000); set @vc5 = repeat('d', 5000); set @bb1 = repeat('1', 2000); set @bb2 = repeat('2', 5000); set @bb3 = repeat('3', 10000); set @bb4 = repeat('4', 40000); set @bb5 = repeat('5', 50000); select length(@vc1),length(@vc2),length(@vc3),length(@vc4),length(@vc5) from dual; select length(@bb1),length(@bb2),length(@bb3),length(@bb4),length(@bb5) from dual; CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(5000), a3 BLOB) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; # CREATE TABLE test.t2 (a1 int NOT NULL, a2 VARCHAR(5000), a3 BLOB) # TABLESPACE ts2 STORAGE DISK ENGINE=NDB; INSERT INTO test.t1 VALUES (1,@vc1,@bb1); INSERT INTO test.t1 VALUES (2,@vc2,@bb2); INSERT INTO test.t1 VALUES (3,@vc3,@bb3); INSERT INTO test.t1 VALUES (4,@vc4,@bb4); INSERT INTO test.t1 VALUES (5,@vc5,@bb5); UPDATE test.t1 SET a2=@vc5, a3=@bb5 WHERE a1=1; SELECT a1,length(a2),substr(a2,4998,2),length(a3),substr(a3,49997,3) FROM test.t1 WHERE a1=1; UPDATE test.t1 SET a2=@vc4, a3=@bb4 WHERE a1=2; SELECT a1,length(a2),substr(a2,3998,2),length(a3),substr(a3,39997,3) FROM test.t1 WHERE a1=2; UPDATE test.t1 SET a2=@vc2, a3=@bb2 WHERE a1=3; SELECT a1,length(a2),substr(a2,498,2),length(a3),substr(a3,3997,3) FROM test.t1 WHERE a1=3; UPDATE test.t1 SET a2=@vc3, a3=@bb3 WHERE a1=4; SELECT a1,length(a2),substr(a2,998,2),length(a3),substr(a3,9997,3) FROM test.t1 WHERE a1=4; UPDATE test.t1 SET a2=@vc1, a3=@bb1 WHERE a1=5; SELECT a1,length(a2),substr(a2,198,2),length(a3),substr(a3,1997,3) FROM test.t1 WHERE a1=5; DELETE FROM test.t1 where a1=5; SELECT count(*) from test.t1; DELETE FROM test.t1 where a1=4; SELECT count(*) from test.t1; DELETE FROM test.t1 where a1=3; SELECT count(*) from test.t1; DELETE FROM test.t1 where a1=2; SELECT count(*) from test.t1; DELETE FROM test.t1 where a1=1; SELECT count(*) from test.t1; DROP TABLE test.t1; ALTER TABLESPACE ts1 DROP DATAFILE './table_space1/datafile.dat' ENGINE NDB; DROP TABLESPACE ts1 ENGINE NDB; DROP LOGFILE GROUP lg ENGINE=NDB; ##### Create Stored procedures that use disk based tables ##### --echo ***** --echo **** Create Stored procedures that use disk based tables --echo ***** CREATE LOGFILE GROUP lg ADD UNDOFILE './lg_group/undofile.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=NDB; CREATE TABLESPACE ts1 ADD DATAFILE './table_space1/datafile.dat' USE LOGFILE GROUP lg INITIAL_SIZE 12M ENGINE NDB; delimiter //; CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB) TABLESPACE ts1 STORAGE DISK ENGINE=NDB// CREATE PROCEDURE test.sp1() BEGIN INSERT INTO test.t1 values (1,'111111','aaaaaaaa'); END// delimiter ;// CALL test.sp1(); SELECT * FROM test.t1; delimiter //; CREATE PROCEDURE test.sp2(n INT, vc VARCHAR(256), blb BLOB) BEGIN UPDATE test.t1 SET a2=vc, a3=blb where a1=n; END// delimiter ;// CALL test.sp2(1,'222222','bbbbbbbb'); SELECT * FROM test.t1; DELETE FROM test.t1; DROP PROCEDURE test.sp1; DROP PROCEDURE test.sp2; DROP TABLE test.t1; ALTER TABLESPACE ts1 DROP DATAFILE './table_space1/datafile.dat' ENGINE=NDB; DROP TABLESPACE ts1 ENGINE=NDB; DROP LOGFILE GROUP lg ENGINE=NDB; #### Create function that operate on disk based tables #### --echo ***** --echo ***** Create function that operate on disk based tables --echo ***** CREATE LOGFILE GROUP lg ADD UNDOFILE './lg_group/undofile.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=NDB; CREATE TABLESPACE ts1 ADD DATAFILE './table_space1/datafile.dat' USE LOGFILE GROUP lg INITIAL_SIZE 12M ENGINE NDB; CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; let $1=100; disable_query_log; while ($1) { eval insert into test.t1 values($1, "aaaaa$1", "bbbbb$1"); dec $1; } enable_query_log; delimiter //; CREATE FUNCTION test.fn1(n INT) RETURNS INT BEGIN DECLARE v INT; SELECT a1 INTO v FROM test.t1 WHERE a1=n; RETURN v; END// delimiter ;// delimiter //; CREATE FUNCTION test.fn2(n INT, blb BLOB) RETURNS BLOB BEGIN DECLARE vv BLOB; UPDATE test.t1 SET a3=blb where a1=n; SELECT a3 INTO vv FROM test.t1 WHERE a1=n; RETURN vv; END// delimiter ;// SELECT test.fn1(10) FROM DUAL; SELECT test.fn2(50, 'new BLOB content') FROM DUAL; DELETE FROM test.t1; DROP FUNCTION test.fn1; DROP FUNCTION test.fn2; DROP TABLE test.t1; ALTER TABLESPACE ts1 DROP DATAFILE './table_space1/datafile.dat' ENGINE=NDB; DROP TABLESPACE ts1 ENGINE=NDB; DROP LOGFILE GROUP lg ENGINE=NDB; #### Create triggers that operate on disk based tables #### --echo ***** --echo ***** Create triggers that operate on disk based tables --echo ***** CREATE LOGFILE GROUP lg ADD UNDOFILE './lg_group/undofile.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=NDB; CREATE TABLESPACE ts1 ADD DATAFILE './table_space1/datafile.dat' USE LOGFILE GROUP lg INITIAL_SIZE 12M ENGINE NDB; CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; delimiter //; CREATE TRIGGER test.trg1 BEFORE INSERT ON test.t1 FOR EACH ROW BEGIN if isnull(new.a2) then set new.a2:= 'trg1 works on a2 field'; end if; if isnull(new.a3) then set new.a3:= 'trg1 works on a3 field'; end if; end// insert into test.t1 (a1) values (1)// insert into test.t1 (a1,a2) values (2, 'ccccccc')// select * from test.t1 order by a1// delimiter ;// DELETE FROM test.t1; DROP TRIGGER test.trg1; DROP TABLE test.t1; ALTER TABLESPACE ts1 DROP DATAFILE './table_space1/datafile.dat' ENGINE=NDB; DROP TABLESPACE ts1 ENGINE=NDB; DROP LOGFILE GROUP lg ENGINE=NDB; #### Create, update views that operate on disk based tables #### --echo ***** --echo ***** Create, update views that operate on disk based tables --echo ***** CREATE LOGFILE GROUP lg ADD UNDOFILE './lg_group/undofile.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=NDB; CREATE TABLESPACE ts1 ADD DATAFILE './table_space1/datafile.dat' USE LOGFILE GROUP lg INITIAL_SIZE 12M ENGINE NDB; CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; let $1=10; disable_query_log; while ($1) { eval insert into test.t1 values($1, "aaaaa$1", "bbbbb$1"); dec $1; } enable_query_log; CREATE VIEW test.v1 AS SELECT * FROM test.t1; SELECT * FROM test.v1 order by a1; CHECK TABLE test.v1, test.t1; UPDATE test.v1 SET a2='zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz' WHERE a1=5; SELECT * FROM test.v1 order by a1; DROP VIEW test.v1; DELETE FROM test.t1; DROP TABLE test.t1; ALTER TABLESPACE ts1 DROP DATAFILE './table_space1/datafile.dat' ENGINE=NDB; DROP TABLESPACE ts1 ENGINE=NDB; DROP LOGFILE GROUP lg ENGINE=NDB; #### Create and use disk based table that use auto inc #### --echo ***** --echo ***** Create and use disk based table that use auto inc --echo ***** CREATE LOGFILE GROUP lg ADD UNDOFILE './lg_group/undofile.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=NDB; CREATE TABLESPACE ts1 ADD DATAFILE './table_space1/datafile.dat' USE LOGFILE GROUP lg INITIAL_SIZE 12M ENGINE NDB; CREATE TABLE test.t1 (a1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a2 VARCHAR(256), a3 BLOB) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; let $1=10; disable_query_log; while ($1) { eval insert into test.t1 values(NULL, "aaaaa$1", "bbbbb$1"); dec $1; } enable_query_log; SELECT * FROM test.t1 ORDER BY a1; DELETE FROM test.t1; DROP TABLE test.t1; ALTER TABLESPACE ts1 DROP DATAFILE './table_space1/datafile.dat' ENGINE=NDB; DROP TABLESPACE ts1 ENGINE=NDB; DROP LOGFILE GROUP lg ENGINE=NDB; #### Create test that use transaction (commit, rollback) #### --echo ***** --echo ***** Create test that use transaction (commit, rollback) --echo ***** CREATE LOGFILE GROUP lg ADD UNDOFILE './lg_group/undofile.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=NDB; CREATE TABLESPACE ts1 ADD DATAFILE './table_space1/datafile.dat' USE LOGFILE GROUP lg INITIAL_SIZE 12M ENGINE NDB; SET AUTOCOMMIT=0; CREATE TABLE test.t1 (a1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a2 VARCHAR(256), a3 BLOB) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; INSERT INTO test.t1 VALUES(NULL, "aaaaa1", "bbbbb1"); COMMIT; SELECT * FROM test.t1 ORDER BY a1; INSERT INTO test.t1 VALUES(NULL, "aaaaa2", "bbbbb2"); ROLLBACK; SELECT * FROM test.t1 ORDER BY a1; DELETE FROM test.t1; DROP TABLE test.t1; SET AUTOCOMMIT=1; # Now do the same thing with START TRANSACTION without using AUTOCOMMIT. CREATE TABLE test.t1 (a1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a2 VARCHAR(256), a3 BLOB) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; START TRANSACTION; INSERT INTO test.t1 VALUES(NULL, "aaaaa1", "bbbbb1"); COMMIT; SELECT * FROM test.t1 ORDER BY a1; START TRANSACTION; INSERT INTO test.t1 VALUES(NULL, "aaaaa2", "bbbbb2"); ROLLBACK; SELECT * FROM test.t1 ORDER BY a1; DELETE FROM test.t1; DROP TABLE test.t1; ALTER TABLESPACE ts1 DROP DATAFILE './table_space1/datafile.dat' ENGINE=NDB; DROP TABLESPACE ts1 ENGINE=NDB; DROP LOGFILE GROUP lg ENGINE=NDB; #### Create test that uses locks #### --echo ***** --echo ***** Create test that uses locks --echo ***** connect (con1,localhost,root,,); connect (con2,localhost,root,,); CREATE LOGFILE GROUP lg ADD UNDOFILE './lg_group/undofile.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=NDB; CREATE TABLESPACE ts1 ADD DATAFILE './table_space1/datafile.dat' USE LOGFILE GROUP lg INITIAL_SIZE 12M ENGINE NDB; # connection con1; --disable_warnings drop table if exists test.t1; CREATE TABLE test.t1 (a1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a2 VARCHAR(256), a3 BLOB) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; --enable_warnings LOCK TABLES test.t1 write; INSERT INTO test.t1 VALUES(NULL, "aaaaa1", "bbbbb1"); INSERT INTO test.t1 VALUES(NULL, "aaaaa2", "bbbbb2"); SELECT * FROM test.t1 ORDER BY a1; connection con2; SELECT * FROM test.t1 ORDER BY a1; INSERT INTO test.t1 VALUES(NULL, "aaaaa3", "bbbbb3"); connection con1; UNLOCK TABLES; connection con2; INSERT INTO test.t1 VALUES(NULL, "aaaaa3", "bbbbb3"); SELECT * FROM test.t1 ORDER BY a1; DELETE FROM test.t1; DROP TABLE test.t1; #connection defualt; ALTER TABLESPACE ts1 DROP DATAFILE './table_space1/datafile.dat' ENGINE=NDB; DROP TABLESPACE ts1 ENGINE=NDB; DROP LOGFILE GROUP lg ENGINE=NDB; #### Create large disk base table, do random queries, check cache hits #### --echo ***** --echo ***** Create large disk base table, do random queries, check cache hits --echo ***** set @vc1 = repeat('a', 200); SELECT @vc1 FROM DUAL; set @vc2 = repeat('b', 500); set @vc3 = repeat('b', 998); # x0 size 256 set @x0 = '01234567012345670123456701234567'; set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0); # b1 length 2000+256 (blob part aligned) set @b1 = 'b1'; set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); set @b1 = concat(@b1,@x0); # d1 length 3000 set @d1 = 'dd1'; set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); # b2 length 20000 set @b2 = 'b2'; set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); # d2 length 30000 set @d2 = 'dd2'; set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); select length(@x0),length(@b1),length(@d1) from dual; select length(@x0),length(@b2),length(@d2) from dual; CREATE LOGFILE GROUP lg ADD UNDOFILE './lg_group/undofile.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=NDB; CREATE TABLESPACE ts1 ADD DATAFILE './table_space1/datafile.dat' USE LOGFILE GROUP lg INITIAL_SIZE 12M ENGINE NDB; CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(1000), a3 BLOB) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; INSERT INTO test.t1 values(1,@vc1,@d1); INSERT INTO test.t1 values(2,@vc2,@d2); explain SELECT * from test.t1 WHERE a1 = 1; SELECT a1,length(a2),substr(a2,1+2*900,2),length(a3),substr(a3,1+3*900,3) FROM test.t1 WHERE a1=1 ORDER BY a1; SELECT a1,length(a2),substr(a2,1+2*9000,2),length(a3),substr(a3,1+3*9000,3) FROM test.t1 where a1=2 ORDER BY a1; UPDATE test.t1 set a2=@vc2,a3=@d2 where a1=1; UPDATE test.t1 set a2=@vc1,a3=@d1 where a1=2; SELECT a1,length(a2),substr(a2,1+2*9000,2),length(a3),substr(a3,1+3*9000,3) FROM test.t1 where a1=1; SELECT a1,length(a2),substr(a2,1+2*900,2),length(a3),substr(a3,1+3*900,3) FROM test.t1 where a1=2; #SHOW VARIABLES LIKE 'have_query_cache'; #SHOW STATUS LIKE 'Qcache%'; DELETE FROM test.t1; DROP TABLE test.t1; ALTER TABLESPACE ts1 DROP DATAFILE './table_space1/datafile.dat' ENGINE=NDB; DROP TABLESPACE ts1 ENGINE=NDB; DROP LOGFILE GROUP lg ENGINE=NDB; #### Create test that uses COUNT(), SUM(), MAX(), MIN(), NOW(), USER(), TRUNCATE #### --echo ***** --echo ***** Create test that uses COUNT(), SUM(), MAX(), MIN(), NOW(), USER(), TRUNCATE --echo ***** CREATE LOGFILE GROUP lg ADD UNDOFILE './lg_group/undofile.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=NDB; CREATE TABLESPACE ts1 ADD DATAFILE './table_space1/datafile.dat' USE LOGFILE GROUP lg INITIAL_SIZE 12M ENGINE NDB; CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB, a4 DATE, a5 CHAR(250)) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; let $1=100; disable_query_log; while ($1) { eval insert into test.t1 values($1, "aaaaaaaaaaaaaaaa$1", "bbbbbbbbbbbbbbbbbb$1", '2006-06-20' , USER()); dec $1; } enable_query_log; SELECT COUNT(*) from test.t1; SELECT SUM(a1) from test.t1; SELECT MIN(a1) from test.t1; SELECT MAX(a1) from test.t1; SELECT a5 from test.t1 where a1=50; SELECT * from test.t1 order by a1; DROP TABLE test.t1; ALTER TABLESPACE ts1 DROP DATAFILE './table_space1/datafile.dat' ENGINE=NDB; DROP TABLESPACE ts1 ENGINE=NDB; DROP LOGFILE GROUP lg ENGINE=NDB; #End 5.1 test case