summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/include/ps_conv.inc4
-rw-r--r--mysql-test/r/bdb_notembedded.result35
-rw-r--r--mysql-test/r/distinct.result2
-rw-r--r--mysql-test/r/ps_2myisam.result4
-rw-r--r--mysql-test/r/ps_3innodb.result4
-rw-r--r--mysql-test/r/ps_4heap.result4
-rw-r--r--mysql-test/r/ps_5merge.result8
-rw-r--r--mysql-test/r/subselect.result4
-rw-r--r--mysql-test/r/type_datetime.result39
-rw-r--r--mysql-test/t/bdb_notembedded.test38
-rw-r--r--mysql-test/t/type_datetime.test20
-rw-r--r--sql/item.cc23
-rw-r--r--sql/item.h3
-rw-r--r--sql/item_cmpfunc.cc318
-rw-r--r--sql/item_cmpfunc.h27
-rw-r--r--sql/sql_select.cc18
-rw-r--r--tests/mysql_client_test.c6
17 files changed, 517 insertions, 40 deletions
diff --git a/mysql-test/include/ps_conv.inc b/mysql-test/include/ps_conv.inc
index 09290d760ce..195d1061664 100644
--- a/mysql-test/include/ps_conv.inc
+++ b/mysql-test/include/ps_conv.inc
@@ -1171,7 +1171,7 @@ execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
######## SELECT .. WHERE column(date/time/..)=value(DATETIME/LONGBLOB) ########
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -1180,7 +1180,7 @@ select 'true' as found from t9
where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
and c17= @arg00 ;
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/bdb_notembedded.result b/mysql-test/r/bdb_notembedded.result
new file mode 100644
index 00000000000..14cb5fad915
--- /dev/null
+++ b/mysql-test/r/bdb_notembedded.result
@@ -0,0 +1,35 @@
+set autocommit=1;
+reset master;
+create table bug16206 (a int);
+insert into bug16206 values(1);
+start transaction;
+insert into bug16206 values(2);
+commit;
+show binlog events;
+Log_name Pos Event_type Server_id End_log_pos Info
+f n Format_desc 1 n Server ver: VERSION, Binlog ver: 4
+f n Query 1 n use `test`; create table bug16206 (a int)
+f n Query 1 n use `test`; insert into bug16206 values(1)
+f n Query 1 n use `test`; insert into bug16206 values(2)
+drop table bug16206;
+reset master;
+create table bug16206 (a int) engine= bdb;
+insert into bug16206 values(0);
+insert into bug16206 values(1);
+start transaction;
+insert into bug16206 values(2);
+commit;
+insert into bug16206 values(3);
+show binlog events;
+Log_name Pos Event_type Server_id End_log_pos Info
+f n Format_desc 1 n Server ver: VERSION, Binlog ver: 4
+f n Query 1 n use `test`; create table bug16206 (a int) engine= bdb
+f n Query 1 n use `test`; insert into bug16206 values(0)
+f n Query 1 n use `test`; insert into bug16206 values(1)
+f n Query 1 n use `test`; BEGIN
+f n Query 1 n use `test`; insert into bug16206 values(2)
+f n Query 1 n use `test`; COMMIT
+f n Query 1 n use `test`; insert into bug16206 values(3)
+drop table bug16206;
+set autocommit=0;
+End of 5.0 tests
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 190e8595126..8525e0f19e4 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -633,7 +633,7 @@ EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1
WHERE ADDDATE(a,1) = '2002-08-03');
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci);
INSERT INTO t2 VALUES (0xf6);
INSERT INTO t2 VALUES ('oe');
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index de6e2d62763..2b2a29b2122 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -3070,7 +3070,7 @@ found
true
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3083,7 +3083,7 @@ and c17= @arg00 ;
found
true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index 1ebaafdd488..f30262ef219 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -3053,7 +3053,7 @@ found
true
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3066,7 +3066,7 @@ and c17= @arg00 ;
found
true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index 74b9326dbc1..a012e516404 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -3054,7 +3054,7 @@ found
true
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3067,7 +3067,7 @@ and c17= @arg00 ;
found
true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index bf80514906b..ac9c8ae55ff 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -2990,7 +2990,7 @@ found
true
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3003,7 +3003,7 @@ and c17= @arg00 ;
found
true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -6004,7 +6004,7 @@ found
true
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -6017,7 +6017,7 @@ and c17= @arg00 ;
found
true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index d5a1c0b2451..09c7b92f51b 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -394,13 +394,13 @@ EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 43 NULL 2 Using where; Using index
Warnings:
-Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = 20020803)
+Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = _latin1'2002-08-03')
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 Using where; Using index
Warnings:
-Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = 20020803)) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
+Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = _latin1'2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
date
2002-08-03
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 7caa23d330d..42f97a6d53b 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -192,3 +192,42 @@ CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMA
SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6))
101112.098700
+create table t1 (f1 date, f2 datetime, f3 timestamp);
+insert into t1(f1) values(curdate());
+select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1;
+curdate() < now() f1 < now() cast(f1 as date) < now()
+1 1 1
+delete from t1;
+insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
+insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
+insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
+insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
+insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
+select f1, f3 from t1 where f1 >= '2001-02-05 00:00:00' and f3 <= '2001-04-15';
+f1 f3
+2001-02-05 2001-02-05 01:01:01
+2001-03-10 2001-03-10 01:01:01
+2001-04-15 2001-04-15 00:00:00
+select f1, f3 from t1 where f1 >= '2001-2-5 0:0:0' and f2 <= '2001-4-15';
+f1 f3
+2001-02-05 2001-02-05 01:01:01
+2001-03-10 2001-03-10 01:01:01
+2001-04-15 2001-04-15 00:00:00
+select f1, f2 from t1 where if(1, f1, 0) >= f2;
+f1 f2
+2001-02-05 2001-02-05 00:00:00
+2001-03-10 2001-03-09 01:01:01
+2001-04-15 2001-04-15 00:00:00
+select 1 from dual where cast('2001-1-1 2:3:4' as date) = cast('2001-01-01' as datetime);
+1
+1
+select f1, f2, UNIX_TIMESTAMP(f2), UNIX_TIMESTAMP(f1),
+f1 > f2, f1 = f2, f1 < f2
+from t1;
+f1 f2 UNIX_TIMESTAMP(f2) UNIX_TIMESTAMP(f1) f1 > f2 f1 = f2 f1 < f2
+2001-01-01 2001-01-01 01:01:01 978300061 978296400 0 0 1
+2001-02-05 2001-02-05 00:00:00 981320400 981320400 0 1 0
+2001-03-10 2001-03-09 01:01:01 984088861 984171600 1 0 0
+2001-04-15 2001-04-15 00:00:00 987282000 987282000 0 1 0
+2001-05-20 2001-05-20 01:01:01 990309661 990306000 0 0 1
+drop table t1;
diff --git a/mysql-test/t/bdb_notembedded.test b/mysql-test/t/bdb_notembedded.test
new file mode 100644
index 00000000000..24e64ebbfb2
--- /dev/null
+++ b/mysql-test/t/bdb_notembedded.test
@@ -0,0 +1,38 @@
+-- source include/not_embedded.inc
+-- source include/have_bdb.inc
+
+#
+# Bug #16206: Superfluous COMMIT event in binlog when updating BDB in autocommit mode
+#
+set autocommit=1;
+
+let $VERSION=`select version()`;
+
+reset master;
+create table bug16206 (a int);
+insert into bug16206 values(1);
+start transaction;
+insert into bug16206 values(2);
+commit;
+--replace_result $VERSION VERSION
+--replace_column 1 f 2 n 5 n
+show binlog events;
+drop table bug16206;
+
+reset master;
+create table bug16206 (a int) engine= bdb;
+insert into bug16206 values(0);
+insert into bug16206 values(1);
+start transaction;
+insert into bug16206 values(2);
+commit;
+insert into bug16206 values(3);
+--replace_result $VERSION VERSION
+--replace_column 1 f 2 n 5 n
+show binlog events;
+drop table bug16206;
+
+set autocommit=0;
+
+
+--echo End of 5.0 tests
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index 3d04eb85cf3..69a19f45411 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -141,3 +141,23 @@ SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6));
SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6));
SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
+#
+# Bug#27590: Wrong DATE/DATETIME comparison.
+#
+create table t1 (f1 date, f2 datetime, f3 timestamp);
+insert into t1(f1) values(curdate());
+select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1;
+delete from t1;
+insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
+insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
+insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
+insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
+insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
+select f1, f3 from t1 where f1 >= '2001-02-05 00:00:00' and f3 <= '2001-04-15';
+select f1, f3 from t1 where f1 >= '2001-2-5 0:0:0' and f2 <= '2001-4-15';
+select f1, f2 from t1 where if(1, f1, 0) >= f2;
+select 1 from dual where cast('2001-1-1 2:3:4' as date) = cast('2001-01-01' as datetime);
+select f1, f2, UNIX_TIMESTAMP(f2), UNIX_TIMESTAMP(f1),
+ f1 > f2, f1 = f2, f1 < f2
+ from t1;
+drop table t1;
diff --git a/sql/item.cc b/sql/item.cc
index 8568a44c547..c28771ef382 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4166,6 +4166,21 @@ enum_field_types Item::field_type() const
}
+bool Item::is_datetime()
+{
+ switch (field_type())
+ {
+ case MYSQL_TYPE_DATE:
+ case MYSQL_TYPE_DATETIME:
+ case MYSQL_TYPE_TIMESTAMP:
+ return TRUE;
+ default:
+ break;
+ }
+ return FALSE;
+}
+
+
/*
Create a field to hold a string value from an item
@@ -6123,6 +6138,14 @@ void Item_cache_int::store(Item *item)
}
+void Item_cache_int::store(Item *item, longlong val_arg)
+{
+ value= val_arg;
+ null_value= item->null_value;
+ unsigned_flag= item->unsigned_flag;
+}
+
+
String *Item_cache_int::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
diff --git a/sql/item.h b/sql/item.h
index 2c4943bea6e..06f20dde8f8 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -847,6 +847,7 @@ public:
representation is more precise than the string one).
*/
virtual bool result_as_longlong() { return FALSE; }
+ bool is_datetime();
};
@@ -2406,11 +2407,13 @@ public:
Item_cache_int(): Item_cache(), value(0) {}
void store(Item *item);
+ void store(Item *item, longlong val_arg);
double val_real() { DBUG_ASSERT(fixed == 1); return (double) value; }
longlong val_int() { DBUG_ASSERT(fixed == 1); return value; }
String* val_str(String *str);
my_decimal *val_decimal(my_decimal *);
enum Item_result result_type() const { return INT_RESULT; }
+ bool result_as_longlong() { return TRUE; }
};
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index ac1adc235c3..4d258ca1e6e 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -326,7 +326,9 @@ void Item_bool_func2::fix_length_and_dec()
if (arg_real_item->type() == FIELD_ITEM)
{
Field *field=((Item_field*) arg_real_item)->field;
- if (field->can_be_compared_as_longlong())
+ if (field->can_be_compared_as_longlong() &&
+ !(arg_real_item->is_datetime() &&
+ args[1]->result_type() == STRING_RESULT))
{
if (convert_constant_item(thd, field,&args[1]))
{
@@ -341,7 +343,9 @@ void Item_bool_func2::fix_length_and_dec()
if (arg_real_item->type() == FIELD_ITEM)
{
Field *field=((Item_field*) arg_real_item)->field;
- if (field->can_be_compared_as_longlong())
+ if (field->can_be_compared_as_longlong() &&
+ !(arg_real_item->is_datetime() &&
+ args[0]->result_type() == STRING_RESULT))
{
if (convert_constant_item(thd, field,&args[0]))
{
@@ -460,6 +464,316 @@ int Arg_comparator::set_compare_func(Item_bool_func2 *item, Item_result type)
}
+/*
+ Convert date provided in a string to the int representation.
+
+ SYNOPSIS
+ get_date_from_str()
+ thd Thread handle
+ str a string to convert
+ warn_type type of the timestamp for issuing the warning
+ warn_name field name for issuing the warning
+ error_arg [out] TRUE if string isn't a DATETIME or clipping occur
+
+ DESCRIPTION
+ Convert date provided in the string str to the int representation.
+ if the string contains wrong date or doesn't contain it at all
+ then the warning is issued and TRUE returned in the error_arg argument.
+ The warn_type and the warn_name arguments are used as the name and the
+ type of the field when issuing the warning.
+
+ RETURN
+ converted value.
+*/
+
+static ulonglong
+get_date_from_str(THD *thd, String *str, timestamp_type warn_type,
+ char *warn_name, bool *error_arg)
+{
+ ulonglong value;
+ int error;
+ MYSQL_TIME l_time;
+ enum_mysql_timestamp_type ret;
+ *error_arg= TRUE;
+
+ ret= str_to_datetime(str->ptr(), str->length(), &l_time,
+ (TIME_FUZZY_DATE | MODE_INVALID_DATES |
+ (thd->variables.sql_mode &
+ (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE))),
+ &error);
+ if ((ret == MYSQL_TIMESTAMP_DATETIME || ret == MYSQL_TIMESTAMP_DATE))
+ {
+ value= TIME_to_ulonglong_datetime(&l_time);
+ *error_arg= FALSE;
+ }
+
+ if (error || *error_arg)
+ {
+ make_truncated_value_warning(thd, str->ptr(), str->length(), warn_type,
+ warn_name);
+ *error_arg= TRUE;
+ }
+ return value;
+}
+
+
+/*
+ Check whether compare_datetime() can be used to compare items.
+
+ SYNOPSIS
+ Arg_comparator::can_compare_as_dates()
+ a, b [in] items to be compared
+ const_value [out] converted value of the string constant, if any
+
+ DESCRIPTION
+ Check several cases when the DATE/DATETIME comparator should be used.
+ The following cases are checked:
+ 1. Both a and b is a DATE/DATETIME field/function returning string or
+ int result.
+ 2. Only a or b is a DATE/DATETIME field/function returning string or
+ int result and the other item (b or a) is an item with string result.
+ If the second item is a constant one then it's checked to be
+ convertible to the DATE/DATETIME type. If the constant can't be
+ converted to a DATE/DATETIME then the compare_datetime() comparator
+ isn't used and the warning about wrong DATE/DATETIME value is issued.
+ In all other cases (date-[int|real|decimal]/[int|real|decimal]-date)
+ the comparison is handled by other comparators.
+ If the datetime comparator can be used and one the operands of the
+ comparison is a string constant that was successfully converted to a
+ DATE/DATETIME type then the result of the conversion is returned in the
+ const_value if it is provided. If there is no constant or
+ compare_datetime() isn't applicable then the *const_value remains
+ unchanged.
+
+ RETURN
+ the found type of date comparison
+*/
+
+enum Arg_comparator::enum_date_cmp_type
+Arg_comparator::can_compare_as_dates(Item *a, Item *b, ulonglong *const_value)
+{
+ enum enum_date_cmp_type cmp_type= CMP_DATE_DFLT;
+ Item *str_arg= 0, *date_arg= 0;
+
+ if (a->type() == Item::ROW_ITEM || b->type() == Item::ROW_ITEM)
+ return CMP_DATE_DFLT;
+
+ if (a->is_datetime())
+ {
+ if (b->is_datetime())
+ cmp_type= CMP_DATE_WITH_DATE;
+ else if (b->result_type() == STRING_RESULT)
+ {
+ cmp_type= CMP_DATE_WITH_STR;
+ date_arg= a;
+ str_arg= b;
+ }
+ }
+ else if (b->is_datetime() && a->result_type() == STRING_RESULT)
+ {
+ cmp_type= CMP_STR_WITH_DATE;
+ date_arg= b;
+ str_arg= a;
+ }
+
+ if (cmp_type != CMP_DATE_DFLT)
+ {
+ if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item())
+ {
+ THD *thd= current_thd;
+ ulonglong value;
+ bool error;
+ String tmp, *str_val= 0;
+ timestamp_type t_type= (date_arg->field_type() == MYSQL_TYPE_DATE ?
+ MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME);
+
+ str_val= str_arg->val_str(&tmp);
+ if (str_arg->null_value)
+ return CMP_DATE_DFLT;
+ value= get_date_from_str(thd, str_val, t_type, date_arg->name, &error);
+ if (error)
+ return CMP_DATE_DFLT;
+ if (const_value)
+ *const_value= value;
+ }
+ }
+ return cmp_type;
+}
+
+
+int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg,
+ Item **a1, Item **a2,
+ Item_result type)
+{
+ enum enum_date_cmp_type cmp_type;
+ ulonglong const_value;
+ a= a1;
+ b= a2;
+
+ if ((cmp_type= can_compare_as_dates(*a, *b, &const_value)))
+ {
+ thd= current_thd;
+ owner= owner_arg;
+ a_type= (*a)->field_type();
+ b_type= (*b)->field_type();
+ a_cache= 0;
+ b_cache= 0;
+
+ if (cmp_type != CMP_DATE_WITH_DATE &&
+ ((*b)->const_item() || (*a)->const_item()))
+ {
+ Item_cache_int *cache= new Item_cache_int();
+ /* Mark the cache as non-const to prevent re-caching. */
+ cache->set_used_tables(1);
+ if (!(*a)->is_datetime())
+ {
+ cache->store((*a), const_value);
+ a_cache= cache;
+ a= (Item **)&a_cache;
+ }
+ else
+ {
+ cache->store((*b), const_value);
+ b_cache= cache;
+ b= (Item **)&b_cache;
+ }
+ }
+ is_nulls_eq= owner->functype() == Item_func::EQUAL_FUNC;
+ func= &Arg_comparator::compare_datetime;
+ return 0;
+ }
+ return set_compare_func(owner_arg, type);
+}
+
+
+/*
+ Retrieves correct DATETIME value from given item.
+
+ SYNOPSIS
+ get_datetime_value()
+ thd thread handle
+ item_arg [in/out] item to retrieve DATETIME value from
+ cache_arg [in/out] pointer to place to store the caching item to
+ warn_item [in] item for issuing the conversion warning
+ is_null [out] TRUE <=> the item_arg is null
+
+ DESCRIPTION
+ Retrieves the correct DATETIME value from given item for comparison by the
+ compare_datetime() function.
+ If item's result can be compared as longlong then its int value is used
+ and its string value is used otherwise. Strings are always parsed and
+ converted to int values by the get_date_from_str() function.
+ This allows us to compare correctly string dates with missed insignificant
+ zeros. If an item is a constant one then its value is cached and it isn't
+ get parsed again. An Item_cache_int object is used for caching values. It
+ seamlessly substitutes the original item. The cache item is marked as
+ non-constant to prevent re-caching it again. In order to compare
+ correctly DATE and DATETIME items the result of the former are treated as
+ a DATETIME with zero time (00:00:00).
+
+ RETURN
+ obtained value
+*/
+
+static ulonglong
+get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
+ Item *warn_item, bool *is_null)
+{
+ ulonglong value;
+ String buf, *str= 0;
+ Item *item= **item_arg;
+
+ if (item->result_as_longlong())
+ {
+ value= item->val_int();
+ *is_null= item->null_value;
+ if (item->field_type() == MYSQL_TYPE_DATE)
+ value*= 1000000L;
+ }
+ else
+ {
+ str= item->val_str(&buf);
+ *is_null= item->null_value;
+ }
+ if (*is_null)
+ return -1;
+ /*
+ Convert strings to the integer DATE/DATETIME representation.
+ Even if both dates provided in strings we can't compare them directly as
+ strings as there is no warranty that they are correct and do not miss
+ some insignificant zeros.
+ */
+ if (str)
+ {
+ bool error;
+ enum_field_types f_type= warn_item->field_type();
+ timestamp_type t_type= f_type ==
+ MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME;
+ value= get_date_from_str(thd, str, t_type, warn_item->name, &error);
+ }
+ if (item->const_item())
+ {
+ Item_cache_int *cache= new Item_cache_int();
+ /* Mark the cache as non-const to prevent re-caching. */
+ cache->set_used_tables(1);
+ cache->store(item, value);
+ *cache_arg= cache;
+ *item_arg= cache_arg;
+ }
+ return value;
+}
+
+/*
+ Compare items values as dates.
+
+ SYNOPSIS
+ Arg_comparator::compare_datetime()
+
+ DESCRIPTION
+ Compare items values as DATE/DATETIME for both EQUAL_FUNC and from other
+ comparison functions. The correct DATETIME values are obtained
+ with help of the get_datetime_value() function.
+
+ RETURN
+ If is_nulls_eq is TRUE:
+ 1 if items are equal or both are null
+ 0 otherwise
+ If is_nulls_eq is FALSE:
+ -1 a < b or one of items is null
+ 0 a == b
+ 1 a > b
+*/
+
+int Arg_comparator::compare_datetime()
+{
+ bool is_null= FALSE;
+ ulonglong a_value, b_value;
+
+ /* Get DATE/DATETIME value of the 'a' item. */
+ a_value= get_datetime_value(thd, &a, &a_cache, *b, &is_null);
+ if (!is_nulls_eq && is_null)
+ {
+ owner->null_value= 1;
+ return -1;
+ }
+
+ /* Get DATE/DATETIME value of the 'b' item. */
+ b_value= get_datetime_value(thd, &b, &b_cache, *a, &is_null);
+ if (is_null)
+ {
+ owner->null_value= is_nulls_eq ? 0 : 1;
+ return is_nulls_eq ? 1 : -1;
+ }
+
+ owner->null_value= 0;
+
+ /* Compare values. */
+ if (is_nulls_eq)
+ return (a_value == b_value);
+ return a_value < b_value ? -1 : (a_value > b_value ? 1 : 0);
+}
+
+
int Arg_comparator::compare_string()
{
String *res1,*res2;
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 0c4a62aaa24..ce4d470a34a 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -35,12 +35,19 @@ class Arg_comparator: public Sql_alloc
Item_bool_func2 *owner;
Arg_comparator *comparators; // used only for compare_row()
double precision;
-
+ /* Fields used in DATE/DATETIME comparison. */
+ THD *thd;
+ enum_field_types a_type, b_type; // Types of a and b items
+ Item *a_cache, *b_cache; // Cached values of a and b items
+ bool is_nulls_eq; // TRUE <=> compare for the EQUAL_FUNC
+ enum enum_date_cmp_type { CMP_DATE_DFLT= 0, CMP_DATE_WITH_DATE,
+ CMP_DATE_WITH_STR, CMP_STR_WITH_DATE };
public:
DTCollation cmp_collation;
- Arg_comparator() {};
- Arg_comparator(Item **a1, Item **a2): a(a1), b(a2) {};
+ Arg_comparator(): thd(0), a_cache(0), b_cache(0) {};
+ Arg_comparator(Item **a1, Item **a2): a(a1), b(a2), thd(0),
+ a_cache(0), b_cache(0) {};
int set_compare_func(Item_bool_func2 *owner, Item_result type);
inline int set_compare_func(Item_bool_func2 *owner_arg)
@@ -48,14 +55,10 @@ public:
return set_compare_func(owner_arg, item_cmp_type((*a)->result_type(),
(*b)->result_type()));
}
- inline int set_cmp_func(Item_bool_func2 *owner_arg,
+ int set_cmp_func(Item_bool_func2 *owner_arg,
Item **a1, Item **a2,
- Item_result type)
- {
- a= a1;
- b= a2;
- return set_compare_func(owner_arg, type);
- }
+ Item_result type);
+
inline int set_cmp_func(Item_bool_func2 *owner_arg,
Item **a1, Item **a2)
{
@@ -83,6 +86,10 @@ public:
int compare_e_row(); // compare args[0] & args[1]
int compare_real_fixed();
int compare_e_real_fixed();
+ int compare_datetime(); // compare args[0] & args[1] as DATETIMEs
+
+ static enum enum_date_cmp_type can_compare_as_dates(Item *a, Item *b,
+ ulonglong *const_val_arg);
static arg_cmp_func comparator_matrix [5][2];
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 79ae4ade8ab..86dec87b9fb 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8621,17 +8621,13 @@ static bool
test_if_equality_guarantees_uniqueness(Item *l, Item *r)
{
return r->const_item() &&
- /* elements must be of the same result type */
- (r->result_type() == l->result_type() ||
- /* or dates compared to longs */
- (((l->type() == Item::FIELD_ITEM &&
- ((Item_field *)l)->field->can_be_compared_as_longlong()) ||
- (l->type() == Item::FUNC_ITEM &&
- ((Item_func *)l)->result_as_longlong())) &&
- r->result_type() == INT_RESULT))
- /* and must have the same collation if compared as strings */
- && (l->result_type() != STRING_RESULT ||
- l->collation.collation == r->collation.collation);
+ /* elements must be compared as dates */
+ (Arg_comparator::can_compare_as_dates(l, r, 0) ||
+ /* or of the same result type */
+ (r->result_type() == l->result_type() &&
+ /* and must have the same collation if compared as strings */
+ (l->result_type() != STRING_RESULT ||
+ l->collation.collation == r->collation.collation)));
}
/*
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index d64ec08a71d..dffd7517e78 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -8852,12 +8852,14 @@ static void test_ts()
mysql_free_result(prep_res);
mysql_stmt_close(stmt);
-
+ char queries [3][60]= {"SELECT a, b, c FROM test_ts WHERE %c=?",
+ "SELECT a, b, c FROM test_ts WHERE %c=?",
+ "SELECT a, b, c FROM test_ts WHERE %c=CAST(? AS DATE)"};
for (name= 'a'; field_count--; name++)
{
int row_count= 0;
- sprintf(query, "SELECT a, b, c FROM test_ts WHERE %c=?", name);
+ sprintf(query, queries[field_count], name);
if (!opt_silent)
fprintf(stdout, "\n %s", query);
View Lorry Controller Status