Debian Codegraph @

mysql-test/suite/ndb/r/ndb_dbug_tc_select.result

raw
1 #
2 # Test tc select with 2-way joins with unique lookups
3 #
4 create table t1 (
5 a int key,
6 b int
7 ) charset latin1 engine ndb;
8 create table t2 (
9 a int,
10 b int,
11 c int,
12 d int,
13 primary key(a,b),
14 unique(d)
15 ) charset latin1 engine ndb partition by key(a);
16 #
17 # 2-way join tc selection in pk
18 #
19 explain select t2.c from t1,t2 where t1.a=50 and t2.a=t1.a and t2.b=t1.b;
20 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
21 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 const # # Parent of 2 pushed join@1; Using pushed condition (`test`.`t1`.`b` is not null)
22 1 SIMPLE t2 p2 eq_ref PRIMARY PRIMARY 8 const,test.t1.b # # Child of 't1' in pushed join@1
23 Warnings:
24 Note 1003 /* select#1 */ select `test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = 50) and (`test`.`t2`.`a` = 50))
25 #
26 # 2-way join tc selection in unique key
27 #
28 explain select t1.b from t1,t2 where t2.d=50 and t1.a=t2.a;
29 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
30 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY,d d 5 const # # Parent of 2 pushed join@1
31 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 test.t2.a # # Child of 't2' in pushed join@1
32 Warnings:
33 Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t2`.`d` = 50))
34 drop table t1,t2;
35 #
36 # Test tc select with 2-way joins with scan followed by unique lookup
37 #
38 create table t1 (
39 a int,
40 b int,
41 primary key(a,b)
42 ) charset latin1 engine ndb partition by key (a);
43 create table t2 (
44 a int,
45 b int,
46 c int,
47 primary key(a,b)
48 ) charset latin1 engine ndb partition by key (a);
49 explain select t2.c from t1,t2 where t1.a=50 and t2.a=t1.a and t2.b=t1.b;
50 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
51 1 SIMPLE t1 p2 ref PRIMARY PRIMARY 4 const # # Parent of 2 pushed join@1
52 1 SIMPLE t2 p2 eq_ref PRIMARY PRIMARY 8 const,test.t1.b # # Child of 't1' in pushed join@1
53 Warnings:
54 Note 1003 /* select#1 */ select `test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = 50) and (`test`.`t2`.`a` = 50))
55 drop table t1,t2;
56 create table t1(
57 a int ,
58 b varchar(22),
59 c int,
60 primary key(a),
61 unique(b)
62 ) charset latin1 engine ndb;
63 # verify tc select on insert using simple int key
64 # verify tc select on select using simple pk int key
65 # verify tc select on select using simple unique int key
66 # verify tc select on update using simple pk int key
67 # verify tc select on update using simple unique int key
68 # verify tc select on delete using simple pk int key
69 # verify tc select on delete using simple unique int key
70 # Cleanup
71 drop table t1;
72 create table t1(
73 a varchar(1024),
74 b int ,
75 c int,
76 primary key(a),
77 unique(b)
78 ) charset latin1 engine ndb;
79 # verify tc select on insert using simple int key
80 # verify tc select on select using simple pk int key
81 # verify tc select on select using simple unique int key
82 # verify tc select on update using simple pk int key
83 # verify tc select on update using simple unique int key
84 # verify tc select on delete using simple pk int key
85 # verify tc select on delete using simple unique int key
86 # Cleanup
87 drop table t1;
88 create table t1(
89 a varchar(32),
90 b varchar(257),
91 c int,
92 primary key(a),
93 unique(b)
94 ) charset latin1 engine ndb;
95 # verify tc select on insert using simple int key
96 # verify tc select on select using simple pk int key
97 # verify tc select on select using simple unique int key
98 # verify tc select on update using simple pk int key
99 # verify tc select on update using simple unique int key
100 # verify tc select on delete using simple pk int key
101 # verify tc select on delete using simple unique int key
102 # Cleanup
103 drop table t1;
104 create table t1 (
105 a int ,
106 b int,
107 c int,
108 d int,
109 primary key(a,c),
110 unique(c,b)
111 ) charset latin1 engine ndb;
112 # verify tc select on insert using simple int key
113 # verify tc select on select using simple pk int key
114 # verify tc select on select using simple unique int key
115 # verify tc select on update using simple pk int key
116 # verify tc select on update using simple unique int key
117 # verify tc select on delete using simple pk int key
118 # verify tc select on delete using simple unique int key
119 # Cleanup
120 drop table t1;
121 create table t1 (
122 a int,
123 b char(22) ,
124 c char(12),
125 d int,
126 primary key(a,c),
127 unique(c,b)
128 ) charset latin1 engine ndb;
129 # verify tc select on insert using simple int key
130 # verify tc select on select using simple pk int key
131 # verify tc select on select using simple unique int key
132 # verify tc select on update using simple pk int key
133 # verify tc select on update using simple unique int key
134 # verify tc select on delete using simple pk int key
135 # verify tc select on delete using simple unique int key
136 # Cleanup
137 drop table t1;
138 create table t1 (
139 a varchar(32),
140 b int,
141 c varchar(257),
142 d int,
143 primary key(a,c),
144 unique(c,b)
145 ) charset latin1 engine ndb;
146 # verify tc select on insert using simple int key
147 # verify tc select on select using simple pk int key
148 # verify tc select on select using simple unique int key
149 # verify tc select on update using simple pk int key
150 # verify tc select on update using simple unique int key
151 # verify tc select on delete using simple pk int key
152 # verify tc select on delete using simple unique int key
153 # Cleanup
154 drop table t1;
155 create table t1 (
156 a int ,
157 b int,
158 c int,
159 d int,
160 e int,
161 primary key(a,c,e),
162 unique(c,b,d)
163 ) charset latin1 engine ndb partition by key(c,e);
164 # verify tc select on insert using simple int key
165 # verify tc select on select using simple pk int key
166 # verify tc select on select using simple unique int key
167 # verify tc select on update using simple pk int key
168 # verify tc select on update using simple unique int key
169 # verify tc select on delete using simple pk int key
170 # verify tc select on delete using simple unique int key
171 # Cleanup
172 drop table t1;
173 create table t1 (
174 a int,
175 b varchar(22) ,
176 c char(12),
177 d int,
178 e varchar(257),
179 primary key(a,c,e),
180 unique(c,b,d)
181 ) charset latin1 engine ndb partition by key(c,e);
182 # verify tc select on insert using simple int key
183 # verify tc select on select using simple pk int key
184 # verify tc select on select using simple unique int key
185 # verify tc select on update using simple pk int key
186 # verify tc select on update using simple unique int key
187 # verify tc select on delete using simple pk int key
188 # verify tc select on delete using simple unique int key
189 # Cleanup
190 drop table t1;
191 create table t1 (
192 a varchar(1024),
193 b char(22),
194 c varchar(257),
195 d int,
196 e char(12),
197 primary key(a,c,e),
198 unique(c,b,d)
199 ) charset latin1 engine ndb partition by key(c,e);
200 # verify tc select on insert using simple int key
201 # verify tc select on select using simple pk int key
202 # verify tc select on select using simple unique int key
203 # verify tc select on update using simple pk int key
204 # verify tc select on update using simple unique int key
205 # verify tc select on delete using simple pk int key
206 # verify tc select on delete using simple unique int key
207 # Cleanup
208 drop table t1;