summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/func_gconcat.result24
-rw-r--r--mysql-test/r/subselect.result43
-rw-r--r--mysql-test/t/func_gconcat.test6
-rw-r--r--mysql-test/t/subselect.test27
-rw-r--r--sql/item.cc70
-rw-r--r--sql/item_subselect.cc129
-rw-r--r--sql/item_subselect.h2
-rw-r--r--sql/mysql_priv.h7
-rw-r--r--sql/sql_lex.cc2
-rw-r--r--sql/sql_lex.h6
-rw-r--r--sql/sql_yacc.yy24
11 files changed, 223 insertions, 117 deletions
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index 1ddbc18d965..180719c092a 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -285,15 +285,21 @@ insert into t2 values (1, 5), (2, 4), (3, 3), (3,3);
select group_concat(c) from t1;
group_concat(c)
2,3,4,5
-select group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1;
-grp
-5,4,3,2
-select group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a)) as grp from t1;
-grp
-5,4,3,2
-select group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1;
-grp
-2,4,3,5
+select t1.a, group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1 group by 1;
+a grp
+1 2
+2 4,3
+3 5
+select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a)) as grp from t1 group by 1;
+a grp
+1 2
+2 4,3
+3 5
+select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1 group by 1;
+a grp
+1 2
+2 4,3
+3 5
select a,c,(select group_concat(c order by a) from t2 where a=t1.a) as grp from t1 order by grp;
a c grp
3 5 3,3
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 77339473142..9ece7c5b6ce 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1899,3 +1899,46 @@ select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (
a C
1 1
drop table t1,t2;
+CREATE TABLE `t1` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
+CREATE TABLE `t2` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
+insert into t1 values (1,1),(1,2),(2,1),(2,2);
+insert into t2 values (1,2),(2,2);
+select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
+aid bid
+1 1
+2 1
+alter table t2 drop primary key;
+alter table t2 add key KEY1 (aid, bid);
+select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
+aid bid
+1 1
+2 1
+alter table t2 drop key KEY1;
+alter table t2 add primary key (bid, aid);
+select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
+aid bid
+1 1
+2 1
+drop table t1,t2;
+CREATE TABLE t1 (howmanyvalues bigint, avalue int);
+INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
+SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
+howmanyvalues count(*)
+1 1
+2 2
+3 3
+4 4
+SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
+howmanyvalues mycount
+1 1
+2 2
+3 3
+4 4
+CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);
+SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues;
+howmanyvalues mycount
+1 1
+2 2
+3 3
+4 4
+drop table t1;
diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
index ad19c8414ec..6cb98f6da0d 100644
--- a/mysql-test/t/func_gconcat.test
+++ b/mysql-test/t/func_gconcat.test
@@ -168,10 +168,10 @@ insert into t1 values (1, 2), (2, 3), (2, 4), (3, 5);
create table t2 (a int, c int);
insert into t2 values (1, 5), (2, 4), (3, 3), (3,3);
select group_concat(c) from t1;
-select group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1;
+select t1.a, group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1 group by 1;
-select group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a)) as grp from t1;
-select group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1;
+select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a)) as grp from t1 group by 1;
+select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1 group by 1;
# The following returns random results as we are sorting on blob addresses
# select group_concat(c order by (select group_concat(c order by a) from t2 where t2.a=t1.a)) as grp from t1;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index eb4b1f33b14..5bff28dca77 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1222,3 +1222,30 @@ CREATE TABLE `t2` ( `b` int(11) default NULL, `a` int(11) default NULL) ENGINE=M
insert into t2 values (1,2);
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
drop table t1,t2;
+
+#
+# Optimized IN with compound index
+#
+CREATE TABLE `t1` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
+CREATE TABLE `t2` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));
+insert into t1 values (1,1),(1,2),(2,1),(2,2);
+insert into t2 values (1,2),(2,2);
+select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
+alter table t2 drop primary key;
+alter table t2 add key KEY1 (aid, bid);
+select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
+alter table t2 drop key KEY1;
+alter table t2 add primary key (bid, aid);
+select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
+drop table t1,t2;
+
+#
+# resolving fields of grouped outer SELECT
+#
+CREATE TABLE t1 (howmanyvalues bigint, avalue int);
+INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
+SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
+SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
+CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);
+SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues;
+drop table t1;
diff --git a/sql/item.cc b/sql/item.cc
index c85aee724cb..11d618748b3 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -60,10 +60,10 @@ Item::Item():
*/
if (thd->lex->current_select)
{
- SELECT_LEX_NODE::enum_parsing_place place=
+ enum_parsing_place place=
thd->lex->current_select->parsing_place;
- if (place == SELECT_LEX_NODE::SELECT_LIST ||
- place == SELECT_LEX_NODE::IN_HAVING)
+ if (place == SELECT_LIST ||
+ place == IN_HAVING)
thd->lex->current_select->select_n_having_items++;
}
}
@@ -1233,21 +1233,34 @@ bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
table_list= (last= sl)->get_table_list();
if (sl->resolve_mode == SELECT_LEX::INSERT_MODE && table_list)
{
- // it is primary INSERT st_select_lex => skip first table resolving
+ /*
+ it is primary INSERT st_select_lex => skip first table
+ resolving
+ */
table_list= table_list->next;
}
Item_subselect *prev_subselect_item= prev_unit->item;
- if ((tmp= find_field_in_tables(thd, this,
- table_list, &where,
- 0)) != not_found_field)
- {
- if (!tmp)
- return -1;
- prev_subselect_item->used_tables_cache|= tmp->table->map;
- prev_subselect_item->const_item_cache= 0;
- break;
- }
+ enum_parsing_place place=
+ prev_subselect_item->parsing_place;
+ /*
+ check table fields only if subquery used somewhere out of HAVING
+ or SELECT list or outer SELECT do not use groupping (i.e. tables
+ are accessable)
+ */
+ if (((place != IN_HAVING &&
+ place != SELECT_LIST) ||
+ (sl->with_sum_func == 0 && sl->group_list.elements == 0)) &&
+ (tmp= find_field_in_tables(thd, this,
+ table_list, &where,
+ 0)) != not_found_field)
+ {
+ if (!tmp)
+ return -1;
+ prev_subselect_item->used_tables_cache|= tmp->table->map;
+ prev_subselect_item->const_item_cache= 0;
+ break;
+ }
if (sl->resolve_mode == SELECT_LEX::SELECT_MODE &&
(refer= find_item_in_list(this, sl->item_list, &counter,
REPORT_EXCEPT_NOT_FOUND)) !=
@@ -1906,16 +1919,25 @@ bool Item_ref::fix_fields(THD *thd,TABLE_LIST *tables, Item **reference)
// it is primary INSERT st_select_lex => skip first table resolving
table_list= table_list->next;
}
- if ((tmp= find_field_in_tables(thd, this,
- table_list, &where,
- 0)) != not_found_field)
- {
- prev_subselect_item->used_tables_cache|= tmp->table->map;
- prev_subselect_item->const_item_cache= 0;
- break;
- }
-
- // Reference is not found => depend from outer (or just error)
+ enum_parsing_place place=
+ prev_subselect_item->parsing_place;
+ /*
+ check table fields only if subquery used somewhere out of HAVING
+ or SELECT list or outer SELECT do not use groupping (i.e. tables
+ are accessable)
+ */
+ if (((place != IN_HAVING &&
+ place != SELECT_LIST) ||
+ (sl->with_sum_func == 0 && sl->group_list.elements == 0)) &&
+ (tmp= find_field_in_tables(thd, this,
+ table_list, &where,
+ 0)) != not_found_field)
+ {
+ prev_subselect_item->used_tables_cache|= tmp->table->map;
+ prev_subselect_item->const_item_cache= 0;
+ break;
+ }
+ // Reference is not found => depend from outer (or just error)
prev_subselect_item->used_tables_cache|= OUTER_REF_TABLE_BIT;
prev_subselect_item->const_item_cache= 0;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 8c4dae92ddc..750fd4aa4af 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -63,12 +63,14 @@ void Item_subselect::init(st_select_lex *select_lex,
=> we do not copy old_engine here
*/
engine= unit->item->engine;
+ parsing_place= unit->item->parsing_place;
unit->item->engine= 0;
unit->item= this;
engine->change_item(this, result);
}
else
{
+ parsing_place= unit->outer_select()->parsing_place;
if (select_lex->next_select())
engine= new subselect_union_engine(unit, result, this);
else
@@ -76,7 +78,7 @@ void Item_subselect::init(st_select_lex *select_lex,
}
{
SELECT_LEX *upper= unit->outer_select();
- if (upper->parsing_place == SELECT_LEX_NODE::IN_HAVING)
+ if (upper->parsing_place == IN_HAVING)
upper->subquery_in_having= 1;
}
DBUG_VOID_RETURN;
@@ -1243,29 +1245,31 @@ int subselect_uniquesubquery_engine::exec()
DBUG_ENTER("subselect_uniquesubquery_engine::exec");
int error;
TABLE *table= tab->table;
- if ((tab->ref.key_err= (*tab->ref.key_copy)->copy()))
+ for (store_key **copy=tab->ref.key_copy ; *copy ; copy++)
{
- table->status= STATUS_NOT_FOUND;
- error= -1;
+ if (tab->ref.key_err= (*copy)->copy())
+ {
+ table->status= STATUS_NOT_FOUND;
+ DBUG_RETURN(1);
+ }
}
+
+ if (!table->file->inited)
+ table->file->ha_index_init(tab->ref.key);
+ error= table->file->index_read(table->record[0],
+ tab->ref.key_buff,
+ tab->ref.key_length,HA_READ_KEY_EXACT);
+ if (error && error != HA_ERR_KEY_NOT_FOUND)
+ error= report_error(table, error);
else
{
- if (!table->file->inited)
- table->file->ha_index_init(tab->ref.key);
- error= table->file->index_read(table->record[0],
- tab->ref.key_buff,
- tab->ref.key_length,HA_READ_KEY_EXACT);
- if (error && error != HA_ERR_KEY_NOT_FOUND)
- error= report_error(table, error);
- else
- {
- error= 0;
- table->null_row= 0;
- ((Item_in_subselect *) item)->value= (!table->status &&
- (!cond || cond->val_int()) ? 1 :
- 0);
- }
+ error= 0;
+ table->null_row= 0;
+ ((Item_in_subselect *) item)->value= (!table->status &&
+ (!cond || cond->val_int()) ? 1 :
+ 0);
}
+
DBUG_RETURN(error != 0);
}
@@ -1293,55 +1297,56 @@ int subselect_indexsubquery_engine::exec()
((Item_in_subselect *) item)->was_null= 0;
}
- if ((*tab->ref.key_copy) && (tab->ref.key_err= (*tab->ref.key_copy)->copy()))
+ for (store_key **copy=tab->ref.key_copy ; *copy ; copy++)
{
- table->status= STATUS_NOT_FOUND;
- error= -1;
+ if (tab->ref.key_err= (*copy)->copy())
+ {
+ table->status= STATUS_NOT_FOUND;
+ DBUG_RETURN(1);
+ }
}
+
+ if (!table->file->inited)
+ table->file->ha_index_init(tab->ref.key);
+ error= table->file->index_read(table->record[0],
+ tab->ref.key_buff,
+ tab->ref.key_length,HA_READ_KEY_EXACT);
+ if (error && error != HA_ERR_KEY_NOT_FOUND)
+ error= report_error(table, error);
else
{
- if (!table->file->inited)
- table->file->ha_index_init(tab->ref.key);
- error= table->file->index_read(table->record[0],
- tab->ref.key_buff,
- tab->ref.key_length,HA_READ_KEY_EXACT);
- if (error && error != HA_ERR_KEY_NOT_FOUND)
- error= report_error(table, error);
- else
+ for (;;)
{
- for (;;)
+ error= 0;
+ table->null_row= 0;
+ if (!table->status)
{
- error= 0;
- table->null_row= 0;
- if (!table->status)
- {
- if (!cond || cond->val_int())
- {
- if (null_finding)
- ((Item_in_subselect *) item)->was_null= 1;
- else
- ((Item_in_subselect *) item)->value= 1;
- break;
- }
- error= table->file->index_next_same(table->record[0],
- tab->ref.key_buff,
- tab->ref.key_length);
- if (error && error != HA_ERR_END_OF_FILE)
- {
- error= report_error(table, error);
- break;
- }
- }
- else
- {
- if (!check_null || null_finding)
- break; /* We don't need to check nulls */
- *tab->ref.null_ref_key= 1;
- null_finding= 1;
- /* Check if there exists a row with a null value in the index */
- if ((error= (safe_index_read(tab) == 1)))
- break;
- }
+ if (!cond || cond->val_int())
+ {
+ if (null_finding)
+ ((Item_in_subselect *) item)->was_null= 1;
+ else
+ ((Item_in_subselect *) item)->value= 1;
+ break;
+ }
+ error= table->file->index_next_same(table->record[0],
+ tab->ref.key_buff,
+ tab->ref.key_length);
+ if (error && error != HA_ERR_END_OF_FILE)
+ {
+ error= report_error(table, error);
+ break;
+ }
+ }
+ else
+ {
+ if (!check_null || null_finding)
+ break; /* We don't need to check nulls */
+ *tab->ref.null_ref_key= 1;
+ null_finding= 1;
+ /* Check if there exists a row with a null value in the index */
+ if ((error= (safe_index_read(tab) == 1)))
+ break;
}
}
}
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 6b8b8b0b3a7..5668b91263f 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -50,6 +50,8 @@ protected:
table_map used_tables_cache;
/* allowed number of columns (1 for single value subqueries) */
uint max_columns;
+ /* where subquery is placed */
+ enum_parsing_place parsing_place;
/* work with 'substitution' */
bool have_to_be_excluded;
/* cache of constant state */
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 9ada2fba164..bd06c269e74 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -293,6 +293,13 @@ void debug_sync_point(const char* lock_name, uint lock_timeout);
*/
#define MAX_DATE_REP_LENGTH 30
+enum enum_parsing_place
+{
+ NO_MATTER,
+ IN_HAVING,
+ SELECT_LIST
+};
+
struct st_table;
class THD;
class Statement;
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 2b6a307092c..f39cbc43b8b 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1017,7 +1017,7 @@ void st_select_lex::init_query()
select_n_having_items= 0;
prep_where= 0;
subquery_in_having= explicit_limit= 0;
- parsing_place= SELECT_LEX_NODE::NO_MATTER;
+ parsing_place= NO_MATTER;
}
void st_select_lex::init_select()
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 053c85166f6..da2c3fba097 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -220,12 +220,6 @@ protected:
*master, *slave, /* vertical links */
*link_next, **link_prev; /* list of whole SELECT_LEX */
public:
- enum enum_parsing_place
- {
- NO_MATTER,
- IN_HAVING,
- SELECT_LIST
- };
ulong options;
/*
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 8e3fb0884a9..de560041ba7 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1113,11 +1113,11 @@ create_select:
lex->sql_command= SQLCOM_REPLACE_SELECT;
lex->current_select->table_list.save_and_clear(&lex->save_list);
mysql_init_select(lex);
- lex->current_select->parsing_place= SELECT_LEX_NODE::SELECT_LIST;
+ lex->current_select->parsing_place= SELECT_LIST;
}
select_options select_item_list
{
- Select->parsing_place= SELECT_LEX_NODE::NO_MATTER;
+ Select->parsing_place= NO_MATTER;
}
opt_select_from
{ Lex->current_select->table_list.push_front(&Lex->save_list); }
@@ -2370,11 +2370,11 @@ select_part2:
lex->lock_option= TL_READ;
if (sel->linkage != UNION_TYPE)
mysql_init_select(lex);
- lex->current_select->parsing_place= SELECT_LEX_NODE::SELECT_LIST;
+ lex->current_select->parsing_place= SELECT_LIST;
}
select_options select_item_list
{
- Select->parsing_place= SELECT_LEX_NODE::NO_MATTER;
+ Select->parsing_place= NO_MATTER;
}
select_into select_lock_type;
@@ -3438,11 +3438,11 @@ select_derived:
YYABORT;
mysql_init_select(lex);
lex->current_select->linkage= DERIVED_TABLE_TYPE;
- lex->current_select->parsing_place= SELECT_LEX_NODE::SELECT_LIST;
+ lex->current_select->parsing_place= SELECT_LIST;
}
select_options select_item_list
{
- Select->parsing_place= SELECT_LEX_NODE::NO_MATTER;
+ Select->parsing_place= NO_MATTER;
}
opt_select_from union_opt
;
@@ -3572,13 +3572,13 @@ having_clause:
/* empty */
| HAVING
{
- Select->parsing_place= SELECT_LEX_NODE::IN_HAVING;
+ Select->parsing_place= IN_HAVING;
}
expr
{
SELECT_LEX *sel= Select;
sel->having= $3;
- sel->parsing_place= SELECT_LEX_NODE::NO_MATTER;
+ sel->parsing_place= NO_MATTER;
if ($3)
$3->top_level_item();
}
@@ -4813,7 +4813,7 @@ simple_ident:
ident
{
SELECT_LEX *sel=Select;
- $$= (sel->parsing_place != SELECT_LEX_NODE::IN_HAVING ||
+ $$= (sel->parsing_place != IN_HAVING ||
sel->get_in_sum_expr() > 0) ?
(Item*) new Item_field(NullS,NullS,$1.str) :
(Item*) new Item_ref(0,0, NullS,NullS,$1.str);
@@ -4829,7 +4829,7 @@ simple_ident:
ER(ER_TABLENAME_NOT_ALLOWED_HERE),
MYF(0), $1.str, thd->where);
}
- $$= (sel->parsing_place != SELECT_LEX_NODE::IN_HAVING ||
+ $$= (sel->parsing_place != IN_HAVING ||
sel->get_in_sum_expr() > 0) ?
(Item*) new Item_field(NullS,$1.str,$3.str) :
(Item*) new Item_ref(0,0,NullS,$1.str,$3.str);
@@ -4845,7 +4845,7 @@ simple_ident:
ER(ER_TABLENAME_NOT_ALLOWED_HERE),
MYF(0), $2.str, thd->where);
}
- $$= (sel->parsing_place != SELECT_LEX_NODE::IN_HAVING ||
+ $$= (sel->parsing_place != IN_HAVING ||
sel->get_in_sum_expr() > 0) ?
(Item*) new Item_field(NullS,$2.str,$4.str) :
(Item*) new Item_ref(0,0,NullS,$2.str,$4.str);
@@ -4861,7 +4861,7 @@ simple_ident:
ER(ER_TABLENAME_NOT_ALLOWED_HERE),
MYF(0), $3.str, thd->where);
}
- $$= (sel->parsing_place != SELECT_LEX_NODE::IN_HAVING ||
+ $$= (sel->parsing_place != IN_HAVING ||
sel->get_in_sum_expr() > 0) ?
(Item*) new Item_field((YYTHD->client_capabilities &
CLIENT_NO_SCHEMA ? NullS : $1.str),
View Lorry Controller Status