diff options
Diffstat (limited to 'mysql-test/suite/ndb/r/ndb_dd_disk2memory.result')
-rw-r--r-- | mysql-test/suite/ndb/r/ndb_dd_disk2memory.result | 505 |
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 |