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 | |
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')
-rw-r--r-- | mysql-test/main/func_debug.result | 109 | ||||
-rw-r--r-- | mysql-test/main/func_debug.test | 47 | ||||
-rw-r--r-- | mysql-test/main/gis.result | 24 | ||||
-rw-r--r-- | mysql-test/main/gis.test | 20 | ||||
-rw-r--r-- | mysql-test/main/type_date.result | 75 | ||||
-rw-r--r-- | mysql-test/main/type_date.test | 35 | ||||
-rw-r--r-- | mysql-test/main/type_datetime.result | 101 | ||||
-rw-r--r-- | mysql-test/main/type_datetime.test | 52 | ||||
-rw-r--r-- | mysql-test/main/type_decimal.result | 97 | ||||
-rw-r--r-- | mysql-test/main/type_decimal.test | 53 | ||||
-rw-r--r-- | mysql-test/main/type_float.result | 32 | ||||
-rw-r--r-- | mysql-test/main/type_float.test | 21 | ||||
-rw-r--r-- | mysql-test/main/type_hex_hybrid.result | 24 | ||||
-rw-r--r-- | mysql-test/main/type_hex_hybrid.test | 21 | ||||
-rw-r--r-- | mysql-test/main/type_int.result | 32 | ||||
-rw-r--r-- | mysql-test/main/type_int.test | 20 | ||||
-rw-r--r-- | mysql-test/main/type_time.result | 101 | ||||
-rw-r--r-- | mysql-test/main/type_time.test | 47 |
18 files changed, 883 insertions, 28 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"; diff --git a/mysql-test/main/func_debug.test b/mysql-test/main/func_debug.test index f9b6daab3a2..9237561500d 100644 --- a/mysql-test/main/func_debug.test +++ b/mysql-test/main/func_debug.test @@ -499,24 +499,59 @@ EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN -1 AND ?' USING 184467440737 EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN -1 AND ?' USING 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'; +SELECT * FROM t1 WHERE a BETWEEN '0' AND ' 0'; +SELECT * FROM t1 WHERE a BETWEEN '0' AND '0 '; +DROP TABLE t1; + +SET SESSION debug_dbug="-d,Item_basic_value"; + + +--echo # +--echo # MDEV-11362 True condition elimination does not work for DECIMAL and temporal dynamic SQL parameters +--echo # + +SET SESSION debug_dbug="+d,Item_basic_value"; -# DECIMAL does not work yet 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; +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN 1.0 AND ?' USING 1.0; DROP TABLE t1; -# Temporal types do not work yet 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'; +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN TIME''00:00:00'' AND ?' USING TIME'00:00:00'; +DROP TABLE t1; + +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'; +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN DATE''2001-01-01'' AND ?' USING 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'; +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN TIMESTAMP''2001-01-01 00:00:00'' AND ?' USING TIMESTAMP'2001-01-01 00:00:00'; DROP TABLE t1; +SET SESSION debug_dbug="-d,Item_basic_value"; + + +--echo # +--echo # MDEV-16426 Optimizer erroneously treats equal constants of different formats as same +--echo # + +SET SESSION debug_dbug="+d,Item_basic_value"; + CREATE TABLE t1 (a VARCHAR(10)); -INSERT INTO t1 VALUES ('0'),('1'),('2'); -SELECT * FROM t1 WHERE a BETWEEN '0' AND '0'; -SELECT * FROM t1 WHERE a BETWEEN '0' AND ' 0'; -SELECT * FROM t1 WHERE a BETWEEN '0' AND '0 '; +INSERT INTO t1 VALUES ('a'),('b'),('c'); +SELECT * FROM t1 WHERE a BETWEEN 'a' AND 0x61; +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN ''a'' AND ?' USING 0x61; DROP TABLE t1; SET SESSION debug_dbug="-d,Item_basic_value"; diff --git a/mysql-test/main/gis.result b/mysql-test/main/gis.result index 1cc4e45629b..c79699bf8cf 100644 --- a/mysql-test/main/gis.result +++ b/mysql-test/main/gis.result @@ -4940,5 +4940,29 @@ SELECT ST_SRID((SELECT MAX(a>3) FROM t1)); ERROR HY000: Illegal parameter data type boolean for operation 'srid' DROP TABLE t1; # +# Mixing GEOMETRY with HEX hybrid +# +SELECT 0x60=POINT(1,1), POINT(1,1)=0x60; +0x60=POINT(1,1) POINT(1,1)=0x60 +0 0 +CREATE TABLE t1 AS SELECT +COALESCE(0x60,POINT(1,1)), +COALESCE(POINT(1,1),0x60), +LEAST(0x60,POINT(1,1)), +LEAST(POINT(1,1),0x60); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `COALESCE(0x60,POINT(1,1))` longblob DEFAULT NULL, + `COALESCE(POINT(1,1),0x60)` longblob DEFAULT NULL, + `LEAST(0x60,POINT(1,1))` longblob DEFAULT NULL, + `LEAST(POINT(1,1),0x60)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +SELECT 0x60+POINT(1,1); +ERROR HY000: Illegal parameter data types bigint and geometry for operation '+' +SELECT POINT(1,1)+0x60; +ERROR HY000: Illegal parameter data types geometry and bigint for operation '+' +# # End of 10.4 tests # diff --git a/mysql-test/main/gis.test b/mysql-test/main/gis.test index 1bac831ac22..aacb9a6c653 100644 --- a/mysql-test/main/gis.test +++ b/mysql-test/main/gis.test @@ -3014,5 +3014,25 @@ SELECT ST_SRID((SELECT MAX(a>3) FROM t1)); DROP TABLE t1; --echo # +--echo # Mixing GEOMETRY with HEX hybrid +--echo # + +SELECT 0x60=POINT(1,1), POINT(1,1)=0x60; + +CREATE TABLE t1 AS SELECT + COALESCE(0x60,POINT(1,1)), + COALESCE(POINT(1,1),0x60), + LEAST(0x60,POINT(1,1)), + LEAST(POINT(1,1),0x60); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT 0x60+POINT(1,1); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT POINT(1,1)+0x60; + + +--echo # --echo # End of 10.4 tests --echo # diff --git a/mysql-test/main/type_date.result b/mysql-test/main/type_date.result index 69bdf569787..897f42a2ec4 100644 --- a/mysql-test/main/type_date.result +++ b/mysql-test/main/type_date.result @@ -889,3 +889,78 @@ 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 DATE); +INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02'),('2001-01-03'); +# Equal values +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE'2001:01:01',a)<=>COALESCE(DATE'2001-01-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 +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 DATE'2001-01-01',DATE'2001-01-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 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE''2001-01-01'',a)<=>COALESCE(?,a)' USING DATE'2001-01-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 +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(DATE''2001-01-01'',a)' USING DATE'2001-01-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 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 +# Equal values but of different data types (should not propagate) +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE'2001:01:01',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 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(DATE'2001-01-01',`test`.`t1`.`a`) <=> coalesce(TIMESTAMP'2001-01-01 00:00:00',`test`.`t1`.`a`) +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING DATE'2001-01-01',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 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(<cache>(DATE'2001-01-01'),`test`.`t1`.`a`) <=> coalesce(<cache>(TIMESTAMP'2001-01-01 00:00:00'),`test`.`t1`.`a`) +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE''2001-01-01'',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 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(DATE'2001-01-01',`test`.`t1`.`a`) <=> coalesce(<cache>(TIMESTAMP'2001-01-01 00:00:00'),`test`.`t1`.`a`) +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(DATE''2001-01-01'',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 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(<cache>(TIMESTAMP'2001-01-01 00:00:00'),`test`.`t1`.`a`) <=> coalesce(DATE'2001-01-01',`test`.`t1`.`a`) +# Not equal values +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE'2001-01-01',a)<=>COALESCE(DATE'2001-01-02',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(DATE'2001-01-01',`test`.`t1`.`a`) <=> coalesce(DATE'2001-01-02',`test`.`t1`.`a`) +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING DATE'2001-01-01',DATE'2001-01-02'; +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(<cache>(DATE'2001-01-01'),`test`.`t1`.`a`) <=> coalesce(<cache>(DATE'2001-01-02'),`test`.`t1`.`a`) +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE''2001-01-01'',a)<=>COALESCE(?,a)' USING DATE'2001-01-02'; +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(DATE'2001-01-01',`test`.`t1`.`a`) <=> coalesce(<cache>(DATE'2001-01-02'),`test`.`t1`.`a`) +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(DATE''2001-01-01'',a)' USING DATE'2001-01-02'; +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(<cache>(DATE'2001-01-02'),`test`.`t1`.`a`) <=> coalesce(DATE'2001-01-01',`test`.`t1`.`a`) +DROP TABLE t1; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/type_date.test b/mysql-test/main/type_date.test index 8d29a54a26c..cbd6cc846aa 100644 --- a/mysql-test/main/type_date.test +++ b/mysql-test/main/type_date.test @@ -614,3 +614,38 @@ DROP TABLE t1; --echo # --echo # End of 10.3 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 DATE); +INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02'),('2001-01-03'); +--echo # Equal values +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE'2001:01:01',a)<=>COALESCE(DATE'2001-01-01',a); +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING DATE'2001-01-01',DATE'2001-01-01'; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE''2001-01-01'',a)<=>COALESCE(?,a)' USING DATE'2001-01-01'; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(DATE''2001-01-01'',a)' USING DATE'2001-01-01'; + +--echo # Equal values but of different data types (should not propagate) +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE'2001:01:01',a)<=>COALESCE(TIMESTAMP'2001-01-01 00:00:00',a); +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING DATE'2001-01-01',TIMESTAMP'2001-01-01 00:00:00'; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE''2001-01-01'',a)<=>COALESCE(?,a)' USING TIMESTAMP'2001-01-01 00:00:00'; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(DATE''2001-01-01'',a)' USING TIMESTAMP'2001-01-01 00:00:00'; + +--echo # Not equal values +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE'2001-01-01',a)<=>COALESCE(DATE'2001-01-02',a); +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING DATE'2001-01-01',DATE'2001-01-02'; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE''2001-01-01'',a)<=>COALESCE(?,a)' USING DATE'2001-01-02'; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(DATE''2001-01-01'',a)' USING DATE'2001-01-02'; + +DROP TABLE t1; + +--echo # +--echo # End of 10.4 tests +--echo # 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(<cache>(TIMESTAMP'2001-01-01 00:00:00'),`test`.`t1`.`a`) <=> coalesce(<cache>(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(<cache>(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(<cache>(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(<cache>(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(<cache>(TIMESTAMP'2001-01-01 00:00:00.00'),`test`.`t1`.`a`)) +DROP TABLE t1; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/type_datetime.test b/mysql-test/main/type_datetime.test index dafa9f9456e..3c8287f8793 100644 --- a/mysql-test/main/type_datetime.test +++ b/mysql-test/main/type_datetime.test @@ -849,3 +849,55 @@ DROP TABLE t1; --echo # --echo # End of 10.3 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 DATETIME); +INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-02 00:00:00'),('2001-01-03 00:00:00'); +--echo # 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); +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'; +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'; +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'; + +--echo # 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); +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'; +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'; +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'; + +DROP TABLE t1; + + +--echo # +--echo # MDEV-16426 Optimizer erroneously treats equal constants of different formats as same +--echo # +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'); + +--echo 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)); +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)); +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'; +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'; + +--echo 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; +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)); +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)); +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'; +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'; + +DROP TABLE t1; + + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/type_decimal.result b/mysql-test/main/type_decimal.result index f96f98e7a9b..4d0f6fc12fd 100644 --- a/mysql-test/main/type_decimal.result +++ b/mysql-test/main/type_decimal.result @@ -1084,3 +1084,100 @@ t2 CREATE TABLE `t2` ( DROP TABLE t2; DROP TABLE t1; DROP TABLE t1dec102; +# +# End of 10.3 tests +# +# +# Start of 10.4 tests +# +# +# MDEV-11362 True condition elimination does not work for DECIMAL dynamic SQL parameters +# +CREATE TABLE t1 (a DECIMAL(10,1)); +INSERT INTO t1 VALUES (1),(2),(3); +# Equal values +EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1.0+a<=>1.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 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1.0,1.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 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1.0+a<=>?+a' USING 1.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 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>1.0+a' USING 1.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 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 +# Not equal values +EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1.0+a<=>1.1+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 1.0 + `test`.`t1`.`a` <=> 1.1 + `test`.`t1`.`a` +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1.0,1.1; +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 1.0 + `test`.`t1`.`a` <=> 1.1 + `test`.`t1`.`a` +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1.0+a<=>?+a' USING 1.1; +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 1.0 + `test`.`t1`.`a` <=> 1.1 + `test`.`t1`.`a` +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>1.0+a' USING 1.1; +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 1.1 + `test`.`t1`.`a` <=> 1.0 + `test`.`t1`.`a` +DROP TABLE t1; +# +# MDEV-16426 Optimizer erroneously treats equal constants of different formats as same +# +CREATE TABLE t1 (a DECIMAL(10,3)); +INSERT INTO t1 VALUES (10.0),(10.1); +Equal values +SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(10.0)+a; +a +10.000 +10.100 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(10.0)+a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(?)+a' USING 10.0; +a +10.000 +10.100 +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(?)+a' USING 10.0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 +Values with different formats +SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(10.00)+a; +a +EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(10.00)+a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where <cache>(octet_length(10.0)) + `test`.`t1`.`a` <=> <cache>(octet_length(10.00)) + `test`.`t1`.`a` +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(?)+a' USING 10.00; +a +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(?)+a' USING 10.00; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where <cache>(octet_length(10.0)) + `test`.`t1`.`a` <=> <cache>(octet_length(10.00)) + `test`.`t1`.`a` +DROP TABLE t1; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/type_decimal.test b/mysql-test/main/type_decimal.test index 903f375d1ae..dd4ba5df40d 100644 --- a/mysql-test/main/type_decimal.test +++ b/mysql-test/main/type_decimal.test @@ -673,3 +673,56 @@ DROP TABLE t2; DROP TABLE t1; DROP TABLE t1dec102; + +--echo # +--echo # End of 10.3 tests +--echo # + + +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-11362 True condition elimination does not work for DECIMAL dynamic SQL parameters +--echo # + +CREATE TABLE t1 (a DECIMAL(10,1)); +INSERT INTO t1 VALUES (1),(2),(3); +--echo # Equal values +EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1.0+a<=>1.0+a; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1.0,1.0; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1.0+a<=>?+a' USING 1.0; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>1.0+a' USING 1.0; +--echo # Not equal values +EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1.0+a<=>1.1+a; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1.0,1.1; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1.0+a<=>?+a' USING 1.1; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>1.0+a' USING 1.1; +DROP TABLE t1; + +--echo # +--echo # MDEV-16426 Optimizer erroneously treats equal constants of different formats as same +--echo # + +CREATE TABLE t1 (a DECIMAL(10,3)); +INSERT INTO t1 VALUES (10.0),(10.1); + +--echo Equal values +SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(10.0)+a; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(10.0)+a; +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(?)+a' USING 10.0; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(?)+a' USING 10.0; + +--echo Values with different formats +SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(10.00)+a; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(10.00)+a; +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(?)+a' USING 10.00; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(?)+a' USING 10.00; + +DROP TABLE t1; + + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/type_float.result b/mysql-test/main/type_float.result index 57cdd1561df..0ac018379d1 100644 --- a/mysql-test/main/type_float.result +++ b/mysql-test/main/type_float.result @@ -800,3 +800,35 @@ DROP TABLE t1; # # End of 10.2 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 DOUBLE); +INSERT INTO t1 VALUES (1),(2),(3); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1e0+a<=>1e0+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 ?+a<=>?+a' USING 1e0,1e0; +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 ?+a<=>1e0+a' USING 1e0; +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 1e0+a<=>?+a' USING 1e0; +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 +DROP TABLE t1; +# +# End of 10.4 tests +# 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 # diff --git a/mysql-test/main/type_hex_hybrid.result b/mysql-test/main/type_hex_hybrid.result new file mode 100644 index 00000000000..eec24f6e89e --- /dev/null +++ b/mysql-test/main/type_hex_hybrid.result @@ -0,0 +1,24 @@ +# +# Start of 10.4 tests +# +# +# MDEV-16426 Optimizer erroneously treats equal constants of different formats as same +# +SET NAMES utf8; +CREATE TABLE t1 (a DECIMAL(10,3)); +INSERT INTO t1 VALUES (10.0),(10.1); +SELECT CHARSET('a'),CHARSET(0x61),LENGTH(CHARSET('a'))+a,LENGTH(CHARSET(0x61))+a FROM t1; +CHARSET('a') CHARSET(0x61) LENGTH(CHARSET('a'))+a LENGTH(CHARSET(0x61))+a +utf8 binary 14.000 16.000 +utf8 binary 14.100 16.100 +SELECT * FROM t1 WHERE LENGTH(CHARSET('a'))+a<=>LENGTH(CHARSET(0x61))+a; +a +EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(CHARSET('a'))+a<=>LENGTH(CHARSET(0x61))+a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where <cache>(octet_length(charset('a'))) + `test`.`t1`.`a` <=> <cache>(octet_length(charset(0x61))) + `test`.`t1`.`a` +DROP TABLE t1; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/type_hex_hybrid.test b/mysql-test/main/type_hex_hybrid.test new file mode 100644 index 00000000000..a39750e2635 --- /dev/null +++ b/mysql-test/main/type_hex_hybrid.test @@ -0,0 +1,21 @@ +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-16426 Optimizer erroneously treats equal constants of different formats as same +--echo # + +# It's important for CHARSET('a') and CHARSET(0x61) to have different lengths in this test. +# 'latin1' and 'binary' have same lengths, so using 'utf8'. +SET NAMES utf8; +CREATE TABLE t1 (a DECIMAL(10,3)); +INSERT INTO t1 VALUES (10.0),(10.1); +SELECT CHARSET('a'),CHARSET(0x61),LENGTH(CHARSET('a'))+a,LENGTH(CHARSET(0x61))+a FROM t1; +SELECT * FROM t1 WHERE LENGTH(CHARSET('a'))+a<=>LENGTH(CHARSET(0x61))+a; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(CHARSET('a'))+a<=>LENGTH(CHARSET(0x61))+a; +DROP TABLE t1; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/type_int.result b/mysql-test/main/type_int.result index 47c859d3ffb..348d72b125b 100644 --- a/mysql-test/main/type_int.result +++ b/mysql-test/main/type_int.result @@ -225,3 +225,35 @@ DROP FUNCTION sint64; # # 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 INT); +INSERT INTO t1 VALUES (1),(2),(3); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1+a<=>1+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 ?+a<=>?+a' USING 1,1; +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 ?+a<=>1+a' USING 1; +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 1+a<=>?+a' USING 1; +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 +DROP TABLE t1; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/type_int.test b/mysql-test/main/type_int.test index bcab2b20dc4..f340d9a72bd 100644 --- a/mysql-test/main/type_int.test +++ b/mysql-test/main/type_int.test @@ -167,3 +167,23 @@ DROP FUNCTION sint64; --echo # --echo # End of 10.3 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 INT); +INSERT INTO t1 VALUES (1),(2),(3); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1+a<=>1+a; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1,1; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>1+a' USING 1; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1+a<=>?+a' USING 1; +DROP TABLE t1; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/type_time.result b/mysql-test/main/type_time.result index 5cdd3b00924..51b2aecaa6b 100644 --- a/mysql-test/main/type_time.result +++ b/mysql-test/main/type_time.result @@ -1953,3 +1953,104 @@ a filler -838:00:04.1 yes -838:00:03.1 yes DROP TABLE t1; +# +# Start of 10.4 tests +# +# +# MDEV-11362 True condition elimination does not work for DECIMAL and temporal dynamic SQL parameters +# +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES (1),(2),(3); +# Equal values +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIME'10:20:30',a)<=>COALESCE(TIME'10:20:30',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 TIME'10:20:30',TIME'10:20:30'; +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(TIME''10:20:30'',a)<=>COALESCE(?,a)' USING TIME'10:20:30'; +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(TIME''10:20:30'',a)' USING TIME'10:20:30'; +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(TIME'10:20:30',a)<=>COALESCE(TIME'10:20:31',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(TIME'10:20:30',`test`.`t1`.`a`) <=> coalesce(TIME'10:20:31',`test`.`t1`.`a`) +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING TIME'10:20:30',TIME'10:20:31'; +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(<cache>(TIME'10:20:30'),`test`.`t1`.`a`) <=> coalesce(<cache>(TIME'10:20:31'),`test`.`t1`.`a`) +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIME''10:20:30'',a)<=>COALESCE(?,a)' USING TIME'10:20:31'; +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(TIME'10:20:30',`test`.`t1`.`a`) <=> coalesce(<cache>(TIME'10:20:31'),`test`.`t1`.`a`) +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(TIME''10:20:30'',a)' USING TIME'10:20:31'; +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(<cache>(TIME'10:20:31'),`test`.`t1`.`a`) <=> coalesce(TIME'10:20:30',`test`.`t1`.`a`) +DROP TABLE t1; +# +# MDEV-16426 Optimizer erroneously treats equal constants of different formats as same +# +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:00'),('00:00:01'),('00:00:02'); +Equal values +SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME'00:00:00.0',a))<=>LENGTH(COALESCE(TIME'00:00:00.0',a)); +a +00:00:00 +00:00:01 +00:00:02 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME'00:00:00.0',a))<=>LENGTH(COALESCE(TIME'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 TIME'00:00:00.0'; +a +00:00:00 +00:00:01 +00:00:02 +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'; +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 +Values with different formats +SELECT LENGTH(COALESCE(TIME'00:00:00.0',a)),LENGTH(COALESCE(TIME'00:00:00.00',a)) FROM t1; +LENGTH(COALESCE(TIME'00:00:00.0',a)) LENGTH(COALESCE(TIME'00:00:00.00',a)) +10 11 +10 11 +10 11 +SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME'00:00:00.0',a))<=>LENGTH(COALESCE(TIME'00:00:00.00',a)); +a +EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME'00:00:00.0',a))<=>LENGTH(COALESCE(TIME'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(TIME'00:00:00.0',`test`.`t1`.`a`)) <=> octet_length(coalesce(TIME'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 TIME'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 TIME'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(<cache>(TIME'00:00:00.00'),`test`.`t1`.`a`)) +DROP TABLE t1; +# +# End of 10.4 tests +# 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 # |