MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
partition_value.inc
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 #
84 # The NDB handler only supports 32 bit integers in VALUES
85 # therefor we have to skip the next test for NDB.
86 if (`SELECT @@session.default_storage_engine NOT IN('ndbcluster')`)
87 {
88 --echo # 3. LIST(<check value>)
89 eval CREATE TABLE t1 (
90 $column_list
91 )
92 PARTITION BY LIST(CAST(f_char1 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER))
93 (PARTITION p0 VALUES IN (0),
94  PARTITION p1 VALUES IN (NULL),
95  PARTITION p2 VALUES IN (CAST( 2147483646 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)),
96  PARTITION p3 VALUES IN (CAST(-2147483646 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)));
97 let $my_val= 2147483646;
98 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
99 VALUES($my_val,$my_val,'$my_val','$my_val','#$my_val#');
100 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '$my_val';
101 let $my_val= -2147483646;
102 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
103 VALUES($my_val,$my_val,'$my_val','$my_val','#$my_val#');
104 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '$my_val';
105 let $my_val= 0;
106 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
107 VALUES($my_val,$my_val,'$my_val','$my_val','#$my_val#');
108 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '$my_val';
109 # let $my_val= NULL;
110 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
111 VALUES(NULL,NULL,NULL,NULL,NULL);
112 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 IS NULL;
113 DROP TABLE t1;
114 }
115 #
116 --echo # 4. Partition by RANGE(...) subpartition by HASH(<check value>)
117 eval CREATE TABLE t1 (
118 $column_list
119 )
120 PARTITION BY RANGE(CAST(f_char1 AS SIGNED INTEGER))
121 SUBPARTITION BY HASH(CAST(f_char2 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)) SUBPARTITIONS 4
122 (PARTITION p0 VALUES LESS THAN (0),
123  PARTITION p1 VALUES LESS THAN MAXVALUE);
124 let $my_val= 2147483646;
125 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
126 VALUES($my_val,$my_val,'1','$my_val','#$my_val#');
127 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '$my_val';
128 let $my_val= -2147483646;
129 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
130 VALUES($my_val,$my_val,'-1','$my_val','#$my_val#');
131 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '$my_val';
132 let $my_val= 0;
133 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
134 VALUES($my_val,$my_val,'$my_val','$my_val','#$my_val#');
135 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '$my_val';
136 # let $my_val= NULL;
137 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
138 VALUES(NULL,NULL,NULL,NULL,NULL);
139 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 IS NULL;
140 DROP TABLE t1;
141 #
142 --echo # 5. Partition by LIST(...) subpartition by HASH(<check value>)
143 eval CREATE TABLE t1 (
144 $column_list
145 )
146 PARTITION BY LIST(CAST(f_char1 AS SIGNED INTEGER))
147 SUBPARTITION BY HASH(CAST(f_char2 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)) SUBPARTITIONS 4
148 (PARTITION p0 VALUES IN (NULL),
149  PARTITION p1 VALUES IN (1));
150 let $my_val= 2147483646;
151 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
152 VALUES($my_val,$my_val,'1','$my_val','#$my_val#');
153 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '$my_val';
154 let $my_val= -2147483646;
155 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
156 VALUES($my_val,$my_val,'1','$my_val','#$my_val#');
157 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '$my_val';
158 let $my_val= 0;
159 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
160 VALUES($my_val,$my_val,'1','$my_val','#$my_val#');
161 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '$my_val';
162 # let $my_val= NULL;
163 eval INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
164 VALUES(NULL,NULL,NULL,NULL,NULL);
165 eval SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 IS NULL;
166 DROP TABLE t1;
167 #
168 
169 --enable_parsing