3354 Marc Alff 2011-04-04 [merge]
Merge mysql-trunk --> mysql-trunk-lock-order
modified:
mysql-test/r/func_group.result
mysql-test/r/order_by_icp_mrr.result
mysql-test/r/order_by_none.result
mysql-test/t/func_group.test
sql/item.cc
sql/item_sum.cc
sql/opt_range.cc
sql/opt_range.h
sql/sql_lex.cc
sql/sql_parse.cc
sql/sql_select.cc
sql/sql_update.cc
sql/table.h
3353 Marc Alff 2011-04-03
Lock order, continued
modified:
sql/debug_lock_order.cc
sql/mysqld.cc
=== modified file 'mysql-test/r/func_group.result'
--- a/mysql-test/r/func_group.result 2011-02-02 09:21:41 +0000
+++ b/mysql-test/r/func_group.result 2011-04-02 19:42:50 +0000
@@ -1737,6 +1737,15 @@ SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',
SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',0) / '0b1111111111111111111111111111111111111111111111111111111111111111111111111' ^ (RAND()));
SELECT RELEASE_LOCK('aaaaaaaaaaaaaaaaa');
#
+# Bug #11766094 - 59132: MIN() AND MAX() REMOVE UNSIGNEDNESS
+#
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (18446668621106209655);
+SELECT MAX(LENGTH(a)), LENGTH(MAX(a)), MIN(a), MAX(a), CONCAT(MIN(a)), CONCAT(MAX(a)) FROM t1;
+MAX(LENGTH(a)) LENGTH(MAX(a)) MIN(a) MAX(a) CONCAT(MIN(a)) CONCAT(MAX(a))
+20 20 18446668621106209655 18446668621106209655 18446668621106209655 18446668621106209655
+DROP TABLE t1;
+#
End of 5.1 tests
#
# Bug#52123 Assertion failed: aggregator == aggr->Aggrtype(),
=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result 2011-02-07 09:46:53 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result 2011-04-01 14:04:52 +0000
@@ -2536,7 +2536,7 @@ CREATE TABLE t1 (a INT,KEY (a));
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
EXPLAIN SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index a a 5 NULL 10 Using where; Using index; Using filesort
+1 SIMPLE t1 index a a 5 NULL 10 Using where; Using index
SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
a 1
10 1
=== modified file 'mysql-test/r/order_by_none.result'
--- a/mysql-test/r/order_by_none.result 2011-02-07 09:46:53 +0000
+++ b/mysql-test/r/order_by_none.result 2011-04-01 14:04:52 +0000
@@ -2535,7 +2535,7 @@ CREATE TABLE t1 (a INT,KEY (a));
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
EXPLAIN SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index a a 5 NULL 10 Using where; Using index; Using filesort
+1 SIMPLE t1 index a a 5 NULL 10 Using where; Using index
SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
a 1
10 1
=== modified file 'mysql-test/t/func_group.test'
--- a/mysql-test/t/func_group.test 2011-02-02 09:21:41 +0000
+++ b/mysql-test/t/func_group.test 2011-04-02 19:42:50 +0000
@@ -1118,6 +1118,16 @@ SELECT RELEASE_LOCK('aaaaaaaaaaaaaaaaa')
--enable_result_log
+
+--echo #
+--echo # Bug #11766094 - 59132: MIN() AND MAX() REMOVE UNSIGNEDNESS
+--echo #
+
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (18446668621106209655);
+SELECT MAX(LENGTH(a)), LENGTH(MAX(a)), MIN(a), MAX(a), CONCAT(MIN(a)), CONCAT(MAX(a)) FROM t1;
+DROP TABLE t1;
+
--echo #
--echo End of 5.1 tests
=== modified file 'sql/item.cc'
--- a/sql/item.cc 2011-03-30 07:42:03 +0000
+++ b/sql/item.cc 2011-04-02 19:42:50 +0000
@@ -7601,7 +7601,7 @@ String *Item_cache_int::val_str(String *
DBUG_ASSERT(fixed == 1);
if (!has_value())
return NULL;
- str->set(value, default_charset());
+ str->set_int(value, unsigned_flag, default_charset());
return str;
}
=== modified file 'sql/item_sum.cc'
--- a/sql/item_sum.cc 2011-03-22 11:44:40 +0000
+++ b/sql/item_sum.cc 2011-04-01 14:04:52 +0000
@@ -2891,7 +2891,8 @@ int group_concat_key_cmp_with_order(cons
uint offset= (field->offset(field->table->record[0]) -
table->s->null_bytes);
if ((res= field->cmp((uchar*)key1 + offset, (uchar*)key2 + offset)))
- return (*order_item)->asc ? res : -res;
+ return ((*order_item)->direction == ORDER::ORDER_ASC) ? res : -res;
+
}
}
/*
@@ -3432,7 +3433,7 @@ void Item_func_group_concat::print(Strin
if (i)
str->append(',');
orig_args[i + arg_count_field]->print(str, query_type);
- if (order[i]->asc)
+ if (order[i]->direction == ORDER::ORDER_ASC)
str->append(STRING_WITH_LEN(" ASC"));
else
str->append(STRING_WITH_LEN(" DESC"));
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2011-03-22 11:44:40 +0000
+++ b/sql/opt_range.cc 2011-04-01 14:04:52 +0000
@@ -742,6 +742,12 @@ public:
bool is_ror_scan;
/* Number of ranges in the last checked tree->key */
uint n_ranges;
+
+ /*
+ The sort order the range access method must be able
+ to provide. Three-value logic: asc/desc/don't care
+ */
+ ORDER::enum_order order_direction;
};
class TABLE_READ_PLAN;
@@ -2142,6 +2148,8 @@ static int fill_used_fields_bitmap(PARAM
limit Query limit
force_quick_range Prefer to use range (instead of full table scan) even
if it is more expensive.
+ interesting_order The sort order the range access method must be able
+ to provide. Three-value logic: asc/desc/don't care
NOTES
Updates the following in the select parameter:
@@ -2197,9 +2205,9 @@ static int fill_used_fields_bitmap(PARAM
*/
int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
- table_map prev_tables,
- ha_rows limit, bool force_quick_range,
- bool ordered_output)
+ table_map prev_tables,
+ ha_rows limit, bool force_quick_range,
+ const ORDER::enum_order interesting_order)
{
uint idx;
double scan_time;
@@ -2260,7 +2268,8 @@ int SQL_SELECT::test_quick_select(THD *t
param.imerge_cost_buff_size= 0;
param.using_real_indexes= TRUE;
param.remove_jump_scans= TRUE;
- param.force_default_mrr= ordered_output;
+ param.force_default_mrr= (interesting_order != ORDER::ORDER_NOT_RELEVANT);
+ param.order_direction= interesting_order;
thd->no_errors=1; // Don't warn about NULL
init_sql_alloc(&alloc, thd->variables.range_alloc_block_size, 0);
@@ -2386,10 +2395,12 @@ int SQL_SELECT::test_quick_select(THD *t
/*
Simultaneous key scans and row deletes on several handler
objects are not allowed so don't use ROR-intersection for
- table deletes.
+ table deletes. Also, ROR-intersection cannot return rows in
+ descending order
*/
if ((thd->lex->sql_command != SQLCOM_DELETE) &&
- thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE))
+ thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE) &&
+ interesting_order != ORDER::ORDER_DESC)
{
/*
Get best non-covering ROR-intersection plan and prepare data for
@@ -2413,7 +2424,9 @@ int SQL_SELECT::test_quick_select(THD *t
}
else
{
- if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE))
+ // Cannot return rows in descending order.
+ if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE) &&
+ interesting_order != ORDER::ORDER_DESC)
{
/* Try creating index_merge/ROR-union scan. */
SEL_IMERGE *imerge;
@@ -4576,6 +4589,9 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
!param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT))
DBUG_RETURN(NULL);
+ if (param->order_direction == ORDER::ORDER_DESC)
+ DBUG_RETURN(NULL);
+
/*
Step1: Collect ROR-able SEL_ARGs and create ROR_SCAN_INFO for each of
them. Also find and save clustered PK scan if there is one.
@@ -9653,6 +9669,9 @@ get_best_group_min_max(PARAM *param, SEL
DBUG_RETURN(NULL);
if (table->s->keys == 0) /* There are no indexes to use. */
DBUG_RETURN(NULL);
+ /* Cannot do reverse ordering */
+ if (param->order_direction == ORDER::ORDER_DESC)
+ DBUG_RETURN(NULL);
/* Check (SA1,SA4) and store the only MIN/MAX argument - the C attribute.*/
if (join->make_sum_func_list(join->all_fields, join->fields_list, 1))
=== modified file 'sql/opt_range.h'
--- a/sql/opt_range.h 2011-03-22 11:44:40 +0000
+++ b/sql/opt_range.h 2011-04-01 14:04:52 +0000
@@ -276,7 +276,15 @@ public:
/* Range end should be called when we have looped over the whole index */
virtual void range_end() {}
- virtual bool reverse_sorted() = 0;
+ /**
+ Whether the range access method returns records in reverse order.
+ */
+ virtual bool reverse_sorted() const = 0;
+ /**
+ Whether the range access method is capable of returning records
+ in reverse order.
+ */
+ virtual bool reverse_sort_possible() const = 0;
virtual bool unique_key_range() { return false; }
virtual bool clustered_pk_range() { return false; }
@@ -472,7 +480,8 @@ public:
void range_end();
int get_next_prefix(uint prefix_length, uint group_key_parts,
uchar *cur_prefix);
- bool reverse_sorted() { return 0; }
+ bool reverse_sorted() const { return false; }
+ bool reverse_sort_possible() const { return true; }
bool unique_key_range();
int init_ror_merged_scan(bool reuse_handler);
void save_last_pos()
@@ -572,7 +581,8 @@ public:
void need_sorted_output(bool sort) { DBUG_ASSERT(!sort); /* Can't do it */ }
int reset(void);
int get_next();
- bool reverse_sorted() { return false; }
+ bool reverse_sorted() const { return false; }
+ bool reverse_sort_possible() const { return false; }
bool unique_key_range() { return false; }
int get_type() { return QS_TYPE_INDEX_MERGE; }
void add_keys_and_lengths(String *key_names, String *used_lengths);
@@ -650,7 +660,8 @@ public:
void need_sorted_output(bool sort) { DBUG_ASSERT(!sort); /* Can't do it */ }
int reset(void);
int get_next();
- bool reverse_sorted() { return false; }
+ bool reverse_sorted() const { return false; }
+ bool reverse_sort_possible() const { return false; }
bool unique_key_range() { return false; }
int get_type() { return QS_TYPE_ROR_INTERSECT; }
void add_keys_and_lengths(String *key_names, String *used_lengths);
@@ -721,7 +732,8 @@ public:
void need_sorted_output(bool sort) { DBUG_ASSERT(!sort); /* Can't do it */ }
int reset(void);
int get_next();
- bool reverse_sorted() { return false; }
+ bool reverse_sorted() const { return false; }
+ bool reverse_sort_possible() const { return false; }
bool unique_key_range() { return false; }
int get_type() { return QS_TYPE_ROR_UNION; }
void add_keys_and_lengths(String *key_names, String *used_lengths);
@@ -863,7 +875,8 @@ public:
void need_sorted_output(bool sort) { /* always do it */ }
int reset();
int get_next();
- bool reverse_sorted() { return false; }
+ bool reverse_sorted() const { return false; }
+ bool reverse_sort_possible() const { return false; }
bool unique_key_range() { return false; }
int get_type() { return QS_TYPE_GROUP_MIN_MAX; }
void add_keys_and_lengths(String *key_names, String *used_lengths);
@@ -885,7 +898,8 @@ public:
QUICK_SELECT_DESC(QUICK_RANGE_SELECT *q, uint used_key_parts,
bool *create_err);
int get_next();
- bool reverse_sorted() { return 1; }
+ bool reverse_sorted() const { return true; }
+ bool reverse_sort_possible() const { return true; }
int get_type() { return QS_TYPE_RANGE_DESC; }
QUICK_SELECT_I *make_reverse(uint used_key_parts_arg)
{
@@ -921,7 +935,8 @@ class SQL_SELECT :public Sql_alloc {
bool check_quick(THD *thd, bool force_quick_range, ha_rows limit)
{
key_map tmp(key_map::ALL_BITS);
- return test_quick_select(thd, tmp, 0, limit, force_quick_range, FALSE) < 0;
+ return test_quick_select(thd, tmp, 0, limit, force_quick_range,
+ ORDER::ORDER_NOT_RELEVANT) < 0;
}
inline bool skip_record(THD *thd, bool *skip_record)
{
@@ -929,8 +944,8 @@ class SQL_SELECT :public Sql_alloc {
return thd->is_error();
}
int test_quick_select(THD *thd, key_map keys, table_map prev_tables,
- ha_rows limit, bool force_quick_range,
- bool ordered_output);
+ ha_rows limit, bool force_quick_range,
+ const ORDER::enum_order interesting_order);
};
=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc 2011-03-11 09:35:38 +0000
+++ b/sql/sql_lex.cc 2011-04-01 14:04:52 +0000
@@ -2196,7 +2196,7 @@ void st_select_lex::print_order(String *
}
else
(*order->item)->print(str, query_type);
- if (!order->asc)
+ if (order->direction == ORDER::ORDER_DESC)
str->append(STRING_WITH_LEN(" desc"));
if (order->next)
str->append(',');
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2011-03-17 17:39:31 +0000
+++ b/sql/sql_parse.cc 2011-04-01 14:04:52 +0000
@@ -5796,7 +5796,7 @@ bool add_to_list(THD *thd, SQL_I_List<OR
DBUG_RETURN(1);
order->item_ptr= item;
order->item= &order->item_ptr;
- order->asc = asc;
+ order->direction= (asc ? ORDER::ORDER_ASC : ORDER::ORDER_DESC);
order->free_me=0;
order->used=0;
order->counter_used= 0;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-03-29 07:20:17 +0000
+++ b/sql/sql_select.cc 2011-04-01 14:04:52 +0000
@@ -4516,7 +4516,6 @@ static ha_rows get_quick_record_count(TH
TABLE *table,
const key_map *keys,ha_rows limit)
{
- int error;
DBUG_ENTER("get_quick_record_count");
uchar buff[STACK_BUFF_ALLOC];
if (check_stack_overrun(thd, STACK_MIN_SIZE, buff))
@@ -4524,8 +4523,13 @@ static ha_rows get_quick_record_count(TH
if (select)
{
select->head=table;
- if ((error= select->test_quick_select(thd, *(key_map *)keys,(table_map) 0,
- limit, 0, FALSE)) == 1)
+ int error= select->test_quick_select(thd,
+ *keys,
+ 0, //empty table_map
+ limit,
+ false, //don't force quick range
+ ORDER::ORDER_NOT_RELEVANT);
+ if (error == 1)
DBUG_RETURN(select->quick->records);
if (error == -1)
{
@@ -9895,13 +9899,14 @@ static bool make_join_select(JOIN *join,
if (sel->cond && !sel->cond->fixed)
sel->cond->quick_fix_field();
- if (sel->test_quick_select(thd, tab->keys,
- used_tables & ~ current_map,
- (join->select_options &
- OPTION_FOUND_ROWS ?
- HA_POS_ERROR :
- join->unit->select_limit_cnt), 0,
- FALSE) < 0)
+ if (sel->test_quick_select(thd, tab->keys,
+ used_tables & ~ current_map,
+ (join->select_options &
+ OPTION_FOUND_ROWS ?
+ HA_POS_ERROR :
+ join->unit->select_limit_cnt),
+ false, // don't force quick range
+ ORDER::ORDER_NOT_RELEVANT) < 0)
{
/*
Before reporting "Impossible WHERE" for the whole query
@@ -9914,8 +9919,9 @@ static bool make_join_select(JOIN *join,
(join->select_options &
OPTION_FOUND_ROWS ?
HA_POS_ERROR :
- join->unit->select_limit_cnt),0,
- FALSE) < 0)
+ join->unit->select_limit_cnt),
+ false, //don't force quick range
+ ORDER::ORDER_NOT_RELEVANT) < 0)
DBUG_RETURN(1); // Impossible WHERE
}
else
@@ -18424,9 +18430,12 @@ test_if_quick_select(JOIN_TAB *tab)
{
delete tab->select->quick;
tab->select->quick=0;
- return tab->select->test_quick_select(tab->join->thd, tab->keys,
- (table_map) 0, HA_POS_ERROR, 0,
- FALSE);
+ return tab->select->test_quick_select(tab->join->thd,
+ tab->keys,
+ 0, // empty table map
+ HA_POS_ERROR,
+ false, // don't force quick range
+ ORDER::ORDER_NOT_RELEVANT);
}
@@ -19651,9 +19660,11 @@ static int test_if_order_by_key(ORDER *o
if (key_part->field != field || !field->part_of_sortkey.is_set(idx))
DBUG_RETURN(0);
+ const ORDER::enum_order keypart_order=
+ (key_part->key_part_flag & HA_REVERSE_SORT) ?
+ ORDER::ORDER_DESC : ORDER::ORDER_ASC;
/* set flag to 1 if we can use read-next on key, else to -1 */
- flag= ((order->asc == !(key_part->key_part_flag & HA_REVERSE_SORT)) ?
- 1 : -1);
+ flag= (order->direction == keypart_order) ? 1 : -1;
if (reverse && flag != reverse)
DBUG_RETURN(0);
reverse=flag; // Remember if reverse
@@ -20110,13 +20121,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
new_ref_key_map.set_bit(new_ref_key); // only for new_ref_key.
select->quick= 0;
- if (select->test_quick_select(tab->join->thd, new_ref_key_map, 0,
+ if (select->test_quick_select(tab->join->thd,
+ new_ref_key_map,
+ 0, // empty table_map
(tab->join->select_options &
OPTION_FOUND_ROWS) ?
HA_POS_ERROR :
- tab->join->unit->select_limit_cnt,0,
- TRUE) <=
- 0)
+ tab->join->unit->select_limit_cnt,
+ false, // don't force quick range
+ order->direction) <= 0)
goto use_filesort;
}
ref_key= new_ref_key;
@@ -20161,11 +20174,14 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
key_map map; // Force the creation of quick select
map.set_bit(best_key); // only best_key.
select->quick= 0;
- select->test_quick_select(join->thd, map, 0,
+ select->test_quick_select(join->thd,
+ map,
+ 0, // empty table_map
join->select_options & OPTION_FOUND_ROWS ?
HA_POS_ERROR :
join->unit->select_limit_cnt,
- TRUE, FALSE);
+ true, // force quick range
+ order->direction);
}
order_direction= best_key_direction;
/*
@@ -20195,18 +20211,13 @@ check_reverse_order:
*/
if (select->quick->reverse_sorted())
goto skipped_filesort;
- else
- {
- int quick_type= select->quick->get_type();
- if (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE ||
- quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT ||
- quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION ||
- quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
- {
- tab->limit= 0;
- goto use_filesort; // Use filesort
- }
- }
+
+ /*
+ test_quick_select() should not create a quick that cannot do
+ reverse ordering
+ */
+ DBUG_ASSERT((select->quick == save_quick) ||
+ select->quick->reverse_sort_possible());
}
}
@@ -20316,7 +20327,7 @@ check_reverse_order:
/*
Cleanup:
We may have both a 'select->quick' and 'save_quick' (original)
- at this point. Delete the one that we wan't use.
+ at this point. Delete the one that we won't use.
*/
skipped_filesort:
@@ -20823,7 +20834,7 @@ SORT_FIELD *make_unireg_sortorder(ORDER
}
else
pos->item= *order->item;
- pos->reverse=! order->asc;
+ pos->reverse= (order->direction == ORDER::ORDER_DESC);
DBUG_ASSERT(pos->field != NULL || pos->item != NULL);
}
*length=count;
@@ -21336,7 +21347,7 @@ create_distinct_group(THD *thd, Item **r
*/
ord->item= ref_pointer_array;
}
- ord->asc=1;
+ ord->direction= ORDER::ORDER_ASC;
*prev=ord;
prev= &ord->next;
}
@@ -21413,7 +21424,7 @@ test_if_subpart(ORDER *a,ORDER *b)
for (; a && b; a=a->next,b=b->next)
{
if ((*a->item)->eq(*b->item,1))
- a->asc=b->asc;
+ a->direction= b->direction;
else
return 0;
}
=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc 2011-02-25 16:41:57 +0000
+++ b/sql/sql_update.cc 2011-04-01 14:04:52 +0000
@@ -1732,7 +1732,7 @@ loop_end:
/* Make an unique key over the first field to avoid duplicated updates */
bzero((char*) &group, sizeof(group));
- group.asc= 1;
+ group.direction= ORDER::ORDER_ASC;
group.item= (Item**) temp_fields.head_ref();
tmp_param->quick_group=1;
=== modified file 'sql/table.h'
--- a/sql/table.h 2011-03-30 11:43:32 +0000
+++ b/sql/table.h 2011-04-01 14:04:52 +0000
@@ -190,16 +190,22 @@ private:
typedef struct st_order {
struct st_order *next;
- Item **item; /* Point at item in select fields */
- Item *item_ptr; /* Storage for initial item */
+ Item **item; /* Point at item in select fields */
+ Item *item_ptr; /* Storage for initial item */
int counter; /* position in SELECT list, correct
- only if counter_used is true*/
- bool asc; /* true if ascending */
- bool free_me; /* true if item isn't shared */
- bool in_field_list; /* true if in select field list */
+ only if counter_used is true */
+ enum enum_order {
+ ORDER_NOT_RELEVANT,
+ ORDER_ASC,
+ ORDER_DESC
+ };
+
+ enum_order direction; /* Requested direction of ordering */
+ bool free_me; /* true if item isn't shared */
+ bool in_field_list; /* true if in select field list */
bool counter_used; /* parameter was counter of columns */
- Field *field; /* If tmp-table group */
- char *buff; /* If tmp-table group */
+ Field *field; /* If tmp-table group */
+ char *buff; /* If tmp-table group */
table_map used, depend_map;
} ORDER;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk-lock-order branch (marc.alff:3353 to 3354) | Marc Alff | 4 Apr |