summaryrefslogtreecommitdiff
path: root/mysql-test/suite/ndb/r/ndb_dd_disk2memory.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/ndb/r/ndb_dd_disk2memory.result')
-rw-r--r--mysql-test/suite/ndb/r/ndb_dd_disk2memory.result505
1 files changed, 505 insertions, 0 deletions
diff --git a/mysql-test/suite/ndb/r/ndb_dd_disk2memory.result b/mysql-test/suite/ndb/r/ndb_dd_disk2memory.result
new file mode 100644
index 00000000000..46661f36d1b
--- /dev/null
+++ b/mysql-test/suite/ndb/r/ndb_dd_disk2memory.result
@@ -0,0 +1,505 @@
+DROP TABLE IF EXISTS test.t1;
+DROP TABLE IF EXISTS test.t2;
+**** Test Setup Section ****
+CREATE LOGFILE GROUP log_group1
+ADD UNDOFILE './log_group1/undofile.dat'
+INITIAL_SIZE 16M
+UNDO_BUFFER_SIZE = 1M
+ENGINE=NDB;
+CREATE TABLESPACE table_space1
+ADD DATAFILE './table_space1/datafile.dat'
+USE LOGFILE GROUP log_group1
+INITIAL_SIZE 12M
+ENGINE NDB;
+CREATE TABLE test.t1
+(pk1 INT NOT NULL PRIMARY KEY, b INT NOT NULL, c INT NOT NULL)
+TABLESPACE table_space1 STORAGE DISK
+ENGINE=NDB;
+CREATE TABLE test.t2
+(pk2 INT NOT NULL PRIMARY KEY, b2 INT NOT NULL, c2 INT NOT NULL)
+ENGINE=NDB;
+
+**** Data load for first test ****
+INSERT INTO test.t1 VALUES
+(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
+(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),
+(11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15),
+(16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),
+(21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),
+(26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30),
+(31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35),
+(36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40),
+(41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45),
+(46,46,46),(47,47,47),(48,48,48),(49,49,49),(50,50,50),
+(51,51,51),(52,52,52),(53,53,53),(54,54,54),(55,55,55),
+(56,56,56),(57,57,57),(58,58,58),(59,59,59),(60,60,60),
+(61,61,61),(62,62,62),(63,63,63),(64,64,64),(65,65,65),
+(66,66,66),(67,67,67),(68,68,68),(69,69,69),(70,70,70),
+(71,71,71),(72,72,72),(73,73,73),(74,74,74),(75,75,75);
+INSERT INTO test.t2 VALUES
+(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
+(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),
+(11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15),
+(16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),
+(21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),
+(26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30),
+(31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35),
+(36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40),
+(41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45),
+(46,46,46),(47,47,47),(48,48,48),(49,49,49),(50,50,50),
+(51,51,51),(52,52,52),(53,53,53),(54,54,54),(55,55,55),
+(56,56,56),(57,57,57),(58,58,58),(59,59,59),(60,60,60),
+(61,61,61),(62,62,62),(63,63,63),(64,64,64),(65,65,65),
+(66,66,66),(67,67,67),(68,68,68),(69,69,69),(70,70,70),
+(71,71,71),(72,72,72),(73,73,73),(74,74,74),(75,75,75);
+
+*** Test 1 Section Begins ***
+SELECT COUNT(*) FROM test.t2 LEFT JOIN test.t1 ON pk2=pk1 WHERE b2 IN (4);
+COUNT(*)
+1
+SELECT * FROM test.t2 LEFT JOIN test.t1 ON pk2=pk1 WHERE b2 IN (4);
+pk2 b2 c2 pk1 b c
+4 4 4 4 4 4
+SELECT COUNT(*) FROM test.t1 LEFT JOIN test.t2 ON pk1=pk2 WHERE b IN (4);
+COUNT(*)
+1
+SELECT COUNT(*) FROM test.t1 LEFT JOIN test.t2 ON b=b2 WHERE pk1 IN (75);
+COUNT(*)
+1
+SELECT b, c FROM test.t1 LEFT JOIN test.t2 ON pk1=pk2 ORDER BY b;
+b c
+1 1
+2 2
+3 3
+4 4
+5 5
+6 6
+7 7
+8 8
+9 9
+10 10
+11 11
+12 12
+13 13
+14 14
+15 15
+16 16
+17 17
+18 18
+19 19
+20 20
+21 21
+22 22
+23 23
+24 24
+25 25
+26 26
+27 27
+28 28
+29 29
+30 30
+31 31
+32 32
+33 33
+34 34
+35 35
+36 36
+37 37
+38 38
+39 39
+40 40
+41 41
+42 42
+43 43
+44 44
+45 45
+46 46
+47 47
+48 48
+49 49
+50 50
+51 51
+52 52
+53 53
+54 54
+55 55
+56 56
+57 57
+58 58
+59 59
+60 60
+61 61
+62 62
+63 63
+64 64
+65 65
+66 66
+67 67
+68 68
+69 69
+70 70
+71 71
+72 72
+73 73
+74 74
+75 75
+
+*** Setup for test 2 ****
+DELETE FROM test.t1;
+INSERT INTO test.t1 VALUES
+(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
+(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),
+(11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15),
+(16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),
+(21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),
+(26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30),
+(31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35),
+(36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40),
+(41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45);
+
+**** Test Section 2 ****
+SELECT b, c FROM test.t1 LEFT JOIN test.t2 ON pk1=pk2 ORDER BY b;
+b c
+1 1
+2 2
+3 3
+4 4
+5 5
+6 6
+7 7
+8 8
+9 9
+10 10
+11 11
+12 12
+13 13
+14 14
+15 15
+16 16
+17 17
+18 18
+19 19
+20 20
+21 21
+22 22
+23 23
+24 24
+25 25
+26 26
+27 27
+28 28
+29 29
+30 30
+31 31
+32 32
+33 33
+34 34
+35 35
+36 36
+37 37
+38 38
+39 39
+40 40
+41 41
+42 42
+43 43
+44 44
+45 45
+SELECT COUNT(*) FROM test.t1 LEFT JOIN test.t2 ON b=b2;
+COUNT(*)
+45
+SELECT COUNT(*) FROM test.t1 RIGHT JOIN test.t2 ON b=b2;
+COUNT(*)
+75
+SHOW CREATE TABLE test.t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `pk2` int(11) NOT NULL,
+ `b2` int(11) NOT NULL,
+ `c2` int(11) NOT NULL,
+ PRIMARY KEY (`pk2`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+SHOW CREATE TABLE test.t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `pk1` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ `c` int(11) NOT NULL,
+ PRIMARY KEY (`pk1`)
+) /*!50100 TABLESPACE table_space1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
+ALTER TABLE test.t2 TABLESPACE table_space1 STORAGE DISK
+ENGINE=NDB;
+SHOW CREATE TABLE test.t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `pk2` int(11) NOT NULL,
+ `b2` int(11) NOT NULL,
+ `c2` int(11) NOT NULL,
+ PRIMARY KEY (`pk2`)
+) /*!50100 TABLESPACE table_space1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
+ALTER TABLE test.t1 STORAGE MEMORY ENGINE=NDBCLUSTER;
+SHOW CREATE TABLE test.t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `pk1` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ `c` int(11) NOT NULL,
+ PRIMARY KEY (`pk1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+
+DROP TABLE test.t1;
+DROP TABLE test.t2;
+*** Setup for Test Section 3 ***
+CREATE TABLE test.t1 (
+usr_id INT unsigned NOT NULL,
+uniq_id INT unsigned NOT NULL AUTO_INCREMENT,
+start_num INT unsigned NOT NULL DEFAULT 1,
+increment INT unsigned NOT NULL DEFAULT 1,
+PRIMARY KEY (uniq_id),
+INDEX usr_uniq_idx (usr_id, uniq_id),
+INDEX uniq_usr_idx (uniq_id, usr_id))
+TABLESPACE table_space1 STORAGE DISK
+ENGINE=NDB;
+CREATE TABLE test.t2 (
+id INT unsigned NOT NULL DEFAULT 0,
+usr2_id INT unsigned NOT NULL DEFAULT 0,
+max INT unsigned NOT NULL DEFAULT 0,
+c_amount INT unsigned NOT NULL DEFAULT 0,
+d_max INT unsigned NOT NULL DEFAULT 0,
+d_num INT unsigned NOT NULL DEFAULT 0,
+orig_time INT unsigned NOT NULL DEFAULT 0,
+c_time INT unsigned NOT NULL DEFAULT 0,
+active ENUM ("no","yes") NOT NULL,
+PRIMARY KEY (id,usr2_id),
+INDEX id_idx (id),
+INDEX usr2_idx (usr2_id))
+ENGINE=NDB;
+INSERT INTO test.t1 VALUES (3,NULL,0,50),(3,NULL,0,200),(3,NULL,0,25),(3,NULL,0,84676),(3,NULL,0,235),(3,NULL,0,10),(3,NULL,0,3098),(3,NULL,0,2947),(3,NULL,0,8987),(3,NULL,0,8347654),(3,NULL,0,20398),(3,NULL,0,8976),(3,NULL,0,500),(3,NULL,0,198);
+
+**** Test Section 3 ****
+SELECT test.t1.usr_id,test.t1.uniq_id,test.t1.increment,
+test.t2.usr2_id,test.t2.c_amount,test.t2.max
+FROM test.t1
+LEFT JOIN test.t2 ON test.t2.id = test.t1.uniq_id
+WHERE test.t1.uniq_id = 4
+ORDER BY test.t2.c_amount;
+usr_id uniq_id increment usr2_id c_amount max
+3 4 84676 NULL NULL NULL
+INSERT INTO test.t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
+INSERT INTO test.t2 VALUES (4,3,3000,6000,0,0,746584,837484,'yes');
+INSERT INTO test.t2 VALUES (7,3,1000,2000,0,0,746294,937484,'yes');
+SELECT test.t1.usr_id,test.t1.uniq_id,test.t1.increment,
+test.t2.usr2_id,test.t2.c_amount,test.t2.max
+FROM test.t1
+LEFT JOIN test.t2 ON test.t2.id = test.t1.uniq_id
+WHERE test.t1.uniq_id = 4
+ORDER BY test.t2.c_amount;
+usr_id uniq_id increment usr2_id c_amount max
+3 4 84676 3 6000 3000
+
+DROP TABLE test.t1;
+DROP TABLE test.t2;
+CREATE TABLE test.t1 (
+a1 smallint NOT NULL,
+a2 int NOT NULL,
+a3 bigint NOT NULL,
+a4 char(10),
+a5 decimal(5,1),
+a6 time,
+a7 date,
+a8 datetime,
+a9 VARCHAR(255),
+a10 blob,
+PRIMARY KEY(a1)
+) TABLESPACE table_space1 STORAGE DISK ENGINE=NDB;
+CREATE TABLE test.t2 (
+b1 smallint NOT NULL,
+b2 int NOT NULL,
+b3 bigint NOT NULL,
+b4 char(10),
+b5 decimal(5,1),
+b6 time,
+b7 date,
+b8 datetime,
+b9 VARCHAR(255),
+b10 blob,
+PRIMARY KEY(b1)
+) ENGINE=NDB;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a1` smallint(6) NOT NULL,
+ `a2` int(11) NOT NULL,
+ `a3` bigint(20) NOT NULL,
+ `a4` char(10) DEFAULT NULL,
+ `a5` decimal(5,1) DEFAULT NULL,
+ `a6` time DEFAULT NULL,
+ `a7` date DEFAULT NULL,
+ `a8` datetime DEFAULT NULL,
+ `a9` varchar(255) DEFAULT NULL,
+ `a10` blob,
+ PRIMARY KEY (`a1`),
+ KEY `a2` (`a2`),
+ KEY `a3` (`a3`),
+ KEY `a8` (`a8`)
+) /*!50100 TABLESPACE table_space1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `b1` smallint(6) NOT NULL,
+ `b2` int(11) NOT NULL,
+ `b3` bigint(20) NOT NULL,
+ `b4` char(10) DEFAULT NULL,
+ `b5` decimal(5,1) DEFAULT NULL,
+ `b6` time DEFAULT NULL,
+ `b7` date DEFAULT NULL,
+ `b8` datetime DEFAULT NULL,
+ `b9` varchar(255) DEFAULT NULL,
+ `b10` blob,
+ PRIMARY KEY (`b1`),
+ KEY `b2` (`b2`),
+ KEY `b3` (`b3`),
+ KEY `b8` (`b8`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+a1 a2 a3 a4 a5 a6 a7 a8 a9 a10
+1 2 2000000001 aaa1 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb1 binary data
+2 3 2000000002 aaa2 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb2 binary data
+3 4 2000000003 aaa3 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb3 binary data
+4 5 2000000004 aaa4 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb4 binary data
+5 6 2000000005 aaa5 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb5 binary data
+6 7 2000000006 aaa6 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb6 binary data
+7 8 2000000007 aaa7 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb7 binary data
+8 9 2000000008 aaa8 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb8 binary data
+9 10 2000000009 aaa9 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb9 binary data
+10 11 2000000010 aaa10 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb10 binary data
+11 12 2000000011 aaa11 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb11 binary data
+12 13 2000000012 aaa12 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb12 binary data
+13 14 2000000013 aaa13 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb13 binary data
+14 15 2000000014 aaa14 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb14 binary data
+15 16 2000000015 aaa15 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb15 binary data
+16 17 2000000016 aaa16 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb16 binary data
+17 18 2000000017 aaa17 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb17 binary data
+18 19 2000000018 aaa18 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb18 binary data
+19 20 2000000019 aaa19 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb19 binary data
+20 21 2000000020 aaa20 34.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb20 binary data
+b1 b2 b3 b4 b5 b6 b7 b8 b9 b10
+3 4 3000000001 aaa1 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb1 binary data
+4 5 3000000002 aaa2 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb2 binary data
+5 6 3000000003 aaa3 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb3 binary data
+6 7 3000000004 aaa4 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb4 binary data
+7 8 3000000005 aaa5 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb5 binary data
+8 9 3000000006 aaa6 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb6 binary data
+9 10 3000000007 aaa7 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb7 binary data
+10 11 3000000008 aaa8 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb8 binary data
+11 12 3000000009 aaa9 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb9 binary data
+12 13 3000000010 aaa10 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb10 binary data
+13 14 3000000011 aaa11 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb11 binary data
+14 15 3000000012 aaa12 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb12 binary data
+15 16 3000000013 aaa13 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb13 binary data
+16 17 3000000014 aaa14 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb14 binary data
+17 18 3000000015 aaa15 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb15 binary data
+18 19 3000000016 aaa16 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb16 binary data
+19 20 3000000017 aaa17 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb17 binary data
+20 21 3000000018 aaa18 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb18 binary data
+21 22 3000000019 aaa19 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb19 binary data
+22 23 3000000020 aaa20 35.2 04:03:02 2006-01-01 1971-05-28 16:55:03 bbbbbbbbbbbbb20 binary data
+COUNT(a1) a1 COUNT(a1)*a1
+1 1 1
+1 2 2
+1 3 3
+1 4 4
+1 5 5
+1 6 6
+1 7 7
+1 8 8
+1 9 9
+1 10 10
+1 11 11
+1 12 12
+1 13 13
+1 14 14
+1 15 15
+1 16 16
+1 17 17
+1 18 18
+1 19 19
+1 20 20
+COUNT(a2) (a2+1) COUNT(a2)*(a2+0)
+1 3 2
+1 4 3
+1 5 4
+1 6 5
+1 7 6
+1 8 7
+1 9 8
+1 10 9
+1 11 10
+1 12 11
+1 13 12
+1 14 13
+1 15 14
+1 16 15
+1 17 16
+1 18 17
+1 19 18
+1 20 19
+1 21 20
+1 22 21
+a
+8
+7
+6
+5
+a c
+8 Clavin
+7 Serge
+6 Jeb
+5 Stewart
+4 Martin
+3 Pekka
+a
+2
+3
+4
+5
+6
+7
+8
+a c
+4 Martin
+5 Stewart
+6 Jeb
+7 Serge
+8 Clavin
+a
+1
+1
+1
+2
+2
+3
+3
+a
+3
+2
+1
+a
+3
+2
+1
+a
+1
+2
+3
+email shipcode
+test1@testdomain.com Z001
+test2@testdomain.com R002
+test2@testdomain.com Z001
+test3@testdomain.com Z001
+email
+test1@testdomain.com
+test2@testdomain.com
+test3@testdomain.com
+email shipcode
+test1@testdomain.com Z001
+test2@testdomain.com Z001
+test2@testdomain.com R002
+test3@testdomain.com Z001
View Lorry Controller Status