diff options
| -rw-r--r-- | mysql-test/r/derived.result | 28 | ||||
| -rw-r--r-- | mysql-test/t/derived.test | 27 | ||||
| -rw-r--r-- | sql/sql_derived.cc | 19 |
3 files changed, 66 insertions, 8 deletions
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index bb268cd1094..8cf0b45102f 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -85,6 +85,10 @@ a b 2 b 3 c 3 c +select * from (select * from t1 union all select * from t1 limit 2) a; +a b +1 a +2 b explain select * from (select * from t1 union select * from t1) a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 @@ -245,3 +249,27 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DERIVED t1 ALL NULL NULL NULL NULL 2 3 UNION t1 ALL NULL NULL NULL NULL 2 drop table t1; +CREATE TABLE t1 ( +OBJECTID int(11) NOT NULL default '0', +SORTORDER int(11) NOT NULL auto_increment, +KEY t1_SortIndex (SORTORDER), +KEY t1_IdIndex (OBJECTID) +) TYPE=MyISAM DEFAULT CHARSET=latin1; +Warnings: +Warning 1286 'TYPE=database_engine' is deprecated. Use 'ENGINE=database_engine' instead. +CREATE TABLE t2 ( +ID int(11) default NULL, +PARID int(11) default NULL, +UNIQUE KEY t2_ID_IDX (ID), +KEY t2_PARID_IDX (PARID) +) engine=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2); +CREATE TABLE t3 ( +ID int(11) default NULL, +DATA decimal(10,2) default NULL, +UNIQUE KEY t3_ID_IDX (ID) +) engine=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75); +select 497, TMP.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as TMP; +497 ID NULL +drop table t1, t2, t3; diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index caf673d95c1..8de95b4b600 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -35,6 +35,7 @@ select a from (select 1 as a) as b; select 1 from (select 1) as a; select * from (select * from t1 union select * from t1) a; select * from (select * from t1 union all select * from t1) a; +select * from (select * from t1 union all select * from t1 limit 2) a; explain select * from (select * from t1 union select * from t1) a; explain select * from (select * from t1 union all select * from t1) a; CREATE TABLE t2 (a int not null); @@ -138,3 +139,29 @@ insert into t1 values (1),(2); select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b; explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b; drop table t1; + + +# +# correct lex->current_select +# +CREATE TABLE t1 ( + OBJECTID int(11) NOT NULL default '0', + SORTORDER int(11) NOT NULL auto_increment, + KEY t1_SortIndex (SORTORDER), + KEY t1_IdIndex (OBJECTID) +) TYPE=MyISAM DEFAULT CHARSET=latin1; +CREATE TABLE t2 ( + ID int(11) default NULL, + PARID int(11) default NULL, + UNIQUE KEY t2_ID_IDX (ID), + KEY t2_PARID_IDX (PARID) +) engine=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2); +CREATE TABLE t3 ( + ID int(11) default NULL, + DATA decimal(10,2) default NULL, + UNIQUE KEY t3_ID_IDX (ID) +) engine=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75); +select 497, TMP.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as TMP; +drop table t1, t2, t3; diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index d70917eb47f..374e56ecdd4 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -146,17 +146,19 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, } derived_result->set_table(table); - unit->offset_limit_cnt= first_select->offset_limit; - unit->select_limit_cnt= first_select->select_limit+ - first_select->offset_limit; - if (unit->select_limit_cnt < first_select->select_limit) - unit->select_limit_cnt= HA_POS_ERROR; - if (unit->select_limit_cnt == HA_POS_ERROR) - first_select->options&= ~OPTION_FOUND_ROWS; - if (is_union) res= mysql_union(thd, lex, derived_result, unit); else + { + unit->offset_limit_cnt= first_select->offset_limit; + unit->select_limit_cnt= first_select->select_limit+ + first_select->offset_limit; + if (unit->select_limit_cnt < first_select->select_limit) + unit->select_limit_cnt= HA_POS_ERROR; + if (unit->select_limit_cnt == HA_POS_ERROR) + first_select->options&= ~OPTION_FOUND_ROWS; + + lex->current_select= first_select; res= mysql_select(thd, &first_select->ref_pointer_array, (TABLE_LIST*) first_select->table_list.first, first_select->with_wild, @@ -169,6 +171,7 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, (first_select->options | thd->options | SELECT_NO_UNLOCK), derived_result, unit, first_select); + } if (!res) { |