| 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 |