summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/select.result28
-rw-r--r--mysql-test/t/select.test23
-rw-r--r--sql/field.h4
-rw-r--r--sql/field_conv.cc15
-rw-r--r--sql/item.cc11
5 files changed, 66 insertions, 15 deletions
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index f3938fd6413..96af58789ec 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3728,3 +3728,31 @@ WHERE ID_better=1 AND ID1_with_null IS NULL AND
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where
DROP TABLE t1;
+CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));
+INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00");
+INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2;
+ANALYZE TABLE t2;
+Table Op Msg_type Msg_text
+test.t2 analyze status OK
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t1 range ts ts 4 NULL 1 Using where
+Warnings:
+Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+a ts a dt1 dt2
+30 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00
+Warnings:
+Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 19c7d742f5b..dfa3c1ac2a4 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -3207,3 +3207,26 @@ EXPLAIN SELECT * FROM t1
(ID2_with_null=1 OR ID2_with_null=2);
DROP TABLE t1;
+
+#
+# Bug #22344: InnoDB keys act strange on datetime vs timestamp comparison
+#
+CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));
+INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
+ANALYZE TABLE t1;
+
+CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00");
+INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2;
+ANALYZE TABLE t2;
+
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+ AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+ AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+ AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+ AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+
+DROP TABLE t1,t2;
diff --git a/sql/field.h b/sql/field.h
index 565342637ba..562991f681e 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -29,7 +29,7 @@
class Send_field;
class Protocol;
struct st_cache_field;
-void field_conv(Field *to,Field *from);
+int field_conv(Field *to,Field *from);
inline uint get_enum_pack_length(int elements)
{
@@ -1242,7 +1242,7 @@ public:
uint max_packed_col_length(uint max_length);
void free() { value.free(); }
inline void clear_temporary() { bzero((char*) &value,sizeof(value)); }
- friend void field_conv(Field *to,Field *from);
+ friend int field_conv(Field *to,Field *from);
uint size_of() const { return sizeof(*this); }
bool has_charset(void) const
{ return charset() == &my_charset_bin ? FALSE : TRUE; }
diff --git a/sql/field_conv.cc b/sql/field_conv.cc
index e5752708123..dbe58d804ad 100644
--- a/sql/field_conv.cc
+++ b/sql/field_conv.cc
@@ -679,7 +679,7 @@ void (*Copy_field::get_copy_func(Field *to,Field *from))(Copy_field*)
/* Simple quick field convert that is called on insert */
-void field_conv(Field *to,Field *from)
+int field_conv(Field *to,Field *from)
{
if (to->real_type() == from->real_type() &&
!(to->type() == FIELD_TYPE_BLOB && to->table->copy_blobs))
@@ -707,7 +707,7 @@ void field_conv(Field *to,Field *from)
if (to->ptr != from->ptr)
#endif
memcpy(to->ptr,from->ptr,to->pack_length());
- return;
+ return 0;
}
}
if (to->type() == FIELD_TYPE_BLOB)
@@ -723,8 +723,7 @@ void field_conv(Field *to,Field *from)
from->real_type() != MYSQL_TYPE_STRING &&
from->real_type() != MYSQL_TYPE_VARCHAR))
blob->value.copy();
- blob->store(blob->value.ptr(),blob->value.length(),from->charset());
- return;
+ return blob->store(blob->value.ptr(),blob->value.length(),from->charset());
}
if ((from->result_type() == STRING_RESULT &&
(to->result_type() == STRING_RESULT ||
@@ -741,15 +740,15 @@ void field_conv(Field *to,Field *from)
end with \0. Can be replaced with .ptr() when we have our own
string->double conversion.
*/
- to->store(result.c_ptr_quick(),result.length(),from->charset());
+ return to->store(result.c_ptr_quick(),result.length(),from->charset());
}
else if (from->result_type() == REAL_RESULT)
- to->store(from->val_real());
+ return to->store(from->val_real());
else if (from->result_type() == DECIMAL_RESULT)
{
my_decimal buff;
- to->store_decimal(from->val_decimal(&buff));
+ return to->store_decimal(from->val_decimal(&buff));
}
else
- to->store(from->val_int(), test(from->flags & UNSIGNED_FLAG));
+ return to->store(from->val_int(), test(from->flags & UNSIGNED_FLAG));
}
diff --git a/sql/item.cc b/sql/item.cc
index 9a55eb25e2c..6d0297f909e 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4245,18 +4245,19 @@ void Item_field::save_org_in_field(Field *to)
int Item_field::save_in_field(Field *to, bool no_conversions)
{
+ int res;
if (result_field->is_null())
{
null_value=1;
- return set_field_to_null_with_conversions(to, no_conversions);
+ res= set_field_to_null_with_conversions(to, no_conversions);
}
else
{
to->set_notnull();
- field_conv(to,result_field);
+ res= field_conv(to,result_field);
null_value=0;
}
- return 0;
+ return res;
}
@@ -5284,9 +5285,9 @@ int Item_ref::save_in_field(Field *to, bool no_conversions)
return set_field_to_null_with_conversions(to, no_conversions);
}
to->set_notnull();
- field_conv(to, result_field);
+ res= field_conv(to, result_field);
null_value= 0;
- return 0;
+ return res;
}
res= (*ref)->save_in_field(to, no_conversions);
null_value= (*ref)->null_value;
View Lorry Controller Status