diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/join_cache.result | 9 | ||||
| -rw-r--r-- | mysql-test/r/limit_rows_examined.result | 24 | ||||
| -rw-r--r-- | mysql-test/t/join_cache.test | 12 | ||||
| -rw-r--r-- | mysql-test/t/limit_rows_examined.test | 1 |
4 files changed, 34 insertions, 12 deletions
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 5cb76a93d9a..eb845c63a76 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5949,4 +5949,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE PROFILING ALL NULL NULL NULL NULL NULL Using where 1 SIMPLE user hash_ALL NULL #hash#$hj 1 information_schema.PROFILING.PAGE_FAULTS_MINOR 4 Using where; Using join buffer (flat, BNLH join) set join_cache_level=default; +create table t1 (c1 date not null, key (c1)) engine=innodb; +insert t1 values ('2017-12-27'); +create table t2 (pk int, f1 int, f2 int); +insert t2 values (4,1,1), (6,1,1); +set join_buffer_size = 222222208; +select f2 from t2,t1 where f2 = 0; +f2 +drop table t1, t2; +set join_buffer_size = default; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result index 157d10ae76a..035d6f7ac1c 100644 --- a/mysql-test/r/limit_rows_examined.result +++ b/mysql-test/r/limit_rows_examined.result @@ -749,22 +749,24 @@ FROM t1, t2 AS alias2, t2 AS alias3 WHERE alias3.c IN ( SELECT 1 UNION SELECT 6 ) GROUP BY field1, field2, field3, field4, field5 LIMIT ROWS EXAMINED 120; -ERROR HY000: Sort aborted: +field1 field2 field3 field4 field5 +Warnings: +Warning 1931 Query execution was interrupted. The query examined at least 121 rows, which exceeds LIMIT ROWS EXAMINED (120). The query result may be incomplete SHOW STATUS LIKE 'Handler_read%'; Variable_name Value Handler_read_first 1 -Handler_read_key 5 +Handler_read_key 4 Handler_read_last 0 -Handler_read_next 7 +Handler_read_next 4 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 -Handler_read_rnd_next 38 +Handler_read_rnd_next 46 SHOW STATUS LIKE 'Handler_tmp%'; Variable_name Value Handler_tmp_update 0 -Handler_tmp_write 70 +Handler_tmp_write 66 FLUSH STATUS; SELECT a AS field1, alias2.d AS field2, alias2.f AS field3, alias2.e AS field4, b AS field5 FROM t1, t2 AS alias2, t2 AS alias3 @@ -772,20 +774,20 @@ WHERE alias3.c IN ( SELECT 1 UNION SELECT 6 ) GROUP BY field1, field2, field3, field4, field5 LIMIT ROWS EXAMINED 124; field1 field2 field3 field4 field5 -00:21:38 06:07:10 a 2007-06-08 04:35:26 2007-05-28 00:00:00 Warnings: Warning 1931 Query execution was interrupted. The query examined at least 125 rows, which exceeds LIMIT ROWS EXAMINED (124). The query result may be incomplete +Warning 1931 Query execution was interrupted. The query examined at least 127 rows, which exceeds LIMIT ROWS EXAMINED (124). The query result may be incomplete SHOW STATUS LIKE 'Handler_read%'; Variable_name Value Handler_read_first 1 -Handler_read_key 5 +Handler_read_key 4 Handler_read_last 0 -Handler_read_next 7 +Handler_read_next 4 Handler_read_prev 0 Handler_read_retry 0 -Handler_read_rnd 2 -Handler_read_rnd_deleted 1 -Handler_read_rnd_next 39 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 48 SHOW STATUS LIKE 'Handler_tmp%'; Variable_name Value Handler_tmp_update 0 diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 009e72c1c14..611f5ab8942 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3900,6 +3900,18 @@ SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user WHERE password_expired = set join_cache_level=default; +# +# MDEV-12580 Wrong query result in join when using an index (Version > "10.2.3") +# +create table t1 (c1 date not null, key (c1)) engine=innodb; +insert t1 values ('2017-12-27'); +create table t2 (pk int, f1 int, f2 int); +insert t2 values (4,1,1), (6,1,1); +set join_buffer_size = 222222208; +select f2 from t2,t1 where f2 = 0; +drop table t1, t2; +set join_buffer_size = default; + # The following command must be the last one the file # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/limit_rows_examined.test b/mysql-test/t/limit_rows_examined.test index 29b3b411602..815394aec5c 100644 --- a/mysql-test/t/limit_rows_examined.test +++ b/mysql-test/t/limit_rows_examined.test @@ -489,7 +489,6 @@ GROUP BY field1, field2, field3, field4, field5 LIMIT ROWS EXAMINED 120; FLUSH STATUS; ---error 1028 SELECT a AS field1, alias2.d AS field2, alias2.f AS field3, alias2.e AS field4, b AS field5 FROM t1, t2 AS alias2, t2 AS alias3 WHERE alias3.c IN ( SELECT 1 UNION SELECT 6 ) |