Debian Codegraph @

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

raw
1 SET @@session.default_storage_engine = 'MyISAM';
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 # MDEV-18166 ASSERT_COLUMN_MARKED_FOR_READ failed on tables with vcols
91 #
92 CREATE TABLE t1 (
93 a INT,
94 b INT,
95 c BIT(4) NOT NULL DEFAULT b'0',
96 pk INTEGER AUTO_INCREMENT,
97 d BIT(4) AS (c) VIRTUAL,
98 PRIMARY KEY(pk),
99 KEY (b,d)
100 ) PARTITION BY HASH(pk);
101 INSERT INTO t1 () VALUES (),();
102 UPDATE t1 SET a = 0 WHERE b IS NULL ORDER BY pk;
103 DROP TABLE t1;
104 #
105 # MDEV-26220 Server crashes with indexed by prefix virtual column
106 #
107 CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, b CHAR(20), c CHAR(20) AS (b),
108 KEY (c(10),a)) PARTITION BY HASH(pk);
109 INSERT INTO t1 (pk,a,b) VALUES (1,10,'foo'),(2,11,'baz');
110 SELECT a FROM t1;
111 a
112 11
113 10
114 DROP TABLE t1;
115 DROP VIEW IF EXISTS v1,v2;
116 DROP TABLE IF EXISTS t1,t2,t3;
117 DROP PROCEDURE IF EXISTS p1;
118 DROP FUNCTION IF EXISTS f1;
119 DROP TRIGGER IF EXISTS trg1;
120 DROP TRIGGER IF EXISTS trg2;
121 set sql_warnings = 0;