1SET @@session.default_storage_engine = 'MyISAM'; 2drop table if exists t1; 3# Case 1. Partitioning by RANGE based on a non-stored generated column. 4CREATE TABLE t1 ( 5a DATE NOT NULL, 6b int generated always as (year(a)) virtual 7) 8PARTITION BY RANGE( b ) ( 9PARTITION p0 VALUES LESS THAN (2006), 10PARTITION p2 VALUES LESS THAN (2008) 11); 12insert into t1 values ('2006-01-01',default); 13insert into t1 values ('2007-01-01',default); 14insert into t1 values ('2005-01-01',default); 15select * from t1; 16a b 172005-01-01 2005 182006-01-01 2006 192007-01-01 2007 20# Modify the expression of generated column b 21ALTER TABLE t1 modify b int generated always as (year(a)-1) virtual; 22select * from t1; 23a b 242005-01-01 2004 252006-01-01 2005 262007-01-01 2006 27drop table t1; 28# Case 2. Partitioning by LIST based on a stored generated column. 29CREATE TABLE t1 (a int, b int generated always as (a % 3 ) stored) 30PARTITION BY LIST (a+1) 31(PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2)); 32insert into t1 values (1,default); 33select * from t1; 34a b 351 1 36select * from t1; 37a b 381 1 39drop table t1; 40# Case 3. Partitioning by HASH based on a non-stored generated column. 41CREATE TABLE t1 ( 42a DATE NOT NULL, 43b int generated always as (year(a)) virtual 44) 45PARTITION BY HASH( b % 3 ) PARTITIONS 3; 46insert into t1 values ('2005-01-01',default); 47insert into t1 values ('2006-01-01',default); 48select * from t1; 49a b 502005-01-01 2005 512006-01-01 2006 52# Modify the expression of generated column b 53ALTER TABLE t1 modify b int generated always as (year(a)-1) virtual; 54select * from t1; 55a b 562005-01-01 2004 572006-01-01 2005 58drop table t1; 59# 60# Bug#21779011 INVALID READS AND SENDING RANDOM SERVER MEMORY BACK 61# TO CLIENT 62# 63CREATE TABLE t ( 64c INTEGER GENERATED ALWAYS AS (2) VIRTUAL, 65d INTEGER, 66KEY (d) 67) PARTITION BY KEY (d) PARTITIONS 2; 68INSERT INTO t (d) VALUES (1),(1),(2),(2); 69SELECT c FROM t WHERE d >= 1 GROUP BY d LIMIT 2; 70c 712 722 73DROP TABLE t; 74# 75# Bug#21779554: CHECK_MISPLACED_ROWS BOGUS "FOUND A MISPLACED ROW" 76# AND CRASHES 77# 78CREATE TABLE t(a INT,b INT GENERATED ALWAYS AS (1) VIRTUAL,c INT) 79PARTITION BY KEY (b)PARTITIONS 6; 80INSERT INTO t VALUES(); 81CHECK TABLE t EXTENDED; 82Table Op Msg_type Msg_text 83test.t check status OK 84FLUSH TABLES; 85CHECK TABLE t EXTENDED; 86Table Op Msg_type Msg_text 87test.t check status OK 88DROP TABLE t; 89# 90# MDEV-18166 ASSERT_COLUMN_MARKED_FOR_READ failed on tables with vcols 91# 92CREATE TABLE t1 ( 93a INT, 94b INT, 95c BIT(4) NOT NULL DEFAULT b'0', 96pk INTEGER AUTO_INCREMENT, 97d BIT(4) AS (c) VIRTUAL, 98PRIMARY KEY(pk), 99KEY (b,d) 100) PARTITION BY HASH(pk); 101INSERT INTO t1 () VALUES (),(); 102UPDATE t1 SET a = 0 WHERE b IS NULL ORDER BY pk; 103DROP TABLE t1; 104# 105# MDEV-26220 Server crashes with indexed by prefix virtual column 106# 107CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, b CHAR(20), c CHAR(20) AS (b), 108KEY (c(10),a)) PARTITION BY HASH(pk); 109INSERT INTO t1 (pk,a,b) VALUES (1,10,'foo'),(2,11,'baz'); 110SELECT a FROM t1; 111a 11211 11310 114DROP TABLE t1; 115DROP VIEW IF EXISTS v1,v2; 116DROP TABLE IF EXISTS t1,t2,t3; 117DROP PROCEDURE IF EXISTS p1; 118DROP FUNCTION IF EXISTS f1; 119DROP TRIGGER IF EXISTS trg1; 120DROP TRIGGER IF EXISTS trg2; 121set sql_warnings = 0; 122