--echo #
--echo # MDEV-30034 UNIQUE USING HASH accepts duplicate entries for tricky collations
--echo #

# TEXT

if (`SELECT UPPER(@@storage_engine) != 'MEMORY'`)
{
EXECUTE IMMEDIATE REPLACE(
  'CREATE TABLE t1 ( '
  ' a TEXT COLLATE <COLLATION>,'
  'UNIQUE(a(3)))',
  '<COLLATION>', @@collation_connection);
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES ('ss ');
--error ER_DUP_ENTRY
INSERT INTO t1 VALUES (_utf8mb3 0xC39F20)/*SZ+SPACE*/;
DROP TABLE t1;


EXECUTE IMMEDIATE REPLACE(
  'CREATE TABLE t1 ( '
  ' a TEXT COLLATE <COLLATION>,'
  'UNIQUE(a(3)) USING HASH)',
  '<COLLATION>', @@collation_connection);
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES ('ss ');
--error ER_DUP_ENTRY
INSERT INTO t1 VALUES (_utf8mb3 0xC39F20)/*SZ+SPACE*/;
DROP TABLE t1;
}


# VARCHAR

EXECUTE IMMEDIATE REPLACE(
  'CREATE TABLE t1 ( '
  ' a VARCHAR(2000) COLLATE <COLLATION>,'
  'UNIQUE(a(3)))',
  '<COLLATION>', @@collation_connection);
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES ('ss ');
--error ER_DUP_ENTRY
INSERT INTO t1 VALUES (_utf8mb3 0xC39F20)/*SZ+SPACE*/;
DROP TABLE t1;


EXECUTE IMMEDIATE REPLACE(
  'CREATE TABLE t1 ( '
  ' a VARCHAR(2000) COLLATE <COLLATION>,'
  'UNIQUE(a(3)) USING HASH)',
  '<COLLATION>', @@collation_connection);
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES ('ss ');
--error ER_DUP_ENTRY
INSERT INTO t1 VALUES (_utf8mb3 0xC39F20)/*SZ+SPACE*/;
DROP TABLE t1;

# CHAR

EXECUTE IMMEDIATE REPLACE(
  'CREATE TABLE t1 ( '
  ' a CHAR(20) COLLATE <COLLATION>,'
  'UNIQUE(a(3)))',
  '<COLLATION>', @@collation_connection);
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES ('ss ');
INSERT INTO t1 VALUES (_utf8mb3 0xC39F20)/*SZ+SPACE*/;
DROP TABLE t1;

EXECUTE IMMEDIATE REPLACE(
  'CREATE TABLE t1 ( '
  ' a CHAR(20) COLLATE <COLLATION>,'
  'UNIQUE(a(3)) USING HASH)',
  '<COLLATION>', @@collation_connection);
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES ('ss ');
INSERT INTO t1 VALUES (_utf8mb3 0xC39F20)/*SZ+SPACE*/;
DROP TABLE t1;