DROP TABLE IF EXISTS t1; create table t1 (a int not null primary key, b int not null, c int, unique index_b (b) using hash) engine ndb; insert into t1 values (1,10,1),(2,9,1),(3,8,1),(4,7,1),(5,6,1),(6,5,2),(7,4,2),(8,3,2), (9,2,2),(10,1,2); affected rows: 10 info: Records: 10 Duplicates: 0 Warnings: 0 # expected result 2 roundtrips # 0 - info call # 1 - read the row # 0 - update the row (deferred to commit) # 1 - update + commit the transaction update t1 set c = 111, b = 20 where a = 1; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 2 affected rows: 1 select * from t1 where a = 1 order by a; a b c 1 20 111 affected rows: 1 # expected result 1 roundtrips # 0 - info call # 0 - read the rows (1 with read before) # 0 - delete the row (deferred to commit) # 1 - delete + commit the transaction delete from t1 where a = 1; affected rows: 1 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 1 affected rows: 1 select * from t1 where a = 1 order by a; a b c affected rows: 0 # expected result 1 roundtrips # 0 - info call # 0 - read the rows # 0 - update the row (deferred to commit) # 1 - update + commit the transaction update t1 set c = 1111 where b = 2; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 1 affected rows: 1 select * from t1 where b = 2 order by a; a b c 9 2 1111 affected rows: 1 # expected result 2 roundtrips # 0 - info call # 1 - read the rows # 0 - update the row (deferred to commit) # 1 - update + commit the transaction update t1 set c = 12, b = 19 where b = 2; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 2 affected rows: 1 select * from t1 where b = 2 order by a; a b c affected rows: 0 # expected result 1 roundtrips # 0 - info call # 0 - read the rows (1 with read before) # 0 - delete the row (deferred to commit) # 1 - delete and commit the transaction delete from t1 where b = 19; affected rows: 1 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 1 affected rows: 1 select * from t1 where b = 19 order by a; a b c affected rows: 0 # expected result 3 roundtrips # 0 - info call # 2 - read the rows # 0 - update the rows (deferred to commit) # 1 - update + commit the transaction update t1 set c = 22 where a = 10 or a >= 10; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 3 affected rows: 1 select * from t1 order by a; a b c 2 9 1 3 8 1 4 7 1 5 6 1 6 5 2 7 4 2 8 3 2 10 1 22 affected rows: 8 # expected result 1 roundtrips # 0 - info call # 0 - read the rows (1 with read before) # 0 - update the rows (2 if no bulk update + 1 deferred to commit) # 1 - update + commit the transaction update t1 set c = 23 where a in (8,10); affected rows: 2 info: Rows matched: 2 Changed: 2 Warnings: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 1 affected rows: 1 select * from t1 order by a; a b c 2 9 1 3 8 1 4 7 1 5 6 1 6 5 2 7 4 2 8 3 23 10 1 23 affected rows: 8 # expected result 4 roundtrips # 0 - info call # 3 - read the rows # 0 - update the rows (executed during read) # 1 - commit the transaction update t1 set c = 23 where a in (7,8) or a >= 10; affected rows: 3 info: Rows matched: 3 Changed: 3 Warnings: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 4 affected rows: 1 select * from t1 order by a; a b c 2 9 1 3 8 1 4 7 1 5 6 1 6 5 2 7 4 23 8 3 23 10 1 23 affected rows: 8 # expected result 5 roundtrips # 0 - info call # 2 - read the rows # 2 - pk read in rnd_pos # 0 - update the rows (deferred to commit) # 1 - update + commit the transaction update t1 set c = 11 where a = 3 or b = 7; affected rows: 2 info: Rows matched: 2 Changed: 2 Warnings: 0 select * from t1 where a = 3 or b = 7 order by a; a b c 3 8 11 4 7 11 affected rows: 2 # expected result 5 roundtrips # 0 - info call # 1 - read the rows # 1 - read the row _again_ in complemented read # 1 - delete the row (pk update) # 1 - insert the row (pk update) # 1 - commit the transaction update t1 set a = 13, b = 20 where a = 3; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 5 affected rows: 1 select * from t1 where a = 13 order by a; a b c 13 20 11 affected rows: 1 # expected result 5 roundtrips # 0 - info call # 1 - read the rows # 1 - read the row _again_ in complemented read # 1 - delete the row (pk update) # 1 - insert the row (pk update) # 1 - commit the transaction update t1 set a = 12, b = 19 where b = 7; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 5 affected rows: 1 select * from t1 where b = 19 order by a; a b c 12 19 11 affected rows: 1 select * from t1 where b = 7 order by a; a b c affected rows: 0 # expected result 3 roundtrips # 1 - info call # 1 - read the rows # 0 - update the rows (deferred to commit) # 1 - update + commit the transaction update t1 set c = 12, b = 29 where a = 5 and b = 6; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 2 affected rows: 1 select * from t1 where b = 19 order by a; a b c 12 19 11 affected rows: 1 # expected result 2 roundtrips # 0 - info call # 1 - read the rows # 0 - delete the rows (no row found) # 1 - commit the transaction delete from t1 where b = 6 and c = 12; affected rows: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 2 affected rows: 1 select * from t1 where b = 6 order by a; a b c affected rows: 0 drop table t1; affected rows: 0 create table t1 (a int primary key, b varchar(4)) engine=ndb; affected rows: 0 insert into t1 values (1, '1'), (2, '2'), (3, '3'); affected rows: 3 info: Records: 3 Duplicates: 0 Warnings: 0 # expected result 1 roundtrip # 0 - info call # 0 - read the row # 0 - update the row # 1 - update+commit the row # Rows matched=changed=affected=1 update t1 set b='two' where a=2; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 1 affected rows: 1 # expected result 1 roundtrip # 0 - info call # 0 - read the row # 0 - update the row # 1 - update+commit the row # Rows matched=changed=affected=0 update t1 set b='lots' where a=2000; affected rows: 0 info: Rows matched: 0 Changed: 0 Warnings: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 1 affected rows: 1 # expected result 1 roundtrip # 0 - info call # 0 - read the row # 0 - update the row # 1 - update+commit the row # Rows matched=changed=affected=1 # 1 warning update t1 set b='one plus one' where a=2; Warnings: Warning 1265 Data truncated for column 'b' at row 1 affected rows: 1 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 1 affected rows: 1 # expected result 1 roundtrip # 0 - info call # 0 - read the row # 0 - update the row # 1 - update+commit the row # Rows matched=changed=affected=0 # 1 warning update t1 set b='two thousand' where a=2000; affected rows: 0 info: Rows matched: 0 Changed: 0 Warnings: 1 show warnings; Level Code Message Warning 1265 Data truncated for column 'b' at row 1 affected rows: 1 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 1 affected rows: 1 # expected result 2 roundtrips # 0 - info call # 0 - read the row # 1 - update the row # 1 - commit # Rows matched=changed=affected=1 begin; affected rows: 0 update t1 set b='two' where a=2; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 commit; affected rows: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 2 affected rows: 1 # expected result 2 roundtrips # 0 - info call # 0 - read the row # 1 - update the row # 1 - commit # Rows matched=changed=affected=0 begin; affected rows: 0 update t1 set b='lots' where a=2000; affected rows: 0 info: Rows matched: 0 Changed: 0 Warnings: 0 commit; affected rows: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 2 affected rows: 1 # expected result 2 roundtrips # 0 - info call # 0 - read the row # 1 - update the row # 1 - commit # Rows matched=changed=affected=1 1 warning begin; affected rows: 0 update t1 set b='one plus one' where a=2; Warnings: Warning 1265 Data truncated for column 'b' at row 1 affected rows: 1 commit; affected rows: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 2 affected rows: 1 # expected result 2 roundtrips # 0 - info call # 0 - read the row # 1 - update the row # 1 - commit # Rows matched=changed=affected=0 # 1 warning begin; affected rows: 0 update t1 set b='two thousand' where a=2000; affected rows: 0 info: Rows matched: 0 Changed: 0 Warnings: 1 commit; affected rows: 0 show warnings; Level Code Message Warning 1265 Data truncated for column 'b' at row 1 affected rows: 1 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 2 affected rows: 1 drop table t1; affected rows: 0 create table t1 (a int not null primary key auto_increment, b int, c varchar(256)) engine = ndb; affected rows: 0 insert into t1(b,c) values (1,'this is a test'), (2,'this is a test'),(3,'this is a test'), (4,'this is a test'),(5,'this is a test'),(6,'this is a test'), (7,'this is a test'),(8,'this is a test'),(9,'this is a test'); affected rows: 9 info: Records: 9 Duplicates: 0 Warnings: 0 insert into t1(b,c) select b,c from t1; affected rows: 9 info: Records: 9 Duplicates: 0 Warnings: 0 insert into t1(b,c) select b,c from t1; affected rows: 18 info: Records: 18 Duplicates: 0 Warnings: 0 insert into t1(b,c) select b,c from t1; affected rows: 36 info: Records: 36 Duplicates: 0 Warnings: 0 insert into t1(b,c) select b,c from t1; affected rows: 72 info: Records: 72 Duplicates: 0 Warnings: 0 insert into t1(b,c) select b,c from t1; affected rows: 144 info: Records: 144 Duplicates: 0 Warnings: 0 insert into t1(b,c) select b,c from t1; affected rows: 288 info: Records: 288 Duplicates: 0 Warnings: 0 insert into t1(b,c) select b,c from t1; affected rows: 576 info: Records: 576 Duplicates: 0 Warnings: 0 insert into t1(b,c) select b,c from t1; affected rows: 1152 info: Records: 1152 Duplicates: 0 Warnings: 0 insert into t1(b,c) select b,c from t1; affected rows: 2304 info: Records: 2304 Duplicates: 0 Warnings: 0 analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK affected rows: 1 select count(*) from t1 where a in (...) count(*) 1000 affected rows: 1 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 5 affected rows: 1 update t1 set c = 'kalle' where a in (...) affected rows: 1000 info: Rows matched: 1000 Changed: 1000 Warnings: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 9 affected rows: 1 delete from t1 where a in (...) affected rows: 1000 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 3 affected rows: 1 delete from t1 where a in (...) affected rows: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 3 affected rows: 1 update t1 set c = 'kalle' where a in (...) affected rows: 0 info: Rows matched: 0 Changed: 0 Warnings: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 9 affected rows: 1 update t1 set c = 'kalle' where a in (...) affected rows: 0 info: Rows matched: 0 Changed: 0 Warnings: 0 @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count 9 affected rows: 1 drop table t1; affected rows: 0