Debian Codegraph @

mysql-test/suite/parts/r/partition_decimal_myisam.result

raw
1 create table t1 (a decimal(10,4) not null, primary key(a)) engine='MYISAM'
2 partition by key (a) (
3 partition pa1 max_rows=20 min_rows=2,
4 partition pa2 max_rows=30 min_rows=3,
5 partition pa3 max_rows=30 min_rows=4,
6 partition pa4 max_rows=40 min_rows=2);
7 show create table t1;
8 Table Create Table
9 t1 CREATE TABLE `t1` (
10 `a` decimal(10,4) NOT NULL,
11 PRIMARY KEY (`a`)
12 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
13 PARTITION BY KEY (`a`)
14 (PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM,
15 PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM,
16 PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM,
17 PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM)
18 insert into t1 values (999999.9999), (-999999.9999), (123456.7899), (-123456.7899), (-1.5), (1), (0), (-1), (1.5), (1234.567), (-1234.567);
19 select * from t1;
20 a
21 -1.0000
22 -1.5000
23 -1234.5670
24 -123456.7899
25 -999999.9999
26 0.0000
27 1.0000
28 1.5000
29 1234.5670
30 123456.7899
31 999999.9999
32 select * from t1 where a=1234.567;
33 a
34 1234.5670
35 delete from t1 where a=1234.567;
36 select * from t1;
37 a
38 -1.0000
39 -1.5000
40 -1234.5670
41 -123456.7899
42 -999999.9999
43 0.0000
44 1.0000
45 1.5000
46 123456.7899
47 999999.9999
48 drop table t1;
49 create table t2 (a decimal(18,9) not null, primary key(a)) engine='MYISAM'
50 partition by key (a) partitions 10;
51 show create table t2;
52 Table Create Table
53 t2 CREATE TABLE `t2` (
54 `a` decimal(18,9) NOT NULL,
55 PRIMARY KEY (`a`)
56 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
57 PARTITION BY KEY (`a`)
58 PARTITIONS 10
59 insert into t2 values (999999999.999999999), (-999999999.999999999), (-1.5), (-1), (0), (1.5), (1234.567), (-1234.567);
60 select * from t2;
61 a
62 -1.000000000
63 -1.500000000
64 -1234.567000000
65 -999999999.999999999
66 0.000000000
67 1.500000000
68 1234.567000000
69 999999999.999999999
70 select * from t2 where a=1234.567;
71 a
72 1234.567000000
73 delete from t2 where a=1234.567;
74 select * from t2;
75 a
76 -1.000000000
77 -1.500000000
78 -1234.567000000
79 -999999999.999999999
80 0.000000000
81 1.500000000
82 999999999.999999999
83 delete from t2;
84 65535*3 inserts;
85 select count(*) from t2;
86 count(*)
87 196605
88 drop table t2;
89 create table t3 (a decimal(18,9) not null, primary key(a)) engine='MYISAM'
90 partition by range (floor(a)) subpartition by key (a) subpartitions 2 (
91 partition pa2 values less than (2),
92 partition pa4 values less than (4),
93 partition pa6 values less than (6),
94 partition pa8 values less than (8),
95 partition pa10 values less than (10)
96 );
97 show create table t3;
98 Table Create Table
99 t3 CREATE TABLE `t3` (
100 `a` decimal(18,9) NOT NULL,
101 PRIMARY KEY (`a`)
102 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
103 PARTITION BY RANGE (floor(`a`))
104 SUBPARTITION BY KEY (`a`)
105 SUBPARTITIONS 2
106 (PARTITION `pa2` VALUES LESS THAN (2) ENGINE = MyISAM,
107 PARTITION `pa4` VALUES LESS THAN (4) ENGINE = MyISAM,
108 PARTITION `pa6` VALUES LESS THAN (6) ENGINE = MyISAM,
109 PARTITION `pa8` VALUES LESS THAN (8) ENGINE = MyISAM,
110 PARTITION `pa10` VALUES LESS THAN (10) ENGINE = MyISAM)
111 9*3 inserts;
112 select count(*) from t3;
113 count(*)
114 27
115 drop table t3;
116 create table t4 (a decimal(18,9) not null, primary key(a)) engine='MYISAM'
117 partition by list (ceiling(a)) subpartition by key (a) subpartitions 2 (
118 partition pa2 values in (1,2),
119 partition pa4 values in (3,4),
120 partition pa6 values in (5,6),
121 partition pa8 values in (7,8),
122 partition pa10 values in (9,10)
123 );
124 show create table t4;
125 Table Create Table
126 t4 CREATE TABLE `t4` (
127 `a` decimal(18,9) NOT NULL,
128 PRIMARY KEY (`a`)
129 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
130 PARTITION BY LIST (ceiling(`a`))
131 SUBPARTITION BY KEY (`a`)
132 SUBPARTITIONS 2
133 (PARTITION `pa2` VALUES IN (1,2) ENGINE = MyISAM,
134 PARTITION `pa4` VALUES IN (3,4) ENGINE = MyISAM,
135 PARTITION `pa6` VALUES IN (5,6) ENGINE = MyISAM,
136 PARTITION `pa8` VALUES IN (7,8) ENGINE = MyISAM,
137 PARTITION `pa10` VALUES IN (9,10) ENGINE = MyISAM)
138 9*3 inserts;
139 select count(*) from t4;
140 count(*)
141 27
142 drop table t4;