Debian Codegraph @

mysql-test/suite/ndb_opt/subquery.result

raw
1 create table t1 (p int not null primary key, u int not null, o int not null,
2 unique (u), key(o)) engine=ndb
3 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4 create table t2 (p int not null primary key, u int not null, o int not null,
5 unique (u), key(o)) engine=ndb
6 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
7 create table t3 (a int not null primary key, b int not null) engine=ndb
8 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
9 create table t4 (c int not null primary key, d int not null) engine=ndb
10 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
11 insert into t1 values (1,1,1),(2,2,2),(3,3,3);
12 insert into t2 values (1,1,1),(2,2,2),(3,3,3), (4,4,4), (5,5,5);
13 insert into t3 values (1,10), (2,10), (3,30), (4, 30);
14 insert into t4 values (1,10), (2,10), (3,30), (4, 30);
15 explain select * from t2 where p NOT IN (select p from t1);
16 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
17 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Parent of 2 pushed join@1
18 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 test.t2.p # # Child of 't2' in pushed join@1; Using where; Not exists
19 Warnings:
20 Note 1003 /* select#1 */ select `test`.`t2`.`p` AS `p`,`test`.`t2`.`u` AS `u`,`test`.`t2`.`o` AS `o` from `test`.`t2` anti join (`test`.`t1`) on((`test`.`t1`.`p` = `test`.`t2`.`p`)) where true
21 select * from t2 where p NOT IN (select p from t1) order by p;
22 p u o
23 4 4 4
24 5 5 5
25 explain select * from t2 where p NOT IN (select u from t1);
26 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
27 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Parent of 2 pushed join@1
28 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref u u 4 test.t2.p # # Child of 't2' in pushed join@1; Using where; Not exists
29 Warnings:
30 Note 1003 /* select#1 */ select `test`.`t2`.`p` AS `p`,`test`.`t2`.`u` AS `u`,`test`.`t2`.`o` AS `o` from `test`.`t2` anti join (`test`.`t1`) on((`test`.`t1`.`u` = `test`.`t2`.`p`)) where true
31 select * from t2 where p NOT IN (select u from t1) order by p;
32 p u o
33 4 4 4
34 5 5 5
35 explain select * from t2 where p NOT IN (select o from t1);
36 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
37 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Parent of 2 pushed join@1
38 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref o o 4 test.t2.p # # Child of 't2' in pushed join@1; Using where; Not exists
39 Warnings:
40 Note 1003 /* select#1 */ select `test`.`t2`.`p` AS `p`,`test`.`t2`.`u` AS `u`,`test`.`t2`.`o` AS `o` from `test`.`t2` anti join (`test`.`t1`) on((`test`.`t1`.`o` = `test`.`t2`.`p`)) where true
41 select * from t2 where p NOT IN (select o from t1) order by p;
42 p u o
43 4 4 4
44 5 5 5
45 explain select * from t2 where p NOT IN (select p+0 from t1);
46 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
47 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # NULL
48 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # Using where; Not exists; Using join buffer (hash join)
49 Warnings:
50 Note 1003 Can't push table 't1' as child, 'type' must be a 'ref' access
51 Note 1003 /* select#1 */ select `test`.`t2`.`p` AS `p`,`test`.`t2`.`u` AS `u`,`test`.`t2`.`o` AS `o` from `test`.`t2` anti join (`test`.`t1`) on((`test`.`t2`.`p` = (`test`.`t1`.`p` + 0))) where true
52 select * from t2 where p NOT IN (select p+0 from t1) order by p;
53 p u o
54 4 4 4
55 5 5 5
56 drop table t1;
57 drop table t2;
58 create table t1 (p int not null primary key, u int not null) engine=ndb;
59 insert into t1 values (1,1),(2,2),(3,3);
60 create table t2 as
61 select t1.*
62 from t1 as t1, t1 as t2, t1 as t3, t1 as t4, t1 as t5, t1 as t6, t1 as t7, t1 as t8
63 where t1.u = t2.u
64 and t2.u = t3.u
65 and t3.u = t4.u
66 and t4.u = t5.u
67 and t5.u = t6.u
68 and t6.u = t7.u
69 and t7.u = t8.u;
70 select * from t2 order by 1;
71 p u
72 1 1
73 2 2
74 3 3
75 select * from t3 where a = any (select c from t4 where c = 1) order by a;
76 a b
77 1 10
78 select * from t3 where a in (select c from t4 where c = 1) order by a;
79 a b
80 1 10
81 select * from t3 where a <> some (select c from t4 where c = 1) order by a;
82 a b
83 2 10
84 3 30
85 4 30
86 select * from t3 where a > all (select c from t4 where c = 1) order by a;
87 a b
88 2 10
89 3 30
90 4 30
91 select * from t3 where row(1,10) = (select c,d from t4 where c = 1) order by a;
92 a b
93 1 10
94 2 10
95 3 30
96 4 30
97 select * from t3 where exists (select * from t4 where c = 1) order by a;
98 a b
99 1 10
100 2 10
101 3 30
102 4 30
103 drop table if exists t1, t2, t3, t4;
104 create table t (k int, uq int, unique key ix1 (uq)) engine = ndb;
105 insert into t values (1,3), (3,6), (6,9), (9,1);
106 select * from t where
107 k in (select uq from t as subq where subq.k>10);
108 k uq
109 drop table if exists t;
110 End of 5.1 tests
111 CREATE TABLE `i` (
112 `col_int_key` int(11) DEFAULT NULL,
113 KEY `col_int_key` (`col_int_key`)
114 ) ENGINE=ndbcluster
115 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
116 Warnings:
117 Warning 1681 Integer display width is deprecated and will be removed in a future release.
118 INSERT INTO `i` VALUES
119 (37), (36),
120 (8), (16), (42), (6), (8),
121 (11), (21), (13), (3), (41), (44),
122 (4), (4)
123 ;
124 CREATE TABLE `v` (
125 `col_varchar_10_key` varchar(10) COLLATE latin1_bin DEFAULT NULL,
126 `col_datetime_key` datetime DEFAULT NULL,
127 `col_varchar_256` varchar(256) COLLATE latin1_bin NOT NULL DEFAULT '',
128 `col_int_unique` int(11) DEFAULT NULL,
129 `pk` int(11) NOT NULL,
130 `col_char_16` char(16) COLLATE latin1_bin DEFAULT NULL,
131 `col_varchar_256_unique` varchar(256) COLLATE latin1_bin DEFAULT NULL,
132 `col_char_16_unique` char(16) COLLATE latin1_bin DEFAULT NULL,
133 `col_int` int(11) DEFAULT NULL,
134 `col_datetime_unique` datetime DEFAULT NULL,
135 `col_datetime` datetime DEFAULT NULL,
136 `col_char_16_key` char(16) COLLATE latin1_bin DEFAULT NULL,
137 `col_varchar_256_key` varchar(256) COLLATE latin1_bin DEFAULT NULL,
138 `col_int_key` int(11) DEFAULT NULL,
139 `col_varchar_10` varchar(10) COLLATE latin1_bin DEFAULT NULL,
140 `col_varchar_10_unique` varchar(10) COLLATE latin1_bin DEFAULT NULL,
141 PRIMARY KEY (`pk`,`col_varchar_256`),
142 KEY `col_int_key` (`col_int_key`)
143 ) ENGINE=ndbcluster
144 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
145 Warnings:
146 Warning 1681 Integer display width is deprecated and will be removed in a future release.
147 Warning 1681 Integer display width is deprecated and will be removed in a future release.
148 Warning 1681 Integer display width is deprecated and will be removed in a future release.
149 Warning 1681 Integer display width is deprecated and will be removed in a future release.
150 INSERT INTO `v` VALUES
151 ('you\'re','2009-07-16 09:36:26','hkovsqapozumxaaufxv',4,3,'ghkovsqapozumx','well','cgh',1,'2003-12-19 20:20:58',NULL,'zcghkovsqapozumx','for',38,'ozcghkovsq','up'),
152 ('how','2009-04-19 21:37:41','something',16,4,'I','fozcghkovsqapozumxaaufx','ofozcghkovsqapoz',16,NULL,NULL,'to','gofozcg',50,'dgofozcghk','back'),
153 ('lcdytrrpqh','2000-08-25 17:23:04','jlcdytrrpqhjwzwdqfcnuiisfrmapu',18,13,'djlcdytrrpqhjwz','odjlcdytrrpqhjwzwdqfcnuiisf','of',6,NULL,'2003-12-21 02:53:21','xodjlcdytrrpqhjw','kxodjlcdytrrpqhjwzwdqfcnui',10,'xkxodjlcdy','you\'re'),
154 ('want','2009-06-09 21:32:42','come',25,24,'yrprhaqtqyuyryml','fyrprhaqtqyuyrymlfv','xfyrprhaqtq',27,NULL,'2007-07-26 02:41:45','nxfyrprhaqtqyuyr','really',13,'mnxfyrprha','a'),
155 ('pozumxaauf',NULL,'there',48,2,'a','qap','sqapozumxaaufxvm',12,NULL,'2008-08-12 14:41:31','vsqapozumxaaufxv','I\'m',45,'ovsqapozum','kovsqapozu'),
156 ('going','2001-11-17 21:30:28','syodnmxkxodjlcdytrr',43,16,'v','dvsyodnmxkxod','ud',46,'2000-03-19 11:23:59','2000-08-05 09:22:19','some','she',40,'iudv','all'),
157 ('ekzgviudvs','2009-02-26 08:37:21','nekzgviudvsyod',49,17,'inekzgviudvsyodn','cinekzgviudvsyodn','ok',12,'2007-12-04 07:42:22','2002-10-07 12:27:21','fcinekzgviudvsyo','to',6,'bfcinekz','hbfcin'),
158 ('a',NULL,'sfrmapuikkd',9,9,'isfrmapuikkdlzaj','he\'s','iisfrmapuikkdlza',5,NULL,NULL,'got','uiisfrmapuikkdlzajntnnwrkdgofoz',28,'nuiisfrmap','cnuiisfrma'),
159 ('odnmxkxo','2006-08-28 02:18:02','for',8,15,'my','hey','who',3,'2002-11-18 21:58:14',NULL,'right','yodnmxkxodjlcdytrrpqhjwz',0,'get','had'),
160 ('your','2000-01-02 20:39:51','can',35,20,'aqgwquyt','gaqgwquytisgdhbfcinekzgviud','cgaqgwquytisgdhb',13,'2005-05-08 17:20:27','2003-07-08 19:23:36','be','lcgaqgwquytisgdhbfcinek',13,'can\'t','like'),
161 ('tisgdhbfci',NULL,'ytisgdhbfcinekzgviudvsyodnmx',38,19,'uy','quytisgdhbfci','wquyti',3,'2008-06-20 00:22:51','2007-11-18 22:50:03','at','gwqu',31,'qgwquytisg','one'),
162 ('rpqh',NULL,'rrpqhjwzwdqfcnuiisf',10,12,'go','yes','trrpqhjwzwdqfcnu',38,'2007-10-04 05:31:37',NULL,'ytrr','dytrrpqhjwzwdqfcnuii',7,'cdytrrpqhj','say'),
163 ('on',NULL,'say',33,23,'about','izjphzzxquxwwrxan','I\'ll',7,'2009-02-10 20:02:17','2005-03-05 21:18:45','are','my',4,'lizjphzzxq','out'),
164 ('just',NULL,'with',22,8,'rmap','something','something',4,NULL,'2006-01-14 09:08:21','or','mean',31,'frmapuikkd','here'),
165 ('who','2000-07-21 09:51:12','or',28,10,'fcnuiisfrmapuikk','for','me',13,'2001-03-20 15:30:14','2003-09-12 10:09:54','is','qfcn',3,'when','well');
166 set ndb_join_pushdown = off;
167 explain
168 SELECT pk, col_int_key
169 FROM v AS table1
170 WHERE table1.col_int_key IN (
171 SELECT /*+ SEMIJOIN(DUPSWEEDOUT) */ col_int_key AS field2 FROM i as table1s)
172 ;
173 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
174 1 SIMPLE table1 p0,p1,p2,p3,p4,p5,p6,p7 ALL col_int_key NULL NULL NULL ### 100.00 Using pushed condition (`test`.`table1`.`col_int_key` is not null)
175 1 SIMPLE table1s p0,p1,p2,p3,p4,p5,p6,p7 ref col_int_key col_int_key 5 test.table1.col_int_key ### 100.00 Start temporary; End temporary
176 Warnings:
177 Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`select#2` DUPSWEEDOUT) */ `test`.`table1`.`pk` AS `pk`,`test`.`table1`.`col_int_key` AS `col_int_key` from `test`.`v` `table1` semi join (`test`.`i` `table1s`) where (`test`.`table1s`.`col_int_key` = `test`.`table1`.`col_int_key`)
178 SELECT pk, col_int_key
179 FROM v AS table1
180 WHERE table1.col_int_key IN (
181 SELECT /*+ SEMIJOIN(DUPSWEEDOUT) */ col_int_key AS field2 FROM i as table1s)
182 ;
183 pk col_int_key
184 10 3
185 17 6
186 20 13
187 23 4
188 24 13
189 DROP TABLE IF EXISTS i,v;
190 End of 5.6 tests