diff options
Diffstat (limited to 'mysql-test/main/alter_table_mdev539_myisam.result')
| -rw-r--r-- | mysql-test/main/alter_table_mdev539_myisam.result | 253 |
1 files changed, 253 insertions, 0 deletions
diff --git a/mysql-test/main/alter_table_mdev539_myisam.result b/mysql-test/main/alter_table_mdev539_myisam.result new file mode 100644 index 00000000000..0339a64c9c7 --- /dev/null +++ b/mysql-test/main/alter_table_mdev539_myisam.result @@ -0,0 +1,253 @@ +# +set @@storage_engine= MyISAM; +# +# mdev-539: fast build of unique/primary indexes for MyISAM/Aria +# +call mtr.add_suppression("Can't find record in '.*'"); +DROP DATABASE IF EXISTS dbt3_s001; +CREATE DATABASE dbt3_s001; +use dbt3_s001; +drop index `primary` on lineitem; +show create table lineitem; +Table Create Table +lineitem CREATE TABLE `lineitem` ( + `l_orderkey` int(11) NOT NULL DEFAULT 0, + `l_partkey` int(11) DEFAULT NULL, + `l_suppkey` int(11) DEFAULT NULL, + `l_linenumber` int(11) NOT NULL DEFAULT 0, + `l_quantity` double DEFAULT NULL, + `l_extendedprice` double DEFAULT NULL, + `l_discount` double DEFAULT NULL, + `l_tax` double DEFAULT NULL, + `l_returnflag` char(1) DEFAULT NULL, + `l_linestatus` char(1) DEFAULT NULL, + `l_shipDATE` date DEFAULT NULL, + `l_commitDATE` date DEFAULT NULL, + `l_receiptDATE` date DEFAULT NULL, + `l_shipinstruct` char(25) DEFAULT NULL, + `l_shipmode` char(10) DEFAULT NULL, + `l_comment` varchar(44) DEFAULT NULL, + KEY `i_l_shipdate` (`l_shipDATE`), + KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`), + KEY `i_l_partkey` (`l_partkey`), + KEY `i_l_suppkey` (`l_suppkey`), + KEY `i_l_receiptdate` (`l_receiptDATE`), + KEY `i_l_orderkey` (`l_orderkey`), + KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`), + KEY `i_l_commitdate` (`l_commitDATE`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table lineitem add primary key (l_orderkey, l_linenumber); +show create table lineitem; +Table Create Table +lineitem CREATE TABLE `lineitem` ( + `l_orderkey` int(11) NOT NULL DEFAULT 0, + `l_partkey` int(11) DEFAULT NULL, + `l_suppkey` int(11) DEFAULT NULL, + `l_linenumber` int(11) NOT NULL DEFAULT 0, + `l_quantity` double DEFAULT NULL, + `l_extendedprice` double DEFAULT NULL, + `l_discount` double DEFAULT NULL, + `l_tax` double DEFAULT NULL, + `l_returnflag` char(1) DEFAULT NULL, + `l_linestatus` char(1) DEFAULT NULL, + `l_shipDATE` date DEFAULT NULL, + `l_commitDATE` date DEFAULT NULL, + `l_receiptDATE` date DEFAULT NULL, + `l_shipinstruct` char(25) DEFAULT NULL, + `l_shipmode` char(10) DEFAULT NULL, + `l_comment` varchar(44) DEFAULT NULL, + PRIMARY KEY (`l_orderkey`,`l_linenumber`), + KEY `i_l_shipdate` (`l_shipDATE`), + KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`), + KEY `i_l_partkey` (`l_partkey`), + KEY `i_l_suppkey` (`l_suppkey`), + KEY `i_l_receiptdate` (`l_receiptDATE`), + KEY `i_l_orderkey` (`l_orderkey`), + KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`), + KEY `i_l_commitdate` (`l_commitDATE`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop index `primary` on lineitem; +select * from lineitem where l_orderkey=1 and l_linenumber=2; +l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipDATE l_commitDATE l_receiptDATE l_shipinstruct l_shipmode l_comment +1 68 9 2 36 34850.16 0.09 0.06 N O 1996-04-12 1996-02-28 1996-04-20 TAKE BACK RETURN MAIL slyly bold pinto beans detect s +insert into lineitem values +(1,68,9,2,36,34850.16,0.07,0.06,'N','O','1996-04-12','1996-02-28','1996-04-20','TAKE BACK RETURN','MAIL','slyly bold pinto beans detect s'); +select * from lineitem where l_orderkey=1 and l_linenumber=2; +l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipDATE l_commitDATE l_receiptDATE l_shipinstruct l_shipmode l_comment +1 68 9 2 36 34850.16 0.09 0.06 N O 1996-04-12 1996-02-28 1996-04-20 TAKE BACK RETURN MAIL slyly bold pinto beans detect s +1 68 9 2 36 34850.16 0.07 0.06 N O 1996-04-12 1996-02-28 1996-04-20 TAKE BACK RETURN MAIL slyly bold pinto beans detect s +alter table lineitem add primary key (l_orderkey, l_linenumber); +ERROR 23000: Duplicate entry '1-2' for key 'PRIMARY' +show create table lineitem; +Table Create Table +lineitem CREATE TABLE `lineitem` ( + `l_orderkey` int(11) NOT NULL DEFAULT 0, + `l_partkey` int(11) DEFAULT NULL, + `l_suppkey` int(11) DEFAULT NULL, + `l_linenumber` int(11) NOT NULL DEFAULT 0, + `l_quantity` double DEFAULT NULL, + `l_extendedprice` double DEFAULT NULL, + `l_discount` double DEFAULT NULL, + `l_tax` double DEFAULT NULL, + `l_returnflag` char(1) DEFAULT NULL, + `l_linestatus` char(1) DEFAULT NULL, + `l_shipDATE` date DEFAULT NULL, + `l_commitDATE` date DEFAULT NULL, + `l_receiptDATE` date DEFAULT NULL, + `l_shipinstruct` char(25) DEFAULT NULL, + `l_shipmode` char(10) DEFAULT NULL, + `l_comment` varchar(44) DEFAULT NULL, + KEY `i_l_shipdate` (`l_shipDATE`), + KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`), + KEY `i_l_partkey` (`l_partkey`), + KEY `i_l_suppkey` (`l_suppkey`), + KEY `i_l_receiptdate` (`l_receiptDATE`), + KEY `i_l_orderkey` (`l_orderkey`), + KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`), + KEY `i_l_commitdate` (`l_commitDATE`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from lineitem where l_orderkey=1 and l_linenumber=2; +l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipDATE l_commitDATE l_receiptDATE l_shipinstruct l_shipmode l_comment +1 68 9 2 36 34850.16 0.09 0.06 N O 1996-04-12 1996-02-28 1996-04-20 TAKE BACK RETURN MAIL slyly bold pinto beans detect s +1 68 9 2 36 34850.16 0.07 0.06 N O 1996-04-12 1996-02-28 1996-04-20 TAKE BACK RETURN MAIL slyly bold pinto beans detect s +delete from lineitem where l_orderkey=1 and l_linenumber=2 and l_discount=0.07; +alter table lineitem add primary key (l_orderkey, l_linenumber); +show create table lineitem; +Table Create Table +lineitem CREATE TABLE `lineitem` ( + `l_orderkey` int(11) NOT NULL DEFAULT 0, + `l_partkey` int(11) DEFAULT NULL, + `l_suppkey` int(11) DEFAULT NULL, + `l_linenumber` int(11) NOT NULL DEFAULT 0, + `l_quantity` double DEFAULT NULL, + `l_extendedprice` double DEFAULT NULL, + `l_discount` double DEFAULT NULL, + `l_tax` double DEFAULT NULL, + `l_returnflag` char(1) DEFAULT NULL, + `l_linestatus` char(1) DEFAULT NULL, + `l_shipDATE` date DEFAULT NULL, + `l_commitDATE` date DEFAULT NULL, + `l_receiptDATE` date DEFAULT NULL, + `l_shipinstruct` char(25) DEFAULT NULL, + `l_shipmode` char(10) DEFAULT NULL, + `l_comment` varchar(44) DEFAULT NULL, + PRIMARY KEY (`l_orderkey`,`l_linenumber`), + KEY `i_l_shipdate` (`l_shipDATE`), + KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`), + KEY `i_l_partkey` (`l_partkey`), + KEY `i_l_suppkey` (`l_suppkey`), + KEY `i_l_receiptdate` (`l_receiptDATE`), + KEY `i_l_orderkey` (`l_orderkey`), + KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`), + KEY `i_l_commitdate` (`l_commitDATE`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from lineitem where l_orderkey=1 and l_linenumber=2; +l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipDATE l_commitDATE l_receiptDATE l_shipinstruct l_shipmode l_comment +1 68 9 2 36 34850.16 0.09 0.06 N O 1996-04-12 1996-02-28 1996-04-20 TAKE BACK RETURN MAIL slyly bold pinto beans detect s +create unique index i_c_name on customer(c_name); +show create table customer; +Table Create Table +customer CREATE TABLE `customer` ( + `c_custkey` int(11) NOT NULL, + `c_name` varchar(25) DEFAULT NULL, + `c_address` varchar(40) DEFAULT NULL, + `c_nationkey` int(11) DEFAULT NULL, + `c_phone` char(15) DEFAULT NULL, + `c_acctbal` double DEFAULT NULL, + `c_mktsegment` char(10) DEFAULT NULL, + `c_comment` varchar(117) DEFAULT NULL, + PRIMARY KEY (`c_custkey`), + UNIQUE KEY `i_c_name` (`c_name`), + KEY `i_c_nationkey` (`c_nationkey`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from customer where c_name='Customer#000000003'; +c_custkey c_name c_address c_nationkey c_phone c_acctbal c_mktsegment c_comment +3 Customer#000000003 MG9kdTD2WBHm 1 11-719-748-3364 7498.12 AUTOMOBILE special packages wake. slyly reg +drop index i_c_name on customer; +insert into customer values +(303,'Customer#000000003','MG9kdTD2WBHm',1,'11-719-748-3364',7498.12,'AUTOMOBILE','special packages wake. slyly reg'); +select * from customer where c_name='Customer#000000003'; +c_custkey c_name c_address c_nationkey c_phone c_acctbal c_mktsegment c_comment +3 Customer#000000003 MG9kdTD2WBHm 1 11-719-748-3364 7498.12 AUTOMOBILE special packages wake. slyly reg +303 Customer#000000003 MG9kdTD2WBHm 1 11-719-748-3364 7498.12 AUTOMOBILE special packages wake. slyly reg +alter table customer add unique index i_c_name(c_name); +ERROR 23000: Duplicate entry 'Customer#000000003' for key 'i_c_name' +show create table customer; +Table Create Table +customer CREATE TABLE `customer` ( + `c_custkey` int(11) NOT NULL, + `c_name` varchar(25) DEFAULT NULL, + `c_address` varchar(40) DEFAULT NULL, + `c_nationkey` int(11) DEFAULT NULL, + `c_phone` char(15) DEFAULT NULL, + `c_acctbal` double DEFAULT NULL, + `c_mktsegment` char(10) DEFAULT NULL, + `c_comment` varchar(117) DEFAULT NULL, + PRIMARY KEY (`c_custkey`), + KEY `i_c_nationkey` (`c_nationkey`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from customer where c_name='Customer#000000003'; +c_custkey c_name c_address c_nationkey c_phone c_acctbal c_mktsegment c_comment +3 Customer#000000003 MG9kdTD2WBHm 1 11-719-748-3364 7498.12 AUTOMOBILE special packages wake. slyly reg +303 Customer#000000003 MG9kdTD2WBHm 1 11-719-748-3364 7498.12 AUTOMOBILE special packages wake. slyly reg +delete from customer where c_custkey=303; +select * from customer where c_name='Customer#000000003'; +c_custkey c_name c_address c_nationkey c_phone c_acctbal c_mktsegment c_comment +3 Customer#000000003 MG9kdTD2WBHm 1 11-719-748-3364 7498.12 AUTOMOBILE special packages wake. slyly reg +alter table customer add unique index i_c_name(c_name); +show create table customer; +Table Create Table +customer CREATE TABLE `customer` ( + `c_custkey` int(11) NOT NULL, + `c_name` varchar(25) DEFAULT NULL, + `c_address` varchar(40) DEFAULT NULL, + `c_nationkey` int(11) DEFAULT NULL, + `c_phone` char(15) DEFAULT NULL, + `c_acctbal` double DEFAULT NULL, + `c_mktsegment` char(10) DEFAULT NULL, + `c_comment` varchar(117) DEFAULT NULL, + PRIMARY KEY (`c_custkey`), + UNIQUE KEY `i_c_name` (`c_name`), + KEY `i_c_nationkey` (`c_nationkey`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from customer where c_name='Customer#000000003'; +c_custkey c_name c_address c_nationkey c_phone c_acctbal c_mktsegment c_comment +3 Customer#000000003 MG9kdTD2WBHm 1 11-719-748-3364 7498.12 AUTOMOBILE special packages wake. slyly reg +drop index `primary` on customer; +show create table customer; +Table Create Table +customer CREATE TABLE `customer` ( + `c_custkey` int(11) NOT NULL, + `c_name` varchar(25) DEFAULT NULL, + `c_address` varchar(40) DEFAULT NULL, + `c_nationkey` int(11) DEFAULT NULL, + `c_phone` char(15) DEFAULT NULL, + `c_acctbal` double DEFAULT NULL, + `c_mktsegment` char(10) DEFAULT NULL, + `c_comment` varchar(117) DEFAULT NULL, + UNIQUE KEY `i_c_name` (`c_name`), + KEY `i_c_nationkey` (`c_nationkey`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into customer values +(3,'Customer#000000303','MG9kdTD2WBHm',1,'11-719-748-3364',7498.12,'AUTOMOBILE','special packages wake. slyly reg'); +alter ignore table customer add primary key (c_custkey); +show create table customer; +Table Create Table +customer CREATE TABLE `customer` ( + `c_custkey` int(11) NOT NULL, + `c_name` varchar(25) DEFAULT NULL, + `c_address` varchar(40) DEFAULT NULL, + `c_nationkey` int(11) DEFAULT NULL, + `c_phone` char(15) DEFAULT NULL, + `c_acctbal` double DEFAULT NULL, + `c_mktsegment` char(10) DEFAULT NULL, + `c_comment` varchar(117) DEFAULT NULL, + PRIMARY KEY (`c_custkey`), + UNIQUE KEY `i_c_name` (`c_name`), + KEY `i_c_nationkey` (`c_nationkey`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from customer where c_custkey=3; +c_custkey c_name c_address c_nationkey c_phone c_acctbal c_mktsegment c_comment +3 Customer#000000003 MG9kdTD2WBHm 1 11-719-748-3364 7498.12 AUTOMOBILE special packages wake. slyly reg +DROP DATABASE dbt3_s001; +set @@storage_engine= default; |