MDEV-11361 Equal condition propagation does not work for DECIMAL and temporal dynamic SQL parameters

MDEV-16426 Optimizer erroneously treats equal constants of different formats as same
A cleanup for MDEV-14630: fixing a crash in Item_decimal::eq().

Problems:
- old implementations of Item_decimal::eq() and
  Item_temporal_literal::eq() were not symmetric
  with Item_param::eq(), this caused MDEV-11361.

- old implementations for DECIMAL and temporal data types
  did not take into account that in case when eq() is called
  with binary_cmp==true, {{eq()}} should check not only equality
  of the two values, but also equality if their decimal precision.
  This cuases MDEV-16426.

- Item_decimal::eq() crashes with "item" pointing
  to a non-DECIMAL value. Before MDEV-14630
  non-DECIMAL values were filtered out by the test:
    type() == item->type()
  as literals of different types had different type().
  After MDEV-14630 type() for literals of all data types return CONST_ITEM.
  This caused failures in tests:
    ./mtr engines/iuds.insert_number
    ./mtr --ps --embedded main.explain_slowquerylog
  (revealed by buildbot)

The essence of the fix:
Making literals and Item_param reuse the same code to avoid
asymmetries between Item_param::eq(Item_literal) and
Item_literal::eq(Item_param), now and in the future, and to
avoid code duplication between Item_literal and Item_param.
Adding tests for "decimals" for DECIMAL and temporal data types,
to treat constants of different scale as not equal when "binary_cmp"
is "true".

Details:
1. Adding a helper class Item_const to extract constant values from Items easier
2. Deriving Item_basic_value from Item_const
3. Joining Type_handler::Item_basic_value_eq() and Item_basic_value_bin_eq()
   into a single method with an extra "binary_cmp" argument
   (it looks simple this way) and renaming the new method to Item_const_eq().
   Modifying its implementations to operate with
   Item_const instead of Item_basic_value.
4. Adding a new class Type_handler_hex_hybrid,
   to handle hex constants like 0x616263.
5. Removing Item::VARBIN_ITEM and fixing Item_hex_constant to
   use type_handler_hex_hybrid instead of type_handler_varchar.
   Item_hex_hybrid::type() now returns CONST_ITEM, like all
   other literals do.
6. Move virtual methods Item::type_handler_for_system_time() and
   Item::cast_to_int_type_handler() from Item to Type_handler.
7. Removing Item_decimal::eq() and Item_temporal_literal::eq().
   These classes are now handled by the generic Item_basic_value::eq().
8. Implementing Type_handler_temporal_result::Item_const_eq()
   and Type_handler_decimal_result::Item_const_eq(),
   this fixes MDEV-11361.
9. Adding tests for "decimals" into
   Type_handler_decimal_result::Item_const_eq() and
   Type_handler_temporal_result::Item_const_eq()
   in case if "binary_cmp" is true.
   This fixes MDEV-16426.
10. Moving Item_cache out of Item_basic_value.
   They share nothing. It simplifies implementation
   of Item_basic_value::eq(). Deriving Item_cache
   directly from Item.

11. Adding class DbugStringItemTypeValue, which
    used Item::print() internally, and using
    in instead of the old debug printing code.
    This gives nicer output in func_debug.result.

Changes N5 and N6 do not directly relate to the bugs fixed,
but make the code fully symmetric across all literal types.
Without a new handler Type_handler_hex_hybrid we'd have
to keep two code branches (for regular literals and for
hex hybrid literals).

1 files changed, 21 insertions, 0 deletions

diff --git a/mysql-test/main/type_float.test b/mysql-test/main/type_float.test
index 2d7c4428507..3d0131b3bb0 100644
--- a/mysql-test/main/type_float.test
+++ b/mysql-test/main/type_float.test
@@ -551,3 +551,24 @@ DROP TABLE t1;
 --echo #
 --echo # End of 10.2 tests
 --echo #
+
+
+--echo #
+--echo # Start of 10.4 tests
+--echo #
+
+--echo #
+--echo # MDEV-11362 True condition elimination does not work for DECIMAL and temporal dynamic SQL parameters
+--echo #
+
+CREATE TABLE t1 (a DOUBLE);
+INSERT INTO t1 VALUES (1),(2),(3);
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1e0+a<=>1e0+a;
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1e0,1e0;
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>1e0+a' USING 1e0;
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1e0+a<=>?+a' USING 1e0;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.4 tests
+--echo #