Debian Codegraph @

mysql-test/suite/parts/inc/partition_value.inc

raw
1 ################################################################################
2 # inc/partition_value.inc #
3 # #
4 # Purpose: #
5 # Tests around "exotic" values calculated by the partitioning function #
6 # #
7 #------------------------------------------------------------------------------#
8 # Original Author: mleich #
9 # Original Date: 2006-04-11 #
10 # Change Author: #
11 # Change Date: #
12 # Change: #
13 ################################################################################
14
15
16 --echo
17 --echo This test relies on the CAST() function for partitioning, which
18 --echo is not allowed. Not deleting it yet, as it may have some useful
19 --echo bits in it. See Bug #30581, "partition_value tests use disallowed
20 --echo CAST() function"
21 --echo
22
23 --disable_parsing
24
25 --echo
26 --echo #========================================================================
27 --echo # Calculation of "exotic" results within the partition function
28 --echo # outside of SIGNED BIGINT value range, 0, NULL
29 --echo # column used in partitioning function has type CHAR
30 --echo #========================================================================
31 --echo # 1. HASH(<check value>)
32 --disable_warnings
33 DROP TABLE IF EXISTS t1;
34 --enable_warnings
35 #
36 eval CREATE TABLE t1 (
37 $column_list
38 )
39 PARTITION BY HASH(CAST(f_char1 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)) PARTITIONS 8;
40 let $my_val= 2147483646;
41 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
42 VALUES($my_val,$my_val,'$my_val','$my_val','#$my_val#');
43 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '$my_val';
44 let $my_val= -2147483646;
45 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
46 VALUES($my_val,$my_val,'$my_val','$my_val','#$my_val#');
47 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '$my_val';
48 let $my_val= 0;
49 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
50 VALUES($my_val,$my_val,'$my_val','$my_val','#$my_val#');
51 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '$my_val';
52 # let $my_val= NULL;
53 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
54 VALUES(NULL,NULL,NULL,NULL,NULL);
55 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 IS NULL;
56 DROP TABLE t1;
57 #
58 --echo # 2. RANGE(<check value>)
59 eval CREATE TABLE t1 (
60 $column_list
61 )
62 PARTITION BY RANGE(CAST(f_char1 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER))
63 (PARTITION p0 VALUES LESS THAN (0),
64 PARTITION p1 VALUES LESS THAN (1000000),
65 PARTITION p2 VALUES LESS THAN MAXVALUE);
66 let $my_val= 2147483646;
67 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
68 VALUES($my_val,$my_val,'$my_val','$my_val','#$my_val#');
69 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '$my_val';
70 let $my_val= -2147483646;
71 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
72 VALUES($my_val,$my_val,'$my_val','$my_val','#$my_val#');
73 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '$my_val';
74 let $my_val= 0;
75 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
76 VALUES($my_val,$my_val,'$my_val','$my_val','#$my_val#');
77 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '$my_val';
78 # let $my_val= NULL;
79 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
80 VALUES(NULL,NULL,NULL,NULL,NULL);
81 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 IS NULL;
82 DROP TABLE t1;
83 --echo # 3. LIST(<check value>)
84 eval CREATE TABLE t1 (
85 $column_list
86 )
87 PARTITION BY LIST(CAST(f_char1 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER))
88 (PARTITION p0 VALUES IN (0),
89 PARTITION p1 VALUES IN (NULL),
90 PARTITION p2 VALUES IN (CAST( 2147483646 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)),
91 PARTITION p3 VALUES IN (CAST(-2147483646 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)));
92 let $my_val= 2147483646;
93 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
94 VALUES($my_val,$my_val,'$my_val','$my_val','#$my_val#');
95 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '$my_val';
96 let $my_val= -2147483646;
97 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
98 VALUES($my_val,$my_val,'$my_val','$my_val','#$my_val#');
99 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '$my_val';
100 let $my_val= 0;
101 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
102 VALUES($my_val,$my_val,'$my_val','$my_val','#$my_val#');
103 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '$my_val';
104 # let $my_val= NULL;
105 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
106 VALUES(NULL,NULL,NULL,NULL,NULL);
107 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 IS NULL;
108 DROP TABLE t1;
109 #
110 --echo # 4. Partition by RANGE(...) subpartition by HASH(<check value>)
111 eval CREATE TABLE t1 (
112 $column_list
113 )
114 PARTITION BY RANGE(CAST(f_char1 AS SIGNED INTEGER))
115 SUBPARTITION BY HASH(CAST(f_char2 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)) SUBPARTITIONS 4
116 (PARTITION p0 VALUES LESS THAN (0),
117 PARTITION p1 VALUES LESS THAN MAXVALUE);
118 let $my_val= 2147483646;
119 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
120 VALUES($my_val,$my_val,'1','$my_val','#$my_val#');
121 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '$my_val';
122 let $my_val= -2147483646;
123 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
124 VALUES($my_val,$my_val,'-1','$my_val','#$my_val#');
125 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '$my_val';
126 let $my_val= 0;
127 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
128 VALUES($my_val,$my_val,'$my_val','$my_val','#$my_val#');
129 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '$my_val';
130 # let $my_val= NULL;
131 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
132 VALUES(NULL,NULL,NULL,NULL,NULL);
133 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 IS NULL;
134 DROP TABLE t1;
135 #
136 --echo # 5. Partition by LIST(...) subpartition by HASH(<check value>)
137 eval CREATE TABLE t1 (
138 $column_list
139 )
140 PARTITION BY LIST(CAST(f_char1 AS SIGNED INTEGER))
141 SUBPARTITION BY HASH(CAST(f_char2 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)) SUBPARTITIONS 4
142 (PARTITION p0 VALUES IN (NULL),
143 PARTITION p1 VALUES IN (1));
144 let $my_val= 2147483646;
145 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
146 VALUES($my_val,$my_val,'1','$my_val','#$my_val#');
147 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '$my_val';
148 let $my_val= -2147483646;
149 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
150 VALUES($my_val,$my_val,'1','$my_val','#$my_val#');
151 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '$my_val';
152 let $my_val= 0;
153 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
154 VALUES($my_val,$my_val,'1','$my_val','#$my_val#');
155 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '$my_val';
156 # let $my_val= NULL;
157 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
158 VALUES(NULL,NULL,NULL,NULL,NULL);
159 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 IS NULL;
160 DROP TABLE t1;
161 #
162
163 --enable_parsing