1-- source include/not_embedded.inc 2 3let $MYSQLD_DATADIR= `select @@datadir`; 4let $PORT= `select @@port`; 5--copy_file $MTR_SUITE_DIR/std_data/expenses.txt $MYSQLD_DATADIR/test/expenses.txt 6 7--echo # 8--echo # Testing the PIVOT table type 9--echo # 10CREATE TABLE expenses ( 11Who CHAR(10) NOT NULL, 12Week INT(2) NOT NULL, 13What CHAR(12) NOT NULL, 14Amount DOUBLE(8,2)) 15ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='expenses.txt' ENDING=2; 16SELECT * FROM expenses; 17 18--echo # 19--echo # Pivoting from What 20--echo # 21CREATE TABLE pivex ( 22Who CHAR(10) NOT NULL, 23Week INT(2) NOT NULL, 24Beer DOUBLE(8,2) FLAG=1, 25Car DOUBLE(8,2) FLAG=1, 26Food DOUBLE(8,2) FLAG=1) 27ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=expenses; 28--replace_result $PORT PORT 29--eval ALTER TABLE pivex OPTION_LIST='port=$PORT' 30SELECT * FROM pivex; 31 32--echo # 33--echo # Restricting the columns in a Pivot Table 34--echo # 35ALTER TABLE pivex DROP COLUMN week; 36SELECT * FROM pivex; 37 38--echo # 39--echo # Using a source definition 40--echo # 41DROP TABLE pivex; 42CREATE TABLE pivex ( 43Who CHAR(10) NOT NULL, 44Week INT(2) NOT NULL, 45Beer DOUBLE(8,2) FLAG=1, 46Car DOUBLE(8,2) FLAG=1, 47Food DOUBLE(8,2) FLAG=1) 48ENGINE=CONNECT TABLE_TYPE=PIVOT 49SRCDEF='select who, week, what, sum(amount) as amount from expenses where week in (4,5) group by who, week, what'; 50--replace_result $PORT PORT 51--eval ALTER TABLE pivex OPTION_LIST='PivotCol=what,FncCol=amount,port=$PORT' 52SELECT * FROM pivex; 53 54--echo # 55--echo # Pivoting from Week 56--echo # 57DROP TABLE pivex; 58CREATE TABLE pivex ( 59Who CHAR(10) NOT NULL, 60What CHAR(12) NOT NULL, 61`3` DOUBLE(8,2) FLAG=1, 62`4` DOUBLE(8,2) FLAG=1, 63`5` DOUBLE(8,2) FLAG=1) 64ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=expenses; 65--replace_result $PORT PORT 66--eval ALTER TABLE pivex OPTION_LIST='PivotCol=Week,port=$PORT' 67SELECT * FROM pivex; 68 69--echo # 70--echo # Using scalar functions and expresssions 71--echo # 72DROP TABLE pivex; 73CREATE TABLE pivex ( 74Who CHAR(10) NOT NULL, 75What CHAR(12) NOT NULL, 76First DOUBLE(8,2) FLAG=1, 77Middle DOUBLE(8,2) FLAG=1, 78Last DOUBLE(8,2) FLAG=1) 79ENGINE=CONNECT TABLE_TYPE=PIVOT 80SRCDEF='select who, what, case when week=3 then ''First'' when week=5 then ''Last'' else ''Middle'' end as wk, sum(amount) * 6.56 as amnt from expenses group by who, what, wk'; 81--replace_result $PORT PORT 82--eval ALTER TABLE pivex OPTION_LIST='PivotCol=wk,FncCol=amnt,port=$PORT' 83SELECT * FROM pivex; 84DROP TABLE pivex; 85DROP TABLE expenses; 86 87--echo # 88--echo # Make the PETS table 89--echo # 90CREATE TABLE pets ( 91Name VARCHAR(12) NOT NULL, 92Race CHAR(6) NOT NULL, 93Number INT NOT NULL) ENGINE=MYISAM; 94INSERT INTO pets VALUES('John','dog',2); 95INSERT INTO pets VALUES('Bill','cat',1); 96INSERT INTO pets VALUES('Mary','dog',1); 97INSERT INTO pets VALUES('Mary','cat',1); 98INSERT INTO pets VALUES('Lisbeth','rabbit',2); 99INSERT INTO pets VALUES('Kevin','cat',2); 100INSERT INTO pets VALUES('Kevin','bird',6); 101INSERT INTO pets VALUES('Donald','dog',1); 102INSERT INTO pets VALUES('Donald','fish',3); 103SELECT * FROM pets; 104 105--echo # 106--echo # Pivot the PETS table 107--echo # 108CREATE TABLE pivet ( 109name VARCHAR(12) NOT NULL, 110dog INT NOT NULL DEFAULT 0 FLAG=1, 111cat INT NOT NULL DEFAULT 0 FLAG=1, 112rabbit INT NOT NULL DEFAULT 0 FLAG=1, 113bird INT NOT NULL DEFAULT 0 FLAG=1, 114fish INT NOT NULL DEFAULT 0 FLAG=1) 115ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=pets OPTION_LIST='PivotCol=race,groupby=1'; 116SELECT * FROM pivet; 117DROP TABLE pivet; 118 119--echo # 120--echo # Testing the "data" column list 121--echo # 122CREATE TABLE pivet ( 123name VARCHAR(12) NOT NULL, 124dog INT NOT NULL DEFAULT 0 FLAG=1, 125cat INT NOT NULL DEFAULT 0 FLAG=1) 126ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=pets OPTION_LIST='PivotCol=race,groupby=1'; 127--error ER_GET_ERRMSG 128SELECT * FROM pivet; 129ALTER TABLE pivet OPTION_LIST='PivotCol=race,groupby=1,accept=1'; 130SELECT * FROM pivet; 131DROP TABLE pivet; 132 133--echo # 134--echo # Adding a "dump" column 135--echo # 136CREATE TABLE pivet ( 137name VARCHAR(12) NOT NULL, 138dog INT NOT NULL DEFAULT 0 FLAG=1, 139cat INT NOT NULL DEFAULT 0 FLAG=1, 140other INT NOT NULL DEFAULT 0 FLAG=2) 141ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=pets OPTION_LIST='PivotCol=race,groupby=1'; 142SELECT * FROM pivet; 143 144DROP TABLE pivet; 145DROP TABLE pets; 146 147--echo # 148--echo # MDEV-5734 149--echo # 150CREATE TABLE fruit ( 151 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 152 `name` varchar(32) NOT NULL, 153 `cnt` int(11) DEFAULT NULL, 154 PRIMARY KEY (`id`) 155) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; 156INSERT INTO fruit VALUES (1,'apple',1),(2,'banana',1),(3,'apple',2),(4,'cherry',4),(5,'durazno',2); 157SELECT * FROM fruit; 158CREATE TABLE fruit_pivot ENGINE=CONNECT TABLE_TYPE=pivot TABNAME=fruit; 159SELECT * FROM fruit_pivot; 160 161DROP TABLE fruit_pivot; 162DROP TABLE fruit; 163--remove_file $MYSQLD_DATADIR/test/expenses.txt 164