Improve update handler (long unique keys on blobs)

MDEV-21606 Improve update handler (long unique keys on blobs)
MDEV-21470 MyISAM and Aria start_bulk_insert doesn't work with long unique
MDEV-21606 Bug fix for previous version of this code
MDEV-21819 2 Assertion `inited == NONE || update_handler != this'

- Move update_handler from TABLE to handler
- Move out initialization of update handler from ha_write_row() to
  prepare_for_insert()
- Fixed that INSERT DELAYED works with update handler
- Give an error if using long unique with an autoincrement column
- Added handler function to check if table has long unique hash indexes
- Disable write cache in MyISAM and Aria when using update_handler as
  if cache is used, the row will not be inserted until end of statement
  and update_handler would not find conflicting rows.
- Removed not used handler argument from
  check_duplicate_long_entries_update()
- Syntax cleanups
  - Indentation fixes
  - Don't use single character indentifiers for arguments

4 files changed, 94 insertions, 0 deletions

diff --git a/mysql-test/main/long_unique.result b/mysql-test/main/long_unique.result
index a4955b3e7b5..aa9c115e02c 100644
--- a/mysql-test/main/long_unique.result
+++ b/mysql-test/main/long_unique.result
@@ -1477,4 +1477,28 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT t2.b FROM t1 JOIN t2 ON t1.d = t2.f WHERE t2.pk >= 20;
 b
 drop table t1,t2;
+#
+# MDEV-21470 MyISAM start_bulk_insert doesn't work with long unique
+#
+CREATE TABLE t1 (a INT, b BLOB) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+CREATE TABLE t2 (c BIT, d BLOB, UNIQUE(d)) ENGINE=MyISAM;
+INSERT INTO t2 SELECT * FROM t1;
+Warnings:
+Warning	1264	Out of range value for column 'c' at row 2
+DROP TABLE t1, t2;
+#
+# MDEV-19338 Using AUTO_INCREMENT with long unique
+#
+CREATE TABLE t1 (pk INT, a TEXT NOT NULL DEFAULT '', PRIMARY KEY (pk), b INT AUTO_INCREMENT, UNIQUE(b), UNIQUE (a,b)) ENGINE=myisam;
+ERROR HY000: AUTO_INCREMENT column `b` cannot be used in the UNIQUE index `a`
+#
+# MDEV-21819 Assertion `inited == NONE || update_handler != this'
+# failed in handler::ha_write_row
+#
+CREATE OR REPLACE TABLE t1 (a INT, b BLOB, s DATE, e DATE, PERIOD FOR app(s,e), UNIQUE(b)) ENGINE=MyISAM PARTITION BY HASH(a) PARTITIONS 2;
+INSERT INTO t1 VALUES (1,'foo','2022-01-01', '2025-01-01');
+DELETE FROM t1 FOR PORTION OF app FROM '2023-01-01' TO '2024-01-01';
+ERROR 23000: Duplicate entry 'foo' for key 'b'
+DROP TABLE t1;
 set @@GLOBAL.max_allowed_packet= @allowed_packet;
diff --git a/mysql-test/main/long_unique.test b/mysql-test/main/long_unique.test
index c0bd77ca5c9..b336b2d0b09 100644
--- a/mysql-test/main/long_unique.test
+++ b/mysql-test/main/long_unique.test
@@ -556,4 +556,33 @@ SELECT t2.b FROM t1 JOIN t2 ON t1.d = t2.f WHERE t2.pk >= 20;
 SELECT t2.b FROM t1 JOIN t2 ON t1.d = t2.f WHERE t2.pk >= 20;
 drop table t1,t2;
 
+--echo #
+--echo # MDEV-21470 MyISAM start_bulk_insert doesn't work with long unique
+--echo #
+
+CREATE TABLE t1 (a INT, b BLOB) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+CREATE TABLE t2 (c BIT, d BLOB, UNIQUE(d)) ENGINE=MyISAM;
+INSERT INTO t2 SELECT * FROM t1;
+DROP TABLE t1, t2;
+
+--echo #
+--echo # MDEV-19338 Using AUTO_INCREMENT with long unique
+--echo #
+
+--error ER_NO_AUTOINCREMENT_WITH_UNIQUE
+CREATE TABLE t1 (pk INT, a TEXT NOT NULL DEFAULT '', PRIMARY KEY (pk), b INT AUTO_INCREMENT, UNIQUE(b), UNIQUE (a,b)) ENGINE=myisam;
+
+--echo #
+--echo # MDEV-21819 Assertion `inited == NONE || update_handler != this'
+--echo # failed in handler::ha_write_row
+--echo #
+
+CREATE OR REPLACE TABLE t1 (a INT, b BLOB, s DATE, e DATE, PERIOD FOR app(s,e), UNIQUE(b)) ENGINE=MyISAM PARTITION BY HASH(a) PARTITIONS 2;
+INSERT INTO t1 VALUES (1,'foo','2022-01-01', '2025-01-01');
+--error ER_DUP_ENTRY
+DELETE FROM t1 FOR PORTION OF app FROM '2023-01-01' TO '2024-01-01';
+DROP TABLE t1;
+
+# Cleanup
 set @@GLOBAL.max_allowed_packet= @allowed_packet;
diff --git a/mysql-test/main/long_unique_delayed.result b/mysql-test/main/long_unique_delayed.result
new file mode 100644
index 00000000000..52365249097
--- /dev/null
+++ b/mysql-test/main/long_unique_delayed.result
@@ -0,0 +1,19 @@
+#
+# Test insert delayed with long unique keys
+#
+create table t1(a blob unique) engine=myisam;
+insert delayed into t1 values(1),(2),(3),(56),('sachin'),('maria'),(123456789034567891),(null),(null),(123456789034567890),('maria');
+insert delayed into t1 values(1),(9);
+flush tables t1;
+select count(*) from t1;
+count(*)
+11
+drop table t1;
+#
+# MDEV-19848
+# Server crashes in check_vcol_forward_refs upon INSERT DELAYED into
+# table with long blob key
+#
+CREATE  TABLE t1 (a BLOB, UNIQUE(a)) ENGINE=MyISAM;
+INSERT DELAYED t1 () VALUES ();
+DROP TABLE t1;
diff --git a/mysql-test/main/long_unique_delayed.test b/mysql-test/main/long_unique_delayed.test
new file mode 100644
index 00000000000..c0de4a74913
--- /dev/null
+++ b/mysql-test/main/long_unique_delayed.test
@@ -0,0 +1,22 @@
+--source include/not_embedded.inc
+
+--echo #
+--echo # Test insert delayed with long unique keys
+--echo #
+
+create table t1(a blob unique) engine=myisam;
+insert delayed into t1 values(1),(2),(3),(56),('sachin'),('maria'),(123456789034567891),(null),(null),(123456789034567890),('maria');
+insert delayed into t1 values(1),(9);
+flush tables t1;
+select count(*) from t1;
+drop table t1;
+
+--echo #
+--echo # MDEV-19848
+--echo # Server crashes in check_vcol_forward_refs upon INSERT DELAYED into
+--echo # table with long blob key
+--echo #
+
+CREATE  TABLE t1 (a BLOB, UNIQUE(a)) ENGINE=MyISAM;
+INSERT DELAYED t1 () VALUES ();
+DROP TABLE t1;