From: Roy Lyseng Date: January 24 2011 2:19pm Subject: bzr commit into mysql-trunk branch (roy.lyseng:3328) Bug#57525 List-Archive: http://lists.mysql.com/commits/129446 X-Bug: 57525 Message-Id: <20110124141953.C4CC01F2@tyr67.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============2676962975909353754==" --===============2676962975909353754== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/rl136806/mysql/repo/mysql-work5/ based on revid:roy.lyseng@stripped 3328 Roy Lyseng 2011-01-24 Bug#57525: Semijoin transformed subquery with inner grouped subquery gives empty result. This is a followup to bug#31480, which attempted to fix resolved information in subqueries as part of semijoin transformation. The specific problem here is that the inner subquery (which is not transformed) contains an outer reference to the outer-most block in its HAVING clause. The HAVING clause is represented by Item_ref objects, and these are not re-resolved properly through fix_after_pullout(). The solution lies in realizing that Item_ref objects contain a complete set of resolution data structures (ie depended_from, name resolution context), and that it may point to an Item object with it's own set of resolution data structures. Hence, we implement fix_after_pullout() for Item_ref by first calling fix_after_pullout() on the referenced object and then for itself. The above fix revealed another problem: The offending query failed in prepared statement mode. The reason is that the depended_from field in Item_ref is reset in cleanup() and not restored at the next fix_fields() call. The problem is fixed by not resetting the depended_from flag. This is an acceptable solution, as long as transformations are run only on the first execution of a query. There is also another followup to bug#31480: A - t1 \ B - t2 \ C - t3 \ D - t4 where t1.x=t4.y The above figure describes an outer query expression (select_lex A) with 3 nested subqueries represented by select_lex B, C and D. The innermost subquery (D) contains a reference to a table t1 in the outermost block (A). The original resolver marked the subquery containing query expression B as using table t1, and the subqueries containing query expressions C and D with OUTER_REF_TABLE_BIT. The code in bug#31480 failed to preserve all outer references when calling fix_after_pullout(). mysql-test/r/optimizer_switch.result Updated with correct result and plan for query that exposed bug. mysql-test/t/optimizer_switch.test Warnings about wrong results removed. sql/item.cc A common implementation of fix_after_pullout() for all Item_ref classes has been made. The exception is Item_outer_ref which we still have no test case for. Item_ref::fix_after_pullout() now calls fix_after_pullout() on the referenced item, and then it calls Item_ident::fix_after_pullout() on itself. Item_field::fix_after_pullout() is also moved to class Item_ident, which is parent class for both Item_field and Item_ref. A new function Item_ref::resolved_used_tables() was needed. sql/item.h Some adjustments to function prototypes, see sql/item.cc. sql/item_subselect.h One changed friend declaration. modified: mysql-test/r/optimizer_switch.result mysql-test/t/optimizer_switch.test sql/item.cc sql/item.h sql/item_subselect.h === modified file 'mysql-test/r/optimizer_switch.result' --- a/mysql-test/r/optimizer_switch.result 2010-11-30 18:16:43 +0000 +++ b/mysql-test/r/optimizer_switch.result 2011-01-24 14:17:03 +0000 @@ -293,14 +293,13 @@ a b 3 20 2 30 set @@optimizer_switch='materialization=off,semijoin=on'; -# The query result with semijoin is WRONG EXPLAIN SELECT * FROM t1 AS ta WHERE ta.a IN (SELECT c FROM t2 AS tb WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc GROUP BY f HAVING ta.a=tc.f)); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY tb ALL NULL NULL NULL NULL 6 Using where; Start temporary +1 PRIMARY tb ALL NULL NULL NULL NULL 6 Start temporary 1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, incremental buffers) 3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 6 Using temporary; Using filesort SELECT * FROM t1 AS ta @@ -309,6 +308,10 @@ WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 GROUP BY f HAVING ta.a=tc.f)); a b +2 10 +2 20 +3 20 +2 30 # Subquery with ORDER BY and LIMIT set @@optimizer_switch='materialization=off,semijoin=off'; # NOTE: The ordered subquery should have a LIMIT clause to make sense @@ -332,7 +335,6 @@ a b 2 30 4 40 set @@optimizer_switch='materialization=off,semijoin=on'; -# The query result with semijoin is WRONG EXPLAIN SELECT * FROM t1 AS ta WHERE ta.a IN (SELECT c FROM t2 AS tb WHERE tb.d IN (SELECT g FROM t4 as tc === modified file 'mysql-test/t/optimizer_switch.test' --- a/mysql-test/t/optimizer_switch.test 2010-10-15 10:32:50 +0000 +++ b/mysql-test/t/optimizer_switch.test 2011-01-24 14:17:03 +0000 @@ -280,8 +280,6 @@ eval $query; set @@optimizer_switch='materialization=off,semijoin=on'; ---echo # The query result with semijoin is WRONG - eval EXPLAIN $query; eval $query; @@ -303,8 +301,6 @@ eval $query; set @@optimizer_switch='materialization=off,semijoin=on'; ---echo # The query result with semijoin is WRONG - eval EXPLAIN $query; eval $query; === modified file 'sql/item.cc' --- a/sql/item.cc 2011-01-12 17:24:53 +0000 +++ b/sql/item.cc 2011-01-24 14:17:03 +0000 @@ -649,7 +649,6 @@ void Item_ident::cleanup() db_name= orig_db_name; table_name= orig_table_name; field_name= orig_field_name; - depended_from= 0; DBUG_VOID_RETURN; } @@ -2310,7 +2309,7 @@ table_map Item_field::resolved_used_tabl return field->table->map; } -void Item_field::fix_after_pullout(st_select_lex *parent_select, +void Item_ident::fix_after_pullout(st_select_lex *parent_select, st_select_lex *removed_select, Item **ref) { @@ -2350,17 +2349,16 @@ void Item_field::fix_after_pullout(st_se */ st_select_lex *child_select= context->select_lex; - if (child_select->outer_select() != depended_from) + while (child_select->outer_select() != depended_from) { /* The subquery on this level is outer-correlated with respect to the field */ Item_subselect *subq_predicate= child_select->master_unit()->item; - subq_predicate->used_tables_cache|= OUTER_REF_TABLE_BIT; - } - while (child_select->outer_select() != depended_from) + subq_predicate->used_tables_cache|= OUTER_REF_TABLE_BIT; child_select= child_select->outer_select(); + } /* child_select is select_lex immediately inner to the depended_from level. @@ -6509,6 +6507,13 @@ void Item_ref::set_properties() } +table_map Item_ref::resolved_used_tables() const +{ + DBUG_ASSERT((*ref)->type() == FIELD_ITEM); + return ((Item_field*)(*ref))->resolved_used_tables(); +} + + void Item_ref::cleanup() { DBUG_ENTER("Item_ref::cleanup"); @@ -6885,29 +6890,12 @@ void Item_ref::fix_after_pullout(st_sele st_select_lex *removed_select, Item **ref_arg) { - // @todo: Find an actual test case where depended_from == new_parent. - DBUG_ASSERT(depended_from != parent_select); - if (depended_from == parent_select) - depended_from= NULL; -} - -void Item_direct_view_ref::fix_after_pullout(st_select_lex *parent_select, - st_select_lex *removed_select, - Item **refptr) -{ - DBUG_EXECUTE("where", - print_where(*refptr, - "Item_direct_view_ref::fix_after_pullout", - QT_ORDINARY);); - (*ref)->fix_after_pullout(parent_select, removed_select, ref); - // @todo: Find an actual test case where depended_from == parent_select. - DBUG_ASSERT(depended_from != parent_select); - if (depended_from == parent_select) - depended_from= NULL; + Item_ident::fix_after_pullout(parent_select, removed_select, ref_arg); } + /** Compare two view column references for equality. === modified file 'sql/item.h' --- a/sql/item.h 2011-01-10 16:37:47 +0000 +++ b/sql/item.h 2011-01-24 14:17:03 +0000 @@ -1675,7 +1675,13 @@ public: const char *field_name_arg); Item_ident(THD *thd, Item_ident *item); Item_ident(TABLE_LIST *view_arg, const char *field_name_arg); + /* + Return used table information for the level on which this table is resolved. + */ + virtual table_map resolved_used_tables() const= 0; const char *full_name() const; + virtual void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); void cleanup(); bool remove_dependence_processor(uchar * arg); virtual void print(String *str, enum_query_type query_type); @@ -1761,16 +1767,11 @@ public: bool send(Protocol *protocol, String *str_arg); void reset_field(Field *f); bool fix_fields(THD *, Item **); - void fix_after_pullout(st_select_lex *parent_select, - st_select_lex *removed_select, Item **ref); void make_field(Send_field *tmp_field); int save_in_field(Field *field,bool no_conversions); void save_org_in_field(Field *field); table_map used_tables() const; - /* - Return used table information for the level on which this table is resolved. - */ - table_map resolved_used_tables() const; + virtual table_map resolved_used_tables() const; enum Item_result result_type () const { return field->result_type(); @@ -2604,6 +2605,7 @@ public: if (!depended_from) (*ref)->update_used_tables(); } + virtual table_map resolved_used_tables() const; table_map not_null_tables() const { return (*ref)->not_null_tables(); } void set_result_field(Field *field) { result_field= field; } bool is_result_field() { return 1; } @@ -2726,8 +2728,6 @@ public: {} bool fix_fields(THD *, Item **); - void fix_after_pullout(st_select_lex *parent_select, - st_select_lex *removed_select, Item **ref); bool eq(const Item *item, bool binary_cmp) const; Item *get_tmp_table_item(THD *thd) { === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2010-12-14 11:15:13 +0000 +++ b/sql/item_subselect.h 2011-01-24 14:17:03 +0000 @@ -171,7 +171,7 @@ public: friend bool Item_field::fix_fields(THD *, Item **); friend int Item_field::fix_outer_field(THD *, Field **, Item **); friend bool Item_ref::fix_fields(THD *, Item **); - friend void Item_field::fix_after_pullout(st_select_lex *parent_select, + friend void Item_ident::fix_after_pullout(st_select_lex *parent_select, st_select_lex *removed_select, Item **ref); friend void mark_select_range_as_dependent(THD*, --===============2676962975909353754== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/roy.lyseng@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: roy.lyseng@stripped # target_branch: file:///home/rl136806/mysql/repo/mysql-work5/ # testament_sha1: ddd087c6366ae68266e00c41bb1a3debee20e7b4 # timestamp: 2011-01-24 15:19:53 +0100 # base_revision_id: roy.lyseng@stripped\ # di2zbjgmv7im7oxk # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWZzBwEUABfH/gHSwAAJ49/// f+ffpL////pgDgu7sZ0ru+B5BSQLb3vvvnZnyn222PZpsrVQq1ipTQ2yEkiaBKfpT1HpPMU9U/VP CPJRk0aeSabU9QAAAD1BKITJimaBDUjQ9RiAyA0ADQDRoGjTQaaTJpDRNNTEaENDJ6IA0AAAAAAE iIQKYKemmKn5NMhE9U8U0yMACeKMjI9QDQiaobUDQaaBoAYmgaDTEBoaMgADQEkQjTQCZAJpoJtV PZRkmnkgAAAAaCUkYWTKx39zBVZ1SrfEaP7YFueid15xncZraKIsE8j4OOTkp+RbfdG25Di3YEG9 imGdbvex/OyK8ZynG7PjzVpwWWtM+tQU3Qb+destxzfF/rXgMGhSwxZcTEu+IvYebdUAZ7PvjTls u1G9nW+DAnK7GJ7/KPk1irwcFibmW9eyXask7srzhqSChhEqSGZIg/75qJp4RVTf4Orb5d0OhFyO KGNjYhttDYGX2e9C7emmdt891rOaRELfMHwPm7sCehdatm7nWYXSsJsh99LpWLwtfiZWnLK530dX ExVhddFHWCPf26zc308CdfLlXs2WoO4j/PdwxrnH5adjdg7ApRU4p12HrnEMcDIMU6Iw0Y5AkTaI 0uvC+eVBdW0TF5FWn/TfC4+wTfZQCukqHwIrG4Q9ZlYdBojFsV1H4kXN7PcxsoVMJduyWOVJR27J TnIbbDy2iFkuC0cNnMyYakmMG6YryIH49VJHK3DBcLxVIyihzzchVFuY4IDdSM3JSk30yIOnBp7r PmZauUh1xsdsS95dZOF4kpvm21QpXQc5xOjW8TEVGXQtoleWYIGwpQVaAprSQmg26XxkHFpaElmA a/RbkHjLqS0UR08fE6+fI/USlfXNToOM1KOjBPyU6UEbJIsCj7Y3FuPFEibHw9lhtUbT75z+EAce MJfLywxilzjR/SflZeiBR6Bf7Pd0jl3yH05WA0hu87xioJiR9R19yZJW3j+KpG1twumpfUZeR5yX 14FxIeyl07sOVpo1ZPTr0mW5F7W56bURCTCDLKIzDF0Eg1D/rBw28uIrQ0VDKl6IqPHHrdAz8rhC qYFJkPVBvtPlkVS6BQcLHRQGIIZIGEKnM6PIsO4riWCU0QC8YHnSwIVkI3CmRR6ESy1KavmDnawg TdE16TwSN8UTJRL7LWjmMKKOnetZA4qcFdyBAUVAOFjMBgTfvbZm8384VghgpJiSr78sjqyjGkza RQGTd/K/nVwle91Loa0L6qiCUSvYJOmkqz6c8bbZutt0ZMYOOFTGBS4SYL4pMeB85+O/wOgZGLGq MzMMShoipbjlKImD7sR+q66lNWc733XZGAy6mwEr6BIYpeNMoMhiRCjLVkriRcVoDZw5zkTVxJyD KQMVtvcC09i88/EwNTFS0OC1ZbkLG4xvdJrMLnYP0z2TeIkYzz0DphlAOo2AvwR0ihq0h99MoFgs 9XUYVsdCJXGssRmwSJHidVwwGXvhu2/8MzjiWNbxre/KESDw0yUyzQsBTQFHEuFNETEpYZunAeRa LNxutzL1GAprKzelaRIJivUE2aHNuFSRAwLaYJvJuMP8QyndnrxqdcujdNVBtQwebW3jsP3yehxr LmkXq+YX5KFFuL78MCozyqC1eGb4mc4QcN3USus4fPBzXE95tKek5+Z7n28bdNnCWmbQjsGhVhwK phVyzHoFoyquoi9b5d+K2njFGZAeaO3K2ClxI5JX2WEsCAxEi+feJVLS6bXNpHSQUAGGHM9SHVNO JlAhZZQyGMo1D5DnoPTAzuolNoTN8Kjr5cPE9l9CxoaGzdjrfrVF4wavWs3jPIlKJvK9RA0ijjRo gD6lGjpMrlUPQydPVtE6DCgAwvGXYy7SE5ntKEac5dxk9NDTL0ZXEsHvMTGxVgcmXlkYFfHi5kWG wvEo6ZqrArPNq+Gcoo77NzoRDvicVzBNrIzXbkEcxJEMw2hAzQEvAFLC3VvGXeFU/X7nWIwoZY2Z r0OQSXWeJFNSkzdvO94gxRCLLWsf3qBYIynxAEhBCAyz0sbiwnRjiDkX13O9iYU3yHx+BvLLmOHJ 6UUAmhOqQZVOomsGix5qnrlpxdWV0lJZ11JttjB5vMH/ZxHg/e/y0769nihfL4KPQHE6eSIVUMCB MI+5CmEQw9numiuG2JVCQIkF7cQPGdy85sHc2bACebTbh24yeBP4MO3fB6SxLpI6BvpwyEQpp1P1 F5Q66CequIFRRDa9gT+S6DFBSoR7CSkVEJbTBoV0DGFReiBSrnbOL5L9VKUsTQk7lZka1jCbxfAt HC9Xz5X77P3bs5/M0FT21DlxTIMRLEu3e0gmxq2cBL+Qc1eYIJTFY9wk4XcB5sEANPqQL5sldDyZ Zm3vtW33HbCciJIxwvYOx+1HKeLam5asX+99UNqA934efC1pQkiIkMYJI1fU0jq0XIR1HAwk0D8D tILGYfhIomCXLbExmTJmH4IdczC6/eYH6WLZncXJcSiUcY44zSJCdgUzYfcpsMTdq3cV2V3h3Xol kxY248ihqsRUjme0bVXxcXdLjIgps9O/Ywt5FeECrOmt3E3kspqqJZHfNXuZBTed2uJxyLJCt5aT y9l1Nkb6dOwjI5GB0GeqNqPhtY3Mn2IoAwbt+2FY/carhltJViBz3vYW1Wua60ibW40rN7S8qniC oFEBCExXX0Obv1O9rxCKLLaSgu8+ac3U/H+0HRGkEa+saQ2JDbj7JwTHbopxP1MrDiGdARHiaOwM geBmngBEPVzmXba3xUGr8HAGAirUetwyvqm8YpFLeOJfU5qKdCtYSvgjsyFmyqSfCAqKjP97qJPF gJOLIJ0oLNNlNTMwyYLBSuawwD066AxcaDux6C8uLUNowwKmo7RSOiNwtGfep+T0NYf+RKIRnYd7 GNTCgNF41ulIpBwCTANKFWSqR0Dg0tdTSpIItU4DvV9Bn9vx8vpVFywtn8VM8PFdmGGYkTQcaG2d oJ43IbXtBtjbGMbGbfPATSZaKuhNjadW7XX8yh4HkJckgqi9d/cdtHv8kRVlVeYAbnkRGF7TdYWV 8NRSUD0lpJcaYRWh7GjDZHDowEItiaGXL2tjaTTFunbvuytKAmYYBnOgUFsGQzDgMtAG/f8uI47j u5FFCW/dRAdqhDRANrL+S8pzoJby/XxW6AuNKgOrHv7C1F/tDsB8rDGvaYN7kbROyIBEkLYMa1bK g3+/UXpQMGxbUUEiBEBEiCczorDE+GoViMjpNTY6MbUK4cfQuLLEBIkGhB+k0l5Z9ubkDokFram0 xsSGg0pwxIzeYHgl4xXQKhZY1TIHNwLlCZ6qlVnm1CNattFaUjWmKXraV/+uPGmOLjJw4eTlRMIh jZrkpL8XA2y0XPYMO4sQizHm0o3CXULLqGoaTsA0R99r9ygwuV1YQ2oSD5r50s02hXoqPWqqtR1B ovUtJQhptQwJhaPVu2ne0N6Hsu3rOtWddhzrNRc+a7yO7ExSVYWJI+bK9K1iVrgwMIdvS+col0xE V6fS2234mZLoJHWBqeXSqDGtU9K7AZHSTjlnrYlmhXB5/Y/KRcFsaz4r2PnakHgHZWc0jwKeTdWR 9V4XpP9UouWaeYKSKlWGwnmVjnWcE+DGQUcQ0EwYtVGV4DvzQoFhdYePFXFBLp6n2TO/ahUPa5fD mlrQ36zUHWuTIacijrmfQwoshpIoJK/UEX+apA8VrrTIXJMKiycu5FRBnkw+IcqRbHvafjFE7NsK euBX4Ljm9BK0GktMoGcFoKfvWdaWBztiufLMOJ6iZD/L6z6jZxPgZmsYBmb4yYCTG3smSvP13ltM K0juugtW6SCo/1cWijRE5qCgwoeXwj27U8OD2KzoevrpBLXuA7AwfRDEaYIocxrmiNvDjIViMC6Z uAaKJ/K4N7LiRV4wik+SOTbbrnWkPPLILi+sQtT8Y2EzEKU+tfrGl5NtdoEICNQdEHAibNhb2fAj CBubBpP7WwlGBlYpzhWqqUlZCtgVHVTBa9cA2zMEbxj+vnMJK90Sie4GDAIaIaQsYpZUCkRDWzvf WJXmMDJfuKyd+H7yYhhAwPVh37wb2QnXkot1ORfEQ9LGCCQOMvirYW92PQyAjxxUyLjwd1OBXAog dBepSjxZoyL5YRe+JBKx21iG0qIF/wv2GKYyDAF1tIHv9BL0X52ppehcbUUc+BtrlrkJfTiEDh0E /2eowQjQG54LcJYepW1MmliRsYo0KOD+fMEALnWtwmyaeSC3Sb38zL0cBUBHEmm1Gi016SSMrMRb jdZ74CURta1gRC47GBBC1Ki4pyDzi6HZsOfhCMWVlSQPoXyLhjLyNS05/yqSDqLQSyuVEl0x4ray z/i7kinChITmDgIo --===============2676962975909353754==--