Debian Codegraph @

mysql-test/suite/gcol/r/gcol_partition_innodb.result

raw
1 SET @@session.default_storage_engine = 'InnoDB';
2 drop table if exists t1;
3 # Case 1. Partitioning by RANGE based on a non-stored generated column.
4 CREATE TABLE t1 (
5 a DATE NOT NULL,
6 b int generated always as (year(a)) virtual
7 )
8 PARTITION BY RANGE( b ) (
9 PARTITION p0 VALUES LESS THAN (2006),
10 PARTITION p2 VALUES LESS THAN (2008)
11 );
12 insert into t1 values ('2006-01-01',default);
13 insert into t1 values ('2007-01-01',default);
14 insert into t1 values ('2005-01-01',default);
15 select * from t1;
16 a b
17 2005-01-01 2005
18 2006-01-01 2006
19 2007-01-01 2007
20 # Modify the expression of generated column b
21 ALTER TABLE t1 modify b int generated always as (year(a)-1) virtual;
22 select * from t1;
23 a b
24 2005-01-01 2004
25 2006-01-01 2005
26 2007-01-01 2006
27 drop table t1;
28 # Case 2. Partitioning by LIST based on a stored generated column.
29 CREATE TABLE t1 (a int, b int generated always as (a % 3 ) stored)
30 PARTITION BY LIST (a+1)
31 (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2));
32 insert into t1 values (1,default);
33 select * from t1;
34 a b
35 1 1
36 select * from t1;
37 a b
38 1 1
39 drop table t1;
40 # Case 3. Partitioning by HASH based on a non-stored generated column.
41 CREATE TABLE t1 (
42 a DATE NOT NULL,
43 b int generated always as (year(a)) virtual
44 )
45 PARTITION BY HASH( b % 3 ) PARTITIONS 3;
46 insert into t1 values ('2005-01-01',default);
47 insert into t1 values ('2006-01-01',default);
48 select * from t1;
49 a b
50 2005-01-01 2005
51 2006-01-01 2006
52 # Modify the expression of generated column b
53 ALTER TABLE t1 modify b int generated always as (year(a)-1) virtual;
54 select * from t1;
55 a b
56 2005-01-01 2004
57 2006-01-01 2005
58 drop table t1;
59 #
60 # Bug#21779011 INVALID READS AND SENDING RANDOM SERVER MEMORY BACK
61 # TO CLIENT
62 #
63 CREATE TABLE t (
64 c INTEGER GENERATED ALWAYS AS (2) VIRTUAL,
65 d INTEGER,
66 KEY (d)
67 ) PARTITION BY KEY (d) PARTITIONS 2;
68 INSERT INTO t (d) VALUES (1),(1),(2),(2);
69 SELECT c FROM t WHERE d >= 1 GROUP BY d LIMIT 2;
70 c
71 2
72 2
73 DROP TABLE t;
74 #
75 # Bug#21779554: CHECK_MISPLACED_ROWS BOGUS "FOUND A MISPLACED ROW"
76 # AND CRASHES
77 #
78 CREATE TABLE t(a INT,b INT GENERATED ALWAYS AS (1) VIRTUAL,c INT)
79 PARTITION BY KEY (b)PARTITIONS 6;
80 INSERT INTO t VALUES();
81 CHECK TABLE t EXTENDED;
82 Table Op Msg_type Msg_text
83 test.t check status OK
84 FLUSH TABLES;
85 CHECK TABLE t EXTENDED;
86 Table Op Msg_type Msg_text
87 test.t check status OK
88 DROP TABLE t;
89 #
90 # Bug#22574695 ASSERTION `!TABLE || (!TABLE->READ_SET || BITMAP_IS_SET(TABLE->READ_SET, FIELD_I
91 #
92 CREATE TABLE t1(
93 col1 VARCHAR(500),
94 col2 INT,
95 col5 VARCHAR(500) GENERATED ALWAYS AS
96 (SUBSTR(col1,1,500)) VIRTUAL
97 ) ENGINE = InnoDB PARTITION BY KEY(col5) PARTITIONS 2;
98 INSERT INTO test . t1 SET col1 = REPEAT('_', 500);
99 COMMIT;
100 UPDATE t1 SET col2 = 1;
101 SELECT 'Obviously no crash';
102 Obviously no crash
103 Obviously no crash
104 DROP TABLE t1;
105 DROP VIEW IF EXISTS v1,v2;
106 DROP TABLE IF EXISTS t1,t2,t3;
107 DROP PROCEDURE IF EXISTS p1;
108 DROP FUNCTION IF EXISTS f1;
109 DROP TRIGGER IF EXISTS trg1;
110 DROP TRIGGER IF EXISTS trg2;
111 set sql_warnings = 0;