summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/order_by.result31
-rw-r--r--mysql-test/t/order_by.test32
-rw-r--r--sql/sql_base.cc6
-rw-r--r--sql/sql_parse.cc1
-rw-r--r--sql/sql_select.cc19
-rw-r--r--sql/sql_select.h2
-rw-r--r--sql/table.cc25
-rw-r--r--sql/table.h12
8 files changed, 116 insertions, 12 deletions
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 306fce1f3c2..0c72f816c21 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -1557,3 +1557,34 @@ a
2001
1991
DROP TABLE t1;
+#
+# Bug #43029: FORCE INDEX FOR ORDER BY is ignored when join buffering
+# is used
+#
+CREATE TABLE t1 (a INT, b INT, KEY (a));
+INSERT INTO t1 VALUES (0, NULL), (1, NULL), (2, NULL), (3, NULL);
+INSERT INTO t1 SELECT a+4, b FROM t1;
+INSERT INTO t1 SELECT a+8, b FROM t1;
+CREATE TABLE t2 (a INT, b INT);
+INSERT INTO t2 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL), (4,NULL);
+INSERT INTO t2 SELECT a+4, b FROM t2;
+# shouldn't have "using filesort"
+EXPLAIN
+SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 2 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 10
+# should have "using filesort"
+EXPLAIN
+SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort
+1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer
+# should have "using filesort"
+EXPLAIN
+SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort
+1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer
+DROP TABLE t1, t2;
+End of 5.1 tests
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index cca1e3209cc..ac2bbaaeeac 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -1402,3 +1402,35 @@ SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9;
SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9;
DROP TABLE t1;
+
+--echo #
+--echo # Bug #43029: FORCE INDEX FOR ORDER BY is ignored when join buffering
+--echo # is used
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, KEY (a));
+
+INSERT INTO t1 VALUES (0, NULL), (1, NULL), (2, NULL), (3, NULL);
+INSERT INTO t1 SELECT a+4, b FROM t1;
+INSERT INTO t1 SELECT a+8, b FROM t1;
+
+CREATE TABLE t2 (a INT, b INT);
+
+INSERT INTO t2 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL), (4,NULL);
+INSERT INTO t2 SELECT a+4, b FROM t2;
+
+--echo # shouldn't have "using filesort"
+EXPLAIN
+SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
+
+--echo # should have "using filesort"
+EXPLAIN
+SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
+
+--echo # should have "using filesort"
+EXPLAIN
+SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
+
+DROP TABLE t1, t2;
+
+--echo End of 5.1 tests
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index d1e96fcdbb3..e706bd04ea6 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -2305,7 +2305,8 @@ bool reopen_name_locked_table(THD* thd, TABLE_LIST* table_list, bool link_in)
table->tablenr=thd->current_tablenr++;
table->used_fields=0;
table->const_table=0;
- table->null_row= table->maybe_null= table->force_index= 0;
+ table->null_row= table->maybe_null= 0;
+ table->force_index= table->force_index_order= table->force_index_group= 0;
table->status=STATUS_NO_RECORD;
DBUG_RETURN(FALSE);
}
@@ -2963,7 +2964,8 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
table->tablenr=thd->current_tablenr++;
table->used_fields=0;
table->const_table=0;
- table->null_row= table->maybe_null= table->force_index= 0;
+ table->null_row= table->maybe_null= 0;
+ table->force_index= table->force_index_order= table->force_index_group= 0;
table->status=STATUS_NO_RECORD;
table->insert_values= 0;
table->fulltext_searched= 0;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 83ef525e3eb..27688e41d4f 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -6258,6 +6258,7 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd,
ptr->table_name_length=table->table.length;
ptr->lock_type= lock_type;
ptr->updating= test(table_options & TL_OPTION_UPDATING);
+ /* TODO: remove TL_OPTION_FORCE_INDEX as it looks like it's not used */
ptr->force_index= test(table_options & TL_OPTION_FORCE_INDEX);
ptr->ignore_leaves= test(table_options & TL_OPTION_IGNORE_LEAVES);
ptr->derived= table->sel;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 3f1432914a0..9dacb2c2ce4 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1231,13 +1231,22 @@ JOIN::optimize()
(!group_list && tmp_table_param.sum_func_count))
order=0;
- // Can't use sort on head table if using row cache
+ // Can't use sort on head table if using join buffering
if (full_join)
{
- if (group_list)
- simple_group=0;
- if (order)
- simple_order=0;
+ TABLE *stable= (sort_by_table == (TABLE *) 1 ?
+ join_tab[const_tables].table : sort_by_table);
+ /*
+ FORCE INDEX FOR ORDER BY can be used to prevent join buffering when
+ sorting on the first table.
+ */
+ if (!stable || !stable->force_index_order)
+ {
+ if (group_list)
+ simple_group= 0;
+ if (order)
+ simple_order= 0;
+ }
}
/*
diff --git a/sql/sql_select.h b/sql/sql_select.h
index a0366d47149..3f06b402638 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -357,6 +357,8 @@ public:
simple_xxxxx is set if ORDER/GROUP BY doesn't include any references
to other tables than the first non-constant table in the JOIN.
It's also set if ORDER/GROUP BY is empty.
+ Used for deciding for or against using a temporary table to compute
+ GROUP/ORDER BY.
*/
bool simple_order, simple_group;
/**
diff --git a/sql/table.cc b/sql/table.cc
index 04f2a3fbcf8..d2538eb4d59 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -4637,7 +4637,8 @@ Item_subselect *TABLE_LIST::containing_subselect()
(TABLE_LIST::index_hints). Using the information in this tagged list
this function sets the members st_table::keys_in_use_for_query,
st_table::keys_in_use_for_group_by, st_table::keys_in_use_for_order_by,
- st_table::force_index and st_table::covering_keys.
+ st_table::force_index, st_table::force_index_order,
+ st_table::force_index_group and st_table::covering_keys.
Current implementation of the runtime does not allow mixing FORCE INDEX
and USE INDEX, so this is checked here. Then the FORCE INDEX list
@@ -4765,14 +4766,28 @@ bool TABLE_LIST::process_index_hints(TABLE *tbl)
}
/* process FORCE INDEX as USE INDEX with a flag */
+ if (!index_order[INDEX_HINT_FORCE].is_clear_all())
+ {
+ tbl->force_index_order= TRUE;
+ index_order[INDEX_HINT_USE].merge(index_order[INDEX_HINT_FORCE]);
+ }
+
+ if (!index_group[INDEX_HINT_FORCE].is_clear_all())
+ {
+ tbl->force_index_group= TRUE;
+ index_group[INDEX_HINT_USE].merge(index_group[INDEX_HINT_FORCE]);
+ }
+
+ /*
+ TODO: get rid of tbl->force_index (on if any FORCE INDEX is specified) and
+ create tbl->force_index_join instead.
+ Then use the correct force_index_XX instead of the global one.
+ */
if (!index_join[INDEX_HINT_FORCE].is_clear_all() ||
- !index_order[INDEX_HINT_FORCE].is_clear_all() ||
- !index_group[INDEX_HINT_FORCE].is_clear_all())
+ tbl->force_index_group || tbl->force_index_order)
{
tbl->force_index= TRUE;
index_join[INDEX_HINT_USE].merge(index_join[INDEX_HINT_FORCE]);
- index_order[INDEX_HINT_USE].merge(index_order[INDEX_HINT_FORCE]);
- index_group[INDEX_HINT_USE].merge(index_group[INDEX_HINT_FORCE]);
}
/* apply USE INDEX */
diff --git a/sql/table.h b/sql/table.h
index 40372fa91cf..e4a382c799f 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -752,6 +752,18 @@ struct st_table {
bytes, it would take up 4.
*/
my_bool force_index;
+
+ /**
+ Flag set when the statement contains FORCE INDEX FOR ORDER BY
+ See TABLE_LIST::process_index_hints().
+ */
+ my_bool force_index_order;
+
+ /**
+ Flag set when the statement contains FORCE INDEX FOR GROUP BY
+ See TABLE_LIST::process_index_hints().
+ */
+ my_bool force_index_group;
my_bool distinct,const_table,no_rows;
/**
View Lorry Controller Status