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