revision-id: df73eb8ef56a020e3b8c48e8983a1b70050876c8 (mariadb-5.5.56-199-gdf73eb8ef56) parent(s): 48c3207570d83468dd276110e7e8297708f8cbc9 author: Varun Gupta committer: Varun Gupta timestamp: 2018-05-07 12:02:15 +0530 message: MDEV-15982: Incorrect results when subquery is materialized The issue was that for an IN subquery that had different types for the left expressions and the select items in the subquery we don't form multiple equalities and the rule for SJM that the outer tables should not refer to the inner columns is violated and an equality of type outer_tbl.col = inner_tbl.col is formed. The solution for the issue is to do the copy_back technique (already used when we scan the materialised table) even when we do lookups into the materialised table. This would set the appropriate values to the inner table columns. --- mysql-test/t/subselect_sj2_mat.test | 48 +++++++++++++++++++++++++++++++++++++ sql/opt_subselect.cc | 34 ++++++++++++++++++-------- sql/sql_select.cc | 8 ++++++- 3 files changed, 79 insertions(+), 11 deletions(-) diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test index cfb6c8c2819..4b7c7a03b5a 100644 --- a/mysql-test/t/subselect_sj2_mat.test +++ b/mysql-test/t/subselect_sj2_mat.test @@ -303,3 +303,51 @@ eval $q; eval explain $q; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-15982: Incorrect results when subquery is materialized +--echo # + +CREATE TABLE `t1` (`id` int(32) NOT NULL primary key); +INSERT INTO `t1` VALUES +(45), (46), (47), (48), (49), (50), (51), (52), (53), (54), (55), (56), (57), (58), (59), (60), (61), (62), +(63), (64), (65), (66), (67), (68), (69), (70), (71), (72), (73), (74), (75), (76), (77), (78), (79), (80), +(81), (82), (83), (84), (85), (86), (87), (88), (89), (90), (91), (92),(93),(94),(95),(96), (97), (98), +(99), (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112), (113), +(114), (115), (116), (117), (118), (119), (120), (121), (122), (123), (124), (125), (126), (127), (128), +(129), (130), (131), (132), (133), (134), (135), (136), (137), (138), (139), (140), (141), (142), (143), (144), (145), (146), +(147), (148), (149), (150), (151), (152), (153), (154), (155), (156), (157), (158), (159), (160), (161), +(162), (163), (164), (165), (166), (167), (168), (169), (170), (171), (172), (173), +(174), (175), (176), (177), (178), (179), (180), (181), (182), (183), (2), (3), (4), (5), (6), (19), (35), +(7), (20), (8), (36), (219), (22), (10), (23), (37), (11), (24); + +CREATE TABLE `t2` (`type` int , `id` int(32) NOT NULL primary key); +INSERT INTO `t2` VALUES +(2,2),(2,3),(1,4),(2,5),(1,6),(1,19),(5,7),(1,20),(1,8),(1,21),(1,9), +(1,22),(2,10),(1,23),(2,11),(1,24),(1,12),(1,25),(2,13),(2,26),(2,14), +(2,27),(1,15),(1,28),(3,16),(1,29),(2,17),(1,30),(5,18),(2,1); + +CREATE TABLE `t3` (`ref_id` int(32) unsigned ,`type` varchar(80),`id` int(32) NOT NULL ); +INSERT INTO `t3` VALUES +(1,'incident',31),(2,'faux pas',32), +(5,'oopsies',33),(3,'deniable',34), +(11,'wasntme',35),(10,'wasntme',36), +(17,'faux pas',37),(13,'unlikely',38), +(13,'improbable',39),(14,'incident',40), +(26,'problem',41),(14,'problem',42), +(26,'incident',43),(27,'incident',44); + +explain +SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + +SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + +set optimizer_switch='materialization=off'; + +SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; +set optimizer_switch='materialization=on'; + +DROP TABLE t1,t2,t3; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index c21541c4b97..09d29a03f3a 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -880,7 +880,7 @@ bool subquery_types_allow_materialization(Item_in_subselect *in_subs) elements > tmp_table_max_key_parts()) DBUG_RETURN(FALSE); - in_subs->types_allow_materialization= TRUE; + in_subs->types_allow_materialization= all_are_fields; in_subs->sjm_scan_allowed= all_are_fields; DBUG_PRINT("info",("subquery_types_allow_materialization: ok, allowed")); DBUG_RETURN(TRUE); @@ -3752,6 +3752,7 @@ bool setup_sj_materialization_part1(JOIN_TAB *sjm_tab) HA_POS_ERROR /*rows_limit */, (char*)"sj-materialize"))) DBUG_RETURN(TRUE); /* purecov: inspected */ + sjm->table->map= emb_sj_nest->nested_join->used_tables; sjm->table->file->extra(HA_EXTRA_WRITE_CACHE); sjm->table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); @@ -3864,11 +3865,8 @@ bool setup_sj_materialization_part2(JOIN_TAB *sjm_tab) sjm_tab->type= JT_EQ_REF; sjm_tab->select_cond= sjm->in_equality; } - else - { - /* - We'll be doing full scan of the temptable. - Setup copying of temptable columns back to the record buffers + + /* Setup copying of temptable columns back to the record buffers for their source tables. We need this because IN-equalities refer to the original tables. @@ -3891,7 +3889,20 @@ bool setup_sj_materialization_part2(JOIN_TAB *sjm_tab) At the moment, our solution is to copy back: when we get the next temptable record, we copy its columns to their corresponding columns - in the record buffers for the source tables. + in the record buffers for the source tables. + + Also need these copy back approach when we are making lookup in the temp table as their might + be condition which refer to fields of the inner tables involved in the materilization. + This happens because the equalities created from the ref access(from the keyuse_array) + may create such an equality which involves the inner tables of a semi join nest. + + lets have an example of such a query like + SELECT ot1.id FROM ot1 JOIN ot2 ON ot1.id=ot2.id + WHERE ot1.id IN (SELECT it1.ref_id FROM it1 JOIN it2); + + In this case the left_expr of the IN subquery has different type compared to the right hand side, + so in the above example ot1.id and it1.ref have different types. + */ sjm->copy_field= new Copy_field[sjm->sjm_table_cols.elements]; //it.rewind(); @@ -3954,13 +3965,16 @@ bool setup_sj_materialization_part2(JOIN_TAB *sjm_tab) /* The write_set for source tables must be set up to allow the copying */ bitmap_set_bit(copy_to->table->write_set, copy_to->field_index); } + sjm_tab->read_record.copy_field= sjm->copy_field; + sjm_tab->read_record.copy_field_end= sjm->copy_field + + sjm->sjm_table_cols.elements; + + if (sjm->is_sj_scan) + { sjm_tab->type= JT_ALL; /* Initialize full scan */ sjm_tab->read_first_record= join_read_record_no_init; - sjm_tab->read_record.copy_field= sjm->copy_field; - sjm_tab->read_record.copy_field_end= sjm->copy_field + - sjm->sjm_table_cols.elements; sjm_tab->read_record.read_record= rr_sequential_and_unpack; } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 90bb536c0e2..87c480d58f8 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -17709,7 +17709,13 @@ join_read_const(JOIN_TAB *tab) static int join_read_key(JOIN_TAB *tab) { - return join_read_key2(tab->join->thd, tab, tab->table, &tab->ref); + int error=0; + error= join_read_key2(tab->join->thd, tab, tab->table, &tab->ref); + + READ_RECORD *info= &tab->read_record; + for (Copy_field *cp= info->copy_field; cp != info->copy_field_end; cp++) + (*cp->do_copy)(cp); + return error; }