From: Date: July 31 2007 7:46am
Subject: bk commit into 4.1 tree (holyfoot:1.2677) BUG#29717
List-Archive: http://lists.mysql.com/commits/31868
X-Bug: 29717
Message-Id: <20070731054606.92A5C2C380A5@hfmain.localdomain>

Below is the list of changes that have just been committed into a local
4.1 repository of hf. When hf does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-07-31 10:46:04+05:00, holyfoot@stripped +6 -0
  Bug #29717 INSERT INTO SELECT inserts values even if
   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@stripped, 2007-07-31 10:45:59+05:00, holyfoot@stripped +17 -0
    Bug #29717 INSERT INTO SELECT inserts values even if
     SELECT statement itself returns empty.
    
    test result

  mysql-test/r/insert_select.result@stripped, 2007-07-31 10:45:59+05:00, holyfoot@stripped +26 -0
    Bug #29717 INSERT INTO SELECT inserts values even if
     SELECT statement itself returns empty.
    
    test result

  mysql-test/t/group_by.test@stripped, 2007-07-31 10:45:59+05:00, holyfoot@stripped +23 -0
    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@stripped, 2007-07-31 10:45:59+05:00, holyfoot@stripped +28 -0
    Bug #29717 INSERT INTO SELECT inserts values even if
     SELECT statement itself returns empty.
    
    test case

  sql/sql_select.cc@stripped, 2007-07-31 10:45:59+05:00, holyfoot@stripped +3 -1
    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@stripped, 2007-07-31 10:45:59+05:00, holyfoot@stripped +9 -0
    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

diff -Nrup a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
--- a/mysql-test/r/group_by.result	2006-10-16 15:10:18 +05:00
+++ b/mysql-test/r/group_by.result	2007-07-31 10:45:59 +05:00
@@ -818,3 +818,20 @@ a
 2
 1
 DROP TABLE t1;
+CREATE TABLE t1 (
+f1 int(10) unsigned NOT NULL auto_increment primary key,
+f2 varchar(100) NOT NULL default ''
+);
+CREATE TABLE t2 (
+f1 varchar(10) NOT NULL default '',
+f2 char(3) NOT NULL default '',
+PRIMARY KEY  (`f1`),
+KEY `k1` (`f2`,`f1`)
+);
+INSERT INTO t1 values(NULL, '');
+INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
+SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+avg(t2.f1)
+SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+avg(t2.f1)
+DROP TABLE t1, t2;
diff -Nrup a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
--- a/mysql-test/r/insert_select.result	2006-06-19 15:22:38 +05:00
+++ b/mysql-test/r/insert_select.result	2007-07-31 10:45:59 +05:00
@@ -690,3 +690,29 @@ CREATE TABLE t1 (a int PRIMARY KEY);
 INSERT INTO t1 values (1), (2);
 INSERT INTO t1 SELECT a + 2 FROM t1 LIMIT 1;
 DROP TABLE t1;
+CREATE TABLE t1 (
+f1 int(10) unsigned NOT NULL auto_increment PRIMARY KEY,
+f2 varchar(100) NOT NULL default ''
+);
+CREATE TABLE t2 (
+f1 varchar(10) NOT NULL default '',
+f2 char(3) NOT NULL default '',
+PRIMARY KEY  (`f1`),
+KEY `k1` (`f2`, `f1`)
+);
+INSERT INTO t1 values(NULL, '');
+INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+1
+SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+min(t2.f1)
+INSERT INTO t1 (f2)
+SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+1
+SELECT * FROM t1;
+f1	f2
+1	
+DROP TABLE t1, t2;
diff -Nrup a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
--- a/mysql-test/t/group_by.test	2006-10-16 15:10:19 +05:00
+++ b/mysql-test/t/group_by.test	2007-07-31 10:45:59 +05:00
@@ -633,4 +633,27 @@ SELECT a FROM t1 ORDER BY 'a' DESC;
 SELECT a FROM t1 ORDER BY "a" DESC;
 SELECT a FROM t1 ORDER BY `a` DESC;
 DROP TABLE t1;
+
+
+#
+# Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself
+# returns empty
+# 
+CREATE TABLE t1 (
+    f1 int(10) unsigned NOT NULL auto_increment primary key,
+    f2 varchar(100) NOT NULL default ''
+);
+CREATE TABLE t2 (
+    f1 varchar(10) NOT NULL default '',
+    f2 char(3) NOT NULL default '',
+    PRIMARY KEY  (`f1`),
+    KEY `k1` (`f2`,`f1`)
+);
+
+INSERT INTO t1 values(NULL, '');
+INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
+SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+DROP TABLE t1, t2;
+
 # End of 4.1 tests
diff -Nrup a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
--- a/mysql-test/t/insert_select.test	2006-06-19 15:22:38 +05:00
+++ b/mysql-test/t/insert_select.test	2007-07-31 10:45:59 +05:00
@@ -239,4 +239,32 @@ INSERT INTO t1 SELECT a + 2 FROM t1 LIMI
 
 DROP TABLE t1;
 
+#
+# Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself returns empty
+#
+
+CREATE TABLE t1 (
+    f1 int(10) unsigned NOT NULL auto_increment PRIMARY KEY,
+    f2 varchar(100) NOT NULL default ''
+);
+CREATE TABLE t2 (
+    f1 varchar(10) NOT NULL default '',
+    f2 char(3) NOT NULL default '',
+    PRIMARY KEY  (`f1`),
+    KEY `k1` (`f2`, `f1`)
+);
+
+INSERT INTO t1 values(NULL, '');
+INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
+SELECT COUNT(*) FROM t1;
+
+SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+
+INSERT INTO t1 (f2)
+  SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t1;
+DROP TABLE t1, t2;
+
 # End of 4.1 tests
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-05-15 11:55:16 +05:00
+++ b/sql/sql_select.cc	2007-07-31 10:45:59 +05:00
@@ -777,6 +777,7 @@ JOIN::optimize()
     order=0;					// The output has only one row
     simple_order=1;
     select_distinct= 0;                       // No need in distinct for 1 row
+    group_optimized_away= 1;
   }
 
   calc_group_buffer(this, group_list);
@@ -6896,7 +6897,8 @@ end_send_group(JOIN *join, JOIN_TAB *joi
   if (!join->first_record || end_of_records ||
       (idx=test_if_group_changed(join->group_fields)) >= 0)
   {
-    if (join->first_record || (end_of_records && !join->group))
+    if (join->first_record || 
+        (end_of_records && !join->group && !join->group_optimized_away))
     {
       if (join->procedure)
 	join->procedure->end_group();
diff -Nrup a/sql/sql_select.h b/sql/sql_select.h
--- a/sql/sql_select.h	2006-06-28 18:28:25 +05:00
+++ b/sql/sql_select.h	2007-07-31 10:45:59 +05:00
@@ -180,6 +180,14 @@ class JOIN :public Sql_alloc
   ROLLUP rollup;				// Used with rollup
 
   bool select_distinct;				// Set if SELECT DISTINCT
+  /*
+    If we have the GROUP BY statement in the query,
+    but the group_list was emptied by optimizer, this
+    flag is TRUE.
+    It happens when fields in the GROUP BY are from
+    constant table
+  */
+  bool group_optimized_away;
 
   /*
     simple_xxxxx is set if ORDER/GROUP BY doesn't include any references
@@ -276,6 +284,7 @@ class JOIN :public Sql_alloc
     ref_pointer_array_size= 0;
     zero_result_cause= 0;
     optimized= 0;
+    group_optimized_away= 0;
 
     fields_list= fields_arg;
     bzero((char*) &keyuse,sizeof(keyuse));