--- /home/rafaeldtinoco/work/sources/general/ubuntu/mysql-8.0-8.0.18/mysql-test/r/subquery_sj_mat_bka.result 2019-09-20 11:30:51.000000000 +0300
+++ /home/rafaeldtinoco/work/sources/general/ubuntu/mysql-8.0-8.0.18/builddir/mysql-test/var/9/log/subquery_sj_mat_bka.reject 2020-01-09 19:54:09.659290415 +0300
@@ -12800,10 +12800,10 @@
-> Hash
-> Table scan on <subquery2> -> Materialize with deduplication
- -> Filter: (t1.col_int_key is not null) (cost=3.40 rows=7)
- -> Filter: (t3.col_int is null) (cost=3.40 rows=7)
- -> Nested loop left join (cost=3.40 rows=7)
- -> Index scan on t1 using col_int_key (cost=0.95 rows=7)
+ -> Filter: (t1.col_int_key is not null) (cost=4.15 rows=7)
+ -> Filter: (t3.col_int is null) (cost=4.15 rows=7)
+ -> Nested loop left join (cost=4.15 rows=7)
+ -> Index scan on t1 using col_int_key (cost=1.70 rows=7)
This test calculates the cost of a query with BKA (Batched Key Access) feature enabled:
"""
set optimizer_switch='batched_key_access=on,mrr_cost_based=off';
--source t/subquery_sj_mat.test
set optimizer_switch=default;
"""
explain format=tree SELECT * FROM t4 WHERE (col_int_key) IN ( SELECT t1.col_int_key FROM t1 LEFT JOIN ( t2 JOIN t3 ON t3.col_int_key = t2.col_int ) USING ( pk ) WHERE t3.col_int IS NULL) ORDER BY col_int_key;
And it looks like optimizer switching makes test to have different costs sometimes. Nevertheless, after fixing (PROBLEM #1) I was able to fully build MySQL Server binary packages without triggering (PROBLEM #2).
dpkg-deb: building package 'mysql-server' in '../mysql-server_8.0.18-0ubuntu4_all.deb'.
dpkg-deb: building package 'mysql-testsuite-8.0' in '../mysql-testsuite-8.0_8.0.18-0ubuntu4_amd64.deb'.
dpkg-deb: building package 'mysql-source-8.0' in '../mysql-source-8.0_8.0.18-0ubuntu4_amd64.deb'.
dpkg-deb: building package 'mysql-client' in '../mysql-client_8.0.18-0ubuntu4_all.deb'.
dpkg-deb: building package 'mysql-testsuite' in '../mysql-testsuite_8.0.18-0ubuntu4_all.deb'.
dpkg-deb: building package 'mysql-router' in '../mysql-router_8.0.18-0ubuntu4_amd64.deb'.
dpkg-deb: building package 'mysql-server-8.0' in '../mysql-server-8.0_8.0.18-0ubuntu4_amd64.deb'.
Problem 2 is an intermittent problem:
(PROBLEM #2)
CURRENT_TEST: main.subquery_ sj_mat_ bka
--- /home/rafaeldti noco/work/ sources/ general/ ubuntu/ mysql-8. 0-8.0.18/ mysql-test/ r/subquery_ sj_mat_ bka.result 2019-09-20 11:30:51.000000000 +0300 noco/work/ sources/ general/ ubuntu/ mysql-8. 0-8.0.18/ builddir/ mysql-test/ var/9/log/ subquery_ sj_mat_ bka.reject 2020-01-09 19:54:09.659290415 +0300
-> Materialize with deduplication
+++ /home/rafaeldti
@@ -12800,10 +12800,10 @@
-> Hash
-> Table scan on <subquery2>
- -> Filter: (t1.col_int_key is not null) (cost=3.40 rows=7)
- -> Filter: (t3.col_int is null) (cost=3.40 rows=7)
- -> Nested loop left join (cost=3.40 rows=7)
- -> Index scan on t1 using col_int_key (cost=0.95 rows=7)
+ -> Filter: (t1.col_int_key is not null) (cost=4.15 rows=7)
+ -> Filter: (t3.col_int is null) (cost=4.15 rows=7)
+ -> Nested loop left join (cost=4.15 rows=7)
+ -> Index scan on t1 using col_int_key (cost=1.70 rows=7)
This test calculates the cost of a query with BKA (Batched Key Access) feature enabled:
""" switch= 'batched_ key_access= on,mrr_ cost_based= off';
set optimizer_
--source t/subquery_ sj_mat. test
set optimizer_ switch= default;
"""
explain format=tree SELECT * FROM t4 WHERE (col_int_key) IN ( SELECT t1.col_int_key FROM t1 LEFT JOIN ( t2 JOIN t3 ON t3.col_int_key = t2.col_int ) USING ( pk ) WHERE t3.col_int IS NULL) ORDER BY col_int_key;
And it looks like optimizer switching makes test to have different costs sometimes. Nevertheless, after fixing (PROBLEM #1) I was able to fully build MySQL Server binary packages without triggering (PROBLEM #2).
dpkg-deb: building package 'mysql-server' in '../mysql- server_ 8.0.18- 0ubuntu4_ all.deb' . testsuite- 8.0' in '../mysql- testsuite- 8.0_8.0. 18-0ubuntu4_ amd64.deb' . source- 8.0_8.0. 18-0ubuntu4_ amd64.deb' . client_ 8.0.18- 0ubuntu4_ all.deb' . testsuite_ 8.0.18- 0ubuntu4_ all.deb' . router_ 8.0.18- 0ubuntu4_ amd64.deb' . server- 8.0_8.0. 18-0ubuntu4_ amd64.deb' .
dpkg-deb: building package 'mysql-
dpkg-deb: building package 'mysql-source-8.0' in '../mysql-
dpkg-deb: building package 'mysql-client' in '../mysql-
dpkg-deb: building package 'mysql-testsuite' in '../mysql-
dpkg-deb: building package 'mysql-router' in '../mysql-
dpkg-deb: building package 'mysql-server-8.0' in '../mysql-