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

1 files changed, 62 insertions, 0 deletions

diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result
index 303d7186015..753dc2cd749 100644
--- a/mysql-test/r/insert_update.result
+++ b/mysql-test/r/insert_update.result
@@ -105,3 +105,65 @@ a	b
 8	28
 9	29
 drop table t1;
+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;
+a	b	c
+1	2	10
+3	4	20
+5	6	30
+INSERT t1 SELECT 5,7,40 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100;
+SELECT * FROM t1;
+a	b	c
+1	2	10
+3	4	20
+5	6	130
+INSERT t1 SELECT 8,4,50 FROM DUAL ON DUPLICATE KEY UPDATE c=c+1000;
+SELECT * FROM t1;
+a	b	c
+1	2	10
+3	4	1020
+5	6	130
+INSERT t1 SELECT 1,4,60 FROM DUAL ON DUPLICATE KEY UPDATE c=c+10000;
+SELECT * FROM t1;
+a	b	c
+1	2	10010
+3	4	1020
+5	6	130
+INSERT t1 SELECT 1,9,70 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100000, b=4;
+ERROR 23000: Duplicate entry '4' for key 2
+SELECT * FROM t1;
+a	b	c
+1	2	10010
+3	4	1020
+5	6	130
+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;
+a	b	c
+1	2	10
+3	4	120
+5	6	30
+8	9	60
+INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0;
+SELECT * FROM t1;
+a	b	c
+1	2	10
+3	4	120
+5	0	30
+8	9	60
+INSERT t1 SELECT x,y,z FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a);
+SELECT *, VALUES(a) FROM t1;
+a	b	c	VALUES(a)
+1	2	10	NULL
+3	4	127	NULL
+5	0	30	NULL
+8	9	60	NULL
+2	1	11	NULL
+DROP TABLE t1;
+DROP TABLE t2;