bk commit into 4.1 tree (holyfoot:1.2677) BUG#29717

[holyfoot]

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-25 19:13:02+05:00, holyfoot@stripped +6 -0
  Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself returns
empty.
  
  We use different way to handle this SELECT in the INSERT INTO as we insert
  in the same table what we use in the select. So we use temporary table,
  and as we have suitable index, we can calculate group values at once
  and store them in the temporary table. At the same time the table
  whose field is in the GROUP BY contains just a single row, so optimizer
  decides to remove the group list at all.
  Still SELECT min(x) from empty_table; and
        SELECT min(x) from empty_table GROUP BY y; have to return different
  results - first query should return the single (NULL) row, second -
  an empty recordset.
  So this fix remembers the case when GROUP BY existed and was removed
  by optimizer and suppress the (NULL) row if that was the case.

  mysql-test/r/group_by.result@stripped, 2007-07-25 19:13:00+05:00, holyfoot@stripped +20 -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-25 19:13:01+05:00, holyfoot@stripped
+30 -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-25 19:13:01+05:00, holyfoot@stripped +24 -0
    Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself returns
empty.
    
    test case

  mysql-test/t/insert_select.test@stripped, 2007-07-25 19:13:01+05:00, holyfoot@stripped +32
-0
    Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself returns
empty.
    
    test case

  sql/sql_select.cc@stripped, 2007-07-25 19:13:01+05:00, holyfoot@stripped +4 -1
    Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself returns
empty.
    
    remember the 'GROUP BY statement removed by optimizer' case in the
    group_optimized and restore join->group if that was the case.

  sql/sql_select.h@stripped, 2007-07-25 19:13:01+05:00, holyfoot@stripped +2 -0
    Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself returns
empty.
    
    JOIN::group_optimized added to remember 'GROUP BY was optimized 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-25 19:13:00 +05:00
@@ -818,3 +818,23 @@ a
 2
 1
 DROP TABLE t1;
+CREATE TABLE t1 (
+f1 int(10) unsigned NOT NULL auto_increment,
+f2 int(10) unsigned NOT NULL default '0',
+f3 decimal(7,2) unsigned NOT NULL default '0.00',
+f4 varchar(100) NOT NULL default '',
+PRIMARY KEY  (`f1`), KEY `k1` (`f2`)
+);
+CREATE TABLE t2 (
+f1 varchar(10) NOT NULL default '',
+f2 char(3) NOT NULL default '',
+f3 char(3) NOT NULL default '',
+PRIMARY KEY  (`f1`),
+KEY `k1` (`f2`,`f3`,`f1`)
+);
+INSERT INTO t1 (f2,f3) values('298', '1639.00');
+INSERT INTO `t2` VALUES ('486878','WDT','YYZ'),('486910','WDT','YYZ');
+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)
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-25 19:13:01 +05:00
@@ -690,3 +690,33 @@ 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,
+f2 int(10) unsigned NOT NULL default '0',
+f3 decimal(7,2) unsigned NOT NULL default '0.00',
+f4 varchar(100) NOT NULL default '',
+PRIMARY KEY  (`f1`), KEY `k1` (`f2`)
+);
+CREATE TABLE t2 (
+f1 varchar(10) NOT NULL default '',
+f2 char(3) NOT NULL default '',
+f3 char(3) NOT NULL default '',
+PRIMARY KEY  (`f1`),
+KEY `k1` (`f2`,`f3`,`f1`)
+);
+INSERT INTO t1 (f2,f3) values('298', '1639.00');
+INSERT INTO `t2` VALUES ('486878','WDT','YYZ'),('486910','WDT','YYZ');
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+1
+SELECT min(pf.f1) as f4_not FROM t1 as ot join t2 as pf on ( pf.f2 = 'SIR') GROUP BY
ot.f1;
+f4_not
+INSERT INTO t1 (f4)
+( 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	f3	f4
+1	298	1639.00	
+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-25 19:13:01 +05:00
@@ -633,4 +633,28 @@ 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,
+    f2 int(10) unsigned NOT NULL default '0',
+    f3 decimal(7,2) unsigned NOT NULL default '0.00',
+    f4 varchar(100) NOT NULL default '',
+    PRIMARY KEY  (`f1`), KEY `k1` (`f2`)
+);
+CREATE TABLE t2 (
+    f1 varchar(10) NOT NULL default '',
+    f2 char(3) NOT NULL default '',
+    f3 char(3) NOT NULL default '',
+    PRIMARY KEY  (`f1`),
+    KEY `k1` (`f2`,`f3`,`f1`)
+);
+
+INSERT INTO t1 (f2,f3) values('298', '1639.00');
+INSERT INTO `t2` VALUES ('486878','WDT','YYZ'),('486910','WDT','YYZ');
+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;
+
 # 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-25 19:13:01 +05:00
@@ -239,4 +239,36 @@ 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,
+    f2 int(10) unsigned NOT NULL default '0',
+    f3 decimal(7,2) unsigned NOT NULL default '0.00',
+    f4 varchar(100) NOT NULL default '',
+    PRIMARY KEY  (`f1`), KEY `k1` (`f2`)
+);
+CREATE TABLE t2 (
+    f1 varchar(10) NOT NULL default '',
+    f2 char(3) NOT NULL default '',
+    f3 char(3) NOT NULL default '',
+    PRIMARY KEY  (`f1`),
+    KEY `k1` (`f2`,`f3`,`f1`)
+);
+
+INSERT INTO t1 (f2,f3) values('298', '1639.00');
+INSERT INTO `t2` VALUES ('486878','WDT','YYZ'),('486910','WDT','YYZ');
+SELECT COUNT(*) FROM t1;
+
+SELECT min(pf.f1) as f4_not FROM t1 as ot join t2 as pf on ( pf.f2 = 'SIR') GROUP BY
ot.f1;
+
+INSERT INTO t1 (f4)
+( 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-25 19:13:01 +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= 1;
   }
 
   calc_group_buffer(this, group_list);
@@ -3590,9 +3591,11 @@ make_simple_join(JOIN *join,TABLE *tmp_t
   join->tmp_table_param.copy_field=join->tmp_table_param.copy_field_end=0;
   join->first_record=join->sort_and_group=0;
   join->send_records=(ha_rows) 0;
-  join->group=0;
+  if (!join->group_optimized)
+    join->group=0;
   join->row_limit=join->unit->select_limit_cnt;
   join->do_send_rows = (join->row_limit) ? 1 : 0;
+  join->group_optimized= 0;
 
   join_tab->cache.buff=0;			/* No caching */
   join_tab->table=tmp_table;
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-25 19:13:01 +05:00
@@ -180,6 +180,7 @@ class JOIN :public Sql_alloc
   ROLLUP rollup;				// Used with rollup
 
   bool select_distinct;				// Set if SELECT DISTINCT
+  bool group_optimized; // Group list removed by optimizer
 
   /*
     simple_xxxxx is set if ORDER/GROUP BY doesn't include any references
@@ -276,6 +277,7 @@ class JOIN :public Sql_alloc
     ref_pointer_array_size= 0;
     zero_result_cause= 0;
     optimized= 0;
+    group_optimized= 0;
 
     fields_list= fields_arg;
     bzero((char*) &keyuse,sizeof(keyuse));