Fix for bug#18437 "Wrong values inserted with a before update trigger on

NDB table".

SQL-layer was not marking fields which were used in triggers as such. As
result these fields were not always properly retrieved/stored by handler
layer. So one might got wrong values or lost changes in triggers for NDB,
Federated and possibly InnoDB tables.
This fix solves the problem by marking fields used in triggers
appropriately.

Also this patch contains the following cleanup of ha_ndbcluster code:

We no longer rely on reading LEX::sql_command value in handler in order
to determine if we can enable optimization which allows us to handle REPLACE
statement in more efficient way by doing replaces directly in write_row()
method without reporting error to SQL-layer.
Instead we rely on SQL-layer informing us whether this optimization
applicable by calling handler::extra() method with
HA_EXTRA_WRITE_CAN_REPLACE flag.
As result we no longer apply this optimzation in cases when it should not
be used (e.g. if we have on delete triggers on table) and use in some
additional cases when it is applicable (e.g. for LOAD DATA REPLACE).

Finally this patch includes fix for bug#20728 "REPLACE does not work
correctly for NDB table with PK and unique index".
  
This was yet another problem which was caused by improper field mark-up.
During row replacement fields which weren't explicity used in REPLACE
statement were not marked as fields to be saved (updated) so they have
retained values from old row version. The fix is to mark all table
fields as set for REPLACE statement. Note that in 5.1 we already solve
this problem by notifying handler that it should save values from all
fields only in case when real replacement happens.

3 files changed, 171 insertions, 0 deletions

diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test
index 80b31c610a2..6e53b1e3adb 100644
--- a/mysql-test/t/federated.test
+++ b/mysql-test/t/federated.test
@@ -1310,4 +1310,46 @@ connection slave;
 DROP TABLE federated.bug_17377_table;
 
 
+# 
+# Additional test for bug#18437 "Wrong values inserted with a before
+# update trigger on NDB table". SQL-layer didn't properly inform
+# handler about fields which were read and set in triggers. In some
+# cases this resulted in incorrect (garbage) values of OLD variables
+# and lost changes to NEW variables.
+# Since for federated engine only operation which is affected by wrong
+# fields mark-up is handler::write_row() this file constains coverage
+# for ON INSERT triggers only. Tests for other types of triggers reside
+# in ndb_trigger.test.
+#
+--disable_warnings
+drop table if exists federated.t1;
+--enable_warnings
+create table federated.t1 (a int, b int, c int);
+connection master;
+--disable_warnings
+drop table if exists federated.t1;
+drop table if exists federated.t2;
+--enable_warnings
+--replace_result $SLAVE_MYPORT SLAVE_PORT
+eval create table federated.t1 (a int,  b int, c int) engine=federated connection='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';
+create trigger federated.t1_bi before insert on federated.t1 for each row set new.c= new.a * new.b;
+create table federated.t2 (a int, b int);
+insert into federated.t2 values (13, 17), (19, 23);
+# Each of three statements should correctly set values for all three fields
+# insert
+insert into federated.t1 (a, b) values (1, 2), (3, 5), (7, 11);
+select * from federated.t1;
+delete from federated.t1;
+# insert ... select
+insert into federated.t1 (a, b) select * from federated.t2;
+select * from federated.t1;
+delete from federated.t1;
+# load
+load data infile '../std_data_ln/loaddata5.dat' into table federated.t1 fields terminated by '' enclosed by '' ignore 1 lines (a, b);
+select * from federated.t1;
+drop tables federated.t1, federated.t2;
+
+connection slave;
+drop table federated.t1;
+
 source include/federated_cleanup.inc;
diff --git a/mysql-test/t/ndb_replace.test b/mysql-test/t/ndb_replace.test
index 94a11f7dfb2..476a607ed44 100644
--- a/mysql-test/t/ndb_replace.test
+++ b/mysql-test/t/ndb_replace.test
@@ -39,6 +39,7 @@ INSERT  INTO t1 VALUES (1,1,23),(2,2,24);
 REPLACE INTO t1 (j,k) VALUES (1,42);
 REPLACE INTO t1 (i,j) VALUES (17,2);
 SELECT * from t1 ORDER BY i;
+DROP TABLE t1;
 
 # bug#19906
 CREATE TABLE t2 (a INT(11) NOT NULL,
@@ -64,4 +65,40 @@ SELECT * FROM t2 ORDER BY id;
 
 DROP TABLE t2;
 
+#
+# Bug #20728 "REPLACE does not work correctly for NDB table with PK and
+#             unique index"
+#
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (pk int primary key, apk int unique, data int) engine=ndbcluster;
+# Test for plain replace which updates pk
+insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3);
+replace into t1 (pk, apk) values (4, 1), (5, 2);
+select * from t1 order by pk;
+delete from t1;
+# Another test for plain replace which doesn't touch pk
+insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3);
+replace into t1 (pk, apk) values (1, 4), (2, 5);
+select * from t1 order by pk;
+delete from t1;
+# Test for load data replace which updates pk
+insert into t1 values (1, 1, 1), (4, 4, 4), (6, 6, 6);
+load data infile '../std_data_ln/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk);
+select * from t1 order by pk;
+delete from t1;
+# Now test for load data replace which doesn't touch pk
+insert into t1 values (1, 1, 1), (3, 3, 3), (5, 5, 5);
+load data infile '../std_data_ln/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk);
+select * from t1 order by pk;
+delete from t1;
+# Finally test for both types of replace ... select
+insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3);
+replace into t1 (pk, apk) select 4, 1;
+replace into t1 (pk, apk) select 2, 4;
+select * from t1 order by pk;
+# Clean-up
+drop table t1;
 
+--echo End of 5.0 tests.
diff --git a/mysql-test/t/ndb_trigger.test b/mysql-test/t/ndb_trigger.test
new file mode 100644
index 00000000000..2521ef17842
--- /dev/null
+++ b/mysql-test/t/ndb_trigger.test
@@ -0,0 +1,92 @@
+# Tests which involve triggers and NDB storage engine
+--source include/have_ndb.inc
+--source include/not_embedded.inc
+
+# 
+# Test for bug#18437 "Wrong values inserted with a before update
+# trigger on NDB table". SQL-layer didn't properly inform handler
+# about fields which were read and set in triggers. In some cases 
+# this resulted in incorrect (garbage) values of OLD variables and
+# lost changes to NEW variables.
+# You can find similar tests for ON INSERT triggers in federated.test
+# since this engine so far is the only engine in MySQL which cares
+# about field mark-up during handler::write_row() operation.
+#
+
+--disable_warnings
+drop table if exists t1, t2, t3;
+--enable_warnings
+
+create table t1 (id int primary key, a int not null, b decimal (63,30) default 0) engine=ndb;
+create table t2 (op char(1), a int not null, b decimal (63,30));
+create table t3 select 1 as i;
+	
+delimiter //;
+create trigger t1_bu before update on t1 for each row
+begin
+  insert into t2 values ("u", old.a, old.b);
+  set new.b = old.b + 10;
+end;//
+create trigger t1_bd before delete on t1 for each row
+begin
+  insert into t2 values ("d", old.a, old.b);
+end;//
+delimiter ;//
+insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (3, 3, 3.05), (4, 4, 4.05);
+
+# Check that usual update works as it should
+update t1 set a=5 where a != 3;
+select * from t1 order by id;
+select * from t2 order by op, a, b;
+delete from t2;
+# Check that everything works for multi-update
+update t1, t3 set a=6 where a = 5;
+select * from t1 order by id;
+select * from t2 order by op, a, b;
+delete from t2;
+# Check for delete
+delete from t1 where a != 3;
+select * from t1 order by id;
+select * from t2 order by op, a, b;
+delete from t2;
+# Check for multi-delete
+insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (4, 4, 4.05);
+delete t1 from t1, t3 where a != 3;
+select * from t1 order by id;
+select * from t2 order by op, a, b;
+delete from t2;
+# Check for insert ... on duplicate key update
+insert into t1 values (4, 4, 4.05);
+insert into t1 (id, a) values (4, 1), (3, 1) on duplicate key update a= a + 1;
+select * from t1 order by id;
+select * from t2 order by op, a, b;
+delete from t2;
+# Check for insert ... select ... on duplicate key update
+delete from t3;
+insert into t3 values (4), (3);
+insert into t1 (id, a) (select i, 1 from t3) on duplicate key update a= a + 1;
+select * from t1 order by id;
+select * from t2 order by op, a, b;
+delete from t2;
+# Check for replace
+replace into t1 (id, a) values (4, 1), (3, 1);
+select * from t1 order by id;
+select * from t2 order by op, a, b;
+delete from t1;
+delete from t2;
+# Check for replace ... select ...
+insert into t1 values (3, 1, 1.05), (4, 1, 2.05);
+replace into t1 (id, a) (select i, 2 from t3);
+select * from t1 order by id;
+select * from t2 order by op, a, b;
+delete from t1;
+delete from t2;
+# Check for load data replace
+insert into t1 values (3, 1, 1.05), (5, 2, 2.05);
+load data infile '../std_data_ln/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (id, a);
+select * from t1 order by id;
+select * from t2 order by op, a, b;
+
+drop tables t1, t2, t3;
+
+--echo End of 5.0 tests