1drop table if exists t1,t2,t3; 2SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 3Warnings: 4Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 5CREATE TABLE t1 6( 7FOLDERID VARCHAR(32)BINARY NOT NULL 8, FOLDERNAME VARCHAR(255)BINARY NOT NULL 9, CREATOR VARCHAR(255)BINARY 10, CREATED TIMESTAMP NOT NULL 11, DESCRIPTION VARCHAR(255)BINARY 12, FOLDERTYPE INTEGER NOT NULL 13, MODIFIED TIMESTAMP 14, MODIFIER VARCHAR(255)BINARY 15, FOLDERSIZE INTEGER NOT NULL 16, PARENTID VARCHAR(32)BINARY 17, REPID VARCHAR(32)BINARY 18, ORIGINATOR INTEGER 19, PRIMARY KEY ( FOLDERID ) 20) ENGINE=InnoDB; 21CREATE INDEX FFOLDERID_IDX ON t1 (FOLDERID); 22CREATE INDEX CMFLDRPARNT_IDX ON t1 (PARENTID); 23INSERT INTO t1 VALUES("0c9aab05b15048c59bc35c8461507deb", "System", "System", "2003-06-05 16:30:00", "The system content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "9c9aab05b15048c59bc35c8461507deb", "1"); 24INSERT INTO t1 VALUES("2f6161e879db43c1a5b82c21ddc49089", "Default", "System", "2003-06-09 10:52:02", "The default content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "03eea05112b845949f3fd03278b5fe43", "1"); 25INSERT INTO t1 VALUES("c373e9f5ad0791724315444553544200", "AddDocumentTest", "admin", "2003-06-09 10:51:25", "Movie Reviews", "0", "2003-06-09 10:51:25", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL); 26SELECT 'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1'); 27'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1') 280 29drop table t1; 30create table t1 (a int) engine=innodb; 31create table t2 (a int) engine=innodb; 32create table t3 (a int) engine=innodb; 33insert into t1 values (1),(2),(3),(4); 34insert into t2 values (10),(20),(30),(40); 35insert into t3 values (1),(2),(10),(50); 36select a from t3 where t3.a in (select a from t1 where a <= 3 union select * from t2 where a <= 30); 37a 381 392 4010 41drop table t1,t2,t3; 42CREATE TABLE t1 ( 43processor_id INTEGER NOT NULL, 44PRIMARY KEY (processor_id) 45) ENGINE=InnoDB; 46CREATE TABLE t3 ( 47yod_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, 48login_processor INTEGER UNSIGNED , 49PRIMARY KEY (yod_id) 50) ENGINE=InnoDB; 51CREATE TABLE t2 ( 52processor_id INTEGER NOT NULL, 53yod_id BIGINT UNSIGNED NOT NULL, 54PRIMARY KEY (processor_id, yod_id), 55INDEX (processor_id), 56INDEX (yod_id), 57FOREIGN KEY (processor_id) REFERENCES t1(processor_id), 58FOREIGN KEY (yod_id) REFERENCES t3(yod_id) 59) ENGINE=InnoDB; 60INSERT INTO t1 VALUES (1),(2),(3); 61INSERT INTO t3 VALUES (1,1),(2,2),(3,3); 62INSERT INTO t2 VALUES (1,1),(2,2),(3,3); 63SELECT distinct p1.processor_id, (SELECT y.yod_id FROM t1 p2, t2 y WHERE p2.processor_id = p1.processor_id and p2.processor_id = y.processor_id) FROM t1 p1; 64processor_id (SELECT y.yod_id FROM t1 p2, t2 y WHERE p2.processor_id = p1.processor_id and p2.processor_id = y.processor_id) 651 1 662 2 673 3 68drop table t2,t1,t3; 69CREATE TABLE t1 ( 70id int(11) NOT NULL default '0', 71b int(11) default NULL, 72c char(3) default NULL, 73PRIMARY KEY (id), 74KEY t2i1 (b) 75) ENGINE=innodb DEFAULT CHARSET=latin1; 76INSERT INTO t1 VALUES (0,0,'GPL'),(1,0,'GPL'),(2,1,'GPL'),(3,2,'GPL'); 77CREATE TABLE t2 ( 78id int(11) NOT NULL default '0', 79b int(11) default NULL, 80c char(3) default NULL, 81PRIMARY KEY (id), 82KEY t2i (b) 83) ENGINE=innodb DEFAULT CHARSET=latin1; 84INSERT INTO t2 VALUES (0,0,'GPL'),(1,0,'GPL'),(2,1,'GPL'),(3,2,'GPL'); 85select (select max(id) from t2 where b=1 group by b) as x,b from t1 where b=1; 86x b 872 1 88drop table t1,t2; 89create table t1 (id int not null, value char(255), primary key(id)) engine=innodb; 90create table t2 (id int not null, value char(255)) engine=innodb; 91insert into t1 values (1,'a'),(2,'b'); 92insert into t2 values (1,'z'),(2,'x'); 93select t2.id,t2.value,(select t1.value from t1 where t1.id=t2.id) from t2; 94id value (select t1.value from t1 where t1.id=t2.id) 951 z a 962 x b 97select t2.id,t2.value,(select t1.value from t1 where t1.id=t2.id) from t2; 98id value (select t1.value from t1 where t1.id=t2.id) 991 z a 1002 x b 101drop table t1,t2; 102create table t1 (a int, b int) engine=innodb; 103insert into t1 values (1,2), (1,3), (2,3), (2,4), (2,5), (3,4), (4,5), (4,100); 104create table t2 (a int) engine=innodb; 105insert into t2 values (1),(2),(3),(4); 106select a, sum(b) as b from t1 group by a having b > (select max(a) from t2); 107a b 1081 5 1092 12 1104 105 111drop table t1, t2; 112CREATE TABLE `t1` ( `unit` varchar(50) NOT NULL default '', `ingredient` varchar(50) NOT NULL default '') ENGINE=InnoDB DEFAULT CHARSET=latin1; 113CREATE TABLE `t2` ( `ingredient` varchar(50) NOT NULL default '', `unit` varchar(50) NOT NULL default '', PRIMARY KEY (ingredient, unit)) ENGINE=InnoDB DEFAULT CHARSET=latin1; 114INSERT INTO `t1` VALUES ('xx','yy'); 115INSERT INTO `t2` VALUES ('yy','xx'); 116SELECT R.unit, R.ingredient FROM t1 R WHERE R.ingredient IN (SELECT N.ingredient FROM t2 N WHERE N.unit = R.unit); 117unit ingredient 118xx yy 119drop table t1, t2; 120CREATE TABLE t1 ( 121id INT NOT NULL auto_increment, 122date1 DATE, coworkerid INT, 123description VARCHAR(255), 124sum_used DOUBLE, 125sum_remaining DOUBLE, 126comments VARCHAR(255), 127PRIMARY KEY(id) 128) engine=innodb; 129insert into t1 values (NULL, '1999-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1999-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1999-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1998-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1998-01-01', 1,'test', 22, 33, 'comment'), (NULL, '2004-01-01', 1,'test', 22, 33, 'comment'), (NULL, '2004-01-01', 1,'test', 22, 33, 'comment'); 130SELECT DISTINCT 131(SELECT sum(sum_used) FROM t1 WHERE sum_used > 0 AND year(date1) <= '2004') as somallontvangsten, 132(SELECT sum(sum_used) FROM t1 WHERE sum_used < 0 AND year(date1) <= '2004') as somalluitgaven 133FROM t1; 134somallontvangsten somalluitgaven 135154 NULL 136select * from t1; 137id date1 coworkerid description sum_used sum_remaining comments 1381 1999-01-01 1 test 22 33 comment 1392 1999-01-01 1 test 22 33 comment 1403 1999-01-01 1 test 22 33 comment 1414 1998-01-01 1 test 22 33 comment 1425 1998-01-01 1 test 22 33 comment 1436 2004-01-01 1 test 22 33 comment 1447 2004-01-01 1 test 22 33 comment 145drop table t1; 146CREATE TABLE `t1` ( `a` char(3) NOT NULL default '', `b` char(3) NOT NULL default '', `c` char(3) NOT NULL default '', PRIMARY KEY (`a`,`b`,`c`)) ENGINE=InnoDB; 147CREATE TABLE t2 LIKE t1; 148INSERT INTO t1 VALUES (1,1,1); 149INSERT INTO t2 VALUES (1,1,1); 150PREPARE my_stmt FROM "SELECT t1.b, count(*) FROM t1 group by t1.b having 151count(*) > ALL (SELECT COUNT(*) FROM t2 WHERE t2.a=1 GROUP By t2.b)"; 152EXECUTE my_stmt; 153b count(*) 154EXECUTE my_stmt; 155b count(*) 156deallocate prepare my_stmt; 157drop table t1,t2; 158CREATE TABLE t1 159( 160DOCID VARCHAR(32)BINARY NOT NULL 161, UUID VARCHAR(32)BINARY NOT NULL 162, MIMETYPE VARCHAR(80)BINARY 163, CONTENTDATA LONGBLOB 164, CONTENTSIZE INTEGER 165, VERSIONID INTEGER 166, REPID VARCHAR(32)BINARY 167, MODIFIED TIMESTAMP 168, MODIFIER VARCHAR(255)BINARY 169, ORIGINATOR INTEGER 170, PRIMARY KEY ( DOCID ) 171) ENGINE=InnoDB 172; 173INSERT INTO t1 (DOCID) VALUES ("1"), ("2"); 174Warnings: 175Warning 1364 Field 'UUID' doesn't have a default value 176CREATE TABLE t2 177( 178DOCID VARCHAR(32)BINARY NOT NULL 179, DOCNAME VARCHAR(255)BINARY NOT NULL 180, DOCTYPEID VARCHAR(32)BINARY NOT NULL 181, FOLDERID VARCHAR(32)BINARY NOT NULL 182, AUTHOR VARCHAR(255)BINARY 183, CREATED TIMESTAMP NOT NULL 184, TITLE VARCHAR(255)BINARY 185, SUBTITLE VARCHAR(255)BINARY 186, DOCABSTRACT LONGBLOB 187, PUBLISHDATE TIMESTAMP 188, EXPIRATIONDATE TIMESTAMP 189, LOCKEDBY VARCHAR(80)BINARY 190, STATUS VARCHAR(80)BINARY 191, PARENTDOCID VARCHAR(32)BINARY 192, REPID VARCHAR(32)BINARY 193, MODIFIED TIMESTAMP NOT NULL 194, MODIFIER VARCHAR(255)BINARY NOT NULL 195, PUBLISHSTATUS INTEGER 196, ORIGINATOR INTEGER 197, PRIMARY KEY ( DOCID ) 198) ENGINE=InnoDB 199; 200CREATE INDEX DDOCTYPEID_IDX ON t2 (DOCTYPEID); 201CREATE INDEX DFOLDERID_IDX ON t2 (FOLDERID); 202CREATE TABLE t3 203( 204FOLDERID VARCHAR(32)BINARY NOT NULL 205, FOLDERNAME VARCHAR(255)BINARY NOT NULL 206, CREATOR VARCHAR(255)BINARY 207, CREATED TIMESTAMP NOT NULL 208, DESCRIPTION VARCHAR(255)BINARY 209, FOLDERTYPE INTEGER NOT NULL 210, MODIFIED TIMESTAMP 211, MODIFIER VARCHAR(255)BINARY 212, FOLDERSIZE INTEGER NOT NULL 213, PARENTID VARCHAR(32)BINARY 214, REPID VARCHAR(32)BINARY 215, ORIGINATOR INTEGER 216, PRIMARY KEY ( FOLDERID ) 217) ENGINE=InnoDB; 218CREATE INDEX FFOLDERID_IDX ON t3 (FOLDERID); 219CREATE INDEX CMFLDRPARNT_IDX ON t3 (PARENTID); 220CREATE TABLE t4 221( 222DOCTYPEID VARCHAR(32)BINARY NOT NULL 223, DOCTYPENAME VARCHAR(80)BINARY NOT NULL 224, DESCRIPTION VARCHAR(255)BINARY 225, EXTNDATA LONGBLOB 226, MODIFIED TIMESTAMP 227, MODIFIER VARCHAR(255)BINARY 228, ORIGINATOR INTEGER 229, PRIMARY KEY ( DOCTYPEID ) 230) ENGINE=InnoDB; 231INSERT INTO t2 VALUES("c373e9f59cf15a6c3e57444553544200", "c373e9f59cf15a6c3e57444553544200", "340d243d45f111d497b00010a4ef934d", "2f6161e879db43c1a5b82c21ddc49089", NULL, "2003-06-06 07:48:42", NULL, NULL, NULL, "2003-06-06 07:48:42", "2003-06-06 07:48:42", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-06 07:48:42", "admin", "0", NULL); 232INSERT INTO t2 VALUES("c373e9f5a472f43ba45e444553544200", "c373e9f5a472f43ba45e444553544200", "340d243d45f111d497b00010a4ef934d", "2f6161e879db43c1a5b82c21ddc49089", NULL, "2003-06-07 18:50:12", NULL, NULL, NULL, "2003-06-07 18:50:12", "2003-06-07 18:50:12", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-07 18:50:12", "admin", "0", NULL); 233INSERT INTO t2 VALUES("c373e9f5a4a0f56014eb444553544200", "c373e9f5a4a0f56014eb444553544200", "340d243d45f111d497b00010a4ef934d", "2f6161e879db43c1a5b82c21ddc49089", NULL, "2003-06-07 19:39:26", NULL, NULL, NULL, "2003-06-07 19:39:26", "2003-06-07 19:39:26", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-07 19:39:26", "admin", "0", NULL); 234INSERT INTO t2 VALUES("c373e9f5a4a0f8fa4a86444553544200", "c373e9f5a4a0f8fa4a86444553544200", "340d243d45f111d497b00010a4ef934d", "2f6161e879db43c1a5b82c21ddc49089", NULL, "2003-06-07 19:43:05", NULL, NULL, NULL, "2003-06-07 19:43:05", "2003-06-07 19:43:05", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-07 19:43:05", "admin", "0", NULL); 235INSERT INTO t2 VALUES("c373e9f5ac7b537205ce444553544200", "c373e9f5ac7b537205ce444553544200", "340d243d45f111d497b00010a4ef934d", "2f6161e879db43c1a5b82c21ddc49089", NULL, "2003-06-09 08:15:24", NULL, NULL, NULL, "2003-06-09 08:15:24", "2003-06-09 08:15:24", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-09 08:15:24", "admin", "0", NULL); 236INSERT INTO t2 VALUES("c373e9f5ad0792012454444553544200", "c373e9f5ad0792012454444553544200", "340d243d45f111d497b00010a4ef934d", "2f6161e879db43c1a5b82c21ddc49089", NULL, "2003-06-09 10:51:44", NULL, NULL, NULL, "2003-06-09 10:51:44", "2003-06-09 10:51:44", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-09 10:51:44", "admin", "0", NULL); 237INSERT INTO t2 VALUES("c373e9f5ad079821ef34444553544200", "First Discussion", "c373e9f5ad079174ff17444553544200", "c373e9f5ad0796c0eca4444553544200", "Goldilocks", "2003-06-09 11:16:50", "Title: First Discussion", NULL, NULL, "2003-06-09 10:51:26", "2003-06-09 10:51:26", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-09 11:16:50", "admin", "0", NULL); 238INSERT INTO t2 VALUES("c373e9f5ad07993f3859444553544200", "Last Discussion", "c373e9f5ad079174ff17444553544200", "c373e9f5ad0796c0eca4444553544200", "Goldilocks", "2003-06-09 11:21:06", "Title: Last Discussion", NULL, "Setting new abstract and keeping doc checked out", "2003-06-09 10:51:26", "2003-06-09 10:51:26", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-09 11:21:06", "admin", "0", NULL); 239INSERT INTO t2 VALUES("c373e9f5ad079a3219c4444553544200", "testdoclayout", "340d243c45f111d497b00010a4ef934d", "c373e9f5ad0796c0eca4444553544200", "Goldilocks", "2003-06-09 11:25:31", "Title: Test doc layout", "Subtitle: test doc layout", NULL, "2003-06-09 10:51:27", "2003-06-09 10:51:27", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-09 11:25:31", "admin", "0", NULL); 240INSERT INTO t3 VALUES("0c9aab05b15048c59bc35c8461507deb", "System", "System", "2003-06-05 16:30:00", "The system content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "9c9aab05b15048c59bc35c8461507deb", "1"); 241INSERT INTO t3 VALUES("2f6161e879db43c1a5b82c21ddc49089", "Default", "System", "2003-06-09 10:52:02", "The default content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "03eea05112b845949f3fd03278b5fe43", "1"); 242INSERT INTO t3 VALUES("c373e9f5ad0791724315444553544200", "AddDocumentTest", "admin", "2003-06-09 10:51:25", "Movie Reviews", "0", "2003-06-09 10:51:25", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL); 243INSERT INTO t3 VALUES("c373e9f5ad07919e1963444553544200", "NewDestDirectory", "admin", "2003-06-09 10:51:28", "Adding new directory", "128", "2003-06-09 10:51:28", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL); 244INSERT INTO t3 VALUES("c373e9f5ad07919fe525444553544200", "SubDestDirectory", "admin", "2003-06-09 10:51:28", "Adding new directory", "128", "2003-06-09 10:51:28", "admin", "0", "c373e9f5ad07919e1963444553544200", "03eea05112b845949f3fd03278b5fe43", NULL); 245INSERT INTO t3 VALUES("c373e9f5ad0791a0dab5444553544200", "Level1", "admin", "2003-06-09 10:51:29", NULL, "0", "2003-06-09 10:51:29", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL); 246INSERT INTO t3 VALUES("c373e9f5ad0791a14669444553544200", "Level2", "admin", "2003-06-09 10:51:29", NULL, "0", "2003-06-09 10:51:29", "admin", "0", "c373e9f5ad0791a0dab5444553544200", "03eea05112b845949f3fd03278b5fe43", NULL); 247INSERT INTO t3 VALUES("c373e9f5ad0791a23c0e444553544200", "Level3", "admin", "2003-06-09 10:51:29", NULL, "0", "2003-06-09 10:51:29", "admin", "0", "c373e9f5ad0791a14669444553544200", "03eea05112b845949f3fd03278b5fe43", NULL); 248INSERT INTO t3 VALUES("c373e9f5ad0791a6b11f444553544200", "Dir1", "admin", "2003-06-09 10:51:30", NULL, "0", "2003-06-09 10:51:30", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL); 249INSERT INTO t3 VALUES("c373e9f5ad0791a897d6444553544200", "Dir2", "admin", "2003-06-09 10:51:30", NULL, "0", "2003-06-09 10:51:30", "admin", "0", "c373e9f5ad0791a6b11f444553544200", "03eea05112b845949f3fd03278b5fe43", NULL); 250INSERT INTO t3 VALUES("c373e9f5ad0791a9a063444553544200", "NewDestDirectory", "admin", "2003-06-09 10:51:31", NULL, "0", "2003-06-09 10:51:31", "admin", "0", "c373e9f5ad0791a897d6444553544200", "03eea05112b845949f3fd03278b5fe43", NULL); 251INSERT INTO t3 VALUES("c373e9f5ad0791aa73e3444553544200", "LevelA", "admin", "2003-06-09 10:51:31", NULL, "0", "2003-06-09 10:51:31", "admin", "0", "c373e9f5ad0791a0dab5444553544200", "03eea05112b845949f3fd03278b5fe43", NULL); 252INSERT INTO t3 VALUES("c373e9f5ad0791ab034b444553544200", "LevelB", "admin", "2003-06-09 10:51:31", NULL, "0", "2003-06-09 10:51:31", "admin", "0", "c373e9f5ad0791aa73e3444553544200", "03eea05112b845949f3fd03278b5fe43", NULL); 253INSERT INTO t3 VALUES("c373e9f5ad0791ac7311444553544200", "LevelC", "admin", "2003-06-09 10:51:32", NULL, "0", "2003-06-09 10:51:32", "admin", "0", "c373e9f5ad0791ab034b444553544200", "03eea05112b845949f3fd03278b5fe43", NULL); 254INSERT INTO t3 VALUES("c373e9f5ad0791ad66cf444553544200", "test2", "admin", "2003-06-09 10:51:32", NULL, "0", "2003-06-09 10:51:32", "admin", "0", "c373e9f5ad0791724315444553544200", "03eea05112b845949f3fd03278b5fe43", NULL); 255INSERT INTO t3 VALUES("c373e9f5ad0791aebd87444553544200", "test3", "admin", "2003-06-09 10:51:33", NULL, "0", "2003-06-09 10:51:33", "admin", "0", "c373e9f5ad0791ad66cf444553544200", "03eea05112b845949f3fd03278b5fe43", NULL); 256INSERT INTO t3 VALUES("c373e9f5ad0791dbaac4444553544200", "Special Café Folder", "admin", "2003-06-09 10:51:43", "test folder names with special chars", "0", "2003-06-09 10:51:43", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL); 257INSERT INTO t3 VALUES("c373e9f5ad0796bf913f444553544200", "CopiedFolder", "admin", "2003-06-09 11:09:05", "Movie Reviews", "0", "2003-06-09 11:09:05", "admin", "0", "c373e9f5ad0791a23c0e444553544200", "03eea05112b845949f3fd03278b5fe43", NULL); 258INSERT INTO t3 VALUES("c373e9f5ad0796c0eca4444553544200", "Movie Reviews", "admin", "2003-06-09 11:09:13", "Movie Reviews", "0", "2003-06-09 11:09:13", "admin", "33", "c373e9f5ad0796bf913f444553544200", "03eea05112b845949f3fd03278b5fe43", NULL); 259INSERT INTO t3 VALUES("c373e9f5ad0796d9b895444553544200", "NewBookFolder", "admin", "2003-06-09 11:12:41", "NewBooks - folder", "0", "2003-06-09 11:12:41", "admin", "0", "c373e9f5ad0796c0eca4444553544200", "03eea05112b845949f3fd03278b5fe43", NULL); 260INSERT INTO t3 VALUES("c373e9f5ad079b4c9355444553544200", "CopiedFolder", "admin", "2003-06-09 11:26:34", "Movie Reviews", "0", "2003-06-09 11:26:34", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL); 261INSERT INTO t4 VALUES("340d243c45f111d497b00010a4ef934d", "Document Layout", "The system Document Layouts Document Type", NULL, "2003-06-05 16:30:00", "System", "1"); 262INSERT INTO t4 VALUES("340d243d45f111d497b00010a4ef934d", "Default", "The default system Document Type", NULL, "2003-06-05 16:30:00", "System", "1"); 263INSERT INTO t4 VALUES("4d09dd60850711d4998a204c4f4f5020", "__SystemResourceType", "The type for all the uploaded resources", NULL, "2003-06-05 16:30:00", "System", "1"); 264INSERT INTO t4 VALUES("91d4d595478211d497b40010a4ef934d", "__PmcSystemDefaultType", "The type for all the default available fields", NULL, "2003-06-05 16:30:00", "System", "1"); 265INSERT INTO t4 VALUES("c373e9f59cf15a59b08a444553544200", "NoFieldDocType", "plain doc type", NULL, "2003-06-06 07:48:40", "admin", NULL); 266INSERT INTO t4 VALUES("c373e9f59cf15a5c6a99444553544200", "Movie Review", "This doc type is for movie reviews", "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n<props autocheckin=\"false\" autopublish=\"false\" binary=\"choice\" categories=\"none\" cleanup=\"false\" folder=\"none\"><![CDATA[Doc type for cm tests]]></props>\r\n", "2003-06-06 07:48:40", "admin", NULL); 267INSERT INTO t4 VALUES("c373e9f59cf15a6116a5444553544200", "Special DocÃu20A4u20A4u0113ééøÉu016BType", "test special chars xxxé in doc type", NULL, "2003-06-06 07:48:41", "admin", NULL); 268INSERT INTO t4 VALUES("c373e9f59cf15a695d47444553544200", "Movie", NULL, NULL, "2003-06-06 07:48:41", "admin", NULL); 269INSERT INTO t4 VALUES("c373e9f5ad079174ff17444553544200", "Discussion", NULL, NULL, "2003-06-09 10:51:25", "admin", NULL); 270INSERT INTO t4 VALUES("c373e9f5ad0791da7e2b444553544200", "Books", "list of recommended books", "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n<props autocheckin=\"false\" autopublish=\"false\" binary=\"choice\" categories=\"none\" cleanup=\"false\" folder=\"none\"><![CDATA[Doc type for cm tests]]><![CDATA[Doc type for book tests]]></props>\r\n", "2003-06-09 10:51:40", "admin", NULL); 271ALTER TABLE t2 ADD FOREIGN KEY FK_DCMNTS_DCTYPES ( DOCTYPEID) 272REFERENCES t4 (DOCTYPEID ); 273ALTER TABLE t2 ADD FOREIGN KEY FK_DCMNTS_FLDRS ( FOLDERID) 274REFERENCES t3 (FOLDERID ); 275ALTER TABLE t3 ADD FOREIGN KEY FK_FLDRS_PRNTID ( PARENTID) 276REFERENCES t3 (FOLDERID ); 277ANALYZE TABLE t1; 278Table Op Msg_type Msg_text 279test.t1 analyze status OK 280ANALYZE TABLE t2; 281Table Op Msg_type Msg_text 282test.t2 analyze status OK 283ANALYZE TABLE t3; 284Table Op Msg_type Msg_text 285test.t3 analyze status OK 286ANALYZE TABLE t4; 287Table Op Msg_type Msg_text 288test.t4 analyze status OK 289SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion'; 290DOCID DOCNAME DOCTYPEID FOLDERID AUTHOR CREATED TITLE SUBTITLE DOCABSTRACT PUBLISHDATE EXPIRATIONDATE LOCKEDBY STATUS PARENTDOCID REPID MODIFIED MODIFIER PUBLISHSTATUS ORIGINATOR DOCTYPENAME CONTENTSIZE MIMETYPE 291c373e9f5ad07993f3859444553544200 Last Discussion c373e9f5ad079174ff17444553544200 c373e9f5ad0796c0eca4444553544200 Goldilocks 2003-06-09 11:21:06 Title: Last Discussion NULL Setting new abstract and keeping doc checked out 2003-06-09 10:51:26 2003-06-09 10:51:26 NULL NULL NULL 03eea05112b845949f3fd03278b5fe43 2003-06-09 11:21:06 admin 0 NULL Discussion NULL NULL 292EXPLAIN EXTENDED SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion'; 293id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2941 SIMPLE t3 NULL ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 const 6 10.00 Using where 2951 SIMPLE t3 NULL ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 10.00 Using where 2961 SIMPLE t3 NULL ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 10.00 Using where 2971 SIMPLE t3 NULL ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 10.00 Using where 2981 SIMPLE t3 NULL ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 10.00 Using where 2991 SIMPLE t2 NULL ref DDOCTYPEID_IDX,DFOLDERID_IDX DFOLDERID_IDX 34 test.t3.FOLDERID 4 11.11 Using where 3001 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 34 test.t2.DOCID 1 100.00 NULL 3011 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 34 test.t2.DOCTYPEID 1 100.00 NULL 302Warnings: 303Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 304Note 1003 /* select#1 */ select `test`.`t2`.`DOCID` AS `DOCID`,`test`.`t2`.`DOCNAME` AS `DOCNAME`,`test`.`t2`.`DOCTYPEID` AS `DOCTYPEID`,`test`.`t2`.`FOLDERID` AS `FOLDERID`,`test`.`t2`.`AUTHOR` AS `AUTHOR`,`test`.`t2`.`CREATED` AS `CREATED`,`test`.`t2`.`TITLE` AS `TITLE`,`test`.`t2`.`SUBTITLE` AS `SUBTITLE`,`test`.`t2`.`DOCABSTRACT` AS `DOCABSTRACT`,`test`.`t2`.`PUBLISHDATE` AS `PUBLISHDATE`,`test`.`t2`.`EXPIRATIONDATE` AS `EXPIRATIONDATE`,`test`.`t2`.`LOCKEDBY` AS `LOCKEDBY`,`test`.`t2`.`STATUS` AS `STATUS`,`test`.`t2`.`PARENTDOCID` AS `PARENTDOCID`,`test`.`t2`.`REPID` AS `REPID`,`test`.`t2`.`MODIFIED` AS `MODIFIED`,`test`.`t2`.`MODIFIER` AS `MODIFIER`,`test`.`t2`.`PUBLISHSTATUS` AS `PUBLISHSTATUS`,`test`.`t2`.`ORIGINATOR` AS `ORIGINATOR`,`test`.`t4`.`DOCTYPENAME` AS `DOCTYPENAME`,`test`.`t1`.`CONTENTSIZE` AS `CONTENTSIZE`,`test`.`t1`.`MIMETYPE` AS `MIMETYPE` from `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t2` join `test`.`t4` left join `test`.`t1` on((`test`.`t1`.`DOCID` = `test`.`t2`.`DOCID`)) where ((`test`.`t4`.`DOCTYPEID` = `test`.`t2`.`DOCTYPEID`) and (`test`.`t2`.`FOLDERID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`FOLDERNAME` = 'Level1') and (`test`.`t3`.`PARENTID` = '2f6161e879db43c1a5b82c21ddc49089') and (`test`.`t3`.`FOLDERNAME` = 'Level2') and (`test`.`t3`.`FOLDERNAME` = 'Level3') and (`test`.`t3`.`FOLDERNAME` = 'CopiedFolder') and (`test`.`t3`.`FOLDERNAME` = 'Movie Reviews') and (`test`.`t2`.`DOCNAME` = 'Last Discussion')) 305drop table t1, t2, t3, t4; 306CREATE TABLE t1 ( 307school_name varchar(45) NOT NULL, 308country varchar(45) NOT NULL, 309funds_requested float NOT NULL, 310schooltype varchar(45) NOT NULL 311) ENGINE=InnoDB DEFAULT CHARSET=utf8; 312insert into t1 values ("the school", "USA", 1200, "Human"); 313select count(country) as countrycount, sum(funds_requested) as smcnt, 314country, (select sum(funds_requested) from t1) as total_funds 315from t1 316group by country; 317countrycount smcnt country total_funds 3181 1200 USA 1200 319select count(country) as countrycount, sum(funds_requested) as smcnt, 320country, (select sum(funds_requested) from t1) as total_funds 321from t1 322group by country; 323countrycount smcnt country total_funds 3241 1200 USA 1200 325drop table t1; 326CREATE TABLE `t1` ( 327`t3_id` int NOT NULL, 328`t1_id` int NOT NULL, 329PRIMARY KEY (`t1_id`) 330); 331CREATE TABLE `t2` ( 332`t2_id` int NOT NULL, 333`t1_id` int NOT NULL, 334`b` int NOT NULL, 335PRIMARY KEY (`t2_id`), 336UNIQUE KEY `idx_t2_t1_b` (`t1_id`,`b`) 337) ENGINE=InnoDB; 338CREATE TABLE `t3` ( 339`t3_id` int NOT NULL 340); 341INSERT INTO `t3` VALUES (3); 342select 343(SELECT rs.t2_id 344FROM t2 rs 345WHERE rs.t1_id= 346(SELECT lt.t1_id 347FROM t1 lt 348WHERE lt.t3_id=a.t3_id) 349ORDER BY b DESC LIMIT 1) 350from t3 AS a; 351(SELECT rs.t2_id 352FROM t2 rs 353WHERE rs.t1_id= 354(SELECT lt.t1_id 355FROM t1 lt 356WHERE lt.t3_id=a.t3_id) 357ORDER BY b DESC LIMIT 1) 358NULL 359DROP PROCEDURE IF EXISTS p1; 360create procedure p1() 361begin 362declare done int default 3; 363repeat 364select 365(SELECT rs.t2_id 366FROM t2 rs 367WHERE rs.t1_id= 368(SELECT lt.t1_id 369FROM t1 lt 370WHERE lt.t3_id=a.t3_id) 371ORDER BY b DESC LIMIT 1) as x 372from t3 AS a; 373set done= done-1; 374until done <= 0 end repeat; 375end// 376call p1(); 377x 378NULL 379x 380NULL 381x 382NULL 383call p1(); 384x 385NULL 386x 387NULL 388x 389NULL 390call p1(); 391x 392NULL 393x 394NULL 395x 396NULL 397drop procedure p1; 398drop tables t1,t2,t3; 399CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB; 400INSERT INTO t1 VALUES (1),(2); 401CREATE TABLE t2 (a int(10), PRIMARY KEY (a)) Engine=InnoDB; 402INSERT INTO t2 VALUES (1); 403CREATE TABLE t3 (a int(10), b int(10), c int(10), 404PRIMARY KEY (a)) Engine=InnoDB; 405INSERT INTO t3 VALUES (1,2,1); 406SELECT t1.* FROM t1 WHERE (SELECT COUNT(*) FROM t3,t2 WHERE t3.c=t2.a 407and t2.a='1' AND t1.a=t3.b) > 0; 408a 4092 410DROP TABLE t1,t2,t3; 411# 412# Bug #58756 413# Crash in heap_rrnd on query with HAVING ... IN (subquery) + LIMIT 414# 415CREATE TABLE t1 ( 416col_time_key time DEFAULT NULL, 417col_datetime_key datetime DEFAULT NULL, 418col_varchar_nokey varchar(1) DEFAULT NULL, 419KEY col_time_key (col_time_key), 420KEY col_datetime_key (col_datetime_key) 421) ENGINE=InnoDB DEFAULT CHARSET=latin1; 422INSERT INTO t1 VALUES ('17:53:30','2005-11-10 12:40:29','h'); 423INSERT INTO t1 VALUES ('11:35:49','2009-04-25 00:00:00','b'); 424INSERT INTO t1 VALUES (NULL,'2002-11-27 00:00:00','s'); 425INSERT INTO t1 VALUES ('06:01:40','2004-01-26 20:32:32','e'); 426INSERT INTO t1 VALUES ('05:45:11','2007-10-26 11:41:40','j'); 427INSERT INTO t1 VALUES ('00:00:00','2005-10-07 00:00:00','e'); 428INSERT INTO t1 VALUES ('00:00:00','2000-07-15 05:00:34','f'); 429INSERT INTO t1 VALUES ('06:11:01','2000-04-03 16:33:32','v'); 430INSERT INTO t1 VALUES ('13:02:46',NULL,'x'); 431INSERT INTO t1 VALUES ('21:44:25','2001-04-25 01:26:12','m'); 432INSERT INTO t1 VALUES ('22:43:58','2000-12-27 00:00:00','c'); 433CREATE TABLE t2 ( 434col_time_key time DEFAULT NULL, 435col_datetime_key datetime DEFAULT NULL, 436col_varchar_nokey varchar(1) DEFAULT NULL, 437KEY col_time_key (col_time_key), 438KEY col_datetime_key (col_datetime_key) 439) ENGINE=InnoDB DEFAULT CHARSET=latin1; 440INSERT INTO t2 VALUES ('11:28:45','2004-10-11 18:13:16','w'); 441SELECT col_time_key, col_datetime_key 442FROM 443( SELECT * FROM t1 ) AS table1 444HAVING ( 'r' , 'e' ) IN 445( SELECT col_varchar_nokey , col_varchar_nokey FROM t2 ) 446ORDER BY col_datetime_key 447LIMIT 10; 448col_time_key col_datetime_key 449DROP TABLE t1; 450DROP TABLE t2; 451# End of Bug #58756 452# 453# Bug#60085 crash in Item::save_in_field() with time data type 454# 455CREATE TABLE t1(a date, b int, unique(b), unique(a), key(b)) engine=innodb; 456INSERT INTO t1 VALUES ('2011-05-13', 0); 457SELECT * FROM t1 WHERE b < (SELECT CAST(a as date) FROM t1 GROUP BY a); 458a b 4592011-05-13 0 460DROP TABLE t1; 461# 462# Bug #11766300 59387: FAILING ASSERTION: CURSOR->POS_STATE == 1997660512 (BTR_PCUR_IS_POSITIONE 463# 464CREATE TABLE t1 (a INT) ENGINE=INNODB; 465INSERT INTO t1 VALUES (0); 466CREATE TABLE t2 (d BINARY(2), PRIMARY KEY (d(1)), UNIQUE KEY (d)) ENGINE=INNODB; 467SELECT 1 FROM t1 WHERE NOT EXISTS 468(SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d); 4691 4701 471EXPLAIN SELECT 1 FROM t1 WHERE NOT EXISTS 472(SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d); 473id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4741 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where 4752 DEPENDENT SUBQUERY t2 NULL eq_ref PRIMARY,d PRIMARY 1 func 1 100.00 Using where 4763 DEPENDENT SUBQUERY t2 NULL index NULL d 2 NULL 1 100.00 Using where; Using index 477Warnings: 478Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 479Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where (not(exists(/* select#2 */ select 1 from `test`.`t2` where (`test`.`t2`.`d` = (/* select#3 */ select `test`.`t2`.`d` from `test`.`t2` where (`test`.`t1`.`a` >= 1)))))) 480DROP TABLE t2; 481CREATE TABLE t2 (b INT, c INT, UNIQUE KEY (b), UNIQUE KEY (b, c )) ENGINE=INNODB; 482INSERT INTO t2 VALUES (1, 1); 483SELECT 1 FROM t1 484WHERE a != (SELECT 1 FROM t2 WHERE a <=> b OR a > '' AND 6 = 7 ORDER BY b, c); 4851 486DROP TABLE t1, t2; 487SET sql_mode = default; 488