Skip to content

Commit 46bdbf7

Browse files
roylysengroylyseng
committed
Bug#14272788: Query with MaterializeScan and materialized subquery
 returns too many rows The fix of this bug is based on rearranging JOIN_TAB elements for materialized semi-join nests and eliminating special optimizer and execution code for such nests. See separate design document for more explanation. internal/mysql-test/suite/i_main/r/subquery.result Added test case results for bug#14272788 and bug#14340367. Updated test results for materialized semi-join queries. internal/mysql-test/suite/i_main/r/subquery_mat_cost_based.result Updated test results for materialized semi-join queries. internal/mysql-test/suite/i_main/t/subquery.test Added test cases for bug#14272788 and bug#14340367. mysql-test/r/derived.result Updated test results for materialized semi-join queries. mysql-test/r/explain_json_all.result Updated test results for materialized semi-join queries. mysql-test/r/group_by.result Updated test results for materialized semi-join queries. mysql-test/r/subquery_mat_all.result Updated test results for materialized semi-join queries. mysql-test/r/subquery_sj_all.result Updated test results for materialized semi-join queries. mysql-test/r/subquery_sj_all_bka.result Updated test results for materialized semi-join queries. mysql-test/r/subquery_sj_all_bka_nixbnl.result Updated test results for materialized semi-join queries. mysql-test/r/subquery_sj_all_bkaunique.result Updated test results for materialized semi-join queries. mysql-test/r/subquery_sj_mat.result Updated test results for materialized semi-join queries. mysql-test/r/subquery_sj_mat_bka.result Updated test results for materialized semi-join queries. mysql-test/r/subquery_sj_mat_bka_nixbnl.result Updated test results for materialized semi-join queries. mysql-test/r/subquery_sj_mat_bkaunique.result Updated test results for materialized semi-join queries. sql/abstract_query_plan.cc Uses plan_is_const() sql/item.h Added argument functions for Item::compile() sql/item_cmpfunc.cc Added Item_equal::equality_substitution_transformer() and Item_func_eq::equality_substitution_transformer(). Updated comments in Item_equal::get_subst_item(). sql/item_cmpfunc.h Added a couple of new function headers. sql/item_subselect.cc Modified to use "primary_tables" instead of "tables". sql/opt_explain.cc Explain_join::shallow_explain() has been updated to reflect the new layout of JOIN_TAB elements for materialized semi-join plans. Explain_join::explain_table_name() now tests the "derived" pointer to check whether a table is (materialized) derived. Explain_join::explain_select_type() has been added to set the "MATERIALIZED" select_type. Explain_join::explain_id() has been added to set original query block id for a materialized semi-join table. sql/opt_explain_json.cc A DBUG_ASSERT was deleted. sql/opt_range.cc Uses plan_is_single_table() and primary_tables. sql/sql_class.h Class Semijoin_mat_exec: Added members emb_sj_nest, field_count, table_index and mat_fields. table_cols is renamed as subq_exprs. Deleted members tab_ref, in_equality, join_cond and copy_field. Member materialized is replaced with JOIN_TAB::materialized. sql/sql_delete.cc Modified to use "primary_tables" instead of "tables". sql/sql_executor.cc Functions join_read_key2(), join_read_record_no_init(), rr_sequential_and_unpack() and sub_select_sjm() are deleted. Function join_materialize_semijoin() is added. sql/sql_executor.h Function prototypes are removed. sql/sql_join_buffer.cc JOIN_CACHE::calc_record_fields() has a different way to locate first buffered table when materializing semi-joined tables. sql/sql_lex.cc Added new select_type: "MATERIALIZED". sql/sql_lex.h Added new select_type: "MATERIALIZED". sql/sql_optimizer.cc eliminate_item_equal() simplified according to new design. Function JOIN::update_keyuse() has been added. Function JOIN::update_equalities() has been added. Function create_keyuse_for_table() has been added. Function make_cond_after_sjm() has been deleted. In semijoin_types_allow_materialization(), MaterializeScan is allowed even when selected expressions are not fields. In make_join_select(), special handling of materialization has been eliminated. Using "primary_tables" several places instead of "tables". Using new functions plan_in_const() and plan_is_single_table(). Several changes due to new layout of JOIN_TAB array. sql/sql_optimizer.h Added fields primary_tables and sjm_nests to class JOIN. Deleted field outer_tables as it was redundant. Added functions plan_is_const() and plan_is_single_table() to class JOIN. sql/sql_planner.cc No longer trashes the sjm member of sj_nest, as it is needed later in optimization. sql/sql_resolver.cc In JOIN::prepare(), initialize field JOIN::primary_tables. sql/sql_select.cc Function setup_sj_materialization() is replaced with setup_materialized_table(). In JOIN::get_best_combination(), number of JOIN_TAB elements is calculated differently and layout is different. setup_materialized_table() is called as materialized semi-join nests are recognized. New adjustments to the optimized plan are made in JOIN::update_keyuse() and JOIN::update_equalities(). In JOIN::set_semijoin_info(), TABLE::derived_select_number is updated to original query block id for members of materialized semi-join nests. Functions is_cond_sj_in_equality(), remove_sj_conds() and create_subquery_equalities() are deleted. Function JOIN_TAB::get_sj_strategy() is simplified. Function JOIN::cleanup() has been modified for new JOIN_TAB setup. Using "primary_tables" several places instead of "tables". Several changes due to new layout of JOIN_TAB array. sql/sql_select.h Added members "position" and "materialized" in struct st_join_table. sql/sql_test.cc Added test for a NULL table pointer. sql/table.cc TABLE::init() sets derived_select_number to zero. sql/table.h Added field "query_block_id" to save id from original query block when a semi-join nest is formed.
1 parent 03a1bc4 commit 46bdbf7

36 files changed

Lines changed: 6530 additions & 5299 deletions

‎mysql-test/r/derived.result‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1629,9 +1629,10 @@ WHERE derived.vc IN (
16291629
SELECT t2.vc1
16301630
FROM t2 JOIN t3 ON t2.vc2=t3.vc);
16311631
id select_type table type possible_keys key key_len ref rows Extra
1632-
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start materialize; Scan
1633-
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where; End materialize; Using join buffer (Block Nested Loop)
1634-
1 PRIMARY <derived2> ref auto_key0 auto_key0 23 test.t2.vc1 2 NULL
1632+
1 PRIMARY <subquery3> ALL NULL NULL NULL NULL 0 Using where
1633+
1 PRIMARY <derived2> ref auto_key0 auto_key0 23 <subquery3>.vc1 2 NULL
1634+
3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 NULL
1635+
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop)
16351636
2 DERIVED t1 ALL NULL NULL NULL NULL 2 NULL
16361637
SELECT derived.vc
16371638
FROM (SELECT * FROM t1) AS derived

‎mysql-test/r/explain_json_all.result‎

Lines changed: 114 additions & 111 deletions
Original file line numberDiff line numberDiff line change
@@ -806,15 +806,31 @@ t1.a IN (SELECT t3.a FROM t3 WHERE t3.a IN
806806
EXPLAIN
807807
{
808808
"query_block": {
809-
"select_id": 1,
809+
"select_id": 3,
810810
"nested_loop": [
811811
{
812812
"table": {
813813
"table_name": "t1",
814814
"access_type": "ALL",
815815
"rows": 3,
816816
"filtered": 100,
817-
"attached_condition": "(`test`.`t1`.`a` > 0)"
817+
"attached_condition": "((`test`.`t1`.`a` > 0) and (`test`.`t1`.`a` is not null))"
818+
} /* table */
819+
},
820+
{
821+
"table": {
822+
"table_name": "<subquery3>",
823+
"access_type": "eq_ref",
824+
"possible_keys": [
825+
"distinct_key"
826+
] /* possible_keys */,
827+
"key": "distinct_key",
828+
"key_length": "5",
829+
"ref": [
830+
"test.t1.a"
831+
] /* ref */,
832+
"rows": 1,
833+
"filtered": 100
818834
} /* table */
819835
},
820836
{
@@ -823,51 +839,35 @@ EXPLAIN
823839
"access_type": "ALL",
824840
"rows": 3,
825841
"filtered": 100,
826-
"first_match": "t1",
842+
"first_match": "<subquery3>",
827843
"using_join_buffer": "Block Nested Loop",
828844
"attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
829845
} /* table */
830846
},
831847
{
832848
"table": {
833-
"using_temporary_table": true,
834-
"access_type": "eq_ref",
835-
"key": "<auto_key>",
836-
"key_length": "5",
837-
"rows": 1,
838-
"attached_condition": "(`test`.`t1`.`a` = `sj-materialize`.`a`)",
839-
"materialized_from_subquery": {
840-
"query_block": {
841-
"nested_loop": [
842-
{
843-
"table": {
844-
"table_name": "t3",
845-
"access_type": "ALL",
846-
"rows": 3,
847-
"filtered": 100,
848-
"attached_condition": "(`test`.`t3`.`a` > 0)"
849-
} /* table */
850-
},
851-
{
852-
"table": {
853-
"table_name": "t4",
854-
"access_type": "ALL",
855-
"rows": 3,
856-
"filtered": 100,
857-
"using_join_buffer": "Block Nested Loop",
858-
"attached_condition": "(`test`.`t4`.`a` = `test`.`t3`.`a`)"
859-
} /* table */
860-
}
861-
] /* nested_loop */
862-
} /* query_block */
863-
} /* materialized_from_subquery */
849+
"table_name": "t3",
850+
"access_type": "ALL",
851+
"rows": 3,
852+
"filtered": 100,
853+
"attached_condition": "(`test`.`t3`.`a` > 0)"
854+
} /* table */
855+
},
856+
{
857+
"table": {
858+
"table_name": "t4",
859+
"access_type": "ALL",
860+
"rows": 3,
861+
"filtered": 100,
862+
"using_join_buffer": "Block Nested Loop",
863+
"attached_condition": "(`test`.`t4`.`a` = `test`.`t3`.`a`)"
864864
} /* table */
865865
}
866866
] /* nested_loop */
867867
} /* query_block */
868868
}
869869
Warnings:
870-
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t4` join `test`.`t3`) where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`) and (`test`.`t4`.`a` = `test`.`t3`.`a`) and (`test`.`t1`.`a` > 0) and (`test`.`t3`.`a` > 0))
870+
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t4` join `test`.`t3`) semi join (`test`.`t2`) where ((`<subquery3>`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t4`.`a` = `test`.`t3`.`a`) and (`test`.`t3`.`a` > 0) and (`test`.`t1`.`a` > 0))
871871
DROP TABLE t1, t2, t3, t4;
872872
# the same subquery is associated with two different JOIN_TABs
873873
CREATE TABLE t1 (
@@ -994,114 +994,117 @@ EXPLAIN SELECT * FROM t5
994994
WHERE c IN (SELECT t2.c FROM t1 JOIN t2 ON t2.c_key = t1.c_key)
995995
AND c IN (SELECT t4.c FROM t3 JOIN t4 ON t4.c_key = t3.c_key);
996996
id select_type table type possible_keys key key_len ref rows Extra
997-
1 SIMPLE t5 ALL NULL NULL NULL NULL 3 NULL
998-
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Start materialize
999-
1 SIMPLE t1 index c_key c_key 5 NULL 3 Using where; Using index; End materialize; Using join buffer (Block Nested Loop)
1000-
1 SIMPLE t4 ALL NULL NULL NULL NULL 3 Using where; Start materialize
1001-
1 SIMPLE t3 ref c_key c_key 5 test.t4.c_key 1 Using index; End materialize
997+
1 SIMPLE t5 ALL NULL NULL NULL NULL 3 Using where
998+
1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 5 test.t5.c 1 NULL
999+
1 SIMPLE <subquery3> eq_ref distinct_key distinct_key 5 test.t5.c 1 NULL
1000+
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL
1001+
2 MATERIALIZED t1 index c_key c_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
1002+
3 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where
1003+
3 MATERIALIZED t3 ref c_key c_key 5 test.t4.c_key 1 Using index
10021004
EXPLAIN FORMAT=JSON SELECT * FROM t5
10031005
WHERE c IN (SELECT t2.c FROM t1 JOIN t2 ON t2.c_key = t1.c_key)
10041006
AND c IN (SELECT t4.c FROM t3 JOIN t4 ON t4.c_key = t3.c_key);
10051007
EXPLAIN
10061008
{
10071009
"query_block": {
1008-
"select_id": 1,
1010+
"select_id": 3,
10091011
"nested_loop": [
10101012
{
10111013
"table": {
10121014
"table_name": "t5",
10131015
"access_type": "ALL",
10141016
"rows": 3,
1015-
"filtered": 100
1017+
"filtered": 100,
1018+
"attached_condition": "((`test`.`t5`.`c` is not null) and (`test`.`t5`.`c` is not null))"
10161019
} /* table */
10171020
},
10181021
{
10191022
"table": {
1020-
"using_temporary_table": true,
1023+
"table_name": "<subquery2>",
10211024
"access_type": "eq_ref",
1022-
"key": "<auto_key>",
1025+
"possible_keys": [
1026+
"distinct_key"
1027+
] /* possible_keys */,
1028+
"key": "distinct_key",
10231029
"key_length": "5",
1030+
"ref": [
1031+
"test.t5.c"
1032+
] /* ref */,
10241033
"rows": 1,
1025-
"attached_condition": "(`test`.`t5`.`c` = `sj-materialize`.`c`)",
1026-
"materialized_from_subquery": {
1027-
"query_block": {
1028-
"nested_loop": [
1029-
{
1030-
"table": {
1031-
"table_name": "t2",
1032-
"access_type": "ALL",
1033-
"rows": 3,
1034-
"filtered": 100
1035-
} /* table */
1036-
},
1037-
{
1038-
"table": {
1039-
"table_name": "t1",
1040-
"access_type": "index",
1041-
"possible_keys": [
1042-
"c_key"
1043-
] /* possible_keys */,
1044-
"key": "c_key",
1045-
"key_length": "5",
1046-
"rows": 3,
1047-
"filtered": 100,
1048-
"using_index": true,
1049-
"using_join_buffer": "Block Nested Loop",
1050-
"attached_condition": "(`test`.`t1`.`c_key` = `test`.`t2`.`c_key`)"
1051-
} /* table */
1052-
}
1053-
] /* nested_loop */
1054-
} /* query_block */
1055-
} /* materialized_from_subquery */
1034+
"filtered": 100
10561035
} /* table */
10571036
},
10581037
{
10591038
"table": {
1060-
"using_temporary_table": true,
1039+
"table_name": "<subquery3>",
10611040
"access_type": "eq_ref",
1062-
"key": "<auto_key>",
1041+
"possible_keys": [
1042+
"distinct_key"
1043+
] /* possible_keys */,
1044+
"key": "distinct_key",
10631045
"key_length": "5",
1046+
"ref": [
1047+
"test.t5.c"
1048+
] /* ref */,
10641049
"rows": 1,
1065-
"attached_condition": "(`test`.`t5`.`c` = `sj-materialize`.`c`)",
1066-
"materialized_from_subquery": {
1067-
"query_block": {
1068-
"nested_loop": [
1069-
{
1070-
"table": {
1071-
"table_name": "t4",
1072-
"access_type": "ALL",
1073-
"rows": 3,
1074-
"filtered": 100,
1075-
"attached_condition": "(`test`.`t4`.`c_key` is not null)"
1076-
} /* table */
1077-
},
1078-
{
1079-
"table": {
1080-
"table_name": "t3",
1081-
"access_type": "ref",
1082-
"possible_keys": [
1083-
"c_key"
1084-
] /* possible_keys */,
1085-
"key": "c_key",
1086-
"key_length": "5",
1087-
"ref": [
1088-
"test.t4.c_key"
1089-
] /* ref */,
1090-
"rows": 1,
1091-
"filtered": 100,
1092-
"using_index": true
1093-
} /* table */
1094-
}
1095-
] /* nested_loop */
1096-
} /* query_block */
1097-
} /* materialized_from_subquery */
1050+
"filtered": 100
1051+
} /* table */
1052+
},
1053+
{
1054+
"table": {
1055+
"table_name": "t2",
1056+
"access_type": "ALL",
1057+
"rows": 3,
1058+
"filtered": 100
1059+
} /* table */
1060+
},
1061+
{
1062+
"table": {
1063+
"table_name": "t1",
1064+
"access_type": "index",
1065+
"possible_keys": [
1066+
"c_key"
1067+
] /* possible_keys */,
1068+
"key": "c_key",
1069+
"key_length": "5",
1070+
"rows": 3,
1071+
"filtered": 100,
1072+
"using_index": true,
1073+
"using_join_buffer": "Block Nested Loop",
1074+
"attached_condition": "(`test`.`t1`.`c_key` = `test`.`t2`.`c_key`)"
1075+
} /* table */
1076+
},
1077+
{
1078+
"table": {
1079+
"table_name": "t4",
1080+
"access_type": "ALL",
1081+
"rows": 3,
1082+
"filtered": 100,
1083+
"attached_condition": "(`test`.`t4`.`c_key` is not null)"
1084+
} /* table */
1085+
},
1086+
{
1087+
"table": {
1088+
"table_name": "t3",
1089+
"access_type": "ref",
1090+
"possible_keys": [
1091+
"c_key"
1092+
] /* possible_keys */,
1093+
"key": "c_key",
1094+
"key_length": "5",
1095+
"ref": [
1096+
"test.t4.c_key"
1097+
] /* ref */,
1098+
"rows": 1,
1099+
"filtered": 100,
1100+
"using_index": true
10981101
} /* table */
10991102
}
11001103
] /* nested_loop */
11011104
} /* query_block */
11021105
}
11031106
Warnings:
1104-
Note 1003 /* select#1 */ select `test`.`t5`.`c` AS `c` from `test`.`t5` semi join (`test`.`t1` join `test`.`t2`) semi join (`test`.`t3` join `test`.`t4`) where ((`test`.`t3`.`c_key` = `test`.`t4`.`c_key`) and (`test`.`t1`.`c_key` = `test`.`t2`.`c_key`) and (`test`.`t2`.`c` = `test`.`t5`.`c`) and (`test`.`t4`.`c` = `test`.`t5`.`c`))
1107+
Note 1003 /* select#1 */ select `test`.`t5`.`c` AS `c` from `test`.`t5` semi join (`test`.`t1` join `test`.`t2`) semi join (`test`.`t3` join `test`.`t4`) where ((`test`.`t3`.`c_key` = `test`.`t4`.`c_key`) and (`test`.`t1`.`c_key` = `test`.`t2`.`c_key`) and (`<subquery2>`.`c` = `test`.`t5`.`c`) and (`<subquery3>`.`c` = `test`.`t5`.`c`))
11051108
DROP TABLE t1, t2, t3, t4, t5;
11061109
CREATE TABLE t1 (i INT);
11071110
CREATE TABLE t2 (i INT);

‎mysql-test/r/group_by.result‎

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1558,8 +1558,9 @@ id select_type table type possible_keys key key_len ref rows Extra
15581558
EXPLAIN SELECT 1 FROM t1 WHERE a IN
15591559
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
15601560
id select_type table type possible_keys key key_len ref rows Extra
1561-
1 SIMPLE t1 index PRIMARY,i2 PRIMARY 4 NULL 144 Using index
1562-
1 SIMPLE t1 ALL NULL NULL NULL NULL 144 Materialize
1561+
1 SIMPLE t1 index PRIMARY,i2 PRIMARY 4 NULL 144 Using where; Using index
1562+
1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 NULL
1563+
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 144 NULL
15631564
CREATE TABLE t2 (a INT, b INT, KEY(a));
15641565
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
15651566
EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
@@ -1571,8 +1572,9 @@ id select_type table type possible_keys key key_len ref rows Extra
15711572
EXPLAIN SELECT 1 FROM t2 WHERE a IN
15721573
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
15731574
id select_type table type possible_keys key key_len ref rows Extra
1574-
1 SIMPLE t2 index a a 5 NULL 4 Using index
1575-
1 SIMPLE t1 ALL NULL NULL NULL NULL 144 Materialize
1575+
1 SIMPLE t2 index a a 5 NULL 4 Using where; Using index
1576+
1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 4 test.t2.a 1 NULL
1577+
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 144 NULL
15761578
SHOW VARIABLES LIKE 'old';
15771579
Variable_name Value
15781580
old OFF

0 commit comments

Comments
 (0)