An error occurred fetching the project authors.
  1. 03 Nov, 2010 1 commit
    • Igor Babaev's avatar
      Fixed LP bug #664594 and other bugs leading to invalid execution · 3d8c7a2c
      Igor Babaev authored
      plans or wrong results due to the fact that JOIN_CACHE functions
      ignored the possibility of interleaving materialized semijoin 
      tables with tables whose records were stored in join buffers.
      This fixes would become mostly unnecessary if the new code of
      mwl 90 was merged into 5.3 right now.
      Yet the fix the code of optimize_wo_join_buffering was needed
      in any case.
      
      3d8c7a2c
  2. 18 Oct, 2010 1 commit
  3. 28 Sep, 2010 1 commit
    • Igor Babaev's avatar
      Fixed bug #52636. · ad4271d0
      Igor Babaev authored
      Applied the fix for bug #47217 from the mysql-6.0 codebase.
      The patch adds not null predicates generated for the left parts
      of the equality predicates used for ref accesses. This is done
      for such predicates both in where conditions and on conditions.
      For the where conditions the not null predicates were generated
      but in 5.0/5.1 they actually never were used due to some lame
      merge from 4.1 to 5.0. The fix for bug #47217 made these 
      predicates to be used in the condition pushed to the tables.
      Yet only this patch generates not null predicates for equality
      predicated from on conditions of outer joins.
      This patch introduces a performance regression that can be
      observed on a test case from null_key.test. The regression
      will disappear after the fix for bug #57024 from mariadb-5.1
      is pulled into mariadb-5.3.
      The patch contains many changes in the outputs of the EXPLAIN 
      commands since generated not null predicates are considered as
      parts of the conditions pushed to join tables and may add
      'Usingwhere' in some rows of EXPLAINs where there used
      to be no such comments.
      ad4271d0
  4. 06 Sep, 2010 1 commit
    • unknown's avatar
      Fixed LP BUG#615760: Check on double cache assignment added into the transformation methods. · db43b21c
      unknown authored
      Cache parameters print added in EXPLAIN EXTENDED output.
      
      mysql-test/r/compare.result:
        Cache parameters print added in EXPLAIN EXTENDED output.
      mysql-test/r/group_by.result:
        Cache parameters print added in EXPLAIN EXTENDED output.
      mysql-test/r/subselect.result:
        Cache parameters print added in EXPLAIN EXTENDED output.
      mysql-test/r/subselect3.result:
        Cache parameters print added in EXPLAIN EXTENDED output.
      mysql-test/r/subselect3_jcl6.result:
        Cache parameters print added in EXPLAIN EXTENDED output.
      mysql-test/r/subselect4.result:
        Cache parameters print added in EXPLAIN EXTENDED output.
      mysql-test/r/subselect_cache.result:
        Added test suite for LP BUG#615760
      mysql-test/r/subselect_mat.result:
        Cache parameters print added in EXPLAIN EXTENDED output.
      mysql-test/r/subselect_no_mat.result:
        Cache parameters print added in EXPLAIN EXTENDED output.
      mysql-test/r/subselect_no_opts.result:
        Cache parameters print added in EXPLAIN EXTENDED output.
      mysql-test/r/subselect_no_semijoin.result:
        Cache parameters print added in EXPLAIN EXTENDED output.
      mysql-test/r/subselect_sj.result:
        Cache parameters print added in EXPLAIN EXTENDED output.
      mysql-test/r/subselect_sj_jcl6.result:
        Cache parameters print added in EXPLAIN EXTENDED output.
      mysql-test/suite/pbxt/r/subselect.result:
        Cache parameters print added in EXPLAIN EXTENDED output.
      mysql-test/t/subselect_cache.test:
        Cache parameters print added in EXPLAIN EXTENDED output.
      sql/item.cc:
        Item::set_expr_cache result fixed according to its description.
        
        Cache parameters print added in EXPLAIN EXTENDED output.
      sql/item.h:
        Cache parameters print added in EXPLAIN EXTENDED output.
      sql/item_cmpfunc.cc:
        Check on double cache assignment added into the transformation methods.
      sql/item_cmpfunc.h:
        Check on double cache assignment added into the transformation methods.
      sql/item_subselect.cc:
        Check on double cache assignment added into the transformation methods.
      sql/item_subselect.h:
        Check on double cache assignment added into the transformation methods.
      sql/sql_expression_cache.cc:
        Cache parameters print added.
      sql/sql_expression_cache.h:
        Cache parameters print added.
      sql/sql_select.cc:
        Removed unused method (now it is impossible to make double transformation with the cache).
      sql/sql_select.h:
        Removed unused method.
      db43b21c
  5. 31 Aug, 2010 2 commits
    • Igor Babaev's avatar
    • unknown's avatar
      LP BUG#615752 fix. Expression cache added to EXPLAIN EXTENDED output. · 422f834f
      unknown authored
      mysql-test/r/compare.result:
        Expression cache added to EXPLAIN EXTENDED output.
      mysql-test/r/explain.result:
        Expression cache added to EXPLAIN EXTENDED output.
      mysql-test/r/group_by.result:
        Expression cache added to EXPLAIN EXTENDED output.
      mysql-test/r/subselect.result:
        Expression cache added to EXPLAIN EXTENDED output.
      mysql-test/r/subselect3.result:
        Expression cache added to EXPLAIN EXTENDED output.
      mysql-test/r/subselect3_jcl6.result:
        Expression cache added to EXPLAIN EXTENDED output.
      mysql-test/r/subselect4.result:
        Expression cache added to EXPLAIN EXTENDED output.
      mysql-test/r/subselect_mat.result:
        Expression cache added to EXPLAIN EXTENDED output.
      mysql-test/r/subselect_no_mat.result:
        Expression cache added to EXPLAIN EXTENDED output.
      mysql-test/r/subselect_no_opts.result:
        Expression cache added to EXPLAIN EXTENDED output.
      mysql-test/r/subselect_no_semijoin.result:
        Expression cache added to EXPLAIN EXTENDED output.
      mysql-test/r/subselect_sj.result:
        Expression cache added to EXPLAIN EXTENDED output.
      mysql-test/r/subselect_sj_jcl6.result:
        Expression cache added to EXPLAIN EXTENDED output.
      sql/item.h:
        Expression cache added to EXPLAIN EXTENDED output.
      422f834f
  6. 10 Jul, 2010 1 commit
    • unknown's avatar
      Subquery cache (MWL#66) added. · ea1b32a3
      unknown authored
      libmysqld/Makefile.am:
        The new file added.
      mysql-test/r/index_merge_myisam.result:
        subquery_cache optimization option added.
      mysql-test/r/myisam_mrr.result:
        subquery_cache optimization option added.
      mysql-test/r/subquery_cache.result:
        The subquery cache tests added.
      mysql-test/r/subselect3.result:
        Subquery cache switched off to avoid changing read statistics.
      mysql-test/r/subselect3_jcl6.result:
        Subquery cache switched off to avoid changing read statistics.
      mysql-test/r/subselect_no_mat.result:
        subquery_cache optimization option added.
      mysql-test/r/subselect_no_opts.result:
        subquery_cache optimization option added.
      mysql-test/r/subselect_no_semijoin.result:
        subquery_cache optimization option added.
      mysql-test/r/subselect_sj.result:
        subquery_cache optimization option added.
      mysql-test/r/subselect_sj_jcl6.result:
        subquery_cache optimization option added.
      mysql-test/t/subquery_cache.test:
        The subquery cache tests added.
      mysql-test/t/subselect3.test:
        Subquery cache switched off to avoid changing read statistics.
      sql/CMakeLists.txt:
        The new file added.
      sql/Makefile.am:
        The new files added.
      sql/item.cc:
        Expression cache item (Item_cache_wrapper) added.
        Item_ref and Item_field fixed for correct usage of result field and fast resolwing in SP.
      sql/item.h:
        Expression cache item (Item_cache_wrapper) added.
        Item_ref and Item_field fixed for correct usage of result field and fast resolwing in SP.
      sql/item_cmpfunc.cc:
        Subquery cache added.
      sql/item_cmpfunc.h:
        Subquery cache added.
      sql/item_subselect.cc:
        Subquery cache added.
      sql/item_subselect.h:
        Subquery cache added.
      sql/item_sum.cc:
        Registration of subquery parameters added.
      sql/mysql_priv.h:
        subquery_cache optimization option added.
      sql/mysqld.cc:
        subquery_cache optimization option added.
      sql/opt_range.cc:
        Fix due to subquery cache.
      sql/opt_subselect.cc:
        Parameters of the function cahnged.
      sql/procedure.h:
        .h file guard added.
      sql/sql_base.cc:
        Registration of subquery parameters added.
      sql/sql_class.cc:
        Option to allow add indeces to temporary table.
      sql/sql_class.h:
        Item iterators added.
        Option to allow add indeces to temporary table.
      sql/sql_expression_cache.cc:
        Expression cache for caching subqueries added.
      sql/sql_expression_cache.h:
        Expression cache for caching subqueries added.
      sql/sql_lex.cc:
        Registration of subquery parameters added.
      sql/sql_lex.h:
        Registration of subqueries and subquery parameters added.
      sql/sql_select.cc:
        Subquery cache added.
      sql/sql_select.h:
        Subquery cache added.
      sql/sql_union.cc:
        A new parameter to the function added.
      sql/sql_update.cc:
        A new parameter to the function added.
      sql/table.cc:
        Procedures to manage temporarty tables index added.
      sql/table.h:
        Procedures to manage temporarty tables index added.
      storage/maria/ha_maria.cc:
        Fix of handler to allow destoy a table in case of error during the table creation.
      storage/maria/ha_maria.h:
        .h file guard added.
      storage/myisam/ha_myisam.cc:
        Fix of handler to allow destoy a table in case of error during the table creation.
      ea1b32a3
  7. 29 Mar, 2010 1 commit
  8. 11 Mar, 2010 1 commit
    • unknown's avatar
      MWL#68 Subquery optimization: Efficient NOT IN execution with NULLs · be4b955f
      unknown authored
      This patch does three things:
      - It adds the possibility to force the execution of top-level [NOT] IN
        subquery predicates via the IN=>EXISTS transformation. This is done by
        setting both optimizer switches partial_match_rowid_merge and
        partial_match_table_scan to "off".
      - It adjusts all test cases where the complete optimizer_switch is
        selected because now we have two more switches.
      - For those test cases where the plan changes because of the new available
        strategies, we switch off both partial match strategies in order to
        force the "old" IN=>EXISTS strategy. This is done because most of these
        test cases specifically test bugs in this strategy.
      
      sql/opt_subselect.cc:
        Adds the possibility to force the execution of top-level [NOT] IN
        subquery predicates via the IN=>EXISTS transformation. This is done by
        setting both optimizer switches partial_match_rowid_merge and
        partial_match_table_scan to "off".
      be4b955f
  9. 09 Mar, 2010 1 commit
    • Davi Arnaut's avatar
      Bug#40277: SHOW CREATE VIEW returns invalid SQL · c383679b
      Davi Arnaut authored
      The problem is that not all column names retrieved from a SELECT
      statement can be used as view column names due to length and format
      restrictions. The server failed to properly check the conformity
      of those automatically generated column names before storing the
      final view definition on disk.
      
      Since columns retrieved from a SELECT statement can be anything
      ranging from functions to constants values of any format and length,
      the solution is to rewrite to a pre-defined format any names that
      are not acceptable as a view column name.
      
      The name is rewritten to "Name_exp_%u" where %u translates to the
      position of the column. To avoid this conversion scheme, define
      explict names for the view columns via the column_list clause.
      Also, aliases are now only generated for top level statements.
      
      mysql-test/include/view_alias.inc:
        Add test case for Bug#40277
      mysql-test/r/compare.result:
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
      mysql-test/r/group_by.result:
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
      mysql-test/r/ps.result:
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
      mysql-test/r/subselect.result:
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
      mysql-test/r/subselect3.result:
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
      mysql-test/r/type_datetime.result:
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
      mysql-test/r/union.result:
        Bug#40277: SHOW CREATE VIEW returns invalid SQL
      mysql-test/r/view.result:
        Add test case result for Bug#40277
      mysql-test/r/view_alias.result:
        Add test case result for Bug#40277
      mysql-test/t/view_alias.test:
        Add test case for Bug#40277
      sql/sql_view.cc:
        Check if auto generated column names are conforming. Also, the
        make_unique_view_field_name function is not used as it uses the
        original name to construct a new one, which does not work if the
        name is invalid.
      c383679b
  10. 17 Feb, 2010 1 commit
  11. 17 Jan, 2010 1 commit
  12. 15 Dec, 2009 1 commit
    • Sergey Petrunya's avatar
      Backport into MariaDB-5.2 the following: · 3bb037aa
      Sergey Petrunya authored
      WL#2474 "Multi Range Read: Change the default MRR implementation to implement new MRR interface"
      WL#2475 "Batched range read functions for MyISAM/InnoDb"
              "Index condition pushdown for MyISAM/InnoDB"
      Igor's fix from sp1r-igor@olga.mysql.com-20080330055902-07614:
        There could be observed the following problems:
        1. EXPLAIN did not mention pushdown conditions from on expressions in the 
        'extra' column.  As a result if a query had no where conditions pushed 
        down to a table, but had on conditions pushed to this table the 'extra' 
        column in the EXPLAIN for the table missed 'using where'.
        2. Conditions for ref access were not eliminated from on expressions 
        though such conditions were eliminated from the where condition.
      3bb037aa
  13. 03 Nov, 2009 1 commit
    • Jorgen Loland's avatar
      Bug#48177 - SELECTs with NOT IN subqueries containing NULL · e11daabf
      Jorgen Loland authored
                  values return too many records
      
      WHERE clauses with "outer_value_list NOT IN subselect" were
      handled incorrectly if the outer value list contained multiple 
      items where at least one of these could be NULL. The first 
      outer record with NULL value was handled correctly, but if a 
      second record with NULL value existed, the optimizer would 
      choose to reuse the result it got on the last execution of the 
      subselect. This is incorrect if the outer value list has 
      multiple items.
           
      The fix is to make Item_in_optimizer::val_int (in 
      item_cmpfunc.cc) reuse the result of the latest execution
      for NULL values only if all values in the outer_value_list 
      are NULL.
      
      
      mysql-test/r/subselect3.result:
        Added test for BUG#48177
      mysql-test/t/subselect3.test:
        Added test for BUG#48177
      sql/item_cmpfunc.cc:
        Make Item_in_optimizer::val_int (in item_cmpfunc.cc) reuse the result of the latest execution for NULL values only if all values in the outer_value_list are NULL.
      e11daabf
  14. 16 Sep, 2009 1 commit
  15. 21 Jul, 2009 1 commit
    • MySQL Build Team's avatar
      Backport into build-200907211706-5.0.82sp1 · 342b5f2e
      MySQL Build Team authored
      > ------------------------------------------------------------
      > revno: 2733
      > revision-id: gshchepa@mysql.com-20090430192037-9p1etcynkglte2j3
      > parent: aelkin@mysql.com-20090430143246-zfqaz0t7uoluzdz2
      > committer: Gleb Shchepa <gshchepa@mysql.com>
      > branch nick: mysql-5.0-bugteam
      > timestamp: Fri 2009-05-01 00:20:37 +0500
      > message:
      >   Bug #37362: Crash in do_field_eq
      >   
      >   EXPLAIN EXTENDED of nested query containing a error:
      >   
      >      1054 Unknown column '...' in 'field list'
      >   
      >   may cause a server crash.
      >   
      >   
      >   Parse error like described above forces a call to
      >   JOIN::destroy() on malformed subquery.
      >   That JOIN::destroy function closes and frees temporary
      >   tables. However, temporary fields of these tables
      >   may be listed in st_select_lex::group_list of outer
      >   query, and that st_select_lex may not cleanup them
      >   properly. So, after the JOIN::destroy call that
      >   st_select_lex::group_list may have Item_field
      >   objects with dangling pointers to freed temporary
      >   table Field objects. That caused a crash.
      342b5f2e
  16. 30 Apr, 2009 1 commit
    • Gleb Shchepa's avatar
      Bug #37362: Crash in do_field_eq · cd69acad
      Gleb Shchepa authored
      EXPLAIN EXTENDED of nested query containing a error:
      
         1054 Unknown column '...' in 'field list'
      
      may cause a server crash.
      
      
      Parse error like described above forces a call to
      JOIN::destroy() on malformed subquery.
      That JOIN::destroy function closes and frees temporary
      tables. However, temporary fields of these tables
      may be listed in st_select_lex::group_list of outer
      query, and that st_select_lex may not cleanup them
      properly. So, after the JOIN::destroy call that
      st_select_lex::group_list may have Item_field
      objects with dangling pointers to freed temporary
      table Field objects. That caused a crash.
      
      
      mysql-test/r/subselect3.result:
        Added test case for bug #37362.
      mysql-test/t/subselect3.test:
        Added test case for bug #37362.
      sql/sql_select.cc:
        Bug #37362: Crash in do_field_eq
        
        The JOIN::destroy function has been modified to
        cleanup temporary table column items.
      cd69acad
  17. 28 Jan, 2009 1 commit
    • Gleb Shchepa's avatar
      Bug #39069: <row constructor> IN <table-subquery> seriously · 39f8dd37
      Gleb Shchepa authored
                  messed up
      
      "ROW(...) IN (SELECT ... FROM DUAL)" always returned TRUE.
      
      Item_in_subselect::row_value_transformer rewrites "ROW(...)
      IN SELECT" conditions into the "EXISTS (SELECT ... HAVING ...)"
      form.
      For a subquery from the DUAL pseudotable resulting HAVING
      condition is an expression on constant values, so further
      transformation with optimize_cond() eliminates this HAVING
      condition and resets JOIN::having to NULL.
      Then JOIN::exec treated that NULL as an always-true-HAVING
      and that caused a bug.
      
      To distinguish an optimized out "HAVING TRUE" clause from
      "HAVING FALSE" we already have the JOIN::having_value flag.
      However, JOIN::exec() ignored JOIN::having_value as described
      above as if it always set to COND_TRUE.
      
      The JOIN::exec method has been modified to take into account
      the value of the JOIN::having_value field.
      
      
      mysql-test/r/subselect3.result:
        Added test case for bug #39069.
      mysql-test/t/subselect3.test:
        Added test case for bug #39069.
      sql/sql_select.cc:
        Bug #39069: <row constructor> IN <table-subquery> seriously
                    messed up
        
        The JOIN::exec method has been modified to take into account
        the value of the JOIN::having_value field.
      39f8dd37
  18. 10 Oct, 2008 1 commit
    • Gleb Shchepa's avatar
      Bug #37894: Assertion in init_read_record_seq in handler.h line 1444 · efb3a353
      Gleb Shchepa authored
      Select with a "NULL NOT IN" condition containing complex
      subselect from the same table as in the outer select failed
      with an assertion.
      
      
      The failure was caused by a concatenation of circumstances:
      1) an inner select was optimized by make_join_statistics to use
         the QUICK_RANGE_SELECT access method (that implies an index
         scan of the table);
      2) a subselect was independent (constant) from the outer select;
      3) a condition was pushed down into inner select.
      
      During the evaluation of a constant IN expression an optimizer
      temporary changed the access method from index scan to table
      scan, but an engine handler was already initialized for index
      access by make_join_statistics. That caused an assertion.
      
      
      Unnecessary index initialization has been removed from
      the QUICK_RANGE_SELECT::init method (QUICK_RANGE_SELECT::reset
      reinvokes this initialization).
      
      
      mysql-test/r/subselect3.result:
        Added test case for bug #37894.
      mysql-test/t/subselect3.test:
        Added test case for bug #37894.
      sql/opt_range.cc:
        Bug #37894: Assertion in init_read_record_seq in handler.h line 1444
        
        Unnecessary index initialization has been removed from
        the QUICK_RANGE_SELECT::init method (QUICK_RANGE_SELECT::reset
        reinvokes this initialization).
      efb3a353
  19. 19 Aug, 2008 1 commit
    • Sergey Petrunia's avatar
      BUG#36135: void Diagnostics_area::set_eof_status(THD*): Assertion `! is_set()' failed. · 9427feac
      Sergey Petrunia authored
        - Before sending EOF, check if we've already sent an error.
      
      mysql-test/r/subselect3.result:
        BUG#36135: void Diagnostics_area::set_eof_status(THD*): Assertion `! is_set()' failed.
        - Testcase
      mysql-test/t/subselect3.test:
        BUG#36135: void Diagnostics_area::set_eof_status(THD*): Assertion `! is_set()' failed.
        - Testcase
      sql/sql_class.cc:
        BUG#36135: void Diagnostics_area::set_eof_status(THD*): Assertion `! is_set()' failed.
        - Before sending EOF, check if we've already sent an error.
      9427feac
  20. 22 Apr, 2008 1 commit
    • unknown's avatar
      Fixed bug#36005: server crashes inside NOT IN clause subquery with · 5bb4282e
      unknown authored
                       impossible WHERE/HAVING clause
                       (subselect_single_select_engine::exec).
      
      Allocation and initialization of joined table list t1, t2... of
      subqueries like:
      
          NOT IN (SELECT ... FROM t1,t2,... WHERE 0)
      
      is optimized out, however server tries to traverse this list.
      
      
      mysql-test/r/subselect3.result:
        Added test case for bug#36005.
      mysql-test/t/subselect3.test:
        Added test case for bug#36005.
      sql/sql_select.cc:
        Fixed bug#36005.
        
        1. JOIN::prepare initializes JOIN::table counter (actually a size
           of the JOIN::join_tab array) and sets it to a number of joined tables.
        
        2. The make_join_statistics function (when called from JOIN::optimize)
           allocates and fills the JOIN::join_tab array.
           However, when optimizing subselect has impossible (definite false)
           WHERE or HAVING clause, optimizer skips call to make_join_statistics
           and leaves JOIN::join_tab == NULL.
        
        3. subselect_single_select_engine::exec does traversal of the JOIN::join_tab
           array and the server dies because array is not allocated but array
           counter is greater than 0.
        
        The JOIN::optimize method has been modified to reset the JOIN::table
        counter to 0 in cause of impossible WHERE/HAVING clause.
      5bb4282e
  21. 14 Mar, 2008 1 commit
    • unknown's avatar
      Fixed bug #34763. · a000e8f0
      unknown authored
      Queries like:
      
        SELECT ROW(1, 2) IN (SELECT t1.a, 2)
          FROM t1 GROUP BY t1.a
      
      or 
      
        SELECT ROW(1, 2) IN (SELECT t1.a, 2 FROM t2)
          FROM t1 GROUP BY t1.a
      
      lead to assertion failure in the
      Item_in_subselect::row_value_transformer method in debugging
      build, or to unexpected error message in release build:
      
        ERROR 1247 (42S22): Reference '<list ref>' not supported (forward
                            reference in item list)
      
      Unexpected error message and assertion failure have been
      eliminated.
      
      
      mysql-test/r/subselect3.result:
        Added test case for bug #34763.
      mysql-test/t/subselect3.test:
        Added test case for bug #34763.
      sql/item.cc:
        Fixed bug #34763.
        The Item_ref::fix_fields method has been modified to silently
        ignore not fixed outer references: by the definition, those
        references should be fixed later by the call to the
        fix_inner_refs function.
      sql/item_subselect.cc:
        Fixed bug #34763.
        The Item_in_subselect::row_value_transformer method has been
        modified to eliminate assertion failure on not fixed outer
        references: by the definition those references are allowed in
        this context and should be fixed later by the call to the
        fix_inner_refs function.
      a000e8f0
  22. 21 Nov, 2007 1 commit
    • unknown's avatar
      Bug #30788: Inconsistent retrieval of char/varchar · f3637af4
      unknown authored
      Index lookup does not always guarantee that we can
      simply remove the relevant conditions from the WHERE
      clause. Reasons can be e.g. conversion errors, 
      partial indexes etc. 
      The optimizer was removing these parts of the WHERE 
      condition without any further checking.
      This leads to "false positives" when using indexes.
      Fixed by checking the index reference conditions
      (using WHERE) when using indexes with sub-queries.
      
      
      mysql-test/r/subselect.result:
        Bug #30788: 
         - using where
         - test case
      mysql-test/r/subselect3.result:
        Bug #30788: using where
      mysql-test/t/subselect.test:
        Bug #30788: test case
      sql/item.h:
        Bug #30788: 
         - Declare eq() method of Item_cache descendants : this is used in
         test_if_ref()
         - preserve the field that is being cached for type comparisions
      sql/sql_select.cc:
        Bug #30788: Don't remove the WHERE when using index lookup 
        with subqueries.
      f3637af4
  23. 29 May, 2007 1 commit
    • unknown's avatar
      Bug #27531: 5.1 part of the fix · d89e1c39
      unknown authored
      - Renamed "Using join cache" to "Using join buffer".
      - "Using join buffer" is now printed on the last
        table that "reads" from the join buffer cache.
      
      
      mysql-test/r/archive_gis.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/compress.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/ctype_utf8.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/derived.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/distinct.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/func_group.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/func_group_innodb.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/gis.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/greedy_optimizer.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/group_by.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/group_min_max.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/index_merge_myisam.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/information_schema.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/innodb_gis.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/innodb_mysql.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/join.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/join_nested.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/key_diff.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/myisam.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/ndb_condition_pushdown.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/ndb_gis.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/range.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/row.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/select.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/ssl.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/ssl_compress.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/subselect.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/subselect3.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/union.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      mysql-test/r/view.result:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      sql/sql_select.cc:
        Bug #27531: renamed "Using join cache" to "Using join buffer"
        and moved to the last table in the batch.
      d89e1c39
  24. 17 May, 2007 1 commit
    • unknown's avatar
      Bug#22855: · a9760355
      unknown authored
      Conversion errors when constructing the condition for an
      IN predicates were treated as if the affected column contains
      NULL. If such a IN predicate is inside NOT we get wrong 
      results.
      Corrected the handling of conversion errors in an IN predicate 
      that is resolved by unique_subquery (through 
      subselect_uniquesubquery_engine).
      
      
      mysql-test/r/subselect3.result:
        Bug#22855: test case
      mysql-test/t/subselect3.test:
        Bug#22855: test case
      sql/item_subselect.cc:
        Bug#22855: corrected the handling of conversion errors and
        NULL key values in IN predicate that is resolved by index
        lookup.
      a9760355
  25. 12 May, 2007 1 commit
    • unknown's avatar
      Fixed bug #28375: a query with an NOT IN subquery predicate may cause · 3d501aec
      unknown authored
      a crash when the left operand of the predicate is evaluated to NULL.
      It happens when the rows from the inner tables (tables from the subquery)
      are accessed by index methods with key values obtained by evaluation of
      the left operand of the subquery predicate. When this predicate is
      evaluated to NULL an alternative access with full table scan is used
      to check whether the result set returned by the subquery is empty or not.
      The crash was due to the fact the info about the access methods used for
      regular key values was not properly restored after a switch back from the
      full scan access method had occurred.
      The patch restores this info properly.
      The same problem existed for queries with IN subquery predicates if they
      were used not at the top level of the queries.
      
      
      mysql-test/r/subselect3.result:
        Added a test case for bug #28375.
      mysql-test/t/subselect3.test:
        Added a test case for bug #28375.
      sql/item_subselect.cc:
        Fixed bug #28375: a query with an NOT IN subquery predicate may cause
        a crash when the left operand of the predicate is evaluated to NULL.
        It happens when the rows from the inner tables (tables from the subquery)
        are accessed by index methods with key values obtained by evaluation of
        the left operand of the subquery predicate. When this predicate is
        evaluated to NULL an alternative access with full table scan is used
        to check whether the result set returned by the subquery is empty or not.
        The crash was due to the fact the info about the access methods used for
        regular key values was not properly restored after a switch back from the
        full scan access method had occurred.
        The patch restores this info properly.
      sql/sql_select.h:
        Fixed bug #28375: a query with an NOT IN subquery predicate may cause
        a crash when the left operand of the predicate is evaluated to NULL.
        In the JOIN_TAB structure two fields have been added to save info about
        index methods used to access the subquery rows. The saved info is used
        after a switch back from the alternative full scan access method has 
        occurred. The full scan is used when the left operand of the subquery
        predicate is evaluated to NULL.
      3d501aec
  26. 04 May, 2007 1 commit
    • unknown's avatar
      bug #27531: 5.1 part of the fix: · e53847f3
      unknown authored
       - added join cache indication in EXPLAIN (Extra column).
       - prefer filesort over full scan over 
         index for ORDER BY (because it's faster).
       - when switching from REF to RANGE because
         RANGE uses longer key turn off sort on
         the head table only as the resulting 
         RANGE access is a candidate for join cache
         and we don't want to disable it by sorting
         on the first table only. 
      
      
      mysql-test/r/archive_gis.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/compress.result:
        bug #27531:
         - join cache in EXPLAIN. 
         - prefer filesort over full scan over index for ORDER BY.
      mysql-test/r/ctype_utf8.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/derived.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/distinct.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/func_group.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/func_group_innodb.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/gis.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/greedy_optimizer.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/group_by.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/group_min_max.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/index_merge_myisam.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/information_schema.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/innodb_gis.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/innodb_mysql.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/join.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/join_nested.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/key_diff.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/myisam.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/ndb_condition_pushdown.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/ndb_gis.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/range.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/row.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/select.result:
        bug #27531:
         - join cache in EXPLAIN.
         - prefer filesort over full scan over index for ORDER BY.
      mysql-test/r/ssl.result:
        bug #27531:
         - join cache in EXPLAIN.
         - prefer filesort over full scan over index for ORDER BY.
      mysql-test/r/ssl_compress.result:
        bug #27531:
         - join cache in EXPLAIN.
         - prefer filesort over full scan over index for ORDER BY.
      mysql-test/r/subselect.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/subselect3.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/union.result:
        bug #27531: join cache in EXPLAIN
      mysql-test/r/view.result:
        bug #27531: join cache in EXPLAIN
      sql/sql_select.cc:
        bug #27531:
         - join cache in EXPLAIN.
         - prefer filesort over full scan over
           index for ORDER BY.
         - disable sorting on the first table only
           when switching from REF to RANGE.
      e53847f3
  27. 23 Apr, 2007 1 commit
    • unknown's avatar
      - addendum of the fix for bug 27786: · 3480915c
      unknown authored
        applied the new function is_union() to places
        in the code that check the same condition.
      - 5.0->5.1 merge fixes
      
      
      mysql-test/r/subselect3.result:
        merge 5.0->5.1 : updated explain.
      sql/item_subselect.cc:
        addendum of the fix for bug 27786:
        applied the new function is_union() to the applicable places.
      sql/sql_derived.cc:
        addendum of the fix for bug 27786:
        applied the new function is_union() to the applicable places.
      sql/sql_parse.cc:
        addendum of the fix for bug 27786:
        applied the new function is_union() to the applicable places.
      sql/sql_select.cc:
        addendum of the fix for bug 27786:
        applied the new function is_union() to places 
        in the code that check the same condition.
      sql/sql_union.cc:
        addendum of the fix for bug 27786:
        applied the new function is_union() to the applicable places.
      sql/sql_view.cc:
        addendum of the fix for bug 27786:
        applied the new function is_union() to the applicable places.
      sql/sql_yacc.yy:
        addendum of the fix for bug 27786:
        applied the new function is_union() to the applicable places.
      3480915c
  28. 21 Apr, 2007 1 commit
  29. 18 Apr, 2007 1 commit
    • unknown's avatar
      Fixed bug #27870. The bug that causes crashes manifests itself at some · e9446176
      unknown authored
      conditions when executing an equijoin query with WHERE condition
      containing a subquery predicate of the form join_attr NOT IN (SELECT ...).
      
      To resolve a problem of the correct evaluation of the expression
        attr NOT IN (SELECT ...)
      an array of guards is created to make it possible to filter out some 
      predicates of the EXISTS subquery into which the original subquery 
      predicate is transformed, in the cases when a takes the NULL value. 
      If attr is defined as a field that cannot be NULL than such an array 
      is not needed and is not created. 
      However if the field a occurred also an an equijoin predicate t2.a=t1.b
      and table t1 is accessed before table t2 then it may happen that the 
      the EXISTS subquery is pushed down to the condition evaluated just after
      table t1 has been accessed. In this case any occurrence of t2.a is 
      substituted for t1.b. When t1.b takes the value of NULL an attempt is 
      made to turn on the corresponding guard. This action caused a crash as 
      no guard array had been created.
      
      Now the code of Item_in_subselect::set_cond_guard_var checks that the guard
      array has been created before setting a guard variable on. Otherwise the
      method does nothing. It cannot results in returning a row that could be
      rejected as the condition t2.a=t1.b will be checked later anyway.        
      
      
      mysql-test/r/subselect3.result:
        Added a test case for bug #27870.
      mysql-test/t/subselect3.test:
        Added a test case for bug #27870.
      sql/item_subselect.h:
        Fixed bug #27870. The bug that causes crashes manifests itself at some
        conditions when executing an equijoin query with WHERE condition
        containing a subquery predicate of the form join_attr NOT IN (SELECT ...).
        
        Forced Item_in_subselect::set_cond_guard_var to check that the guard
        array has been created before setting a guard variable on. Otherwise the
        method does nothing.
      e9446176
  30. 15 Apr, 2007 1 commit
    • unknown's avatar
      Bug#27321: Wrong subquery result in a grouping select. · 321726e8
      unknown authored
      The Item_outer_ref class based on the Item_direct_ref class was always used
      to represent an outer field. But if the outer select is a grouping one and the 
      outer field isn't under an aggregate function which is aggregated in that
      outer select an Item_ref object should be used to represent such a field.
      If the outer select in which the outer field is resolved isn't grouping then
      the Item_field class should be used to represent such a field.
      This logic also should be used for an outer field resolved through its alias
      name.
      
      Now the Item_field::fix_outer_field() uses Item_outer_field objects to
      represent aliased and non-aliased outer fields for grouping outer selects
      only.
      Now the fix_inner_refs() function chooses which class to use to access outer
      field - the Item_ref or the Item_direct_ref. An object of the chosen class
      substitutes the original field in the Item_outer_ref object.
      The direct_ref and the found_in_select_list fields were added to the
      Item_outer_ref class.
      
      
      mysql-test/t/subselect3.test:
        Some test cases were corrected after the fix for the bug#27321.
      mysql-test/r/subselect3.result:
        Some test cases were corrected after the fix for the bug#27321.
      mysql-test/t/subselect.test:
        Added a test case for the bug#27321: Wrong subquery result in a grouping select.
      mysql-test/r/subselect.result:
        Added a test case for the bug#27321: Wrong subquery result in a grouping select.
        Some test cases were corrected after this fix.
      sql/sql_union.cc:
        Bug#27321: Wrong subquery result in a grouping select.
        Cleanup of the inner_refs_list.
      sql/sql_select.cc:
        Bug#27321: Wrong subquery result in a grouping select.
        Now the fix_inner_refs() function chooses which class to use to access outer
        field - the Item_ref or the Item_direct_ref. An object of the chosen class
        substitutes the original field in the Item_outer_ref object.
        A comment is corrected.
      sql/item.cc:
        Bug#27321: Wrong subquery result in a grouping select.
        Now the Item_field::fix_outer_field() uses Item_outer_field objects to
        represent aliased and non-aliased outer fields for grouping outer selects
        only.
      sql/item.h:
        Bug#27321: Wrong subquery result in a grouping select.
        The direct_ref and the found_in_select_list fields were added to the
        Item_outer_ref class.
      321726e8
  31. 28 Mar, 2007 1 commit
  32. 20 Mar, 2007 1 commit
    • unknown's avatar
      Bug #24484: · b8912903
      unknown authored
      To correctly decide which predicates can be evaluated with a given table
      the optimizer must know the exact set of tables that a predicate depends 
      on. If that mask is too wide (refer to non-existing tables) the optimizer
      can erroneously skip a predicate.
      One such case of wrong table usage mask were the aggregate functions.
      The have a all-1 mask (meaning depend on all tables, including non-existent
      ones).
      Fixed by making a real used_tables mask for the aggregates. The mask is
      constructed in the following way :
      1. OR the table dependency masks of all the arguments of the aggregate.
      2. If all the arguments of the function are from the local name resolution 
        context and it is evaluated in the same name resolution
        context where it is referenced all the tables from that name resolution 
        context are OR-ed to the dependency mask. This is to denote that an
        aggregate function depends on the number of rows it processes.
      3. Handle correctly the case of an aggregate function optimization (such that
        the aggregate function can be pre-calculated and made a constant).
      
      Made sure that an aggregate function is never a constant (unless subject of a 
      specific optimization and pre-calculation).  
      
      One other flaw was revealed and fixed in the process : references were 
      not calling the recalculation method for used_tables of their targets.
      
      
      mysql-test/r/subselect3.result:
        Bug #24484: test case
      mysql-test/t/subselect3.test:
        Bug #24484: test case
      sql/item.h:
        Bug #24484: Item_ref must update the used tables.
      sql/item_sum.cc:
        Bug #24484: correct calculation of used_tables for aggregates.
      sql/item_sum.h:
        Bug #24484: correct calculation of used_tables for aggregates.
      sql/opt_range.cc:
        Bug #24484: fixed ref resolution in loose index scan
      sql/sql_base.cc:
        Bug #24484: moved counting of leaf tables inside 
        setup_tables_and_check_access.
      sql/sql_class.h:
        Bug #24484: changed table count to more narrow type.
      sql/sql_insert.cc:
        Bug #24484: moved counting of leaf tables inside 
        setup_tables_and_check_access. Substract the first
        table (and its subtables) of an INSERT statement
        from leaf_count.
      sql/sql_select.cc:
        Bug #24484: correct check for aggregates
      b8912903
  33. 21 Feb, 2007 1 commit
    • unknown's avatar
      Bug#23800: Outer fields in correlated subqueries is used in a temporary table · 4410c83f
      unknown authored
      created for sorting.
      
      Any outer reference in a subquery was represented by an Item_field object.
      If the outer select employs a temporary table all such fields should be
      replaced with fields from that temporary table in order to point to the 
      actual data. This replacement wasn't done and that resulted in a wrong
      subquery evaluation and a wrong result of the whole query.
      
      Now any outer field is represented by two objects - Item_field placed in the
      outer select and Item_outer_ref in the subquery. Item_field object is
      processed as a normal field and the reference to it is saved in the
      ref_pointer_array. Thus the Item_outer_ref is always references the correct
      field. The original field is substituted for a reference in the
      Item_field::fix_outer_field() function.
      
      New function called fix_inner_refs() is added to fix fields referenced from
      inner selects and to fix references (Item_ref objects) to these fields.
      
      The new Item_outer_ref class is a descendant of the Item_direct_ref class.
      It additionally stores a reference to the original field and designed to
      behave more like a field.
      
      
      sql/item.cc:
        Bug#23800: Correlated sub query returning incorrect results when operated upon.
        Now all outer fields are substituted with references to them (Item_outer_ref objects)
        in the Item_field::fix_outer_field() function.
        The original field is saved in the Item_outer_ref object.
      sql/item.h:
        Bug#23800: Correlated sub query returning incorrect results when operated upon.
        Added the Item_outer_ref class.
      sql/mysql_priv.h:
        Bug#23800: Correlated sub query returning incorrect results when operated upon.
        Added the fix_inner_refs() function prototype.
      sql/sql_delete.cc:
        Bug#23800: Correlated sub query returning incorrect results when operated upon.
        Added call to the fix_inner_refs() function.
      sql/sql_select.cc:
        Bug#23800: Correlated sub query returning incorrect results when operated upon.
        The new function called fix_inner_refs() is added.
      mysql-test/r/subselect.result:
        Added a test case for bug#23800: Correlated sub query returning incorrect results when
        operated upon.
      sql/sql_update.cc:
        Bug#23800: Correlated sub query returning incorrect results when operated upon.
        Added call to the fix_inner_refs() function.
      mysql-test/r/subselect3.result:
        Corrected test cases result after fix for bug#23800: Correlated sub query returning
        incorrect results when operated upon.
      mysql-test/t/subselect.test:
        Added a test case for bug#23800: Correlated sub query returning incorrect results when
        operated upon.
      sql/sql_lex.cc:
        Bug#23800: Correlated sub query returning incorrect results when operated upon.
        Added cleanup of the inner_refs_list.
      sql/sql_lex.h:
        Bug#23800: Correlated sub query returning incorrect results when operated upon.
        The inner_refs_list is added to the SELECT_LEX class.
      4410c83f
  34. 27 Jan, 2007 1 commit
    • unknown's avatar
      Fixed bug #24420. · 30161731
      unknown authored
      Objects of the classes Item_func_is_not_null_test and Item_func_trig_cond
      must be transparent for the method Item::split_sum_func2 as these classes
      are pure helpers. It means that the method Item::split_sum_func2 should
      look at those objects as at pure wrappers.
      
      
      mysql-test/r/subselect3.result:
        Added a test case for bug #24420.
      mysql-test/t/subselect3.test:
        Added a test case for bug #24420.
      30161731
  35. 25 Jan, 2007 1 commit
  36. 12 Jan, 2007 2 commits
    • unknown's avatar
      BUG#24127: (a,b) IN (SELECT c,d ...) can produce wrong results if a and/or b are NULLs: · acee1edb
      unknown authored
      - Make the code produce correct result: use an array of triggers to turn on/off equalities for each
        compared column. Also turn on/off optimizations based on those equalities.
      - Make EXPLAIN output show "Full scan on NULL key" for tables for which we switch between
        ref/unique_subquery/index_subquery and ALL access.
      - index_subquery engine now has HAVING clause when it is needed, and it is
        displayed in EXPLAIN EXTENDED
      - Fix incorrect presense of "Using index" for index/unique-based subqueries (BUG#22930)
      // bk trigger note: this commit refers to BUG#24127
      
      
      mysql-test/r/ndb_subquery.result:
        BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
        - Updated test results (checked)
      mysql-test/r/subselect.result:
        BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
        - Updated test results (checked)
      mysql-test/r/subselect2.result:
        BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
        - Updated test results (checked)
      mysql-test/r/subselect3.result:
        BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
        - Testcases
      mysql-test/t/subselect3.test:
        BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
        - Testcases
      sql/item_cmpfunc.cc:
        BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
        - For row-based IN subqueries, use one flag per each column. Set the flags appropriately before
          running the subquery.
      sql/item_cmpfunc.h:
        BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
         - Added Item_func_trig_cond::get_triv_var()
      sql/item_subselect.cc:
        BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
        - Item_subselect::exec() and subselect_*_engine::exec() don't have parameter
          anymore - now Item_subselect owns the pushed down predicates guard flags.
        - A correct set of conditional predicates is now pushed into row-based IN 
          subquery.
        - select_indexsubquery_engine now has "HAVING clause" (needed for correct query
          results), and it is shown in EXPLAIN EXTENDED
      sql/item_subselect.h:
        BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
        - Item_subselect::exec() and subselect_*_engine::exec() don't have parameter
          anymore - now Item_subselect owns the pushed down predicates guard flags.
        - A correct set of conditional predicates is now pushed into row-based IN 
          subquery.
        - select_indexsubquery_engine now has "HAVING clause" (needed for correct query
          results), and it is shown in EXPLAIN EXTENDED
      sql/mysql_priv.h:
        BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
        - Added "in_having_cond" special Item name
      sql/mysqld.cc:
        BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
        - Added "in_having_cond" special Item name
      sql/sql_lex.h:
        BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
      sql/sql_select.cc:
        BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
        - Make "ref" analyzer be able to work with conditional equalities
        - Fix subquery optimization code to match the changes in what kinds of 
          conditions are pushed down into subqueries 
        - Fix wrong EXPLAIN output in some queries with subquery (BUG#22390)
      sql/sql_select.h:
        BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
        - Make "ref" analyzer be able to work with conditional equalities
        - Fix wrong EXPLAIN output in some queries with subquery (BUG#22390)
      acee1edb
    • unknown's avatar
      BUG#24085: Wrong result for NULL IN (SELECT not_null_val FROM ...) · 710c31f4
      unknown authored
      When transforming "oe IN (SELECT ie ...)" wrap the pushed-down predicates
      iff "oe can be null", not "ie can be null".
      The fix doesn't cover row-based subqueries, those will be fixed in #24127.
      
      
      mysql-test/r/subselect.result:
        BUG#24085: Wrong result for NULL IN (SELECT not_null_val FROM ...)
        Update the test results (checked)
      mysql-test/r/subselect3.result:
        BUG#24085: Wrong result for NULL IN (SELECT not_null_val FROM ...)
        - Testcase
      mysql-test/t/subselect3.test:
        BUG#24085: Wrong result for NULL IN (SELECT not_null_val FROM ...)
        - Testcase
      sql/item_subselect.cc:
        BUG#24085: Wrong result for NULL IN (SELECT not_null_val FROM ...)
        When transforming "oe IN (SELECT ie ...)" we should make special 
        provisions (wrap the pushed predicates) if we can encounter 
        NULL IN (SELECT ...), i.e. when oe->maybe_null. The code was checking
        for ie->maybe_null instead, fixed it for single value based subqueries.
        
        Row-based subqueries (e.g. (a,b) IN (SELECT c,d ...)) are not fixed 
        because they won't produce correct results for several other reasons 
        (filed as #24085)
      710c31f4
  37. 09 Nov, 2006 1 commit
    • unknown's avatar
      merging · 1a135ea6
      unknown authored
      mysql-test/r/subselect3.result:
        'filtered' column added
      mysql-test/r/udf.result:
        'filtered' column added
      1a135ea6
  38. 31 Oct, 2006 1 commit
    • unknown's avatar
      BUG#8804: wrong results for NULL IN (SELECT ...) · 9dfc7e78
      unknown authored
      Evaluate "NULL IN (SELECT ...)" in a special way: Disable pushed-down 
      conditions and their "consequences": 
       = Do full table scans instead of unique_[index_subquery] lookups.
       = Change appropriate "ref_or_null" accesses to full table scans in
         subquery's joins.
      Also cache value of NULL IN (SELECT ...) if the SELECT is not correlated 
      wrt any upper select.
      
      
      mysql-test/r/subselect.result:
        BUG#8804: wrong results for NULL IN (SELECT ...): 
         - Updated test results
      sql/item.h:
        BUG#8804: wrong results for NULL IN (SELECT ...): 
         - Added comments
      sql/item_cmpfunc.cc:
        BUG#8804: wrong results for NULL IN (SELECT ...): 
        Made Item_in_optimizer to:
        - cache the value of "NULL IN (uncorrelated select)"
        - Turn off pushed-down predicates when evaluating "NULL IN (SELECT ...)"
      sql/item_cmpfunc.h:
        BUG#8804: wrong results for NULL IN (SELECT ...): 
        - Made Item_in_optimizer cache the value of "NULL IN (uncorrelated select)"
        - Added comments
      sql/item_subselect.cc:
        BUG#8804: wrong results for NULL IN (SELECT ...):
        - When needed, wrap the predicates we push into subquery into an 
          Item_func_trig_cond so we're able to turn them off when evaluating 
          NULL IN (SELECT ...).
        - Added code to evaluate NULL IN (SELECT ...) in a special way:
          = In [unique_]index_subquery, do full table scan to see if there 
            are any rows.
          = For other subqueries, change ref[_or_null] to ALL if the
            ref[_or_null] was created from pushed-down predicate.
      sql/item_subselect.h:
        BUG#8804: wrong results for NULL IN (SELECT ...):
        - Added Item_subselect::is_correlated
        - Added comments
      sql/records.cc:
        BUG#8804: wrong results for NULL IN (SELECT ...):
        - Make rr_sequential() non-static
      sql/sql_lex.cc:
        BUG#8804: wrong results for NULL IN (SELECT ...):
        - Added st_select_lex::is_correlated and Item_subselect::is_correlated.
      sql/sql_lex.h:
        BUG#8804: wrong results for NULL IN (SELECT ...):
        - Added st_select_lex::is_correlated
      sql/sql_select.cc:
        BUG#8804: wrong results for NULL IN (SELECT ...):
        - Added KEY_FIELD::outer_ref to keep track of which ref accesses are 
          created from predicates that were pushed down into the subquery.
      sql/sql_select.h:
        BUG#8804: wrong results for NULL IN (SELECT ...):
        - Added KEYUSE::outer_ref
      mysql-test/r/subselect3.result:
        New BitKeeper file ``mysql-test/r/subselect3.result''
      mysql-test/t/subselect3.test:
        New BitKeeper file ``mysql-test/t/subselect3.test''
      9dfc7e78