summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect.result70
-rw-r--r--mysql-test/t/subselect.test27
-rw-r--r--sql/item.cc54
-rw-r--r--sql/item.h26
-rw-r--r--sql/item_cmpfunc.cc7
-rw-r--r--sql/item_subselect.cc169
-rw-r--r--sql/item_subselect.h18
-rw-r--r--sql/sql_class.cc5
8 files changed, 307 insertions, 69 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 493768ece4a..d45b7b24585 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -52,6 +52,54 @@ a
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
a
1
+SELECT (SELECT 1,2,3) = ROW(1,2,3);
+(SELECT 1,2,3) = ROW(1,2,3)
+1
+SELECT (SELECT 1,2,3) = ROW(1,2,1);
+(SELECT 1,2,3) = ROW(1,2,1)
+0
+SELECT (SELECT 1,2,3) < ROW(1,2,1);
+(SELECT 1,2,3) < ROW(1,2,1)
+0
+SELECT (SELECT 1,2,3) > ROW(1,2,1);
+(SELECT 1,2,3) > ROW(1,2,1)
+1
+SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
+(SELECT 1,2,3) = ROW(1,2,NULL)
+NULL
+SELECT ROW(1,2,3) = (SELECT 1,2,3);
+ROW(1,2,3) = (SELECT 1,2,3)
+1
+SELECT ROW(1,2,3) = (SELECT 1,2,1);
+ROW(1,2,3) = (SELECT 1,2,1)
+0
+SELECT ROW(1,2,3) < (SELECT 1,2,1);
+ROW(1,2,3) < (SELECT 1,2,1)
+0
+SELECT ROW(1,2,3) > (SELECT 1,2,1);
+ROW(1,2,3) > (SELECT 1,2,1)
+1
+SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
+ROW(1,2,3) = (SELECT 1,2,NULL)
+NULL
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
+(SELECT 1.5,2,'a') = ROW(1.5,2,'a')
+1
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
+(SELECT 1.5,2,'a') = ROW(1.5,2,'b')
+0
+SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b');
+(SELECT 1.5,2,'a') = ROW('b',2,'b')
+0
+SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
+(SELECT 'b',2,'a') = ROW(1.5,2,'a')
+0
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a');
+(SELECT 1.5,2,'a') = ROW(1.5,'c','a')
+0
+SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
+(SELECT 1.5,'c','a') = ROW(1.5,2,'a')
+0
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
create table t1 (a int);
create table t2 (a int, b int);
@@ -602,7 +650,7 @@ CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin
INSERT INTO t1 values (1),(1);
UPDATE t SET id=(SELECT * FROM t1);
Subselect returns more than 1 record
-drop table t;
+drop table t, t1;
create table t (a int);
insert into t values (1),(2),(3);
select 1 IN (SELECT * from t);
@@ -705,3 +753,23 @@ select 10.5 > ANY (SELECT * from t);
10.5 > ANY (SELECT * from t)
1
drop table t;
+create table t1 (a int, b int, c varchar(10));
+create table t2 (a int);
+insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
+insert into t2 values (1),(2),(NULL);
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a) from t2;
+a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a') (select c from t1 where a=t2.a)
+1 1 a
+2 0 b
+NULL NULL NULL
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;
+a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b') (select c from t1 where a=t2.a)
+1 0 a
+2 1 b
+NULL NULL NULL
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;
+a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c') (select c from t1 where a=t2.a)
+1 0 a
+2 0 b
+NULL NULL NULL
+drop table t1,t2;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 0527d6a2001..ee3d31e66af 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -26,6 +26,22 @@ select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
+SELECT (SELECT 1,2,3) = ROW(1,2,3);
+SELECT (SELECT 1,2,3) = ROW(1,2,1);
+SELECT (SELECT 1,2,3) < ROW(1,2,1);
+SELECT (SELECT 1,2,3) > ROW(1,2,1);
+SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
+SELECT ROW(1,2,3) = (SELECT 1,2,3);
+SELECT ROW(1,2,3) = (SELECT 1,2,1);
+SELECT ROW(1,2,3) < (SELECT 1,2,1);
+SELECT ROW(1,2,3) > (SELECT 1,2,1);
+SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
+SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b');
+SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a');
+SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
create table t1 (a int);
@@ -363,7 +379,7 @@ CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin
INSERT INTO t1 values (1),(1);
-- error 1240
UPDATE t SET id=(SELECT * FROM t1);
-drop table t;
+drop table t, t1;
#NULL test
@@ -409,3 +425,12 @@ select 10.5 > ALL (SELECT * from t);
select 1.5 > ANY (SELECT * from t);
select 10.5 > ANY (SELECT * from t);
drop table t;
+
+create table t1 (a int, b int, c varchar(10));
+create table t2 (a int);
+insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
+insert into t2 values (1),(2),(NULL);
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a) from t2;
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;
+drop table t1,t2;
diff --git a/sql/item.cc b/sql/item.cc
index fbe0e27a01c..44851d26ef1 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1217,6 +1217,60 @@ bool field_is_equal_to_item(Field *field,Item *item)
return result == field->val_real();
}
+Item_cache* Item_cache::get_cache(Item_result type)
+{
+ switch (type)
+ {
+ case INT_RESULT:
+ return new Item_cache_int();
+ case REAL_RESULT:
+ return new Item_cache_real();
+ case STRING_RESULT:
+ return new Item_cache_str();
+ default:
+ // should never be in real life
+ DBUG_ASSERT(0);
+ return 0;
+ }
+}
+
+void Item_cache_str::store(Item *item)
+{
+ str_value.set(buffer, sizeof(buffer), item->charset());
+ value= item->str_result(&str_value);
+ if ((null_value= item->null_value))
+ value= 0;
+ else if (value != &str_value)
+ {
+ /*
+ We copy string value to avoid changing value if 'item' is table field
+ in queries like following (where t1.c is varchar):
+ select a,
+ (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),
+ (select c from t1 where a=t2.a)
+ from t2;
+ */
+ str_value.copy(*value);
+ value= &str_value;
+ }
+
+}
+double Item_cache_str::val()
+{
+ if (value)
+ return my_strntod(value->charset(), value->ptr(),
+ value->length(), (char**)0);
+ else
+ return (double)0;
+}
+longlong Item_cache_str::val_int()
+{
+ if (value)
+ return my_strntoll(value->charset(), value->ptr(),
+ value->length(), (char**) 0, 10);
+ else
+ return (longlong)0;
+}
/*****************************************************************************
** Instantiate templates
diff --git a/sql/item.h b/sql/item.h
index 461a3805080..d11148f8b3f 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -103,7 +103,9 @@ public:
virtual Item** addr(uint i) { return 0; }
virtual bool check_cols(uint c);
// It is not row => null inside is impossible
- virtual bool null_inside() { return 0; };
+ virtual bool null_inside() { return 0; }
+ // used in row subselects to get value of elements
+ virtual void bring_value() {}
};
@@ -719,6 +721,7 @@ public:
decimals= dec;
}
enum Type type() const { return CACHE_ITEM; }
+ static Item_cache* get_cache(Item_result type);
};
class Item_cache_int: public Item_cache
@@ -766,24 +769,9 @@ class Item_cache_str: public Item_cache
public:
Item_cache_str() { fixed= 1; null_value= 1; }
- void store(Item *item)
- {
- str_value.set(buffer, sizeof(buffer), item->charset());
- value= item->str_result(&str_value);
- // TODO remove if str_value charset have no side effect for now
- str_value.set_charset(value->charset());
- null_value= item->null_value;
- }
- double val()
- {
- return my_strntod(value->charset(), value->ptr(),
- value->length(), (char**)0);
- }
- longlong val_int()
- {
- return my_strntoll(value->charset(), value->ptr(),
- value->length(), (char**) 0, 10);
- }
+ void store(Item *item);
+ double val();
+ longlong val_int();
String* val_str(String *) { return value; }
enum Item_result result_type() const { return STRING_RESULT; }
CHARSET_INFO *charset() const { return value->charset(); };
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 851a591bae9..39840fc90fd 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -247,6 +247,8 @@ int Arg_comparator::compare_e_int()
int Arg_comparator::compare_row()
{
int res= 0;
+ (*a)->bring_value();
+ (*b)->bring_value();
uint n= (*a)->cols();
for (uint i= 0; i<n; i++)
{
@@ -261,6 +263,8 @@ int Arg_comparator::compare_row()
int Arg_comparator::compare_e_row()
{
int res= 0;
+ (*a)->bring_value();
+ (*b)->bring_value();
uint n= (*a)->cols();
for (uint i= 0; i<n; i++)
{
@@ -1219,6 +1223,7 @@ void cmp_item_row::store_value(Item *item)
n= item->cols();
if ((comparators= (cmp_item **) thd->alloc(sizeof(cmp_item *)*n)))
{
+ item->bring_value();
item->null_value= 0;
for (uint i=0; i < n; i++)
if ((comparators[i]= cmp_item::get_comparator(item->el(i))))
@@ -1252,6 +1257,7 @@ void cmp_item_row::store_value_by_template(cmp_item *t, Item *item)
n= tmpl->n;
if ((comparators= (cmp_item **) sql_alloc(sizeof(cmp_item *)*n)))
{
+ item->bring_value();
item->null_value= 0;
for (uint i=0; i < n; i++)
if ((comparators[i]= tmpl->comparators[i]->make_same()))
@@ -1284,6 +1290,7 @@ int cmp_item_row::cmp(Item *arg)
return 1;
}
bool was_null= 0;
+ arg->bring_value();
for (uint i=0; i < n; i++)
if (comparators[i]->cmp(arg->el(i)))
{
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 588be06a7e7..972c265a16b 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -112,9 +112,14 @@ bool Item_subselect::check_loop(uint id)
DBUG_RETURN(engine->check_loop(id));
}
+Item::Type Item_subselect::type() const
+{
+ return SUBSELECT_ITEM;
+}
+
void Item_subselect::fix_length_and_dec()
{
- engine->fix_length_and_dec();
+ engine->fix_length_and_dec(0);
}
inline table_map Item_subselect::used_tables() const
@@ -130,6 +135,7 @@ Item_singleval_subselect::Item_singleval_subselect(THD *thd,
init(thd, select_lex, new select_singleval_subselect(this));
max_columns= 1;
maybe_null= 1;
+ max_columns= UINT_MAX;
DBUG_VOID_RETURN;
}
@@ -140,9 +146,9 @@ void Item_singleval_subselect::reset()
value->null_value= 1;
}
-void Item_singleval_subselect::store(Item *item)
+void Item_singleval_subselect::store(uint i, Item *item)
{
- value->store(item);
+ row[i]->store(item);
}
enum Item_result Item_singleval_subselect::result_type() const
@@ -152,29 +158,58 @@ enum Item_result Item_singleval_subselect::result_type() const
void Item_singleval_subselect::fix_length_and_dec()
{
- engine->fix_length_and_dec();
- switch (engine->type())
+ if ((max_columns= engine->cols()) == 1)
+ {
+ engine->fix_length_and_dec(row= &value);
+ if (!(value= Item_cache::get_cache(engine->type())))
+ {
+ my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+ current_thd->fatal_error= 1;
+ return;
+ }
+ }
+ else
{
- case INT_RESULT:
- value= new Item_cache_int();
- break;
- case REAL_RESULT:
- value= new Item_cache_real();
- break;
- case STRING_RESULT:
- value= new Item_cache_str();
- break;
- default:
- // should never be in real life
- DBUG_ASSERT(0);
- return;
+ THD *thd= current_thd;
+ if (!(row= (Item_cache**)thd->alloc(sizeof(Item_cache*)*max_columns)))
+ {
+ my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+ thd->fatal_error= 1;
+ return;
+ }
+ engine->fix_length_and_dec(row);
+ value= *row;
}
- value->set_len_n_dec(max_length, decimals);
}
-Item::Type Item_subselect::type() const
+uint Item_singleval_subselect::cols()
{
- return SUBSELECT_ITEM;
+ return engine->cols();
+}
+
+bool Item_singleval_subselect::check_cols(uint c)
+{
+ if (c != engine->cols())
+ {
+ my_error(ER_CARDINALITY_COL, MYF(0), c);
+ return 1;
+ }
+ return 0;
+}
+
+bool Item_singleval_subselect::null_inside()
+{
+ for (uint i= 0; i < max_columns ; i++)
+ {
+ if (row[i]->null_value)
+ return 1;
+ }
+ return 0;
+}
+
+void Item_singleval_subselect::bring_value()
+{
+ engine->exec();
}
double Item_singleval_subselect::val ()
@@ -268,7 +303,7 @@ Item_allany_subselect::Item_allany_subselect(THD *thd, Item * left_exp,
void Item_exists_subselect::fix_length_and_dec()
{
- decimals=0;
+ decimals= 0;
max_length= 1;
}
@@ -540,31 +575,85 @@ int subselect_union_engine::prepare()
return unit->prepare(thd, result);
}
-void subselect_single_select_engine::fix_length_and_dec()
+static Item_result set_row(SELECT_LEX *select_lex, Item * item,
+ Item_cache **row)
{
+ Item_result res_type= STRING_RESULT;
+ Item *sel_item;
List_iterator_fast<Item> li(select_lex->item_list);
- Item *sel_item= li++;
- item->max_length= sel_item->max_length;
- res_type= sel_item->result_type();
- item->decimals= sel_item->decimals;
+ for (uint i= 0; (sel_item= li++); i++)
+ {
+ item->max_length= sel_item->max_length;
+ res_type= sel_item->result_type();
+ item->decimals= sel_item->decimals;
+ if (row)
+ {
+ if (!(row[i]= Item_cache::get_cache(res_type)))
+ {
+ my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+ current_thd->fatal_error= 1;
+ return STRING_RESULT; // we should return something
+ }
+ row[i]->set_len_n_dec(sel_item->max_length, sel_item->decimals);
+ }
+ }
+ if (select_lex->item_list.elements > 1)
+ res_type= ROW_RESULT;
+ return res_type;
}
-void subselect_union_engine::fix_length_and_dec()
+void subselect_single_select_engine::fix_length_and_dec(Item_cache **row)
{
- uint32 mlen= 0, len;
- Item *sel_item= 0;
- for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
+ DBUG_ASSERT(row || select_lex->item_list.elements==1);
+ res_type= set_row(select_lex, item, row);
+}
+
+void subselect_union_engine::fix_length_and_dec(Item_cache **row)
+{
+ DBUG_ASSERT(row || unit->first_select()->item_list.elements==1);
+
+ if (unit->first_select()->item_list.elements == 1)
{
- List_iterator_fast<Item> li(sl->item_list);
- Item *s_item= li++;
- if ((len= s_item->max_length))
- mlen= len;
- if (!sel_item)
- sel_item= s_item;
+ uint32 mlen= 0, len;
+ Item *sel_item= 0;
+ for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
+ {
+ List_iterator_fast<Item> li(sl->item_list);
+ Item *s_item= li++;
+ if ((len= s_item->max_length) > mlen)
+ mlen= len;
+ if (!sel_item)
+ sel_item= s_item;
+ }
+ item->max_length= mlen;
+ res_type= sel_item->result_type();
+ item->decimals= sel_item->decimals;
+ if (row)
+ {
+ if (!(row[0]= Item_cache::get_cache(res_type)))
+ {
+ my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+ current_thd->fatal_error= 1;
+ return;
+ }
+ row[0]->set_len_n_dec(mlen, sel_item->decimals);
+ }
+ }
+ else
+ {
+ SELECT_LEX *sl= unit->first_select();
+ res_type= set_row(sl, item, row);
+ for(sl= sl->next_select(); sl; sl->next_select())
+ {
+ List_iterator_fast<Item> li(sl->item_list);
+ Item *sel_item;
+ for (uint i= 0; (sel_item= li++); i++)
+ {
+ if (sel_item->max_length > row[i]->max_length)
+ row[i]->max_length= sel_item->max_length;
+ }
+ }
}
- item->max_length= mlen;
- res_type= sel_item->result_type();
- item->decimals= sel_item->decimals;
}
int subselect_single_select_engine::exec()
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index a43caca22f2..3cb68cb3875 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -94,8 +94,7 @@ class Item_cache;
class Item_singleval_subselect :public Item_subselect
{
protected:
- Item_cache *value;
-
+ Item_cache *value, **row;
public:
Item_singleval_subselect(THD *thd, st_select_lex *select_lex);
Item_singleval_subselect(Item_singleval_subselect *item):
@@ -106,7 +105,7 @@ public:
decimals= item->decimals;
}
void reset();
- void store(Item* item);
+ void store(uint i, Item* item);
double val();
longlong val_int ();
String *val_str (String *);
@@ -114,6 +113,13 @@ public:
enum Item_result result_type() const;
void fix_length_and_dec();
+ uint cols();
+ Item* el(uint i) { return (Item*)row[i]; }
+ Item** addr(uint i) { return (Item**)row + i; }
+ bool check_cols(uint c);
+ bool null_inside();
+ void bring_value();
+
friend class select_singleval_subselect;
};
@@ -212,7 +218,7 @@ public:
}
virtual int prepare()= 0;
- virtual void fix_length_and_dec()= 0;
+ virtual void fix_length_and_dec(Item_cache** row)= 0;
virtual int exec()= 0;
virtual uint cols()= 0; /* return number of columnss in select */
virtual bool depended()= 0; /* depended from outer select */
@@ -233,7 +239,7 @@ public:
select_subselect *result,
Item_subselect *item);
int prepare();
- void fix_length_and_dec();
+ void fix_length_and_dec(Item_cache** row);
int exec();
uint cols();
bool depended();
@@ -250,7 +256,7 @@ public:
select_subselect *result,
Item_subselect *item);
int prepare();
- void fix_length_and_dec();
+ void fix_length_and_dec(Item_cache** row);
int exec();
uint cols();
bool depended();
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 50164aed342..0b12a34ebfb 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -931,8 +931,9 @@ bool select_singleval_subselect::send_data(List<Item> &items)
DBUG_RETURN(0);
}
List_iterator_fast<Item> li(items);
- Item *val_item= li++; // Only one (single value subselect)
- it->store(val_item);
+ Item *val_item;
+ for (uint i= 0; (val_item= li++); i++)
+ it->store(i, val_item);
it->assigned(1);
DBUG_RETURN(0);
}
View Lorry Controller Status