Merge lp:~vkolesnikov/pbxt/pbxt-bug-349177 into lp:pbxt

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

Subscribers

People subscribed via source and target branches