Debian Codegraph @

mysql-test/suite/ndb_opt/subquery.test

raw
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