From 46364ddb1978802ad9bf5418738b03d0cfe8bd61 Mon Sep 17 00:00:00 2001
From: unknown <acurtis@pcgem.rdg.cyberkinetica.com>
Date: Mon, 13 Dec 2004 12:26:28 +0000
Subject: WL#2274 - INSERT..SELECT..UPDATE   UPDATE clause conflicts with
 SELECT for use of item_list field.   Alter UPDATE clause to use new lex field
 update_list   Tests included

mysql-test/r/insert_update.result:
  WL#2274
    New tests for INSERT..SELECT..UPDATE
mysql-test/t/insert_update.test:
  WL#2274
    New tests for INSERT..SELECT..UPDATE
sql/mysql_priv.h:
  Remove function - insert_select_precheck()
sql/sql_class.h:
  WL#2274
    New constructor for class select_insert
sql/sql_insert.cc:
  WL#2274
    Move code into mysql_prepare_insert
    Add checks as param values may be NULL
sql/sql_lex.cc:
  WL#2274
    initialize lex->update_list
sql/sql_lex.h:
  WL#2274
    New field in LEX: update_list
sql/sql_parse.cc:
  WL#2274
    INSERT..UPDATE clause now populates lex->update_list
    Remove redundant function: insert_select_precheck()
sql/sql_prepare.cc:
  WL#2274
    invoke insert_precheck() instead of insert_select_precheck()
sql/sql_yacc.yy:
  WL#2274
    Enable INSERT..SELECT..UPDATE syntax
    New rule - insert_update_list, to populate lex->update_list
---
 mysql-test/t/insert_update.test | 31 +++++++++++++++++++++++++++++++
 1 file changed, 31 insertions(+)

(limited to 'mysql-test/t/insert_update.test')

diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test
index fc54ce88f8a..182baa641da 100644
--- a/mysql-test/t/insert_update.test
+++ b/mysql-test/t/insert_update.test
@@ -48,3 +48,34 @@ disable_info;
 
 select * from t1;
 drop table t1;
+
+# WorkLog #2274 - enable INSERT .. SELECT .. UPDATE syntax
+# Same tests as beginning of this test except that insert source
+# is a result from a select statement
+#
+CREATE TABLE t1 (a INT, b INT, c INT, UNIQUE (A), UNIQUE(B));
+INSERT t1 VALUES (1,2,10), (3,4,20);
+INSERT t1 SELECT 5,6,30 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100;
+SELECT * FROM t1;
+INSERT t1 SELECT 5,7,40 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100;
+SELECT * FROM t1;
+INSERT t1 SELECT 8,4,50 FROM DUAL ON DUPLICATE KEY UPDATE c=c+1000;
+SELECT * FROM t1;
+INSERT t1 SELECT 1,4,60 FROM DUAL ON DUPLICATE KEY UPDATE c=c+10000;
+SELECT * FROM t1;
+-- error 1062
+INSERT t1 SELECT 1,9,70 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100000, b=4;
+SELECT * FROM t1;
+TRUNCATE TABLE t1;
+INSERT t1 VALUES (1,2,10), (3,4,20);
+CREATE TABLE t2 (x INT, y INT, z INT, d INT);
+INSERT t2 VALUES (5,6,30,1), (7,4,40,1), (8,9,60,1);
+INSERT t2 VALUES (2,1,11,2), (7,4,40,2);
+INSERT t1 SELECT x,y,z FROM t2 WHERE d=1 ON DUPLICATE KEY UPDATE c=c+100;
+SELECT * FROM t1;
+INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0;
+SELECT * FROM t1;
+INSERT t1 SELECT x,y,z FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a);
+SELECT *, VALUES(a) FROM t1;
+DROP TABLE t1;
+DROP TABLE t2;
-- 
cgit v1.2.1