handle insert sequence table backed column with value consistently with mysql's AUTO_INCREMENT

[inexplicable]

Please search the existing issues for relevant feature requests, and use the reaction feature to add upvotes to pre-existing requests.

Feature Description

When we have a table using vitess sequence table. (using seq_test.go for example)

create table sequence_test(
		id bigint,
		val varchar(16),
		primary key(id)
	)Engine=InnoDB;

	create table sequence_test_seq (
		id int default 0, 
		next_id bigint default null, 
		cache bigint default null, 
		primary key(id)
	) comment 'vitess_sequence' Engine=InnoDB;

it's clearly stated in the test case that if we have inserted a row with id explicitly fed with value, the next insert w/o value (null/0) will fail due to the backing sequence table not updated in the first insertion.

exec(t, conn, "insert into sequence_test(id, val) values(6, 'f')")
	qr = exec(t, conn, "select * from sequence_test")
	if got, want := fmt.Sprintf("%v", qr.Rows), `[[INT64(1) VARCHAR("a")] [INT64(2) VARCHAR("b")] [INT64(3) VARCHAR("c")] [INT64(4) VARCHAR("d")] [INT64(6) VARCHAR("f")]]`; got != want {
		t.Errorf("select:\n%v want\n%v", got, want)
	}

	//Next insert will fail as we have corrupted the sequence
	exec(t, conn, "begin")
	_, err = conn.ExecuteFetch("insert into sequence_test(val) values('g')", 1000, false)
	exec(t, conn, "rollback")
	want := "Duplicate entry"

this is different from the behavior of mysql's autoinc:
https://dev.mysql.com/doc/mysql-tutorial-excerpt/5.7/en/example-auto-increment.html

INSERT INTO animals (id,name) VALUES(100,'rabbit');
INSERT INTO animals (id,name) VALUES(NULL,'mouse');
SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
+-----+-----------+

Use Case(s)

We're encountered with this as we're about to move a table from an unsharded keyspace to a sharded keyspace directly (we won't be creating a new unsharded keyspace for MoveTables as it should be with the other tables that's already in the existent sharded keyspace, but the reason why this table sticks in the unsharded one was a historical issue by itself)

As now we need a sequence table to provide auto-inc, we found that MoveTables could work, but it won't automatically lift the next_id value in the sequence table. that's inconsistent behavior with the mysql auto-inc.

Any relevant use-cases that you see.
i guess it might be a blocker for: #7305 as well.

[harshit-gangal]

support added via #13656