summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/create.result30
-rw-r--r--mysql-test/r/insert_select.result29
-rw-r--r--mysql-test/r/temp_table.result21
-rw-r--r--mysql-test/r/type_float.result14
-rw-r--r--mysql-test/r/union.result4
-rw-r--r--mysql-test/t/insert_select.test27
-rw-r--r--mysql-test/t/temp_table.test15
-rw-r--r--sql/field.h4
-rw-r--r--sql/item.cc18
-rw-r--r--sql/item_sum.cc4
-rw-r--r--sql/sql_select.h53
11 files changed, 171 insertions, 48 deletions
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index ab9e6762d21..77eb2bb025f 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -455,21 +455,21 @@ create table t2 select ifnull(a,a), ifnull(b,b), ifnull(c,c), ifnull(d,d), ifnul
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
- `ifnull(a,a)` tinyint(4) DEFAULT NULL,
- `ifnull(b,b)` smallint(6) DEFAULT NULL,
- `ifnull(c,c)` mediumint(8) DEFAULT NULL,
- `ifnull(d,d)` int(11) DEFAULT NULL,
- `ifnull(e,e)` bigint(20) DEFAULT NULL,
- `ifnull(f,f)` float(24,2) DEFAULT NULL,
- `ifnull(g,g)` double(53,3) DEFAULT NULL,
- `ifnull(h,h)` decimal(5,4) DEFAULT NULL,
- `ifnull(i,i)` year(4) DEFAULT NULL,
- `ifnull(j,j)` date DEFAULT NULL,
- `ifnull(k,k)` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
- `ifnull(l,l)` datetime DEFAULT NULL,
- `ifnull(m,m)` varchar(1) DEFAULT NULL,
- `ifnull(n,n)` varchar(3) DEFAULT NULL,
- `ifnull(o,o)` varchar(10) DEFAULT NULL
+ `ifnull(a,a)` tinyint(4) default NULL,
+ `ifnull(b,b)` smallint(6) default NULL,
+ `ifnull(c,c)` mediumint(8) default NULL,
+ `ifnull(d,d)` int(11) default NULL,
+ `ifnull(e,e)` bigint(20) default NULL,
+ `ifnull(f,f)` float(3,2) default NULL,
+ `ifnull(g,g)` double(4,3) default NULL,
+ `ifnull(h,h)` decimal(5,4) default NULL,
+ `ifnull(i,i)` year(4) default NULL,
+ `ifnull(j,j)` date default NULL,
+ `ifnull(k,k)` timestamp NOT NULL default '0000-00-00 00:00:00',
+ `ifnull(l,l)` datetime default NULL,
+ `ifnull(m,m)` varchar(1) default NULL,
+ `ifnull(n,n)` varchar(3) default NULL,
+ `ifnull(o,o)` varchar(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1,t2;
create table t1(str varchar(10) default 'def',strnull varchar(10),intg int default '10',rel double default '3.14');
diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index 18a0ed1a1cb..57339b2e29f 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -730,3 +730,32 @@ f1 f2
2 2
10 10
DROP TABLE t1, t2;
+SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
+CREATE TABLE t1 (c VARCHAR(30), INDEX ix_c (c(10)));
+CREATE TABLE t2 (d VARCHAR(10));
+INSERT INTO t1 (c) VALUES ('7_chars'), ('13_characters');
+EXPLAIN
+SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+2 SUBQUERY t1 ref ix_c ix_c 13 const 1 Using where
+SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1;
+(SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters')
+13
+13
+INSERT INTO t2 (d)
+SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1;
+INSERT INTO t2 (d)
+SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='7_chars') FROM t1;
+INSERT INTO t2 (d)
+SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c IN (SELECT t1.c FROM t1))
+FROM t1;
+SELECT * FROM t2;
+d
+13
+13
+7
+7
+20
+20
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/temp_table.result b/mysql-test/r/temp_table.result
index 6c9a389c1f4..9cb4a6b2427 100644
--- a/mysql-test/r/temp_table.result
+++ b/mysql-test/r/temp_table.result
@@ -152,6 +152,27 @@ SELECT * FROM t1;
i
DROP TABLE t1;
End of 4.1 tests.
+CREATE TABLE t1 ( c FLOAT( 20, 14 ) );
+INSERT INTO t1 VALUES( 12139 );
+CREATE TABLE t2 ( c FLOAT(30,18) );
+INSERT INTO t2 VALUES( 123456 );
+SELECT AVG( c ) FROM t1 UNION SELECT 1;
+AVG( c )
+12139
+1
+SELECT 1 UNION SELECT AVG( c ) FROM t1;
+1
+1
+12139
+SELECT 1 UNION SELECT * FROM t2 UNION SELECT 1;
+1
+1
+123456
+SELECT c/1 FROM t1 UNION SELECT 1;
+c/1
+12139
+1
+DROP TABLE t1, t2;
create temporary table t1 (a int);
insert into t1 values (4711);
select * from t1;
diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result
index dbe60aff3d9..de782f9a5e4 100644
--- a/mysql-test/r/type_float.result
+++ b/mysql-test/r/type_float.result
@@ -91,10 +91,10 @@ col1 col2 col3 col4
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
- `col1` double DEFAULT NULL,
- `col2` double(53,5) DEFAULT NULL,
- `col3` double DEFAULT NULL,
- `col4` double DEFAULT NULL
+ `col1` double default NULL,
+ `col2` double(22,5) default NULL,
+ `col3` double default NULL,
+ `col4` double default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1,t2;
create table t1 (a float);
@@ -232,12 +232,12 @@ insert into t2 values ("1.23456780");
create table t3 select * from t2 union select * from t1;
select * from t3;
d
-1.234567800
-100000000.000000000
+1.2345678
+100000000
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
- `d` double(22,9) DEFAULT NULL
+ `d` double default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2, t3;
create table t1 select 105213674794682365.00 + 0.0 x;
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 447a8e032cb..2c8fa5cb896 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -554,7 +554,7 @@ aa
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `a` varbinary(20) NOT NULL DEFAULT ''
+ `a` varbinary(2) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 SELECT 12 as a UNION select 12.2 as a;
@@ -655,7 +655,7 @@ f
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `f` varbinary(24) DEFAULT NULL
+ `f` varbinary(12) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 SELECT y from t2 UNION select da from t2;
diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
index 0f9a0ca4872..95a55b732c5 100644
--- a/mysql-test/t/insert_select.test
+++ b/mysql-test/t/insert_select.test
@@ -293,4 +293,29 @@ INSERT INTO t2 (f1, f2)
SELECT * FROM t2;
DROP TABLE t1, t2;
-
+#
+# Bug #26207: inserts don't work with shortened index
+#
+SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
+
+CREATE TABLE t1 (c VARCHAR(30), INDEX ix_c (c(10)));
+CREATE TABLE t2 (d VARCHAR(10));
+INSERT INTO t1 (c) VALUES ('7_chars'), ('13_characters');
+
+EXPLAIN
+ SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1;
+
+SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1;
+
+INSERT INTO t2 (d)
+ SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1;
+
+INSERT INTO t2 (d)
+ SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='7_chars') FROM t1;
+
+INSERT INTO t2 (d)
+ SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c IN (SELECT t1.c FROM t1))
+ FROM t1;
+
+SELECT * FROM t2;
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/temp_table.test b/mysql-test/t/temp_table.test
index d06fde87b34..e10fd386bd3 100644
--- a/mysql-test/t/temp_table.test
+++ b/mysql-test/t/temp_table.test
@@ -164,6 +164,21 @@ DROP TABLE t1;
--echo End of 4.1 tests.
+#
+# Bug #24791: Union with AVG-groups generates wrong results
+#
+CREATE TABLE t1 ( c FLOAT( 20, 14 ) );
+INSERT INTO t1 VALUES( 12139 );
+
+CREATE TABLE t2 ( c FLOAT(30,18) );
+INSERT INTO t2 VALUES( 123456 );
+
+SELECT AVG( c ) FROM t1 UNION SELECT 1;
+SELECT 1 UNION SELECT AVG( c ) FROM t1;
+SELECT 1 UNION SELECT * FROM t2 UNION SELECT 1;
+SELECT c/1 FROM t1 UNION SELECT 1;
+
+DROP TABLE t1, t2;
#
# Test truncate with temporary tables
diff --git a/sql/field.h b/sql/field.h
index f27ed8b9394..b96be208e41 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -504,6 +504,7 @@ public:
{}
int store_decimal(const my_decimal *);
my_decimal *val_decimal(my_decimal *);
+ uint32 max_display_length() { return field_length; }
};
@@ -532,7 +533,6 @@ public:
void overflow(bool negative);
bool zero_pack() const { return 0; }
void sql_type(String &str) const;
- uint32 max_display_length() { return field_length; }
};
@@ -783,7 +783,6 @@ public:
void sort_string(char *buff,uint length);
uint32 pack_length() const { return sizeof(float); }
void sql_type(String &str) const;
- uint32 max_display_length() { return 24; }
};
@@ -825,7 +824,6 @@ public:
void sort_string(char *buff,uint length);
uint32 pack_length() const { return sizeof(double); }
void sql_type(String &str) const;
- uint32 max_display_length() { return 53; }
uint size_of() const { return sizeof(*this); }
};
diff --git a/sql/item.cc b/sql/item.cc
index 0c03bd14d5f..c7935607130 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -6437,8 +6437,6 @@ Item_type_holder::Item_type_holder(THD *thd, Item *item)
:Item(thd, item), enum_set_typelib(0), fld_type(get_real_type(item))
{
DBUG_ASSERT(item->fixed);
-
- max_length= display_length(item);
maybe_null= item->maybe_null;
collation.set(item->collation);
get_full_info(item);
@@ -6610,11 +6608,17 @@ bool Item_type_holder::join_types(THD *thd, Item *item)
{
int delta1= max_length_orig - decimals_orig;
int delta2= item->max_length - item->decimals;
- if (fld_type == MYSQL_TYPE_DECIMAL)
- max_length= max(delta1, delta2) + decimals;
- else
- max_length= min(max(delta1, delta2) + decimals,
- (fld_type == MYSQL_TYPE_FLOAT) ? FLT_DIG+6 : DBL_DIG+7);
+ max_length= max(delta1, delta2) + decimals;
+ if (fld_type == MYSQL_TYPE_FLOAT && max_length > FLT_DIG + 2)
+ {
+ max_length= FLT_DIG + 6;
+ decimals= NOT_FIXED_DEC;
+ }
+ if (fld_type == MYSQL_TYPE_DOUBLE && max_length > DBL_DIG + 2)
+ {
+ max_length= DBL_DIG + 7;
+ decimals= NOT_FIXED_DEC;
+ }
}
else
max_length= (fld_type == MYSQL_TYPE_FLOAT) ? FLT_DIG+6 : DBL_DIG+7;
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index f38950c95c3..cd67f83af17 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -1130,8 +1130,10 @@ void Item_sum_avg::fix_length_and_dec()
f_scale= args[0]->decimals;
dec_bin_size= my_decimal_get_binary_size(f_precision, f_scale);
}
- else
+ else {
decimals= min(args[0]->decimals + prec_increment, NOT_FIXED_DEC);
+ max_length= args[0]->max_length + prec_increment;
+ }
}
diff --git a/sql/sql_select.h b/sql/sql_select.h
index ca93179f9d9..ca37c7bd274 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -527,15 +527,11 @@ extern "C" int refpos_order_cmp(void* arg, const void *a,const void *b);
class store_key :public Sql_alloc
{
- protected:
- Field *to_field; // Store data here
- char *null_ptr;
- char err;
public:
bool null_key; /* TRUE <=> the value of the key has a null part */
enum store_key_result { STORE_KEY_OK, STORE_KEY_FATAL, STORE_KEY_CONV };
store_key(THD *thd, Field *field_arg, char *ptr, char *null, uint length)
- :null_ptr(null), err(0), null_key(0)
+ :null_key(0), null_ptr(null), err(0)
{
if (field_arg->type() == MYSQL_TYPE_BLOB)
{
@@ -550,8 +546,35 @@ public:
ptr, (uchar*) null, 1);
}
virtual ~store_key() {} /* Not actually needed */
- virtual enum store_key_result copy()=0;
virtual const char *name() const=0;
+
+ /**
+ @brief sets ignore truncation warnings mode and calls the real copy method
+
+ @details this function makes sure truncation warnings when preparing the
+ key buffers don't end up as errors (because of an enclosing INSERT/UPDATE).
+ */
+ enum store_key_result copy()
+ {
+ enum store_key_result result;
+ enum_check_fields saved_count_cuted_fields=
+ to_field->table->in_use->count_cuted_fields;
+
+ to_field->table->in_use->count_cuted_fields= CHECK_FIELD_IGNORE;
+
+ result= copy_inner();
+
+ to_field->table->in_use->count_cuted_fields= saved_count_cuted_fields;
+
+ return result;
+ }
+
+ protected:
+ Field *to_field; // Store data here
+ char *null_ptr;
+ char err;
+
+ virtual enum store_key_result copy_inner()=0;
};
@@ -571,7 +594,10 @@ class store_key_field: public store_key
copy_field.set(to_field,from_field,0);
}
}
- enum store_key_result copy()
+ const char *name() const { return field_name; }
+
+ protected:
+ enum store_key_result copy_inner()
{
TABLE *table= copy_field.to_field->table;
my_bitmap_map *old_map= dbug_tmp_use_all_columns(table,
@@ -581,7 +607,6 @@ class store_key_field: public store_key
null_key= to_field->is_null();
return err != 0 ? STORE_KEY_FATAL : STORE_KEY_OK;
}
- const char *name() const { return field_name; }
};
@@ -596,7 +621,10 @@ public:
null_ptr_arg ? null_ptr_arg : item_arg->maybe_null ?
&err : NullS, length), item(item_arg)
{}
- enum store_key_result copy()
+ const char *name() const { return "func"; }
+
+ protected:
+ enum store_key_result copy_inner()
{
TABLE *table= to_field->table;
my_bitmap_map *old_map= dbug_tmp_use_all_columns(table,
@@ -606,7 +634,6 @@ public:
null_key= to_field->is_null() || item->null_value;
return (err != 0 || res > 2 ? STORE_KEY_FATAL : (store_key_result) res);
}
- const char *name() const { return "func"; }
};
@@ -622,7 +649,10 @@ public:
&err : NullS, length, item_arg), inited(0)
{
}
- enum store_key_result copy()
+ const char *name() const { return "const"; }
+
+protected:
+ enum store_key_result copy_inner()
{
int res;
if (!inited)
@@ -637,7 +667,6 @@ public:
null_key= to_field->is_null() || item->null_value;
return (err > 2 ? STORE_KEY_FATAL : (store_key_result) err);
}
- const char *name() const { return "const"; }
};
bool cp_buffer_from_ref(THD *thd, TABLE *table, TABLE_REF *ref);
View Lorry Controller Status