Skip to content

Commit 23487a6

Browse files
roylysengroylyseng
committed
Bug#14272788: Query with MaterializeScan and materialized subquery
 returns too many rows Patch alibaba#2 - address Guilhem's comments - added optimizer trace changes supplied by Guilhem. mysql-test/r/subquery_sj_all_bka_nixbnl.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_bka.result mysql-test/r/subquery_sj_mat_bka_nixbnl.result mysql-test/r/subquery_sj_mat_bkaunique.result EXPLAIN changes due to no longer checking that a keyuse->val->type() is Item::FIELD_ITEM within JOIN::update_equalities_for_sjm(). mysql-test/suite/opt_trace/include/general.inc Modified optimizer trace settings for preservation of existing behaviour. mysql-test/suite/opt_trace/r/bugs_no_prot_all.result mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result mysql-test/suite/opt_trace/r/general_no_prot_all.result mysql-test/suite/opt_trace/r/general_no_prot_none.result mysql-test/suite/opt_trace/r/general_ps_prot_all.result mysql-test/suite/opt_trace/r/general_ps_prot_none.result Changed optimizer trace results. sql/item_cmpfunc.cc Code simplifications and added comments. Check on whether an equality originates from a semi-join equality is slightly changed. sql/item_subselect.cc Replaced use of TABLE_LIST::materialized with JOIN_TAB::materialized. sql/opt_explain.cc Semijoin_mat_exec pointer is taken from JOIN_TAB, not TABLE_LIST. Use inner_table_index instead of old table_index. sql/opt_range.cc Reverted use of JOIN::plan_is_single_table(). sql/sql_class.h In class Semijoin_mat_exec, added field mat_table_index, and renamed table_index to inner_table_index. inner_table_index is also made an absolute index (it used to be relative to mat_table_index). Member field_count is deleted. subq_exprs is converted from being a List to pointer to List. sql/sql_executor.cc In end_sj_materialize(), Semijoin_mat_exec is taken from JOIN_TAB. Some comment changes. join_materialize_table() is renamed to join_materialize_derived(). join_materialize_semijoin() zeroes last->next_select and last->sj_mat_exec after use. inner_table_index is converted to an absolute index. sql/sql_executor.h join_materialize_table() is renamed to join_materialize_derived(). sql/sql_join_buffer.cc Comment added inside JOIN_CACHE::calc_record_fields(). sql/sql_optimizer.cc Logic of eliminate_item_equal() is rewritten according to suggestion from Guilhem. JOIN::update_keyuse() is incorporated into JOIN::update_equalities(), which is renamed to JOIN::update_equalities_for_sjm(). Semijoin_mat_exec pointer is taken from JOIN_TAB instead of TABLE_LIST. JOIN::allocate_sj_mat_exec() is incorporated into JOIN::get_best_combination(). Local struct KEY_FIELD is renamed to Key_field in association with introduction of constructor. create_keyuse_for_table() now uses add_key_part() when creating keyuse array. sql/sql_optimizer.h Removed member sjm_nests from class JOIN, it is calculated locally. update_equalities() is renamed to update_equalities_for_sjm(). Updated comments. sql/sql_select.cc In clear_sj_tmp_tables(), added back clearing of materialized flag for semi-joined materialized tables. JOIN::destroy() now deletes all Semijoin_mat_exec objects. JOIN::get_best_combination(): Logic slightly re-arranged, creates the list of Semijoin_mat_exec objects. In JOIN::setup_materialized_table(), take Semijoin_mat_exec from JOIN_TAB, increment name allocation by 1 byte for null termination, use TABLE_LIST::init_one_table() to initialize object, modify "fanout" calculation for materialized temporary table, use POSITION::set_prefix_costs() to set cost. JOIN_TAB::cleanup(), earlier valgrind compensation deleted. sql/sql_select.h Added Semijoin_mat_exec pointer to class JOIN_TAB. sql/sql_show.cc Cosmetic changes.
1 parent 46bdbf7 commit 23487a6

25 files changed

Lines changed: 641 additions & 1216 deletions

‎mysql-test/r/subquery_sj_all_bka_nixbnl.result‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9785,7 +9785,7 @@ FROM t1 AS subquery3_t1
97859785
id select_type table type possible_keys key key_len ref rows Extra
97869786
1 PRIMARY subquery3_t1 index col_varchar_key col_varchar_key 9 NULL 0 Using where; Using index
97879787
1 PRIMARY <subquery3> ALL NULL NULL NULL NULL 0 NULL
9788-
1 PRIMARY <derived2> ref auto_key0 auto_key0 9 const,test.subquery3_t1.col_varchar_key 2 NULL
9788+
1 PRIMARY <derived2> ref auto_key0 auto_key0 9 <subquery3>.7,test.subquery3_t1.col_varchar_key 2 Using where
97899789
1 PRIMARY table2 index NULL PRIMARY 4 NULL 1 Using index
97909790
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 0 NULL
97919791
2 DERIVED subquery1_t1 ALL PRIMARY NULL NULL NULL 1 NULL

‎mysql-test/r/subquery_sj_mat.result‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9868,7 +9868,7 @@ FROM t1 AS subquery3_t1
98689868
id select_type table type possible_keys key key_len ref rows Extra
98699869
1 PRIMARY subquery3_t1 index col_varchar_key col_varchar_key 9 NULL 0 Using where; Using index
98709870
1 PRIMARY <subquery3> ALL NULL NULL NULL NULL 0 Using join buffer (Block Nested Loop)
9871-
1 PRIMARY <derived2> ref auto_key0 auto_key0 9 const,test.subquery3_t1.col_varchar_key 2 NULL
9871+
1 PRIMARY <derived2> ref auto_key0 auto_key0 9 <subquery3>.7,test.subquery3_t1.col_varchar_key 2 Using where
98729872
1 PRIMARY table2 index NULL PRIMARY 4 NULL 1 Using index; Using join buffer (Block Nested Loop)
98739873
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 0 NULL
98749874
2 DERIVED subquery1_t1 ALL PRIMARY NULL NULL NULL 1 NULL

‎mysql-test/r/subquery_sj_mat_bka.result‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9869,7 +9869,7 @@ FROM t1 AS subquery3_t1
98699869
id select_type table type possible_keys key key_len ref rows Extra
98709870
1 PRIMARY subquery3_t1 index col_varchar_key col_varchar_key 9 NULL 0 Using where; Using index
98719871
1 PRIMARY <subquery3> ALL NULL NULL NULL NULL 0 Using join buffer (Block Nested Loop)
9872-
1 PRIMARY <derived2> ref auto_key0 auto_key0 9 const,test.subquery3_t1.col_varchar_key 2 NULL
9872+
1 PRIMARY <derived2> ref auto_key0 auto_key0 9 <subquery3>.7,test.subquery3_t1.col_varchar_key 2 Using where
98739873
1 PRIMARY table2 index NULL PRIMARY 4 NULL 1 Using index; Using join buffer (Block Nested Loop)
98749874
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 0 NULL
98759875
2 DERIVED subquery1_t1 ALL PRIMARY NULL NULL NULL 1 NULL

‎mysql-test/r/subquery_sj_mat_bka_nixbnl.result‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9858,7 +9858,7 @@ FROM t1 AS subquery3_t1
98589858
id select_type table type possible_keys key key_len ref rows Extra
98599859
1 PRIMARY subquery3_t1 index col_varchar_key col_varchar_key 9 NULL 0 Using where; Using index
98609860
1 PRIMARY <subquery3> ALL NULL NULL NULL NULL 0 NULL
9861-
1 PRIMARY <derived2> ref auto_key0 auto_key0 9 const,test.subquery3_t1.col_varchar_key 2 NULL
9861+
1 PRIMARY <derived2> ref auto_key0 auto_key0 9 <subquery3>.7,test.subquery3_t1.col_varchar_key 2 Using where
98629862
1 PRIMARY table2 index NULL PRIMARY 4 NULL 1 Using index
98639863
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 0 NULL
98649864
2 DERIVED subquery1_t1 ALL PRIMARY NULL NULL NULL 1 NULL

‎mysql-test/r/subquery_sj_mat_bkaunique.result‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9870,7 +9870,7 @@ FROM t1 AS subquery3_t1
98709870
id select_type table type possible_keys key key_len ref rows Extra
98719871
1 PRIMARY subquery3_t1 index col_varchar_key col_varchar_key 9 NULL 0 Using where; Using index
98729872
1 PRIMARY <subquery3> ALL NULL NULL NULL NULL 0 Using join buffer (Block Nested Loop)
9873-
1 PRIMARY <derived2> ref auto_key0 auto_key0 9 const,test.subquery3_t1.col_varchar_key 2 NULL
9873+
1 PRIMARY <derived2> ref auto_key0 auto_key0 9 <subquery3>.7,test.subquery3_t1.col_varchar_key 2 Using where
98749874
1 PRIMARY table2 index NULL PRIMARY 4 NULL 1 Using index; Using join buffer (Block Nested Loop)
98759875
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 0 NULL
98769876
2 DERIVED subquery1_t1 ALL PRIMARY NULL NULL NULL 1 NULL

‎mysql-test/suite/opt_trace/include/general.inc‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -425,6 +425,9 @@ select * from information_schema.OPTIMIZER_TRACE|
425425
# treated differently: it is executed, so the order and amount of
426426
# executions of f1() becomes different, which changes the order of
427427
# substatements in the trace.
428+
set @old_opt_switch=@@optimizer_switch;
429+
# Test was created when sj-mat-scan could not handle this query...
430+
set optimizer_switch="materialization=off";
428431
set optimizer_trace_offset=-60, optimizer_trace_limit=60|
429432
select * from t6 where d in (select f1() from t2 where s="c")|
430433
select * from information_schema.OPTIMIZER_TRACE|
@@ -438,6 +441,7 @@ select * from t6 where d in (select f1() from t2 where s="c")|
438441
# In ps-protocol mode, we also have trace of PREPARE for the top
439442
# SELECT, so we see one trace before the DELETE: the INSERT.
440443
select * from information_schema.OPTIMIZER_TRACE|
444+
set @@optimizer_switch=@old_opt_switch;
441445

442446
# Stored procedures
443447
create procedure p1(arg char(1))
@@ -446,9 +450,13 @@ begin
446450
select d into res from t6 where d in (select f1() from t2 where s=arg);
447451
select d+1 into res from t6 where d=res+1;
448452
end|
453+
set @old_opt_switch=@@optimizer_switch;
454+
# Test was created when sj-mat-scan could not handle this query...
455+
set optimizer_switch="materialization=off";
449456
set optimizer_trace_offset=0, optimizer_trace_limit=100;
450457
call p1("c")|
451458
select * from information_schema.OPTIMIZER_TRACE|
459+
set @@optimizer_switch=@old_opt_switch;
452460

453461
# Triggers
454462
create trigger trg1 before insert on t2 for each row

‎mysql-test/suite/opt_trace/r/bugs_no_prot_all.result‎

Lines changed: 88 additions & 77 deletions
Original file line numberDiff line numberDiff line change
@@ -1610,6 +1610,10 @@ ON table2 .col_int_key = table1 .col_int_key
16101610
"cost_for_plan": 7.4138,
16111611
"rows_for_plan": 12,
16121612
"semijoin_strategy_choice": [
1613+
{
1614+
"strategy": "MaterializeScan",
1615+
"choice": "deferred"
1616+
}
16131617
] /* semijoin_strategy_choice */,
16141618
"rest_of_plan": [
16151619
{
@@ -1633,11 +1637,36 @@ ON table2 .col_int_key = table1 .col_int_key
16331637
"rows_for_plan": 72,
16341638
"semijoin_strategy_choice": [
16351639
{
1636-
"strategy": "DuplicatesWeedout",
1637-
"cost": 41.434,
1640+
"strategy": "MaterializeScan",
1641+
"recalculate_access_paths_and_cost": {
1642+
"tables": [
1643+
{
1644+
"table": "`where_subselect_20070`",
1645+
"best_access_path": {
1646+
"considered_access_paths": [
1647+
{
1648+
"access_type": "scan",
1649+
"using_join_cache": true,
1650+
"rows": 6,
1651+
"cost": 6.8194,
1652+
"chosen": true
1653+
}
1654+
] /* considered_access_paths */
1655+
} /* best_access_path */
1656+
}
1657+
] /* tables */
1658+
} /* recalculate_access_paths_and_cost */,
1659+
"cost": 19.433,
16381660
"rows": 6,
16391661
"duplicate_tables_left": true,
16401662
"chosen": true
1663+
},
1664+
{
1665+
"strategy": "DuplicatesWeedout",
1666+
"cost": 41.434,
1667+
"rows": 6,
1668+
"duplicate_tables_left": false,
1669+
"chosen": false
16411670
}
16421671
] /* semijoin_strategy_choice */,
16431672
"pruned_by_cost": true
@@ -1758,6 +1787,10 @@ ON table2 .col_int_key = table1 .col_int_key
17581787
"cost_for_plan": 7.4138,
17591788
"rows_for_plan": 12,
17601789
"semijoin_strategy_choice": [
1790+
{
1791+
"strategy": "MaterializeScan",
1792+
"choice": "deferred"
1793+
}
17611794
] /* semijoin_strategy_choice */,
17621795
"rest_of_plan": [
17631796
{
@@ -1781,11 +1814,36 @@ ON table2 .col_int_key = table1 .col_int_key
17811814
"rows_for_plan": 72,
17821815
"semijoin_strategy_choice": [
17831816
{
1784-
"strategy": "DuplicatesWeedout",
1785-
"cost": 41.434,
1817+
"strategy": "MaterializeScan",
1818+
"recalculate_access_paths_and_cost": {
1819+
"tables": [
1820+
{
1821+
"table": "`where_subselect_20070`",
1822+
"best_access_path": {
1823+
"considered_access_paths": [
1824+
{
1825+
"access_type": "scan",
1826+
"using_join_cache": true,
1827+
"rows": 6,
1828+
"cost": 6.8194,
1829+
"chosen": true
1830+
}
1831+
] /* considered_access_paths */
1832+
} /* best_access_path */
1833+
}
1834+
] /* tables */
1835+
} /* recalculate_access_paths_and_cost */,
1836+
"cost": 19.433,
17861837
"rows": 6,
17871838
"duplicate_tables_left": true,
17881839
"chosen": true
1840+
},
1841+
{
1842+
"strategy": "DuplicatesWeedout",
1843+
"cost": 41.434,
1844+
"rows": 6,
1845+
"duplicate_tables_left": false,
1846+
"chosen": false
17891847
}
17901848
] /* semijoin_strategy_choice */,
17911849
"pruned_by_cost": true
@@ -1799,9 +1857,27 @@ ON table2 .col_int_key = table1 .col_int_key
17991857
}
18001858
] /* considered_execution_plans */
18011859
},
1860+
{
1861+
"creating_tmp_table": {
1862+
"tmp_table_info": {
1863+
"row_length": 13,
1864+
"key_length": 14,
1865+
"unique_constraint": false,
1866+
"location": "memory (heap)",
1867+
"row_limit_estimate": 80659
1868+
} /* tmp_table_info */
1869+
} /* creating_tmp_table */
1870+
},
1871+
{
1872+
"subselect_execution": {
1873+
"select#": 2,
1874+
"steps": [
1875+
] /* steps */
1876+
} /* subselect_execution */
1877+
},
18021878
{
18031879
"attaching_conditions_to_tables": {
1804-
"original_condition": "((`table1`.`col_int_key` = `where_subselect_20070`.`field1`) and (`table2`.`col_int_key` = `table1`.`col_int_key`))",
1880+
"original_condition": "((`<subquery3>`.`field1` = `where_subselect_20070`.`field1`) and (`table2`.`col_int_key` = `table1`.`col_int_key`))",
18051881
"attached_conditions_computation": [
18061882
{
18071883
"table": "`t1` `table2`",
@@ -1851,6 +1927,10 @@ ON table2 .col_int_key = table1 .col_int_key
18511927
"attached_conditions_summary": [
18521928
{
18531929
"table": "`where_subselect_20070`",
1930+
"attached": "(`where_subselect_20070`.`field1` is not null)"
1931+
},
1932+
{
1933+
"table": "``.`<subquery3>`",
18541934
"attached": null
18551935
},
18561936
{
@@ -1870,38 +1950,11 @@ ON table2 .col_int_key = table1 .col_int_key
18701950
"table": "`where_subselect_20070`",
18711951
"access_type": "table_scan"
18721952
},
1953+
{
1954+
"table": "``.`<subquery3>`"
1955+
},
18731956
{
18741957
"table": "`t1` `table1`",
1875-
"unknown_key_1": {
1876-
"creating_tmp_table": {
1877-
"tmp_table_info": {
1878-
"row_length": 13,
1879-
"key_length": 14,
1880-
"unique_constraint": false,
1881-
"location": "memory (heap)",
1882-
"row_limit_estimate": 80659
1883-
} /* tmp_table_info */
1884-
} /* creating_tmp_table */
1885-
},
1886-
"creating_semijoin_materialization_conditions": {
1887-
"evaluating_constant_semijoin_conditions": [
1888-
{
1889-
"subselect_execution": {
1890-
"select#": 2,
1891-
"steps": [
1892-
] /* steps */
1893-
} /* subselect_execution */
1894-
},
1895-
{
1896-
"subselect_execution": {
1897-
"select#": 2,
1898-
"steps": [
1899-
] /* steps */
1900-
} /* subselect_execution */
1901-
}
1902-
] /* evaluating_constant_semijoin_conditions */,
1903-
"resulting_condition": "((`where_subselect_20070`.`field1` = `sj-materialize`.`field1`) and (4 = `sj-materialize`.`( SELECT COUNT( col_int_key )\nFROM t1\n)`))"
1904-
} /* creating_semijoin_materialization_conditions */,
19051958
"access_type": "index_scan"
19061959
},
19071960
{
@@ -2000,48 +2053,6 @@ ON table2 .col_int_key = table1 .col_int_key
20002053
"steps": [
20012054
] /* steps */
20022055
} /* subselect_execution */
2003-
},
2004-
{
2005-
"subselect_execution": {
2006-
"select#": 2,
2007-
"steps": [
2008-
] /* steps */
2009-
} /* subselect_execution */
2010-
},
2011-
{
2012-
"subselect_execution": {
2013-
"select#": 2,
2014-
"steps": [
2015-
] /* steps */
2016-
} /* subselect_execution */
2017-
},
2018-
{
2019-
"subselect_execution": {
2020-
"select#": 2,
2021-
"steps": [
2022-
] /* steps */
2023-
} /* subselect_execution */
2024-
},
2025-
{
2026-
"subselect_execution": {
2027-
"select#": 2,
2028-
"steps": [
2029-
] /* steps */
2030-
} /* subselect_execution */
2031-
},
2032-
{
2033-
"subselect_execution": {
2034-
"select#": 2,
2035-
"steps": [
2036-
] /* steps */
2037-
} /* subselect_execution */
2038-
},
2039-
{
2040-
"subselect_execution": {
2041-
"select#": 2,
2042-
"steps": [
2043-
] /* steps */
2044-
} /* subselect_execution */
20452056
}
20462057
] /* steps */
20472058
} /* join_execution */

0 commit comments

Comments
 (0)