Change List backing store code for indexed list to shift in bulk (single statement)

[andyjefferson]

Backport from #413 on master branch.

With an indexed list we have an index column. Consequently whenever adding / removing from an indexed list at a position we need to shift all existing elements AFTER that position down to create space.

If we have a list of 10 elements using an index, and we do a List.remove(3) then we get

UPDATE B SET A_ID_OID=NULL, ORDERIDX=-1 WHERE A_ID_OID=1 AND ORDERIDX=3
UPDATE B SET ORDERIDX = ORDERIDX + -1 WHERE A_ID_OID=1 AND ORDERIDX=4
UPDATE B SET ORDERIDX = ORDERIDX + -1 WHERE A_ID_OID=1 AND ORDERIDX=5
UPDATE B SET ORDERIDX = ORDERIDX + -1 WHERE A_ID_OID=1 AND ORDERIDX=6
UPDATE B SET ORDERIDX = ORDERIDX + -1 WHERE A_ID_OID=1 AND ORDERIDX=7
UPDATE B SET ORDERIDX = ORDERIDX + -1 WHERE A_ID_OID=1 AND ORDERIDX=8
UPDATE B SET ORDERIDX = ORDERIDX + -1 WHERE A_ID_OID=1 AND ORDERIDX=9

whereas we ought to do

UPDATE B SET A_ID_OID=NULL, ORDERIDX=-1 WHERE A_ID_OID=1 AND ORDERIDX=3
UPDATE B SET ORDERIDX = ORDERIDX + -1 WHERE A_ID_OID=1 AND ORDERIDX>3

[chrisco484]

It looks like I've found a bug related to this optimization - testcase is available in issue #464