An error occurred fetching the project authors.
  1. 02 Nov, 2011 1 commit
  2. 12 Oct, 2011 1 commit
  3. 05 Oct, 2011 1 commit
  4. 15 Jul, 2011 1 commit
    • unknown's avatar
      Make subquery cache off by default. · af284b55
      unknown authored
      mysql-test/r/subselect_scache.result:
        Test with subquery cache on.
      mysql-test/t/subselect_scache.test:
        Test with subquery cache on.
      af284b55
  5. 08 Jul, 2011 1 commit
    • Sergey Petrunya's avatar
      Set the default to be mrr=off,mrr_sort_keys=off: · 1492de85
      Sergey Petrunya authored
      - Set the default
      - Adjust the testcases so that 'new' tests are run with optimizations turned on.
      - Pull out relevant tests from "irrelevant" tests and run them with optimizations on.
      - Run range.test and innodb.test with both mrr=on and mrr=off
      1492de85
  6. 04 Jul, 2011 1 commit
    • Sergey Petrunya's avatar
      Change the default @@optimizer_switch setting from · c1de6f8b
      Sergey Petrunya authored
        semijoin=on,firstmatch=on,loosescan=on
      to
        semijoin=off,firstmatch=off,loosescan=off
      Adjust the testcases:
      - Modify subselect*.test and join_cache.test so that all tests
        use the same execution paths as before (i.e. optimizations that
        are being tested are enabled)
      - Let all other test files run with the new default settings (i.e.
        with new optimizations disabled)
      - Copy subquery testcases from these files into t/subselect_extra.test
        which will run them with new optimizations enabled.
      c1de6f8b
  7. 28 May, 2011 1 commit
  8. 18 Feb, 2011 1 commit
    • Martin Hansson's avatar
      Bug#11766675 - 59839: Aggregation followed by subquery yields wrong result · 61b25617
      Martin Hansson authored
      The loop that was looping over subqueries' references to outer field used a
      local boolean variable to tell whether the field was grouped or not. But the
      implementor failed to reset the variable after each iteration. Thus a field
      that was not directly aggregated appeared to be.
      
      Fixed by resetting the variable upon each new iteration.
      61b25617
  9. 03 Feb, 2011 1 commit
    • unknown's avatar
      MWL#89 · 648e6046
      unknown authored
      Adjusted test cases in accordance with the implementation.
      648e6046
  10. 03 Nov, 2010 1 commit
    • Igor Babaev's avatar
      Fixed LP bug #664594 and other bugs leading to invalid execution · 73898792
      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.
      
      73898792
  11. 29 Oct, 2010 1 commit
    • Sergey Glukhov's avatar
      Bug#57688 Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field · 4a23ac20
      Sergey Glukhov authored
      Lines below which were added in the patch for Bug#56814 cause this crash:
      
      +      if (table->table)
      +        table->table->maybe_null= FALSE;
      
      Consider following test case:
      --
      CREATE TABLE t1(f1 INT NOT NULL);
      INSERT INTO t1 VALUES (16777214),(0);
      
      SELECT COUNT(*) FROM t1 LEFT JOIN t1 t2
      ON 1 WHERE t2.f1 > 1 GROUP BY t2.f1;
      
      DROP TABLE t1;
      --
      
      We set TABLE::maybe_null to FALSE for t2 table
      and in create_tmp_field() we create appropriate tmp table field
      using create_tmp_field_from_item() function instead of
      create_tmp_field_from_field. As a result we have
      LONGLONG field. As we have GROUP BY clause we calculate
      group buffer length, see calc_group_buffer().
      Item from group list which is used for calculation
      refer to the field from real tables and have LONG type.
      So group buffer length become insufficient for storing of
      LONGLONG value. It leads to overwriting of wrong memory
      area in do_field_int() function which is called from
      end_update().
      After some investigation I found out that
      create_tmp_field_from_item() is used only for OLAP
      grouping and can not be used for common grouping
      as it could be an incompatibility between tmp
      table fields and group buffer length.
      We can not remove create_tmp_field_from_item() call from
      create_tmp_field as OLAP needs it and we can not use this
      function for common grouping. So we should remove setting
      TABLE::maybe_null to FALSE from simplify_joins().
      In this case we'll get wrong behaviour of
      list_contains_unique_index() back. To fix it we
      could use Field::real_maybe_null() check instead of
      Field::maybe_null() and add addition check of
      TABLE_LIST::outer_join.
      
      
      mysql-test/r/group_by.result:
        test case
      mysql-test/r/join_outer.result:
        test case
      mysql-test/t/group_by.test:
        test case
      mysql-test/t/join_outer.test:
        test case
      sql/sql_select.cc:
        --remove wrong code
        --use Field::real_maybe_null() check instead of
          Field::maybe_null() and add addition check of
          TABLE_LIST::outer_join
      4a23ac20
  12. 18 Oct, 2010 1 commit
  13. 28 Sep, 2010 1 commit
    • Igor Babaev's avatar
      Fixed bug #52636. · 21b1b5f0
      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.
      21b1b5f0
  14. 06 Sep, 2010 1 commit
    • unknown's avatar
      Fixed LP BUG#615760: Check on double cache assignment added into the transformation methods. · d6a9b522
      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.
      d6a9b522
  15. 31 Aug, 2010 1 commit
    • unknown's avatar
      LP BUG#615752 fix. Expression cache added to EXPLAIN EXTENDED output. · 97199ad5
      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.
      97199ad5
  16. 23 Aug, 2010 1 commit
    • Michael Widenius's avatar
      Fix for LP#612894 Some aggregate functions (such as MIN MAX) work incorrectly... · b6fe4713
      Michael Widenius authored
      Fix for LP#612894 Some aggregate functions (such as MIN MAX) work incorrectly in subqueries after getting NULL value
      
      
      mysql-test/r/group_by.result:
        Added test that showed problems that no_rows_in_results() didn't work for expressions
      mysql-test/r/subselect4.result:
        Test case for LP#612894
      mysql-test/t/group_by.test:
        Added test that showed problems that no_rows_in_results() didn't work for expressions
      mysql-test/t/subselect4.test:
        Test case for LP#612894
      sql/item.h:
        Added restore_to_before_no_rows_in_result()
        Added function processor for no_rows_in_results() and restore_to_before_no_rows_in_results() to ensure it works with functions
        Fix that above functions are handled by Item_ref()
      sql/item_func.h:
        Ensure that no_rows_in_results() and restore_to_before_no_rows_in_result() are called for all function arguments
      sql/item_sum.cc:
        Added restore_to_before_no_rows_in_result() to restore settings after Item_sum_hybrid::no_rows_in_result() was called.
        This is needed to handle the case where we have made 'make_const()' on the item in opt_sum(), but the item will be reused again in a sub query.
        Ignore multiple calls to no_rows_in_result() as Item_ref is calling it twice.
      sql/item_sum.h:
        Added restore_to_before_no_rows_in_result();
      sql/sql_select.cc:
        Added reset of no_rows_in_result() for JOIN::reinit()
      sql/sql_select.h:
        Added marker if no_rows_in_result() is called.
      b6fe4713
  17. 30 Jul, 2010 1 commit
    • Georgi Kodinov's avatar
      Bug #55188: GROUP BY, GROUP_CONCAT and TEXT - inconsistent results · de5029a4
      Georgi Kodinov authored
      In order to be able to check if the set of the grouping fields in a 
      GROUP BY has changed (and thus to start a new group) the optimizer
      caches the current values of these fields in a set of Cached_item 
      derived objects.
      The Cached_item_str, used for caching varchar and TEXT columns,
      is limited in length by the max_sort_length variable.
      A String buffer to store the value with an alloced length of either
      the max length of the string or the value of max_sort_length 
      (whichever is smaller) in Cached_item_str's constructor.
      Then, at compare time the value of the string to compare to was 
      truncated to the alloced length of the string buffer inside 
      Cached_item_str.
      This is all fine and valid, but only if you're not assigning 
      values near or equal to the alloced length of this buffer.
      Because when assigning values like this the alloced length is 
      rounded up and as a result the next set of data will not match the
      group buffer, thus leading to wrong results because of the changed
      alloced_length.
      Fixed by preserving the original maximum length in the 
      Cached_item_str's constructor and using this instead of the 
      alloced_length to limit the string to compare to.
      Test case added.
      de5029a4
  18. 12 May, 2010 1 commit
    • Ramil Kalimullin's avatar
      Fix for bug#52051: Aggregate functions incorrectly returns · feb03a82
      Ramil Kalimullin authored
            NULL from outer join query
            
            Problem: optimising MIN/MAX() queries without GROUP BY clause
            by replacing the aggregate expression with a constant, we may set it
            to NULL disregarding the fact that there may be outer joins involved.
            
            Fix: don't replace MIN/MAX() with NULL if there're outer joins.
            
            Note: the fix itself is just
            - if (!count)
            + if (!count && !outer_tables)
                set to NULL
            
            The rest of the patch eliminates repeated code to improve speed
            and for easy maintenance of the code.
      
      
      mysql-test/r/group_by.result:
                Fix for bug#52051: Aggregate functions incorrectly returns
                NULL from outer join query
                  - test result.
      mysql-test/t/group_by.test:
                Fix for bug#52051: Aggregate functions incorrectly returns
                NULL from outer join query
                  - test case.
      sql/opt_sum.cc:
                Fix for bug#52051: Aggregate functions incorrectly returns
                NULL from outer join query
                  - optimising MIN/MAX() queries without GROUP BY clause by
                replacing them with a constant, take into account that
                there're may be outer joins involved.
                  - repeated code for MIN/MAX optimization in the opt_sum_query()
                eliminated by introducing new functions that read MIN/MAX values
                using index and combining MIN/MAX cases to one.
      feb03a82
  19. 09 Mar, 2010 1 commit
    • Davi Arnaut's avatar
      Bug#40277: SHOW CREATE VIEW returns invalid SQL · f502deac
      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.
      f502deac
  20. 11 Feb, 2010 1 commit
    • Sergey Petrunya's avatar
      Apply Jorgen Loland's fix: Bug#45221: Query "SELECT pk FROM C WHERE pk IN (SELECT int_key)" failing · 5c836140
      Sergey Petrunya authored
      XOR conditions are not optimized, and Item_cond_xor therefore
      acts like type Func_item even though it inherits from Item_cond.
      A subtle difference between Item_func and Item_cond is that
      you can get the children Items from the former by calling
      arguments(), and from the latter by calling argument_list().
      However, since Item_cond_xor inherits from Item_cond,
      arguments() did not return any Items.
      
      The fact that Item_cond_xor::arguments() did not return it's
      children items lead to a problem for make_cond_for_index();
      the method accepted that XOR items on unindexed columns were
      pushed using ICP. ICP evaluation of non-indexed columns
      does not (and should not) work.
      
      The fix for this bug is to make Item_cond_xor return it's
      children items when the arguments() method is used. This makes
      Item_cond_xor behave more like Item_func and in turn allows
      make_cond_for_index() to discover any conflicting children
      Items.
      
      This is a temporary fix and should be removed when Item_cond_xor
       is optimized.
      5c836140
  21. 06 Feb, 2010 1 commit
    • Gleb Shchepa's avatar
      Bug #45640: optimizer bug produces wrong results · 994c0f83
      Gleb Shchepa authored
      Grouping by a subquery in a query with a distinct aggregate
      function lead to a wrong result (wrong and unordered
      grouping values).
      
      There are two related problems:
      
      1) The query like this:
      
         SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) c
         FROM t1 GROUP BY aa
      
      returned wrong result, because the outer reference "t1.a"
      in the subquery was substituted with the Item_ref item.
      
      The Item_ref item obtains data from the result_field object
      that refreshes once after the end of each group. This data
      is not applicable to filesort since filesort() doesn't care
      about groups (and doesn't update result_field objects with
      copy_fields() and so on). Also that data is not applicable
      to group separation algorithm: end_send_group() checks every
      record with test_if_group_changed() that evaluates Item_ref
      items, but it refreshes those Item_ref-s only after the end
      of group, that is a vicious circle and the grouped column
      values in the output are shifted.
      
      Fix: if
             a) we grouping by a subquery and
             b) that subquery has outer references to FROM list
                of the grouping query,
           then we substitute these outer references with
           Item_direct_ref like references under aggregate
           functions: Item_direct_ref obtains data directly
           from the current record.
      
      2) The query with a non-trivial grouping expression like:
      
         SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) c
         FROM t1 GROUP BY aa+0
      
      also returned wrong result, since JOIN::exec() substitutes
      references to top-level aliases in SELECT list with Item_copy
      caching items. Item_copy items have same refreshing policy
      as Item_ref items, so the whole groping expression with
      Item_copy inside returns wrong result in filesort() and
      end_send_group().
      
      Fix: include aliased items into GROUP BY item tree instead
           of Item_ref references to them.
      
      
      
      mysql-test/r/group_by.result:
        Test case for bug #45640
      mysql-test/t/group_by.test:
        Test case for bug #45640
      sql/item.cc:
        Bug #45640: optimizer bug produces wrong results
        
        Item_field::fix_fields() has been modified to resolve
        aliases in GROUP BY item trees into aliased items instead
        of Item_ref items.
      sql/item.h:
        Bug #45640: optimizer bug produces wrong results
        
        - Item::find_item_processor() has been introduced.
        - Item_ref::walk() has been modified to apply processors
          to itself too (not only to referenced item).
      sql/mysql_priv.h:
        Bug #45640: optimizer bug produces wrong results
        
        fix_inner_refs() has been modified to accept group_list
        parameter.
      sql/sql_lex.cc:
        Bug #45640: optimizer bug produces wrong results
        
        Initialization of st_select_lex::group_fix_field has
        been added.
      sql/sql_lex.h:
        Bug #45640: optimizer bug produces wrong results
        
        The st_select_lex::group_fix_field field has been introduced
        to control alias resolution in Itef_fied::fix_fields.
      sql/sql_select.cc:
        Bug #45640: optimizer bug produces wrong results
        
        - The fix_inner_refs function has been modified to treat
          subquery outer references like outer fields under aggregate
          functions, if they are included in GROUP BY item tree.
        
        - The find_order_in_list function has been modified to
          fix Item_field alias fields included in the GROUP BY item
          trees in a special manner.
      994c0f83
  22. 26 Feb, 2009 1 commit
  23. 24 Oct, 2008 1 commit
    • Sergey Petrunia's avatar
      BUG#38072: Wrong result: HAVING not observed in a query with aggregate · 844797c4
      Sergey Petrunia authored
      - Make send_row_on_empty_set() return FALSE when simplify_cond() has found out
        that HAVING is always FALSE
      re-committing to put the fix into 5.0 and 5.1
      
      mysql-test/r/group_by.result:
        BUG#38072: Wrong result: HAVING not observed in a query with aggregate
        - Testcase
      mysql-test/t/group_by.test:
        BUG#38072: Wrong result: HAVING not observed in a query with aggregate
        - Testcase
      sql/sql_select.h:
        BUG#38072: Wrong result: HAVING not observed in a query with aggregate
        - Make send_row_on_empty_set() return FALSE when simplify_cond() has found out
          that HAVING is always FALSE
      844797c4
  24. 27 Mar, 2008 1 commit
    • unknown's avatar
      Bug#27219: Aggregate functions in ORDER BY. · 9d661efd
      unknown authored
      Mixing aggregate functions and non-grouping columns is not allowed in the
      ONLY_FULL_GROUP_BY mode. However in some cases the error wasn't thrown because
      of insufficient check.
      
      In order to check more thoroughly the new algorithm employs a list of outer
      fields used in a sum function and a SELECT_LEX::full_group_by_flag.
      Each non-outer field checked to find out whether it's aggregated or not and
      the current select is marked accordingly.
      All outer fields that are used under an aggregate function are added to the
      Item_sum::outer_fields list and later checked by the Item_sum::check_sum_func
      function.
      
      
      mysql-test/t/group_by.test:
        Added a test case for the bug#27219: Aggregate functions in ORDER BY.
      mysql-test/r/group_by.result:
        Added a test case for the bug#27219: Aggregate functions in ORDER BY.
      sql/sql_select.cc:
        Bug#27219: Aggregate functions in ORDER BY.
        Implementation of new check for mixing non aggregated fields and aggregation
        function in the ONLY_FULL_GROUP_BY mode.
      sql/sql_lex.cc:
        Bug#27219: Aggregate functions in ORDER BY.
        Initialization of the full_group_by_flag bitmap.
        SELECT_LEX::test_limit function doesn't reset ORDER BY
        clause anymore.
      sql/sql_lex.h:
        Bug#27219: Aggregate functions in ORDER BY.
        The full_group_by_flag is added to the SELECT_LEX class.
      sql/item_sum.h:
        Bug#27219: Aggregate functions in ORDER BY.
        The outer_fields list is added to the Item_sum class.
      sql/mysql_priv.h:
        Bug#27219: Aggregate functions in ORDER BY.
        Defined a set of constants used in the new check for mixing non aggregated
        fields and sum functions in the ONLY_FULL_GROUP_BY_MODE.
      sql/item_subselect.cc:
        Bug#27219: Aggregate functions in ORDER BY.
        The Item_in_subselect::select_in_like_transformer function now drops
        ORDER BY clause in all selects in a subquery.
      sql/item_sum.cc:
        Bug#27219: Aggregate functions in ORDER BY.
        Now the Item_sum::check_sum_func function now checks whether fields in the
        outer_fields list are aggregated or not and marks selects accordingly.
      sql/item.cc:
        Bug#27219: Aggregate functions in ORDER BY.
        Now the Item_field::fix_fields function checks whether the field is aggregated
        or not and marks its select_lex accordingly.
      9d661efd
  25. 14 Mar, 2008 1 commit
  26. 19 Jan, 2008 1 commit
    • unknown's avatar
      Post-merge fixes. · 80857e0d
      unknown authored
      mysql-test/r/group_by.result:
        Post-merge fix after merging 5.0-opt to 5.1-opt.
      mysql-test/t/disabled.def:
        Disabled innodb_mysql back, bug #32724 is still not fixed.
      80857e0d
  27. 11 Jan, 2008 1 commit
    • unknown's avatar
      Bug#31797: error while parsing subqueries -- WHERE is parsed as HAVING · df8e9fc2
      unknown authored
      The name resolution for correlated subqueries and HAVING clauses
      failed to distinguish which of two was being performed when there 
      was a reference to an outer aliased field.
      Fixed by adding the condition that HAVING clause name resulotion
      is being performed.
      
      
      mysql-test/r/group_by.result:
        Bug#31797: Test result
      mysql-test/t/group_by.test:
        Bug#31797: Test case
      sql/item.cc:
        Bug#31797: 
        Corrected function comment.
        The fix, raising the error is restricted to HAVING name resolution.
      df8e9fc2
  28. 09 Nov, 2007 1 commit
    • unknown's avatar
      Fix for bug #32202: ORDER BY not working with GROUP BY · 55499d2b
      unknown authored
      The bug is a regression introduced by the fix for bug30596. The problem
      was that in cases when groups in GROUP BY correspond to only one row,
      and there is ORDER BY, the GROUP BY was removed and the ORDER BY
      rewritten to ORDER BY <group_by_columns> without checking if the
      columns in GROUP BY and ORDER BY are compatible. This led to
      incorrect ordering of the result set as it was sorted using the
      GROUP BY columns. Additionaly, the code discarded ASC/DESC modifiers
      from ORDER BY even if its columns were compatible with the GROUP BY
      ones.
      
      This patch fixes the regression by checking if ORDER BY columns form a
      prefix of the GROUP BY ones, and rewriting ORDER BY only in that case,
      preserving the ASC/DESC modifiers. That check is sufficient, since the
      GROUP BY columns contain a unique index.
      
      
      mysql-test/r/group_by.result:
        Added a test case for bug #32202.
      mysql-test/t/group_by.test:
        Added a test case for bug #32202.
      sql/sql_select.cc:
        In cases when groups in GROUP BY correspond to only one row and there
        is ORDER BY, rewrite the query to ORDER BY <group_by_columns> only if
        the columns in ORDER BY and GROUP BY are compatible, i.e. either one
        forms a prefix for another.
      55499d2b
  29. 28 Sep, 2007 2 commits
    • unknown's avatar
      Bug#30665: Post-merge fix · be67347f
      unknown authored
      be67347f
    • unknown's avatar
      Bug#30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY} · 5a177308
      unknown authored
      The optimizer takes different execution paths during EXPLAIN than SELECT,
      this fix relates only to EXPLAIN, hence no behavior changes.
      The test of sort keys for ORDER BY was prohibited from considering keys
      that were mentioned in IGNORE KEYS FOR ORDER BY. This led to two 
      inconsistencies: One was that IGNORE INDEX FOR GROUP BY and 
      IGNORE INDEX FOR ORDER BY gave apparently different EXPLAINs; the latter 
      erroneously claimed to do filesort. The second inconsistency 
      is that the test of sort keys is called twice, finding a sort key the first
      time but not the second time, leading to the mentioned filesort.
      
      Fixed by making the test of sort keys consider all enabled 
      keys on the table. This test rejects keys that are not covering, and for 
      covering keys the hint should be ignored anyway. 
      
      
      mysql-test/r/group_by.result:
        Bug#30665: Changed test result. The plan gets more efficient here. 
        The output is included in order to show that it is still correct.
      mysql-test/r/order_by.result:
        Bug#30665: Test result
      mysql-test/t/group_by.test:
        Bug#30665: Changed test case to show correctness of changed plan
      mysql-test/t/order_by.test:
        Bug#30665: Test case
      sql/sql_select.cc:
        Bug#30665: 
        - the fix: Give test_if_skip_sort_order all keys not the subset of non-disabled keys.
        - Added comment to test_if_skip_sort_order
      5a177308
  30. 29 Aug, 2007 1 commit
    • unknown's avatar
      Bug #30393: Test "group_by" fails with a difference in "row count" · 1bae3c20
      unknown authored
          and strategy (explain)
        
      The fix for WL3527 adds tests that test if the index usage hints
      combinations don't cause syntax errors.
      The EXPLAIN for one of these tests can be affected by the size of the
      rowid on the disk (affected by the presence of large file support). 
      Fixed to avoid the platform dependent test result by removing the 
      irrelevant columns from the EXPLAIN result.
      
      
      mysql-test/r/group_by.result:
        Bug #30393: ignore columns irrelevant to the test
      mysql-test/t/group_by.test:
        Bug #30393: ignore columns irrelevant to the test
      1bae3c20
  31. 28 Aug, 2007 1 commit
    • unknown's avatar
      Bug #30596 GROUP BY optimization gives wrong result order · 22440b53
      unknown authored
        
      The optimization that uses a unique index to remove GROUP BY did not 
      ensure that the index was actually used, thus violating the ORDER BY
      that is implied by GROUP BY.
      Fixed by replacing GROUP BY with ORDER BY if the GROUP BY clause contains
      a unique index over non-nullable field(s). In case GROUP BY ... ORDER BY 
      null is used, GROUP BY is simply removed.
      
      
      mysql-test/include/mix1.inc:
        Bug#30596: Test case for InnoDB
        Here, as opposed to for MyISAM, row lookup is done using index 
        whenever the index covers the group list.
      mysql-test/r/distinct.result:
        Bug#30596: Changed test case. 
        Prior to Bug#16458, These queries use temp table and filesort. The
        bug was that they used a temp table. However, that patch removed
        filesort also, in which case we can no longer gurantee correct ordering.
      mysql-test/r/group_by.result:
        Bug#30596: Correct result
        The test case for IGNORE INDEX FOR GROUP BY gets degraded performance 
        (unneccesary filesort). This is due to Bug#30665, which will be fixed separately.
      mysql-test/r/innodb_mysql.result:
        Bug#30596: Test result
      mysql-test/t/group_by.test:
        Bug#30596: Test case
      sql/sql_select.cc:
        Bug#30596: The fix: 
        - replace GROUP BY with ORDER BY unless ORDER BY [NULL|<constant>]
        - make sure to use the keys for GROUP BY in this ORDER BY.
      22440b53
  32. 27 Aug, 2007 1 commit
    • unknown's avatar
      Bug #30596 GROUP BY optimization gives wrong result order · 23686646
      unknown authored
      The optimization that uses a unique index to remove GROUP BY, did not 
      ensure that the index was actually used, thus violating the ORDER BY
      that is impled by GROUP BY.
      Fixed by replacing GROUP BY with ORDER BY if the GROUP BY clause contains
      a unique index. In case GROUP BY ... ORDER BY null is used, GROUP BY is
      simply removed.
      
      
      BitKeeper/etc/ignore:
        Added support-files/mysqld_multi.server tests/bug25714 cscope.in.out cscope.out cscope.po.out to the ignore list
      mysql-test/r/distinct.result:
        Bug#30596: Changed test case. 
        Prior to Bug#16458, These queries use temp table and filesort. The
        bug was that they used a temp table. However, that patch removed
        filesort also, in which case we can no longer gurantee correct ordering.
      mysql-test/r/group_by.result:
        Bug#30596: Correct result
      mysql-test/r/innodb_mysql.result:
        Bug#30596: Test case for innodb. Here, as opposed to for MyISAM, row 
        lookup is done using index whenever the index covers the group list.
      mysql-test/t/group_by.test:
        Bug#30596: Test case
      mysql-test/t/innodb_mysql.test:
        Bug#30596: Test case
      sql/sql_select.cc:
        Bug#30596: The fix, replacing GROUP BY with ORDER BY unless 
        ORDER BY [NULL|<constant>]
      23686646
  33. 02 Aug, 2007 1 commit
    • unknown's avatar
      Fixed bug#28404. · c9049374
      unknown authored
      This patch adds cost estimation for the queries with ORDER BY / GROUP BY
      and LIMIT. 
      If there was a ref/range access to the table whose rows were required
      to be ordered in the result set the optimizer always employed this access
      though a scan by a different index that was compatible with the required 
      order could be cheaper to produce the first L rows of the result set.
      Now for such queries the optimizer makes a choice between the cheapest
      ref/range accesses not compatible with the given order and index scans
      compatible with it.
      
      
      mysql-test/r/distinct.result:
        Adjusted results for test cases affected fy the fix for bug #28404.
      mysql-test/r/endspace.result:
        Adjusted results for test cases affected fy the fix for bug #28404.
      mysql-test/r/group_by.result:
        Adjusted results for test cases affected fy the fix for bug #28404.
      mysql-test/r/group_min_max.result:
        Adjusted results for test cases affected fy the fix for bug #28404.
      mysql-test/r/innodb.result:
        Adjusted results for test cases affected fy the fix for bug #28404.
      mysql-test/r/innodb_mysql.result:
        Adjusted results for test cases affected fy the fix for bug #28404.
      mysql-test/r/merge.result:
        Adjusted results for test cases affected fy the fix for bug #28404.
      mysql-test/r/order_by.result:
        Added a test case for bug #28404.
      mysql-test/r/select_found.result:
        Adjusted results for test cases affected fy the fix for bug #28404.
      mysql-test/r/subselect.result:
        Adjusted results for test cases affected fy the fix for bug #28404.
      mysql-test/t/distinct.test:
        Changed a test case after adding the fix for bug #28404.
      mysql-test/t/order_by.test:
        Added a test case for bug #28404.
      sql/sql_select.cc:
        Fixed bug#28404.
        This patch adds cost estimation for the queries with ORDER BY / GROUP BY
        and LIMIT. 
        Now for such queries the optimizer makes a choice between the cheapest
        ref/range accesses not compatible with the given order and index scans
        compatible with it.
        
        Modified the function test_if_skip_sort_order to make the above mentioned
        choice cost based.
      sql/sql_select.h:
        Fixed bug#28404.
        This patch adds cost estimation for the queries with ORDER BY / GROUP BY
        and LIMIT. 
        Added a new field fot the JOIN_TAB structure.
      c9049374
  34. 31 Jul, 2007 2 commits
    • unknown's avatar
      merging · 21024348
      unknown authored
      21024348
    • unknown's avatar
      Bug #29717 INSERT INTO SELECT inserts values even if · 791584ae
      unknown authored
       SELECT statement itself returns empty.
      
      As a result of this bug 'SELECT AGGREGATE_FUNCTION(fld) ... GROUP BY'
      can return one row instead of an empty result set.
      
      When GROUP BY only has fields of constant tables
      (with a single row), the optimizer deletes the group_list.
      After that we lose the information about whether we had an
      GROUP BY statement. Though it's important
      as SELECT min(x) from empty_table; and
         SELECT min(x) from empty_table GROUP BY y; have to return
      different results - the first query should return one row,
      second - an empty result set.
      So here we add the 'group_optimized_away' flag to remember this case
      when GROUP BY exists in the query and is removed
      by the optimizer, and check this flag in end_send_group()
      
      
      mysql-test/r/group_by.result:
        Bug #29717 INSERT INTO SELECT inserts values even if
         SELECT statement itself returns empty.
        
        test result
      mysql-test/r/insert_select.result:
        Bug #29717 INSERT INTO SELECT inserts values even if
         SELECT statement itself returns empty.
        
        test result
      mysql-test/t/group_by.test:
        Bug #29717 INSERT INTO SELECT inserts values even if
         SELECT statement itself returns empty.
        
        This is additional testcase that is more basic than the
        original bug's testcase and has the same reason.
      mysql-test/t/insert_select.test:
        Bug #29717 INSERT INTO SELECT inserts values even if
         SELECT statement itself returns empty.
        
        test case
      sql/sql_select.cc:
        Bug #29717 INSERT INTO SELECT inserts values even if
         SELECT statement itself returns empty.
        
        Remember the 'GROUP BY was optimized away' case in the JOIN::group_optimized
        and check this in the end_send_group()
      sql/sql_select.h:
        Bug #29717 INSERT INTO SELECT inserts values even if
         SELECT statement itself returns empty.
        
        JOIN::group_optimized member added to remember the 'GROUP BY optimied away'
        case
      791584ae
  35. 12 Jun, 2007 1 commit
    • unknown's avatar
      Bug#27634: group_by test fails · a8bb10ac
      unknown authored
      On many architectures, e.g. 68000, x86, the double registers have higher precision 
      than the IEEE standard prescribes. When compiled with flags -O and higher, some double's 
      go into registers and therefore have higher precision. In one test case the cost 
      information of the best and second-best key were close enough to be influenced by this 
      effect, causing a failed test in distribution builds.
      
      Fixed by removing some rows from the table in question so that cost information is not
      influenced by decimals beyond standard definition of double.
      
      
      mysql-test/r/group_by.result:
        Bug#27634: Altered test reslut. The only difference in the results is in the 'rows' column.
      mysql-test/t/group_by.test:
        Bug#27634: Altered test case to avoid the corner case where excess precision causes 
        non-minimum costs to appear minimal.
      a8bb10ac
  36. 29 May, 2007 1 commit
    • unknown's avatar
      Bug #27531: 5.1 part of the fix · aff34a8c
      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.
      aff34a8c
  37. 04 May, 2007 1 commit
    • unknown's avatar
      bug #27531: 5.1 part of the fix: · 0c835da8
      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.
      0c835da8
  38. 24 Apr, 2007 1 commit
    • unknown's avatar
      Bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode. · 3ffdeef0
      unknown authored
      When fields are inserted instead of * in the select list they were not marked
      for check for the ONLY_FULL_GROUP_BY mode.
      
      The Field_iterator_table::create_item() function now marks newly created
      items for check when in the ONLY_FULL_GROUP_BY mode.
      The setup_wild() and the insert_fields() functions now maintain the
      cur_pos_in_select_list counter for the ONLY_FULL_GROUP_BY mode.
      
      
      sql/sql_base.cc:
        Bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode.
        The setup_wild() and the insert_fields() functions now maintain the
        cur_pos_in_select_list counter for the ONLY_FULL_GROUP_BY mode.
      sql/table.cc:
        Bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode.
        The Field_iterator_table::create_item() function now marks newly created
        items for check when in the ONLY_FULL_GROUP_BY mode.
      mysql-test/r/group_by.result:
        Added a test case for the bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode.
      mysql-test/t/group_by.test:
        Added a test case for the bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode.
      3ffdeef0