summaryrefslogtreecommitdiff
path: root/mysql-test/main/func_debug.result
diff options
context:
space:
mode:
authorAlexander Barkov <bar@mariadb.com>2018-06-08 12:36:42 +0400
committerAlexander Barkov <bar@mariadb.com>2018-06-08 12:36:42 +0400
commit9043dd7a2d380b26349bc23e904d44e9ce634cef (patch)
treeb2580defb58f21ecd88d95fc528ebfd2fac531f5 /mysql-test/main/func_debug.result
parent054412598b167d246826f6b33bee8a6247308459 (diff)
downloadmariadb-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/func_debug.result')
-rw-r--r--mysql-test/main/func_debug.result109
1 files changed, 87 insertions, 22 deletions
diff --git a/mysql-test/main/func_debug.result b/mysql-test/main/func_debug.result
index a394bf3c334..6a33557dca3 100644
--- a/mysql-test/main/func_debug.result
+++ b/mysql-test/main/func_debug.result
@@ -1665,94 +1665,159 @@ SELECT * FROM t1 WHERE a BETWEEN 1 AND 1.0;
a
1
Warnings:
-Note 1105 bin_eq=0 a=int'1' b=decimal'1.0'
+Note 1105 bin_eq=0 a=(int)1 b=(decimal)1.0
SELECT * FROM t1 WHERE a BETWEEN 1 AND 1;
a
1
Warnings:
-Note 1105 bin_eq=1 a=int'1' b=int'1'
+Note 1105 bin_eq=1 a=(int)1 b=(int)1
SELECT * FROM t1 WHERE a BETWEEN 0 AND 1;
a
1
Warnings:
-Note 1105 bin_eq=0 a=int'0' b=int'1'
+Note 1105 bin_eq=0 a=(int)0 b=(int)1
SELECT * FROM t1 WHERE a BETWEEN 0 AND -1;
a
Warnings:
-Note 1105 bin_eq=0 a=int'0' b=int''
+Note 1105 bin_eq=0 a=(int)0 b=(int)-1
SELECT * FROM t1 WHERE a BETWEEN -1 AND -1;
a
Warnings:
-Note 1105 bin_eq=1 a=int'' b=int''
+Note 1105 bin_eq=1 a=(int)-1 b=(int)-1
SELECT * FROM t1 WHERE a BETWEEN -0000000000000001 AND -1;
a
Warnings:
-Note 1105 bin_eq=1 a=bigint'' b=int''
+Note 1105 bin_eq=1 a=(bigint)-1 b=(int)-1
SELECT * FROM t1 WHERE a BETWEEN -1 AND 18446744073709551615;
a
1
2
3
Warnings:
-Note 1105 bin_eq=0 a=int'' b=bigint'18446744073709551615'
+Note 1105 bin_eq=0 a=(int)-1 b=(bigint)18446744073709551615
SELECT * FROM t1 WHERE a BETWEEN -1 AND 18446744073709551616;
a
1
2
3
Warnings:
-Note 1105 bin_eq=0 a=int'' b=decimal'18446744073709551616'
+Note 1105 bin_eq=0 a=(int)-1 b=(decimal)18446744073709551616
SELECT * FROM t1 WHERE a BETWEEN 1e2 AND 100e0;
a
Warnings:
-Note 1105 bin_eq=1 a=double'1e2' b=double'100e0'
+Note 1105 bin_eq=1 a=(double)1e2 b=(double)100e0
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN 1 AND ?' USING 1;
a
1
Warnings:
-Note 1105 bin_eq=1 a=int'1' b=int'?'
+Note 1105 bin_eq=1 a=(int)1 b=(int)1
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN -1 AND ?' USING 18446744073709551615;
a
1
2
3
Warnings:
-Note 1105 bin_eq=0 a=int'' b=bigint'?'
+Note 1105 bin_eq=0 a=(int)-1 b=(bigint)18446744073709551615
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN -1 AND ?' USING 18446744073709551616;
a
1
2
3
Warnings:
-Note 1105 bin_eq=0 a=int'' b=decimal'?'
+Note 1105 bin_eq=0 a=(int)-1 b=(decimal)18446744073709551616
DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('0'),('1'),('2');
+SELECT * FROM t1 WHERE a BETWEEN '0' AND '0';
+a
+0
+Warnings:
+Note 1105 eq=1 a=(varchar)'0' b=(varchar)'0'
+SELECT * FROM t1 WHERE a BETWEEN '0' AND ' 0';
+a
+Warnings:
+Note 1105 eq=0 a=(varchar)'0' b=(varchar)' 0'
+SELECT * FROM t1 WHERE a BETWEEN '0' AND '0 ';
+a
+0
+Warnings:
+Note 1105 eq=1 a=(varchar)'0' b=(varchar)'0 '
+DROP TABLE t1;
+SET SESSION debug_dbug="-d,Item_basic_value";
+#
+# MDEV-11362 True condition elimination does not work for DECIMAL and temporal dynamic SQL parameters
+#
+SET SESSION debug_dbug="+d,Item_basic_value";
CREATE TABLE t1 (a DECIMAL(10,3));
INSERT INTO t1 VALUES (1),(2),(3);
SELECT * FROM t1 WHERE a BETWEEN 1.0 AND 1.0;
a
1.000
+Warnings:
+Note 1105 bin_eq=1 a=(decimal)1.0 b=(decimal)1.0
+EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN 1.0 AND ?' USING 1.0;
+a
+1.000
+Warnings:
+Note 1105 bin_eq=1 a=(decimal)1.0 b=(decimal)1.0
DROP TABLE t1;
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE a BETWEEN TIME'00:00:00' AND TIME'00:00:00';
a
00:00:00
+Warnings:
+Note 1105 bin_eq=1 a=(time)TIME'00:00:00' b=(time)TIME'00:00:00'
+EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN TIME''00:00:00'' AND ?' USING TIME'00:00:00';
+a
+00:00:00
+Warnings:
+Note 1105 bin_eq=1 a=(time)TIME'00:00:00' b=(time)TIME'00:00:00'
DROP TABLE t1;
-CREATE TABLE t1 (a VARCHAR(10));
-INSERT INTO t1 VALUES ('0'),('1'),('2');
-SELECT * FROM t1 WHERE a BETWEEN '0' AND '0';
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02');
+SELECT * FROM t1 WHERE a BETWEEN DATE'2001-01-01' AND DATE'2001-01-01';
a
-0
+2001-01-01
Warnings:
-Note 1105 eq=1 a=varchar'0' b=varchar'0'
-SELECT * FROM t1 WHERE a BETWEEN '0' AND ' 0';
+Note 1105 bin_eq=1 a=(date)DATE'2001-01-01' b=(date)DATE'2001-01-01'
+EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN DATE''2001-01-01'' AND ?' USING DATE'2001-01-01';
a
+2001-01-01
Warnings:
-Note 1105 eq=0 a=varchar'0' b=varchar'0'
-SELECT * FROM t1 WHERE a BETWEEN '0' AND '0 ';
+Note 1105 bin_eq=1 a=(date)DATE'2001-01-01' b=(date)DATE'2001-01-01'
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:00');
+SELECT * FROM t1 WHERE a BETWEEN TIMESTAMP'2001-01-01 00:00:00' AND TIMESTAMP'2001-01-01 00:00:00';
+a
+2001-01-01 00:00:00
+2001-01-01 00:00:00
+Warnings:
+Note 1105 bin_eq=1 a=(datetime)TIMESTAMP'2001-01-01 00:00:00' b=(datetime)TIMESTAMP'2001-01-01 00:00:00'
+EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN TIMESTAMP''2001-01-01 00:00:00'' AND ?' USING TIMESTAMP'2001-01-01 00:00:00';
+a
+2001-01-01 00:00:00
+2001-01-01 00:00:00
+Warnings:
+Note 1105 bin_eq=1 a=(datetime)TIMESTAMP'2001-01-01 00:00:00' b=(datetime)TIMESTAMP'2001-01-01 00:00:00'
+DROP TABLE t1;
+SET SESSION debug_dbug="-d,Item_basic_value";
+#
+# MDEV-16426 Optimizer erroneously treats equal constants of different formats as same
+#
+SET SESSION debug_dbug="+d,Item_basic_value";
+CREATE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('a'),('b'),('c');
+SELECT * FROM t1 WHERE a BETWEEN 'a' AND 0x61;
+a
+a
+Warnings:
+Note 1105 eq=0 a=(varchar)'a' b=(hex_hybrid)0x61
+EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN ''a'' AND ?' USING 0x61;
+a
a
-0
Warnings:
-Note 1105 eq=1 a=varchar'0' b=varchar'0 '
+Note 1105 eq=0 a=(varchar)'a' b=(hex_hybrid)'a'
DROP TABLE t1;
SET SESSION debug_dbug="-d,Item_basic_value";