Bug#27590: Wrong DATE/DATETIME comparison.

DATE and DATETIME can be compared either as strings or as int. Both
methods have their disadvantages. Strings can contain valid DATETIME value
but have insignificant zeros omitted thus became non-comparable with
other DATETIME strings. The comparison as int usually will require conversion
from the string representation and the automatic conversion in most cases is
carried out in a wrong way thus producing wrong comparison result. Another
problem occurs when one tries to compare DATE field with a DATETIME constant.
The constant is converted to DATE losing its precision i.e. losing time part.

This fix addresses the problems described above by adding a special
DATE/DATETIME comparator. The comparator correctly converts DATE/DATETIME
string values to int when it's necessary, adds zero time part (00:00:00)
to DATE values to compare them correctly to DATETIME values. Due to correct
conversion malformed DATETIME string values are correctly compared to other
DATE/DATETIME values.

As of this patch a DATE value equals to DATETIME value with zero time part.
For example '2001-01-01' equals to '2001-01-01 00:00:00'.

The compare_datetime() function is added to the Arg_comparator class.
It implements the correct comparator for DATE/DATETIME values.
Two supplementary functions called get_date_from_str() and get_datetime_value()
are added. The first one extracts DATE/DATETIME value from a string and the
second one retrieves the correct DATE/DATETIME value from an item.
The new Arg_comparator::can_compare_as_dates() function is added and used
to check whether two given items can be compared by the compare_datetime()
comparator.
Two caching variables were added to the Arg_comparator class to speedup the
DATE/DATETIME comparison.
One more store() method was added to the Item_cache_int class to cache int
values.
The new is_datetime() function was added to the Item class. It indicates
whether the item returns a DATE/DATETIME value.


sql/item.cc:
  Bug#27590: Wrong DATE/DATETIME comparison.
  One more store() method was added to the Item_cache_int class to cache int
  values.
  The new is_datetime() function was added to the Item class. It indicates
  whether the item returns a DATE/DATETIME value.
sql/item.h:
  Bug#27590: Wrong DATE/DATETIME comparison.
  One more store() method was added to the Item_cache_int class to cache int
  values.
  The new is_datetime() function was added to the Item class. It indicates
  whether the item returns a DATE/DATETIME value.
sql/item_cmpfunc.cc:
  Bug#27590: Wrong DATE/DATETIME comparison.
  The compare_datetime() function is added to the Arg_comparator class.
  It implements the correct comparator for DATE/DATETIME values.
  Two supplementary functions called get_date_from_str() and get_datetime_value()
  are added. The first one extracts DATE/DATETIME value from a string and the
  second one retrieves the correct DATE/DATETIME value from an item.
  The new Arg_comparator::can_compare_as_dates() function is added and used
  to check whether two given items can be compared by the compare_datetime()
  comparator.
sql/item_cmpfunc.h:
  Bug#27590: Wrong DATE/DATETIME comparison.
  The compare_datetime() function is added to the Arg_comparator class.
  It implements the correct comparator for DATE/DATETIME values.
  Two supplementary functions called get_date_from_str() and get_datetime_value()
  are added. The first one extracts DATE/DATETIME value from a string and the
  second one retrieves the correct DATE/DATETIME value from an item.
  The new Arg_comparator::can_compare_as_dates() function is added and used
  to check whether two given items can be compared by the compare_datetime()
  comparator.
  Two caching variables were added to the Arg_comparator class to speedup the
  DATE/DATETIME comparison.
mysql-test/include/ps_conv.inc:
  Test case adjusted after fix for bug#27590.
mysql-test/r/distinct.result:
  Test cases results are corrected after fix for bug#27590.
sql/sql_select.cc:
  Bug#27590: Wrong DATE/DATETIME comparison.
  The test_if_equality_guarantees_uniqueness() function now uses
  Arg_comparator::can_compare_as_dates() to detect comparable DATE/DATETIME items.
mysql-test/r/ps_2myisam.result:
  The result of the adjusted test case after fix for bug#27590.
mysql-test/r/ps_3innodb.result:
  The result of the adjusted test case after fix for bug#27590.
mysql-test/r/ps_4heap.result:
  The result of the adjusted test case after fix for bug#27590.
mysql-test/r/ps_5merge.result:
  The result of the adjusted test case after fix for bug#27590.
mysql-test/r/subselect.result:
  Test cases results are corrected after fix for bug#27590.
mysql-test/r/type_datetime.result:
  Added a test case for the bug#27590: Wrong DATE/DATETIME comparison.
mysql-test/t/type_datetime.test:
  Added a test case for the bug#27590: Wrong DATE/DATETIME comparison.
tests/mysql_client_test.c:
  Test case adjusted after fix for bug#27590.

1 files changed, 2 insertions, 2 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