SET @@session.default_storage_engine = 'InnoDB'; SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency; SET GLOBAL innodb_purge_rseg_truncate_frequency = 1; drop table if exists t1; # Case 1. Partitioning by RANGE based on a non-stored generated column. CREATE TABLE t1 ( a DATE NOT NULL, b int generated always as (year(a)) virtual ) PARTITION BY RANGE( b ) ( PARTITION p0 VALUES LESS THAN (2006), PARTITION p2 VALUES LESS THAN (2008) ); insert into t1 values ('2006-01-01',default); insert into t1 values ('2007-01-01',default); insert into t1 values ('2005-01-01',default); select * from t1; a b 2005-01-01 2005 2006-01-01 2006 2007-01-01 2007 # Modify the expression of generated column b ALTER TABLE t1 modify b int generated always as (year(a)-1) virtual; select * from t1; a b 2005-01-01 2004 2006-01-01 2005 2007-01-01 2006 drop table t1; # Case 2. Partitioning by LIST based on a stored generated column. CREATE TABLE t1 (a int, b int generated always as (a % 3 ) stored) PARTITION BY LIST (a+1) (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2)); insert into t1 values (1,default); select * from t1; a b 1 1 select * from t1; a b 1 1 drop table t1; # Case 3. Partitioning by HASH based on a non-stored generated column. CREATE TABLE t1 ( a DATE NOT NULL, b int generated always as (year(a)) virtual ) PARTITION BY HASH( b % 3 ) PARTITIONS 3; insert into t1 values ('2005-01-01',default); insert into t1 values ('2006-01-01',default); select * from t1; a b 2005-01-01 2005 2006-01-01 2006 # Modify the expression of generated column b ALTER TABLE t1 modify b int generated always as (year(a)-1) virtual; select * from t1; a b 2005-01-01 2004 2006-01-01 2005 drop table t1; # # Bug#21779011 INVALID READS AND SENDING RANDOM SERVER MEMORY BACK # TO CLIENT # CREATE TABLE t ( c INTEGER GENERATED ALWAYS AS (2) VIRTUAL, d INTEGER, KEY (d) ) PARTITION BY KEY (d) PARTITIONS 2; INSERT INTO t (d) VALUES (1),(1),(2),(2); SELECT c FROM t WHERE d >= 1 GROUP BY d LIMIT 2; c 2 2 DROP TABLE t; # # Bug#21779554: CHECK_MISPLACED_ROWS BOGUS "FOUND A MISPLACED ROW" # AND CRASHES # CREATE TABLE t(a INT,b INT GENERATED ALWAYS AS (1) VIRTUAL,c INT) PARTITION BY KEY (b)PARTITIONS 6; INSERT INTO t VALUES(); CHECK TABLE t EXTENDED; Table Op Msg_type Msg_text test.t check status OK FLUSH TABLES; CHECK TABLE t EXTENDED; Table Op Msg_type Msg_text test.t check status OK DROP TABLE t; # # MDEV-16980 Wrongly set tablename len while opening the # table for purge thread # CREATE TABLE t1(pk SERIAL, d DATE, vd DATE AS (d) VIRTUAL, PRIMARY KEY(pk), KEY (vd))ENGINE=InnoDB PARTITION BY HASH(pk) PARTITIONS 2; INSERT IGNORE INTO t1 (d) VALUES ('2015-04-14'); SET sql_mode= ''; REPLACE INTO t1 SELECT * FROM t1; Warnings: Warning 1906 The value specified for generated column 'vd' in table 't1' has been ignored DROP TABLE t1; InnoDB 0 transactions not purged DROP VIEW IF EXISTS v1,v2; DROP TABLE IF EXISTS t1,t2,t3; DROP PROCEDURE IF EXISTS p1; DROP FUNCTION IF EXISTS f1; DROP TRIGGER IF EXISTS trg1; DROP TRIGGER IF EXISTS trg2; set sql_warnings = 0; SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency;