incremental: prepared statement arg count for update is changing

Test case

drop table if exists a.a488;
create table a.a488 (id int, val int);
insert into a.a488 values (1, null);
insert into a.a488 values (2, 2);

-- do full copy.
-- run following incr

update a.a488 set id=11 where id = 1; -- ok
update a.a488 set id=12 where id = 2; -- cause job dead

-- error: sql: expected 3 arguments, got 4

Reason: the first update generate a PS

update `a`.`a488`
set `id`=?, `val`=?
where ((`id` = ?) and (`val` is NULL))
limit 1

which is not suitable for the second update.

The second update requires

update `a`.`a488`
set `id`=?, `val`=?
where ((`id` = ?) and (`val` = ?))
limit 1

The same thing applies to a delete.

Note that it only happens on no-PK tables. Comparison/args for PK table has special logic.

	if len(uniqueKeyComparisons) > 0 {
		comparisons = uniqueKeyComparisons
	}
	if len(uniqueKeyArgs) > 0 {
		columnArgs = uniqueKeyArgs
	}

Plan 1

• use placeholder on all field

prepare ps1 from 'select 1 is ?' gives error -- cannot the placeholder for null.

update1: NG: the form of PS is changing anyway.

Plan 2

• do not use PS on update and delete

2

Consider the code of BuildDMLDeleteQuery/BuildDMLUpdateQuery

Q1 Is the form of u/d query unique when there is PK?

• PK cannot be null.
• Column type does not change.
• Conclusion: the form is unique.

Q2 Do uniqueKeyComparisons and uniqueKeyArgs appear together?

• For a binary PRI column, uniqueKeyComparisons is appended, without uniqueKeyArgs.
  • arg is formatted in the comparison
• Q2.2 Is it causing inconsistency?

A2.2 Yes. Case

create table a.a488b (
 id binary(8) primary key,
 val int
);
insert into a.a488b values (1,1);
select * from a.a488b;
-- create a full+incr job
-- wait full complete
-- execute following in incr stage
delete from a.a488b where id = cast(1 as binary(8));

Then job dead error: sql: expected 0 arguments, got 1. This is different from main case. The ERR happens on the first execution of the stmt.

[yaqigithub]

solve in Dtle 9.9.9.9 (git: master 933b599)