bzr push into mysql-next-mr-bugfixing branch (jorgen.loland:3234 to 3235)

[Jorgen Loland]

 3235 Jorgen Loland	2010-09-02
      Cast return of log10(double) to int to avoid warning

    modified:
      sql/sql_select.cc
 3234 Jorgen Loland	2010-09-02
      Bug#49129 - Wrong result with IN-subquery with join_cache_level=6 
                  and firstmatch=off
      
      Patch based on contribution from Sergey Petrunia.
      
      Consider the query:
      
      SELECT * FROM t0 WHERE t0.a IN (
         SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
      
      With join cache level 6, this query only returns the first tuple
      from t0 that has a match in the subquery. Consider the relevant part
      of EXPLAIN:
      
      t0    | Using where                                   |
      t1    | Start temporary; Using join buffer            |
      t2    | Using where; End temporary; Using join buffer |
      
      When the optimizer decides to use join buffers, temporary tables
      created for duplicate weedout should extend to the first table
      after const tables. I.e., Start temporary should be printed for
      t0 above.
      
      The reason for the bug is that setup_semijoin_dups_elimination()
      is called before the final decision is made in
      check_join_cache_usage() on whether or not to use join buffering.
      In this case, use_join_buffer==false for t1 and t2 during
      setup_semijoin_dups_elimination(), and the range of tables to
      buffer is therefore not extended to t0.
      
      Since check_join_cache_usage() needs to know if duplicate weedout
      is used, so moving setup_semijoin_dups_elimination() from before
      check_join_cache_usage() to after it is not possible. 
      
      The temporary fix of this patch is to use a rough estimate of
      whether join buffering will be used in
      setup_semijoin_dups_elimination(). This rough test covers more 
      cases than actually end up with join buffering, and in these cases 
      we now extend the temporary table to store rowids for more tables 
      than strictly required, i.e., the first non-const table up to the 
      start of the semijoin. A proper (but much more costly to do) fix 
      would be to merge the loops of setup_semijoin_dups_elimination() 
      and make_join_readinfo() (which calls check_join_cache_usage()).
     @ mysql-test/r/subquery_sj_all.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_all_jcl6.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_all_jcl7.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_dupsweed.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_dupsweed_jcl6.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_dupsweed_jcl7.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_firstmatch.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_firstmatch_jcl6.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_firstmatch_jcl7.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_loosescan.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_loosescan_jcl6.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_loosescan_jcl7.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_mat.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_mat_jcl6.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_mat_jcl7.result
        Recorded new explain output for queries using duplicate weedout.
     @ sql/sql_select.cc
        setup_semijoin_dups_elimination() relied on
        best_position[i].use_join_buffer when checking whether temporary
        tables for duplicate weedout should be extended to the first
        table of the execution plan. However, use_join_buffer is not
        reliable at this stage, so setup_semijoin_dups_elimination()
        needs to use a rough estimate instead.
     @ sql/sql_select.h
        Made st_join_table::use_quick an enum
     @ sql/sql_test.cc
        Made st_join_table::use_quick an enum

    modified:
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      sql/sql_join_cache.cc
      sql/sql_select.cc
      sql/sql_select.h
      sql/sql_test.cc
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-09-02 07:34:10 +0000
+++ b/sql/sql_select.cc	2010-09-02 14:20:05 +0000
@@ -22167,7 +22167,7 @@ void select_describe(JOIN *join, bool ne
     {
       SELECT_LEX *last_select= join->unit->first_select()->last_select();
       // # characters needed to print select_number of last select
-      int last_length= log10((double)last_select->select_number)+1;
+      int last_length= (int)log10((double)last_select->select_number)+1;
 
       SELECT_LEX *sl= join->unit->first_select();
       uint len= 6, lastop= 0;


Attachment: [text/bzr-bundle] bzr/[e-mail]