From: Ole John Aske Date: March 23 2011 1:15pm Subject: bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4252) Bug#11764737 List-Archive: http://lists.mysql.com/commits/133635 X-Bug: 11764737 Message-Id: <20110323131521.9870F218@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============3831089851247646651==" --===============3831089851247646651== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0/ based on revid:magnus.blaudd@stripped 4252 Ole John Aske 2011-03-23 Cherry picked & backported fix for bug#11764737: 'Optimizer is overly eager to request ordered access.' 1) Ensure that JOIN_TAB::sorted and QUICK_SELECT_I::sorted is requested only when strict ordering is required from the handler - Either as a result of the query specifying ORDER/GROUP BY, or the handler being a source in a QUICK access method which require the sources to be ordered 2a Call handler::ha_index_init(int idx, bool sorted) with 'sorted==false' in any access methods requesting a single row (HA_READ_KEY_EXACT) (join_read_key(), ..... 2b) Else: Always use above 'sorted' attributes as arguments to handler::ha_index_init() and other handler methods having a 'bool sorted' argument(::read_range_first()) Furthermore the function 'disable_sorted_access(JOIN_TAB* join_tab)' has been introduced which collect all logic for turning off sort requirement. @ mysql-test/r/partition.result Accept changed result order for this (unordered) result set. modified: mysql-test/r/partition.result sql/ha_ndbcluster.cc sql/opt_range.cc sql/opt_range.h sql/sql_select.cc === modified file 'mysql-test/r/partition.result' --- a/mysql-test/r/partition.result 2010-03-22 12:30:27 +0000 +++ b/mysql-test/r/partition.result 2011-03-23 13:15:16 +0000 @@ -1563,8 +1563,8 @@ insert into t1 values (18446744073709551 select * from t1; a 18446744073709551612 -18446744073709551613 18446744073709551614 +18446744073709551613 18446744073709551615 select * from t1 where a = 18446744073709551615; a @@ -1573,8 +1573,8 @@ delete from t1 where a = 184467440737095 select * from t1; a 18446744073709551612 -18446744073709551613 18446744073709551614 +18446744073709551613 drop table t1; CREATE TABLE t1 ( num int(11) NOT NULL, cs int(11) NOT NULL) === modified file 'sql/ha_ndbcluster.cc' --- a/sql/ha_ndbcluster.cc 2011-03-16 06:53:41 +0000 +++ b/sql/ha_ndbcluster.cc 2011-03-23 13:15:16 +0000 @@ -5575,7 +5575,11 @@ int ha_ndbcluster::index_first(uchar *bu // Start the ordered index scan and fetch the first row // Only HA_READ_ORDER indexes get called by index_first +#ifdef MCP_BUG11764737 const int error= ordered_index_scan(0, 0, TRUE, FALSE, buf, NULL); +#else + const int error= ordered_index_scan(0, 0, m_sorted, FALSE, buf, NULL); +#endif table->status=error ? STATUS_NOT_FOUND: 0; DBUG_RETURN(error); } @@ -5585,7 +5589,11 @@ int ha_ndbcluster::index_last(uchar *buf { DBUG_ENTER("ha_ndbcluster::index_last"); ha_statistic_increment(&SSV::ha_read_last_count); +#ifdef MCP_BUG11764737 const int error= ordered_index_scan(0, 0, TRUE, TRUE, buf, NULL); +#else + const int error= ordered_index_scan(0, 0, m_sorted, TRUE, buf, NULL); +#endif table->status=error ? STATUS_NOT_FOUND: 0; DBUG_RETURN(error); } === modified file 'sql/opt_range.cc' --- a/sql/opt_range.cc 2010-10-06 10:06:47 +0000 +++ b/sql/opt_range.cc 2011-03-23 13:15:16 +0000 @@ -1097,7 +1097,12 @@ SQL_SELECT::~SQL_SELECT() #undef index // Fix for Unixware 7 QUICK_SELECT_I::QUICK_SELECT_I() +#ifdef MCP_BUG11764737 :max_used_key_length(0), +#else + :sorted(false), + max_used_key_length(0), +#endif used_key_parts(0) {} @@ -1109,7 +1114,9 @@ QUICK_RANGE_SELECT::QUICK_RANGE_SELECT(T DBUG_ENTER("QUICK_RANGE_SELECT::QUICK_RANGE_SELECT"); in_ror_merged_scan= 0; +#ifdef MCP_BUG11764737 sorted= 0; +#endif index= key_nr; head= table; key_part_info= head->key_info[index].key_part; @@ -4983,7 +4990,11 @@ QUICK_SELECT_I *TRP_ROR_INTERSECT::make_ { if (!(quick= get_quick_select(param, (*first_scan)->idx, (*first_scan)->sel_arg, alloc)) || +#ifdef MCP_BUG11764737 quick_intrsect->push_quick_back(quick)) +#else + (quick->sorted= 1, quick_intrsect->push_quick_back(quick))) +#endif { delete quick_intrsect; DBUG_RETURN(NULL); @@ -4998,6 +5009,9 @@ QUICK_SELECT_I *TRP_ROR_INTERSECT::make_ DBUG_RETURN(NULL); } quick->file= NULL; +#ifndef MCP_BUG11764737 + quick->sorted= 1; +#endif quick_intrsect->cpk_quick= quick; } quick_intrsect->records= records; @@ -5024,7 +5038,11 @@ QUICK_SELECT_I *TRP_ROR_UNION::make_quic for (scan= first_ror; scan != last_ror; scan++) { if (!(quick= (*scan)->make_quick(param, FALSE, &quick_roru->alloc)) || +#ifdef MCP_BUG11764737 quick_roru->push_quick_back(quick)) +#else + (quick->sorted= 1, quick_roru->push_quick_back(quick))) +#endif DBUG_RETURN(NULL); } quick_roru->records= records; @@ -8454,7 +8472,11 @@ int QUICK_RANGE_SELECT::reset() in_range= FALSE; cur_range= (QUICK_RANGE**) ranges.buffer; +#ifdef MCP_BUG11764737 if (file->inited == handler::NONE && (error= file->ha_index_init(index,1))) +#else + if (file->inited == handler::NONE && (error= file->ha_index_init(index,sorted))) +#endif DBUG_RETURN(error); /* Do not allocate the buffers twice. */ @@ -8671,7 +8693,11 @@ int QUICK_RANGE_SELECT::get_next_prefix( result= file->read_range_first(last_range->min_keypart_map ? &start_key : 0, last_range->max_keypart_map ? &end_key : 0, test(last_range->flag & EQ_RANGE), +#ifdef MCP_BUG11764737 TRUE); +#else + sorted); +#endif if (last_range->flag == (UNIQUE_RANGE | EQ_RANGE)) last_range= 0; // Stop searching @@ -8784,6 +8810,10 @@ QUICK_SELECT_DESC::QUICK_SELECT_DESC(QUI multi_range= NULL; multi_range_buff= NULL; +#ifndef MCP_BUG11764737 + sorted= 1; // 'sorted' as internals use index_last/_prev +#endif + QUICK_RANGE **pr= (QUICK_RANGE**)ranges.buffer; QUICK_RANGE **end_range= pr + ranges.elements; for (; pr!=end_range; pr++) @@ -8802,6 +8832,16 @@ QUICK_SELECT_DESC::QUICK_SELECT_DESC(QUI } +#ifndef MCP_BUG11764737 +int QUICK_SELECT_DESC::reset(void) +{ + sorted= 1; // 'sorted' index access is required by internals + rev_it.rewind(); + return QUICK_RANGE_SELECT::reset(); +} +#endif + + int QUICK_SELECT_DESC::get_next() { DBUG_ENTER("QUICK_SELECT_DESC::get_next"); @@ -10208,11 +10248,21 @@ TRP_GROUP_MIN_MAX::make_quick(PARAM *par if (quick_prefix_records == HA_POS_ERROR) quick->quick_prefix_select= NULL; /* Can't construct a quick select. */ else + { /* Make a QUICK_RANGE_SELECT to be used for group prefix retrieval. */ quick->quick_prefix_select= get_quick_select(param, param_idx, index_tree, &quick->alloc); +#ifndef MCP_BUG11764737 + if (!quick->quick_prefix_select) + { + delete quick; + DBUG_RETURN(NULL); + } + quick->quick_prefix_select->sorted= 1; +#endif + } /* Extract the SEL_ARG subtree that contains only ranges for the MIN/MAX attribute, and create an array of QUICK_RANGES to be used by the @@ -10603,6 +10653,10 @@ int QUICK_GROUP_MIN_MAX_SELECT::reset(vo DBUG_ENTER("QUICK_GROUP_MIN_MAX_SELECT::reset"); head->set_keyread(TRUE); /* We need only the key attributes */ + /* + Request ordered index access as usage of ::index_last(), + ::index_first() within QUICK_GROUP_MIN_MAX_SELECT depends on it. + */ if ((result= file->ha_index_init(index,1))) DBUG_RETURN(result); if (quick_prefix_select && quick_prefix_select->reset()) === modified file 'sql/opt_range.h' --- a/sql/opt_range.h 2010-10-06 10:06:47 +0000 +++ b/sql/opt_range.h 2011-03-23 13:15:16 +0000 @@ -762,7 +762,11 @@ public: int get_type() { return QS_TYPE_RANGE_DESC; } private: bool range_reads_after_key(QUICK_RANGE *range); +#ifdef MCP_BUG11764737 int reset(void) { rev_it.rewind(); return QUICK_RANGE_SELECT::reset(); } +#else + int reset(void); +#endif List rev_ranges; List_iterator rev_it; uint used_key_parts; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-02-23 10:42:16 +0000 +++ b/sql/sql_select.cc 2011-03-23 13:15:16 +0000 @@ -1743,6 +1743,19 @@ JOIN::save_join_tab() } +#ifndef MCP_BUG11764737 +static void +disable_sorted_access(JOIN_TAB* join_tab) +{ + join_tab->sorted= 0; + if (join_tab->select && join_tab->select->quick) + { + join_tab->select->quick->sorted= 0; + } +} +#endif + + /** Exec select. @@ -1912,7 +1925,11 @@ JOIN::exec() DBUG_PRINT("info", ("%s", thd->proc_info)); if (!curr_join->sort_and_group && curr_join->const_tables != curr_join->tables) +#ifdef MCP_BUG11764737 curr_join->join_tab[curr_join->const_tables].sorted= 0; +#else + disable_sorted_access(&curr_join->join_tab[curr_join->const_tables]); +#endif if ((tmp_error= do_select(curr_join, (List *) 0, curr_tmp_table, 0))) { error= tmp_error; @@ -2076,7 +2093,11 @@ JOIN::exec() curr_join->group_list= 0; if (!curr_join->sort_and_group && curr_join->const_tables != curr_join->tables) +#ifdef MCP_BUG11764737 curr_join->join_tab[curr_join->const_tables].sorted= 0; +#else + disable_sorted_access(&curr_join->join_tab[curr_join->const_tables]); +#endif if (setup_sum_funcs(curr_join->thd, curr_join->sum_funcs) || (tmp_error= do_select(curr_join, (List *) 0, curr_tmp_table, 0))) @@ -6846,9 +6867,15 @@ make_join_readinfo(JOIN *join, ulonglong uint i; bool statistics= test(!(join->select_options & SELECT_DESCRIBE)); bool ordered_set= 0; - bool sorted= 1; DBUG_ENTER("make_join_readinfo"); +#ifdef MCP_BUG11764737 + bool sorted= 1; +#else + /* First table sorted if ORDER or GROUP BY was specified */ + bool sorted= (join->order || join->group_list); +#endif + for (i=join->const_tables ; i < join->tables ; i++) { JOIN_TAB *tab=join->join_tab+i; @@ -12156,7 +12183,12 @@ join_read_key(JOIN_TAB *tab) if (!table->file->inited) { +#ifdef MCP_BUG11764737 + table->file->ha_index_init(tab->ref.key, 0); +#else + DBUG_ASSERT(!tab->sorted); // Don't expect sort req. for single row. table->file->ha_index_init(tab->ref.key, tab->sorted); +#endif } if (cmp_buffer_with_ref(tab) || (table->status & (STATUS_GARBAGE | STATUS_NO_PARENT | STATUS_NULL_ROW))) @@ -12428,7 +12460,11 @@ join_read_last(JOIN_TAB *tab) tab->read_record.index=tab->index; tab->read_record.record=table->record[0]; if (!table->file->inited) +#ifdef MCP_BUG11764737 table->file->ha_index_init(tab->index, 1); +#else + table->file->ha_index_init(tab->index, tab->sorted); +#endif if ((error= tab->table->file->index_last(tab->table->record[0]))) return report_error(table, error); return 0; @@ -12452,7 +12488,11 @@ join_ft_read_first(JOIN_TAB *tab) TABLE *table= tab->table; if (!table->file->inited) +#ifdef MCP_BUG11764737 table->file->ha_index_init(tab->ref.key, 1); +#else + table->file->ha_index_init(tab->ref.key, tab->sorted); +#endif #if NOT_USED_YET /* as ft-key doesn't use store_key's, see also FT_SELECT::init() */ if (cp_buffer_from_ref(tab->join->thd, table, &tab->ref)) --===============3831089851247646651== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/ole.john.aske@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: ole.john.aske@stripped\ # b31agywhlki5f0gb # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0/ # testament_sha1: 1ca7a7c01e8397776cf6fd650d480bfc9da65be8 # timestamp: 2011-03-23 14:15:21 +0100 # base_revision_id: magnus.blaudd@stripped\ # ev1pi0ag7bju7pkg # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWdA7k5wAB5ffgHUweff//3// /mq////+YA5F193OoZw4Lqpssaati3O5457tbZpVE20STYZVsYHCRRBI8iaPUzRGRoym1PQTIyNG hoNAGIMR6mglCA0RpNDUm1TaaNTT0mE0AAABoGgaGhoJKpmmSm9TU0AAwTQ0yAGQHqZNDQBoBiAk QiCaEyaaqfqfqM0FT0G01J6Q9IAaB+qAGgAHNGjQ0wgGmBNNAGQ0MQBoxGhgjIAJJAgAEmNCZNGm kZMmSmmYoNGjIHpABspLcsKwP3tGqxIlUikYdCt7VWDRw7LyZ+/Dt0t6bg25tsjFtsbbbbioQbb0 uvbz9kPktzUvV49SFl3LUx9MECSk9lwc/IvROvsdiHN0jcOTI6u18oZR8FWUS4oYPPpwLhi2wbIE bWsfblHtily7IVuXPDdplqEjFtoA5c9skXudyhoo7KQvCawxiEHyjKk3RCuGJ1o9ZqHFplhcklj/ GP83Z57phJKpCoYQcmTmRQyUGGwr5O6NH72bL6sf94Ya/HpRqt2/5diVPiR+EYkMG0hsGxDaBsCy 36H1iR2nZ4R47/GZmfHxHr7Hbo0NM7LFtj1QHPOFqqEZ+OGWwmrYMu7OHGNSVnBSFweJOw9LSyKI I9FZCKNbqpEIPJSAq2FIjFyNty0Zu0wXiDbaCoz15devt6/k4eh337/p5zy8+fRngEJC5EcR70V6 lp/p9ntVdcP4rZqyr3vbhHGqjPbwSPzzinXqhTXrt6v8vOhKun9XzKFN7K0SL68KXhzR9HI5nz5h quviQxjKOf6PKam8WU3dDdQqU3051IHTb0tC4lieS9ENSaqPC2NtB7iE/OuWTM6ipy819xu0ov0H X393e35LNEOarSvM3k3wSo1eGqbgw7CRO6mCMXGJC262yUy61KwOVLE82S+VhfnhesELEx1Qmcgw zHORUWWWg5e7omM7n4wZzSUxDDbE7fk58T/ZAseRb0PwLysNNJLqG8/gQO/QQmajuORuGC9eAkeS O3ga0zBsZyl82NpFAwO7j2JJixjvWoolTlqwklSaIA8jf41rn8a18+6Brx8t1+QBMFdOSYcQtIJI Lx6Ise44rWfU0vK9+OSNVckcdvHMRuaphDGxfQwUzCbQdLJYh+yGNQdTtaRSepDCDDKqGXFAoSB3 13Pw0FNyECLsSfkyrFQYxS9Zcm7oLRdUZmZlgQslFFCrA5YAQtB4n21X3ToMS5RTNU8hJnQPTUIU a4Es8Ys52XlW2RovdYiuDF4wHxeRYeJwgqbwgG8yvYy+BNLFilIGgpBUY2TNvKCHka2TFA3KBi/N 4exRn1w+/hRLwETQvGprdrS1Eh6ZfPDM6DKmsSmnQQcxdMJOtk8ndayceKSIoIYrHlRPfKF+l2oG tvF6QnhzjUoHFGcRLWaUVF8d0Ej7lV9KsSos01OfrsnogsEhUmSWxSxCMLELsVCM5XJUmgGaaxA7 PK0y5ZYY6kbMiMS7k9JF7nEhVFK+tU5VAi6ZjTSNj0aGfbqdx0BrL8NELVM1Z64ttdk1foFFVZiU iVc+PASgUSUhBFdBibSFllvIuLiuqAxuhotTkkTzNPQFknHkT9E5deZhtCyoyLUarjKDrmcPm0SY hEYXJ5dYy12RQxkdRG0ZbYcCjdZX0GeEGWBQ4xmYOKoIS1Wi0KT4tMiKXN424S9ziTJkmKbD+MOc 8ZxPjNb0zxpwhjgUhGclhXZm5K6I6W3jiJckputC2obbY5jMtBznMrgcBSXVFB0JRvEdhgbJzDnA 1tQn5jYnpDN50UYbhJuhWlHUbDIqOlLuZarqY0SM5bmaCIji7M8FJMTny3lFgbE9+2ZcVBDYuy1a J244WqzMEC0gwM5LGfd521yBhMuQaDOnmbiwzNUGogQaRwnU6qah5wsXC89xPtUqJqFiTnZHtxTh ZyTHqAtXuZoIOLwksSdULGMyUIZT5FYsu4vodFU7OMdvDZrhLYbzgqio4lkTSda4rsJ4hjfodjhL JzOc1sJmk+wcrHzzNqJeJJKFwnHWUKZRylRKkSsK0OPehGZNiwsGrI4ttYqW3GC97NTEMSeJeUHU SiWU0nJS00bdZGSZNaaAd27PTtOwnQaFgmVqdNpG+L5Uj0cSUhxG4yaFFAs1+3R3+/8dP+Ls8qh+ svw1SHMXuQxAcKIzCUqiywiRSbMNwGVpj759nGG0ZNDbb9wsuKC5zs3707bYxvxomgmIc8J8HIjM crQBKTsGksuBZ+b9cvf3duHTOwpVY2mDITgjtUpRnyIoBCFFSVu+3mwXpHyX/6en3BEK6cVgs4+X 6rocLghlDszQXk5yNH5KBI9smC9aQnWUZAhrqIaZqZjUH5K5/30qf+thcCGjIKlbaMRyUNWx2Mg1 5V3MQV9k8CPuSR+oap6kdNaQcx1mR7gh0HnQhozmse6q9g1a4b5eqm3uogR0iaRCbe4AbETAIjxu xqMr9o9LGnGexs0LicI4SQIwISKmuiYPON4JilJS6WypK5B4SCURhUDLNAwzA7FbV8EVBol0kOK0 cxaVpaAGhWGqJP57+lfLg0fEudBRMVigk4bF1CgUkEQU/4P0AOZezu872D0v+wvl9qS4xau1pEI9 OJmJRD+AQNZI7jbOUG93Ex5FQyzH18hxXpzrKv5E61gkEXT06JHHtEU3X7QFzNAnIYlkpgJ1zmMq O065y9B6RQes0GsoNMYUmZEdVdMyBxec20UHRqVCBoRuXhfckjjB/c8zMyaDV/4+smmqYhs6csAi 5GhsMqLxdvGuYchhovm4WIHSbpQWtAdyVpMa7EoPJF6CZIaR3A4W1m4FxQuoHkiBeTDJKSPw28Sl koJKQwI8RQ+6bDnqqJxdZs3PccIHY1+oS+fz9/XTRRynz1153UcVclBdMENNCKfYTw4OOJtGuO9x x3nRuu7HVlI1ZlOQYJyMjD0RyRpAL1lpcnT9AouAcF5pbjyLDRiqesoGa9iy09W2W23jEGyYFLQh mEnAz007rVjfJ8uIIqN1oOPA4S1JqulrPW63ECz8RKuiWcARq1WTMyMFEJY5btlH1MXkZaPCOyeN bk6ilIvm3xtxuenaTvxtdSdNyegyJEhziAFgTQTzNNIZBakq3pHoaAiTVpI56wQ7i/bccEiQ9JNx 7hwMOOs0HgbzuPltnInMl3azFJOPrKzIWURdRvB3zRu5q/070OkfAfWZbUZo9qsLB+wiKoXZzN63 nkhaDm4u8vJLAcxwxVGIUG+cDtChLx5n3IZEd69ZrMpKwVVLa6sp5LqJ9emRoscUF5/ERIjEq0BB QPGcEh4b0xtwEu1a6RvKXAf0oNzJUvtWjiCbo35/GCONL3pKRdcIfUYCRgr7WahSMRx4AUbOWy8T QcZ3Yaq7s9GrcUitqrLWTNDtujXOyGS4HqW5bg+WZXmy0hoA88hQwdn+pr0ec5UsEPp9M108wCwt YzGcOb4NtLl1dysTkLBkoFyA9T4h412Dc0bxKALLsyGY951e4xOC4gcQyArlK42Ws4BYt35H6tSO 4wCBbQRYAhIkMNGlnmKiZ6u0SY66OPtXTskUCTHVIDK29fHVea+vcXgK41h4sST/MvR3q/FpjOZN wbFXhvVvtC2xUicFfg8+g6arc8GYU3iSKLAG2ESNJiLm1F7MpB2LYiElkCKE8ZQSAyc8pStj8RzC weBYPXgjIBjTTAmDUIqi5Z8CeF1hUrRUq/t1gG6mANiqzP7TpEyHeV1bALGz73IXJ8A7FIp6RmI6 BmILgCNIEPYy5Mtcy2LkyEmo6aFNphI2UF0olRCQBmsjjNqZDJZZLW+gVBzwv6wd8FV9S6jetV9Y dqtfiqrBeG08CGoJpl9hYW6AXYXhjSFfmfT63vHR6R0O6W9jYfLChuzXJryGqXMGCXddLu6ikDfV ytyeRQw2G8nYhbToaMRh9CHdz74FXYe4qoG7bGd1jXpt4YuG3hI5z36k7xvJwoCfQq0AVWxKT2DM 6g8MMfkmcS/fA1VNk0/he10ag3HeTlNIRkYYEqRKIytdtiwwbJLGI8MWIo+caQIo46gDXLYqnbV4 0Cht86hDu0yDpIQEyWIQeaPY2228IiTcHxBMEcEjUM6lu6XJQ+58U9ANUAfPN3yxJjpg3Pkchlth VTUbIWq0ogtVlAWs7SpsfA+M7zTFFtBUIg4nyprcfNCj6VBE53T24BFkhYa3rpuNUI8y0Q6jtygJ /bW59q94Sk0thpcX50oejnV6mkBisnOCTJTVWzeuXrJHQHevA4HqKg+z3I61zTgRVgBsGd2bH0dL x+kzD36w1Lr3HBYXBI2hWmTej076lfEDYV4mG40lBG2g/QPURLtKvSHSthcbSz7Sc6t7i7m5IgX5 kFDEkOQuon1Qq2MOhJPGzNJ3Bxt7EdipN4lQR7DJL8xyvlhrKBslejW3DBCxjCH/F3JFOFCQ0DuT nA== --===============3831089851247646651==--