summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/derived.result12
-rw-r--r--mysql-test/t/derived.test6
-rw-r--r--mysql-test/t/subselect.test21
-rw-r--r--sql/item.cc12
-rw-r--r--sql/item.h6
-rw-r--r--sql/item_cmpfunc.h2
-rw-r--r--sql/item_subselect.cc12
-rw-r--r--sql/item_sum.h16
-rw-r--r--sql/mysql_priv.h1
-rw-r--r--sql/sql_class.h11
-rw-r--r--sql/sql_derived.cc67
-rw-r--r--sql/sql_lex.cc12
-rw-r--r--sql/sql_lex.h6
-rw-r--r--sql/sql_parse.cc3
-rw-r--r--sql/sql_prepare.cc12
-rw-r--r--sql/sql_select.cc36
16 files changed, 141 insertions, 94 deletions
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 4f16e239e15..e1e823350d3 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -95,7 +95,17 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 8
2 DERIVED t1 ALL NULL NULL NULL NULL 4
3 UNION t1 ALL NULL NULL NULL NULL 4
-drop table if exists t1;
+CREATE TABLE t2 (a int not null);
+insert into t2 values(1);
+select * from (select * from t1 where t1.a=(select a from t2 where t2.a=t1.a)) a;
+a b
+1 a
+select * from (select * from t1 where t1.a=(select t2.a from t2 where t2.a=t1.a) union select t1.a, t1.b from t1) a;
+a b
+1 a
+2 b
+3 c
+drop table t1, t2;
create table t1(a int not null, t char(8), index(a));
SELECT * FROM (SELECT * FROM t1) as b ORDER BY a ASC LIMIT 0,20;
a t
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index 6d1d825a523..1dbdd6e0ae8 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -37,7 +37,11 @@ select * from (select * from t1 union select * from t1) a;
select * from (select * from t1 union all select * from t1) a;
explain select * from (select * from t1 union select * from t1) a;
explain select * from (select * from t1 union all select * from t1) a;
-drop table if exists t1;
+CREATE TABLE t2 (a int not null);
+insert into t2 values(1);
+select * from (select * from t1 where t1.a=(select a from t2 where t2.a=t1.a)) a;
+select * from (select * from t1 where t1.a=(select t2.a from t2 where t2.a=t1.a) union select t1.a, t1.b from t1) a;
+drop table t1, t2;
create table t1(a int not null, t char(8), index(a));
disable_query_log;
let $1 = 10000;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index e715cd1d09f..baf58916bd4 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -206,19 +206,22 @@ SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='j
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
-- error 1054
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
-SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY date);
-SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY date HAVING topic < 4100);
+
+SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
+SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
-SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY date);
-SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY date HAVING topic < 4100);
+SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
+SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
-SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY date);
-SELECT topic FROM t2 GROUP BY date;
-SELECT topic FROM t2 GROUP BY date HAVING topic < 4100;
-SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY date HAVING topic < 4100);
-SELECT *, date as fff from t2 where not (SELECT date FROM t2 GROUP BY date HAVING topic < 4100 and fff!=date);
+SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
+SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
+SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100;
+SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);
SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
+delete from t2 where topic=40143;
+SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
+SELECT *, topic as fff, exists (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100 and fff = topic) from t2;
drop table t1,t2;
#forumconthardwarefr7
diff --git a/sql/item.cc b/sql/item.cc
index 34eae344af9..d4128341fd3 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -573,20 +573,14 @@ bool Item_ref_on_list_position::fix_fields(THD *thd,
struct st_table_list *tables,
Item ** reference)
{
- List_iterator<Item> li(list);
- Item *item;
- for (uint i= 0; (item= li++) && i < pos; i++);
- if (item)
- {
- ref= li.ref();
- return Item_ref_null_helper::fix_fields(thd, tables, reference);
- }
- else
+ if (select_lex->item_list.elements <= pos)
{
ref= 0;
my_error(ER_CARDINALITY_COL, MYF(0), pos);
return 1;
}
+ ref= select_lex->ref_pointer_array + pos;
+ return Item_ref_null_helper::fix_fields(thd, tables, reference);
}
double Item_ref_null_helper::val()
diff --git a/sql/item.h b/sql/item.h
index 788d37be491..31c3c48cca8 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -548,14 +548,14 @@ public:
class Item_ref_on_list_position: public Item_ref_null_helper
{
protected:
- List<Item> &list;
+ st_select_lex *select_lex;
uint pos;
public:
Item_ref_on_list_position(Item_in_subselect* master,
- List<Item> &li, uint num,
+ st_select_lex *sl, uint num,
char *table_name, char *field_name):
Item_ref_null_helper(master, 0, table_name, field_name),
- list(li), pos(num) {}
+ select_lex(sl), pos(num) {}
bool fix_fields(THD *, struct st_table_list *, Item ** ref);
};
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index d1557f022d2..dc0a587e9f2 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -17,8 +17,6 @@
/* compare and test functions */
-#include "assert.h"
-
#ifdef __GNUC__
#pragma interface /* gcc class implementation */
#endif
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index e337f9c0c40..18a32b97a9f 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -439,7 +439,6 @@ void Item_in_subselect::single_value_transformer(THD *thd,
compare_func_creator func)
{
DBUG_ENTER("Item_in_subselect::single_value_transformer");
- THD *thd= current_thd;
if (unit->global_parameters->select_limit != HA_POS_ERROR)
{
@@ -488,9 +487,8 @@ void Item_in_subselect::single_value_transformer(THD *thd,
{
sl->item_list.push_back(item);
setup_ref_array(thd, &sl->ref_pointer_array,
- 1+ select_lex->with_sum_func +
- select_lex->order_list.elements +
- select_lex->group_list.elements);
+ 1 + sl->with_sum_func +
+ sl->order_list.elements + sl->group_list.elements);
item= (*func)(expr, new Item_ref_null_helper(this,
sl->ref_pointer_array,
(char *)"<no matter>",
@@ -597,9 +595,9 @@ void Item_in_subselect::row_value_transformer(THD *thd,
for (uint i= 0; i < n; i++)
{
Item *func=
- new Item_ref_on_list_position(this, sl->item_list, i,
- (char *) "<no matter>",
- (char *) "<list ref>");
+ new Item_ref_on_list_position(this, sl, i,
+ (char *) "<no matter>",
+ (char *) "<list ref>");
func=
Item_bool_func2::eq_creator(new Item_ref((*optimizer->get_cache())->
addr(i),
diff --git a/sql/item_sum.h b/sql/item_sum.h
index 311e7778be6..7f7b5e70f41 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -332,6 +332,22 @@ public:
Item_std_field(Item_sum_std *item);
enum Type type() const { return FIELD_STD_ITEM; }
double val();
+};
+
+/*
+ standard_deviation(a) = sqrt(variance(a))
+*/
+
+class Item_sum_std :public Item_sum_variance
+{
+ public:
+ Item_sum_std(Item *item_par) :Item_sum_variance(item_par){}
+ enum Sumfunctype sum_func () const { return STD_FUNC; }
+ double val();
+ Item *result_item(Field *field)
+ { return new Item_std_field(this); }
+ const char *func_name() const { return "std"; }
+};
// This class is a string or number function depending on num_func
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index b1507a1e6f6..978678fa975 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -406,6 +406,7 @@ int mysql_select(THD *thd, Item ***rref_pointer_array,
SELECT_LEX *select_lex, bool fake_select_lex);
void free_ulderlayed_joins(THD *thd, SELECT_LEX *select);
void fix_tables_pointers(SELECT_LEX *select_lex);
+void fix_tables_pointers(SELECT_LEX_UNIT *select_lex);
int mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit,
select_result *result);
int mysql_explain_select(THD *thd, SELECT_LEX *sl, char const *type,
diff --git a/sql/sql_class.h b/sql/sql_class.h
index e33ccb82cff..739deb1ac69 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -663,17 +663,6 @@ public:
#endif
void add_possible_loop(Item *);
- void free_tmp_tables()
- {
- if (temporary_tables_should_be_free.elements)
- {
- List_iterator_fast<TABLE> lt(temporary_tables_should_be_free);
- TABLE *table;
- while ((table= lt++))
- free_tmp_table(this,table);
- temporary_tables_should_be_free.empty();
- }
- }
};
/*
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 9296625dfdb..8fc3314b69f 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -70,7 +70,8 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t)
select_union *derived_result;
TABLE_LIST *tables= (TABLE_LIST *)sl->table_list.first;
TMP_TABLE_PARAM tmp_table_param;
- bool is_union=sl->next_select() && sl->next_select()->linkage == UNION_TYPE;
+ bool is_union= sl->next_select() && sl->next_select()->linkage == UNION_TYPE;
+ bool is_subsel= sl->first_inner_unit();
SELECT_LEX_NODE *save_current_select= lex->current_select;
DBUG_ENTER("mysql_derived");
@@ -81,7 +82,7 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t)
recognize better when this function is called from derived tables
and when from other functions.
*/
- if (is_union && unit->create_total_list(thd, lex, &tables))
+ if ((is_union || is_subsel) && unit->create_total_list(thd, lex, &tables, 1))
DBUG_RETURN(-1);
if (tables)
@@ -90,29 +91,44 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t)
res= check_access(thd, SELECT_ACL, any_db);
if (res)
DBUG_RETURN(-1);
-
- Item *item;
- List_iterator<Item> it(sl->item_list);
-
- while ((item= it++))
- item_list.push_back(item);
if (!(res=open_and_lock_tables(thd,tables)))
{
- if (setup_wild(thd, tables, item_list, 0, sl->with_wild) ||
- setup_fields(thd, 0, tables, item_list, 0, 0, 1))
+ if (is_union || is_subsel)
+ {
+ /*
+ The following code is a re-do of fix_tables_pointers() found
+ in sql_select.cc for UNION's within derived tables. The only
+ difference is in navigation, as in derived tables we care for
+ this level only.
+
+ */
+ fix_tables_pointers(unit);
+ }
+
+ Item *item;
+ List_iterator<Item> it(sl->item_list);
+
+ while ((item= it++))
+ item_list.push_back(item);
+
+
+ lex->current_select= sl;
+ TABLE_LIST *first_table= (TABLE_LIST*) sl->table_list.first;
+ if (setup_wild(thd, first_table, item_list, 0, sl->with_wild) ||
+ setup_fields(thd, 0, first_table, item_list, 0, 0, 1))
{
res= -1;
goto exit;
}
bzero((char*) &tmp_table_param,sizeof(tmp_table_param));
- tmp_table_param.field_count=item_list.elements;
- if (!(table=create_tmp_table(thd, &tmp_table_param, item_list,
- (ORDER*) 0,
- is_union && !unit->union_option, 1,
- (sl->options | thd->options |
- TMP_TABLE_ALL_COLUMNS),
- HA_POS_ERROR)))
+ tmp_table_param.field_count= item_list.elements;
+ if (!(table= create_tmp_table(thd, &tmp_table_param, item_list,
+ (ORDER*) 0,
+ is_union && !unit->union_option, 1,
+ (sl->options | thd->options |
+ TMP_TABLE_ALL_COLUMNS),
+ HA_POS_ERROR)))
{
res= -1;
goto exit;
@@ -129,9 +145,11 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t)
sl->options&= ~OPTION_FOUND_ROWS;
if (is_union)
- res= mysql_union(thd,lex,derived_result,unit);
+ res= mysql_union(thd, lex, derived_result, unit);
else
- res= mysql_select(thd, &sl->ref_pointer_array, tables, sl->with_wild,
+ res= mysql_select(thd, &sl->ref_pointer_array,
+ (TABLE_LIST*) sl->table_list.first,
+ sl->with_wild,
sl->item_list, sl->where,
sl->order_list.elements+sl->group_list.elements,
(ORDER *) sl->order_list.first,
@@ -170,11 +188,12 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t)
delete derived_result;
}
if (res)
-mp_table(thd, table);
-
-
->next= thd->derived_tables
-erived_tables= table;
+ free_tmp_table(thd, table);
+ else
+ {
+ table->next= thd->derived_tables;
+ thd->derived_tables= table;
+ }
exit:
lex->current_select= save_current_select;
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 764b7650f1d..2b6c337bedb 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1245,15 +1245,17 @@ ulong st_select_lex_node::get_table_join_options() { return 0; }
// interface
bool st_select_lex_unit::create_total_list(THD *thd, st_lex *lex,
- TABLE_LIST **result)
+ TABLE_LIST **result,
+ bool check_derived)
{
*result= 0;
- return create_total_list_n_last_return(thd, lex, &result);
+ return create_total_list_n_last_return(thd, lex, &result, check_derived);
}
// list creator
bool st_select_lex_unit::create_total_list_n_last_return(THD *thd, st_lex *lex,
- TABLE_LIST ***result)
+ TABLE_LIST ***result,
+ bool check_derived)
{
TABLE_LIST *slave_list_first=0, **slave_list_last= &slave_list_first;
TABLE_LIST **new_table_list= *result, *aux;
@@ -1266,13 +1268,13 @@ bool st_select_lex_unit::create_total_list_n_last_return(THD *thd, st_lex *lex,
net_printf(thd,ER_WRONG_USAGE,"UNION","ORDER BY");
return 1;
}
- if (sl->linkage == DERIVED_TABLE_TYPE && !sl->next_select())
+ if (sl->linkage == DERIVED_TABLE_TYPE && !check_derived)
continue;
for (SELECT_LEX_UNIT *inner= sl->first_inner_unit();
inner;
inner= inner->next_unit())
if (inner->create_total_list_n_last_return(thd, lex,
- &slave_list_last))
+ &slave_list_last, 0))
return 1;
if ((aux= (TABLE_LIST*) sl->table_list.first))
{
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index a77f0ce76eb..a9a792ee783 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -302,7 +302,8 @@ public:
uint union_option;
void init_query();
- bool create_total_list(THD *thd, st_lex *lex, TABLE_LIST **result);
+ bool create_total_list(THD *thd, st_lex *lex, TABLE_LIST **result,
+ bool check_current_derived);
st_select_lex_unit* master_unit();
st_select_lex* outer_select();
st_select_lex* first_select() { return (st_select_lex*) slave; }
@@ -318,7 +319,8 @@ public:
friend int subselect_union_engine::exec();
private:
bool create_total_list_n_last_return(THD *thd, st_lex *lex,
- TABLE_LIST ***result);
+ TABLE_LIST ***result,
+ bool check_current_derived);
};
typedef class st_select_lex_unit SELECT_LEX_UNIT;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index c0b38cec254..eda2cbb69ce 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1599,7 +1599,7 @@ mysql_execute_command(THD *thd)
}
}
if ((&lex->select_lex != lex->all_selects_list &&
- lex->unit.create_total_list(thd, lex, &tables))
+ lex->unit.create_total_list(thd, lex, &tables, 0))
#ifndef EMBEDDED_LIBRARY
||
(table_rules_on && tables && thd->slave_thread &&
@@ -2887,7 +2887,6 @@ mysql_execute_command(THD *thd)
send_ok(thd);
break;
}
- thd->free_tmp_tables();
thd->proc_info="query end"; // QQ
if (res < 0)
send_error(thd,thd->killed ? ER_SERVER_SHUTDOWN : 0);
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 7c9e4b27564..5d9e3c8d7e2 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -532,8 +532,9 @@ static bool mysql_test_upd_fields(PREP_STMT *stmt, TABLE_LIST *table_list,
And send column list fields info back to client.
*/
static bool mysql_test_select_fields(PREP_STMT *stmt, TABLE_LIST *tables,
+ uint wild_num,
List<Item> &fields, COND *conds,
- ORDER *order, ORDER *group,
+ uint og_num, ORDER *order, ORDER *group,
Item *having, ORDER *proc,
ulong select_options,
SELECT_LEX_UNIT *unit,
@@ -545,7 +546,7 @@ static bool mysql_test_select_fields(PREP_STMT *stmt, TABLE_LIST *tables,
DBUG_ENTER("mysql_test_select_fields");
if ((&lex->select_lex != lex->all_selects_list &&
- lex->unit.create_total_list(thd, lex, &tables)))
+ lex->unit.create_total_list(thd, lex, &tables, 0)))
DBUG_RETURN(1);
if (open_and_lock_tables(thd, tables))
@@ -564,7 +565,8 @@ static bool mysql_test_select_fields(PREP_STMT *stmt, TABLE_LIST *tables,
JOIN *join= new JOIN(thd, fields, select_options, result);
thd->used_tables= 0; // Updated by setup_fields
- if (join->prepare(tables, conds, order, group, having, proc,
+ if (join->prepare(&select_lex->ref_pointer_array, tables,
+ wild_num, conds, og_num, order, group, having, proc,
select_lex, unit, 0))
DBUG_RETURN(1);
@@ -623,9 +625,11 @@ static bool send_prepare_results(PREP_STMT *stmt)
break;
case SQLCOM_SELECT:
- if (mysql_test_select_fields(stmt, tables,
+ if (mysql_test_select_fields(stmt, tables, select_lex->with_wild,
select_lex->item_list,
select_lex->where,
+ select_lex->order_list.elements +
+ select_lex->group_list.elements,
(ORDER*) select_lex->order_list.first,
(ORDER*) select_lex->group_list.first,
select_lex->having,
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index f33433c6c37..c9872813412 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -217,6 +217,16 @@ void fix_tables_pointers(SELECT_LEX *select_lex)
}
}
+void fix_tables_pointers(SELECT_LEX_UNIT *unit)
+{
+ for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
+ {
+ relink_tables(sl);
+ for(SELECT_LEX_UNIT *un= sl->first_inner_unit(); un; un= un->next_unit())
+ fix_tables_pointers(un);
+ }
+}
+
/*
Function to setup clauses without sum functions
@@ -720,8 +730,17 @@ JOIN::optimize()
(order == 0 || skip_sort_order) ? select_limit :
HA_POS_ERROR)))
DBUG_RETURN(1);
-
- //thd->temporary_tables_should_be_free.push_front(exec_tmp_table1);
+
+ /*
+ We don't have to store rows in temp table that doesn't match HAVING if:
+ - we are sorting the table and writing complete group rows to the
+ temp table.
+ - We are using DISTINCT without resolving the distinct as a GROUP BY
+ on all columns.
+
+ If having is not handled here, it will be checked before the row
+ is sent to the client.
+ */
if (having &&
(sort_and_group || (exec_tmp_table1->distinct && !group_list)))
having= tmp_having;
@@ -938,17 +957,7 @@ JOIN::exec()
if (tmp_join)
curr_join= tmp_join;
curr_tmp_table= exec_tmp_table1;
- /*
- TODO: move this comment on its place
- We don't have to store rows in temp table that doesn't match HAVING if:
- - we are sorting the table and writing complete group rows to the
- temp table.
- - We are using DISTINCT without resolving the distinct as a GROUP BY
- on all columns.
-
- If having is not handled here, it will be checked before the row
- is sent to the client.
- */
+
/* Copy data to the temporary table */
thd->proc_info= "Copying to tmp table";
@@ -1058,7 +1067,6 @@ JOIN::exec()
1, curr_join->select_options,
HA_POS_ERROR)))
DBUG_VOID_RETURN;
- //thd->temporary_tables_should_be_free.push_front(exec_tmp_table2);
curr_join->exec_tmp_table2= exec_tmp_table2;
}
if (group_list)
View Lorry Controller Status