| 1 |
-- source include/have_ndb.inc |
| 2 |
|
| 3 |
########## |
| 4 |
# bug#5367 |
| 5 |
create table t1 (p int not null primary key, u int not null, o int not null, |
| 6 |
unique (u), key(o)) engine=ndb |
| 7 |
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"; |
| 8 |
|
| 9 |
create table t2 (p int not null primary key, u int not null, o int not null, |
| 10 |
unique (u), key(o)) engine=ndb |
| 11 |
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"; |
| 12 |
|
| 13 |
create table t3 (a int not null primary key, b int not null) engine=ndb |
| 14 |
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"; |
| 15 |
create table t4 (c int not null primary key, d int not null) engine=ndb |
| 16 |
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"; |
| 17 |
|
| 18 |
insert into t1 values (1,1,1),(2,2,2),(3,3,3); |
| 19 |
insert into t2 values (1,1,1),(2,2,2),(3,3,3), (4,4,4), (5,5,5); |
| 20 |
insert into t3 values (1,10), (2,10), (3,30), (4, 30); |
| 21 |
insert into t4 values (1,10), (2,10), (3,30), (4, 30); |
| 22 |
|
| 23 |
# Use pk |
| 24 |
--replace_column 10 # 11 # |
| 25 |
explain select * from t2 where p NOT IN (select p from t1); |
| 26 |
select * from t2 where p NOT IN (select p from t1) order by p; |
| 27 |
|
| 28 |
# Use unique index |
| 29 |
--replace_column 10 # 11 # |
| 30 |
explain select * from t2 where p NOT IN (select u from t1); |
| 31 |
select * from t2 where p NOT IN (select u from t1) order by p; |
| 32 |
|
| 33 |
# Use ordered index |
| 34 |
--replace_column 10 # 11 # |
| 35 |
explain select * from t2 where p NOT IN (select o from t1); |
| 36 |
select * from t2 where p NOT IN (select o from t1) order by p; |
| 37 |
|
| 38 |
# Use scan |
| 39 |
--replace_column 10 # 11 # |
| 40 |
explain select * from t2 where p NOT IN (select p+0 from t1); |
| 41 |
select * from t2 where p NOT IN (select p+0 from t1) order by p; |
| 42 |
|
| 43 |
drop table t1; |
| 44 |
drop table t2; |
| 45 |
# bug#5367 |
| 46 |
########## |
| 47 |
|
| 48 |
# End of 4.1 tests |
| 49 |
|
| 50 |
# |
| 51 |
# bug#11205 |
| 52 |
# |
| 53 |
create table t1 (p int not null primary key, u int not null) engine=ndb; |
| 54 |
insert into t1 values (1,1),(2,2),(3,3); |
| 55 |
|
| 56 |
create table t2 as |
| 57 |
select t1.* |
| 58 |
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 |
| 59 |
where t1.u = t2.u |
| 60 |
and t2.u = t3.u |
| 61 |
and t3.u = t4.u |
| 62 |
and t4.u = t5.u |
| 63 |
and t5.u = t6.u |
| 64 |
and t6.u = t7.u |
| 65 |
and t7.u = t8.u; |
| 66 |
|
| 67 |
select * from t2 order by 1; |
| 68 |
|
| 69 |
select * from t3 where a = any (select c from t4 where c = 1) order by a; |
| 70 |
select * from t3 where a in (select c from t4 where c = 1) order by a; |
| 71 |
select * from t3 where a <> some (select c from t4 where c = 1) order by a; |
| 72 |
select * from t3 where a > all (select c from t4 where c = 1) order by a; |
| 73 |
select * from t3 where row(1,10) = (select c,d from t4 where c = 1) order by a; |
| 74 |
select * from t3 where exists (select * from t4 where c = 1) order by a; |
| 75 |
|
| 76 |
drop table if exists t1, t2, t3, t4; |
| 77 |
|
| 78 |
########## |
| 79 |
# bug#58163 |
| 80 |
|
| 81 |
create table t (k int, uq int, unique key ix1 (uq)) engine = ndb; |
| 82 |
insert into t values (1,3), (3,6), (6,9), (9,1); |
| 83 |
|
| 84 |
select * from t where |
| 85 |
k in (select uq from t as subq where subq.k>10); |
| 86 |
|
| 87 |
drop table if exists t; |
| 88 |
|
| 89 |
--echo End of 5.1 tests |
| 90 |
|
| 91 |
########## |
| 92 |
# bug#16744050 DUPLICATED ROWS RETURNED FROM IN-SUBQUERY |
| 93 |
# |
| 94 |
# Duplicate weedout didn't remove duplicates due |
| 95 |
# to ha_ndbcluster::position() constructing keys with |
| 96 |
# garbage past actuall length for VARCHAR fields. |
| 97 |
|
| 98 |
CREATE TABLE `i` ( |
| 99 |
`col_int_key` int(11) DEFAULT NULL, |
| 100 |
KEY `col_int_key` (`col_int_key`) |
| 101 |
) ENGINE=ndbcluster |
| 102 |
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"; |
| 103 |
|
| 104 |
INSERT INTO `i` VALUES |
| 105 |
(37), (36), |
| 106 |
(8), (16), (42), (6), (8), |
| 107 |
(11), (21), (13), (3), (41), (44), |
| 108 |
(4), (4) |
| 109 |
; |
| 110 |
|
| 111 |
CREATE TABLE `v` ( |
| 112 |
`col_varchar_10_key` varchar(10) COLLATE latin1_bin DEFAULT NULL, |
| 113 |
`col_datetime_key` datetime DEFAULT NULL, |
| 114 |
`col_varchar_256` varchar(256) COLLATE latin1_bin NOT NULL DEFAULT '', |
| 115 |
`col_int_unique` int(11) DEFAULT NULL, |
| 116 |
`pk` int(11) NOT NULL, |
| 117 |
`col_char_16` char(16) COLLATE latin1_bin DEFAULT NULL, |
| 118 |
`col_varchar_256_unique` varchar(256) COLLATE latin1_bin DEFAULT NULL, |
| 119 |
`col_char_16_unique` char(16) COLLATE latin1_bin DEFAULT NULL, |
| 120 |
`col_int` int(11) DEFAULT NULL, |
| 121 |
`col_datetime_unique` datetime DEFAULT NULL, |
| 122 |
`col_datetime` datetime DEFAULT NULL, |
| 123 |
`col_char_16_key` char(16) COLLATE latin1_bin DEFAULT NULL, |
| 124 |
`col_varchar_256_key` varchar(256) COLLATE latin1_bin DEFAULT NULL, |
| 125 |
`col_int_key` int(11) DEFAULT NULL, |
| 126 |
`col_varchar_10` varchar(10) COLLATE latin1_bin DEFAULT NULL, |
| 127 |
`col_varchar_10_unique` varchar(10) COLLATE latin1_bin DEFAULT NULL, |
| 128 |
PRIMARY KEY (`pk`,`col_varchar_256`), |
| 129 |
KEY `col_int_key` (`col_int_key`) |
| 130 |
) ENGINE=ndbcluster |
| 131 |
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"; |
| 132 |
|
| 133 |
INSERT INTO `v` VALUES |
| 134 |
('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'), |
| 135 |
('how','2009-04-19 21:37:41','something',16,4,'I','fozcghkovsqapozumxaaufx','ofozcghkovsqapoz',16,NULL,NULL,'to','gofozcg',50,'dgofozcghk','back'), |
| 136 |
('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'), |
| 137 |
('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'), |
| 138 |
('pozumxaauf',NULL,'there',48,2,'a','qap','sqapozumxaaufxvm',12,NULL,'2008-08-12 14:41:31','vsqapozumxaaufxv','I\'m',45,'ovsqapozum','kovsqapozu'), |
| 139 |
('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'), |
| 140 |
('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'), |
| 141 |
('a',NULL,'sfrmapuikkd',9,9,'isfrmapuikkdlzaj','he\'s','iisfrmapuikkdlza',5,NULL,NULL,'got','uiisfrmapuikkdlzajntnnwrkdgofoz',28,'nuiisfrmap','cnuiisfrma'), |
| 142 |
('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'), |
| 143 |
('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'), |
| 144 |
('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'), |
| 145 |
('rpqh',NULL,'rrpqhjwzwdqfcnuiisf',10,12,'go','yes','trrpqhjwzwdqfcnu',38,'2007-10-04 05:31:37',NULL,'ytrr','dytrrpqhjwzwdqfcnuii',7,'cdytrrpqhj','say'), |
| 146 |
('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'), |
| 147 |
('just',NULL,'with',22,8,'rmap','something','something',4,NULL,'2006-01-14 09:08:21','or','mean',31,'frmapuikkd','here'), |
| 148 |
('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'); |
| 149 |
|
| 150 |
set ndb_join_pushdown = off; |
| 151 |
|
| 152 |
--replace_column 10 ### |
| 153 |
explain |
| 154 |
SELECT pk, col_int_key |
| 155 |
FROM v AS table1 |
| 156 |
WHERE table1.col_int_key IN ( |
| 157 |
SELECT /*+ SEMIJOIN(DUPSWEEDOUT) */ col_int_key AS field2 FROM i as table1s) |
| 158 |
; |
| 159 |
--sorted_result |
| 160 |
SELECT pk, col_int_key |
| 161 |
FROM v AS table1 |
| 162 |
WHERE table1.col_int_key IN ( |
| 163 |
SELECT /*+ SEMIJOIN(DUPSWEEDOUT) */ col_int_key AS field2 FROM i as table1s) |
| 164 |
; |
| 165 |
|
| 166 |
DROP TABLE IF EXISTS i,v; |
| 167 |
|
| 168 |
--echo End of 5.6 tests |