--echo #
--echo # Test that INFORMATION_SCHEMA.INNODB_COLUMNS shows all the columns
--echo # in a non-partitioned table
--echo #

CREATE TABLE t_nopart (a int PRIMARY KEY, b int, c varchar(64));

SELECT lower(t.name), lower(c.name)
  FROM INFORMATION_SCHEMA.INNODB_COLUMNS c
  JOIN INFORMATION_SCHEMA.INNODB_TABLES t
  WHERE t.table_id = c.table_id and t.name like '%t_nopart%'
  ORDER BY t.name, c.name;

DROP TABLE t_nopart;

--echo #
--echo # Test that INFORMATION_SCHEMA.INNODB_COLUMNS shows columns for
--echo # all the partitions in a partitioned table
--echo #

CREATE TABLE t_part
(a int,
 b int,
 c varchar(64),
 PRIMARY KEY (a),
 KEY (b),
 KEY (c,b))
ENGINE = InnoDB
PARTITION BY HASH (a) PARTITIONS 5;

SELECT lower(t.name), lower(c.name)
  FROM INFORMATION_SCHEMA.INNODB_COLUMNS c
  JOIN INFORMATION_SCHEMA.INNODB_TABLES t
  WHERE t.table_id = c.table_id and t.name like '%t_part%'
  ORDER BY t.name, c.name;

DROP TABLE t_part;

--echo #
--echo # Test that INFORMATION_SCHEMA.INNODB_COLUMNS shows columns for all
--echo # the subpartitions of a table
--echo #

CREATE TABLE t_subpart (a INT, b INT)
  PARTITION BY RANGE(b)
    SUBPARTITION BY HASH(b)
    SUBPARTITIONS 2 (
      PARTITION p0 VALUES LESS THAN (1000),
      PARTITION p1 VALUES LESS THAN (2000),
      PARTITION p2 VALUES LESS THAN MAXVALUE
);

SELECT lower(t.name), lower(c.name)
  FROM INFORMATION_SCHEMA.INNODB_COLUMNS c
  JOIN INFORMATION_SCHEMA.INNODB_TABLES t
  WHERE t.table_id = c.table_id and t.name like '%t_subpart%'
  ORDER BY t.name, c.name;

DROP TABLE t_subpart;

--echo #
--echo # Test of optimizer estimates
--echo #
CREATE TABLE t0 (a int PRIMARY KEY, b int, c varchar(64));
CREATE TABLE t1
(a int,
 b int,
 c varchar(64),
 PRIMARY KEY (a),
 KEY (b),
 KEY (c,b))
ENGINE = InnoDB
PARTITION BY HASH (a) PARTITIONS 5;

--echo # Set up the rows so that the following query would match
--echo # 100 PK values but only 10 'b' values. But 90 of the matching
--echo # PKs will be in the smallest partition.
--echo # Since the fix for bug#1364811 will ha_partition only check the biggest
--echo # partitions, but ha_innopart will check all partitions when estimating
--echo # numbers of records in range. Resulting in ha_partition will
--echo # assume there are only 10 matching PK values and choose to do search
--echo # by PK but ha_innopart will search by secondary index 'b' since it
--echo # will prefer to read 10 records from the secondary index over reading
--echo # 100 records from the PRIMARY index.
--echo # SELECT * FROM t1 WHERE a BETWEEN 1 AND 450 AND b BETWEEN 1 AND 10;


--echo # Using t0 to prevent any issues with purge in t1.
INSERT INTO t0 VALUES (1,0,"common"),(2,0,"common"),(3,0,"common"),
(4,0,"common"),(5,0,"common"), (6,0,"common"),(7,0,"common"),(8,0,"common"),
(9,0,"common"),(10,0,"common");
INSERT INTO t0 SELECT a + 10, b, c FROM t0;
INSERT INTO t0 SELECT a + 20, b, c FROM t0;
INSERT INTO t0 SELECT a + 40, b, c FROM t0;
INSERT INTO t0 SELECT a + 80, b, c FROM t0;
INSERT INTO t0 SELECT a + 160, b, c FROM t0;
INSERT INTO t0 SELECT a + 320, b, c FROM t0;
INSERT INTO t0 SELECT a + 640, b, c FROM t0;
DELETE FROM t0 WHERE a BETWEEN 13 AND 450 AND (a % 5) <> 0;
DELETE FROM t0 WHERE a > 450 AND (a % 5) = 0 LIMIT 250;

# Uncomment these to see the resulting distribution...
#SELECT COUNT(*) FROM t0 WHERE a BETWEEN 1 AND 450;
#SELECT COUNT(*) FROM t0 WHERE a BETWEEN 1 AND 450 AND (a % 5) = 0;
#SELECT (a % 5) as part, COUNT(*) FROM t0 GROUP BY part;

UPDATE t0 SET b = a, c = "MATCH!" WHERE a <= 10;
#SELECT COUNT(*) FROM t0 WHERE b BETWEEN 1 AND 10;
#SELECT (a % 5) as part, COUNT(*) FROM t0 WHERE b BETWEEN 1 AND 10 GROUP BY part;
INSERT INTO t1 SELECT * FROM t0;
DROP TABLE t0;

ANALYZE TABLE t1;
EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 450 AND b BETWEEN 1 AND 10;

--disable_warnings
# Access session_status, so that the session_status metadata is accessed and
# kept in DD cache. Else the HANDLER_READ_KEY would increment more than
# required.
SELECT * FROM performance_schema.session_status
WHERE VARIABLE_NAME LIKE 'dummy';
--enable_warnings

FLUSH STATUS;
--sorted_result
SELECT * FROM t1 WHERE a BETWEEN 1 AND 450 AND b BETWEEN 1 AND 10;
let $get_handler_status_counts= SELECT * FROM performance_schema.session_status
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
--source include/get_handler_status_counts.inc

DROP TABLE t1;

--echo # Test of index_merge and delete
CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
)
/*!50100 PARTITION BY HASH (a)
PARTITIONS 3 */;
INSERT INTO t1 VALUES (0,0),(0,0),(1,0),(1,0),(2,0),(2,0),(0,0);
INSERT INTO t1 SELECT t1.a,t1.b FROM t1, t1 t2;
INSERT INTO t1 VALUES (1,1),(2,2);

ANALYZE TABLE t1;
--replace_column 10 #
EXPLAIN SELECT * FROM t1 WHERE a = 4 OR b > 0;
--replace_column 10 #
EXPLAIN DELETE FROM t1 WHERE a = 4 OR b > 0;
--sorted_result
SELECT * FROM t1 WHERE a = 4 OR b > 0;
SELECT COUNT(*) FROM t1;
DELETE FROM t1 WHERE a = 4 OR b > 0;
SELECT COUNT(*) FROM t1;
--sorted_result
SELECT * FROM t1 WHERE a = 4 OR b > 0;
DROP TABLE t1;

--echo # more testing of index_merge and delete
CREATE TABLE t1
(a int not null,
 b int not null,
 INDEX(a),
 INDEX(b))
engine=InnoDB
PARTITION BY KEY(a,b) PARTITIONS 5;
INSERT INTO t1 VALUES (0,0),(1,1), (2,2), (3,3), (4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
INSERT INTO t1 SELECT a + 10, b + 10 FROM t1;
INSERT INTO t1 SELECT a + 20, b + 20 FROM t1;
INSERT INTO t1 SELECT a + 40, b + 40 FROM t1;
INSERT INTO t1 SELECT a + 80, b + 80 FROM t1;
INSERT INTO t1 SELECT a + 160, b + 160 FROM t1;

ANALYZE TABLE t1;

--replace_column 10 #
EXPLAIN SELECT * FROM t1 WHERE a = 4 OR b < 3;
--sorted_result
SELECT * FROM t1 WHERE a = 4 OR b < 3;

--replace_column 10 #
EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 0 AND 20 OR b BETWEEN 10 AND 20;
--sorted_result
SELECT * FROM t1 WHERE a BETWEEN 0 AND 20 OR b BETWEEN 10 AND 20;

--replace_column 10 #
EXPLAIN DELETE FROM t1 WHERE a = 4 OR b < 3;
DELETE FROM t1 WHERE a = 4 OR b < 3;

--replace_column 10 #
EXPLAIN DELETE FROM t1 WHERE a BETWEEN 0 AND 20 OR b BETWEEN 10 AND 20;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
DELETE FROM t1 WHERE a BETWEEN 0 AND 20 OR b BETWEEN 10 AND 20;

DROP TABLE t1;

--echo #
--echo # Bug#20584754: CAN'T FIND RECORD IN 'TABLE100_KEY_PK_PARTS_2_DATETIME_3'
--echo #
let $MYSQLD_DATADIR=`SELECT @@datadir`;
CREATE TABLE `t1` (
  `c1` tinyint unsigned DEFAULT NULL,
  `c2` char(1) DEFAULT NULL,
  `pk` tinyint NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `c1` (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (pk)
PARTITIONS 2 */;

--echo # Create the dataset in a separate table, so it can be loaded
--echo # directly without update/delete which may give unstable EXPLAIN
--echo # due to background pruning.
CREATE TABLE t0
(a tinyint not null auto_increment primary key,
 b char(1) DEFAULT 'a',
 c tinyint DEFAULT 1);
INSERT INTO t0 (a) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
INSERT INTO t0 (a) SELECT NULL FROM t0, t0 t0_2 LIMIT 50;
UPDATE t0 SET c = NULL WHERE a IN (2,3,6,13,18);
INSERT INTO t1 SELECT c,b,a FROM t0;
DROP TABLE t0;

ANALYZE TABLE t1;

--replace_column 10 #
EXPLAIN SELECT
pk
FROM `t1`
WHERE `c1` IS  NULL OR `c2` = '0';

--sorted_result
SELECT
pk
FROM `t1`
WHERE `c1` IS  NULL OR `c2` = '0';

DROP TABLE t1;

--echo #
--echo # Bug#20516390: PARTITIONED TABLE, TBLSPACE MOVE, ALTER, ASSERT
--echo # !(M_FILE_PER_TABLE && M_USE_SHAR
--echo #
# CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
CREATE TABLE t1
(a int not null,
 d int not null,
 b varchar(198) not null,
 c varchar(177),
 index(d),
 index(a),
 PRIMARY KEY (b(10), a, d),
 INDEX (c(95), b(95)),
 INDEX (b(5), c(10), a))
ENGINE=InnoDB stats_persistent=DEFAULT
PARTITION BY LINEAR HASH(d) PARTITIONS 2;
# TODO : Enable following once shared tablespace are allowed in Partitioned
#	 Table (wl#12034).
# ALTER TABLE t1 TABLESPACE ts1;
ALTER TABLE t1 DROP PRIMARY KEY, ALGORITHM=DEFAULT;
DROP TABLE t1;
# DROP TABLESPACE ts1;

--echo #
--echo # Bug #20593808: CRASH WITH PARTITIONED TABLES
--echo # MULTITABLE DELETE
--echo #

--echo Test DML with index scan on partitioned table
--echo #1. test delete with join using PK index scan on partitioned table
eval CREATE TABLE t1 (
  col_1_int int,
  col_2_text text)
  ENGINE=InnoDB;

SHOW CREATE TABLE t1;

--echo #insert two records in t1
INSERT INTO t1 VALUES (5, 'row to delete');
INSERT INTO t1 VALUES (3, 'row to keep');

SELECT * FROM t1 ORDER BY col_1_int;

eval CREATE TABLE t2 (
  col_1_int int,
  col_2_int int,
  col_3_text text,
  PRIMARY KEY(col_1_int, col_3_text(100)),
  KEY idx1 (col_2_int, col_1_int))
  ENGINE=InnoDB
  PARTITION BY KEY(col_1_int) partitions 5;

SHOW CREATE TABLE t2;

--echo #insert five records in t2
INSERT INTO t2 VALUES (91, 11, 'bigger value for no-op');
INSERT INTO t2 VALUES ( 4, 25, 'smaller value to trigger delete');
INSERT INTO t2 VALUES (35, 33, 'bigger value for no-op addlen');
INSERT INTO t2 VALUES (55, 42, 'bigger value for no-op addlen + addlen + addlen');
INSERT INTO t2 VALUES (82, 54, 'bigger value for no-op addlen + addlen');

SELECT * FROM t2 ORDER BY col_1_int;

--echo # test delete with join and index scan
DELETE t1 FROM t2, t1  WHERE t2.col_1_int <= t1.col_1_int;

--echo expect one record
SELECT * FROM t1;

DROP TABLE t1;
DROP TABLE t2;

--echo #2. test delete with join using secondary index scan on partitioned table
eval CREATE TABLE t1 (
  col_1_int int,
  col_2_text text)
  ENGINE=InnoDB;

SHOW CREATE TABLE t1;

--echo #insert two records in t1
INSERT INTO t1 VALUES (5, 'row to delete');
INSERT INTO t1 VALUES (3, 'row to keep');

SELECT * FROM t1 ORDER BY col_1_int;

eval CREATE TABLE t2 (
  col_1_int int,
  col_2_int int,
  col_3_text text,
  PRIMARY KEY(col_2_int, col_1_int),
  KEY idx1(col_1_int, col_3_text(100)))
  ENGINE=InnoDB
  PARTITION BY KEY(col_2_int) partitions 5;

SHOW CREATE TABLE t2;

--echo #insert five records in t2
INSERT INTO t2 VALUES (91, 11, 'bigger value for no-op');
INSERT INTO t2 VALUES ( 4, 25, 'smaller value to trigger delete');
INSERT INTO t2 VALUES (35, 33, 'bigger value for no-op addlen');
INSERT INTO t2 VALUES (55, 42, 'bigger value for no-op addlen + addlen + addlen');
INSERT INTO t2 VALUES (82, 54, 'bigger value for no-op addlen + addlen');

SELECT * FROM t2 ORDER BY col_1_int;

--echo # test delete with join and index scan
DELETE t1 FROM t2, t1  WHERE t2.col_1_int <= t1.col_1_int;

--echo expect one record
SELECT * FROM t1;

DROP TABLE t1;
DROP TABLE t2;

--echo #
--echo # Bug #20949314: PARTITION_HELPER::PH_RND_INIT(BOOL): ASSERTION
--echo #

CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (3), (4), (5), (1), (2);

CREATE TABLE t2 PARTITION BY KEY (c1) PARTITIONS 3
AS
SELECT * FROM t1;

DELETE A FROM t1 AS A WHERE A.c1 IN
(SELECT c1 FROM t2 AS B WHERE B.c1 = A.c1);

SELECT * from t1;

DROP TABLE t1;
DROP TABLE t2;

--echo #
--echo # Bug#21211524: CRASH IN ACTUAL_KEY_PARTS WITH PARTITIONED TABLES
--echo #
CREATE TABLE t1(
  col1 SET('') CHARACTER SET UTF16 COLLATE UTF16_SPANISH_CI,
  col2 INT,
  PRIMARY KEY (col2, col1),
  UNIQUE KEY (col1, col2))
  ENGINE=INNODB
  PARTITION BY KEY (col1);

SELECT * from t1 WHERE col1 BETWEEN 0x92 AND "";

DROP TABLE t1;

--echo #
--echo # Bug#21794110: ASSERTION `MP->COUNT > 0 && MY_THREAD_EQUAL
--echo #

CREATE TABLE t1(col1 INT PRIMARY KEY, col2 CHAR(10))
ENGINE=INNODB
PARTITION BY HASH(col1) PARTITIONS 2;

INSERT INTO t1 VALUES(0, 'string1');
ALTER TABLE t1 CHANGE col1 col1 INT UNSIGNED AUTO_INCREMENT;
SHOW CREATE TABLE t1;

DROP TABLE t1;

--echo #
--echo # Bug#21620577: ASSERTION H->ACTIVE_INDEX == 64
--echo #

CREATE TABLE t1(col1 YEAR not null, col2 int, KEY(col1), KEY(col1, col2))
ENGINE=INNODB
PARTITION BY KEY(col1) PARTITIONS 2;

INSERT INTO t1 VALUES('2015', 10);

SELECT /*+ bka()*/ a.col2 from t1 AS a
RIGHT JOIN t1 AS b on a.col1<=>null
RIGHT JOIN t1 AS c on 1;

DROP TABLE t1;

--echo #
--echo # Bug#21754608: HANDLE_FATAL_SIGNAL PARTITION_HELPER::PH_READ_RANGE_FIRST
--echo #

CREATE TABLE t1(col1 int PRIMARY KEY, col2 int, col3 int)
ENGINE=INNODB
PARTITION BY LIST(col1) PARTITIONS 2
   (PARTITION p0 VALUES IN (11,12),
    PARTITION p1 VALUES IN (0,1));

INSERT INTO t1 values(1, 10, 100);

--disable_warnings
SELECT * from t1 WHERE col1 BETWEEN '2000-01-01 01:02:00' AND '2000-01-01 01:03:00';
--enable_warnings

DROP TABLE t1;

--echo #
--echo # Bug#21755994 ALTER INPLACE CRASHES ON A INNODB TABLE WITH PARTITION ON SEPARATE TABLESPACE
--echo #
--echo # Note that we do not support partitions in shared tablespaces
--echo # (at least not until WL12034).

# This test case was disabled during WL#6378 due to Bug#21914047.
# The bug has been fixed, but InnoDB now does not support partitions
# in shared tablespaces. Hence, the test case is temporarily rewritten
# and the original test case may be restored when partitinos are again
# allowed to be in shared tablespaces.
#
# TODO WL#12034: Enable the test case below.
#
#CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
#
#CREATE TABLE t1 ( a INT NOT NULL, PRIMARY KEY (a))
#ENGINE=InnoDB
#PARTITION BY RANGE (a) PARTITIONS 3
#( PARTITION P1 VALUES LESS THAN (2) TABLESPACE ts1,
#  PARTITION P2 VALUES LESS THAN (4) TABLESPACE `innodb_system`,
#  PARTITION P3 VALUES LESS THAN (6));
#
#ALTER TABLE t1 ADD COLUMN f int;
#
#DROP TABLE t1;
#DROP TABLESPACE ts1;

--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLE t1 ( a INT NOT NULL, PRIMARY KEY (a))
ENGINE=InnoDB
PARTITION BY RANGE (a) PARTITIONS 3
( PARTITION P1 VALUES LESS THAN (2),
  PARTITION P2 VALUES LESS THAN (4) TABLESPACE `innodb_system`,
  PARTITION P3 VALUES LESS THAN (6));

CREATE TABLE t1 ( a INT NOT NULL, PRIMARY KEY (a))
ENGINE=InnoDB
PARTITION BY RANGE (a) PARTITIONS 3
( PARTITION P1 VALUES LESS THAN (2),
  PARTITION P2 VALUES LESS THAN (4) TABLESPACE `innodb_file_per_table`,
  PARTITION P3 VALUES LESS THAN (6));

ALTER TABLE t1 ADD COLUMN f int;

DROP TABLE t1;
 
# TODO : Enable following once shared tablespace are allowed in Partitioned
#	 Table (wl#12034).
# --echo #
# --echo # Bug#21796845 ASSERT ERR == DB_SUCCESS, ROW_QUIESCE_TABLE_START(), FLUSH TABLES FOR EXPORT
# --echo #
# CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
#
# CREATE TABLE t1 (a INT, b INT) ENGINE = InnoDB
#      PARTITION BY RANGE(a) (
#      PARTITION p1 VALUES LESS THAN (100) TABLESPACE innodb_system,
#      PARTITION p2 VALUES LESS THAN (200),
#      PARTITION p3 VALUES LESS THAN (300) TABLESPACE ts1,
#      PARTITION p4 VALUES LESS THAN (400) TABLESPACE innodb_system);
#
# FLUSH table t1 FOR EXPORT;
# UNLOCK TABLES;
#
# DROP TABLE t1;
# DROP TABLESPACE ts1;

--echo #
--echo # Bug#21957001 INNODB: FAILING ASSERTION: 0 IN FILE HA_INNOPART.CC LINE 3526
--echo #
call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Trying to access missing tablespace .*");
call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Missing .ibd file for table `test`\.`t1` .* ");
call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Cannot calculate statistics for table .*");
call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Operating system error number 2 in a file operation.");
call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* The error means the system cannot find the path specified.");
call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Cannot open datafile for read-only:");

CREATE TABLE t1(c1 INT,c2 CHAR(1)) PARTITION BY KEY(c1) PARTITIONS 4;

ALTER TABLE t1 DISCARD PARTITION p3 TABLESPACE;
ANALYZE TABLE t1;
SELECT TABLE_ROWS FROM information_schema.tables where table_name = 't1';

ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
ANALYZE TABLE t1;
SELECT TABLE_ROWS FROM information_schema.tables where table_name = 't1';

ALTER TABLE t1 DISCARD PARTITION p2 TABLESPACE;
ANALYZE TABLE t1;
SELECT TABLE_ROWS FROM information_schema.tables where table_name = 't1';

ALTER TABLE t1 DISCARD PARTITION p0 TABLESPACE;
ANALYZE TABLE t1;
SELECT TABLE_ROWS FROM information_schema.tables where table_name = 't1';

DROP TABLE t1;

CREATE TABLE t1(c1 INT,c2 CHAR(1)) PARTITION BY KEY(c1) PARTITIONS 4;

ALTER TABLE t1 DISCARD TABLESPACE;
ANALYZE TABLE t1;
SELECT TABLE_ROWS FROM information_schema.tables where table_name = 't1';

DROP TABLE t1;

--echo #
--echo # Bug#21864838: SIG11 IN FIELD_BLOB::CMP| AT SQL/FIELD.CC
--echo #

CREATE TABLE t1 (col1 INT PRIMARY KEY, col2 VARCHAR(1), col3 BLOB AS (REPEAT(col2, 500)) VIRTUAL, KEY(col3(100)))
ENGINE=INNODB
PARTITION BY HASH(col1) PARTITIONS 2;

CREATE TABLE t2 (col1 INT PRIMARY KEY, col2 VARCHAR(1), col3 BLOB AS (REPEAT(col2, 500)) VIRTUAL, KEY(col3(100)))
ENGINE=INNODB
PARTITION BY HASH(col1) PARTITIONS 2;

INSERT INTO t1 (col1, col2) VALUES(1, 'd'), (2, 'c'), (3, 'b'), (4, 'a');
INSERT INTO t2 (col1, col2) VALUES(1, 'd'), (2, 'c'), (3, 'b'), (4, 'a');

SELECT t1.col1 FROM t1, t2 where t1.col1 = 3 AND t1.col3 = t2.col3 ORDER BY t2.col1 DESC;

DROP TABLE t1;
DROP TABLE t2;

--echo #
--echo # Bug #21881155: UNINTIALIZED READ IN KEY_REC_CMP CAUSE CRASH LATER
--echo #

CREATE TABLE t1 (col1 int, col2 BLOB AS ('a') VIRTUAL, col3 INT,
PRIMARY KEY(col1), KEY (col3, col2(1), col1))
ENGINE=INNODB
PARTITION BY KEY (col1) PARTITIONS 2;

INSERT INTO t1(col1) values (1),(2);
SELECT 1 FROM t1 WHERE col2 > 'a' GROUP BY col3;

DROP TABLE t1;

# TODO : Enable following once shared tablespace are allowed in Partitioned
#	 Table (wl#12034).
# --echo #
# --echo # Bug#21914047 SHOW CREATE DUMPS AFTER ALTER ON INNODB TABLE WITH PARTITIONS USING TABLESPACE
# --echo #
# CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
# CREATE TABLESPACE ts2 ADD DATAFILE 'ts2.ibd';
#
# CREATE TABLE t1 (a INT NOT NULL, PRIMARY KEY (a))
# ENGINE=InnoDB TABLESPACE ts2
# PARTITION BY RANGE(a)
# PARTITIONS 4
#  ( PARTITION p1 VALUES LESS THAN (2) TABLESPACE ts1,
#    PARTITION p2 VALUES LESS THAN (4) TABLESPACE innodb_system,
#    PARTITION p3 VALUES LESS THAN (6),
#    PARTITION p4  VALUES LESS THAN (8) TABLESPACE innodb_file_per_table);
#
# SHOW CREATE TABLE t1;
#
# --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
# --replace_result #P# #p#
# SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
# FROM information_schema.innodb_tables A
# LEFT JOIN
# information_schema.innodb_tablespaces B
# ON A.SPACE = B.SPACE
# WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
#
# ALTER TABLE t1 ADD COLUMN b int;
# SHOW CREATE TABLE t1;
#
# --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
# --replace_result #P# #p#
# SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
# FROM information_schema.innodb_tables A
# LEFT JOIN
# information_schema.innodb_tablespaces B
# ON A.SPACE = B.SPACE
# WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
#
# ALTER TABLE t1 ADD COLUMN c int, TABLESPACE ts1;
# SHOW CREATE TABLE t1;
#
# --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
# --replace_result #P# #p#
# SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
# FROM information_schema.innodb_tables A
# LEFT JOIN
# information_schema.innodb_tablespaces B
# ON A.SPACE = B.SPACE
# WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
#
# ALTER TABLE t1 ADD PARTITION (PARTITION p5 VALUES LESS THAN (10) TABLESPACE ts2);
# SHOW CREATE TABLE t1;
#
# --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
# --replace_result #P# #p#
# SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
# FROM information_schema.innodb_tables A
# LEFT JOIN
# information_schema.innodb_tablespaces B
# ON A.SPACE = B.SPACE
# WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
#
# ALTER TABLE t1 ADD PARTITION (PARTITION p6 VALUES LESS THAN (12));
# SHOW CREATE TABLE t1;
#
# --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
# --replace_result #P# #p#
# SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
# FROM information_schema.innodb_tables A
# LEFT JOIN
# information_schema.innodb_tablespaces B
# ON A.SPACE = B.SPACE
# WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
#
# ALTER TABLE t1 REORGANIZE PARTITION p4 INTO (PARTITION p4 VALUES LESS THAN (8));
# SHOW CREATE TABLE t1;
#
# --replace_result #P# #p#
# --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
# SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
# FROM information_schema.innodb_tables A
# LEFT JOIN
# information_schema.innodb_tablespaces B
# ON A.SPACE = B.SPACE
# WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
#
# ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (PARTITION p3 VALUES LESS THAN (6) TABLESPACE innodb_system);
# SHOW CREATE TABLE t1;
#
# --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
# --replace_result #P# #p#
# SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
# FROM information_schema.innodb_tables A
# LEFT JOIN
# information_schema.innodb_tablespaces B
# ON A.SPACE = B.SPACE
# WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
#
# DROP TABLE t1;
#
# CREATE TABLE t1 (a INT NOT NULL, PRIMARY KEY (a))
# ENGINE=InnoDB
# PARTITION BY RANGE(a)
# PARTITIONS 4
#  ( PARTITION p1 VALUES LESS THAN (2) TABLESPACE ts1,
#    PARTITION p2 VALUES LESS THAN (4) TABLESPACE innodb_system,
#    PARTITION p3 VALUES LESS THAN (6),
#    PARTITION p4  VALUES LESS THAN (8) TABLESPACE innodb_file_per_table);
#
# SHOW CREATE TABLE t1;
#
# --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
# --replace_result #P# #p#
# SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
# FROM information_schema.innodb_tables A
# LEFT JOIN
# information_schema.innodb_tablespaces B
# ON A.SPACE = B.SPACE
# WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
#
# ALTER TABLE t1 ADD COLUMN b int;
# SHOW CREATE TABLE t1;
#
# --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
# --replace_result #P# #p#
# SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
# FROM information_schema.innodb_tables A
# LEFT JOIN
# information_schema.innodb_tablespaces B
# ON A.SPACE = B.SPACE
# WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
#
# ALTER TABLE t1 ADD COLUMN c int, TABLESPACE ts1;
# SHOW CREATE TABLE t1;
#
# --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
# --replace_result #P# #p#
# SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
# FROM information_schema.innodb_tables A
# LEFT JOIN
# information_schema.innodb_tablespaces B
# ON A.SPACE = B.SPACE
# WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
#
# ALTER TABLE t1 ADD PARTITION (PARTITION p5 VALUES LESS THAN (10) TABLESPACE ts2);
# SHOW CREATE TABLE t1;
#
# --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
# --replace_result #P# #p#
# SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
# FROM information_schema.innodb_tables A
# LEFT JOIN
# information_schema.innodb_tablespaces B
# ON A.SPACE = B.SPACE
# WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
#
# ALTER TABLE t1 ADD PARTITION (PARTITION p6 VALUES LESS THAN (12));
# SHOW CREATE TABLE t1;
#
# --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
# --replace_result #P# #p#
# SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
# FROM information_schema.innodb_tables A
# LEFT JOIN
# information_schema.innodb_tablespaces B
# ON A.SPACE = B.SPACE
# WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
#
# ALTER TABLE t1 REORGANIZE PARTITION p4 INTO (PARTITION p4 VALUES LESS THAN (8));
# SHOW CREATE TABLE t1;
#
# --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
# --replace_result #P# #p#
# SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
# FROM information_schema.innodb_tables A
# LEFT JOIN
# information_schema.innodb_tablespaces B
# ON A.SPACE = B.SPACE
# WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
#
# ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (PARTITION p3 VALUES LESS THAN (6) TABLESPACE innodb_system);
# SHOW CREATE TABLE t1;
#
# --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
# --replace_result #P# #p#
# SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
# FROM information_schema.innodb_tables A
# LEFT JOIN
# information_schema.innodb_tablespaces B
# ON A.SPACE = B.SPACE
# WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
#
# DROP TABLE t1;
# DROP TABLESPACE ts1;
# DROP TABLESPACE ts2;
#
# --echo #
# --echo # Bug#22245554 OUTPUT OF SHOW CREATE TABLE INVOLVING PARTITION TABLESPACE
# --echo # LEADS TO CONFUSION
# --echo #
#
# CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
#
# --let $innodb_file_per_table=`select @@innodb_file_per_table`
#
# --let $TABLESPACE1=
# --let $TABLESPACE2=TABLESPACE innodb_system
# --let $TABLESPACE3=TABLESPACE innodb_file_per_table
# --let $TABLESPACE4=
#
# --let $INNODB_FILE_PER_TABLE_FOR_CREATE=OFF
# --let $INNODB_FILE_PER_TABLE_FOR_ALTER=ON
# --echo # CASE A
# --echo # file_per_table OFF for CREATE, ON for ALTER
# --source suite/innodb/include/create_alter_partition.inc
#
# --let $INNODB_FILE_PER_TABLE_FOR_CREATE=ON
# --let $INNODB_FILE_PER_TABLE_FOR_ALTER=OFF
# --echo # CASE B
# --echo # file_per_table ON for CREATE, OFF for ALTER
# --source suite/innodb/include/create_alter_partition.inc
#
# --let $TABLESPACE1=
# --let $TABLESPACE2=
# --let $TABLESPACE3=
# --let $TABLESPACE4=
#
# --let $INNODB_FILE_PER_TABLE_FOR_CREATE=OFF
# --let $INNODB_FILE_PER_TABLE_FOR_ALTER=OFF
# --echo # CASE C
# --echo # file_per_table OFF for CREATE, OFF for ALTER
# --source suite/innodb/include/create_alter_partition.inc
#
# --let $INNODB_FILE_PER_TABLE_FOR_CREATE=ON
# --let $INNODB_FILE_PER_TABLE_FOR_ALTER=ON
# --echo # CASE D
# --echo # file_per_table ON for CREATE, ON for ALTER
# --source suite/innodb/include/create_alter_partition.inc
#
# --let $INNODB_FILE_PER_TABLE_FOR_CREATE=ON
# --let $INNODB_FILE_PER_TABLE_FOR_ALTER=OFF
# --echo # CASE E
# --echo # file_per_table ON for CREATE, OFF for ALTER
# --source suite/innodb/include/create_alter_partition.inc
#
# --eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table
#
# DROP TABLESPACE ts1;

--echo # Bug#22444530 - GCOLS + PARTITIONED TABLE, CRASH IN
--echo #
set sql_mode='';
create table t (
   a int not null,
   b int generated always as (1) virtual not null,
   c int generated always as (1) virtual not null,
   key (c)
) engine=innodb partition by key (a) partitions 2;
insert into t(a) values(1);
select b from t group by c;

drop table t;

# Make column b a BLOB
create table t (
   a int not null,
   b blob generated always as ("a") virtual not null,
   c int generated always as (1) virtual not null,
   key (c)
) engine=innodb partition by key (a) partitions 2;
insert into t(a) values(1);
select b from t group by c;

drop table t;

--echo #
--echo # Bug#23037025 COL+PARTITION SIG11 IN ROW_SEL_STORE_MYSQL_REC
--echo #
set sql_mode='';

CREATE TABLE t1 (
   col1 INT,
   col2 INT AS (col1) VIRTUAL NOT NULL,
   col3 INT AS (1) VIRTUAL,
   KEY (col3)
) ENGINE=INNODB PARTITION BY KEY (col1) PARTITIONS 2;

CREATE TABLE t2 (col1 INTEGER);
CREATE TABLE t3 (col1 INTEGER);

CREATE TRIGGER t2_trig1 BEFORE INSERT ON t2 FOR EACH ROW
  INSERT INTO t3 SELECT col2 FROM t1 GROUP BY col3;

--disable_warnings
INSERT IGNORE INTO t1(col1) values(NULL);
--enable_warnings

--error ER_BAD_NULL_ERROR
INSERT INTO t2 VALUES(1);

DROP TRIGGER t2_trig1;
DROP TABLE t3;
DROP TABLE t2;
DROP TABLE t1;

--echo #
--echo # Bug#23194259 ASSERTION FAILURE:TRX0TRX.H:1416:SRV_READ_ONLY_MODE
--echo #

CREATE TABLE t1(col1 INT AS(1), KEY(col1))
ENGINE=INNODB
PARTITION BY KEY(col1) PARTITIONS 2;

--disable_warnings
SELECT * FROM t1 WHERE sha(uuid_short());
--enable_warnings

DROP TABLE t1;

# TODO : Enable following once shared tablespace are allowed in Partitioned
#	 Table (wl#12034).
# --echo #
# --echo # Bug#25516367 - CRASH IN HA_INNOBASE::COMMIT_INPLACE_ALTER_TABLE_IMPL
# --echo #
#
# CREATE TABLESPACE s1 ADD DATAFILE '1.ibd' ENGINE = InnoDB;
# CREATE TABLE t1 (col1 INT, col2 INT) ENGINE = InnoDB ROW_FORMAT = DYNAMIC TABLESPACE s1;
#
# ALTER TABLE t1 PARTITION BY RANGE(col1)
# ( PARTITION p0 VALUES LESS THAN (128), PARTITION p3 VALUES LESS THAN MAXVALUE );
# --error ER_ILLEGAL_HA_CREATE_OPTION
# ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
#
# DROP TABLE t1;
# DROP TABLESPACE s1;
#
# --echo #
# --echo # This checks if operation bit is set properly for REORGANIZE PARTITION
# --echo #
#
# CREATE TABLE t1 (a INTEGER, b INTEGER NOT NULL, KEY(b)) ENGINE = INNODB /*!50100 PARTITION BY KEY(b) PARTITIONS 3 */;
# SHOW CREATE TABLE t1;
# ALTER TABLE t1 REORGANIZE PARTITION;
# SHOW CREATE TABLE t1;
# DROP TABLE t1;
#
#
# --echo # Ignore incompatible options of a table which was created in not strict mode
#
# SET @orig_innodb_file_per_table= @@innodb_file_per_table;
# SET GLOBAL innodb_file_per_table = 0;
# SET @save_innodb_strict_mode=@@session.innodb_strict_mode;
# SET SESSION innodb_strict_mode = 0;
#
# CREATE TABLE t1(a INT NOT NULL, d INT NOT NULL, KEY(a), KEY(d)) ENGINE = InnoDB PARTITION BY HASH(d) PARTITIONS 02;
#
# SET SESSION innodb_strict_mode = 0;
#
# # To prevent the compression warning differences in different platform
# --disable_warnings
#
# ALTER TABLE t1 COMPRESSION='zlib', ALGORITHM=INPLACE;
#
# ALTER TABLE t1 TABLESPACE innodb_system, ALGORITHM=INPLACE, LOCK=DEFAULT;
#
# OPTIMIZE TABLE t1;
#
# --enable_warnings
#
# SHOW CREATE TABLE t1;
#
# SET GLOBAL innodb_file_per_table = @orig_innodb_file_per_table;
# SET SESSION innodb_strict_mode=@save_innodb_strict_mode;
#
# --source include/restart_mysqld.inc
#
# CHECK TABLE t1;
#
# INSERT INTO t1 VALUES(0, 1), (2, 3), (5, 6), (10, 11);
#
# SELECT * FROM t1 ORDER BY a;
#
# DROP TABLE t1;
#
#
# --echo # DATA DIRECTORY should be read correctly after restart
#
# --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
# eval CREATE TABLE t1 (a INT, b INT) PARTITION BY RANGE(a)
# (PARTITION P0 VALUES LESS THAN (10) DATA DIRECTORY '$MYSQL_TMP_DIR',
#  PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY '$MYSQL_TMP_DIR');
#
# --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
# SHOW CREATE TABLE t1;
#
# --source include/restart_mysqld.inc
#
# TRUNCATE TABLE t1;
#
# --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
# SHOW CREATE TABLE t1;
#
# DROP TABLE t1;
# # Remove directory left behind by the above
# --rmdir $MYSQL_TMP_DIR/test