diff options
author | Sergei Petrunia <psergey@askmonty.org> | 2019-08-29 15:37:49 +0300 |
---|---|---|
committer | Sergei Petrunia <psergey@askmonty.org> | 2019-08-30 12:02:40 +0300 |
commit | ef76f81c982bdbcfa4797ce26224db9c016ddebd (patch) | |
tree | 2f65a45620e011e24ddd4fd80d2eae35a28c4f7a /mysql-test/suite/compat/oracle | |
parent | d58437d1956b2fd92197beac2e9c869ef968eba7 (diff) | |
download | mariadb-git-ef76f81c982bdbcfa4797ce26224db9c016ddebd.tar.gz |
MDEV-20109: Optimizer ignores distinct key created for materialized...
(Backported to 10.3, addressed review input)
Sj_materialization_picker::check_qep(): fix error in cost/fanout
calculations:
- for each join prefix, add #prefix_rows / TIME_FOR_COMPARE to the cost,
like best_extension_by_limited_search does
- Remove the fanout produced by the subquery tables.
- Also take into account join condition selectivity
optimize_wo_join_buffering() (used by LooseScan and FirstMatch)
- also add #prefix_rows / TIME_FOR_COMPARE to the cost of each prefix.
- Also take into account join condition selectivity
Diffstat (limited to 'mysql-test/suite/compat/oracle')
-rw-r--r-- | mysql-test/suite/compat/oracle/r/table_value_constr.result | 24 |
1 files changed, 12 insertions, 12 deletions
diff --git a/mysql-test/suite/compat/oracle/r/table_value_constr.result b/mysql-test/suite/compat/oracle/r/table_value_constr.result index f0c7c4eebe1..d4f8e28fe07 100644 --- a/mysql-test/suite/compat/oracle/r/table_value_constr.result +++ b/mysql-test/suite/compat/oracle/r/table_value_constr.result @@ -741,21 +741,21 @@ a b explain extended select * from t1 where a in (values (1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1" +Note 1003 select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where 1 explain extended select * from t1 where a in (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1" +Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where 1 # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a in (values (1) union select 2); @@ -976,21 +976,21 @@ a b explain extended select * from t1 where a = any (values (1),(2)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1" +Note 1003 select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where 1 explain extended select * from t1 where a = any (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1" +Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where 1 # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a = any (values (1) union select 2); |