← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/r/subselect2.result

  • Committer: Brian Aker
  • Date: 2009-01-09 22:20:19 UTC
  • mfrom: (642.1.80 drizzle-clean-code)
  • Revision ID: brian@tangent.org-20090109222019-bjzie4zzy6e8zdcp
Merge from Lee

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/r/subselect2.result
2
2
CREATE TABLE t1
3
3
(
4
4
DOCID VARCHAR(32)BINARY NOT NULL
5
 
, UUID VARCHAR(32)BINARY NOT NULL
 
5
, UUID VARCHAR(32)BINARY NOT NULL DEFAULT ""
6
6
, MIMETYPE VARCHAR(80)BINARY
7
7
, CONTENTDATA LONGBLOB
8
8
, CONTENTSIZE INTEGER
15
15
) ENGINE=InnoDB
16
16
;
17
17
INSERT INTO t1 (DOCID) VALUES ("1"), ("2");
18
 
Warnings:
19
 
Warning 1364    Field 'UUID' doesn't have a default value
20
18
CREATE TABLE t2
21
19
(
22
20
DOCID VARCHAR(32)BINARY NOT NULL
97
95
INSERT 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);
98
96
INSERT 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);
99
97
INSERT 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);
100
 
INSERT 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);
 
98
INSERT 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);
101
99
INSERT 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);
102
100
INSERT 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);
103
101
INSERT 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);
108
106
INSERT INTO t4 VALUES("91d4d595478211d497b40010a4ef934d", "__PmcSystemDefaultType", "The type for all the default available fields", NULL, "2003-06-05 16:30:00", "System", "1");
109
107
INSERT INTO t4 VALUES("c373e9f59cf15a59b08a444553544200", "NoFieldDocType", "plain doc type", NULL, "2003-06-06 07:48:40", "admin", NULL);
110
108
INSERT 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);
111
 
INSERT INTO t4 VALUES("c373e9f59cf15a6116a5444553544200", "Special Doc�u20A4u20A4u0113����u016BType", "test special chars xxx� in doc type", NULL, "2003-06-06 07:48:41", "admin", NULL);
112
109
INSERT INTO t4 VALUES("c373e9f59cf15a695d47444553544200", "Movie", NULL, NULL, "2003-06-06 07:48:41", "admin", NULL);
113
110
INSERT INTO t4 VALUES("c373e9f5ad079174ff17444553544200", "Discussion", NULL, NULL, "2003-06-09 10:51:25", "admin", NULL);
114
111
INSERT 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);
123
120
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    Discussion      NULL    NULL
124
121
EXPLAIN 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';
125
122
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
126
 
1       PRIMARY t4      ALL     PRIMARY NULL    NULL    NULL    10      
 
123
1       PRIMARY t3      ref     PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX   CMFLDRPARNT_IDX 131     const   2       Using where
 
124
1       PRIMARY t3      ref     PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX   CMFLDRPARNT_IDX 131     test.t3.FOLDERID        1       Using where
 
125
1       PRIMARY t3      ref     PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX   CMFLDRPARNT_IDX 131     test.t3.FOLDERID        1       Using where
 
126
1       PRIMARY t3      ref     PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX   CMFLDRPARNT_IDX 131     test.t3.FOLDERID        1       Using where
 
127
1       PRIMARY t3      ref     PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX   CMFLDRPARNT_IDX 131     test.t3.FOLDERID        1       Using where
127
128
1       PRIMARY t2      ALL     DDOCTYPEID_IDX,DFOLDERID_IDX    NULL    NULL    NULL    9       Using where; Using join buffer
128
 
1       PRIMARY t1      eq_ref  PRIMARY PRIMARY 34      test.t2.DOCID   1       
129
 
1       PRIMARY t3      eq_ref  PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX   PRIMARY 34      test.t2.FOLDERID        1       Using where
130
 
1       PRIMARY t3      eq_ref  PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX   PRIMARY 34      test.t3.PARENTID        1       Using where; FirstMatch(t3)
131
 
1       PRIMARY t3      eq_ref  PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX   PRIMARY 34      test.t3.PARENTID        1       Using where; FirstMatch(t3)
132
 
1       PRIMARY t3      eq_ref  PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX   PRIMARY 34      test.t3.PARENTID        1       Using where; FirstMatch(t3)
133
 
1       PRIMARY t3      eq_ref  PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX   PRIMARY 34      test.t3.PARENTID        1       Using where; FirstMatch(t3)
 
129
1       PRIMARY t1      eq_ref  PRIMARY PRIMARY 130     test.t2.DOCID   1       
 
130
1       PRIMARY t4      eq_ref  PRIMARY PRIMARY 130     test.t2.DOCTYPEID       1       
134
131
drop table t1, t2, t3, t4;
135
 
CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
 
132
CREATE TABLE t1 (a int, PRIMARY KEY (a)) Engine=InnoDB;
136
133
INSERT INTO t1 VALUES (1),(2);
137
 
CREATE TABLE t2 (a int(10), PRIMARY KEY (a)) Engine=InnoDB;
 
134
CREATE TABLE t2 (a int, PRIMARY KEY (a)) Engine=InnoDB;
138
135
INSERT INTO t2 VALUES (1);
139
 
CREATE TABLE t3 (a int(10), b int(10), c int(10),
 
136
CREATE TABLE t3 (a int, b int, c int,
140
137
PRIMARY KEY (a)) Engine=InnoDB;
141
138
INSERT INTO t3 VALUES (1,2,1);
142
139
SELECT t1.* FROM t1 WHERE (SELECT COUNT(*) FROM t3,t2 WHERE t3.c=t2.a