diff options
author | Alexander Barkov <bar@mariadb.com> | 2018-06-08 12:36:42 +0400 |
---|---|---|
committer | Alexander Barkov <bar@mariadb.com> | 2018-06-08 12:36:42 +0400 |
commit | 9043dd7a2d380b26349bc23e904d44e9ce634cef (patch) | |
tree | b2580defb58f21ecd88d95fc528ebfd2fac531f5 /mysql-test/main/type_time.test | |
parent | 054412598b167d246826f6b33bee8a6247308459 (diff) | |
download | mariadb-git-9043dd7a2d380b26349bc23e904d44e9ce634cef.tar.gz |
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).
Diffstat (limited to 'mysql-test/main/type_time.test')
-rw-r--r-- | mysql-test/main/type_time.test | 47 |
1 files changed, 47 insertions, 0 deletions
diff --git a/mysql-test/main/type_time.test b/mysql-test/main/type_time.test index d8bb66fcd5c..17494cec04f 100644 --- a/mysql-test/main/type_time.test +++ b/mysql-test/main/type_time.test @@ -1290,3 +1290,50 @@ INSERT INTO t1 VALUES ('-838:00:04.1', 'yes'); EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-838:00:01.1','-838:00:02.1'); SELECT * FROM t1 WHERE a NOT IN ('-838:00:01.1','-838:00:02.1'); DROP TABLE t1; + +--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 TIME); +INSERT INTO t1 VALUES (1),(2),(3); +--echo # Equal values +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIME'10:20:30',a)<=>COALESCE(TIME'10:20:30',a); +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING TIME'10:20:30',TIME'10:20:30'; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIME''10:20:30'',a)<=>COALESCE(?,a)' USING TIME'10:20:30'; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(TIME''10:20:30'',a)' USING TIME'10:20:30'; +--echo # Not equal values +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIME'10:20:30',a)<=>COALESCE(TIME'10:20:31',a); +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING TIME'10:20:30',TIME'10:20:31'; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIME''10:20:30'',a)<=>COALESCE(?,a)' USING TIME'10:20:31'; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(TIME''10:20:30'',a)' USING TIME'10:20:31'; +DROP TABLE t1; + +--echo # +--echo # MDEV-16426 Optimizer erroneously treats equal constants of different formats as same +--echo # +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:00'),('00:00:01'),('00:00:02'); + +--echo Equal values +SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME'00:00:00.0',a))<=>LENGTH(COALESCE(TIME'00:00:00.0',a)); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME'00:00:00.0',a))<=>LENGTH(COALESCE(TIME'00:00:00.0',a)); +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME''00:00:00.0'',a))<=>LENGTH(COALESCE(?,a))' USING TIME'00:00:00.0'; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME''00:00:00.0'',a))<=>LENGTH(COALESCE(?,a))' USING TIME'00:00:00.0'; + +--echo Values with different formats +SELECT LENGTH(COALESCE(TIME'00:00:00.0',a)),LENGTH(COALESCE(TIME'00:00:00.00',a)) FROM t1; +SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME'00:00:00.0',a))<=>LENGTH(COALESCE(TIME'00:00:00.00',a)); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME'00:00:00.0',a))<=>LENGTH(COALESCE(TIME'00:00:00.00',a)); +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME''00:00:00.0'',a))<=>LENGTH(COALESCE(?,a))' USING TIME'00:00:00.00'; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME''00:00:00.0'',a))<=>LENGTH(COALESCE(?,a))' USING TIME'00:00:00.00'; + +DROP TABLE t1; + +--echo # +--echo # End of 10.4 tests +--echo # |