Merge lp:~vkolesnikov/pbxt/pbxt-bug-349176 into lp:pbxt
- pbxt-bug-349176
- Merge into trunk
Proposed by
Vladimir Kolesnikov
Status: | Merged | ||||
---|---|---|---|---|---|
Merged at revision: | not available | ||||
Proposed branch: | lp:~vkolesnikov/pbxt/pbxt-bug-349176 | ||||
Merge into: | lp:pbxt | ||||
Diff against target: | None lines | ||||
To merge this branch: | bzr merge lp:~vkolesnikov/pbxt/pbxt-bug-349176 | ||||
Related bugs: |
|
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
PBXT Core | Pending | ||
Review via email: mp+5288@code.launchpad.net |
Commit message
Description of the change
To post a comment you must log in.
Preview Diff
[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1 | === modified file 'ChangeLog' |
2 | --- ChangeLog 2009-03-31 14:47:43 +0000 |
3 | +++ ChangeLog 2009-04-04 01:03:03 +0000 |
4 | @@ -3,6 +3,8 @@ |
5 | |
6 | ------- 1.0.08 RC - Not yet released |
7 | |
8 | +RN234: Fixed bug 349176: a compiler warning |
9 | + |
10 | RN233: Fixed bugs which occur when PBXT is used together with PBMS (BLOB Streaming engine). |
11 | |
12 | RN232: Merged Drizzle-specific changes into the main tree. |
13 | |
14 | === modified file 'src/cache_xt.h' |
15 | --- src/cache_xt.h 2009-03-02 13:10:56 +0000 |
16 | +++ src/cache_xt.h 2009-04-04 01:03:03 +0000 |
17 | @@ -45,8 +45,8 @@ |
18 | #define IDX_CAC_BLOCK_CLEAN 1 |
19 | #define IDX_CAC_BLOCK_DIRTY 2 |
20 | |
21 | -typedef enum XTPageLockType { XT_LOCK_READ, XT_LOCK_WRITE, XT_XLOCK_LEAF }; |
22 | -typedef enum XTPageUnlockType { XT_UNLOCK_NONE, XT_UNLOCK_READ, XT_UNLOCK_WRITE, XT_UNLOCK_R_UPDATE, XT_UNLOCK_W_UPDATE }; |
23 | +enum XTPageLockType { XT_LOCK_READ, XT_LOCK_WRITE, XT_XLOCK_LEAF }; |
24 | +enum XTPageUnlockType { XT_UNLOCK_NONE, XT_UNLOCK_READ, XT_UNLOCK_WRITE, XT_UNLOCK_R_UPDATE, XT_UNLOCK_W_UPDATE }; |
25 | |
26 | /* A block is X locked if it is being changed or freed. |
27 | * A block is S locked if it is being read. |
28 | |
29 | === modified file 'src/ha_pbxt.cc' |
30 | --- src/ha_pbxt.cc 2009-04-03 10:08:43 +0000 |
31 | +++ src/ha_pbxt.cc 2009-04-03 19:39:12 +0000 |
32 | @@ -91,16 +91,16 @@ |
33 | //#define PRINT_STATEMENTS |
34 | #endif |
35 | |
36 | +#ifndef DRIZZLED |
37 | static handler *pbxt_create_handler(handlerton *hton, TABLE_SHARE *table, MEM_ROOT *mem_root); |
38 | static int pbxt_init(void *p); |
39 | static int pbxt_end(void *p); |
40 | -#ifndef DRIZZLED |
41 | static int pbxt_panic(handlerton *hton, enum ha_panic_function flag); |
42 | -#endif |
43 | static void pbxt_drop_database(handlerton *hton, char *path); |
44 | static int pbxt_close_connection(handlerton *hton, THD* thd); |
45 | static int pbxt_commit(handlerton *hton, THD *thd, bool all); |
46 | static int pbxt_rollback(handlerton *hton, THD *thd, bool all); |
47 | +#endif |
48 | static void ha_aquire_exclusive_use(XTThreadPtr self, XTSharePtr share, ha_pbxt *mine); |
49 | static void ha_release_exclusive_use(XTThreadPtr self, XTSharePtr share); |
50 | static void ha_close_open_tables(XTThreadPtr self, XTSharePtr share, ha_pbxt *mine); |
51 | @@ -979,9 +979,13 @@ |
52 | /* |
53 | * Outout the PBXT status. Return FALSE on error. |
54 | */ |
55 | +#ifdef DRIZZLED |
56 | +bool PBXTStorageEngine::show_status(Session *thd, stat_print_fn *stat_print, enum ha_stat_type) |
57 | +#else |
58 | static bool pbxt_show_status(handlerton *hton __attribute__((unused)), THD* thd, |
59 | stat_print_fn* stat_print, |
60 | enum ha_stat_type stat_type __attribute__((unused))) |
61 | +#endif |
62 | { |
63 | XTThreadPtr self; |
64 | int err = 0; |
65 | @@ -1045,22 +1049,23 @@ |
66 | |
67 | xt_p_mutex_init_with_autoname(&pbxt_database_mutex, NULL); |
68 | |
69 | +#ifdef DRIZZLED |
70 | + StorageEngine **engine= static_cast<StorageEngine **>(p); |
71 | + pbxt_hton= new PBXTStorageEngine(std::string("PBXT")); |
72 | + *engine = pbxt_hton; |
73 | +#else |
74 | pbxt_hton = (handlerton *) p; |
75 | pbxt_hton->state = SHOW_OPTION_YES; |
76 | -#ifndef DRIZZLED |
77 | pbxt_hton->db_type = DB_TYPE_PBXT; // Wow! I have my own! |
78 | -#endif |
79 | pbxt_hton->close_connection = pbxt_close_connection; /* close_connection, cleanup thread related data. */ |
80 | pbxt_hton->commit = pbxt_commit; /* commit */ |
81 | pbxt_hton->rollback = pbxt_rollback; /* rollback */ |
82 | pbxt_hton->create = pbxt_create_handler; /* Create a new handler */ |
83 | pbxt_hton->drop_database = pbxt_drop_database; /* Drop a database */ |
84 | -#ifndef DRIZZLED |
85 | pbxt_hton->panic = pbxt_panic; /* Panic call */ |
86 | -#endif |
87 | pbxt_hton->show_status = pbxt_show_status; |
88 | pbxt_hton->flags = HTON_NO_FLAGS; /* HTON_CAN_RECREATE - Without this flags TRUNCATE uses delete_all_rows() */ |
89 | - |
90 | +#endif |
91 | if (!xt_init_logging()) /* Initialize logging */ |
92 | goto error_1; |
93 | |
94 | @@ -1262,8 +1267,14 @@ |
95 | /* |
96 | * Kill the PBXT thread associated with the MySQL thread. |
97 | */ |
98 | +#ifdef DRIZZLED |
99 | +int PBXTStorageEngine::close_connection(Session *thd) |
100 | +{ |
101 | + PBXTStorageEngine * const hton = this; |
102 | +#else |
103 | static int pbxt_close_connection(handlerton *hton, THD* thd) |
104 | { |
105 | +#endif |
106 | XTThreadPtr self; |
107 | #ifdef XT_STREAMING |
108 | XTExceptionRec e; |
109 | @@ -1290,7 +1301,11 @@ |
110 | * when the last PBXT table was removed from the |
111 | * database. |
112 | */ |
113 | +#ifdef DRIZZLED |
114 | +void PBXTStorageEngine::drop_database(char *) |
115 | +#else |
116 | static void pbxt_drop_database(handlerton *hton __attribute__((unused)), char *path __attribute__((unused))) |
117 | +#endif |
118 | { |
119 | XT_TRACE_CALL(); |
120 | } |
121 | @@ -1317,8 +1332,14 @@ |
122 | * pbxt_thr is a pointer the the PBXT thread structure. |
123 | * |
124 | */ |
125 | +#ifdef DRIZZLED |
126 | +int PBXTStorageEngine::commit(Session *thd, bool all) |
127 | +{ |
128 | + PBXTStorageEngine * const hton = this; |
129 | +#else |
130 | static int pbxt_commit(handlerton *hton, THD *thd, bool all) |
131 | { |
132 | +#endif |
133 | int err = 0; |
134 | XTThreadPtr self; |
135 | |
136 | @@ -1343,8 +1364,14 @@ |
137 | return err; |
138 | } |
139 | |
140 | +#ifdef DRIZZLED |
141 | +int PBXTStorageEngine::rollback(Session *thd, bool all) |
142 | +{ |
143 | + PBXTStorageEngine * const hton = this; |
144 | +#else |
145 | static int pbxt_rollback(handlerton *hton, THD *thd, bool all) |
146 | { |
147 | +#endif |
148 | int err = 0; |
149 | XTThreadPtr self; |
150 | |
151 | @@ -1377,8 +1404,14 @@ |
152 | return 0; |
153 | } |
154 | |
155 | +#ifdef DRIZZLED |
156 | +handler *PBXTStorageEngine::create(TABLE_SHARE *table, MEM_ROOT *mem_root) |
157 | +{ |
158 | + PBXTStorageEngine * const hton = this; |
159 | +#else |
160 | static handler *pbxt_create_handler(handlerton *hton, TABLE_SHARE *table, MEM_ROOT *mem_root) |
161 | { |
162 | +#endif |
163 | if (table && XTSystemTableShare::isSystemTable(table->path.str)) |
164 | return new (mem_root) ha_xtsys(hton, table); |
165 | else |
166 | @@ -1631,7 +1664,12 @@ |
167 | |
168 | int pbxt_init_statitics(void *p) |
169 | { |
170 | +#ifdef DRIZZLED |
171 | + ST_SCHEMA_TABLE *schema = (ST_SCHEMA_TABLE *)xt_calloc_ns(sizeof(ST_SCHEMA_TABLE)); |
172 | + *(ST_SCHEMA_TABLE **)p = schema; |
173 | +#else |
174 | ST_SCHEMA_TABLE *schema = (ST_SCHEMA_TABLE *) p; |
175 | +#endif |
176 | schema->fields_info = pbxt_statistics_fields_info; |
177 | schema->fill_table = pbxt_statistics_fill_table; |
178 | |
179 | @@ -1647,6 +1685,9 @@ |
180 | |
181 | int pbxt_exit_statitics(void *p __attribute__((unused))) |
182 | { |
183 | +#ifdef DRIZZLED |
184 | + xt_free_ns(p); |
185 | +#endif |
186 | return(0); |
187 | } |
188 | |
189 | @@ -2887,9 +2928,12 @@ |
190 | xt_idx_prep_key(ind, &search_key, ((find_flag == HA_READ_AFTER_KEY) ? XT_SEARCH_AFTER_KEY : 0) | prefix, (xtWord1 *) key, key_len); |
191 | if (!xt_idx_search(pb_open_tab, ind, &search_key)) |
192 | err = ha_log_pbxt_thread_error_for_mysql(pb_ignore_dup_key); |
193 | - else |
194 | + else { |
195 | err = xt_index_next_read(pb_open_tab, ind, pb_key_read, |
196 | (find_flag == HA_READ_KEY_EXACT || find_flag == HA_READ_PREFIX) ? &search_key : NULL, buf); |
197 | + if (err == HA_ERR_END_OF_FILE && find_flag == HA_READ_AFTER_KEY) |
198 | + err = HA_ERR_KEY_NOT_FOUND; |
199 | + } |
200 | break; |
201 | } |
202 | |
203 | @@ -3445,7 +3489,7 @@ |
204 | if (ind->mi_seg_count == 1 && (ind->mi_flags & HA_NOSAME)) |
205 | rec_per_key = 1; |
206 | else { |
207 | - |
208 | + rec_per_key = 1; |
209 | } |
210 | for (u_int j = 0; j < table->key_info[i].key_parts; j++) |
211 | table->key_info[i].rec_per_key[j] = (ulong) rec_per_key; |
212 | @@ -3570,6 +3614,8 @@ |
213 | if (pb_open_tab) |
214 | pb_open_tab->ot_table->tab_locks.xt_make_lock_permanent(pb_open_tab, &self->st_lock_list); |
215 | } |
216 | + if (pb_open_tab) |
217 | + pb_open_tab->ot_for_update = 0; |
218 | break; |
219 | case HA_EXTRA_KEYREAD: |
220 | /* This means we so not need to read the entire record. */ |
221 | @@ -3993,8 +4039,10 @@ |
222 | * (or update statement) just saw. |
223 | */ |
224 | if (pb_open_tab) { |
225 | - if (pb_open_tab->ot_for_update) |
226 | + if (pb_open_tab->ot_for_update) { |
227 | self->st_visible_time = self->st_database->db_xn_end_time; |
228 | + pb_open_tab->ot_for_update = 0; |
229 | + } |
230 | |
231 | if (pb_share->sh_recalc_selectivity) { |
232 | if ((pb_share->sh_table->tab_row_eof_id - 1 - pb_share->sh_table->tab_row_fnum) >= 200) { |
233 | @@ -4592,10 +4640,17 @@ |
234 | freer_(); // ha_unget_share(share) |
235 | } |
236 | catch_(b) { |
237 | - /* If the table does not exist, just log the error and continue... */ |
238 | + /* In MySQL if the table does not exist, just log the error and continue. This is |
239 | + * needed to delete table in the case when CREATE TABLE fails and no PBXT disk |
240 | + * structures were created. |
241 | + * Drizzle unlike MySQL iterates over all handlers and tries to delete table. It |
242 | + * stops after when a handler returns TRUE, so in Drizzle we need to report error. |
243 | + */ |
244 | +#ifndef DRIZZLED |
245 | if (self->t_exception.e_xt_err == XT_ERR_TABLE_NOT_FOUND) |
246 | xt_log_and_clear_exception(self); |
247 | else |
248 | +#endif |
249 | throw_(); |
250 | } |
251 | cont_(b); |
252 | @@ -4619,6 +4674,10 @@ |
253 | } |
254 | catch_(a) { |
255 | err = xt_ha_pbxt_thread_error_for_mysql(thd, self, pb_ignore_dup_key); |
256 | +#ifdef DRIZZLED |
257 | + if (err == HA_ERR_NO_SUCH_TABLE) |
258 | + err = ENOENT; |
259 | +#endif |
260 | } |
261 | cont_(a); |
262 | |
263 | |
264 | === modified file 'src/ha_pbxt.h' |
265 | --- src/ha_pbxt.h 2009-03-25 01:43:40 +0000 |
266 | +++ src/ha_pbxt.h 2009-04-03 19:39:12 +0000 |
267 | @@ -28,7 +28,7 @@ |
268 | #ifdef DRIZZLED |
269 | #include <drizzled/common.h> |
270 | #include <drizzled/handler.h> |
271 | -#include <drizzled/handlerton.h> |
272 | +#include <drizzled/plugin/storage_engine.h> |
273 | #include <mysys/thr_lock.h> |
274 | #else |
275 | #include "mysql_priv.h" |
276 | @@ -51,6 +51,25 @@ |
277 | |
278 | class ha_pbxt; |
279 | |
280 | +#ifdef DRIZZLED |
281 | + |
282 | +class PBXTStorageEngine : public StorageEngine { |
283 | +public: |
284 | + PBXTStorageEngine(std::string name_arg) |
285 | + : StorageEngine(name_arg, HTON_NO_FLAGS) {} |
286 | + |
287 | + /* override */ int close_connection(Session *); |
288 | + /* override */ int commit(Session *, bool); |
289 | + /* override */ int rollback(Session *, bool); |
290 | + /* override */ handler *create(TABLE_SHARE *, MEM_ROOT *); |
291 | + /* override */ void drop_database(char *); |
292 | + /* override */ bool show_status(Session *, stat_print_fn *, enum ha_stat_type); |
293 | +}; |
294 | + |
295 | +typedef PBXTStorageEngine handlerton; |
296 | + |
297 | +#endif |
298 | + |
299 | extern handlerton *pbxt_hton; |
300 | |
301 | /* |
302 | |
303 | === modified file 'src/myxt_xt.cc' |
304 | --- src/myxt_xt.cc 2009-03-31 12:33:50 +0000 |
305 | +++ src/myxt_xt.cc 2009-04-03 19:39:12 +0000 |
306 | @@ -1989,7 +1989,9 @@ |
307 | * plugin_shutdown() and reap_plugins() in sql_plugin.cc |
308 | * from doing their job on shutdown! |
309 | */ |
310 | +#ifndef DRIZZLED |
311 | plugin_unlock(NULL, table->s->db_plugin); |
312 | +#endif |
313 | table->s->db_plugin = NULL; |
314 | return table; |
315 | } |
316 | |
317 | === modified file 'src/table_xt.cc' |
318 | --- src/table_xt.cc 2009-04-02 20:27:49 +0000 |
319 | +++ src/table_xt.cc 2009-04-03 19:39:12 +0000 |
320 | @@ -35,7 +35,7 @@ |
321 | #include <drizzled/common.h> |
322 | #include <mysys/thr_lock.h> |
323 | #include <drizzled/dtcollation.h> |
324 | -#include <drizzled/handlerton.h> |
325 | +#include <drizzled/plugin/storage_engine.h> |
326 | #else |
327 | #include "mysql_priv.h" |
328 | #endif |
329 | @@ -1598,6 +1598,14 @@ |
330 | if (!self->st_ignore_fkeys) |
331 | can_drop = tab->tab_dic.dic_table->checkCanDrop(); |
332 | } |
333 | +#ifdef DRIZZLED |
334 | + /* See the comment in ha_pbxt::delete_table regarding different implmentation of DROP TABLE |
335 | + * in MySQL and Drizzle |
336 | + */ |
337 | + else { |
338 | + xt_throw_xterr(XT_CONTEXT, XT_ERR_TABLE_NOT_FOUND); |
339 | + } |
340 | +#endif |
341 | |
342 | if (can_drop) { |
343 | if (tab_id) { |
344 | |
345 | === modified file 'src/xt_defs.h' |
346 | --- src/xt_defs.h 2009-03-25 01:43:40 +0000 |
347 | +++ src/xt_defs.h 2009-04-03 19:39:12 +0000 |
348 | @@ -750,6 +750,9 @@ |
349 | (((uint32_t) (((const unsigned char*) (A))[1])) << 16) +\ |
350 | (((uint32_t) (((const unsigned char*) (A))[0])) << 24))) |
351 | |
352 | +class PBXTStorageEngine; |
353 | +typedef PBXTStorageEngine handlerton; |
354 | + |
355 | #else // DRIZZLED |
356 | /* The MySQL case: */ |
357 | #if MYSQL_VERSION_ID >= 60008 |
358 | |
359 | === modified file 'test/mysql-test/r/alter_table.result' |
360 | --- test/mysql-test/r/alter_table.result 2008-11-01 09:01:35 +0000 |
361 | +++ test/mysql-test/r/alter_table.result 2009-04-03 19:39:12 +0000 |
362 | @@ -126,23 +126,23 @@ |
363 | alter table t1 disable keys; |
364 | show keys from t1; |
365 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
366 | -t1 0 n1 1 n1 NULL 0 NULL NULL BTREE |
367 | -t1 1 n1_2 1 n1 NULL NULL NULL NULL BTREE |
368 | -t1 1 n1_2 2 n2 NULL NULL NULL NULL YES BTREE |
369 | -t1 1 n1_2 3 n3 NULL NULL NULL NULL YES BTREE |
370 | -t1 1 n1_2 4 n4 NULL NULL NULL NULL YES BTREE |
371 | -t1 1 n2 1 n2 NULL NULL NULL NULL YES BTREE |
372 | -t1 1 n2 2 n3 NULL NULL NULL NULL YES BTREE |
373 | -t1 1 n2 3 n4 NULL NULL NULL NULL YES BTREE |
374 | -t1 1 n2 4 n1 NULL NULL NULL NULL BTREE |
375 | -t1 1 n3 1 n3 NULL NULL NULL NULL YES BTREE |
376 | -t1 1 n3 2 n4 NULL NULL NULL NULL YES BTREE |
377 | -t1 1 n3 3 n1 NULL NULL NULL NULL BTREE |
378 | -t1 1 n3 4 n2 NULL NULL NULL NULL YES BTREE |
379 | -t1 1 n4 1 n4 NULL NULL NULL NULL YES BTREE |
380 | -t1 1 n4 2 n1 NULL NULL NULL NULL BTREE |
381 | -t1 1 n4 3 n2 NULL NULL NULL NULL YES BTREE |
382 | -t1 1 n4 4 n3 NULL NULL NULL NULL YES BTREE |
383 | +t1 0 n1 1 n1 A 0 NULL NULL BTREE |
384 | +t1 1 n1_2 1 n1 A 0 NULL NULL BTREE |
385 | +t1 1 n1_2 2 n2 A 0 NULL NULL YES BTREE |
386 | +t1 1 n1_2 3 n3 A 0 NULL NULL YES BTREE |
387 | +t1 1 n1_2 4 n4 A 0 NULL NULL YES BTREE |
388 | +t1 1 n2 1 n2 A 0 NULL NULL YES BTREE |
389 | +t1 1 n2 2 n3 A 0 NULL NULL YES BTREE |
390 | +t1 1 n2 3 n4 A 0 NULL NULL YES BTREE |
391 | +t1 1 n2 4 n1 A 0 NULL NULL BTREE |
392 | +t1 1 n3 1 n3 A 0 NULL NULL YES BTREE |
393 | +t1 1 n3 2 n4 A 0 NULL NULL YES BTREE |
394 | +t1 1 n3 3 n1 A 0 NULL NULL BTREE |
395 | +t1 1 n3 4 n2 A 0 NULL NULL YES BTREE |
396 | +t1 1 n4 1 n4 A 0 NULL NULL YES BTREE |
397 | +t1 1 n4 2 n1 A 0 NULL NULL BTREE |
398 | +t1 1 n4 3 n2 A 0 NULL NULL YES BTREE |
399 | +t1 1 n4 4 n3 A 0 NULL NULL YES BTREE |
400 | insert into t1 values(10,RAND()*1000,RAND()*1000,RAND()); |
401 | insert into t1 values(9,RAND()*1000,RAND()*1000,RAND()); |
402 | insert into t1 values(8,RAND()*1000,RAND()*1000,RAND()); |
403 | @@ -156,23 +156,23 @@ |
404 | alter table t1 enable keys; |
405 | show keys from t1; |
406 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
407 | -t1 0 n1 1 n1 NULL 10 NULL NULL BTREE |
408 | -t1 1 n1_2 1 n1 NULL NULL NULL NULL BTREE |
409 | -t1 1 n1_2 2 n2 NULL NULL NULL NULL YES BTREE |
410 | -t1 1 n1_2 3 n3 NULL NULL NULL NULL YES BTREE |
411 | -t1 1 n1_2 4 n4 NULL NULL NULL NULL YES BTREE |
412 | -t1 1 n2 1 n2 NULL NULL NULL NULL YES BTREE |
413 | -t1 1 n2 2 n3 NULL NULL NULL NULL YES BTREE |
414 | -t1 1 n2 3 n4 NULL NULL NULL NULL YES BTREE |
415 | -t1 1 n2 4 n1 NULL NULL NULL NULL BTREE |
416 | -t1 1 n3 1 n3 NULL NULL NULL NULL YES BTREE |
417 | -t1 1 n3 2 n4 NULL NULL NULL NULL YES BTREE |
418 | -t1 1 n3 3 n1 NULL NULL NULL NULL BTREE |
419 | -t1 1 n3 4 n2 NULL NULL NULL NULL YES BTREE |
420 | -t1 1 n4 1 n4 NULL NULL NULL NULL YES BTREE |
421 | -t1 1 n4 2 n1 NULL NULL NULL NULL BTREE |
422 | -t1 1 n4 3 n2 NULL NULL NULL NULL YES BTREE |
423 | -t1 1 n4 4 n3 NULL NULL NULL NULL YES BTREE |
424 | +t1 0 n1 1 n1 A 10 NULL NULL BTREE |
425 | +t1 1 n1_2 1 n1 A 10 NULL NULL BTREE |
426 | +t1 1 n1_2 2 n2 A 10 NULL NULL YES BTREE |
427 | +t1 1 n1_2 3 n3 A 10 NULL NULL YES BTREE |
428 | +t1 1 n1_2 4 n4 A 10 NULL NULL YES BTREE |
429 | +t1 1 n2 1 n2 A 10 NULL NULL YES BTREE |
430 | +t1 1 n2 2 n3 A 10 NULL NULL YES BTREE |
431 | +t1 1 n2 3 n4 A 10 NULL NULL YES BTREE |
432 | +t1 1 n2 4 n1 A 10 NULL NULL BTREE |
433 | +t1 1 n3 1 n3 A 10 NULL NULL YES BTREE |
434 | +t1 1 n3 2 n4 A 10 NULL NULL YES BTREE |
435 | +t1 1 n3 3 n1 A 10 NULL NULL BTREE |
436 | +t1 1 n3 4 n2 A 10 NULL NULL YES BTREE |
437 | +t1 1 n4 1 n4 A 10 NULL NULL YES BTREE |
438 | +t1 1 n4 2 n1 A 10 NULL NULL BTREE |
439 | +t1 1 n4 3 n2 A 10 NULL NULL YES BTREE |
440 | +t1 1 n4 4 n3 A 10 NULL NULL YES BTREE |
441 | drop table t1; |
442 | create table t1 (i int unsigned not null auto_increment primary key); |
443 | alter table t1 rename t2; |
444 | @@ -286,17 +286,17 @@ |
445 | alter table t1 add unique (a,b), add key (b); |
446 | show keys from t1; |
447 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
448 | -t1 0 a 1 a A NULL NULL NULL YES BTREE |
449 | -t1 0 a 2 b A NULL NULL NULL YES BTREE |
450 | -t1 1 b 1 b A NULL NULL NULL YES BTREE |
451 | +t1 0 a 1 a A 300 NULL NULL YES BTREE |
452 | +t1 0 a 2 b A 300 NULL NULL YES BTREE |
453 | +t1 1 b 1 b A 300 NULL NULL YES BTREE |
454 | analyze table t1; |
455 | Table Op Msg_type Msg_text |
456 | test.t1 analyze status OK |
457 | show keys from t1; |
458 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
459 | -t1 0 a 1 a A NULL NULL NULL YES BTREE |
460 | -t1 0 a 2 b A NULL NULL NULL YES BTREE |
461 | -t1 1 b 1 b A NULL NULL NULL YES BTREE |
462 | +t1 0 a 1 a A 300 NULL NULL YES BTREE |
463 | +t1 0 a 2 b A 300 NULL NULL YES BTREE |
464 | +t1 1 b 1 b A 300 NULL NULL YES BTREE |
465 | drop table t1; |
466 | CREATE TABLE t1 (i int(10), index(i) ); |
467 | ALTER TABLE t1 DISABLE KEYS; |
468 | @@ -545,37 +545,37 @@ |
469 | create table t1 (a int, key(a)); |
470 | show indexes from t1; |
471 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
472 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
473 | +t1 1 a 1 a A 0 NULL NULL YES BTREE |
474 | "this used not to disable the index" |
475 | alter table t1 modify a int, disable keys; |
476 | show indexes from t1; |
477 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
478 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
479 | +t1 1 a 1 a A 0 NULL NULL YES BTREE |
480 | alter table t1 enable keys; |
481 | show indexes from t1; |
482 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
483 | -t1 1 a 1 a NULL NULL NULL NULL YES BTREE |
484 | +t1 1 a 1 a A 0 NULL NULL YES BTREE |
485 | alter table t1 modify a bigint, disable keys; |
486 | show indexes from t1; |
487 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
488 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
489 | +t1 1 a 1 a A 0 NULL NULL YES BTREE |
490 | alter table t1 enable keys; |
491 | show indexes from t1; |
492 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
493 | -t1 1 a 1 a NULL NULL NULL NULL YES BTREE |
494 | +t1 1 a 1 a A 0 NULL NULL YES BTREE |
495 | alter table t1 add b char(10), disable keys; |
496 | show indexes from t1; |
497 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
498 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
499 | +t1 1 a 1 a A 0 NULL NULL YES BTREE |
500 | alter table t1 add c decimal(10,2), enable keys; |
501 | show indexes from t1; |
502 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
503 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
504 | +t1 1 a 1 a A 0 NULL NULL YES BTREE |
505 | "this however did" |
506 | alter table t1 disable keys; |
507 | show indexes from t1; |
508 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
509 | -t1 1 a 1 a NULL NULL NULL NULL YES BTREE |
510 | +t1 1 a 1 a A 0 NULL NULL YES BTREE |
511 | desc t1; |
512 | Field Type Null Key Default Extra |
513 | a bigint(20) YES MUL NULL |
514 | @@ -585,7 +585,7 @@ |
515 | "The key should still be disabled" |
516 | show indexes from t1; |
517 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
518 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
519 | +t1 1 a 1 a A 0 NULL NULL YES BTREE |
520 | drop table t1; |
521 | "Now will test with one unique index" |
522 | create table t1(a int, b char(10), unique(a)); |
523 | @@ -595,7 +595,7 @@ |
524 | alter table t1 disable keys; |
525 | show indexes from t1; |
526 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
527 | -t1 0 a 1 a NULL 0 NULL NULL YES BTREE |
528 | +t1 0 a 1 a A 0 NULL NULL YES BTREE |
529 | alter table t1 enable keys; |
530 | "If no copy on noop change, this won't touch the data file" |
531 | "Unique index, no change" |
532 | @@ -623,12 +623,12 @@ |
533 | show indexes from t1; |
534 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
535 | t1 0 a 1 a A 0 NULL NULL YES BTREE |
536 | -t1 1 b 1 b A NULL NULL NULL YES BTREE |
537 | +t1 1 b 1 b A 0 NULL NULL YES BTREE |
538 | alter table t1 disable keys; |
539 | show indexes from t1; |
540 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
541 | -t1 0 a 1 a NULL 0 NULL NULL YES BTREE |
542 | -t1 1 b 1 b NULL NULL NULL NULL YES BTREE |
543 | +t1 0 a 1 a A 0 NULL NULL YES BTREE |
544 | +t1 1 b 1 b A 0 NULL NULL YES BTREE |
545 | alter table t1 enable keys; |
546 | "If no copy on noop change, this won't touch the data file" |
547 | "The non-unique index will be disabled" |
548 | @@ -636,31 +636,31 @@ |
549 | show indexes from t1; |
550 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
551 | t1 0 a 1 a A 0 NULL NULL YES BTREE |
552 | -t1 1 b 1 b A NULL NULL NULL YES BTREE |
553 | +t1 1 b 1 b A 0 NULL NULL YES BTREE |
554 | alter table t1 enable keys; |
555 | show indexes from t1; |
556 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
557 | -t1 0 a 1 a NULL 0 NULL NULL YES BTREE |
558 | -t1 1 b 1 b NULL NULL NULL NULL YES BTREE |
559 | +t1 0 a 1 a A 0 NULL NULL YES BTREE |
560 | +t1 1 b 1 b A 0 NULL NULL YES BTREE |
561 | "Change the type implying data copy" |
562 | "The non-unique index will be disabled" |
563 | alter table t1 modify a bigint, disable keys; |
564 | show indexes from t1; |
565 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
566 | t1 0 a 1 a A 0 NULL NULL YES BTREE |
567 | -t1 1 b 1 b A NULL NULL NULL YES BTREE |
568 | +t1 1 b 1 b A 0 NULL NULL YES BTREE |
569 | "Change again the type, but leave the indexes as_is" |
570 | alter table t1 modify a int; |
571 | show indexes from t1; |
572 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
573 | t1 0 a 1 a A 0 NULL NULL YES BTREE |
574 | -t1 1 b 1 b A NULL NULL NULL YES BTREE |
575 | +t1 1 b 1 b A 0 NULL NULL YES BTREE |
576 | "Try the same. When data is no copied on similar tables, this is noop" |
577 | alter table t1 modify a int; |
578 | show indexes from t1; |
579 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
580 | t1 0 a 1 a A 0 NULL NULL YES BTREE |
581 | -t1 1 b 1 b A NULL NULL NULL YES BTREE |
582 | +t1 1 b 1 b A 0 NULL NULL YES BTREE |
583 | drop table t1; |
584 | create database mysqltest; |
585 | create table t1 (c1 int); |
586 | @@ -697,11 +697,11 @@ |
587 | CREATE TABLE bug24219 (a INT, INDEX(a)); |
588 | SHOW INDEX FROM bug24219; |
589 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
590 | -bug24219 1 a 1 a A NULL NULL NULL YES BTREE |
591 | +bug24219 1 a 1 a A 0 NULL NULL YES BTREE |
592 | ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS; |
593 | SHOW INDEX FROM bug24219_2; |
594 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
595 | -bug24219_2 1 a 1 a A NULL NULL NULL YES BTREE |
596 | +bug24219_2 1 a 1 a A 0 NULL NULL YES BTREE |
597 | DROP TABLE bug24219_2; |
598 | create table t1 (mycol int(10) not null); |
599 | alter table t1 alter column mycol set default 0; |
600 | @@ -882,7 +882,7 @@ |
601 | char_field char(10) YES NULL |
602 | SHOW INDEXES FROM t1; |
603 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
604 | -t1 1 int_field 1 int_field A NULL NULL NULL BTREE |
605 | +t1 1 int_field 1 int_field A 0 NULL NULL BTREE |
606 | INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet"); |
607 | "Non-copy data change - new frm, but old data and index files" |
608 | ALTER TABLE t1 |
609 | |
610 | === modified file 'test/mysql-test/r/analyze.result' |
611 | --- test/mysql-test/r/analyze.result 2008-06-09 12:49:32 +0000 |
612 | +++ test/mysql-test/r/analyze.result 2009-04-03 19:39:12 +0000 |
613 | @@ -56,5 +56,5 @@ |
614 | test.t1 analyze status OK |
615 | show index from t1; |
616 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
617 | -t1 1 a 1 a NULL NULL NULL NULL YES BTREE |
618 | +t1 1 a 1 a A 5 NULL NULL YES BTREE |
619 | drop table t1; |
620 | |
621 | === modified file 'test/mysql-test/r/compress.result' |
622 | --- test/mysql-test/r/compress.result 2008-06-09 12:49:32 +0000 |
623 | +++ test/mysql-test/r/compress.result 2009-04-03 19:39:12 +0000 |
624 | @@ -611,15 +611,15 @@ |
625 | explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; |
626 | id select_type table type possible_keys key key_len ref rows Extra |
627 | 1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort |
628 | -1 SIMPLE t3 ref period period 4 test.t1.period 18 |
629 | +1 SIMPLE t3 ref period period 4 test.t1.period 1 |
630 | explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; |
631 | id select_type table type possible_keys key key_len ref rows Extra |
632 | -1 SIMPLE t3 index period period 4 NULL 1 |
633 | -1 SIMPLE t1 ref period period 4 test.t3.period 18 |
634 | +1 SIMPLE t3 index period period 4 NULL 10 |
635 | +1 SIMPLE t1 ref period period 4 test.t3.period 1 |
636 | explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; |
637 | id select_type table type possible_keys key key_len ref rows Extra |
638 | -1 SIMPLE t1 index period period 4 NULL 1 |
639 | -1 SIMPLE t3 ref period period 4 test.t1.period 18 |
640 | +1 SIMPLE t1 index period period 4 NULL 10 |
641 | +1 SIMPLE t3 ref period period 4 test.t1.period 1 |
642 | select period from t1; |
643 | period |
644 | 9410 |
645 | @@ -2099,7 +2099,7 @@ |
646 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
647 | t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE |
648 | t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE |
649 | -t2 1 fld3 1 fld3 A NULL NULL NULL BTREE |
650 | +t2 1 fld3 1 fld3 A 1199 NULL NULL BTREE |
651 | drop table t4, t3, t2, t1; |
652 | CREATE TABLE t1 ( |
653 | cont_nr int(11) NOT NULL auto_increment, |
654 | |
655 | === modified file 'test/mysql-test/r/ctype_mb.result' |
656 | --- test/mysql-test/r/ctype_mb.result 2006-10-23 09:14:04 +0000 |
657 | +++ test/mysql-test/r/ctype_mb.result 2009-04-03 19:39:12 +0000 |
658 | @@ -33,7 +33,7 @@ |
659 | ) ENGINE=PBXT DEFAULT CHARSET=latin1 |
660 | SHOW KEYS FROM t1; |
661 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
662 | -t1 1 key_a 1 a A NULL 3 NULL YES BTREE |
663 | +t1 1 key_a 1 a A 0 3 NULL YES BTREE |
664 | ALTER TABLE t1 CHANGE a a CHAR(4); |
665 | SHOW CREATE TABLE t1; |
666 | Table Create Table |
667 | @@ -43,7 +43,7 @@ |
668 | ) ENGINE=PBXT DEFAULT CHARSET=latin1 |
669 | SHOW KEYS FROM t1; |
670 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
671 | -t1 1 key_a 1 a A NULL 3 NULL YES BTREE |
672 | +t1 1 key_a 1 a A 0 3 NULL YES BTREE |
673 | ALTER TABLE t1 CHANGE a a CHAR(4) CHARACTER SET utf8; |
674 | SHOW CREATE TABLE t1; |
675 | Table Create Table |
676 | @@ -53,5 +53,5 @@ |
677 | ) ENGINE=PBXT DEFAULT CHARSET=latin1 |
678 | SHOW KEYS FROM t1; |
679 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
680 | -t1 1 key_a 1 a A NULL 3 NULL YES BTREE |
681 | +t1 1 key_a 1 a A 0 3 NULL YES BTREE |
682 | DROP TABLE t1; |
683 | |
684 | === modified file 'test/mysql-test/r/distinct.result' |
685 | --- test/mysql-test/r/distinct.result 2008-03-28 10:24:43 +0000 |
686 | +++ test/mysql-test/r/distinct.result 2009-04-03 19:39:12 +0000 |
687 | @@ -174,8 +174,8 @@ |
688 | explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; |
689 | id select_type table type possible_keys key key_len ref rows Extra |
690 | 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 4 Using temporary |
691 | -1 SIMPLE t3 ref a a 5 test.t1.b 2 Using where; Using index |
692 | -1 SIMPLE t2 index a a 4 NULL 5 Using where; Using index; Distinct; Using join buffer |
693 | +1 SIMPLE t2 ref a a 4 test.t1.a 1 Using index |
694 | +1 SIMPLE t3 ref a a 5 test.t1.b 1 Using where; Using index |
695 | SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; |
696 | a |
697 | 1 |
698 | @@ -190,7 +190,7 @@ |
699 | explain select distinct t1.a from t1,t3 where t1.a=t3.a; |
700 | id select_type table type possible_keys key key_len ref rows Extra |
701 | 1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary |
702 | -1 SIMPLE t3 ref a a 5 test.t1.a 11 Using where; Using index; Distinct |
703 | +1 SIMPLE t3 ref a a 5 test.t1.a 1 Using where; Using index; Distinct |
704 | select distinct t1.a from t1,t3 where t1.a=t3.a; |
705 | a |
706 | 1 |
707 | @@ -212,7 +212,7 @@ |
708 | 1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index |
709 | explain SELECT distinct a from t3 order by a desc limit 2; |
710 | id select_type table type possible_keys key key_len ref rows Extra |
711 | -1 SIMPLE t3 index NULL a 5 NULL 40 Using index |
712 | +1 SIMPLE t3 index NULL a 5 NULL 2 Using index |
713 | explain SELECT distinct a,b from t3 order by a+1; |
714 | id select_type table type possible_keys key key_len ref rows Extra |
715 | 1 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort |
716 | |
717 | === modified file 'test/mysql-test/r/func_group.result' |
718 | --- test/mysql-test/r/func_group.result 2008-03-10 11:36:34 +0000 |
719 | +++ test/mysql-test/r/func_group.result 2009-04-03 19:39:12 +0000 |
720 | @@ -613,8 +613,8 @@ |
721 | explain |
722 | select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 > 'CA'; |
723 | id select_type table type possible_keys key key_len ref rows Extra |
724 | -1 SIMPLE t2 range k1 k1 3 NULL 1 Using where; Using index |
725 | -1 SIMPLE t1 range k1 k1 7 NULL 1 Using where; Using index; Using join buffer |
726 | +1 SIMPLE t1 range k1 k1 7 NULL 1 Using where; Using index |
727 | +1 SIMPLE t2 range k1 k1 3 NULL 1 Using where; Using index; Using join buffer |
728 | explain |
729 | select min(a4 - 0.01) from t1; |
730 | id select_type table type possible_keys key key_len ref rows Extra |
731 | |
732 | === modified file 'test/mysql-test/r/group_min_max.result' |
733 | --- test/mysql-test/r/group_min_max.result 2009-03-27 20:05:34 +0000 |
734 | +++ test/mysql-test/r/group_min_max.result 2009-04-03 19:39:12 +0000 |
735 | @@ -133,34 +133,34 @@ |
736 | test.t3 analyze status OK |
737 | explain select a1, min(a2) from t1 group by a1; |
738 | id select_type table type possible_keys key key_len ref rows Extra |
739 | -1 SIMPLE t1 range NULL idx_t1_1 130 NULL 10 Using index for group-by |
740 | +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 129 Using index for group-by |
741 | explain select a1, max(a2) from t1 group by a1; |
742 | id select_type table type possible_keys key key_len ref rows Extra |
743 | -1 SIMPLE t1 range NULL idx_t1_1 65 NULL 10 Using index for group-by |
744 | +1 SIMPLE t1 range NULL idx_t1_1 65 NULL 129 Using index for group-by |
745 | explain select a1, min(a2), max(a2) from t1 group by a1; |
746 | id select_type table type possible_keys key key_len ref rows Extra |
747 | -1 SIMPLE t1 range NULL idx_t1_1 130 NULL 10 Using index for group-by |
748 | +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 129 Using index for group-by |
749 | explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b; |
750 | id select_type table type possible_keys key key_len ref rows Extra |
751 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by |
752 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by |
753 | explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b; |
754 | id select_type table type possible_keys key key_len ref rows Extra |
755 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by |
756 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by |
757 | explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b; |
758 | id select_type table type possible_keys key key_len ref rows Extra |
759 | 1 SIMPLE t2 range NULL idx_t2_1 # NULL # Using index for group-by |
760 | explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1; |
761 | id select_type table type possible_keys key key_len ref rows Extra |
762 | -1 SIMPLE t1 range NULL idx_t1_1 130 NULL 10 Using index for group-by |
763 | +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 129 Using index for group-by |
764 | explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b; |
765 | id select_type table type possible_keys key key_len ref rows Extra |
766 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by |
767 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by |
768 | explain select min(a2) from t1 group by a1; |
769 | id select_type table type possible_keys key key_len ref rows Extra |
770 | -1 SIMPLE t1 range NULL idx_t1_1 130 NULL 10 Using index for group-by |
771 | +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 129 Using index for group-by |
772 | explain select a2, min(c), max(c) from t1 group by a1,a2,b; |
773 | id select_type table type possible_keys key key_len ref rows Extra |
774 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by |
775 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by |
776 | select a1, min(a2) from t1 group by a1; |
777 | a1 min(a2) |
778 | a a |
779 | @@ -293,13 +293,13 @@ |
780 | 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 65 NULL 1 Using where |
781 | explain select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; |
782 | id select_type table type possible_keys key key_len ref rows Extra |
783 | -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 1 Using where; Using index for group-by |
784 | +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 2 Using where; Using index for group-by |
785 | explain select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; |
786 | id select_type table type possible_keys key key_len ref rows Extra |
787 | -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 1 Using where; Using index for group-by |
788 | +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 2 Using where; Using index for group-by |
789 | explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; |
790 | id select_type table type possible_keys key key_len ref rows Extra |
791 | -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by |
792 | +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 129 Using where; Using index for group-by |
793 | explain select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; |
794 | id select_type table type possible_keys key key_len ref rows Extra |
795 | 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 65 NULL 3 Using where; Using index |
796 | @@ -669,40 +669,40 @@ |
797 | d p422 |
798 | explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; |
799 | id select_type table type possible_keys key key_len ref rows Extra |
800 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by |
801 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by |
802 | explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; |
803 | id select_type table type possible_keys key key_len ref rows Extra |
804 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by |
805 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by |
806 | explain select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; |
807 | id select_type table type possible_keys key key_len ref rows Extra |
808 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by |
809 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by |
810 | explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; |
811 | id select_type table type possible_keys key key_len ref rows Extra |
812 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by |
813 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by |
814 | explain select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; |
815 | id select_type table type possible_keys key key_len ref rows Extra |
816 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by |
817 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by |
818 | explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; |
819 | id select_type table type possible_keys key key_len ref rows Extra |
820 | -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by |
821 | +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 Using where; Using index for group-by |
822 | explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; |
823 | id select_type table type possible_keys key key_len ref rows Extra |
824 | -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by |
825 | +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 Using where; Using index for group-by |
826 | explain select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; |
827 | id select_type table type possible_keys key key_len ref rows Extra |
828 | -1 SIMPLE t2 range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by |
829 | +1 SIMPLE t2 range NULL idx_t2_1 146 NULL 165 Using where; Using index for group-by |
830 | explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; |
831 | id select_type table type possible_keys key key_len ref rows Extra |
832 | -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by |
833 | +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 Using where; Using index for group-by |
834 | explain select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; |
835 | id select_type table type possible_keys key key_len ref rows Extra |
836 | -1 SIMPLE t2 range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by |
837 | +1 SIMPLE t2 range NULL idx_t2_1 146 NULL 165 Using where; Using index for group-by |
838 | explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; |
839 | id select_type table type possible_keys key key_len ref rows Extra |
840 | -1 SIMPLE t3 range NULL idx_t3_1 6 NULL 10 Using where; Using index for group-by |
841 | +1 SIMPLE t3 range NULL idx_t3_1 6 NULL 193 Using where; Using index for group-by |
842 | explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; |
843 | id select_type table type possible_keys key key_len ref rows Extra |
844 | -1 SIMPLE t3 range NULL idx_t3_1 6 NULL 10 Using where; Using index for group-by |
845 | +1 SIMPLE t3 range NULL idx_t3_1 6 NULL 193 Using where; Using index for group-by |
846 | select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; |
847 | a1 a2 b max(c) min(c) |
848 | a a b h112 e112 |
849 | @@ -804,22 +804,22 @@ |
850 | c h312 e312 |
851 | explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; |
852 | id select_type table type possible_keys key key_len ref rows Extra |
853 | -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by |
854 | +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 Using where; Using index for group-by |
855 | explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; |
856 | id select_type table type possible_keys key key_len ref rows Extra |
857 | -1 SIMPLE t2 range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by |
858 | +1 SIMPLE t2 range NULL idx_t2_1 146 NULL 165 Using where; Using index for group-by |
859 | explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; |
860 | id select_type table type possible_keys key key_len ref rows Extra |
861 | -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by |
862 | +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 Using where; Using index for group-by |
863 | explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; |
864 | id select_type table type possible_keys key key_len ref rows Extra |
865 | -1 SIMPLE t2 range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by |
866 | -explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; |
867 | -id select_type table type possible_keys key key_len ref rows Extra |
868 | -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by |
869 | -explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; |
870 | -id select_type table type possible_keys key key_len ref rows Extra |
871 | -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by |
872 | +1 SIMPLE t2 range NULL idx_t2_1 146 NULL 165 Using where; Using index for group-by |
873 | +explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; |
874 | +id select_type table type possible_keys key key_len ref rows Extra |
875 | +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 Using where; Using index for group-by |
876 | +explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; |
877 | +id select_type table type possible_keys key key_len ref rows Extra |
878 | +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 Using where; Using index for group-by |
879 | select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; |
880 | a1 a2 b min(c) |
881 | a a NULL a777 |
882 | @@ -849,49 +849,49 @@ |
883 | 1 SIMPLE t1 range NULL idx_t1_1 147 NULL # Using where; Using index for group-by |
884 | explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b; |
885 | id select_type table type possible_keys key key_len ref rows Extra |
886 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by |
887 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by |
888 | explain select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b; |
889 | id select_type table type possible_keys key key_len ref rows Extra |
890 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by |
891 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by |
892 | explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b; |
893 | id select_type table type possible_keys key key_len ref rows Extra |
894 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by |
895 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by |
896 | explain select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b; |
897 | id select_type table type possible_keys key key_len ref rows Extra |
898 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by |
899 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by |
900 | explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b; |
901 | id select_type table type possible_keys key key_len ref rows Extra |
902 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by |
903 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by |
904 | explain select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b; |
905 | id select_type table type possible_keys key key_len ref rows Extra |
906 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by |
907 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by |
908 | explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b; |
909 | id select_type table type possible_keys key key_len ref rows Extra |
910 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by |
911 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by |
912 | explain select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; |
913 | id select_type table type possible_keys key key_len ref rows Extra |
914 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by |
915 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by |
916 | explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; |
917 | id select_type table type possible_keys key key_len ref rows Extra |
918 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by |
919 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by |
920 | explain select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; |
921 | id select_type table type possible_keys key key_len ref rows Extra |
922 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by |
923 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by |
924 | explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; |
925 | id select_type table type possible_keys key key_len ref rows Extra |
926 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by |
927 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by |
928 | explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; |
929 | id select_type table type possible_keys key key_len ref rows Extra |
930 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by |
931 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by |
932 | explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; |
933 | id select_type table type possible_keys key key_len ref rows Extra |
934 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by |
935 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by |
936 | explain select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; |
937 | id select_type table type possible_keys key key_len ref rows Extra |
938 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by |
939 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by |
940 | explain select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b; |
941 | id select_type table type possible_keys key key_len ref rows Extra |
942 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by |
943 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by |
944 | explain select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b; |
945 | id select_type table type possible_keys key key_len ref rows Extra |
946 | 1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by |
947 | @@ -1364,29 +1364,29 @@ |
948 | where exists ( select * from t2 where t2.c > 'b1' ) |
949 | group by a1,a2,b; |
950 | id select_type table type possible_keys key key_len ref rows Extra |
951 | -1 PRIMARY t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by |
952 | +1 PRIMARY t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by |
953 | 2 SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index |
954 | explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; |
955 | id select_type table type possible_keys key key_len ref rows Extra |
956 | -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by |
957 | +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 129 Using where; Using index for group-by |
958 | explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; |
959 | id select_type table type possible_keys key key_len ref rows Extra |
960 | -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 10 Using where; Using index for group-by |
961 | +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 129 Using where; Using index for group-by |
962 | explain select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; |
963 | id select_type table type possible_keys key key_len ref rows Extra |
964 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by |
965 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by |
966 | explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b; |
967 | id select_type table type possible_keys key key_len ref rows Extra |
968 | -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 1 Using where; Using index for group-by |
969 | +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 2 Using where; Using index for group-by |
970 | explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b; |
971 | id select_type table type possible_keys key key_len ref rows Extra |
972 | -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 1 Using where; Using index for group-by |
973 | +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 2 Using where; Using index for group-by |
974 | explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; |
975 | id select_type table type possible_keys key key_len ref rows Extra |
976 | 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 65 NULL 1 Using where |
977 | explain select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b; |
978 | id select_type table type possible_keys key key_len ref rows Extra |
979 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by |
980 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by |
981 | explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; |
982 | id select_type table type possible_keys key key_len ref rows Extra |
983 | 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by |
984 | @@ -1491,13 +1491,13 @@ |
985 | a1 a2 b min(c) |
986 | explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; |
987 | id select_type table type possible_keys key key_len ref rows Extra |
988 | -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by |
989 | +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 129 Using where; Using index for group-by |
990 | explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; |
991 | id select_type table type possible_keys key key_len ref rows Extra |
992 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by |
993 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by |
994 | explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; |
995 | id select_type table type possible_keys key key_len ref rows Extra |
996 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by |
997 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by |
998 | explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; |
999 | id select_type table type possible_keys key key_len ref rows Extra |
1000 | 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 1 Using where |
1001 | @@ -1554,13 +1554,13 @@ |
1002 | a1 a2 b |
1003 | explain select distinct a1,a2,b from t1; |
1004 | id select_type table type possible_keys key key_len ref rows Extra |
1005 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by |
1006 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by |
1007 | explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a'); |
1008 | id select_type table type possible_keys key key_len ref rows Extra |
1009 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by |
1010 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by |
1011 | explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); |
1012 | id select_type table type possible_keys key key_len ref rows filtered Extra |
1013 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 100.00 Using where; Using index for group-by |
1014 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 99.22 Using where; Using index for group-by |
1015 | Warnings: |
1016 | Note 1003 select distinct `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`c` = 'i121') and (`test`.`t1`.`b` = 'a') and (`test`.`t1`.`a2` >= 'b')) |
1017 | explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); |
1018 | @@ -1577,7 +1577,7 @@ |
1019 | 1 SIMPLE t2 range NULL idx_t2_2 146 NULL # Using where; Using index for group-by |
1020 | explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); |
1021 | id select_type table type possible_keys key key_len ref rows filtered Extra |
1022 | -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 100.00 Using where; Using index for group-by |
1023 | +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 165 99.39 Using where; Using index for group-by |
1024 | Warnings: |
1025 | Note 1003 select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = 'i121') and (`test`.`t2`.`b` = 'a') and (`test`.`t2`.`a2` >= 'b')) |
1026 | explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); |
1027 | @@ -1702,19 +1702,19 @@ |
1028 | d e |
1029 | explain select distinct a1,a2,b from t1; |
1030 | id select_type table type possible_keys key key_len ref rows Extra |
1031 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by |
1032 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by |
1033 | explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; |
1034 | id select_type table type possible_keys key key_len ref rows Extra |
1035 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by |
1036 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by |
1037 | explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; |
1038 | id select_type table type possible_keys key key_len ref rows Extra |
1039 | -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 10 Using where; Using index for group-by |
1040 | +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 129 Using where; Using index for group-by |
1041 | explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; |
1042 | id select_type table type possible_keys key key_len ref rows Extra |
1043 | 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 1 Using where |
1044 | explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; |
1045 | id select_type table type possible_keys key key_len ref rows Extra |
1046 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using where; Using index for group-by; Using temporary; Using filesort |
1047 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by; Using temporary; Using filesort |
1048 | explain select distinct a1,a2,b from t2; |
1049 | id select_type table type possible_keys key key_len ref rows Extra |
1050 | 1 SIMPLE t2 range NULL idx_t2_2 146 NULL # Using index for group-by |
1051 | @@ -1846,7 +1846,7 @@ |
1052 | 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 65 NULL 1 Using where |
1053 | explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2; |
1054 | id select_type table type possible_keys key key_len ref rows Extra |
1055 | -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 10 Using index for group-by |
1056 | +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by |
1057 | select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b; |
1058 | a1 a2 b concat(min(c), max(c)) |
1059 | a a a a111d111 |
1060 | @@ -1985,7 +1985,7 @@ |
1061 | d |
1062 | explain select a1 from t1 where a2 = 'b' group by a1; |
1063 | id select_type table type possible_keys key key_len ref rows Extra |
1064 | -1 SIMPLE t1 range NULL idx_t1_1 130 NULL 10 Using where; Using index for group-by |
1065 | +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 129 Using where; Using index for group-by |
1066 | select a1 from t1 where a2 = 'b' group by a1; |
1067 | a1 |
1068 | a |
1069 | @@ -1994,7 +1994,7 @@ |
1070 | d |
1071 | explain select distinct a1 from t1 where a2 = 'b'; |
1072 | id select_type table type possible_keys key key_len ref rows Extra |
1073 | -1 SIMPLE t1 range NULL idx_t1_1 130 NULL 10 Using where; Using index for group-by |
1074 | +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 129 Using where; Using index for group-by |
1075 | select distinct a1 from t1 where a2 = 'b'; |
1076 | a1 |
1077 | a |
1078 | @@ -2188,7 +2188,7 @@ |
1079 | (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); |
1080 | EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; |
1081 | id select_type table type possible_keys key key_len ref rows Extra |
1082 | -1 SIMPLE t1 range NULL a 5 NULL 8 Using index for group-by |
1083 | +1 SIMPLE t1 index NULL a 10 NULL 15 Using index |
1084 | FLUSH STATUS; |
1085 | SELECT max(b), a FROM t1 GROUP BY a; |
1086 | max(b) a |
1087 | @@ -2202,7 +2202,7 @@ |
1088 | Handler_read_next 0 |
1089 | EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; |
1090 | id select_type table type possible_keys key key_len ref rows Extra |
1091 | -1 SIMPLE t1 range NULL a 5 NULL 8 Using index for group-by |
1092 | +1 SIMPLE t1 index NULL a 10 NULL 15 Using index |
1093 | FLUSH STATUS; |
1094 | CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a; |
1095 | SHOW STATUS LIKE 'handler_read__e%'; |
1096 | @@ -2235,14 +2235,14 @@ |
1097 | EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION |
1098 | (SELECT max(b), a FROM t1 GROUP BY a); |
1099 | id select_type table type possible_keys key key_len ref rows Extra |
1100 | -1 PRIMARY t1 range NULL a 5 NULL 8 Using index for group-by |
1101 | -2 UNION t1 range NULL a 5 NULL 8 Using index for group-by |
1102 | +1 PRIMARY t1 index NULL a 10 NULL 15 Using index |
1103 | +2 UNION t1 index NULL a 10 NULL 15 Using index |
1104 | NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL |
1105 | EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x |
1106 | FROM t1 AS t1_outer; |
1107 | id select_type table type possible_keys key key_len ref rows Extra |
1108 | 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index |
1109 | -2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by |
1110 | +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index |
1111 | EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS |
1112 | (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); |
1113 | id select_type table type possible_keys key key_len ref rows Extra |
1114 | @@ -2252,7 +2252,7 @@ |
1115 | (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; |
1116 | id select_type table type possible_keys key key_len ref rows Extra |
1117 | 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE |
1118 | -2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by |
1119 | +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index |
1120 | EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE |
1121 | a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); |
1122 | id select_type table type possible_keys key key_len ref rows Extra |
1123 | @@ -2261,21 +2261,21 @@ |
1124 | EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING |
1125 | a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); |
1126 | id select_type table type possible_keys key key_len ref rows Extra |
1127 | -1 PRIMARY t1_outer range NULL a 5 NULL 8 Using index for group-by |
1128 | -2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by |
1129 | +1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index |
1130 | +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index |
1131 | EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 |
1132 | ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) |
1133 | AND t1_outer1.b = t1_outer2.b; |
1134 | id select_type table type possible_keys key key_len ref rows Extra |
1135 | 1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index |
1136 | 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index; Using join buffer |
1137 | -2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by |
1138 | +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index |
1139 | EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x |
1140 | FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; |
1141 | id select_type table type possible_keys key key_len ref rows Extra |
1142 | 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using index |
1143 | 2 SUBQUERY t1_outer index NULL a 10 NULL 15 Using index |
1144 | -3 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by |
1145 | +3 SUBQUERY t1 index NULL a 10 NULL 15 Using index |
1146 | CREATE TABLE t3 LIKE t1; |
1147 | FLUSH STATUS; |
1148 | INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a; |
1149 | @@ -2312,7 +2312,7 @@ |
1150 | (4), (2), (1), (2), (2), (4), (1), (4); |
1151 | EXPLAIN SELECT DISTINCT(a) FROM t1; |
1152 | id select_type table type possible_keys key key_len ref rows Extra |
1153 | -1 SIMPLE t1 range NULL idx 5 NULL 9 Using index for group-by |
1154 | +1 SIMPLE t1 index NULL idx 5 NULL 16 Using index |
1155 | SELECT DISTINCT(a) FROM t1; |
1156 | a |
1157 | 1 |
1158 | @@ -2320,7 +2320,7 @@ |
1159 | 4 |
1160 | EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; |
1161 | id select_type table type possible_keys key key_len ref rows Extra |
1162 | -1 SIMPLE t1 range NULL idx 5 NULL 9 Using index for group-by |
1163 | +1 SIMPLE t1 index NULL idx 5 NULL 16 Using index |
1164 | SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; |
1165 | a |
1166 | 1 |
1167 | @@ -2345,7 +2345,7 @@ |
1168 | EXPLAIN |
1169 | SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; |
1170 | id select_type table type possible_keys key key_len ref rows Extra |
1171 | -1 SIMPLE t1 range NULL break_it 10 NULL 7 Using index for group-by |
1172 | +1 SIMPLE t1 index NULL break_it 10 NULL 12 Using index |
1173 | SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; |
1174 | a MIN(b) MAX(b) |
1175 | 1 1 3 |
1176 | @@ -2355,7 +2355,7 @@ |
1177 | EXPLAIN |
1178 | SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; |
1179 | id select_type table type possible_keys key key_len ref rows Extra |
1180 | -1 SIMPLE t1 range NULL break_it 10 NULL 7 Using index for group-by; Using temporary; Using filesort |
1181 | +1 SIMPLE t1 index NULL break_it 10 NULL 12 Using index |
1182 | SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; |
1183 | a MIN(b) MAX(b) |
1184 | 4 1 3 |
1185 | |
1186 | === modified file 'test/mysql-test/r/join.result' |
1187 | --- test/mysql-test/r/join.result 2008-03-10 11:36:34 +0000 |
1188 | +++ test/mysql-test/r/join.result 2009-04-03 19:39:12 +0000 |
1189 | @@ -774,7 +774,7 @@ |
1190 | explain select * from t2,t3 where t2.a < 200 and t2.b=t3.b; |
1191 | id select_type table type possible_keys key key_len ref rows Extra |
1192 | 1 SIMPLE t2 range a,b a 5 NULL 1 Using where |
1193 | -1 SIMPLE t3 ref b b 5 test.t2.b 11 Using where |
1194 | +1 SIMPLE t3 ref b b 5 test.t2.b 1 Using where |
1195 | drop table t1, t2, t3; |
1196 | create table t1 (a int); |
1197 | insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
1198 | |
1199 | === modified file 'test/mysql-test/r/join_nested.result' |
1200 | --- test/mysql-test/r/join_nested.result 2009-03-27 20:05:34 +0000 |
1201 | +++ test/mysql-test/r/join_nested.result 2009-04-03 19:39:12 +0000 |
1202 | @@ -851,7 +851,7 @@ |
1203 | id select_type table type possible_keys key key_len ref rows filtered Extra |
1204 | 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 |
1205 | 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer |
1206 | -1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00 |
1207 | +1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 1 100.00 |
1208 | 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 |
1209 | Warnings: |
1210 | Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) where 1 |
1211 | @@ -958,15 +958,15 @@ |
1212 | 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where |
1213 | 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer |
1214 | 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where |
1215 | +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 100.00 |
1216 | 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where |
1217 | -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where |
1218 | 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where |
1219 | 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where |
1220 | 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where |
1221 | 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where |
1222 | 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer |
1223 | Warnings: |
1224 | -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) |
1225 | +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) |
1226 | CREATE INDEX idx_b ON t8(b); |
1227 | EXPLAIN EXTENDED |
1228 | SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, |
1229 | @@ -1006,16 +1006,16 @@ |
1230 | id select_type table type possible_keys key key_len ref rows filtered Extra |
1231 | 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where |
1232 | 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer |
1233 | -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where |
1234 | -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where |
1235 | -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where |
1236 | 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where |
1237 | 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where |
1238 | 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where |
1239 | -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where |
1240 | +1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 100.00 Using where |
1241 | +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where |
1242 | +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 100.00 |
1243 | +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where |
1244 | 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer |
1245 | Warnings: |
1246 | -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) |
1247 | +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) |
1248 | CREATE INDEX idx_b ON t1(b); |
1249 | CREATE INDEX idx_a ON t0(a); |
1250 | EXPLAIN EXTENDED |
1251 | @@ -1055,17 +1055,17 @@ |
1252 | (t9.a=1); |
1253 | id select_type table type possible_keys key key_len ref rows filtered Extra |
1254 | 1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 Using where |
1255 | -1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 2 100.00 Using where |
1256 | -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where |
1257 | -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where |
1258 | -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where |
1259 | +1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 1 100.00 Using where |
1260 | 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where |
1261 | 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where |
1262 | 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where |
1263 | -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where |
1264 | +1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 100.00 Using where |
1265 | +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where |
1266 | +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 100.00 |
1267 | +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where |
1268 | 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer |
1269 | Warnings: |
1270 | -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) |
1271 | +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) |
1272 | SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, |
1273 | t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b |
1274 | FROM t0,t1 |
1275 | @@ -1103,20 +1103,20 @@ |
1276 | a b a b a b a b a b a b a b a b a b a b |
1277 | 1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1 |
1278 | 1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1 |
1279 | +1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1 |
1280 | +1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1 |
1281 | 1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 |
1282 | -1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1 |
1283 | 1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 |
1284 | -1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1 |
1285 | 1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1 |
1286 | 1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2 |
1287 | 1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2 |
1288 | +1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2 |
1289 | +1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2 |
1290 | 1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2 |
1291 | +1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2 |
1292 | +1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2 |
1293 | 1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 |
1294 | -1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2 |
1295 | -1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2 |
1296 | 1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 |
1297 | -1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2 |
1298 | -1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2 |
1299 | 1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 2 |
1300 | SELECT t2.a,t2.b |
1301 | FROM t2; |
1302 | @@ -1203,7 +1203,7 @@ |
1303 | id select_type table type possible_keys key key_len ref rows Extra |
1304 | 1 SIMPLE t1 index NULL a 5 NULL 21 Using index |
1305 | 1 SIMPLE t3 index c c 5 NULL 6 Using index |
1306 | -1 SIMPLE t2 ref b b 5 test.t3.c 2 Using index |
1307 | +1 SIMPLE t2 ref b b 5 test.t3.c 1 Using index |
1308 | EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; |
1309 | id select_type table type possible_keys key key_len ref rows Extra |
1310 | 1 SIMPLE t1 index NULL a 5 NULL # Using index |
1311 | @@ -1484,8 +1484,8 @@ |
1312 | on (t1.a = t2.a); |
1313 | id select_type table type possible_keys key key_len ref rows Extra |
1314 | 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 |
1315 | -1 SIMPLE t2 ref a a 5 test.t1.a 11 |
1316 | -1 SIMPLE t3 ref a a 5 test.t2.a 11 |
1317 | +1 SIMPLE t2 ref a a 5 test.t1.a 1 |
1318 | +1 SIMPLE t3 ref a a 5 test.t2.a 1 |
1319 | drop table t1, t2, t3; |
1320 | CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10)); |
1321 | CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10)); |
1322 | |
1323 | === modified file 'test/mysql-test/r/key.result' |
1324 | --- test/mysql-test/r/key.result 2008-03-11 16:31:13 +0000 |
1325 | +++ test/mysql-test/r/key.result 2009-04-03 19:39:12 +0000 |
1326 | @@ -153,7 +153,7 @@ |
1327 | t1 0 a 1 a A 0 NULL NULL BTREE |
1328 | t1 0 e 1 e A 0 NULL NULL BTREE |
1329 | t1 0 b 1 b A 0 NULL NULL YES BTREE |
1330 | -t1 1 c 1 c A NULL NULL NULL YES BTREE |
1331 | +t1 1 c 1 c A 0 NULL NULL YES BTREE |
1332 | drop table t1; |
1333 | CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT, |
1334 | UNIQUE (c,i)); |
1335 | |
1336 | === modified file 'test/mysql-test/r/key_cache.result' |
1337 | --- test/mysql-test/r/key_cache.result 2008-11-14 12:08:22 +0000 |
1338 | +++ test/mysql-test/r/key_cache.result 2009-04-03 19:39:12 +0000 |
1339 | @@ -122,7 +122,7 @@ |
1340 | explain select count(*) from t1, t2 where t1.p = t2.i; |
1341 | id select_type table type possible_keys key key_len ref rows Extra |
1342 | 1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index |
1343 | -1 SIMPLE t2 ref k1 k1 5 test.t1.p 2 Using where; Using index |
1344 | +1 SIMPLE t2 ref k1 k1 5 test.t1.p 1 Using where; Using index |
1345 | select count(*) from t1, t2 where t1.p = t2.i; |
1346 | count(*) |
1347 | 3 |
1348 | |
1349 | === modified file 'test/mysql-test/r/key_diff.result' |
1350 | --- test/mysql-test/r/key_diff.result 2008-03-10 11:36:34 +0000 |
1351 | +++ test/mysql-test/r/key_diff.result 2009-04-03 19:39:12 +0000 |
1352 | @@ -36,7 +36,7 @@ |
1353 | explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B; |
1354 | id select_type table type possible_keys key key_len ref rows Extra |
1355 | 1 SIMPLE t1 ALL a NULL NULL NULL 5 |
1356 | -1 SIMPLE t2 ALL b NULL NULL NULL 5 Using where; Using join buffer |
1357 | +1 SIMPLE t2 ref b b 4 test.t1.a 1 Using where |
1358 | select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a; |
1359 | a b a b |
1360 | A B a a |
1361 | |
1362 | === modified file 'test/mysql-test/r/myisam.result' |
1363 | --- test/mysql-test/r/myisam.result 2009-03-27 20:05:34 +0000 |
1364 | +++ test/mysql-test/r/myisam.result 2009-04-03 19:39:12 +0000 |
1365 | @@ -42,16 +42,16 @@ |
1366 | test.t1 optimize status OK |
1367 | show index from t1; |
1368 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1369 | -t1 0 PRIMARY 1 a NULL 5 NULL NULL BTREE |
1370 | -t1 1 b 1 b NULL NULL NULL NULL BTREE |
1371 | +t1 0 PRIMARY 1 a A 5 NULL NULL BTREE |
1372 | +t1 1 b 1 b A 5 NULL NULL BTREE |
1373 | optimize table t1; |
1374 | Table Op Msg_type Msg_text |
1375 | test.t1 optimize note Table does not support optimize, doing recreate + analyze instead |
1376 | test.t1 optimize status OK |
1377 | show index from t1; |
1378 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1379 | -t1 0 PRIMARY 1 a NULL 5 NULL NULL BTREE |
1380 | -t1 1 b 1 b NULL NULL NULL NULL BTREE |
1381 | +t1 0 PRIMARY 1 a A 5 NULL NULL BTREE |
1382 | +t1 1 b 1 b A 5 NULL NULL BTREE |
1383 | drop table t1; |
1384 | create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=pbxt; |
1385 | insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); |
1386 | @@ -344,12 +344,12 @@ |
1387 | test.t1 optimize status OK |
1388 | show index from t1; |
1389 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1390 | -t1 1 b 1 b NULL NULL NULL NULL YES BTREE |
1391 | -t1 1 c 1 c NULL NULL NULL NULL YES BTREE |
1392 | -t1 1 a 1 a NULL NULL NULL NULL BTREE |
1393 | -t1 1 a 2 b NULL NULL NULL NULL YES BTREE |
1394 | -t1 1 c_2 1 c NULL NULL NULL NULL YES BTREE |
1395 | -t1 1 c_2 2 a NULL NULL NULL NULL BTREE |
1396 | +t1 1 b 1 b A 5 NULL NULL YES BTREE |
1397 | +t1 1 c 1 c A 5 NULL NULL YES BTREE |
1398 | +t1 1 a 1 a A 5 NULL NULL BTREE |
1399 | +t1 1 a 2 b A 5 NULL NULL YES BTREE |
1400 | +t1 1 c_2 1 c A 5 NULL NULL YES BTREE |
1401 | +t1 1 c_2 2 a A 5 NULL NULL BTREE |
1402 | explain select * from t1,t2 where t1.a=t2.a; |
1403 | id select_type table type possible_keys key key_len ref rows Extra |
1404 | 1 SIMPLE t2 ALL a NULL NULL NULL 2 |
1405 | @@ -365,7 +365,7 @@ |
1406 | explain select * from t1,t2 where t1.b=t2.b; |
1407 | id select_type table type possible_keys key key_len ref rows Extra |
1408 | 1 SIMPLE t2 ALL b NULL NULL NULL 2 |
1409 | -1 SIMPLE t1 ALL b NULL NULL NULL 5 Using where; Using join buffer |
1410 | +1 SIMPLE t1 ref b b 5 test.t2.b 1 Using where |
1411 | explain select * from t1,t2 force index(c) where t1.a=t2.a; |
1412 | id select_type table type possible_keys key key_len ref rows Extra |
1413 | 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 |
1414 | @@ -584,25 +584,25 @@ |
1415 | create table t1 (a int, key (a)); |
1416 | show keys from t1; |
1417 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1418 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
1419 | +t1 1 a 1 a A 0 NULL NULL YES BTREE |
1420 | alter table t1 disable keys; |
1421 | Warnings: |
1422 | Note 1031 Table storage engine for 't1' doesn't have this option |
1423 | show keys from t1; |
1424 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1425 | -t1 1 a 1 a NULL NULL NULL NULL YES BTREE |
1426 | +t1 1 a 1 a A 0 NULL NULL YES BTREE |
1427 | create table t2 (a int); |
1428 | set @@rand_seed1=31415926,@@rand_seed2=2718281828; |
1429 | insert t1 select * from t2; |
1430 | show keys from t1; |
1431 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1432 | -t1 1 a 1 a NULL NULL NULL NULL YES BTREE |
1433 | +t1 1 a 1 a A 1000 NULL NULL YES BTREE |
1434 | alter table t1 enable keys; |
1435 | Warnings: |
1436 | Note 1031 Table storage engine for 't1' doesn't have this option |
1437 | show keys from t1; |
1438 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1439 | -t1 1 a 1 a NULL NULL NULL NULL YES BTREE |
1440 | +t1 1 a 1 a A 1000 NULL NULL YES BTREE |
1441 | alter table t1 engine=heap; |
1442 | alter table t1 disable keys; |
1443 | Warnings: |
1444 | @@ -670,7 +670,7 @@ |
1445 | test.t1 analyze status OK |
1446 | show index from t1; |
1447 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1448 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
1449 | +t1 1 a 1 a A 10 NULL NULL YES BTREE |
1450 | insert into t1 values (11); |
1451 | delete from t1 where a=11; |
1452 | check table t1; |
1453 | @@ -678,7 +678,7 @@ |
1454 | test.t1 check status OK |
1455 | show index from t1; |
1456 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1457 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
1458 | +t1 1 a 1 a A 11 NULL NULL YES BTREE |
1459 | set myisam_stats_method=nulls_equal; |
1460 | show variables like 'myisam_stats_method'; |
1461 | Variable_name Value |
1462 | @@ -690,7 +690,7 @@ |
1463 | test.t1 analyze status OK |
1464 | show index from t1; |
1465 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1466 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
1467 | +t1 1 a 1 a A 10 NULL NULL YES BTREE |
1468 | insert into t1 values (11); |
1469 | delete from t1 where a=11; |
1470 | check table t1; |
1471 | @@ -698,7 +698,7 @@ |
1472 | test.t1 check status OK |
1473 | show index from t1; |
1474 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1475 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
1476 | +t1 1 a 1 a A 11 NULL NULL YES BTREE |
1477 | set myisam_stats_method=DEFAULT; |
1478 | show variables like 'myisam_stats_method'; |
1479 | Variable_name Value |
1480 | @@ -710,7 +710,7 @@ |
1481 | test.t1 analyze status OK |
1482 | show index from t1; |
1483 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1484 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
1485 | +t1 1 a 1 a A 10 NULL NULL YES BTREE |
1486 | insert into t1 values (11); |
1487 | delete from t1 where a=11; |
1488 | check table t1; |
1489 | @@ -718,7 +718,7 @@ |
1490 | test.t1 check status OK |
1491 | show index from t1; |
1492 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1493 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
1494 | +t1 1 a 1 a A 11 NULL NULL YES BTREE |
1495 | drop table t1; |
1496 | set myisam_stats_method=nulls_ignored; |
1497 | show variables like 'myisam_stats_method'; |
1498 | @@ -737,20 +737,20 @@ |
1499 | test.t1 analyze status OK |
1500 | show index from t1; |
1501 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1502 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
1503 | -t1 1 a 2 b A NULL NULL NULL YES BTREE |
1504 | -t1 1 a 3 c A NULL NULL NULL YES BTREE |
1505 | -t1 1 a 4 d A NULL NULL NULL YES BTREE |
1506 | +t1 1 a 1 a A 4 NULL NULL YES BTREE |
1507 | +t1 1 a 2 b A 4 NULL NULL YES BTREE |
1508 | +t1 1 a 3 c A 4 NULL NULL YES BTREE |
1509 | +t1 1 a 4 d A 4 NULL NULL YES BTREE |
1510 | delete from t1; |
1511 | analyze table t1; |
1512 | Table Op Msg_type Msg_text |
1513 | test.t1 analyze status OK |
1514 | show index from t1; |
1515 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1516 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
1517 | -t1 1 a 2 b A NULL NULL NULL YES BTREE |
1518 | -t1 1 a 3 c A NULL NULL NULL YES BTREE |
1519 | -t1 1 a 4 d A NULL NULL NULL YES BTREE |
1520 | +t1 1 a 1 a A 0 NULL NULL YES BTREE |
1521 | +t1 1 a 2 b A 0 NULL NULL YES BTREE |
1522 | +t1 1 a 3 c A 0 NULL NULL YES BTREE |
1523 | +t1 1 a 4 d A 0 NULL NULL YES BTREE |
1524 | set myisam_stats_method=DEFAULT; |
1525 | drop table t1; |
1526 | create table t1( |
1527 | @@ -1788,7 +1788,7 @@ |
1528 | test.t1 analyze status OK |
1529 | show keys from t1; |
1530 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1531 | -t1 1 a 1 a A NULL NULL NULL YES BTREE |
1532 | +t1 1 a 1 a A 8 NULL NULL YES BTREE |
1533 | alter table t1 disable keys; |
1534 | Warnings: |
1535 | Note 1031 Table storage engine for 't1' doesn't have this option |
1536 | @@ -1797,7 +1797,7 @@ |
1537 | Note 1031 Table storage engine for 't1' doesn't have this option |
1538 | show keys from t1; |
1539 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1540 | -t1 1 a 1 a NULL NULL NULL NULL YES BTREE |
1541 | +t1 1 a 1 a A 8 NULL NULL YES BTREE |
1542 | drop table t1; |
1543 | create table t1 (c1 int) engine=myisam pack_keys=0; |
1544 | create table t2 (c1 int) engine=myisam pack_keys=1; |
1545 | |
1546 | === modified file 'test/mysql-test/r/mysqldump.result' |
1547 | --- test/mysql-test/r/mysqldump.result 2008-12-04 13:27:22 +0000 |
1548 | +++ test/mysql-test/r/mysqldump.result 2009-04-03 19:39:12 +0000 |
1549 | @@ -10,7 +10,7 @@ |
1550 | <database name="test"> |
1551 | <table_structure name="t1"> |
1552 | <field Field="a" Type="int(11)" Null="YES" Key="MUL" Extra="" /> |
1553 | - <key Table="t1" Non_unique="1" Key_name="a" Seq_in_index="1" Column_name="a" Collation="A" Null="YES" Index_type="BTREE" Comment="" /> |
1554 | + <key Table="t1" Non_unique="1" Key_name="a" Seq_in_index="1" Column_name="a" Collation="A" Cardinality="2" Null="YES" Index_type="BTREE" Comment="" /> |
1555 | </table_structure> |
1556 | <table_data name="t1"> |
1557 | <row> |
1558 | |
1559 | === modified file 'test/mysql-test/r/ndb_alter_table3.result' |
1560 | --- test/mysql-test/r/ndb_alter_table3.result 2008-05-21 12:07:14 +0000 |
1561 | +++ test/mysql-test/r/ndb_alter_table3.result 2009-04-03 19:39:12 +0000 |
1562 | @@ -5,8 +5,8 @@ |
1563 | show indexes from t1; |
1564 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1565 | t1 0 PRIMARY 1 a A 3 NULL NULL BTREE |
1566 | -t1 1 b 1 b A NULL NULL NULL YES BTREE |
1567 | -t1 1 c 1 c A NULL NULL NULL YES BTREE |
1568 | +t1 1 b 1 b A 3 NULL NULL YES BTREE |
1569 | +t1 1 c 1 c A 3 NULL NULL YES BTREE |
1570 | select * from t1 where c = 'two'; |
1571 | a b c |
1572 | 2 two two |
1573 | @@ -14,7 +14,7 @@ |
1574 | show indexes from t1; |
1575 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1576 | t1 0 PRIMARY 1 a A 3 NULL NULL BTREE |
1577 | -t1 1 b 1 b A NULL NULL NULL YES BTREE |
1578 | +t1 1 b 1 b A 3 NULL NULL YES BTREE |
1579 | select * from t1 where c = 'two'; |
1580 | a b c |
1581 | 2 two two |
1582 | |
1583 | === modified file 'test/mysql-test/r/ndb_basic.result' |
1584 | --- test/mysql-test/r/ndb_basic.result 2008-12-11 01:07:22 +0000 |
1585 | +++ test/mysql-test/r/ndb_basic.result 2009-04-03 19:39:12 +0000 |
1586 | @@ -12,7 +12,7 @@ |
1587 | INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9411,9413, 17, '9413'); |
1588 | SHOW INDEX FROM t1; |
1589 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1590 | -t1 0 PRIMARY 1 pk1 NULL 2 NULL NULL BTREE |
1591 | +t1 0 PRIMARY 1 pk1 A 2 NULL NULL BTREE |
1592 | SELECT pk1 FROM t1 ORDER BY pk1; |
1593 | pk1 |
1594 | 9410 |
1595 | |
1596 | === modified file 'test/mysql-test/r/null_key.result' |
1597 | --- test/mysql-test/r/null_key.result 2007-03-13 19:06:47 +0000 |
1598 | +++ test/mysql-test/r/null_key.result 2009-04-03 19:39:12 +0000 |
1599 | @@ -407,8 +407,8 @@ |
1600 | LEFT JOIN t3 ON t2.b=t3.b; |
1601 | id select_type table type possible_keys key key_len ref rows Extra |
1602 | 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 |
1603 | -1 SIMPLE t2 ref idx idx 5 test.t1.a 2 |
1604 | -1 SIMPLE t3 ref idx idx 5 test.t2.b 186 Using index |
1605 | +1 SIMPLE t2 ref idx idx 5 test.t1.a 1 |
1606 | +1 SIMPLE t3 ref idx idx 5 test.t2.b 1 Using index |
1607 | FLUSH STATUS ; |
1608 | SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a |
1609 | LEFT JOIN t3 ON t2.b=t3.b; |
1610 | |
1611 | === modified file 'test/mysql-test/r/partition_pruning.result' |
1612 | --- test/mysql-test/r/partition_pruning.result 2009-03-27 20:05:34 +0000 |
1613 | +++ test/mysql-test/r/partition_pruning.result 2009-04-03 19:39:12 +0000 |
1614 | @@ -338,12 +338,12 @@ |
1615 | where X.b = Y.b and (X.a=1 or X.a=2) and (Y.a=2 or Y.a=3); |
1616 | id select_type table partitions type possible_keys key key_len ref rows Extra |
1617 | 1 SIMPLE X p1,p2 ALL a,b NULL NULL NULL 2 Using where |
1618 | -1 SIMPLE Y p2,p3 ref a,b b 4 test.X.b 2 Using where |
1619 | +1 SIMPLE Y p2,p3 ref a,b b 4 test.X.b 1 Using where |
1620 | explain partitions |
1621 | select * from t1 X, t1 Y where X.a = Y.a and (X.a=1 or X.a=2); |
1622 | id select_type table partitions type possible_keys key key_len ref rows Extra |
1623 | 1 SIMPLE X p1,p2 ALL a NULL NULL NULL 4 Using where |
1624 | -1 SIMPLE Y p1,p2 ref a a 4 test.X.a 2 |
1625 | +1 SIMPLE Y p1,p2 ref a a 4 test.X.a 1 |
1626 | drop table t1; |
1627 | create table t1 (a int) partition by hash(a) partitions 20; |
1628 | insert into t1 values (1),(2),(3); |
1629 | |
1630 | === modified file 'test/mysql-test/r/ps_1general.result' |
1631 | --- test/mysql-test/r/ps_1general.result 2008-09-17 07:41:28 +0000 |
1632 | +++ test/mysql-test/r/ps_1general.result 2009-04-03 19:39:12 +0000 |
1633 | @@ -288,7 +288,7 @@ |
1634 | execute stmt4; |
1635 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1636 | t2 0 PRIMARY 1 a A 0 NULL NULL BTREE |
1637 | -t2 1 t2_idx 1 b A NULL NULL NULL YES BTREE |
1638 | +t2 1 t2_idx 1 b A 0 NULL NULL YES BTREE |
1639 | prepare stmt4 from ' show table status from test like ''t2%'' '; |
1640 | execute stmt4; |
1641 | Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment |
1642 | |
1643 | === modified file 'test/mysql-test/r/range.result' |
1644 | --- test/mysql-test/r/range.result 2009-03-27 20:05:34 +0000 |
1645 | +++ test/mysql-test/r/range.result 2009-04-03 19:39:12 +0000 |
1646 | @@ -423,19 +423,19 @@ |
1647 | explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; |
1648 | id select_type table type possible_keys key key_len ref rows Extra |
1649 | 1 SIMPLE t1 range uid_index uid_index 4 NULL 1 Using where |
1650 | -1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 12 |
1651 | +1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 1 |
1652 | explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid > 0; |
1653 | id select_type table type possible_keys key key_len ref rows Extra |
1654 | 1 SIMPLE t1 range uid_index uid_index 4 NULL 1 Using where |
1655 | -1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 12 |
1656 | +1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 1 |
1657 | explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0; |
1658 | id select_type table type possible_keys key key_len ref rows Extra |
1659 | 1 SIMPLE t1 range uid_index uid_index 4 NULL 2 Using where |
1660 | -1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 12 |
1661 | +1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 1 |
1662 | explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid != 0; |
1663 | id select_type table type possible_keys key key_len ref rows Extra |
1664 | 1 SIMPLE t1 range uid_index uid_index 4 NULL 2 Using where |
1665 | -1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 12 |
1666 | +1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 1 |
1667 | select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; |
1668 | id name uid id name uid |
1669 | 1001 A 1 1001 A 1 |
1670 | |
1671 | === modified file 'test/mysql-test/r/repair.result' |
1672 | --- test/mysql-test/r/repair.result 2008-11-20 09:17:23 +0000 |
1673 | +++ test/mysql-test/r/repair.result 2009-04-03 19:39:12 +0000 |
1674 | @@ -48,7 +48,7 @@ |
1675 | test.t1 repair status OK |
1676 | SHOW INDEX FROM t1; |
1677 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1678 | -t1 1 a 1 a NULL NULL NULL NULL YES BTREE |
1679 | +t1 1 a 1 a A 5 NULL NULL YES BTREE |
1680 | SET myisam_repair_threads=@@global.myisam_repair_threads; |
1681 | DROP TABLE t1; |
1682 | CREATE TABLE t1(a INT) engine = myisam; |
1683 | |
1684 | === modified file 'test/mysql-test/r/select.result' |
1685 | --- test/mysql-test/r/select.result 2009-03-27 20:05:34 +0000 |
1686 | +++ test/mysql-test/r/select.result 2009-04-03 19:39:12 +0000 |
1687 | @@ -604,15 +604,15 @@ |
1688 | explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; |
1689 | id select_type table type possible_keys key key_len ref rows Extra |
1690 | 1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort |
1691 | -1 SIMPLE t3 ref period period 4 test.t1.period 18 |
1692 | +1 SIMPLE t3 ref period period 4 test.t1.period 1 |
1693 | explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; |
1694 | id select_type table type possible_keys key key_len ref rows Extra |
1695 | -1 SIMPLE t3 index period period 4 NULL 1 |
1696 | -1 SIMPLE t1 ref period period 4 test.t3.period 18 |
1697 | +1 SIMPLE t3 index period period 4 NULL 10 |
1698 | +1 SIMPLE t1 ref period period 4 test.t3.period 1 |
1699 | explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; |
1700 | id select_type table type possible_keys key key_len ref rows Extra |
1701 | -1 SIMPLE t1 index period period 4 NULL 1 |
1702 | -1 SIMPLE t3 ref period period 4 test.t1.period 18 |
1703 | +1 SIMPLE t1 index period period 4 NULL 10 |
1704 | +1 SIMPLE t3 ref period period 4 test.t1.period 1 |
1705 | select period from t1; |
1706 | period |
1707 | 9410 |
1708 | @@ -2095,7 +2095,7 @@ |
1709 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1710 | t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE |
1711 | t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE |
1712 | -t2 1 fld3 1 fld3 A NULL NULL NULL BTREE |
1713 | +t2 1 fld3 1 fld3 A 1199 NULL NULL BTREE |
1714 | drop table t4, t3, t2, t1; |
1715 | DO 1; |
1716 | DO benchmark(100,1+1),1,1; |
1717 | @@ -2369,7 +2369,7 @@ |
1718 | explain select * from t1 left join t2 on a=c where d in (4); |
1719 | id select_type table type possible_keys key key_len ref rows Extra |
1720 | 1 SIMPLE t2 ref c,d d 5 const 1 Using where |
1721 | -1 SIMPLE t1 ref a a 5 test.t2.c 2 Using where |
1722 | +1 SIMPLE t1 ref a a 5 test.t2.c 1 Using where |
1723 | select * from t1 left join t2 on a=c where d in (4); |
1724 | a b c d |
1725 | 3 2 3 4 |
1726 | @@ -2377,7 +2377,7 @@ |
1727 | explain select * from t1 left join t2 on a=c where d = 4; |
1728 | id select_type table type possible_keys key key_len ref rows Extra |
1729 | 1 SIMPLE t2 ref c,d d 5 const 1 Using where |
1730 | -1 SIMPLE t1 ref a a 5 test.t2.c 2 Using where |
1731 | +1 SIMPLE t1 ref a a 5 test.t2.c 1 Using where |
1732 | select * from t1 left join t2 on a=c where d = 4; |
1733 | a b c d |
1734 | 3 2 3 4 |
1735 | @@ -2403,11 +2403,11 @@ |
1736 | EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a; |
1737 | id select_type table type possible_keys key key_len ref rows Extra |
1738 | 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 |
1739 | -1 SIMPLE t2 ref a a 23 test.t1.a 2 |
1740 | +1 SIMPLE t2 ref a a 23 test.t1.a 1 |
1741 | EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a; |
1742 | id select_type table type possible_keys key key_len ref rows Extra |
1743 | 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 |
1744 | -1 SIMPLE t2 ref a a 23 test.t1.a 2 |
1745 | +1 SIMPLE t2 ref a a 23 test.t1.a 1 |
1746 | DROP TABLE t1, t2; |
1747 | CREATE TABLE t1 ( city char(30) ); |
1748 | INSERT INTO t1 VALUES ('London'); |
1749 | @@ -3454,7 +3454,7 @@ |
1750 | and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5); |
1751 | id select_type table type possible_keys key key_len ref rows Extra |
1752 | 1 SIMPLE A range PRIMARY PRIMARY 12 NULL 1 Using where |
1753 | -1 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 11 |
1754 | +1 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 1 |
1755 | drop table t1, t2; |
1756 | CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b)); |
1757 | INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2), |
1758 | @@ -3468,12 +3468,12 @@ |
1759 | SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6; |
1760 | id select_type table type possible_keys key key_len ref rows Extra |
1761 | 1 SIMPLE t1 range PRIMARY,b b 5 NULL 1 Using where |
1762 | -1 SIMPLE t2 ref c c 5 test.t1.a 2 Using where |
1763 | +1 SIMPLE t2 ref c c 5 test.t1.a 1 Using where |
1764 | EXPLAIN |
1765 | SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0; |
1766 | id select_type table type possible_keys key key_len ref rows Extra |
1767 | 1 SIMPLE t1 range PRIMARY,b PRIMARY 4 NULL 1 Using where |
1768 | -1 SIMPLE t2 ref c c 5 test.t1.a 2 Using where |
1769 | +1 SIMPLE t2 ref c c 5 test.t1.a 1 Using where |
1770 | DROP TABLE t1, t2; |
1771 | create table t1 ( |
1772 | a int unsigned not null auto_increment primary key, |
1773 | |
1774 | === modified file 'test/mysql-test/r/select_safe.result' |
1775 | --- test/mysql-test/r/select_safe.result 2006-10-23 09:14:04 +0000 |
1776 | +++ test/mysql-test/r/select_safe.result 2009-04-03 19:39:12 +0000 |
1777 | @@ -70,12 +70,12 @@ |
1778 | explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; |
1779 | id select_type table type possible_keys key key_len ref rows Extra |
1780 | 1 SIMPLE t1 ALL b NULL NULL NULL 21 |
1781 | -1 SIMPLE t2 ref b b 21 test.t1.b 2 Using where |
1782 | +1 SIMPLE t2 ref b b 21 test.t1.b 1 Using where |
1783 | set MAX_SEEKS_FOR_KEY=1; |
1784 | explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; |
1785 | id select_type table type possible_keys key key_len ref rows Extra |
1786 | 1 SIMPLE t1 ALL b NULL NULL NULL 21 |
1787 | -1 SIMPLE t2 ref b b 21 test.t1.b 2 Using where |
1788 | +1 SIMPLE t2 ref b b 21 test.t1.b 1 Using where |
1789 | SET MAX_SEEKS_FOR_KEY=DEFAULT; |
1790 | drop table t1; |
1791 | create table t1 (a int); |
1792 | |
1793 | === modified file 'test/mysql-test/r/subselect.result' |
1794 | --- test/mysql-test/r/subselect.result 2008-12-11 01:07:22 +0000 |
1795 | +++ test/mysql-test/r/subselect.result 2009-04-03 19:39:12 +0000 |
1796 | @@ -1333,7 +1333,7 @@ |
1797 | explain extended select * from t2 where t2.a in (select a from t1); |
1798 | id select_type table type possible_keys key key_len ref rows filtered Extra |
1799 | 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index |
1800 | -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where |
1801 | +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index; Using where |
1802 | Warnings: |
1803 | Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) |
1804 | select * from t2 where t2.a in (select a from t1 where t1.b <> 30); |
1805 | @@ -1343,7 +1343,7 @@ |
1806 | explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); |
1807 | id select_type table type possible_keys key key_len ref rows filtered Extra |
1808 | 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index |
1809 | -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where |
1810 | +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index; Using where |
1811 | Warnings: |
1812 | Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) |
1813 | select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); |
1814 | @@ -1353,8 +1353,8 @@ |
1815 | explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); |
1816 | id select_type table type possible_keys key key_len ref rows filtered Extra |
1817 | 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index |
1818 | -2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using where; Using index |
1819 | -2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer |
1820 | +2 DEPENDENT SUBQUERY t1 ref a a 5 func 1 100.00 Using where; Using index |
1821 | +2 DEPENDENT SUBQUERY t3 ref a a 5 test.t1.b 1 100.00 Using where; Using index |
1822 | Warnings: |
1823 | Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) |
1824 | insert into t1 values (3,31); |
1825 | @@ -1370,7 +1370,7 @@ |
1826 | explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); |
1827 | id select_type table type possible_keys key key_len ref rows filtered Extra |
1828 | 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index |
1829 | -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where |
1830 | +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index; Using where |
1831 | Warnings: |
1832 | Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) |
1833 | drop table t1, t2, t3; |
1834 | @@ -3546,7 +3546,7 @@ |
1835 | id select_type table type possible_keys key key_len ref rows Extra |
1836 | 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 |
1837 | 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index; Using join buffer |
1838 | -2 DEPENDENT SUBQUERY t1 ref PRIMARY PRIMARY 8 test.t2.i1,const 2 Using where; Using index; Using filesort |
1839 | +2 DEPENDENT SUBQUERY t1 ref PRIMARY PRIMARY 8 test.t2.i1,const 1 Using where; Using index; Using filesort |
1840 | SELECT * FROM t1,t2 |
1841 | WHERE t1.t = (SELECT t1.t FROM t1 |
1842 | WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 |
1843 | @@ -4214,7 +4214,7 @@ |
1844 | EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); |
1845 | id select_type table type possible_keys key key_len ref rows Extra |
1846 | 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where |
1847 | -2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where |
1848 | +2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 1 Using index; Using where |
1849 | SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); |
1850 | a b |
1851 | CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)); |
1852 | @@ -4224,14 +4224,14 @@ |
1853 | EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); |
1854 | id select_type table type possible_keys key key_len ref rows Extra |
1855 | 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where |
1856 | -2 DEPENDENT SUBQUERY t2 index_subquery I1 I1 4 func 2 Using index; Using where |
1857 | +2 DEPENDENT SUBQUERY t2 index_subquery I1 I1 4 func 1 Using index; Using where |
1858 | SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); |
1859 | a b |
1860 | EXPLAIN |
1861 | SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); |
1862 | id select_type table type possible_keys key key_len ref rows Extra |
1863 | 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where |
1864 | -2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where |
1865 | +2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 1 Using index; Using where |
1866 | SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); |
1867 | a b |
1868 | DROP TABLE t1,t2; |
1869 | |
1870 | === modified file 'test/mysql-test/r/type_ranges.result' |
1871 | --- test/mysql-test/r/type_ranges.result 2008-03-11 16:31:13 +0000 |
1872 | +++ test/mysql-test/r/type_ranges.result 2009-04-03 19:39:12 +0000 |
1873 | @@ -70,19 +70,19 @@ |
1874 | show keys from t1; |
1875 | Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment |
1876 | t1 0 PRIMARY 1 auto A 0 NULL NULL BTREE |
1877 | -t1 1 utiny 1 utiny A NULL NULL NULL BTREE |
1878 | -t1 1 tiny 1 tiny A NULL NULL NULL BTREE |
1879 | -t1 1 short 1 short A NULL NULL NULL BTREE |
1880 | -t1 1 any_name 1 medium A NULL NULL NULL BTREE |
1881 | -t1 1 longlong 1 longlong A NULL NULL NULL BTREE |
1882 | -t1 1 real_float 1 real_float A NULL NULL NULL BTREE |
1883 | -t1 1 ushort 1 ushort A NULL NULL NULL BTREE |
1884 | -t1 1 umedium 1 umedium A NULL NULL NULL BTREE |
1885 | -t1 1 ulong 1 ulong A NULL NULL NULL BTREE |
1886 | -t1 1 ulonglong 1 ulonglong A NULL NULL NULL BTREE |
1887 | -t1 1 ulonglong 2 ulong A NULL NULL NULL BTREE |
1888 | -t1 1 options 1 options A NULL NULL NULL BTREE |
1889 | -t1 1 options 2 flags A NULL NULL NULL BTREE |
1890 | +t1 1 utiny 1 utiny A 0 NULL NULL BTREE |
1891 | +t1 1 tiny 1 tiny A 0 NULL NULL BTREE |
1892 | +t1 1 short 1 short A 0 NULL NULL BTREE |
1893 | +t1 1 any_name 1 medium A 0 NULL NULL BTREE |
1894 | +t1 1 longlong 1 longlong A 0 NULL NULL BTREE |
1895 | +t1 1 real_float 1 real_float A 0 NULL NULL BTREE |
1896 | +t1 1 ushort 1 ushort A 0 NULL NULL BTREE |
1897 | +t1 1 umedium 1 umedium A 0 NULL NULL BTREE |
1898 | +t1 1 ulong 1 ulong A 0 NULL NULL BTREE |
1899 | +t1 1 ulonglong 1 ulonglong A 0 NULL NULL BTREE |
1900 | +t1 1 ulonglong 2 ulong A 0 NULL NULL BTREE |
1901 | +t1 1 options 1 options A 0 NULL NULL BTREE |
1902 | +t1 1 options 2 flags A 0 NULL NULL BTREE |
1903 | CREATE UNIQUE INDEX test on t1 ( auto ) ; |
1904 | CREATE INDEX test2 on t1 ( ulonglong,ulong) ; |
1905 | CREATE INDEX test3 on t1 ( medium ) ; |
1906 | |
1907 | === modified file 'test/mysql-test/t/query_cache.test' |
1908 | --- test/mysql-test/t/query_cache.test 2008-11-14 12:08:22 +0000 |
1909 | +++ test/mysql-test/t/query_cache.test 2009-04-03 19:39:12 +0000 |
1910 | @@ -1240,6 +1240,7 @@ |
1911 | --disable_result_log |
1912 | --reap |
1913 | --enable_result_log |
1914 | +--sleep 2 |
1915 | --echo Next select should contain 4 rows, as the insert is long finished. |
1916 | select *, (select count(*) from t2) from t1; |
1917 | reset query cache; |