diff options
Diffstat (limited to 'mysql-test/main/type_decimal.result')
| -rw-r--r-- | mysql-test/main/type_decimal.result | 111 |
1 files changed, 111 insertions, 0 deletions
diff --git a/mysql-test/main/type_decimal.result b/mysql-test/main/type_decimal.result index d87ab0b38a3..6e7d7c8b6fe 100644 --- a/mysql-test/main/type_decimal.result +++ b/mysql-test/main/type_decimal.result @@ -1129,3 +1129,114 @@ 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; +# +# MDEV-16984 Assertion `dec' failed in Dec_ptr::cmp +# +SET sql_mode=''; +CREATE TABLE t1 (dc decimal(10)); +INSERT INTO t1 VALUES (0000000),(NULL); +SELECT 1 FROM t1 GROUP BY 'm' <=> dc; +1 +1 +1 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: 'm' +DROP TABLE t1; +SET sql_mode=DEFAULT; +# +# End of 10.4 tests +# |