summaryrefslogtreecommitdiff
path: root/mysql-test/t
diff options
context:
space:
mode:
authorunknown <holyfoot/hf@mysql.com/hfmain.(none)>2007-07-31 10:46:04 +0500
committerunknown <holyfoot/hf@mysql.com/hfmain.(none)>2007-07-31 10:46:04 +0500
commit791584ae0d57ce1f31b4652c1dd9cb4edee8526e (patch)
tree7f93a63d138c3bb1107b87f1d770f853b438e461 /mysql-test/t
parent1a2d2117ffcd9641ed472039f29c8996023e7b60 (diff)
downloadmariadb-git-791584ae0d57ce1f31b4652c1dd9cb4edee8526e.tar.gz
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: Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself returns empty. test result mysql-test/r/insert_select.result: Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself returns empty. test result mysql-test/t/group_by.test: 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: Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself returns empty. test case sql/sql_select.cc: 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: 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
Diffstat (limited to 'mysql-test/t')
-rw-r--r--mysql-test/t/group_by.test23
-rw-r--r--mysql-test/t/insert_select.test28
2 files changed, 51 insertions, 0 deletions
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 064d46aa0c0..180299f7f4a 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -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 --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
index fcea489fcff..dfb313706dd 100644
--- a/mysql-test/t/insert_select.test
+++ b/mysql-test/t/insert_select.test
@@ -239,4 +239,32 @@ INSERT INTO t1 SELECT a + 2 FROM t1 LIMIT 1;
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