Debian Codegraph @

mysql-test/suite/ndb_opt/spj_dict.result

raw
1 use test;
2 create table parent(a int primary key, b int not null, key(b)) engine=myisam;
3 create table child(a int, b int, primary key(a,b), key(b)) engine=myisam;
4 insert into parent values (1,1), (2,2), (3,3), (4,4);
5 insert into parent select a+4, b+4 from parent;
6 insert into parent select a+8, b+8 from parent;
7 insert into parent select a+16, b+16 from parent;
8 insert into parent select a+32, b+32 from parent;
9 insert into parent select a+64, b+64 from parent;
10 insert into parent select a+128, b+128 from parent;
11 insert into parent select a+256, b+256 from parent;
12 insert into parent select a+512, b+512 from parent;
13 insert into parent select a+1024, b+1024 from parent;
14 insert into parent select a+(2*1024), b+(2*1024) from parent;
15 insert into parent select a+(4*1024), b+(4*1024) from parent;
16 insert into parent select a+(8*1024), b+(8*1024) from parent;
17 insert into parent select a+(16*1024), b+(16*1024) from parent;
18 insert into child select * from parent;
19 alter table parent engine=ndb;
20 alter table child engine=ndb;
21 show variables like 'server_id';
22 Variable_name Value
23 server_id 1
24 set ndb_join_pushdown = true;
25 ============================================
26 Early 'online' drop of REF'ed child's KEY b.
27 ============================================
28 explain select straight_join count(*)
29 from parent join child
30 on child.b = parent.a
31 where parent.b > 5;
32 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
33 1 SIMPLE parent p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY,b b 4 NULL ### ### Parent of 2 pushed join@1; Using pushed condition (`test`.`parent`.`b` > 5); Using MRR
34 1 SIMPLE child p0,p1,p2,p3,p4,p5,p6,p7 ref b b 4 test.parent.a ### ### Child of 'parent' in pushed join@1
35 Warnings:
36 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
37 alter table child algorithm=inplace, drop key b;
38 commit;
39 alter table child add key(b);
40 ============================================
41 Late 'online' drop of REF'ed child's KEY b.
42 ============================================
43 explain select straight_join count(*)
44 from parent join child
45 on child.b = parent.a
46 where parent.b > 5;
47 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
48 1 SIMPLE parent p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY,b b 4 NULL ### ### Parent of 2 pushed join@1; Using pushed condition (`test`.`parent`.`b` > 5); Using MRR
49 1 SIMPLE child p0,p1,p2,p3,p4,p5,p6,p7 ref b b 4 test.parent.a ### ### Child of 'parent' in pushed join@1
50 Warnings:
51 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
52 alter table child algorithm=inplace, drop key b;
53 commit;
54 alter table child add key(b);
55 ============================================
56 Early drop of EQ_REF'ed child's PRIMARY KEY.
57 ============================================
58 explain select straight_join count(*)
59 from parent join child
60 on child.a = parent.a and child.b = parent.b
61 where parent.b > 5;
62 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
63 1 SIMPLE parent p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY,b b 4 NULL ### ### Parent of 2 pushed join@1; Using pushed condition (`test`.`parent`.`b` > 5); Using MRR
64 1 SIMPLE child p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY,b PRIMARY 8 test.parent.a,test.parent.b ### ### Child of 'parent' in pushed join@1
65 Warnings:
66 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`b`) and (`test`.`child`.`a` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
67 alter table child drop primary key;
68 commit;
69 alter table child add primary key(a,b);
70 =============================================================
71 Drop of EQ_REF'ed child's PRIMARY KEY after executed a while.
72 =============================================================
73 explain select straight_join count(*)
74 from parent join child
75 on child.a = parent.a and child.b = parent.b
76 where parent.b > 5;
77 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
78 1 SIMPLE parent p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY,b b 4 NULL ### ### Parent of 2 pushed join@1; Using pushed condition (`test`.`parent`.`b` > 5); Using MRR
79 1 SIMPLE child p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY,b PRIMARY 8 test.parent.a,test.parent.b ### ### Child of 'parent' in pushed join@1
80 Warnings:
81 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`b`) and (`test`.`child`.`a` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
82 alter table child drop primary key;
83 commit;
84 alter table child add primary key(a,b);
85 ===========================================================
86 ONLINE ALTER: Rename table to make it go away - temporarily
87 ===========================================================
88 explain select straight_join count(*)
89 from parent join child
90 on child.a = parent.a and child.b = parent.b
91 where parent.b > 5;
92 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
93 1 SIMPLE parent p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY,b b 4 NULL ### ### Parent of 2 pushed join@1; Using pushed condition (`test`.`parent`.`b` > 5); Using MRR
94 1 SIMPLE child p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY,b PRIMARY 8 test.parent.a,test.parent.b ### ### Child of 'parent' in pushed join@1
95 Warnings:
96 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`b`) and (`test`.`child`.`a` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
97 alter table child algorithm=inplace, rename to child_orig;
98 commit;
99 alter table child_orig rename to child;
100 ============================================================
101 OFFLINE ALTER: Rename table to make it go away - temporarily
102 ============================================================
103 explain select straight_join count(*)
104 from parent join child
105 on child.a = parent.a and child.b = parent.b
106 where parent.b > 5;
107 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
108 1 SIMPLE parent p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY,b b 4 NULL ### ### Parent of 2 pushed join@1; Using pushed condition (`test`.`parent`.`b` > 5); Using MRR
109 1 SIMPLE child p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY,b PRIMARY 8 test.parent.a,test.parent.b ### ### Child of 'parent' in pushed join@1
110 Warnings:
111 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`b`) and (`test`.`child`.`a` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
112 alter table child algorithm=copy, rename to child_orig;
113 commit;
114 alter table child_orig rename to child;
115 =================================================================
116 ONLINE ALTER: drop + recreate table 'child' key b in use by query
117 =================================================================
118 explain select straight_join count(*)
119 from parent join child
120 on child.b = parent.a
121 where parent.b > 5;
122 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
123 1 SIMPLE parent p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY,b b 4 NULL ### ### Parent of 2 pushed join@1; Using pushed condition (`test`.`parent`.`b` > 5); Using MRR
124 1 SIMPLE child p0,p1,p2,p3,p4,p5,p6,p7 ref b b 4 test.parent.a ### ### Child of 'parent' in pushed join@1
125 Warnings:
126 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
127 alter table child algorithm=inplace, drop key b;
128 alter table child add key(b);
129 ==================================================================
130 ONLINE ALTER: drop + recreate table 'parent' key b in use by query
131 ==================================================================
132 explain select straight_join count(*)
133 from parent join child
134 on child.a = parent.a and child.b = parent.b
135 where parent.b > 5;
136 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
137 1 SIMPLE parent p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY,b b 4 NULL ### ### Parent of 2 pushed join@1; Using pushed condition (`test`.`parent`.`b` > 5); Using MRR
138 1 SIMPLE child p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY,b PRIMARY 8 test.parent.a,test.parent.b ### ### Child of 'parent' in pushed join@1
139 Warnings:
140 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`b`) and (`test`.`child`.`a` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
141 alter table parent algorithm=inplace, drop key b;
142 alter table parent add key(b);
143 ===============================================
144 OFFLINE ALTER: add + drop column c from 'child'
145 ===============================================
146 explain select straight_join count(*)
147 from parent join child
148 on child.a = parent.a and child.b = parent.b
149 where parent.b > 5;
150 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
151 1 SIMPLE parent p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY,b b 4 NULL ### ### Parent of 2 pushed join@1; Using pushed condition (`test`.`parent`.`b` > 5); Using MRR
152 1 SIMPLE child p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY,b PRIMARY 8 test.parent.a,test.parent.b ### ### Child of 'parent' in pushed join@1
153 Warnings:
154 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`b`) and (`test`.`child`.`a` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
155 alter table child algorithm=copy, add column c int;
156 alter table child drop column c;
157 ==========================================================
158 OFFLINE ALTER: drop + recreate primary key(a,b) for 'child'
159 ==========================================================
160 explain select straight_join count(*)
161 from parent join child
162 on child.a = parent.a
163 where parent.b > 5;
164 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
165 1 SIMPLE parent p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY,b b 4 NULL ### ### Parent of 2 pushed join@1; Using pushed condition (`test`.`parent`.`b` > 5); Using MRR
166 1 SIMPLE child p0,p1,p2,p3,p4,p5,p6,p7 ref PRIMARY PRIMARY 4 test.parent.a ### ### Child of 'parent' in pushed join@1
167 Warnings:
168 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`a` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
169 alter table child algorithm=copy, drop primary key;
170 alter table child add primary key(a,b);
171 ==================
172 DROP TABLE 'child'
173 ==================
174 explain select straight_join count(*)
175 from parent join child
176 on child.a = parent.a and child.b = parent.b
177 where parent.b > 5;
178 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
179 1 SIMPLE parent p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY,b b 4 NULL ### ### Parent of 2 pushed join@1; Using pushed condition (`test`.`parent`.`b` > 5); Using MRR
180 1 SIMPLE child p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY,b PRIMARY 8 test.parent.a,test.parent.b ### ### Child of 'parent' in pushed join@1
181 Warnings:
182 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`b`) and (`test`.`child`.`a` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
183 drop table child;
184 ===========
185 Cleaning up
186 ===========
187 drop table if exists parent;