| 1 |
################################################################################ |
| 2 |
# t/partition_supported_sql_funcs.inc # |
| 3 |
# # |
| 4 |
# Purpose: # |
| 5 |
# Tests frame for allowed sql functions # |
| 6 |
# # |
| 7 |
# # |
| 8 |
#------------------------------------------------------------------------------# |
| 9 |
# Original Author: HH # |
| 10 |
# Original Date: 2006-11-22 # |
| 11 |
# Change Author: MattiasJ # |
| 12 |
# Change Date: 2008-05-15 # |
| 13 |
# Change: Added $max_8_partitions since ndb only capable of 8 partitions # |
| 14 |
# and $no_reorg_partition since ndb does not support that # |
| 15 |
################################################################################ |
| 16 |
--echo ------------------------------------------------------------------------- |
| 17 |
--echo --- $sqlfunc in partition with coltype $coltype |
| 18 |
--echo ------------------------------------------------------------------------- |
| 19 |
--disable_abort_on_error |
| 20 |
--disable_warnings |
| 21 |
drop table if exists t1 ; |
| 22 |
drop table if exists t2 ; |
| 23 |
drop table if exists t3 ; |
| 24 |
drop table if exists t4 ; |
| 25 |
drop table if exists t5 ; |
| 26 |
drop table if exists t6 ; |
| 27 |
--enable_warnings |
| 28 |
--enable_abort_on_error |
| 29 |
|
| 30 |
let $part_t1= partition by range($sqlfunc) |
| 31 |
(partition p0 values less than (15), |
| 32 |
partition p1 values less than maxvalue); |
| 33 |
|
| 34 |
let $part_t2= partition by list($sqlfunc) |
| 35 |
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), |
| 36 |
partition p1 values in (11,12,13,14,15,16,17,18,19,20), |
| 37 |
partition p2 values in (21,22,23,24,25,26,27,28,29,30), |
| 38 |
partition p3 values in (31,32,33,34,35,36,37,38,39,40), |
| 39 |
partition p4 values in (41,42,43,44,45,46,47,48,49,50), |
| 40 |
partition p5 values in (51,52,53,54,55,56,57,58,59,60) |
| 41 |
); |
| 42 |
|
| 43 |
let $part_t3= partition by hash($sqlfunc); |
| 44 |
|
| 45 |
let $part_t4= partition by range(colint) |
| 46 |
subpartition by hash($sqlfunc) subpartitions 2 |
| 47 |
(partition p0 values less than (15), |
| 48 |
partition p1 values less than maxvalue); |
| 49 |
|
| 50 |
let $part_t5= partition by list(colint) |
| 51 |
subpartition by hash($sqlfunc) subpartitions 2 |
| 52 |
(partition p0 values in (1,2,3,4,5,6,7,8,9,10), |
| 53 |
partition p1 values in (11,12,13,14,15,16,17,18,19,20), |
| 54 |
partition p2 values in (21,22,23,24,25,26,27,28,29,30), |
| 55 |
partition p3 values in (31,32,33,34,35,36,37,38,39,40), |
| 56 |
partition p4 values in (41,42,43,44,45,46,47,48,49,50), |
| 57 |
partition p5 values in (51,52,53,54,55,56,57,58,59,60) |
| 58 |
); |
| 59 |
if ($max_8_partitions) |
| 60 |
{ |
| 61 |
let $part_t5= partition by list(colint) |
| 62 |
subpartition by hash($sqlfunc) subpartitions 2 |
| 63 |
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15), |
| 64 |
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30), |
| 65 |
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45), |
| 66 |
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) |
| 67 |
); |
| 68 |
} |
| 69 |
|
| 70 |
let $part_t6= partition by range(colint) |
| 71 |
(partition p0 values less than ($valsqlfunc), |
| 72 |
partition p1 values less than maxvalue); |
| 73 |
|
| 74 |
let $part_t55_altered= partition by list(colint) |
| 75 |
subpartition by hash($sqlfunc) subpartitions 5 |
| 76 |
(partition p0 values in (1,2,3,4,5,6,7,8,9,10), |
| 77 |
partition p1 values in (11,12,13,14,15,16,17,18,19,20), |
| 78 |
partition p2 values in (21,22,23,24,25,26,27,28,29,30), |
| 79 |
partition p3 values in (31,32,33,34,35,36,37,38,39,40), |
| 80 |
partition p4 values in (41,42,43,44,45,46,47,48,49,50), |
| 81 |
partition p5 values in (51,52,53,54,55,56,57,58,59,60) |
| 82 |
); |
| 83 |
if ($max_8_partitions) |
| 84 |
{ |
| 85 |
let $part_t55_altered= partition by list(colint) |
| 86 |
subpartition by hash($sqlfunc) subpartitions 4 |
| 87 |
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30), |
| 88 |
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) |
| 89 |
); |
| 90 |
} |
| 91 |
--echo ------------------------------------------------------------------------- |
| 92 |
--echo --- Create tables with $sqlfunc |
| 93 |
--echo ------------------------------------------------------------------------- |
| 94 |
eval create table t1 (col1 $coltype) engine=$engine |
| 95 |
$part_t1; |
| 96 |
|
| 97 |
eval create table t2 (col1 $coltype) engine=$engine |
| 98 |
$part_t2; |
| 99 |
|
| 100 |
eval create table t3 (col1 $coltype) engine=$engine |
| 101 |
$part_t3; |
| 102 |
|
| 103 |
eval create table t4 (colint int, col1 $coltype) engine=$engine |
| 104 |
$part_t4; |
| 105 |
|
| 106 |
eval create table t5 (colint int, col1 $coltype) engine=$engine |
| 107 |
$part_t5; |
| 108 |
|
| 109 |
eval create table t6 (colint int, col1 $coltype) engine=$engine |
| 110 |
$part_t6; |
| 111 |
|
| 112 |
--echo ------------------------------------------------------------------------- |
| 113 |
--echo --- Access tables with $sqlfunc |
| 114 |
--echo ------------------------------------------------------------------------- |
| 115 |
|
| 116 |
eval insert into t1 values ($val1); |
| 117 |
eval insert into t1 values ($val2); |
| 118 |
|
| 119 |
eval insert into t2 values ($val1); |
| 120 |
eval insert into t2 values ($val2); |
| 121 |
eval insert into t2 values ($val3); |
| 122 |
|
| 123 |
eval insert into t3 values ($val1); |
| 124 |
eval insert into t3 values ($val2); |
| 125 |
eval insert into t3 values ($val3); |
| 126 |
|
| 127 |
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR |
| 128 |
eval load data infile '$MYSQLTEST_VARDIR/std_data/parts/$infile' into table t4; |
| 129 |
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR |
| 130 |
eval load data infile '$MYSQLTEST_VARDIR/std_data/parts/$infile' into table t5; |
| 131 |
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR |
| 132 |
eval load data infile '$MYSQLTEST_VARDIR/std_data/parts/$infile' into table t6; |
| 133 |
|
| 134 |
eval select $sqlfunc from t1 order by col1; |
| 135 |
|
| 136 |
select * from t1 order by col1; |
| 137 |
select * from t2 order by col1; |
| 138 |
select * from t3 order by col1; |
| 139 |
select * from t4 order by colint; |
| 140 |
select * from t5 order by colint; |
| 141 |
select * from t6 order by colint; |
| 142 |
|
| 143 |
if ($do_long_tests) |
| 144 |
{ |
| 145 |
eval update t1 set col1=$val4 where col1=$val1; |
| 146 |
eval update t2 set col1=$val4 where col1=$val1; |
| 147 |
eval update t3 set col1=$val4 where col1=$val1; |
| 148 |
eval update t4 set col1=$val4 where col1=$val1; |
| 149 |
eval update t5 set col1=$val4 where col1=$val1; |
| 150 |
eval update t6 set col1=$val4 where col1=$val1; |
| 151 |
|
| 152 |
select * from t1 order by col1; |
| 153 |
select * from t2 order by col1; |
| 154 |
select * from t3 order by col1; |
| 155 |
select * from t4 order by colint; |
| 156 |
select * from t5 order by colint; |
| 157 |
select * from t6 order by colint; |
| 158 |
} |
| 159 |
|
| 160 |
--echo ------------------------------------------------------------------------- |
| 161 |
--echo --- Alter tables with $sqlfunc |
| 162 |
--echo ------------------------------------------------------------------------- |
| 163 |
|
| 164 |
--disable_abort_on_error |
| 165 |
--disable_warnings |
| 166 |
drop table if exists t11 ; |
| 167 |
drop table if exists t22 ; |
| 168 |
drop table if exists t33 ; |
| 169 |
drop table if exists t44 ; |
| 170 |
drop table if exists t55 ; |
| 171 |
drop table if exists t66 ; |
| 172 |
--enable_warnings |
| 173 |
--enable_abort_on_error |
| 174 |
|
| 175 |
eval create table t11 engine=$engine as select * from t1; |
| 176 |
eval create table t22 engine=$engine as select * from t2; |
| 177 |
eval create table t33 engine=$engine as select * from t3; |
| 178 |
eval create table t44 engine=$engine as select * from t4; |
| 179 |
eval create table t55 engine=$engine as select * from t5; |
| 180 |
eval create table t66 engine=$engine as select * from t6; |
| 181 |
eval alter table t11 |
| 182 |
$part_t1; |
| 183 |
eval alter table t22 |
| 184 |
$part_t2; |
| 185 |
eval alter table t33 |
| 186 |
$part_t3; |
| 187 |
eval alter table t44 |
| 188 |
$part_t4; |
| 189 |
eval alter table t55 |
| 190 |
$part_t5; |
| 191 |
eval alter table t66 |
| 192 |
$part_t6; |
| 193 |
|
| 194 |
select * from t11 order by col1; |
| 195 |
select * from t22 order by col1; |
| 196 |
select * from t33 order by col1; |
| 197 |
select * from t44 order by colint; |
| 198 |
select * from t55 order by colint; |
| 199 |
select * from t66 order by colint; |
| 200 |
|
| 201 |
if ($do_long_tests) |
| 202 |
{ |
| 203 |
--echo --------------------------- |
| 204 |
--echo ---- some alter table begin |
| 205 |
--echo --------------------------- |
| 206 |
if (!$no_reorg_partition) |
| 207 |
{ |
| 208 |
eval alter table t11 |
| 209 |
reorganize partition p0,p1 into |
| 210 |
(partition s1 values less than maxvalue); |
| 211 |
select * from t11 order by col1; |
| 212 |
|
| 213 |
eval alter table t11 |
| 214 |
reorganize partition s1 into |
| 215 |
(partition p0 values less than (15), |
| 216 |
partition p1 values less than maxvalue); |
| 217 |
select * from t11 order by col1; |
| 218 |
} |
| 219 |
|
| 220 |
eval alter table t55 |
| 221 |
$part_t55_altered; |
| 222 |
show create table t55; |
| 223 |
select * from t55 order by colint; |
| 224 |
|
| 225 |
if (!$no_reorg_partition) |
| 226 |
{ |
| 227 |
eval alter table t66 |
| 228 |
reorganize partition p0,p1 into |
| 229 |
(partition s1 values less than maxvalue); |
| 230 |
select * from t66 order by colint; |
| 231 |
|
| 232 |
eval alter table t66 |
| 233 |
reorganize partition s1 into |
| 234 |
(partition p0 values less than ($valsqlfunc), |
| 235 |
partition p1 values less than maxvalue); |
| 236 |
select * from t66 order by colint; |
| 237 |
|
| 238 |
eval alter table t66 |
| 239 |
reorganize partition p0,p1 into |
| 240 |
(partition s1 values less than maxvalue); |
| 241 |
select * from t66 order by colint; |
| 242 |
|
| 243 |
eval alter table t66 |
| 244 |
reorganize partition s1 into |
| 245 |
(partition p0 values less than ($valsqlfunc), |
| 246 |
partition p1 values less than maxvalue); |
| 247 |
select * from t66 order by colint; |
| 248 |
} |
| 249 |
|
| 250 |
let $t1=t1; |
| 251 |
let $t2=t2; |
| 252 |
let $t3=t3; |
| 253 |
let $t4=t4; |
| 254 |
let $t5=t5; |
| 255 |
let $t6=t6; |
| 256 |
--source suite/parts/inc/part_supported_sql_funcs_delete.inc |
| 257 |
|
| 258 |
let $t1=t11; |
| 259 |
let $t2=t22; |
| 260 |
let $t3=t33; |
| 261 |
let $t4=t44; |
| 262 |
let $t5=t55; |
| 263 |
let $t6=t66; |
| 264 |
--source suite/parts/inc/part_supported_sql_funcs_delete.inc |
| 265 |
--echo ------------------------- |
| 266 |
--echo ---- some alter table end |
| 267 |
--echo ------------------------- |
| 268 |
} |
| 269 |
--disable_warnings |
| 270 |
drop table if exists t1 ; |
| 271 |
drop table if exists t2 ; |
| 272 |
drop table if exists t3 ; |
| 273 |
drop table if exists t4 ; |
| 274 |
drop table if exists t5 ; |
| 275 |
drop table if exists t6 ; |
| 276 |
drop table if exists t11 ; |
| 277 |
drop table if exists t22 ; |
| 278 |
drop table if exists t33 ; |
| 279 |
drop table if exists t44 ; |
| 280 |
drop table if exists t55 ; |
| 281 |
drop table if exists t66 ; |
| 282 |
--enable_warnings |
| 283 |
|