1SET @max_row = 20; 2SET @@session.storage_engine = 'MyISAM'; 3 4#------------------------------------------------------------------------ 5# There are several testcases disabled because of the open bugs 6# #15890 7# The expected results suffer from the following bugs 8# harmless #17455, #19305 9# which cannot be suppressed because of technical reasons. 10#------------------------------------------------------------------------ 11 12#------------------------------------------------------------------------ 13# 0. Setting of auxiliary variables + Creation of an auxiliary tables 14# needed in many testcases 15#------------------------------------------------------------------------ 16SELECT @max_row DIV 2 INTO @max_row_div2; 17SELECT @max_row DIV 3 INTO @max_row_div3; 18SELECT @max_row DIV 4 INTO @max_row_div4; 19SET @max_int_4 = 2147483647; 20DROP TABLE IF EXISTS t0_template; 21CREATE TABLE t0_template ( 22f_int1 INTEGER, 23f_int2 INTEGER, 24f_char1 CHAR(20), 25f_char2 CHAR(20), 26f_charbig VARCHAR(1000) , 27PRIMARY KEY(f_int1)) 28ENGINE = MEMORY; 29# Logging of <max_row> INSERTs into t0_template suppressed 30DROP TABLE IF EXISTS t0_definition; 31CREATE TABLE t0_definition ( 32state CHAR(3), 33create_command VARBINARY(5000), 34file_list VARBINARY(5000), 35PRIMARY KEY (state) 36) ENGINE = MEMORY; 37DROP TABLE IF EXISTS t0_aux; 38CREATE TABLE t0_aux ( f_int1 INTEGER, 39f_int2 INTEGER, 40f_char1 CHAR(20), 41f_char2 CHAR(20), 42f_charbig VARCHAR(1000) ) 43ENGINE = MEMORY; 44SET AUTOCOMMIT= 1; 45SET @@session.sql_mode= ''; 46# End of basic preparations needed for all tests 47#----------------------------------------------- 48 49#======================================================================== 50# Calculation of "exotic" results within the partition function 51# outside of SIGNED BIGINT value range, 0, NULL 52# column used in partitioning function has type CHAR 53#======================================================================== 54# 1. HASH(<check value>) 55DROP TABLE IF EXISTS t1; 56CREATE TABLE t1 ( 57f_int1 INTEGER, 58f_int2 INTEGER, 59f_char1 CHAR(20), 60f_char2 CHAR(20), 61f_charbig VARCHAR(1000) 62) 63PARTITION BY HASH(CAST(f_char1 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)) PARTITIONS 8; 64INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 65VALUES(2147483646,2147483646,'2147483646','2147483646','#2147483646#'); 66SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '2147483646'; 67COUNT(*) = 1 681 69INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 70VALUES(-2147483646,-2147483646,'-2147483646','-2147483646','#-2147483646#'); 71SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '-2147483646'; 72COUNT(*) = 1 731 74INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 75VALUES(0,0,'0','0','#0#'); 76SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '0'; 77COUNT(*) = 1 781 79INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 80VALUES(NULL,NULL,NULL,NULL,NULL); 81SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 IS NULL; 82COUNT(*) = 1 831 84DROP TABLE t1; 85# 2. RANGE(<check value>) 86CREATE TABLE t1 ( 87f_int1 INTEGER, 88f_int2 INTEGER, 89f_char1 CHAR(20), 90f_char2 CHAR(20), 91f_charbig VARCHAR(1000) 92) 93PARTITION BY RANGE(CAST(f_char1 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)) 94(PARTITION p0 VALUES LESS THAN (0), 95PARTITION p1 VALUES LESS THAN (1000000), 96PARTITION p2 VALUES LESS THAN MAXVALUE); 97INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 98VALUES(2147483646,2147483646,'2147483646','2147483646','#2147483646#'); 99SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '2147483646'; 100COUNT(*) = 1 1011 102INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 103VALUES(-2147483646,-2147483646,'-2147483646','-2147483646','#-2147483646#'); 104SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '-2147483646'; 105COUNT(*) = 1 1061 107INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 108VALUES(0,0,'0','0','#0#'); 109SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '0'; 110COUNT(*) = 1 1111 112INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 113VALUES(NULL,NULL,NULL,NULL,NULL); 114SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 IS NULL; 115COUNT(*) = 1 1161 117DROP TABLE t1; 118# 3. LIST(<check value>) 119CREATE TABLE t1 ( 120f_int1 INTEGER, 121f_int2 INTEGER, 122f_char1 CHAR(20), 123f_char2 CHAR(20), 124f_charbig VARCHAR(1000) 125) 126PARTITION BY LIST(CAST(f_char1 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)) 127(PARTITION p0 VALUES IN (0), 128PARTITION p1 VALUES IN (NULL), 129PARTITION p2 VALUES IN (CAST( 2147483646 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)), 130PARTITION p3 VALUES IN (CAST(-2147483646 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER))); 131INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 132VALUES(2147483646,2147483646,'2147483646','2147483646','#2147483646#'); 133SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '2147483646'; 134COUNT(*) = 1 1351 136INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 137VALUES(-2147483646,-2147483646,'-2147483646','-2147483646','#-2147483646#'); 138SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '-2147483646'; 139COUNT(*) = 1 1401 141INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 142VALUES(0,0,'0','0','#0#'); 143SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '0'; 144COUNT(*) = 1 1451 146INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 147VALUES(NULL,NULL,NULL,NULL,NULL); 148SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 IS NULL; 149COUNT(*) = 1 1501 151DROP TABLE t1; 152# 4. Partition by RANGE(...) subpartition by HASH(<check value>) 153CREATE TABLE t1 ( 154f_int1 INTEGER, 155f_int2 INTEGER, 156f_char1 CHAR(20), 157f_char2 CHAR(20), 158f_charbig VARCHAR(1000) 159) 160PARTITION BY RANGE(CAST(f_char1 AS SIGNED INTEGER)) 161SUBPARTITION BY HASH(CAST(f_char2 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)) SUBPARTITIONS 4 162(PARTITION p0 VALUES LESS THAN (0), 163PARTITION p1 VALUES LESS THAN MAXVALUE); 164INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 165VALUES(2147483646,2147483646,'1','2147483646','#2147483646#'); 166SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '2147483646'; 167COUNT(*) = 1 1681 169INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 170VALUES(-2147483646,-2147483646,'-1','-2147483646','#-2147483646#'); 171SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '-2147483646'; 172COUNT(*) = 1 1731 174INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 175VALUES(0,0,'0','0','#0#'); 176SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '0'; 177COUNT(*) = 1 1781 179INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 180VALUES(NULL,NULL,NULL,NULL,NULL); 181SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 IS NULL; 182COUNT(*) = 1 1831 184DROP TABLE t1; 185# 5. Partition by LIST(...) subpartition by HASH(<check value>) 186CREATE TABLE t1 ( 187f_int1 INTEGER, 188f_int2 INTEGER, 189f_char1 CHAR(20), 190f_char2 CHAR(20), 191f_charbig VARCHAR(1000) 192) 193PARTITION BY LIST(CAST(f_char1 AS SIGNED INTEGER)) 194SUBPARTITION BY HASH(CAST(f_char2 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)) SUBPARTITIONS 4 195(PARTITION p0 VALUES IN (NULL), 196PARTITION p1 VALUES IN (1)); 197INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 198VALUES(2147483646,2147483646,'1','2147483646','#2147483646#'); 199SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '2147483646'; 200COUNT(*) = 1 2011 202INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 203VALUES(-2147483646,-2147483646,'1','-2147483646','#-2147483646#'); 204SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '-2147483646'; 205COUNT(*) = 1 2061 207INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 208VALUES(0,0,'1','0','#0#'); 209SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '0'; 210COUNT(*) = 1 2111 212INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 213VALUES(NULL,NULL,NULL,NULL,NULL); 214SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 IS NULL; 215COUNT(*) = 1 2161 217DROP TABLE t1; 218DROP VIEW IF EXISTS v1; 219DROP TABLE IF EXISTS t1; 220DROP TABLE IF EXISTS t0_aux; 221DROP TABLE IF EXISTS t0_definition; 222DROP TABLE IF EXISTS t0_template; 223