From 9043dd7a2d380b26349bc23e904d44e9ce634cef Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Fri, 8 Jun 2018 12:36:42 +0400 Subject: 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). --- mysql-test/main/type_datetime.result | 101 +++++++++++++++++++++++++++++++++++ 1 file changed, 101 insertions(+) (limited to 'mysql-test/main/type_datetime.result') diff --git a/mysql-test/main/type_datetime.result b/mysql-test/main/type_datetime.result index 74b761a2e8f..86dddc93a70 100644 --- a/mysql-test/main/type_datetime.result +++ b/mysql-test/main/type_datetime.result @@ -1301,3 +1301,104 @@ DROP TABLE t1; # # End of 10.3 tests # +# +# Start of 10.4 tests +# +# +# MDEV-11362 True condition elimination does not work for DECIMAL and temporal dynamic SQL parameters +# +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-02 00:00:00'),('2001-01-03 00:00:00'); +# Equal values +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIMESTAMP'2001:01:01 00:00:00',a)<=>COALESCE(TIMESTAMP'2001-01-01 00:00:00',a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING TIMESTAMP'2001-01-01 00:00:00',TIMESTAMP'2001-01-01 00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIMESTAMP''2001-01-01 00:00:00'',a)<=>COALESCE(?,a)' USING TIMESTAMP'2001-01-01 00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(TIMESTAMP''2001-01-01 00:00:00'',a)' USING TIMESTAMP'2001-01-01 00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 +# Not equal values +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIMESTAMP'2001:01:01 00:00:00',a)<=>COALESCE(TIMESTAMP'2001-01-01 00:00:01',a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(TIMESTAMP'2001-01-01 00:00:00',`test`.`t1`.`a`) <=> coalesce(TIMESTAMP'2001-01-01 00:00:01',`test`.`t1`.`a`) +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING TIMESTAMP'2001-01-01 00:00:00',TIMESTAMP'2001-01-01 00:00:01'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce((TIMESTAMP'2001-01-01 00:00:00'),`test`.`t1`.`a`) <=> coalesce((TIMESTAMP'2001-01-01 00:00:01'),`test`.`t1`.`a`) +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIMESTAMP''2001-01-01 00:00:00'',a)<=>COALESCE(?,a)' USING TIMESTAMP'2001-01-01 00:00:01'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(TIMESTAMP'2001-01-01 00:00:00',`test`.`t1`.`a`) <=> coalesce((TIMESTAMP'2001-01-01 00:00:01'),`test`.`t1`.`a`) +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(TIMESTAMP''2001-01-01 00:00:00'',a)' USING TIMESTAMP'2001-01-01 00:00:01'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce((TIMESTAMP'2001-01-01 00:00:01'),`test`.`t1`.`a`) <=> coalesce(TIMESTAMP'2001-01-01 00:00:00',`test`.`t1`.`a`) +DROP TABLE t1; +# +# MDEV-16426 Optimizer erroneously treats equal constants of different formats as same +# +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'),('2001-01-01 00:00:02'); +Equal values +SELECT * FROM t1 WHERE LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.0',a))<=>LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.0',a)); +a +2001-01-01 00:00:00 +2001-01-01 00:00:01 +2001-01-01 00:00:02 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.0',a))<=>LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.0',a)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME''00:00:00.0'',a))<=>LENGTH(COALESCE(?,a))' USING TIMESTAMP'2001-01-01 00:00:00.0'; +a +2001-01-01 00:00:00 +2001-01-01 00:00:01 +2001-01-01 00:00:02 +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME''00:00:00.0'',a))<=>LENGTH(COALESCE(?,a))' USING TIMESTAMP'2001-01-01 00:00:00.0'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where octet_length(coalesce(TIME'00:00:00.0',`test`.`t1`.`a`)) <=> octet_length(coalesce((TIMESTAMP'2001-01-01 00:00:00.0'),`test`.`t1`.`a`)) +Values with different formats +SELECT LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.0',a)),LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.00',a)) FROM t1; +LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.0',a)) LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.00',a)) +21 22 +21 22 +21 22 +SELECT * FROM t1 WHERE LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.0',a))<=>LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.00',a)); +a +EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.0',a))<=>LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.00',a)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where octet_length(coalesce(TIMESTAMP'2001-01-01 00:00:00.0',`test`.`t1`.`a`)) <=> octet_length(coalesce(TIMESTAMP'2001-01-01 00:00:00.00',`test`.`t1`.`a`)) +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME''00:00:00.0'',a))<=>LENGTH(COALESCE(?,a))' USING TIMESTAMP'2001-01-01 00:00:00.00'; +a +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME''00:00:00.0'',a))<=>LENGTH(COALESCE(?,a))' USING TIMESTAMP'2001-01-01 00:00:00.00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where octet_length(coalesce(TIME'00:00:00.0',`test`.`t1`.`a`)) <=> octet_length(coalesce((TIMESTAMP'2001-01-01 00:00:00.00'),`test`.`t1`.`a`)) +DROP TABLE t1; +# +# End of 10.4 tests +# -- cgit v1.2.1