--source include/have_debug.inc
--source ../include/have_xengine.inc

--echo ### test creating index with data contains null field

set @save_sql_mode=@@sql_mode;

--echo ==========================================================
--echo # case 1 with hidden primary key
--echo ==========================================================
--echo # case 1.1 on INT column
--echo ==========================================================
CREATE TABLE t1(id INT, a INT);
INSERT INTO t1 VALUES(1, 1), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1846
ALTER TABLE t1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1062
ALTER TABLE t1 ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT, a INT);
INSERT INTO t1 VALUES(1, 1), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT, a INT);
INSERT INTO t1 VALUES(1, 1), (2, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

--echo ==========================================================
--echo # case 1.2 on CHAR COLUMN
--echo ==========================================================
CREATE TABLE t1(id INT, a CHAR(10));
INSERT INTO t1 VALUES(1, 'a'), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1846
ALTER TABLE t1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1062
ALTER TABLE t1 ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT, a CHAR(10));
INSERT INTO t1 VALUES(1, 'a'), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT, a CHAR(10));
INSERT INTO t1 VALUES(1, 'a'), (2, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

--echo ==========================================================
--echo # case 1.3 on VARCHAR COLUMN
--echo ==========================================================
CREATE TABLE t1(id INT, a VARCHAR(60));
INSERT INTO t1 VALUES(1, 'a'), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1846
ALTER TABLE t1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1062
ALTER TABLE t1 ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT, a VARCHAR(60));
INSERT INTO t1 VALUES(1, 'a'), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT, a VARCHAR(60));
INSERT INTO t1 VALUES(1, 'a'), (2, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

--echo ==========================================================
--echo # case 1.4 on TEXT COLUMN
--echo ==========================================================
CREATE TABLE t1(id INT, a TEXT);
INSERT INTO t1 VALUES(1, 'a'), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a(2)), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a(4)), ALGORITHM=INPLACE;
--error 1846
ALTER TABLE t1 ADD PRIMARY KEY(a(10)), ALGORITHM=INPLACE;
--error 1062
ALTER TABLE t1 ADD PRIMARY KEY(a(10));

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT, a TEXT);
INSERT INTO t1 VALUES(1, 'a'), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a(2)), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a(4)), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a(10)), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a(10));

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT, a TEXT);
INSERT INTO t1 VALUES(1, 'a'), (2, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a(2)), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a(4)), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a(10)), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 ADD PRIMARY KEY(a(10));

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

--echo ==========================================================
--echo # case 2 with primary key
--echo ==========================================================
--echo # case 2.1 on INT column
--echo ==========================================================
CREATE TABLE t1(id INT PRIMARY KEY, a INT);
INSERT INTO t1 VALUES(1, 1), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1846
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1062
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT PRIMARY KEY, a INT);
INSERT INTO t1 VALUES(1, 1), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT PRIMARY KEY, a INT);
INSERT INTO t1 VALUES(1, 1), (2, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

--echo ==========================================================
--echo # case 2.2 on CHAR column
--echo ==========================================================
CREATE TABLE t1(id INT PRIMARY KEY, a CHAR(10));
INSERT INTO t1 VALUES(1, 'a'), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1846
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1062
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT PRIMARY KEY, a CHAR(10));
INSERT INTO t1 VALUES(1, 'a'), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT PRIMARY KEY, a CHAR(10));
INSERT INTO t1 VALUES(1, 'a'), (2, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

--echo ==========================================================
--echo # case 2.3 on VARCHAR column
--echo ==========================================================
CREATE TABLE t1(id INT PRIMARY KEY, a VARCHAR(60));
INSERT INTO t1 VALUES(1, 'a'), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1846
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1062
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT PRIMARY KEY, a VARCHAR(60));
INSERT INTO t1 VALUES(1, 'a'), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT PRIMARY KEY, a VARCHAR(60));
INSERT INTO t1 VALUES(1, 'a'), (2, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a);

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

--echo ==========================================================
--echo # case 2.4 on TEXT column
--echo ==========================================================
CREATE TABLE t1(id INT PRIMARY KEY, a TEXT);
INSERT INTO t1 VALUES(1, 'a'), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a(2)), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a(4)), ALGORITHM=INPLACE;
--error 1846
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a(10)), ALGORITHM=INPLACE;
--error 1062
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a(10));

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT PRIMARY KEY, a TEXT);
INSERT INTO t1 VALUES(1, 'a'), (2, NULL), (3, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a(2)), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a(4)), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a(10)), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a(10));

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

set sql_mode="STRICT_ALL_TABLES";
CREATE TABLE t1(id INT PRIMARY KEY, a TEXT);
INSERT INTO t1 VALUES(1, 'a'), (2, NULL);
SELECT * FROM t1;

ALTER TABLE t1 ADD KEY(a(2)), ALGORITHM=INPLACE;
ALTER TABLE t1 ADD UNIQUE KEY(a(4)), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a(10)), ALGORITHM=INPLACE;
--error 1138
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a(10));

SHOW CREATE TABLE t1;
CHECK TABLE t1;
DROP TABLE t1;
set sql_mode = @save_sql_mode;

--source suite/xengine/include/check_xengine_log_error.inc