summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect.result49
-rw-r--r--mysql-test/t/subselect.test42
-rw-r--r--sql/item.cc16
-rw-r--r--sql/item.h14
-rw-r--r--sql/item_cmpfunc.cc2
-rw-r--r--sql/item_subselect.cc13
-rw-r--r--sql/sp_rcontext.cc6
-rw-r--r--sql/sp_rcontext.h2
-rw-r--r--sql/sql_class.cc2
9 files changed, 132 insertions, 14 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index bfacfc86eef..1c450269809 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4150,4 +4150,53 @@ SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
0
0
DROP TABLE t1, t2;
+CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));
+INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);
+SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
+s1 s2
+SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
+s1 s2
+CREATE INDEX I1 ON t1 (s1);
+CREATE INDEX I2 ON t1 (s2);
+SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
+s1 s2
+SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
+s1 s2
+TRUNCATE t1;
+INSERT INTO t1 VALUES (0x41,0x41);
+SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
+s1 s2
+DROP TABLE t1;
+CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));
+CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));
+CREATE TABLE t3 (a3 BINARY(2) default '0');
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t2 VALUES (1),(2),(3);
+INSERT INTO t3 VALUES (1),(2),(3);
+SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;
+LEFT(t2.a2, 1)
+1
+2
+3
+SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;
+a1 t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2)
+1 0
+2 0
+3 0
+4 0
+DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));
+CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);
+CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);
+INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
+INSERT INTO t2 VALUES (2), (3), (4), (5);
+INSERT INTO t3 VALUES (10), (20), (30);
+SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;
+LEFT(t1.a1,1)
+1
+2
+3
+SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
+a2
+DROP TABLE t1, t2, t3;
End of 5.0 tests.
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index b5279331a5f..5a1870e49c6 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -3002,4 +3002,46 @@ INSERT INTO t2 VALUES (103, 203);
SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
DROP TABLE t1, t2;
+#
+# Bug #28076: inconsistent binary/varbinary comparison
+#
+
+CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));
+INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);
+
+SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
+SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
+
+CREATE INDEX I1 ON t1 (s1);
+CREATE INDEX I2 ON t1 (s2);
+
+SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
+SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
+
+TRUNCATE t1;
+INSERT INTO t1 VALUES (0x41,0x41);
+SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));
+CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));
+CREATE TABLE t3 (a3 BINARY(2) default '0');
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t2 VALUES (1),(2),(3);
+INSERT INTO t3 VALUES (1),(2),(3);
+SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;
+SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;
+DROP TABLE t1,t2,t3;
+
+CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));
+CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);
+CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);
+INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
+INSERT INTO t2 VALUES (2), (3), (4), (5);
+INSERT INTO t3 VALUES (10), (20), (30);
+SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;
+SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
+DROP TABLE t1, t2, t3;
+
--echo End of 5.0 tests.
diff --git a/sql/item.cc b/sql/item.cc
index dc3b1fc6b79..af3b1566632 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -6259,9 +6259,9 @@ bool field_is_equal_to_item(Field *field,Item *item)
return result == field->val_real();
}
-Item_cache* Item_cache::get_cache(Item_result type)
+Item_cache* Item_cache::get_cache(const Item *item)
{
- switch (type) {
+ switch (item->result_type()) {
case INT_RESULT:
return new Item_cache_int();
case REAL_RESULT:
@@ -6269,7 +6269,7 @@ Item_cache* Item_cache::get_cache(Item_result type)
case DECIMAL_RESULT:
return new Item_cache_decimal();
case STRING_RESULT:
- return new Item_cache_str();
+ return new Item_cache_str(item);
case ROW_RESULT:
return new Item_cache_row();
default:
@@ -6447,6 +6447,14 @@ my_decimal *Item_cache_str::val_decimal(my_decimal *decimal_val)
}
+int Item_cache_str::save_in_field(Field *field, bool no_conversions)
+{
+ int res= Item_cache::save_in_field(field, no_conversions);
+ return (is_varbinary && field->type() == MYSQL_TYPE_STRING &&
+ value->length() < field->field_length) ? 1 : res;
+}
+
+
bool Item_cache_row::allocate(uint num)
{
item_count= num;
@@ -6465,7 +6473,7 @@ bool Item_cache_row::setup(Item * item)
{
Item *el= item->element_index(i);
Item_cache *tmp;
- if (!(tmp= values[i]= Item_cache::get_cache(el->result_type())))
+ if (!(tmp= values[i]= Item_cache::get_cache(el)))
return 1;
tmp->setup(el);
}
diff --git a/sql/item.h b/sql/item.h
index 2f504badec0..a1135c2c725 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -2469,7 +2469,7 @@ public:
};
virtual void store(Item *)= 0;
enum Type type() const { return CACHE_ITEM; }
- static Item_cache* get_cache(Item_result type);
+ static Item_cache* get_cache(const Item *item);
table_map used_tables() const { return used_table_map; }
virtual void keep_array() {}
// to prevent drop fixed flag (no need parent cleanup call)
@@ -2531,9 +2531,16 @@ class Item_cache_str: public Item_cache
{
char buffer[STRING_BUFFER_USUAL_SIZE];
String *value, value_buff;
+ bool is_varbinary;
+
public:
- Item_cache_str(): Item_cache(), value(0) { }
-
+ Item_cache_str(const Item *item) :
+ Item_cache(), value(0),
+ is_varbinary(item->type() == FIELD_ITEM &&
+ ((const Item_field *) item)->field->type() ==
+ MYSQL_TYPE_VARCHAR &&
+ !((const Item_field *) item)->field->has_charset())
+ {}
void store(Item *item);
double val_real();
longlong val_int();
@@ -2541,6 +2548,7 @@ public:
my_decimal *val_decimal(my_decimal *);
enum Item_result result_type() const { return STRING_RESULT; }
CHARSET_INFO *charset() const { return value->charset(); };
+ int save_in_field(Field *field, bool no_conversions);
};
class Item_cache_row: public Item_cache
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index a9ad5ad675d..345b84e8868 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1373,7 +1373,7 @@ longlong Item_func_truth::val_int()
bool Item_in_optimizer::fix_left(THD *thd, Item **ref)
{
if (!args[0]->fixed && args[0]->fix_fields(thd, args) ||
- !cache && !(cache= Item_cache::get_cache(args[0]->result_type())))
+ !cache && !(cache= Item_cache::get_cache(args[0])))
return 1;
cache->setup(args[0]);
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 0020dd35c61..57c3b391507 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1717,7 +1717,7 @@ void subselect_engine::set_row(List<Item> &item_list, Item_cache **row)
item->decimals= sel_item->decimals;
item->unsigned_flag= sel_item->unsigned_flag;
maybe_null= sel_item->maybe_null;
- if (!(row[i]= Item_cache::get_cache(res_type)))
+ if (!(row[i]= Item_cache::get_cache(sel_item)))
return;
row[i]->setup(sel_item);
}
@@ -2178,6 +2178,7 @@ int subselect_indexsubquery_engine::exec()
((Item_in_subselect *) item)->value= 0;
empty_result_set= TRUE;
null_keypart= 0;
+ table->status= 0;
if (check_null)
{
@@ -2190,6 +2191,16 @@ int subselect_indexsubquery_engine::exec()
if (copy_ref_key())
DBUG_RETURN(1);
+ if (table->status)
+ {
+ /*
+ We know that there will be no rows even if we scan.
+ Can be set in copy_ref_key.
+ */
+ ((Item_in_subselect *) item)->value= 0;
+ DBUG_RETURN(0);
+ }
+
if (null_keypart)
DBUG_RETURN(scan_table());
diff --git a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc
index ac7c46c9fe5..54e016f6099 100644
--- a/sql/sp_rcontext.cc
+++ b/sql/sp_rcontext.cc
@@ -503,14 +503,14 @@ sp_cursor::fetch(THD *thd, List<struct sp_variable> *vars)
*/
Item_cache *
-sp_rcontext::create_case_expr_holder(THD *thd, Item_result result_type)
+sp_rcontext::create_case_expr_holder(THD *thd, const Item *item)
{
Item_cache *holder;
Query_arena current_arena;
thd->set_n_backup_active_arena(thd->spcont->callers_arena, &current_arena);
- holder= Item_cache::get_cache(result_type);
+ holder= Item_cache::get_cache(item);
thd->restore_active_arena(thd->spcont->callers_arena, &current_arena);
@@ -559,7 +559,7 @@ sp_rcontext::set_case_expr(THD *thd, int case_expr_id, Item **case_expr_item_ptr
case_expr_item->result_type())
{
m_case_expr_holders[case_expr_id]=
- create_case_expr_holder(thd, case_expr_item->result_type());
+ create_case_expr_holder(thd, case_expr_item);
}
m_case_expr_holders[case_expr_id]->store(case_expr_item);
diff --git a/sql/sp_rcontext.h b/sql/sp_rcontext.h
index 0104b71a648..43102cfeeb2 100644
--- a/sql/sp_rcontext.h
+++ b/sql/sp_rcontext.h
@@ -261,7 +261,7 @@ private:
bool init_var_table(THD *thd);
bool init_var_items();
- Item_cache *create_case_expr_holder(THD *thd, Item_result result_type);
+ Item_cache *create_case_expr_holder(THD *thd, const Item *item);
int set_variable(THD *thd, Field *field, Item **value);
}; // class sp_rcontext : public Sql_alloc
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 0bef946928b..ef199b6f883 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -1553,7 +1553,7 @@ bool select_max_min_finder_subselect::send_data(List<Item> &items)
{
if (!cache)
{
- cache= Item_cache::get_cache(val_item->result_type());
+ cache= Item_cache::get_cache(val_item);
switch (val_item->result_type())
{
case REAL_RESULT:
View Lorry Controller Status