summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorSergei Golubchik <serg@mariadb.org>2019-09-24 19:47:45 +0200
committerSergei Golubchik <serg@mariadb.org>2019-10-14 10:29:30 +0200
commitc7320830a62b0ed3245c476f074c534d3cd20027 (patch)
tree2e4386e647286b0a53be85f751758b92cdecf88e
parent173ae63114d3c36ce8366d50ac17c18765ab2ceb (diff)
downloadmariadb-git-c7320830a62b0ed3245c476f074c534d3cd20027.tar.gz
outer references in subqueries in INSERT
remove inconsistent limitation
-rw-r--r--mysql-test/main/insert.result16
-rw-r--r--mysql-test/main/insert.test13
-rw-r--r--mysql-test/main/subselect.result2
-rw-r--r--mysql-test/main/subselect.test2
-rw-r--r--mysql-test/main/subselect_no_exists_to_in.result2
-rw-r--r--mysql-test/main/subselect_no_mat.result2
-rw-r--r--mysql-test/main/subselect_no_opts.result2
-rw-r--r--mysql-test/main/subselect_no_scache.result2
-rw-r--r--mysql-test/main/subselect_no_semijoin.result2
-rw-r--r--sql/sql_insert.cc19
10 files changed, 29 insertions, 33 deletions
diff --git a/mysql-test/main/insert.result b/mysql-test/main/insert.result
index 4aea81262d2..d8f40b7383a 100644
--- a/mysql-test/main/insert.result
+++ b/mysql-test/main/insert.result
@@ -751,3 +751,19 @@ REPLACE INTO v1 SET f = NULL;
ERROR 22007: Truncated incorrect DOUBLE value: 'foo'
DROP VIEW v1;
DROP TABLE t1;
+create table t1 (a int default 5);
+insert t1 values (1);
+insert t1 values (a);
+insert t1 values ((select a+1));
+insert t1 set a=2;
+insert t1 set a=a+2;
+insert t1 set a=(select a+3);
+select * from t1;
+a
+1
+5
+6
+2
+7
+8
+drop table t1;
diff --git a/mysql-test/main/insert.test b/mysql-test/main/insert.test
index e00c9cd7a0d..4ccaa20ece8 100644
--- a/mysql-test/main/insert.test
+++ b/mysql-test/main/insert.test
@@ -612,3 +612,16 @@ CREATE VIEW v1 AS SELECT * FROM t1 WHERE f <=> 'foo' WITH CHECK OPTION;
REPLACE INTO v1 SET f = NULL;
DROP VIEW v1;
DROP TABLE t1;
+
+#
+# outer referencesin subqueries in INSERT
+#
+create table t1 (a int default 5);
+insert t1 values (1);
+insert t1 values (a);
+insert t1 values ((select a+1));
+insert t1 set a=2;
+insert t1 set a=a+2;
+insert t1 set a=(select a+3);
+select * from t1;
+drop table t1;
diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result
index 2f78a77b10f..abf758469f0 100644
--- a/mysql-test/main/subselect.result
+++ b/mysql-test/main/subselect.result
@@ -682,8 +682,6 @@ x
3
11
11
-INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
-ERROR 42S22: Unknown column 'x' in 'field list'
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
select * from t1;
x
diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test
index 8b34e32740b..f6333c22684 100644
--- a/mysql-test/main/subselect.test
+++ b/mysql-test/main/subselect.test
@@ -394,8 +394,6 @@ select * from t1;
# After this, only data based on old t1 records should have been added.
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
select * from t1;
--- error ER_BAD_FIELD_ERROR
-INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
let $row_count_before= `SELECT COUNT(*) FROM t1`;
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
let $wait_condition= SELECT COUNT(*) <> $row_count_before FROM t1;
diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result
index e830c8bdc91..d8704730a92 100644
--- a/mysql-test/main/subselect_no_exists_to_in.result
+++ b/mysql-test/main/subselect_no_exists_to_in.result
@@ -686,8 +686,6 @@ x
3
11
11
-INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
-ERROR 42S22: Unknown column 'x' in 'field list'
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
select * from t1;
x
diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result
index 8203f59f92b..f411467fa66 100644
--- a/mysql-test/main/subselect_no_mat.result
+++ b/mysql-test/main/subselect_no_mat.result
@@ -689,8 +689,6 @@ x
3
11
11
-INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
-ERROR 42S22: Unknown column 'x' in 'field list'
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
select * from t1;
x
diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result
index ec4d0d99a6d..39306df943a 100644
--- a/mysql-test/main/subselect_no_opts.result
+++ b/mysql-test/main/subselect_no_opts.result
@@ -685,8 +685,6 @@ x
3
11
11
-INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
-ERROR 42S22: Unknown column 'x' in 'field list'
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
select * from t1;
x
diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result
index f4c850409da..39149cf04b7 100644
--- a/mysql-test/main/subselect_no_scache.result
+++ b/mysql-test/main/subselect_no_scache.result
@@ -688,8 +688,6 @@ x
3
11
11
-INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
-ERROR 42S22: Unknown column 'x' in 'field list'
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
select * from t1;
x
diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result
index e1d147da761..464df7e6539 100644
--- a/mysql-test/main/subselect_no_semijoin.result
+++ b/mysql-test/main/subselect_no_semijoin.result
@@ -685,8 +685,6 @@ x
3
11
11
-INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
-ERROR 42S22: Unknown column 'x' in 'field list'
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
select * from t1;
x
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 515f40212fb..266aed3c866 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -1512,25 +1512,6 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list,
DBUG_RETURN(TRUE);
if (thd->lex->handle_list_of_derived(table_list, DT_PREPARE))
DBUG_RETURN(TRUE);
- /*
- For subqueries in VALUES() we should not see the table in which we are
- inserting (for INSERT ... SELECT this is done by changing table_list,
- because INSERT ... SELECT share SELECT_LEX it with SELECT.
- */
- if (!select_insert)
- {
- for (SELECT_LEX_UNIT *un= select_lex->first_inner_unit();
- un;
- un= un->next_unit())
- {
- for (SELECT_LEX *sl= un->first_select();
- sl;
- sl= sl->next_select())
- {
- sl->context.outer_context= 0;
- }
- }
- }
if (duplic == DUP_UPDATE)
{