An error occurred fetching the project authors.
  1. 03 Feb, 2011 1 commit
    • unknown's avatar
      MWL#89 · 090b4a10
      unknown authored
      Adjusted test cases in accordance with the implementation.
      090b4a10
  2. 17 Jan, 2011 1 commit
    • unknown's avatar
      MWL#89 · 771fe38e
      unknown authored
      Fixed query plans with loose index scan degraded into index scan.
      
      Analysis:
      With MWL#89 subqueries are no longer executed and substituted during
      the optimization of the outer query. As a result subquery predicates
      that were previously executed and substituted by a constant before
      the range optimizer were present as regular subquery predicates during
      range optimization. The procedure check_group_min_max_predicates()
      had a naive test that ruled out all queries with subqueries in the
      WHERE clause. This resulted in worse plans with MWL#89.
      
      Solution:
      The solution is to refine the test in check_group_min_max_predicates()
      to check if each MIN/MAX argument is referred to by a subquery predicate.
      771fe38e
  3. 18 Oct, 2010 1 commit
  4. 11 Jun, 2010 1 commit
    • Martin Hansson's avatar
      Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&, · fdd0bb47
      Martin Hansson authored
      Item*) at opt_sum.cc:305
            
      Queries applying MIN/MAX functions to indexed columns are
      optimized to read directly from the index if all key parts
      of the index preceding the aggregated key part are bound to
      constants by the WHERE clause. A prefix length is also
      produced, equal to the total length of the bound key
      parts. If the aggregated column itself is bound to a
      constant, however, it is also included in the prefix.
      
      Such full search keys are read as closed intervals for
      reasons beyond the scope of this bug. However, the procedure
      missed one case where a key part meant for use as range
      endpoint was being overwritten with a NULL value destined
      for equality checking. In this case the key part was
      overwritten but the range flag remained, causing open
      interval reading to be performed.
      
      Bug was fixed by adding more stringent checking to the
      search key building procedure (matching_cond) and never
      allow overwrites of range predicates with non-range
      predicates.
      
      An assertion was added to make sure open intervals are never
      used with full search keys.
      fdd0bb47
  5. 27 May, 2010 1 commit
    • Sergey Petrunya's avatar
      MWL#90: Subqueries: Inside-out execution for non-semijoin materialized... · b93f53b2
      Sergey Petrunya authored
      MWL#90: Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE
      - Change "SUBQUERY#n" to "<subquery{n}>" in EXPLAIN output. We need to it to be
        lowercase so that EXPLAIN results do not differ in case between systems with
        case-sensitive and case-insensitive filesystems.
      - Remove garbage comments, add better comments.
      b93f53b2
  6. 06 Apr, 2010 2 commits
  7. 05 Apr, 2010 1 commit
  8. 19 Mar, 2010 1 commit
    • Martin Hansson's avatar
      Post-push fix to disable a subset of the test case for Bug#47762. · 5c87c97c
      Martin Hansson authored
      This has been back-ported from 6.0 as the problems proved to afflict 
      5.1 as well.
      The fix exposed two new bugs. They were reported as follows.
            
      Bug no 52174: Sometimes wrong plan when reading a MAX value 
      from non-NULL index
            
      Bug no 52173: Reading NULL value from non-NULL index gives wrong 
      result in embedded server 
            
      Both bugs taken together affect a much smaller class of queries than #47762, 
      so the fix stays for now.
      5c87c97c
  9. 16 Mar, 2010 1 commit
    • Martin Hansson's avatar
      Bug#47762: Incorrect result from MIN() when WHERE tests NOT · 1aca0297
      Martin Hansson authored
      NULL column for NULL
      
      The optimization to read MIN() and MAX() values from an
      index did not properly handle comparisons with NULL
      values. Fixed by giving up the particular optimization step
      if there are non-NULL safe comparisons with NULL values, as 
      the result is NULL anyway.
      
      Also, Oracle copyright notice was added to all files.
      1aca0297
  10. 11 Feb, 2010 1 commit
  11. 09 Feb, 2010 1 commit
    • Sergey Vojtovich's avatar
      BUG#49902 - SELECT returns incorrect results · b586bed7
      Sergey Vojtovich authored
      Queries optimized with GROUP_MIN_MAX didn't cleanup KEYREAD
      optimization properly. As a result subsequent queries may
      return incomplete rows (fields are initialized to default
      values).
      
      mysql-test/r/group_min_max.result:
        A test case for BUG#49902.
      mysql-test/t/group_min_max.test:
        A test case for BUG#49902.
      sql/opt_range.cc:
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::set_keyread().
      sql/opt_sum.cc:
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::set_keyread().
      sql/sql_select.cc:
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::set_keyread().
      sql/sql_update.cc:
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::set_keyread().
      sql/table.cc:
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::set_keyread().
      sql/table.h:
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::set_keyread().
      b586bed7
  12. 17 Nov, 2009 1 commit
    • Alexey Kopytov's avatar
      Bug #48472: Loose index scan inappropriately chosen for some · 37f45abf
      Alexey Kopytov authored
                  WHERE conditions 
       
      check_group_min_max() checks if the loose index scan 
      optimization is applicable for a given WHERE condition, that is 
      if the MIN/MAX attribute participates only in range predicates 
      comparing the corresponding field with constants. 
       
      The problem was that it considered the whole predicate suitable 
      for the loose index scan optimization as soon as it encountered 
      a constant as a predicate argument. This is obviously wrong for 
      cases when a constant is the first argument of a predicate 
      which does not satisfy the above condition. 
       
      Fixed check_group_min_max() so that all arguments of the input 
      predicate are considered to decide if it passes the test, even 
      though a constant has already been encountered.
      
      mysql-test/r/group_min_max.result:
        Added a test case for bug #48472.
      mysql-test/t/group_min_max.test:
        Added a test case for bug #48472.
      sql/opt_range.cc:
        Fixed check_group_min_max() so that all arguments of the input 
        predicate are considered to decide if it passes the test, even 
        though a constant has already been encountered.
      37f45abf
  13. 09 Oct, 2009 1 commit
    • Martin Hansson's avatar
      Bug#42846: wrong result returned for range scan when using · fda4fb6b
      Martin Hansson authored
      covering index
            
      When two range predicates were combined under an OR
      predicate, the algorithm tried to merge overlapping ranges
      into one. But the case when a range overlapped several other
      ranges was not handled. This lead to
      
      1) ranges overlapping, which gave repeated results and 
      2) a range that overlapped several other ranges was cut off.  
      
      Fixed by 
      
      1) Making sure that a range got an upper bound equal to the
      next range with a greater minimum.
      2) Removing a continue statement
      
      
      mysql-test/r/group_min_max.result:
        Bug#42846: Changed query plans
      mysql-test/r/range.result:
        Bug#42846: Test result.
      mysql-test/t/range.test:
        Bug#42846: Test case.
      sql/opt_range.cc:
        Bug#42846: The fix. 
        
        Part1: Previously, both endpoints from key2 were copied,
        which is not safe. Since ranges are processed in ascending
        order of minimum endpoints, it is safe to copy the minimum
        endpoint from key2 but not the maximum. The maximum may only
        be copied if there is no other range or the other range's
        minimum is greater than key2's maximum.
      fda4fb6b
  14. 30 Aug, 2009 1 commit
    • Alexey Kopytov's avatar
      Bug #46607: Assertion failed: (cond_type == Item::FUNC_ITEM) · 9620aec7
      Alexey Kopytov authored
                  results in server crash 
       
      check_group_min_max_predicates() assumed the input condition 
      item to be one of COND_ITEM, SUBSELECT_ITEM, or FUNC_ITEM. 
      Since a condition of the form "field" is also a valid condition 
      equivalent to "field <> 0", using such a condition in a query 
      where the loose index scan was chosen resulted in a debug 
      assertion failure. 
       
      Fixed by handling conditions of the FIELD_ITEM type in 
      check_group_min_max_predicates(). 
      
      mysql-test/r/group_min_max.result:
        Added a test case for bug #46607.
      mysql-test/t/group_min_max.test:
        Added a test case for bug #46607.
      sql/opt_range.cc:
        Handle conditions of the FUNC_ITEM type in 
        check_group_mix_max_predicates().
      9620aec7
  15. 13 Jul, 2009 1 commit
    • Georgi Kodinov's avatar
      Bug #40113: Embedded SELECT inside UPDATE or DELETE can timeout · 045741fb
      Georgi Kodinov authored
      without error
      
      When using quick access methods for searching rows in UPDATE or 
      DELETE there was no check if a fatal error was not already sent 
      to the client while evaluating the quick condition.
      As a result a false OK (following the error) was sent to the 
      client and the error was thus transformed into a warning.
      
      Fixed by checking for errors sent to the client during 
      SQL_SELECT::check_quick() and treating them as real errors.
      
      Fixed a wrong test case in group_min_max.test
      Fixed a wrong return code in mysql_update() and mysql_delete()
      
      mysql-test/r/bug40113.result:
        Bug #40013: test case
      mysql-test/r/group_min_max.result:
        Bug #40013: fixed a wrong test case
      mysql-test/t/bug40113-master.opt:
        Bug #40013: test case
      mysql-test/t/bug40113.test:
        Bug #40013: test case
      mysql-test/t/group_min_max.test:
        Bug #40013: fixed a wrong test case
      sql/sql_delete.cc:
        Bug #40113: check for errors evaluating the quick select
      sql/sql_update.cc:
        Bug #40113: check for errors evaluating the quick select
      045741fb
  16. 16 Jun, 2009 1 commit
  17. 12 Jun, 2009 1 commit
    • Georgi Kodinov's avatar
      Bug #45386: Wrong query result with MIN function in field list, · 131c9556
      Georgi Kodinov authored
      WHERE and GROUP BY clause
      
      Loose index scan may use range conditions on the argument of 
      the MIN/MAX aggregate functions to find the beginning/end of 
      the interval that satisfies the range conditions in a single go.
      These range conditions may have open or closed minimum/maximum 
      values. When the comparison returns 0 (equal) the code should 
      check the type of the min/max values of the current interval 
      and accept or reject the row based on whether the limit is 
      open or not.
      There was a wrong composite condition on checking this and it was
      not working in all cases.
      Fixed by simplifying the conditions and reversing the logic.
      
      mysql-test/r/group_min_max.result:
        Bug #45386: test case
      mysql-test/t/group_min_max.test:
        Bug #45386: test case
      sql/opt_range.cc:
        Bug #45386: fix the check whether to use the value if on the
        interval boundry
      131c9556
  18. 10 Jun, 2009 1 commit
    • Martin Hansson's avatar
      Bug#44821: select distinct on partitioned table returns wrong results · 5659973b
      Martin Hansson authored
      Range analysis did not request sorted output from the storage engine,
      which cause partitioned handlers to process one partition at a time
      while reading key prefixes in ascending order, causing values to be 
      missed. Fixed by always requesting sorted order during range analysis.
      This fix is introduced in 6.0 by the fix for bug no 41136.
      
      mysql-test/r/group_min_max.result:
        Bug#44821: Test result.
      mysql-test/t/group_min_max.test:
        Bug#44821: Test case
      sql/opt_range.cc:
        Bug#44821: Fix.
      5659973b
  19. 27 Feb, 2009 1 commit
    • Georgi Kodinov's avatar
      Bug #41610: key_infix_len can be overwritten causing some group by queries to · baf88385
      Georgi Kodinov authored
      return no rows
      
      The algorithm of determining the best key for loose index scan is doing a loop
      over the available indexes and selects the one that has the best cost.
      It retrieves the parameters of the current index into a set of variables.
      If the cost of using the current index is lower than the best cost so far it 
      copies these variables into another set of variables that contain the 
      information for the best index so far.
      After having checked all the indexes it uses these variables (outside of the 
      index loop) to create the table read plan object instance.
      The was a single omission : the key_infix/key_infix_len variables were used 
      outside of the loop without being preserved in the loop for the best index 
      so far.
      This causes these variables to get overwritten by the next index(es) checked.
      Fixed by adding variables to hold the data for the current index, passing 
      the new variables to the function that assigns values to them and copying 
      the new variables into the existing ones when selecting a new current best 
      index.
      To avoid further such problems moved the declarations of the variables used 
      to keep information about the current index inside the loop's compound 
      statement.
      
      mysql-test/r/group_min_max.result:
        Bug #41610: test case
      mysql-test/t/group_min_max.test:
        Bug #41610: test case
      sql/opt_range.cc:
        Bug #41610: copy the infix data for the current best index
      baf88385
  20. 27 Aug, 2008 1 commit
    • Evgeny Potemkin's avatar
      Bug#38195: Incorrect handling of aggregate functions when loose index scan is · dcf82665
      Evgeny Potemkin authored
      used causes server crash.
            
      When the loose index scan access method is used values of aggregated functions
      are precomputed by it. Aggregation of such functions shouldn't be performed
      in this case and functions should be treated as normal ones.
      The create_tmp_table function wasn't taking this into account and this led to
      a crash if a query has MIN/MAX aggregate functions and employs temporary table
      and loose index scan.
      Now the JOIN::exec and the create_tmp_table functions treat MIN/MAX aggregate
      functions as normal ones when the loose index scan is used.
      
      
      mysql-test/r/group_min_max.result:
        Added a test case for the bug#38195.
      mysql-test/t/group_min_max.test:
        Added a test case for the bug#38195.
      sql/sql_select.cc:
        Bug#38195: Incorrect handling of aggregate functions when loose index scan is
        used causes server crash.
        The JOIN::exec and the create_tmp_table functions treat MIN/MAX aggregate
        functions as normal ones when the loose index scan is used.
      dcf82665
  21. 19 Aug, 2008 1 commit
  22. 15 Aug, 2008 1 commit
  23. 14 Aug, 2008 1 commit
  24. 13 Aug, 2008 1 commit
    • Evgeny Potemkin's avatar
      Bug#38195: Incorrect handling of aggregate functions when loose index scan is · f7e34341
      Evgeny Potemkin authored
      used causes server crash.
      
      When the loose index scan access method is used values of aggregated functions
      are precomputed by it. Aggregation of such functions shouldn't be performed
      in this case and functions should be treated as normal ones.
      The create_tmp_table function wasn't taking this into account and this led to
      a crash if a query has MIN/MAX aggregate functions and employs temporary table
      and loose index scan.
      Now the JOIN::exec and the create_tmp_table functions treat MIN/MAX aggregate
      functions as normal ones when the loose index scan is used.
      
      
      mysql-test/r/group_min_max.result:
        Added a test case for the bug#38195.
      mysql-test/t/group_min_max.test:
        Added a test case for the bug#38195.
      sql/sql_select.cc:
        Bug#38195: Incorrect handling of aggregate functions when loose index scan is
        used causes server crash.
        Now the JOIN::exec and the create_tmp_table functions treat MIN/MAX aggregate
        functions as normal ones when the loose index scan is used.
      f7e34341
  25. 12 Feb, 2008 1 commit
    • unknown's avatar
      Fix for Bug#32538: View definition picks up character set, · 3a8c0e34
      unknown authored
      but not collation.
      
      The problem here was that text literals in a view were always
      dumped with character set introducer. That lead to loosing
      collation information.
      
      The fix is to dump character set introducer only if it was
      in the original query. That is now possible because there 
      is no problem any more of loss of character set of string
      literals in views -- after WL#4052 the view is dumped 
      in the original character set.
      
      
      mysql-test/r/case.result:
        Update result file.
      mysql-test/r/compress.result:
        Update result file.
      mysql-test/r/ctype_collate.result:
        Update result file.
      mysql-test/r/date_formats.result:
        Update result file.
      mysql-test/r/ddl_i18n_koi8r.result:
        Update result file.
      mysql-test/r/ddl_i18n_utf8.result:
        Update result file.
      mysql-test/r/fulltext.result:
        Update result file.
      mysql-test/r/func_crypt.result:
        Update result file.
      mysql-test/r/func_encrypt.result:
        Update result file.
      mysql-test/r/func_if.result:
        Update result file.
      mysql-test/r/func_in.result:
        Update result file.
      mysql-test/r/func_like.result:
        Update result file.
      mysql-test/r/func_regexp.result:
        Update result file.
      mysql-test/r/func_set.result:
        Update result file.
      mysql-test/r/func_str.result:
        Update result file.
      mysql-test/r/func_time.result:
        Update result file.
      mysql-test/r/gis.result:
        Update result file.
      mysql-test/r/group_min_max.result:
        Update result file.
      mysql-test/r/mysqldump.result:
        Update result file.
      mysql-test/r/negation_elimination.result:
        Update result file.
      mysql-test/r/null.result:
        Update result file.
      mysql-test/r/select.result:
        Update result file.
      mysql-test/r/show_check.result:
        Update result file.
      mysql-test/r/sp-code.result:
        Update result file.
      mysql-test/r/ssl.result:
        Update result file.
      mysql-test/r/ssl_compress.result:
        Update result file.
      mysql-test/r/subselect.result:
        Update result file.
      mysql-test/r/temp_table.result:
        Update result file.
      mysql-test/r/type_blob.result:
        Update result file.
      mysql-test/r/view.result:
        Update result file.
      mysql-test/suite/binlog/r/binlog_stm_blackhole.result:
        Update result file.
      mysql-test/suite/rpl/r/rpl_get_lock.result:
        Update result file.
      mysql-test/suite/rpl/r/rpl_master_pos_wait.result:
        Update result file.
      mysql-test/t/view.test:
        Add a test case for Bug#32538.
      sql/item.cc:
        Do not dump character set introducer if it was not specified
        explicitly in the original query.
      sql/item.h:
        Add 'cs_specified' property to Item_string.
      sql/sql_yacc.yy:
        Set Item_string::cs_specified property to TRUE
        when character set introducer is explicitly specified.
      3a8c0e34
  26. 20 Nov, 2007 1 commit
    • unknown's avatar
      Bug #32268: Indexed queries give bogus MIN and MAX results · 162f8bed
      unknown authored
      Loose index scan does the grouping so the temp table does 
      not need to do it, even when sorting.
      Fixed by checking if the grouping is already done before
      doing sorting and grouping in a temp table and do only 
      sorting.
      
      
      mysql-test/r/group_min_max.result:
        Bug #32268: test case
      mysql-test/t/group_min_max.test:
        Bug #32268: test case
      sql/sql_select.cc:
        Bug #32268: don't group in the temp table if already done
      162f8bed
  27. 06 Nov, 2007 1 commit
    • unknown's avatar
      Bug #31974: additional commit (test case updates) · 8ced7dc3
      unknown authored
       loose index scan enabled for subqueries
      
      
      mysql-test/r/group_min_max.result:
        Bug #31974: test case updates
         loose index scan enabled for subqueries
      mysql-test/r/index_merge_myisam.result:
        Bug #31974: test case updates
         loose index scan enabled for subqueries
      8ced7dc3
  28. 01 Nov, 2007 1 commit
    • unknown's avatar
      A fix for Bug#32030 "DELETE does not return an error and deletes rows if · 9d4d6f47
      unknown authored
      error evaluating WHERE"
      
      DELETE with a subquery in WHERE clause would sometimes ignore subquery
      evaluation error and proceed with deletion.
      
      The fix is to check for an error after evaluation of the WHERE clause
      in DELETE.
      
      Addressed review comments.
      
      
      mysql-test/r/group_min_max.result:
        Update the test results to reflect the fix for Bug#32030.
      mysql-test/r/ps.result:
        Update test results (Bug#32030)
      mysql-test/t/group_min_max.test:
        Update the test case to reflect the fix for Bug#32030
      mysql-test/t/ps.test:
        Add a test case for Bug#32030
      sql/sql_delete.cc:
        Check for an error before calling send_ok(). Two different places are
        covered because the subquery code has slightly different execution
        paths depending on ps-protocol/old-protocol
      9d4d6f47
  29. 31 Oct, 2007 1 commit
  30. 02 Aug, 2007 1 commit
    • unknown's avatar
      Fixed bug#28404. · bdba4c96
      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.
      bdba4c96
  31. 24 Jun, 2007 1 commit
    • unknown's avatar
      Fixed bug #25602. A query with DISTINCT in the select list to which · 09e2b603
      unknown authored
      the loose scan optimization for grouping queries was applied returned 
      a wrong result set when the query was used with the SQL_BIG_RESULT
      option.
      
      The SQL_BIG_RESULT option forces to use sorting algorithm for grouping
      queries instead of employing a suitable index. The current loose scan
      optimization is applied only for one table queries when the suitable
      index is covering. It does not make sense to use sort algorithm in this
      case. However the create_sort_index function does not take into account
      the possible choice of the loose scan to implement the DISTINCT operator
      which makes sorting unnecessary. Moreover the current implementation of
      the loose scan for queries with distinct assumes that sorting will
      never happen. Thus in this case create_sort_index should not call
      the function filesort.
      
      
      mysql-test/r/group_min_max.result:
        Added a test case for bug #25602.
      mysql-test/t/group_min_max.test:
        Added a test case for bug #25602.
      09e2b603
  32. 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
  33. 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
  34. 29 Nov, 2006 1 commit
  35. 28 Nov, 2006 1 commit
    • unknown's avatar
      Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar · 64aff71e
      unknown authored
                 statements
      Currently the optimizer evaluates loose index scan only for top-level SELECT
      statements
      Extend loose index scan applicability by :
       - Test the applicability of loose scan for each sub-select, instead of the
         whole query. This change enables loose index scan for sub-queries.
       - allow non-select statements with SELECT parts (like, e.g. 
         CREATE TABLE .. SELECT ...) to use loose index scan.
      
      
      mysql-test/r/group_min_max.result:
        Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar
                   statements
         - test case
      mysql-test/t/group_min_max.test:
        Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar
                   statements
         - test case
      sql/opt_range.cc:
        Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar
                   statements
         - loose index scan will be tried over the current subselect 
           (lex->current_select) instead of the whole query (lex->select_lex).
         - allow non-select statements with SELECT parts (like, e.g. 
           CREATE TABLE .. SELECT ...) to use loose index scan.
      64aff71e
  36. 19 Oct, 2006 1 commit
  37. 16 Oct, 2006 1 commit
    • unknown's avatar
      Bug #22342: No results returned for query using max and group by · 1d3a5f57
      unknown authored
       When using index for group by and range access the server isolates    
       a set of ranges based on the conditions over the key parts of the
       index used. Then it uses only the ranges over the GROUP BY fields to
       jump from one group to another. Since the GROUP BY fields may form a
       prefix over the index, we may use only a prefix of the ranges produced
       by the range optimizer.
       Each range contains a notion on whether it includes its border values.
       The problem is that when using a range prefix, the last range is open
       because it assumes that there is a range on the next keypart. Thus when
       we use a prefix range as it is, it excludes all border values.
       The solution is when ignoring the suffix of the range conditions 
       (to jump over the GROUP BY prefix only) the server must change the 
       remaining intervals so they always contain their borders, e.g. 
       if the whole range was :
       (1,-inf) <= (<group_by_col>,<min_max_arg_col>) < (1, 3) we must make
       (1) <= (<group_by_col>) <= (1) because (a,b) < (c1,c2) means :
       a < c1 OR (a = c1 AND b < c2).
      
      
      mysql-test/r/group_min_max.result:
        Bug #22342: No results returned for query using max and group by
         - test case
      mysql-test/t/group_min_max.test:
        Bug #22342: No results returned for query using max and group by
         - test case
      sql/opt_range.cc:
        Bug #22342: No results returned for query using max and group by
         - open the intervals for prefix select when there are more conditions
           than used for the prefix search.
      sql/opt_range.h:
        Bug #22342: No results returned for query using max and group by
         - open the intervals for prefix select when there are more conditions
           than used for the prefix search.
      1d3a5f57
  38. 28 Jul, 2006 1 commit
    • unknown's avatar
      BUG#14940 "MySQL choose wrong index", v.2 · 8dd88f48
      unknown authored
      - Make the range-et-al optimizer produce E(#table records after table 
                                                 condition is applied),
      - Make the join optimizer use this value,
      - Add "filtered" column to EXPLAIN EXTENDED to show 
        fraction of records left after table condition is applied
      - Adjust test results, add comments
      
      
      mysql-test/r/archive_gis.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/auto_increment.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/bdb_gis.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/bench_count_distinct.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/binlog_stm_blackhole.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/case.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/cast.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/compress.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/ctype_collate.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/ctype_cp1250_ch.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/date_formats.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/distinct.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/fulltext.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_compress.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_crypt.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_default.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_encrypt.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_gconcat.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_group.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_if.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_in.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_like.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_math.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_op.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_regexp.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_set.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_str.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_system.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_test.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/func_time.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/gis.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/group_by.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/group_min_max.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/having.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/heap.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/heap_hash.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/index_merge.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/index_merge_innodb.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/index_merge_ror.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/innodb_gis.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/insert_update.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/join.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/join_nested.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/key_diff.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/myisam.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/ndb_gis.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/negation_elimination.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/null.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/olap.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/partition_pruning.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/query_cache.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/row.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/rpl_get_lock.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/rpl_master_pos_wait.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/select.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/ssl.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/ssl_compress.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/subselect.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/type_blob.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/union.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/varbinary.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/variables.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/r/view.result:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/t/ctype_cp1250_ch.test:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/t/func_like.test:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/t/group_min_max.test:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/t/index_merge_ror.test:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/t/index_merge_ror_cpk.test:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/t/join.test:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      mysql-test/t/partition_pruning.test:
        BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
      sql/opt_range.cc:
        BUG#14940: Make range/index_merge/group-min-max optimizer produce TABLE::quick_condition_rows -
          estimate of #records that will match the table condition.
      sql/sql_class.cc:
        BUG#14940: Add "filtered" column to output of EXPLAIN EXTENDED
      sql/sql_select.cc:
        BUG#14940: 
        - Make the join optimizer to use TABLE::quick_condition_rows=
          = E(#table records after filtering with table condition) 
        - Add "filtered" column to output of EXPLAIN EXTENDED
      sql/sql_select.h:
        BUG#14940: Added comments
      sql/table.h:
        BUG#14940: Added comments
      8dd88f48
  39. 21 Jul, 2006 1 commit
    • unknown's avatar
      Fix for BUG#21007. · 2ce5582a
      unknown authored
      The problem was that store_top_level_join_columns() incorrectly assumed
      that the left/right neighbor of a nested join table reference can be only
      at the same level in the join tree.
      
      The fix checks if the current nested join table reference has no immediate
      left/right neighbor, and if so chooses the left/right neighbors of the
      nearest upper level, where these references are != NULL.
      
      
      mysql-test/r/group_min_max.result:
        Test for BUG#21007.
      mysql-test/t/group_min_max.test:
        Test for BUG#21007.
      sql/sql_base.cc:
        After computing and materializing the columns of all NATURAL joins in a FROM clause,
        the procedure store_top_level_join_columns() has to change the current natural join
        into a leaf table reference for name resolution. For this it needs to make the left
        neighbor point to the natural join table reference, and the natural join itself point
        to its left neighbor.
        
        This fix correctly determines the left/right neighbors of a table reference, even if
        the neghbors are at higher levels in the nested join tree. The rule is that if a table
        reference has no immediate left/right neighbors, we recursively pick the left/right
        neighbor of the level(s) above.
      2ce5582a