summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/func_concat.result7
-rw-r--r--mysql-test/r/func_group.result24
-rw-r--r--mysql-test/r/func_in.result21
-rw-r--r--mysql-test/r/func_str.result15
-rw-r--r--mysql-test/r/func_time.result41
-rw-r--r--mysql-test/r/multi_update.result5
-rw-r--r--mysql-test/r/select.result14
-rw-r--r--mysql-test/t/func_concat.test7
-rw-r--r--mysql-test/t/func_group.test19
-rw-r--r--mysql-test/t/func_in.test12
-rw-r--r--mysql-test/t/func_str.test12
-rw-r--r--mysql-test/t/func_time.test21
-rw-r--r--mysql-test/t/multi_update.test8
-rw-r--r--mysql-test/t/select.test15
-rw-r--r--sql/field.cc16
-rw-r--r--sql/item.h16
-rw-r--r--sql/item_cmpfunc.cc175
-rw-r--r--sql/item_cmpfunc.h7
-rw-r--r--sql/item_strfunc.cc26
-rw-r--r--sql/item_timefunc.cc60
-rw-r--r--sql/item_timefunc.h12
-rw-r--r--sql/opt_range.cc2
-rw-r--r--sql/opt_sum.cc13
-rw-r--r--sql/sql_parse.cc4
-rw-r--r--sql/sql_select.cc6
-rw-r--r--sql/sql_select.h2
-rw-r--r--sql/structs.h8
27 files changed, 515 insertions, 53 deletions
diff --git a/mysql-test/r/func_concat.result b/mysql-test/r/func_concat.result
index cf6fbf2da4f..94f1f640523 100644
--- a/mysql-test/r/func_concat.result
+++ b/mysql-test/r/func_concat.result
@@ -64,3 +64,10 @@ select 'a' union select concat('a', -0.0);
a
a
good
+select concat((select x from (select 'a' as x) as t1 ),
+(select y from (select 'b' as y) as t2 )) from (select 1 union select 2 )
+as t3;
+concat((select x from (select 'a' as x) as t1 ),
+(select y from (select 'b' as y) as t2 ))
+ab
+ab
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 67966b999d4..ffa68f279f3 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -916,3 +916,27 @@ select count(*), min(7), max(7) from t2m, t1i;
count(*) min(7) max(7)
0 NULL NULL
drop table t1m, t1i, t2m, t2i;
+CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b));
+INSERT INTO t1 VALUES (1,'xx'), (2,'aa');
+SELECT * FROM t1;
+id b
+1 xx
+2 aa
+SELECT MAX(b) FROM t1 WHERE b < 'ppppp';
+MAX(b)
+aa
+SHOW WARNINGS;
+Level Code Message
+SELECT MAX(b) FROM t1 WHERE b < 'pp';
+MAX(b)
+aa
+DROP TABLE t1;
+CREATE TABLE t1 (id int PRIMARY KEY, b char(16), INDEX(b(4)));
+INSERT INTO t1 VALUES (1, 'xxxxbbbb'), (2, 'xxxxaaaa');
+SELECT MAX(b) FROM t1;
+MAX(b)
+xxxxbbbb
+EXPLAIN SELECT MAX(b) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+DROP TABLE t1;
diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index 3cf2afc83d1..0632dddb87e 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -202,3 +202,24 @@ select count(*) from t1 where id not in (1,2);
count(*)
1
drop table t1;
+create table t1 (f1 char(1), f2 int);
+insert into t1 values (1,0),('a',1),('z',2);
+select f1 from t1 where f1 in (1,'z');
+f1
+1
+z
+select f2 from t1 where f2 in (1,'z');
+f2
+0
+1
+select f1 from t1 where 'z' in (1,f1);
+f1
+z
+select * from t1 where 'z' in (f2,f1);
+f1 f2
+z 2
+select * from t1 where 1 in (f2,f1);
+f1 f2
+1 0
+a 1
+drop table t1;
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 0609624af18..24e6bb6f38a 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -1006,4 +1006,19 @@ NULL
select ifnull(load_file("lkjlkj"),"it's null");
ifnull(load_file("lkjlkj"),"it's null")
it's null
+create table t1 (f1 varchar(4), f2 varchar(64), unique key k1 (f1,f2));
+insert into t1 values ( 'test',md5('test')), ('test', sha('test'));
+select * from t1 where f1='test' and (f2= md5("test") or f2= md5("TEST"));
+f1 f2
+test 098f6bcd4621d373cade4e832627b4f6
+select * from t1 where f1='test' and (f2= md5("TEST") or f2= md5("test"));
+f1 f2
+test 098f6bcd4621d373cade4e832627b4f6
+select * from t1 where f1='test' and (f2= sha("test") or f2= sha("TEST"));
+f1 f2
+test a94a8fe5ccb19ba61c4c0873d391e987982fbbd3
+select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test"));
+f1 f2
+test a94a8fe5ccb19ba61c4c0873d391e987982fbbd3
+drop table t1;
End of 4.1 tests
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 3dcdf535038..e7a00b71961 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -630,6 +630,47 @@ select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')),
monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m'));
monthname(str_to_date(null, '%m')) monthname(str_to_date(null, '%m')) monthname(str_to_date(1, '%m')) monthname(str_to_date(0, '%m'))
NULL NULL January NULL
+create table t1(f1 date, f2 time, f3 datetime);
+insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01");
+insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02");
+select f1 from t1 where f1 between "2006-1-1" and 20060101;
+f1
+2006-01-01
+select f1 from t1 where f1 between "2006-1-1" and "2006.1.1";
+f1
+2006-01-01
+select f1 from t1 where date(f1) between "2006-1-1" and "2006.1.1";
+f1
+2006-01-01
+select f2 from t1 where f2 between "12:1:2" and "12:2:2";
+f2
+12:01:02
+select f2 from t1 where time(f2) between "12:1:2" and "12:2:2";
+f2
+12:01:02
+select f3 from t1 where f3 between "2006-1-1 12:1:1" and "2006-1-1 12:1:2";
+f3
+2006-01-01 12:01:01
+select f3 from t1 where timestamp(f3) between "2006-1-1 12:1:1" and "2006-1-1 12:1:2";
+f3
+2006-01-01 12:01:01
+select f1 from t1 where "2006-1-1" between f1 and f3;
+f1
+2006-01-01
+select f1 from t1 where "2006-1-1" between date(f1) and date(f3);
+f1
+2006-01-01
+select f1 from t1 where "2006-1-1" between f1 and 'zzz';
+f1
+Warnings:
+Warning 1292 Truncated incorrect date value: 'zzz'
+select f1 from t1 where makedate(2006,1) between date(f1) and date(f3);
+f1
+2006-01-01
+select f1 from t1 where makedate(2006,2) between date(f1) and date(f3);
+f1
+2006-01-02
+drop table t1;
select now() - now() + 0, curtime() - curtime() + 0,
sec_to_time(1) + 0, from_unixtime(1) + 0;
now() - now() + 0 curtime() - curtime() + 0 sec_to_time(1) + 0 from_unixtime(1) + 0
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index dba10296063..ee62bd3bce6 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -475,3 +475,8 @@ aclid bigint, index idx_acl(aclid)
insert into t2 values(1,null);
delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1';
drop table t1, t2;
+create table t1(a int);
+create table t2(a int);
+delete from t1,t2 using t1,t2 where t1.a=(select a from t1);
+ERROR HY000: You can't specify target table 't1' for update in FROM clause
+drop table t1, t2;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index bd5e9147c7c..7808e787e39 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2714,6 +2714,20 @@ select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 fro
f1 f2
1 1
drop table t1,t2;
+CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c));
+insert into t1 values (1,0,0),(2,0,0);
+CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a));
+insert into t2 values (1,'',''), (2,'','');
+CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b));
+insert into t3 values (1,1),(1,2);
+explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2
+where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and
+t2.b like '%%' order by t2.b limit 0,1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref b,c b 5 const 1 Using where; Using temporary; Using filesort
+1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
+DROP TABLE t1,t2,t3;
CREATE TABLE t1 (a int, INDEX idx(a));
INSERT INTO t1 VALUES (2), (3), (1);
EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx);
diff --git a/mysql-test/t/func_concat.test b/mysql-test/t/func_concat.test
index 69ce10c83c9..f546a25f647 100644
--- a/mysql-test/t/func_concat.test
+++ b/mysql-test/t/func_concat.test
@@ -50,4 +50,11 @@ select 'a' union select concat('a', -0);
--replace_result 'a-0.0' good 'a0.0' good
select 'a' union select concat('a', -0.0);
+#
+# Bug#16716: subselect in concat() may lead to a wrong result
+#
+select concat((select x from (select 'a' as x) as t1 ),
+ (select y from (select 'b' as y) as t2 )) from (select 1 union select 2 )
+ as t3;
+
# End of 4.1 tests
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index dc647dbcfeb..f8a3ed0f25e 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -598,4 +598,23 @@ select count(*), min(7), max(7) from t2m, t1i;
drop table t1m, t1i, t2m, t2i;
+#
+# Bug #18206: min/max optimization cannot be applied to partial index
+#
+
+CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b));
+INSERT INTO t1 VALUES (1,'xx'), (2,'aa');
+SELECT * FROM t1;
+
+SELECT MAX(b) FROM t1 WHERE b < 'ppppp';
+SHOW WARNINGS;
+SELECT MAX(b) FROM t1 WHERE b < 'pp';
+DROP TABLE t1;
+
+CREATE TABLE t1 (id int PRIMARY KEY, b char(16), INDEX(b(4)));
+INSERT INTO t1 VALUES (1, 'xxxxbbbb'), (2, 'xxxxaaaa');
+SELECT MAX(b) FROM t1;
+EXPLAIN SELECT MAX(b) FROM t1;
+DROP TABLE t1;
+
# End of 4.1 tests
diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test
index 2ffe5a2d5f7..3481f2c7b11 100644
--- a/mysql-test/t/func_in.test
+++ b/mysql-test/t/func_in.test
@@ -109,4 +109,16 @@ select count(*) from t1 where id not in (1);
select count(*) from t1 where id not in (1,2);
drop table t1;
+#
+# Bug#18360 Incorrect type coercion in IN() results in false comparison
+#
+create table t1 (f1 char(1), f2 int);
+insert into t1 values (1,0),('a',1),('z',2);
+select f1 from t1 where f1 in (1,'z');
+select f2 from t1 where f2 in (1,'z');
+select f1 from t1 where 'z' in (1,f1);
+select * from t1 where 'z' in (f2,f1);
+select * from t1 where 1 in (f2,f1);
+drop table t1;
+
# End of 4.1 tests
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index c2f76dbac43..c36e15a08b9 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -669,4 +669,16 @@ drop table t1;
select load_file("lkjlkj");
select ifnull(load_file("lkjlkj"),"it's null");
+#
+# Bug#15351: Wrong collation used for comparison of md5() and sha()
+# parameter can lead to a wrong result.
+#
+create table t1 (f1 varchar(4), f2 varchar(64), unique key k1 (f1,f2));
+insert into t1 values ( 'test',md5('test')), ('test', sha('test'));
+select * from t1 where f1='test' and (f2= md5("test") or f2= md5("TEST"));
+select * from t1 where f1='test' and (f2= md5("TEST") or f2= md5("test"));
+select * from t1 where f1='test' and (f2= sha("test") or f2= sha("TEST"));
+select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test"));
+drop table t1;
+
--echo End of 4.1 tests
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index f7012021705..23e3b9d982a 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -323,6 +323,27 @@ select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')),
monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m'));
#
+# Bug#16377 result of DATE/TIME functions were compared as strings which
+# can lead to a wrong result.
+#
+create table t1(f1 date, f2 time, f3 datetime);
+insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01");
+insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02");
+select f1 from t1 where f1 between "2006-1-1" and 20060101;
+select f1 from t1 where f1 between "2006-1-1" and "2006.1.1";
+select f1 from t1 where date(f1) between "2006-1-1" and "2006.1.1";
+select f2 from t1 where f2 between "12:1:2" and "12:2:2";
+select f2 from t1 where time(f2) between "12:1:2" and "12:2:2";
+select f3 from t1 where f3 between "2006-1-1 12:1:1" and "2006-1-1 12:1:2";
+select f3 from t1 where timestamp(f3) between "2006-1-1 12:1:1" and "2006-1-1 12:1:2";
+select f1 from t1 where "2006-1-1" between f1 and f3;
+select f1 from t1 where "2006-1-1" between date(f1) and date(f3);
+select f1 from t1 where "2006-1-1" between f1 and 'zzz';
+select f1 from t1 where makedate(2006,1) between date(f1) and date(f3);
+select f1 from t1 where makedate(2006,2) between date(f1) and date(f3);
+drop table t1;
+
+#
# Bug #16546
#
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index 27bdf4df3d7..e628e900c73 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -448,4 +448,12 @@ insert into t2 values(1,null);
delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1';
drop table t1, t2;
+#
+# Bug#19225: unchecked error leads to server crash
+#
+create table t1(a int);
+create table t2(a int);
+--error 1093
+delete from t1,t2 using t1,t2 where t1.a=(select a from t1);
+drop table t1, t2;
# End of 4.1 tests
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 12eafef9bde..f7b5a2fbcb6 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -2249,6 +2249,21 @@ select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 fro
drop table t1,t2;
#
+# Bug #4981: 4.x and 5.x produce non-optimal execution path, 3.23 regression test failure
+#
+CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c));
+insert into t1 values (1,0,0),(2,0,0);
+CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a));
+insert into t2 values (1,'',''), (2,'','');
+CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b));
+insert into t3 values (1,1),(1,2);
+# must have "range checked" for t2
+explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2
+ where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and
+ t2.b like '%%' order by t2.b limit 0,1;
+DROP TABLE t1,t2,t3;
+
+#
# Bug #17873: confusing error message when IGNORE INDEX refers a column name
#
diff --git a/sql/field.cc b/sql/field.cc
index a64eaad7308..ec4d4b4e4f5 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -6841,7 +6841,11 @@ create_field::create_field(Field *old_field,Field *orig_field)
bool
Field::set_warning(const uint level, const uint code, int cuted_increment)
{
- THD *thd= table->in_use;
+ /*
+ If this field was created only for type conversion purposes it
+ will have table == NULL.
+ */
+ THD *thd= table ? table->in_use : current_thd;
if (thd->count_cuted_fields)
{
thd->cuted_fields+= cuted_increment;
@@ -6876,7 +6880,8 @@ Field::set_datetime_warning(const uint level, const uint code,
timestamp_type ts_type, int cuted_increment)
{
if (set_warning(level, code, cuted_increment))
- make_truncated_value_warning(table->in_use, str, str_length, ts_type);
+ make_truncated_value_warning(table ? table->in_use : current_thd,
+ str, str_length, ts_type);
}
@@ -6905,8 +6910,8 @@ Field::set_datetime_warning(const uint level, const uint code,
{
char str_nr[22];
char *str_end= longlong10_to_str(nr, str_nr, -10);
- make_truncated_value_warning(table->in_use, str_nr, str_end - str_nr,
- ts_type);
+ make_truncated_value_warning(table ? table->in_use : current_thd,
+ str_nr, str_end - str_nr, ts_type);
}
}
@@ -6935,7 +6940,8 @@ Field::set_datetime_warning(const uint level, const uint code,
/* DBL_DIG is enough to print '-[digits].E+###' */
char str_nr[DBL_DIG + 8];
uint str_len= my_sprintf(str_nr, (str_nr, "%g", nr));
- make_truncated_value_warning(table->in_use, str_nr, str_len, ts_type);
+ make_truncated_value_warning(table ? table->in_use : current_thd,
+ str_nr, str_len, ts_type);
}
}
diff --git a/sql/item.h b/sql/item.h
index 66da7ad4e74..eca8ee184a1 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -327,6 +327,14 @@ public:
cleanup();
delete this;
}
+ /*
+ result_as_longlong() must return TRUE for Items representing DATE/TIME
+ functions and DATE/TIME table fields.
+ Those Items have result_type()==STRING_RESULT (and not INT_RESULT), but
+ their values should be compared as integers (because the integer
+ representation is more precise than the string one).
+ */
+ virtual bool result_as_longlong() { return FALSE; }
};
@@ -450,6 +458,10 @@ public:
Item *get_tmp_table_item(THD *thd);
void cleanup();
inline uint32 max_disp_length() { return field->max_length(); }
+ bool result_as_longlong()
+ {
+ return field->can_be_compared_as_longlong();
+ }
friend class Item_default_value;
friend class Item_insert_value;
friend class st_select_lex_unit;
@@ -973,6 +985,10 @@ public:
}
Item *real_item() { return *ref; }
void print(String *str);
+ bool result_as_longlong()
+ {
+ return (*ref)->result_as_longlong();
+ }
};
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 3c41fb56d89..126037a24d8 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -25,6 +25,8 @@
#include <m_ctype.h>
#include "sql_select.h"
+static bool convert_constant_item(THD *thd, Field *field, Item **item);
+
static Item_result item_store_type(Item_result a,Item_result b)
{
if (a == STRING_RESULT || b == STRING_RESULT)
@@ -58,12 +60,145 @@ static void agg_result_type(Item_result *type, Item **items, uint nitems)
}
}
-static void agg_cmp_type(Item_result *type, Item **items, uint nitems)
+
+/*
+ Aggregates result types from the array of items.
+
+ SYNOPSIS:
+ agg_cmp_type()
+ thd thread handle
+ type [out] the aggregated type
+ items array of items to aggregate the type from
+ nitems number of items in the array
+
+ DESCRIPTION
+ This function aggregates result types from the array of items. Found type
+ supposed to be used later for comparison of values of these items.
+ Aggregation itself is performed by the item_cmp_type() function.
+
+ NOTES
+ Aggregation rules:
+ If all items are constants the type will be aggregated from all items.
+ If there are some non-constant items then only types of non-constant
+ items will be used for aggregation.
+ If there are DATE/TIME fields/functions in the list and no string
+ fields/functions in the list then:
+ The INT_RESULT type will be used for aggregation instead of original
+ result type of any DATE/TIME field/function in the list
+ All constant items in the list will be converted to a DATE/TIME using
+ found field or result field of found function.
+
+ Implementation notes:
+ The code is equivalent to:
+ 1. Check the list for presence of a STRING field/function.
+ Collect the is_const flag.
+ 2. Get a Field* object to use for type coercion
+ 3. Perform type conversion.
+ 1 and 2 are implemented in 2 loops. The first searches for a DATE/TIME
+ field/function and checks presence of a STRING field/function.
+ The second loop works only if a DATE/TIME field/function is found.
+ It checks presence of a STRING field/function in the rest of the list.
+
+ TODO
+ 1) The current implementation can produce false comparison results for
+ expressions like:
+ date_time_field BETWEEN string_field_with_dates AND string_constant
+ if the string_constant will omit some of leading zeroes.
+ In order to fully implement correct comparison of DATE/TIME the new
+ DATETIME_RESULT result type should be introduced and agg_cmp_type()
+ should return the DATE/TIME field used for the conversion. Later
+ this field can be used by comparison functions like Item_func_between to
+ convert string values to ints on the fly and thus return correct results.
+ This modification will affect functions BETWEEN, IN and CASE.
+
+ 2) If in the list a DATE field/function and a DATETIME field/function
+ are present in the list then the first found field/function will be
+ used for conversion. This may lead to wrong results and probably should
+ be fixed.
+*/
+
+static void agg_cmp_type(THD *thd, Item_result *type, Item **items, uint nitems)
{
uint i;
- type[0]= items[0]->result_type();
- for (i=1 ; i < nitems ; i++)
- type[0]= item_cmp_type(type[0], items[i]->result_type());
+ Item::Type res= (Item::Type)0;
+ /* Used only for date/time fields, max_length = 19 */
+ char buff[20];
+ uchar null_byte;
+ Field *field= NULL;
+
+ /* Search for date/time fields/functions */
+ for (i= 0; i < nitems; i++)
+ {
+ if (!items[i]->result_as_longlong())
+ {
+ /* Do not convert anything if a string field/function is present */
+ if (!items[i]->const_item() && items[i]->result_type() == STRING_RESULT)
+ {
+ i= nitems;
+ break;
+ }
+ continue;
+ }
+ if ((res= items[i]->real_item()->type()) == Item::FIELD_ITEM)
+ {
+ field= ((Item_field *)items[i]->real_item())->field;
+ break;
+ }
+ else if (res == Item::FUNC_ITEM)
+ {
+ field= items[i]->tmp_table_field_from_field_type(0);
+ if (field)
+ field->move_field(buff, &null_byte, 0);
+ break;
+ }
+ }
+ if (field)
+ {
+ /* Check the rest of the list for presence of a string field/function. */
+ for (i++ ; i < nitems; i++)
+ {
+ if (!items[i]->const_item() && items[i]->result_type() == STRING_RESULT &&
+ !items[i]->result_as_longlong())
+ {
+ if (res == Item::FUNC_ITEM)
+ delete field;
+ field= 0;
+ break;
+ }
+ }
+ }
+ /* Reset to 0 on first occurence of non-const item. 1 otherwise */
+ bool is_const= items[0]->const_item();
+ /*
+ If the first item is a date/time function then its result should be
+ compared as int
+ */
+ if (field)
+ {
+ /* Suppose we are comparing dates and some non-constant items are present. */
+ type[0]= INT_RESULT;
+ is_const= 0;
+ }
+ else
+ type[0]= items[0]->result_type();
+
+ for (i= 0; i < nitems ; i++)
+ {
+ if (!items[i]->const_item())
+ {
+ Item_result result= field && items[i]->result_as_longlong() ?
+ INT_RESULT : items[i]->result_type();
+ type[0]= is_const ? result : item_cmp_type(type[0], result);
+ is_const= 0;
+ }
+ else if (is_const)
+ type[0]= item_cmp_type(type[0], items[i]->result_type());
+ else if (field)
+ convert_constant_item(thd, field, &items[i]);
+ }
+
+ if (res == Item::FUNC_ITEM && field)
+ delete field;
}
static void my_coll_agg_error(DTCollation &c1, DTCollation &c2,
@@ -896,31 +1031,9 @@ void Item_func_between::fix_length_and_dec()
*/
if (!args[0] || !args[1] || !args[2])
return;
- agg_cmp_type(&cmp_type, args, 3);
- if (cmp_type == STRING_RESULT &&
- agg_arg_charsets(cmp_collation, args, 3, MY_COLL_CMP_CONV))
- return;
-
- /*
- Make a special case of compare with date/time and longlong fields.
- They are compared as integers, so for const item this time-consuming
- conversion can be done only once, not for every single comparison
- */
- if (args[0]->type() == FIELD_ITEM)
- {
- Field *field=((Item_field*) args[0])->field;
- if (field->can_be_compared_as_longlong())
- {
- /*
- The following can't be recoded with || as convert_constant_item
- changes the argument
- */
- if (convert_constant_item(thd, field,&args[1]))
- cmp_type=INT_RESULT; // Works for all types.
- if (convert_constant_item(thd, field,&args[2]))
- cmp_type=INT_RESULT; // Works for all types.
- }
- }
+ agg_cmp_type(thd, &cmp_type, args, 3);
+ if (cmp_type == STRING_RESULT)
+ agg_arg_charsets(cmp_collation, args, 3, MY_COLL_CMP_CONV);
}
@@ -1444,7 +1557,7 @@ void Item_func_case::fix_length_and_dec()
for (nagg= 0; nagg < ncases/2 ; nagg++)
agg[nagg+1]= args[nagg*2];
nagg++;
- agg_cmp_type(&cmp_type, agg, nagg);
+ agg_cmp_type(current_thd, &cmp_type, agg, nagg);
if ((cmp_type == STRING_RESULT) &&
agg_arg_charsets(cmp_collation, agg, nagg, MY_COLL_CMP_CONV))
return;
@@ -1925,7 +2038,7 @@ void Item_func_in::fix_length_and_dec()
uint const_itm= 1;
THD *thd= current_thd;
- agg_cmp_type(&cmp_type, args, arg_count);
+ agg_cmp_type(thd, &cmp_type, args, arg_count);
if (cmp_type == STRING_RESULT &&
agg_arg_charsets(cmp_collation, args, arg_count, MY_COLL_CMP_CONV))
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index ade09113c63..68852b5a5f6 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -44,7 +44,7 @@ public:
inline int set_compare_func(Item_bool_func2 *owner_arg)
{
return set_compare_func(owner_arg, item_cmp_type((*a)->result_type(),
- (*b)->result_type()));
+ (*b)->result_type()));
}
inline int set_cmp_func(Item_bool_func2 *owner_arg,
Item **a1, Item **a2,
@@ -57,8 +57,9 @@ public:
inline int set_cmp_func(Item_bool_func2 *owner_arg,
Item **a1, Item **a2)
{
- return set_cmp_func(owner_arg, a1, a2, item_cmp_type((*a1)->result_type(),
- (*a2)->result_type()));
+ return set_cmp_func(owner_arg, a1, a2,
+ item_cmp_type((*a1)->result_type(),
+ (*a2)->result_type()));
}
inline int compare() { return (this->*func)(); }
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index e74d0100b55..ee585649f8c 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -118,7 +118,15 @@ String *Item_func_md5::val_str(String *str)
void Item_func_md5::fix_length_and_dec()
{
- max_length=32;
+ max_length=32;
+ /*
+ The MD5() function treats its parameter as being a case sensitive. Thus
+ we set binary collation on it so different instances of MD5() will be
+ compared properly.
+ */
+ args[0]->collation.set(
+ get_charset_by_csname(args[0]->collation.collation->csname,
+ MY_CS_BINSORT,MYF(0)), DERIVATION_COERCIBLE);
}
@@ -159,7 +167,15 @@ String *Item_func_sha::val_str(String *str)
void Item_func_sha::fix_length_and_dec()
{
- max_length=SHA1_HASH_SIZE*2; // size of hex representation of hash
+ max_length=SHA1_HASH_SIZE*2; // size of hex representation of hash
+ /*
+ The SHA() function treats its parameter as being a case sensitive. Thus
+ we set binary collation on it so different instances of MD5() will be
+ compared properly.
+ */
+ args[0]->collation.set(
+ get_charset_by_csname(args[0]->collation.collation->csname,
+ MY_CS_BINSORT,MYF(0)), DERIVATION_COERCIBLE);
}
@@ -252,11 +268,14 @@ String *Item_func_concat::val_str(String *str)
DBUG_ASSERT(fixed == 1);
String *res,*res2,*use_as_buff;
uint i;
+ bool is_const= 0;
null_value=0;
if (!(res=args[0]->val_str(str)))
goto null;
use_as_buff= &tmp_value;
+ /* Item_subselect in --ps-protocol mode will state it as a non-const */
+ is_const= args[0]->const_item() || !args[0]->used_tables();
for (i=1 ; i < arg_count ; i++)
{
if (res->length() == 0)
@@ -279,7 +298,7 @@ String *Item_func_concat::val_str(String *str)
current_thd->variables.max_allowed_packet);
goto null;
}
- if (res->alloced_length() >= res->length()+res2->length())
+ if (!is_const && res->alloced_length() >= res->length()+res2->length())
{ // Use old buffer
res->append(*res2);
}
@@ -334,6 +353,7 @@ String *Item_func_concat::val_str(String *str)
res= &tmp_value;
use_as_buff=str;
}
+ is_const= 0;
}
}
res->set_charset(collation.collation);
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 7ab3936d700..8d3e768b74e 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -2306,6 +2306,20 @@ String *Item_datetime_typecast::val_str(String *str)
}
+longlong Item_datetime_typecast::val_int()
+{
+ DBUG_ASSERT(fixed == 1);
+ TIME ltime;
+ if (get_arg0_date(&ltime,1))
+ {
+ null_value= 1;
+ return 0;
+ }
+
+ return TIME_to_ulonglong_datetime(&ltime);
+}
+
+
bool Item_time_typecast::get_time(TIME *ltime)
{
bool res= get_arg0_time(ltime);
@@ -2320,6 +2334,17 @@ bool Item_time_typecast::get_time(TIME *ltime)
}
+longlong Item_time_typecast::val_int()
+{
+ TIME ltime;
+ if (get_time(&ltime))
+ {
+ null_value= 1;
+ return 0;
+ }
+ return ltime.hour * 10000L + ltime.minute * 100 + ltime.second;
+}
+
String *Item_time_typecast::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
@@ -2359,6 +2384,14 @@ String *Item_date_typecast::val_str(String *str)
return 0;
}
+longlong Item_date_typecast::val_int()
+{
+ DBUG_ASSERT(fixed == 1);
+ TIME ltime;
+ if (args[0]->get_date(&ltime, TIME_FUZZY_DATE))
+ return 0;
+ return (longlong) (ltime.year * 10000L + ltime.month * 100 + ltime.day);
+}
/*
MAKEDATE(a,b) is a date function that creates a date value
@@ -2395,6 +2428,33 @@ err:
}
+longlong Item_func_makedate::val_int()
+{
+ DBUG_ASSERT(fixed == 1);
+ TIME l_time;
+ long daynr= (long) args[1]->val_int();
+ long yearnr= (long) args[0]->val_int();
+ long days;
+
+ if (args[0]->null_value || args[1]->null_value ||
+ yearnr < 0 || daynr <= 0)
+ goto err;
+
+ days= calc_daynr(yearnr,1,1) + daynr - 1;
+ /* Day number from year 0 to 9999-12-31 */
+ if (days >= 0 && days < MAX_DAY_NUMBER)
+ {
+ null_value=0;
+ get_date_from_daynr(days,&l_time.year,&l_time.month,&l_time.day);
+ return (longlong) (l_time.year * 10000L + l_time.month * 100 + l_time.day);
+ }
+
+err:
+ null_value= 1;
+ return 0;
+}
+
+
void Item_func_add_time::fix_length_and_dec()
{
enum_field_types arg0_field_type;
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index ac26455371d..45cad627c05 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -339,6 +339,7 @@ public:
{
return (new Field_date(maybe_null, name, t_arg, &my_charset_bin));
}
+ bool result_as_longlong() { return TRUE; }
};
@@ -354,6 +355,7 @@ public:
{
return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin));
}
+ bool result_as_longlong() { return TRUE; }
};
@@ -383,6 +385,7 @@ public:
TIME representation using UTC-SYSTEM or per-thread time zone.
*/
virtual void store_now_in_TIME(TIME *now_time)=0;
+ bool result_as_longlong() { return TRUE; }
};
@@ -589,6 +592,7 @@ public:
{
return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
}
+ bool result_as_longlong() { return TRUE; }
};
/*
@@ -716,6 +720,8 @@ public:
max_length= 10;
maybe_null= 1;
}
+ bool result_as_longlong() { return TRUE; }
+ longlong val_int();
};
@@ -732,6 +738,8 @@ public:
{
return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
}
+ bool result_as_longlong() { return TRUE; }
+ longlong val_int();
};
@@ -747,6 +755,8 @@ public:
{
return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin));
}
+ bool result_as_longlong() { return TRUE; }
+ longlong val_int();
};
class Item_func_makedate :public Item_str_func
@@ -765,6 +775,8 @@ public:
{
return (new Field_date(maybe_null, name, t_arg, &my_charset_bin));
}
+ bool result_as_longlong() { return TRUE; }
+ longlong val_int();
};
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 71f937f90c6..67141aab6ce 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -64,7 +64,7 @@ public:
uint8 min_flag, uint8 max_flag, uint8 maybe_flag);
SEL_ARG(enum Type type_arg)
:elements(1),use_count(1),left(0),next_key_part(0),color(BLACK),
- type(type_arg)
+ type(type_arg),min_flag(0)
{}
inline bool is_same(SEL_ARG *arg)
{
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index cfb5b3695a3..97e271121d3 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -582,7 +582,8 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo,
}
else
{
- store_val_in_field(part->field, args[between && max_fl ? 2 : 1]);
+ store_val_in_field(part->field, args[between && max_fl ? 2 : 1],
+ CHECK_FIELD_IGNORE);
if (part->null_bit)
*key_ptr++= (byte) test(part->field->is_null());
part->field->get_key_image((char*) key_ptr, part->length,
@@ -638,6 +639,8 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo,
field BETWEEN const1 AND const2
3. all references to the columns from the same table as column field
occur only in conjucts mentioned above.
+ 4. each of k first components the index is not partial, i.e. is not
+ defined on a fixed length proper prefix of the field.
If such an index exists the function through the ref parameter
returns the key value to find max/min for the field using the index,
@@ -647,8 +650,8 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo,
of the whole search key)
NOTE
- This function may set table->key_read to 1, which must be reset after
- index is used! (This can only happen when function returns 1)
+ This function may set table->key_read to 1, which must be reset after
+ index is used! (This can only happen when function returns 1)
RETURN
0 Index can not be used to optimize MIN(field)/MAX(field)
@@ -682,6 +685,10 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref,
if (!(table->file->index_flags(idx, jdx, 0) & HA_READ_ORDER))
return 0;
+ /* Check whether the index component is partial */
+ if (part->length < table->field[part->fieldnr-1]->pack_length())
+ break;
+
if (field->eq(part->field))
{
ref->key= idx;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 51ef3f31b26..06005f31198 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3047,8 +3047,8 @@ unsent_create_error:
}
}
- if (!thd->is_fatal_error && (result= new multi_delete(thd,aux_tables,
- table_count)))
+ if (!res && !thd->is_fatal_error &&
+ (result= new multi_delete(thd,aux_tables, table_count)))
{
res= mysql_select(thd, &select_lex->ref_pointer_array,
select_lex->get_table_list(),
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 57fb9738612..5a7e9e52aed 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3434,7 +3434,7 @@ get_store_key(THD *thd, KEYUSE *keyuse, table_map used_tables,
*/
bool
-store_val_in_field(Field *field,Item *item)
+store_val_in_field(Field *field, Item *item, enum_check_fields check_flag)
{
bool error;
THD *thd=current_thd;
@@ -3445,7 +3445,7 @@ store_val_in_field(Field *field,Item *item)
with select_insert, which make count_cuted_fields= 1
*/
enum_check_fields old_count_cuted_fields= thd->count_cuted_fields;
- thd->count_cuted_fields= CHECK_FIELD_WARN;
+ thd->count_cuted_fields= check_flag;
error= item->save_in_field(field, 1);
thd->count_cuted_fields= old_count_cuted_fields;
return error || cuted_fields != thd->cuted_fields;
@@ -7097,7 +7097,7 @@ static bool test_if_ref(Item_field *left_item,Item *right_item)
field->real_type() != FIELD_TYPE_VAR_STRING &&
(field->type() != FIELD_TYPE_FLOAT || field->decimals() == 0))
{
- return !store_val_in_field(field,right_item);
+ return !store_val_in_field(field, right_item, CHECK_FIELD_WARN);
}
}
}
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 94cc8839466..75cd0b4d797 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -335,7 +335,7 @@ extern const char *join_type_str[];
void TEST_join(JOIN *join);
/* Extern functions in sql_select.cc */
-bool store_val_in_field(Field *field,Item *val);
+bool store_val_in_field(Field *field, Item *val, enum_check_fields check_flag);
TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
ORDER *group, bool distinct, bool save_sum_fields,
ulong select_options, ha_rows rows_limit,
diff --git a/sql/structs.h b/sql/structs.h
index 081ada88bf7..2037496635a 100644
--- a/sql/structs.h
+++ b/sql/structs.h
@@ -70,7 +70,13 @@ typedef struct st_key_part_info { /* Info about a key part */
Field *field;
uint offset; /* offset in record (from 0) */
uint null_offset; /* Offset to null_bit in record */
- uint16 length; /* Length of key_part */
+ uint16 length; /* Length of keypart value in bytes */
+ /*
+ Number of bytes required to store the keypart value. This may be
+ different from the "length" field as it also counts
+ - possible NULL-flag byte (see HA_KEY_NULL_LENGTH)
+ - possible HA_KEY_BLOB_LENGTH bytes needed to store actual value length.
+ */
uint16 store_length;
uint16 key_type;
uint16 fieldnr; /* Fieldnum in UNIREG */
View Lorry Controller Status