summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/row.result76
-rw-r--r--mysql-test/t/row.test25
-rw-r--r--sql/item_cmpfunc.cc130
-rw-r--r--sql/item_cmpfunc.h191
-rw-r--r--sql/sql_list.h10
5 files changed, 365 insertions, 67 deletions
diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result
index e5bc5f4abe5..e0b524d9bb2 100644
--- a/mysql-test/r/row.result
+++ b/mysql-test/r/row.result
@@ -4,6 +4,35 @@ row(1,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3))
select row(10,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3));
row(10,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3))
0
+select row(1,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3));
+row(1,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3))
+1
+select row(10,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3));
+row(10,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3))
+NULL
+select row('a',1.5,3) IN (row(1,2,3), row('a',1.5,3), row('a','a','a'));
+row('a',1.5,3) IN (row(1,2,3), row('a',1.5,3), row('a','a','a'))
+1
+select row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3));
+row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3))
+1
+select row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3));
+row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3))
+1
+select row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3));
+row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3))
+1
+select row('b',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3));
+row('b',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3))
+NULL
+select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,4)));
+row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,4)))
+1
+select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,4));
+Cardinality error (more/less than 2 columns)
+select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL)));
+row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL)))
+NULL
SELECT ROW(1,2,3)=ROW(1,2,3);
ROW(1,2,3)=ROW(1,2,3)
1
@@ -42,9 +71,20 @@ ROW('test',2,3.33)=ROW('test',2,3.33)
1
SELECT ROW('test',2,3.33)=ROW('test',2,3.33,4);
Cardinality error (more/less than 3 columns)
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,33));
+ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,33))
+1
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,3));
+ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,3))
+0
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,NULL));
+ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,NULL))
+NULL
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,4);
+Cardinality error (more/less than 2 columns)
drop table if exists t1;
create table t1 ( a int, b int, c int);
-insert into t1 values (1,2,3), (2,3,1), (3,2,1);
+insert into t1 values (1,2,3), (2,3,1), (3,2,1), (1,2,NULL);
select * from t1 where ROW(1,2,3)=ROW(a,b,c);
a b c
1 2 3
@@ -54,16 +94,30 @@ select * from t1 where ROW(1,2,3)<ROW(a,b,c);
a b c
2 3 1
3 2 1
-select * from t1 where ROW(a,2,3) IN(row(1,b,c), row(2,3,1));
-a b c
-1 2 3
-select * from t1 where ROW(c,2,3) IN(row(1,b,a), row(2,3,1));
-a b c
-3 2 1
-select * from t1 where ROW(a,b,c) IN(row(1,2,3), row(3,2,1));
-a b c
-1 2 3
-3 2 1
+select ROW(a,2,3) IN(row(1,b,c), row(2,3,1)) from t1;
+ROW(a,2,3) IN(row(1,b,c), row(2,3,1))
+1
+0
+0
+NULL
+select ROW(c,2,3) IN(row(1,b,a), row(2,3,1)) from t1;
+ROW(c,2,3) IN(row(1,b,a), row(2,3,1))
+0
+0
+1
+NULL
+select ROW(a,b,c) IN(row(1,2,3), row(3,2,1)) from t1;
+ROW(a,b,c) IN(row(1,2,3), row(3,2,1))
+1
+0
+1
+NULL
+select ROW(1,2,3) IN(row(a,b,c), row(1,2,3)) from t1;
+ROW(1,2,3) IN(row(a,b,c), row(1,2,3))
+1
+1
+1
+1
drop table t1;
select ROW(1,1);
Cardinality error (more/less than 1 columns)
diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test
index 4515d6b220d..04f31fcbfe1 100644
--- a/mysql-test/t/row.test
+++ b/mysql-test/t/row.test
@@ -1,5 +1,16 @@
select row(1,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3));
select row(10,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3));
+select row(1,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3));
+select row(10,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3));
+select row('a',1.5,3) IN (row(1,2,3), row('a',1.5,3), row('a','a','a'));
+select row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3));
+select row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3));
+select row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3));
+select row('b',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3));
+select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,4)));
+-- error 1239
+select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,4));
+select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL)));
SELECT ROW(1,2,3)=ROW(1,2,3);
SELECT ROW(2,2,3)=ROW(1+1,2,3);
@@ -15,15 +26,21 @@ SELECT ROW(1,2,ROW(3,4,5))=ROW(1,2,ROW(3,4,5));
SELECT ROW('test',2,3.33)=ROW('test',2,3.33);
-- error 1239
SELECT ROW('test',2,3.33)=ROW('test',2,3.33,4);
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,33));
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,3));
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,NULL));
+-- error 1239
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,4);
drop table if exists t1;
create table t1 ( a int, b int, c int);
-insert into t1 values (1,2,3), (2,3,1), (3,2,1);
+insert into t1 values (1,2,3), (2,3,1), (3,2,1), (1,2,NULL);
select * from t1 where ROW(1,2,3)=ROW(a,b,c);
select * from t1 where ROW(0,2,3)=ROW(a,b,c);
select * from t1 where ROW(1,2,3)<ROW(a,b,c);
-select * from t1 where ROW(a,2,3) IN(row(1,b,c), row(2,3,1));
-select * from t1 where ROW(c,2,3) IN(row(1,b,a), row(2,3,1));
-select * from t1 where ROW(a,b,c) IN(row(1,2,3), row(3,2,1));
+select ROW(a,2,3) IN(row(1,b,c), row(2,3,1)) from t1;
+select ROW(c,2,3) IN(row(1,b,a), row(2,3,1)) from t1;
+select ROW(a,b,c) IN(row(1,2,3), row(3,2,1)) from t1;
+select ROW(1,2,3) IN(row(a,b,c), row(1,2,3)) from t1;
drop table t1;
-- error 1239
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 9a6107d4b47..370feb65e3a 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -144,7 +144,14 @@ int Arg_comparator::set_compare_func(Item_bool_func2 *item, Item_result type)
}
if ((comparators= (Arg_comparator *) sql_alloc(sizeof(Arg_comparator)*n)))
for (uint i=0; i < n; i++)
+ {
+ if ((*a)->cols() != (*a)->cols())
+ {
+ my_error(ER_CARDINALITY_COL, MYF(0), (*a)->cols());
+ return 1;
+ }
comparators[i].set_cmp_func(owner, (*a)->addr(i), (*b)->addr(i));
+ }
else
{
my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
@@ -1033,6 +1040,11 @@ static int cmp_double(double *a,double *b)
return *a < *b ? -1 : *a == *b ? 0 : 1;
}
+static int cmp_row(cmp_item_row* a, cmp_item_row* b)
+{
+ return a->compare(b);
+}
+
int in_vector::find(Item *item)
{
byte *result=get_value(item);
@@ -1055,7 +1067,6 @@ int in_vector::find(Item *item)
return (int) ((*compare)(base+start*size,result) == 0);
}
-
in_string::in_string(uint elements,qsort_cmp cmp_func)
:in_vector(elements,sizeof(String),cmp_func),tmp(buff,sizeof(buff),default_charset_info)
{}
@@ -1082,6 +1093,29 @@ byte *in_string::get_value(Item *item)
return (byte*) item->val_str(&tmp);
}
+in_row::in_row(uint elements, Item * item)
+{
+ DBUG_ENTER("in_row::in_row");
+ base= (char*) new cmp_item_row[elements];
+ size= sizeof(cmp_item_row);
+ compare= (qsort_cmp) cmp_row;
+ tmp.store_value(item);
+ DBUG_VOID_RETURN;
+}
+
+byte *in_row::get_value(Item *item)
+{
+ tmp.store_value(item);
+ return (byte *)&tmp;
+}
+
+void in_row::set(uint pos, Item *item)
+{
+ DBUG_ENTER("in_row::set");
+ DBUG_PRINT("enter", ("pos %u item 0x%lx", pos, (ulong) item));
+ ((cmp_item_row*) base)[pos].store_value_by_template(&tmp, item);
+ DBUG_VOID_RETURN;
+}
in_longlong::in_longlong(uint elements)
:in_vector(elements,sizeof(longlong),(qsort_cmp) cmp_longlong)
@@ -1094,13 +1128,12 @@ void in_longlong::set(uint pos,Item *item)
byte *in_longlong::get_value(Item *item)
{
- tmp=item->val_int();
+ tmp= item->val_int();
if (item->null_value)
- return 0; /* purecov: inspected */
+ return 0;
return (byte*) &tmp;
}
-
in_double::in_double(uint elements)
:in_vector(elements,sizeof(double),(qsort_cmp) cmp_double)
{}
@@ -1112,7 +1145,7 @@ void in_double::set(uint pos,Item *item)
byte *in_double::get_value(Item *item)
{
- tmp=item->val();
+ tmp= item->val();
if (item->null_value)
return 0; /* purecov: inspected */
return (byte*) &tmp;
@@ -1140,14 +1173,43 @@ cmp_item* cmp_item::get_comparator (Item *item)
return 0; // to satisfy compiler :)
}
+cmp_item* cmp_item_sort_string::make_same()
+{
+ return new cmp_item_sort_string_in_static();
+}
+
+cmp_item* cmp_item_binary_string::make_same()
+{
+ return new cmp_item_binary_string_in_static();
+}
+
+cmp_item* cmp_item_int::make_same()
+{
+ return new cmp_item_int();
+}
+
+cmp_item* cmp_item_real::make_same()
+{
+ return new cmp_item_real();
+}
+
+cmp_item* cmp_item_row::make_same()
+{
+ return new cmp_item_row();
+}
+
void cmp_item_row::store_value(Item *item)
{
n= item->cols();
if ((comparators= (cmp_item **) sql_alloc(sizeof(cmp_item *)*n)))
{
+ item->null_value= 0;
for (uint i=0; i < n; i++)
if ((comparators[i]= cmp_item::get_comparator(item->el(i))))
+ {
comparators[i]->store_value(item->el(i));
+ item->null_value|= item->el(i)->null_value;
+ }
else
{
my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
@@ -1163,17 +1225,70 @@ void cmp_item_row::store_value(Item *item)
}
}
+void cmp_item_row::store_value_by_template(cmp_item *t, Item *item)
+{
+ cmp_item_row *tmpl= (cmp_item_row*) t;
+ if (tmpl->n != item->cols())
+ {
+ my_error(ER_CARDINALITY_COL, MYF(0), tmpl->n);
+ return;
+ }
+ n= tmpl->n;
+ if ((comparators= (cmp_item **) sql_alloc(sizeof(cmp_item *)*n)))
+ {
+ item->null_value= 0;
+ for (uint i=0; i < n; i++)
+ if ((comparators[i]= tmpl->comparators[i]->make_same()))
+ {
+ comparators[i]->store_value_by_template(tmpl->comparators[i],
+ item->el(i));
+ item->null_value|= item->el(i)->null_value;
+ }
+ else
+ {
+ my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+ current_thd->fatal_error= 1;
+ return;
+ }
+ }
+ else
+ {
+ my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+ current_thd->fatal_error= 1;
+ return;
+ }
+}
+
int cmp_item_row::cmp(Item *arg)
{
+ arg->null_value= 0;
+ if (arg->cols() != n)
+ {
+ my_error(ER_CARDINALITY_COL, MYF(0), n);
+ return 1;
+ }
for(uint i=0; i < n; i++)
if(comparators[i]->cmp(arg->el(i)))
+ {
+ arg->null_value|= arg->el(i)->null_value;
return 1;
+ }
+ return 0;
+}
+
+int cmp_item_row::compare(cmp_item *c)
+{
+ int res;
+ cmp_item_row *cmp= (cmp_item_row *) c;
+ for(uint i=0; i < n; i++)
+ if((res= comparators[i]->compare(cmp->comparators[i])))
+ return res;
return 0;
}
void Item_func_in::fix_length_and_dec()
{
- if (const_item() && item->result_type()!=ROW_RESULT)
+ if (const_item())
{
switch (item->result_type()) {
case STRING_RESULT:
@@ -1189,8 +1304,7 @@ void Item_func_in::fix_length_and_dec()
array= new in_double(arg_count);
break;
case ROW_RESULT:
- // This case should never be choosen
- DBUG_ASSERT(0);
+ array= new in_row(arg_count, item);
break;
}
uint j=0;
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index d15e8856ece..fbf0eb3c123 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -17,6 +17,8 @@
/* compare and test functions */
+#include "assert.h"
+
#ifdef __GNUC__
#pragma interface /* gcc class implementation */
#endif
@@ -375,6 +377,7 @@ class in_vector :public Sql_alloc
uint count;
public:
uint used_count;
+ in_vector() {}
in_vector(uint elements,uint element_length,qsort_cmp cmp_func)
:base((char*) sql_calloc(elements*element_length)),
size(element_length), compare(cmp_func), count(elements),
@@ -389,7 +392,6 @@ public:
int find(Item *item);
};
-
class in_string :public in_vector
{
char buff[80];
@@ -401,7 +403,6 @@ public:
byte *get_value(Item *item);
};
-
class in_longlong :public in_vector
{
longlong tmp;
@@ -411,7 +412,6 @@ public:
byte *get_value(Item *item);
};
-
class in_double :public in_vector
{
double tmp;
@@ -421,7 +421,6 @@ public:
byte *get_value(Item *item);
};
-
/*
** Classes for easy comparing of non const items
*/
@@ -431,73 +430,114 @@ class cmp_item :public Sql_alloc
public:
cmp_item() {}
virtual ~cmp_item() {}
- virtual void store_value(Item *item)=0;
- virtual int cmp(Item *item)=0;
+ virtual void store_value(Item *item)= 0;
+ virtual int cmp(Item *item)= 0;
+ // for optimized IN with row
+ virtual int compare(cmp_item *item)= 0;
static cmp_item* get_comparator(Item *);
+ virtual cmp_item *make_same()= 0;
+ virtual void store_value_by_template(cmp_item *tmpl, Item *item)
+ {
+ store_value(item);
+ }
};
+class cmp_item_string :public cmp_item
+{
+protected:
+ String *value_res;
+public:
+ friend class cmp_item_sort_string;
+ friend class cmp_item_binary_string;
+ friend class cmp_item_sort_string_in_static;
+ friend class cmp_item_binary_string_in_static;
+};
-class cmp_item_sort_string :public cmp_item {
- protected:
+class cmp_item_sort_string :public cmp_item_string
+{
+protected:
char value_buff[80];
- String value,*value_res;
+ String value;
public:
- cmp_item_sort_string() :value(value_buff,sizeof(value_buff),default_charset_info) {}
+ cmp_item_sort_string():
+ value(value_buff, sizeof(value_buff), default_charset_info) {}
void store_value(Item *item)
- {
- value_res=item->val_str(&value);
- }
+ {
+ value_res= item->val_str(&value);
+ }
int cmp(Item *arg)
- {
- char buff[80];
- String tmp(buff,sizeof(buff),default_charset_info),*res;
- if (!(res=arg->val_str(&tmp)))
- return 1; /* Can't be right */
- return sortcmp(value_res,res);
- }
+ {
+ char buff[80];
+ String tmp(buff, sizeof(buff), default_charset_info), *res;
+ if (!(res= arg->val_str(&tmp)))
+ return 1; /* Can't be right */
+ return sortcmp(value_res, res);
+ }
+ int compare(cmp_item *c)
+ {
+ cmp_item_string *cmp= (cmp_item_string *)c;
+ return sortcmp(value_res, cmp->value_res);
+ }
+ cmp_item *make_same();
};
class cmp_item_binary_string :public cmp_item_sort_string {
public:
cmp_item_binary_string() {}
int cmp(Item *arg)
- {
- char buff[80];
- String tmp(buff,sizeof(buff),default_charset_info),*res;
- if (!(res=arg->val_str(&tmp)))
- return 1; /* Can't be right */
- return stringcmp(value_res,res);
- }
+ {
+ char buff[80];
+ String tmp(buff,sizeof(buff),default_charset_info),*res;
+ if (!(res=arg->val_str(&tmp)))
+ return 1; /* Can't be right */
+ return stringcmp(value_res,res);
+ }
+ int compare(cmp_item *c)
+ {
+ cmp_item_string *cmp= (cmp_item_string *)c;
+ return stringcmp(value_res, cmp->value_res);
+ }
+ cmp_item *make_same();
};
-
class cmp_item_int :public cmp_item
{
longlong value;
public:
void store_value(Item *item)
- {
- value=item->val_int();
- }
+ {
+ value= item->val_int();
+ }
int cmp(Item *arg)
- {
- return value != arg->val_int();
- }
+ {
+ return value != arg->val_int();
+ }
+ int compare(cmp_item *c)
+ {
+ cmp_item_int *cmp= (cmp_item_int *)c;
+ return (value < cmp->value) ? -1 : ((value == cmp->value) ? 0 : 1);
+ }
+ cmp_item *make_same();
};
-
class cmp_item_real :public cmp_item
{
double value;
public:
void store_value(Item *item)
- {
- value= item->val();
- }
+ {
+ value= item->val();
+ }
int cmp(Item *arg)
- {
- return value != arg->val();
- }
+ {
+ return value != arg->val();
+ }
+ int compare(cmp_item *c)
+ {
+ cmp_item_real *cmp= (cmp_item_real *)c;
+ return (value < cmp->value)? -1 : ((value == cmp->value) ? 0 : 1);
+ }
+ cmp_item *make_same();
};
class cmp_item_row :public cmp_item
@@ -505,8 +545,75 @@ class cmp_item_row :public cmp_item
cmp_item **comparators;
uint n;
public:
+ cmp_item_row(): comparators(0), n(0) {}
+ ~cmp_item_row()
+ {
+ if(comparators)
+ for(uint i= 0; i < n; i++)
+ if (comparators[i])
+ delete comparators[i];
+ }
void store_value(Item *item);
int cmp(Item *arg);
+ int compare(cmp_item *arg);
+ cmp_item *make_same();
+ void store_value_by_template(cmp_item *tmpl, Item *);
+};
+
+
+class in_row :public in_vector
+{
+ cmp_item_row tmp;
+public:
+ in_row(uint elements, Item *);
+ void set(uint pos,Item *item);
+ byte *get_value(Item *item);
+};
+
+/*
+ cmp_item for optimized IN with row (right part string, which never
+ be changed)
+*/
+
+class cmp_item_sort_string_in_static :public cmp_item_string
+{
+ protected:
+ String value;
+public:
+ cmp_item_sort_string_in_static() {}
+ void store_value(Item *item)
+ {
+ value_res= item->val_str(&value);
+ }
+ int cmp(Item *item)
+ {
+ // Should never be called
+ DBUG_ASSERT(0);
+ return 1;
+ }
+ int compare(cmp_item *c)
+ {
+ cmp_item_string *cmp= (cmp_item_string *)c;
+ return sortcmp(value_res, cmp->value_res);
+ }
+ cmp_item * make_same()
+ {
+ return new cmp_item_sort_string_in_static();
+ }
+};
+
+class cmp_item_binary_string_in_static :public cmp_item_sort_string_in_static {
+public:
+ cmp_item_binary_string_in_static() {}
+ int compare(cmp_item *c)
+ {
+ cmp_item_string *cmp= (cmp_item_string *)c;
+ return stringcmp(value_res, cmp->value_res);
+ }
+ cmp_item * make_same()
+ {
+ return new cmp_item_binary_string_in_static();
+ }
};
class Item_func_in :public Item_int_func
@@ -546,8 +653,6 @@ class Item_func_in :public Item_int_func
}
};
-
-
/* Functions used by where clause */
class Item_func_isnull :public Item_bool_func
diff --git a/sql/sql_list.h b/sql/sql_list.h
index 56e6528f214..1711a340cae 100644
--- a/sql/sql_list.h
+++ b/sql/sql_list.h
@@ -25,8 +25,16 @@
class Sql_alloc
{
public:
- static void *operator new(size_t size) {return (void*) sql_alloc((uint) size); }
+ static void *operator new(size_t size)
+ {
+ return (void*) sql_alloc((uint) size);
+ }
+ static void *operator new[](size_t size)
+ {
+ return (void*) sql_alloc((uint) size);
+ }
static void operator delete(void *ptr, size_t size) {} /*lint -e715 */
+ static void operator delete[](void *ptr, size_t size) {}
#ifdef HAVE_purify
bool dummy;
inline Sql_alloc() :dummy(0) {}
View Lorry Controller Status