From: Date: July 27 2007 1:46pm Subject: bk commit into 4.1 tree (holyfoot:1.2677) BUG#29717 List-Archive: http://lists.mysql.com/commits/31695 X-Bug: 29717 Message-Id: <20070727114649.9E4092C380A5@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-27 16:46:47+05:00, holyfoot@stripped +4 -0 Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself returns empty. As a result of this bug some SELECT ... GROUP BY queries return (NULL) row instead of empty recordset. Ultimately failure happens in end_send_group() where we decide to return the (NULL) row as JOIN::group is FALSE 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/insert_select.result@stripped, 2007-07-27 16:46:46+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/insert_select.test@stripped, 2007-07-27 16:46:46+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-27 16:46:46+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-27 16:46:46+05:00, holyfoot@stripped +2 -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/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-27 16:46:46 +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/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-27 16:46:46 +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-27 16:46:46 +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); @@ -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)) { 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-27 16:46:46 +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));