summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect.result31
-rw-r--r--mysql-test/t/subselect.test8
-rw-r--r--sql/item.cc29
-rw-r--r--sql/item_subselect.cc6
-rw-r--r--sql/sql_class.cc11
-rw-r--r--sql/sql_lex.h1
-rw-r--r--sql/sql_parse.cc10
-rw-r--r--sql/sql_select.cc7
-rw-r--r--sql/sql_yacc.yy1
9 files changed, 75 insertions, 29 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 64ceea72498..2e40d0a92d8 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1,3 +1,6 @@
+select (select 2);
+(select 2)
+2
drop table if exists t1,t2,t3,t4;
create table t1 (a int);
create table t2 (a int, b int);
@@ -26,18 +29,42 @@ select * from t2 where t2.a=(select a from t1);
a b
2 7
insert into t3 values (6),(7),(3);
-select * from t2 where t2.b=(select a from t3 order by 1 limit 1);
+select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
a b
1 7
2 7
-select * from t2 where t2.b=(select a from t3 order by 1 limit 1)
+select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)
union (select * from t4 order by a limit 2) limit 3;
a b
1 7
2 7
3 8
+select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)
+union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
+a b
+1 7
+2 7
+3 8
+4 8
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
(select a from t3 where a<t2.a*4 order by 1 desc limit 1) a
3 1
7 2
+select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
+(select * from t2 where a>1) as tt;
+(select t3.a from t3 where a<8 order by 1 desc limit 1) a
+7 2
+select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
+a
+2
+select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1);
+a
+2
+select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1);
+a
+select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
+b (select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)
+8 7.5000
+8 6.0000
+9 5.5000
drop table t1,t2,t3,t4;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 7383608ed9e..c94522fde8f 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1,5 +1,5 @@
-#select (select 2);
+select (select 2);
drop table if exists t1,t2,t3,t4;
create table t1 (a int);
create table t2 (a int, b int);
@@ -17,7 +17,13 @@ insert into t3 values (6),(7),(3);
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)
union (select * from t4 order by a limit 2) limit 3;
+select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)
+union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
(select * from t2 where a>1) as tt;
+select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
+select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1);
+select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1);
+select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
drop table t1,t2,t3,t4;
diff --git a/sql/item.cc b/sql/item.cc
index 8a785ee3902..45564bcd98e 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -330,23 +330,32 @@ bool Item_field::fix_fields(THD *thd,TABLE_LIST *tables)
mention of table name, but if we join tables in one list it will
cause error ER_NON_UNIQ_ERROR in find_field_in_tables.
*/
+ SELECT_LEX *last;
for (SELECT_LEX *sl= thd->lex.select->outer_select();
sl && !tmp;
sl= sl->outer_select())
tmp=find_field_in_tables(thd, this,
- (TABLE_LIST*)sl->table_list.first);
+ (TABLE_LIST*)(last= sl)->table_list.first);
if (!tmp)
return 1;
else
- if( !thd->lex.select->depended )
- {
- thd->lex.select->depended= 1; //Select is depended of outer select(s)
- //Tables will be reopened many times
- for (TABLE_LIST *tbl= (TABLE_LIST*)thd->lex.select->table_list.first;
- tbl;
- tbl= tbl->next)
- tbl->shared= 1;
- }
+ /*
+ Mark all selects from resolved to 1 before select where was
+ found table as depended (of select where was found table)
+ */
+ for (SELECT_LEX *s= thd->lex.select;
+ s &&s != last;
+ s= s->outer_select())
+ if( !s->depended )
+ {
+ s->depended= 1; //Select is depended of outer select
+ //Tables will be reopened many times
+ for (TABLE_LIST *tbl=
+ (TABLE_LIST*)s->table_list.first;
+ tbl;
+ tbl= tbl->next)
+ tbl->shared= 1;
+ }
}
set_field(tmp);
}
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 72bbbcba5a7..e18c8d78830 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -23,6 +23,7 @@ SUBSELECT TODO:
- remove double 'having' & 'having_list' from JOIN
(sql_select.h/sql_select.cc)
+ - subselect in HAVING clause
- add subselect union select (sql_union.cc)
*/
@@ -43,7 +44,7 @@ Item_subselect::Item_subselect(THD *thd, st_select_lex *select_lex):
SELECT_LEX_UNIT *unit= select_lex->master_unit();
unit->offset_limit_cnt= unit->global_parameters->offset_limit;
unit->select_limit_cnt= unit->global_parameters->select_limit+
- select_lex->offset_limit;
+ unit->global_parameters ->offset_limit;
if (unit->select_limit_cnt < unit->global_parameters->select_limit)
unit->select_limit_cnt= HA_POS_ERROR; // no limit
if (unit->select_limit_cnt == HA_POS_ERROR)
@@ -148,9 +149,6 @@ int Item_subselect::exec()
join->thd->lex.select= select_lex;
join->exec();
join->thd->lex.select= save_select;
- //if (!executed)
- //No rows returned => value is null (returned as inited)
- // executed= 1;
return join->error;
}
return 0;
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 975d36069f9..d3bd957c308 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -790,11 +790,17 @@ bool select_subselect::send_data(List<Item> &items)
DBUG_RETURN(1);
}
if (unit->offset_limit_cnt)
- { // using limit offset,count
+ { // Using limit offset,count
unit->offset_limit_cnt--;
DBUG_RETURN(0);
}
- Item *val_item= (Item *)item->select_lex->item_list.head();
+ List_iterator_fast<Item> li(items);
+ Item *val_item= li++; // Only one (single value subselect)
+ /*
+ Following val() call have to be first, because function AVG() & STD()
+ calculate value on it & determinate "is it NULL?".
+ */
+ item->real_value= val_item->val();
if ((item->null_value= val_item->is_null()))
{
item->assign_null();
@@ -804,7 +810,6 @@ bool select_subselect::send_data(List<Item> &items)
item->binary= val_item->binary;
val_item->val_str(&item->str_value);
item->int_value= val_item->val_int();
- item->real_value= val_item->val();
item->res_type= val_item->result_type();
}
item->executed= 1;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 5c113e46a2b..47bbfea3030 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -194,7 +194,6 @@ protected:
public:
ulong options;
enum sub_select_type linkage;
- //uint sort_default;
SQL_LIST order_list; /* ORDER clause */
ha_rows select_limit, offset_limit; /* LIMIT clause parameters */
void init_query();
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 3492854329a..ab49645dbae 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2690,12 +2690,12 @@ mysql_init_query(THD *thd)
void
mysql_init_select(LEX *lex)
{
- SELECT_LEX *select_lex = lex->select;
+ SELECT_LEX *select_lex= lex->select;
select_lex->init_select();
- select_lex->select_limit=lex->thd->default_select_limit;
- select_lex->offset_limit=0;
- lex->exchange = 0;
- lex->proc_list.first=0;
+ select_lex->master_unit()->select_limit= select_lex->select_limit=
+ lex->thd->default_select_limit;
+ lex->exchange= 0;
+ lex->proc_list.first= 0;
}
bool
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 4748e857276..62418a12497 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -607,9 +607,10 @@ JOIN::reinit()
unit->select_limit_cnt= HA_POS_ERROR; // no limit
if (unit->select_limit_cnt == HA_POS_ERROR)
select_lex->options&= ~OPTION_FOUND_ROWS;
-
+
if (setup_tables(tables_list))
DBUG_RETURN(1);
+
DBUG_RETURN(0);
}
@@ -2830,7 +2831,9 @@ join_free(JOIN *join)
}
end_read_record(&tab->read_record);
}
- join->table=0;
+ //TODO: is enough join_free at the end of mysql_select?
+ if (!join->select_lex->depended)
+ join->table=0;
}
// We are not using tables anymore
// Unlock all tables. We may be in an INSERT .... SELECT statement.
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index ebd26939ad5..9b95ab05977 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -2325,7 +2325,6 @@ order_clause:
LEX *lex=Lex;
if (lex->sql_command == SQLCOM_MULTI_UPDATE)
YYABORT;
- /*lex->select->sort_default=1;*/
} order_list
order_list:
View Lorry Controller Status