List:Commits« Previous MessageNext Message »
From:Tor Didriksen Date:December 9 2010 12:31pm
Subject:bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258) Bug#58782
View as plain text  
#At file:///export/home/didrik/repo/next-mr-opt-team-wl1393-merge/ based on revid:tor.didriksen@stripped

 3258 Tor Didriksen	2010-12-09
      Bug #58782 Missing rows with SELECT .. WHERE .. IN subquery with full GROUP BY and no aggr
      
      The GROUP BY is optimized away earlier than any example queries seen so far:
      In JOIN::optimize()
              We have found that grouping can be removed since groups correspond to
              only one row anyway, but we still have to guarantee correct result
              order. The line below effectively rewrites the query from GROUP BY
              <fields> to ORDER BY <fields>.
      So, the handling of GROUP BY to disable PQ in JOIN::exec does not catch it.
      This is a DEPENDENT SUBQUERY, which is executed once for each outer row.
      This means we have to return all rows each time we do filesort,
      i.e. we must set limit == HA_POS_ERROR, and disable PQ.
     @ mysql-test/r/group_by.result
        New test case.
     @ mysql-test/r/order_by_icp_mrr.result
        New (correct) result.
     @ mysql-test/r/order_by_none.result
        New (correct) result.
     @ mysql-test/t/group_by.test
        New test case.
     @ sql/sql_select.cc
        If the query as a GROUP BY, then remember that fact in JOIN::prepare.
        Use this fact in JOIN::exec, to disable PQ.
     @ sql/sql_select.h
        Rename group to has_group_by, for readability, add had_group_by.

    modified:
      mysql-test/r/group_by.result
      mysql-test/r/order_by_icp_mrr.result
      mysql-test/r/order_by_none.result
      mysql-test/t/group_by.test
      sql/sql_select.cc
      sql/sql_select.h
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2010-12-06 13:12:51 +0000
+++ b/mysql-test/r/group_by.result	2010-12-09 12:31:15 +0000
@@ -1886,3 +1886,48 @@ f1	MIN(f2)	MAX(f2)
 4	00:25:00	00:25:00
 DROP TABLE t1;
 #End of test#49771
+#
+# Bug #58782
+# Missing rows with SELECT .. WHERE .. IN subquery 
+# with full GROUP BY and no aggr
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (10,7);
+INSERT INTO t1 VALUES (11,1);
+INSERT INTO t1 VALUES (12,5);
+INSERT INTO t1 VALUES (13,3);
+SELECT pk AS field1, col_int_nokey AS field2 
+FROM t1 
+WHERE col_int_nokey > 0
+GROUP BY field1, field2;
+field1	field2
+10	7
+11	1
+12	5
+13	3
+CREATE TABLE where_subselect
+SELECT pk AS field1, col_int_nokey AS field2
+FROM t1
+WHERE col_int_nokey > 0
+GROUP BY field1, field2
+;
+SELECT * 
+FROM where_subselect
+WHERE (field1, field2) IN (
+SELECT pk AS field1, col_int_nokey AS field2
+FROM t1
+WHERE col_int_nokey > 0
+GROUP BY field1, field2
+);
+field1	field2
+10	7
+11	1
+12	5
+13	3
+DROP TABLE t1;
+DROP TABLE where_subselect;
+# End of Bug #58782

=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result	2010-12-09 11:54:39 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result	2010-12-09 12:31:15 +0000
@@ -2384,6 +2384,8 @@ GROUP BY field1, field2
 );
 field1	field2
 27	27
+28	28
+29	29
 DROP TABLE t1;
 DROP TABLE where_subselect;
 # End of Bug #58761

=== modified file 'mysql-test/r/order_by_none.result'
--- a/mysql-test/r/order_by_none.result	2010-12-09 11:54:39 +0000
+++ b/mysql-test/r/order_by_none.result	2010-12-09 12:31:15 +0000
@@ -2383,6 +2383,8 @@ GROUP BY field1, field2
 );
 field1	field2
 27	27
+28	28
+29	29
 DROP TABLE t1;
 DROP TABLE where_subselect;
 # End of Bug #58761

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2010-12-06 13:12:51 +0000
+++ b/mysql-test/t/group_by.test	2010-12-09 12:31:15 +0000
@@ -1273,3 +1273,52 @@ SELECT f1,MIN(f2),MAX(f2) FROM t1 GROUP 
 
 DROP TABLE t1;
 --echo #End of test#49771
+
+--echo #
+--echo # Bug #58782
+--echo # Missing rows with SELECT .. WHERE .. IN subquery 
+--echo # with full GROUP BY and no aggr
+--echo #
+
+CREATE TABLE t1 (
+  pk INT NOT NULL,
+  col_int_nokey INT,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t1 VALUES (10,7);
+INSERT INTO t1 VALUES (11,1);
+INSERT INTO t1 VALUES (12,5);
+INSERT INTO t1 VALUES (13,3);
+
+## original query:
+
+SELECT pk AS field1, col_int_nokey AS field2 
+FROM t1 
+WHERE col_int_nokey > 0
+GROUP BY field1, field2;
+
+## store query results in a new table:
+
+CREATE TABLE where_subselect
+  SELECT pk AS field1, col_int_nokey AS field2
+  FROM t1
+  WHERE col_int_nokey > 0
+  GROUP BY field1, field2
+;
+
+## query the new table and compare to original using WHERE ... IN():
+
+SELECT * 
+FROM where_subselect
+WHERE (field1, field2) IN (
+  SELECT pk AS field1, col_int_nokey AS field2
+  FROM t1
+  WHERE col_int_nokey > 0
+  GROUP BY field1, field2
+);
+
+DROP TABLE t1;
+DROP TABLE where_subselect;
+
+--echo # End of Bug #58782

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-12-09 11:54:39 +0000
+++ b/sql/sql_select.cc	2010-12-09 12:31:15 +0000
@@ -744,7 +744,8 @@ JOIN::prepare(Item ***rref_pointer_array
   /* Init join struct */
   count_field_types(select_lex, &tmp_table_param, all_fields, 0);
   ref_pointer_array_size= all_fields.elements*sizeof(Item*);
-  this->group= group_list != 0;
+  this->has_group_by= group_list != 0;
+  this->had_group_by= this->has_group_by;
   unit= unit_arg;
 
   if (tmp_table_param.sum_func_count && !group_list)
@@ -2118,7 +2119,7 @@ JOIN::optimize()
       join_tab->table->keys_in_use_for_order_by=
         join_tab->table->keys_in_use_for_group_by;
       group_list= 0;
-      group= 0;
+      has_group_by= false;
     }
     if (select_distinct &&
        list_contains_unique_index(join_tab[const_tables].table,
@@ -2183,7 +2184,8 @@ JOIN::optimize()
 	  }
 	  order=0;
         }
-	group=1;				// For end_write_group
+        has_group_by= true;                     // For end_write_group
+        had_group_by= true;
       }
       else
 	group_list= 0;
@@ -2206,7 +2208,7 @@ JOIN::optimize()
     if (old_group_list && !group_list)
       select_distinct= 0;
   }
-  if (!group_list && group)
+  if (!group_list && has_group_by)
   {
     order=0;					// The output has only one row
     simple_order=1;
@@ -2740,8 +2742,6 @@ JOIN::exec()
   int      tmp_error;
   DBUG_ENTER("JOIN::exec");
 
-  const bool has_group_by= this->group;
-
   thd_proc_info(thd, "executing");
   error= 0;
   if (procedure)
@@ -3127,7 +3127,7 @@ JOIN::exec()
     count_field_types(select_lex, &curr_join->tmp_table_param, 
                       *curr_all_fields, 0);
   
-  if (curr_join->group || curr_join->implicit_grouping ||
+  if (curr_join->has_group_by || curr_join->implicit_grouping ||
       curr_join->tmp_table_param.sum_func_count ||
       (procedure && (procedure->flags & PROC_GROUP)))
   {
@@ -3220,7 +3220,7 @@ JOIN::exec()
       }
     }
     {
-      if (group)
+      if (has_group_by)
 	curr_join->m_select_limit= HA_POS_ERROR;
       else
       {
@@ -3270,17 +3270,17 @@ JOIN::exec()
         unit->select_limit_cnt == 1 (we only need one row in the result set)
        */
       const ha_rows filesort_limit_arg=
-        (has_group_by || curr_join->tables > 1)
+        (had_group_by || curr_join->tables > 1)
         ? curr_join->m_select_limit : unit->select_limit_cnt;
       const ha_rows select_limit_arg=
         select_options & OPTION_FOUND_ROWS
         ? HA_POS_ERROR : unit->select_limit_cnt;
 
-      DBUG_PRINT("info", ("has_group_by %d "
+      DBUG_PRINT("info", ("had_group_by %d "
                           "curr_join->tables %d "
                           "curr_join->m_select_limit %d "
                           "unit->select_limit_cnt %d",
-                          has_group_by,
+                          had_group_by,
                           curr_join->tables,
                           (int) curr_join->m_select_limit,
                           (int) unit->select_limit_cnt));
@@ -9089,7 +9089,7 @@ JOIN::make_simple_join(JOIN *parent, TAB
   tmp_table_param.copy_field= tmp_table_param.copy_field_end=0;
   first_record= sort_and_group=0;
   send_records= (ha_rows) 0;
-  group= 0;
+  has_group_by= 0;
   row_limit= unit->select_limit_cnt;
   do_send_rows= row_limit ? 1 : 0;
 
@@ -18525,7 +18525,7 @@ end_send_group(JOIN *join, JOIN_TAB *joi
       (idx=test_if_item_cache_changed(join->group_fields)) >= 0)
   {
     if (join->first_record || 
-        (end_of_records && !join->group && !join->group_optimized_away))
+        (end_of_records && !join->has_group_by && !join->group_optimized_away))
     {
       if (join->procedure)
 	join->procedure->end_group();
@@ -18821,7 +18821,7 @@ end_write_group(JOIN *join, JOIN_TAB *jo
   if (!join->first_record || end_of_records ||
       (idx=test_if_item_cache_changed(join->group_fields)) >= 0)
   {
-    if (join->first_record || (end_of_records && !join->group))
+    if (join->first_record || (end_of_records && !join->has_group_by))
     {
       if (join->procedure)
 	join->procedure->end_group();
@@ -21216,7 +21216,10 @@ calc_group_buffer(JOIN *join,ORDER *grou
   uint key_length=0, parts=0, null_parts=0;
 
   if (group)
-    join->group= 1;
+  {
+    join->has_group_by= true;
+    join->had_group_by= true;
+  }
   for (; group ; group=group->next)
   {
     Item *group_item= *group->item;
@@ -23622,7 +23625,7 @@ test_if_cheaper_ordering(const JOIN_TAB 
   bool is_best_covering= FALSE;
   double fanout= 1;
   ha_rows table_records= table->file->stats.records;
-  bool group= join && join->group && order == join->group_list;
+  bool group= join && join->has_group_by && order == join->group_list;
   ha_rows ref_key_quick_rows= HA_POS_ERROR;
 
   /*

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2010-12-06 15:21:08 +0000
+++ b/sql/sql_select.h	2010-12-09 12:31:15 +0000
@@ -1638,7 +1638,8 @@ public:
   */
   bool     sort_and_group; 
   bool     first_record,full_join, no_field_update;
-  bool	   group;          /**< If query contains GROUP BY clause */
+  bool     has_group_by; ///< If query contains GROUP BY clause.
+  bool     had_group_by; ///< If query contained GROUP BY before optimizations.
   bool	   do_send_rows;
   table_map const_table_map,found_const_table_map;
   /*
@@ -1978,7 +1979,7 @@ public:
   JOIN_TAB *get_sort_by_join_tab()
   {
     return (!sort_by_table || skip_sort_order ||
-            ((group || tmp_table_param.sum_func_count) && !group_list)) ?
+            ((has_group_by || tmp_table_param.sum_func_count) && !group_list)) ?
               NULL : join_tab+const_tables;
   }
 private:


Attachment: [text/bzr-bundle] bzr/tor.didriksen@oracle.com-20101209123115-fduno7x6gpt9n2tp.bundle
Thread
bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258) Bug#58782Tor Didriksen9 Dec
  • Re: bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258)Bug#58782Jorgen Loland10 Dec
    • Re: bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258) Bug#58782Tor Didriksen10 Dec