~drizzle-trunk/drizzle/development

delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null);

736

select * from t1;

737

select * from t2;

738

select * from t2;

739

drop table t1,t2;

740

741

695

742

# A simple test with some isolation levels

696

743

# TODO: Make this into a test using replication to really test how

697

744

# this works.

720

767

DROP TABLE t1;

721

768

722

769

770

# Test of multi-table-update

771

772

eval create $temp table t1 (n int, d int) engine=$engine_type;

773

eval create $temp table t2 (n int, d int) engine=$engine_type;

774

insert into t1 values(1,1),(1,2);

775

insert into t2 values(1,10),(2,20);

776

UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;

777

select * from t1;

778

select * from t2;

779

drop table t1,t2;

780

781

723

782

# Testing of IFNULL

724

783

725

784

if(!$using_temp)

735

794

t2.b=t3.a order by 1;

736

795

drop table t1;

737

796

}

797

798

# Test of read_through not existing const_table

799

800

801

eval create $temp table t1 (a varchar(10) not null) engine = $other_engine_type;

802

eval create $temp table t2 (b varchar(10) not null unique) engine=$engine_type;

803

select t1.a from t1,t2 where t1.a=t2.b;

804

drop table t1,t2;

805

eval create $temp table t1 (a int not null, b int, primary key (a)) engine = $engine_type;

806

eval create $temp table t2 (a int not null, b int, primary key (a)) engine = $engine_type;

807

insert into t1 values (10, 20);

808

insert into t2 values (10, 20);

809

update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;

810

drop table t1,t2;

738

811

739

812

if ($test_foreign_keys)

740

813

{

809

882

drop table t1;

810

883

811

884

885

# Test multi update with different join methods

886

887

888

eval create $temp table t1 (a int not null primary key, b int not null, key (b)) engine=$engine_type;

889

eval create $temp table t2 (a int not null primary key, b int not null, key (b)) engine=$engine_type;

890

INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12);

891

INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

892

893

# Full join, without key

894

update t1,t2 set t1.a=t1.a+100;

895

select * from t1;

896

897

# unique key

898

update t1,t2 set t1.a=t1.a+100 where t1.a=101;

899

select * from t1;

900

901

# ref key

902

update t1,t2 set t1.b=t1.b+10 where t1.b=2;

903

select * from t1;

904

905

# Range key (in t1)

906

update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100;

907

select * from t1;

908

select * from t2;

909

910

drop table t1,t2;

911

eval create $temp table t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=$other_non_trans_engine_type;

912

eval create $temp table t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=$engine_type;

913

SET AUTOCOMMIT=0;

914

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

915

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

916

ROLLBACK;

917

SELECT * FROM t1;

918

drop table t1,t2;

919

eval create $temp table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = $engine_type;

920

insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);

921

select distinct parent,child from t1 order by parent;

922

drop table t1;

923

924

812

925

# Test that MySQL priorities clustered indexes

813

926

814

927

eval create $temp table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=$engine_type;

862

975

drop table t1;

863

976

864

977

978

# Test of multi-table-updates (bug #1980).

979

980

981

eval create $temp table t1 ( c char(8) not null ) engine=$engine_type;

982

insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');

983

insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

984

985

alter table t1 add b char(8) not null;

986

alter table t1 add a char(8) not null;

987

alter table t1 add primary key (a,b,c);

988

update t1 set a=c, b=c;

989

990

eval create $temp table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=$engine_type;

991

insert into t2 select * from t1;

992

993

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;

994

drop table t1,t2;

995

996

865

997

# test autoincrement with TRUNCATE

866

998

867

999

895

1027

insert into `t2`values ( 1 ) ;

896

1028

eval create $temp table `t3` (`id` int not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = $engine_type;

897

1029

insert into `t3`values ( 1 ) ;

898

--error ER_ROW_IS_REFERENCED_2

1030

--error 1451

899

1031

delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

900

--error ER_ROW_IS_REFERENCED_2

1032

--error 1451

901

1033

update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

902

--error ER_BAD_FIELD_ERROR

1034

--error 1054

903

1035

update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

904

1036

drop table t3,t2,t1;

905

1037

913

1045

foreign key(pid) references t1(id) on delete cascade) engine=$engine_type;

914

1046

insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),

915

1047

(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);

916

--error ER_ROW_IS_REFERENCED_2

1048

-- error 1451

917

1049

delete from t1 where id=0;

918

1050

delete from t1 where id=15;

919

1051

delete from t1 where id=0;

930

1062

eval create $temp table t2 (col1 int,stamp TIMESTAMP,INDEX stamp_idx

931

1063

(stamp))ENGINE=$engine_type;

932

1064

insert into t1 values (1),(2),(3);

933

insert into t2 values (1, 20020204130000);

934

insert into t2 values (2, 20020204130000);

935

--error ER_INVALID_TIMESTAMP_VALUE # Bad timestamp

936

insert into t2 values (4, 20020204310000);

937

insert into t2 values (5, 20020204230000);

938

SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp < '20020204120000' GROUP BY col1;

1065

# Note that timestamp 3 is wrong

1066

--error 1685 # Bad timestamp

1067

insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);

1068

SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <

1069

'20020204120000' GROUP BY col1;

939

1070

drop table t1,t2;

940

1071

941

1072

973

1104

}

974

1105

975

1106

# Live checksum feature available + enabled

976

eval create $temp table t1 (a int, b varchar(200), c text not null) engine=$other_live_chks_engine_type;

1107

eval create $temp table t1 (a int, b varchar(200), c text not null) checksum=1 engine=$other_live_chks_engine_type;

977

1108

# Live checksum feature available + disabled

978

eval create $temp table t2 (a int, b varchar(200), c text not null) engine=$other_live_chks_engine_type;

1109

eval create $temp table t2 (a int, b varchar(200), c text not null) checksum=0 engine=$other_live_chks_engine_type;

979

1110

980

1111

# Live checksum feature not available + enabled

981

eval create $temp table t3 (a int, b varchar(200), c varchar(200) not null) engine=$other_non_live_chks_engine_type;

1112

eval create $temp table t3 (a int, b varchar(200), c varchar(200) not null) checksum=1 engine=$other_non_live_chks_engine_type;

982

1113

# Live checksum feature not available + disabled

983

eval create $temp table t4 (a int, b varchar(200), c varchar(200) not null) engine=$other_non_live_chks_engine_type;

1114

eval create $temp table t4 (a int, b varchar(200), c varchar(200) not null) checksum=0 engine=$other_non_live_chks_engine_type;

984

1115

985

1116

# Live checksum feature probably available + enabled

986

eval create $temp table t5 (a int, b varchar(200), c text not null) engine=$engine_type;

1117

eval create $temp table t5 (a int, b varchar(200), c text not null) checksum=1 engine=$engine_type;

987

1118

# Live checksum feature probably available + disabled

988

eval create $temp table t6 (a int, b varchar(200), c text not null) engine=$engine_type;

1119

eval create $temp table t6 (a int, b varchar(200), c text not null) checksum=0 engine=$engine_type;

989

1120

990

1121

insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");

991

1122

insert t2 select * from t1;

993

1124

insert t4 select * from t1;

994

1125

insert t5 select * from t1;

995

1126

insert t6 select * from t1;

1127

checksum table t1, t2, t3, t4, t5, t6, t7 quick;

1128

checksum table t1, t2, t3, t4, t5, t6, t7;

1129

checksum table t1, t2, t3, t4, t5, t6, t7 extended;

996

1130

# #show table status;

997

1131

drop table t1,t2,t3, t4, t5, t6;

998

1132

1011

1145

# Bug#2160: Extra error message for create $temp table LIKE with InnoDB

1012

1146

1013

1147

eval create $temp table t1 (a int) engine=$engine_type;

1014

--error ER_CANT_CREATE_TABLE

1015

1148

create table t2 like t1;

1016

create table t2 like t1 engine=innodb;

1017

1149

show create table t2;

1018

1150

drop table t1,t2;

1019

1151

1032

1164

create index id2 on t2 (id);

1033

1165

show create table t2;

1034

1166

drop index id2 on t2;

1035

--error ER_ERROR_ON_RENAME, ER_ERROR_ON_RENAME

1167

--error 1025,1025

1036

1168

drop index id on t2;

1037

1169

show create table t2;

1038

1170

drop table t2;

1071

1203

# Clean up filename -- embedded server reports whole path without .frm,

1072

1204

# regular server reports relative path with .frm (argh!)

1073

1205

--replace_result \\ / $DRIZZLE_TEST_DIR . /var/master-data/ / t2.frm t2

1074

--error ER_CANT_CREATE_TABLE

1206

--error 1005

1075

1207

eval create $temp table t2 (id int not null, id2 int not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = $engine_type;

1076

1208

1077

1209

# bug#3749

1124

1256

1125

1257

# Bug #6126: Duplicate columns in keys gives misleading error message

1126

1258

1127

--error ER_DUP_FIELDNAME

1259

--error 1060

1128

1260

eval create $temp table t1 (c char(10), index (c,c)) engine=$engine_type;

1129

--error ER_DUP_FIELDNAME

1261

--error 1060

1130

1262

eval create $temp table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=$engine_type;

1131

--error ER_DUP_FIELDNAME

1263

--error 1060

1132

1264

eval create $temp table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=$engine_type;

1133

--error ER_DUP_FIELDNAME

1265

--error 1060

1134

1266

eval create $temp table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=$engine_type;

1135

1267

eval create $temp table t1 (c1 char(10), c2 char(10)) engine=$engine_type;

1136

--error ER_DUP_FIELDNAME

1268

--error 1060

1137

1269

alter table t1 add key (c1,c1);

1138

--error ER_DUP_FIELDNAME

1270

--error 1060

1139

1271

alter table t1 add key (c2,c1,c1);

1140

--error ER_DUP_FIELDNAME

1272

--error 1060

1141

1273

alter table t1 add key (c1,c2,c1);

1142

--error ER_DUP_FIELDNAME

1274

--error 1060

1143

1275

alter table t1 add key (c1,c1,c2);

1144

1276

drop table t1;

1145

1277

1160

1292

1161

1293

1162

1294

eval create $temp table t1 ( a char(10) ) ENGINE=$engine_type;

1163

--error ER_TABLE_CANT_HANDLE_FT

1295

--error 1214

1164

1296

SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);

1165

1297

DROP TABLE t1;

1166

1298

}

1229

1361

1230

1362

eval create $temp table t1 (x bigint not null primary key) engine=$engine_type;

1231

1363

# The below is out of range for a BIGINT (signed)

1232

--error ER_WARN_DATA_OUT_OF_RANGE

1364

--error 1264

1233

1365

insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);

1234

1366

select * from t1;

1235

1367

select count(*) from t1 where x>0;

1399

1531

SELECT GRADE FROM t1 WHERE GRADE= 151;

1400

1532

DROP TABLE t1;

1401

1533

1534

1535

# Bug #12340 multitable delete deletes only one record

1536

1537

eval create $temp table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=$engine_type;

1538

eval create $temp table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=$engine_type;

1539

insert into t2 values ('aa','cc');

1540

insert into t1 values ('aa','bb'),('aa','cc');

1541

delete t1 from t1,t2 where f1=f3 and f4='cc';

1542

select * from t1;

1543

drop table t1,t2;

1544

1402

1545

if ($test_foreign_keys)

1403

1546

{

1404

1547

1435

1578

id INT PRIMARY KEY

1436

1579

) ENGINE=$engine_type;

1437

1580

1438

--error ER_CANT_CREATE_TABLE, ER_CANT_CREATE_TABLE

1581

--error 1005,1005

1439

1582

eval CREATE TEMPORARY TABLE t2

1440

1583

(

1441

1584

id INT NOT NULL PRIMARY KEY,

1509

1652

{

1510

1653

1511

1654

# these should be refused

1512

--error ER_TOO_LONG_KEY

1655

--error 1071

1513

1656

eval create $temp table t1 (col1 varchar(768) primary key)

1514

1657

character set = latin1 engine = $engine_type;

1515

--error ER_TOO_LONG_KEY

1658

--error 1071

1516

1659

eval create $temp table t2 (col1 varbinary(768) primary key)

1517

1660

character set = latin1 engine = $engine_type;

1518

--error ER_TOO_LONG_KEY

1661

--error 1071

1519

1662

eval create $temp table t3 (col1 text, primary key(col1(768)))

1520

1663

character set = latin1 engine = $engine_type;

1521

--error ER_TOO_LONG_KEY

1664

--error 1071

1522

1665

eval create $temp table t4 (col1 blob, primary key(col1(768)))

1523

1666

character set = latin1 engine = $engine_type;

1524

1667

1541

1684

CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)

1542

1685

) ENGINE=$engine_type;

1543

1686

1544

--error ER_NO_REFERENCED_ROW_2

1687

--error 1452

1545

1688

INSERT INTO t2 VALUES(2);

1546

1689

1547

1690

INSERT INTO t1 VALUES(1);

1548

1691

INSERT INTO t2 VALUES(1);

1549

1692

1550

--error ER_ROW_IS_REFERENCED_2

1693

--error 1451

1551

1694

DELETE FROM t1 WHERE id = 1;

1552

1695

1553

--error ER_ROW_IS_REFERENCED

1696

--error 1217

1554

1697

DROP TABLE t1;

1555

1698

1556

1699

SET FOREIGN_KEY_CHECKS=0;

1557

1700

DROP TABLE t1;

1558

1701

SET FOREIGN_KEY_CHECKS=1;

1559

1702

1560

--error ER_NO_REFERENCED_ROW_2

1703

--error 1452

1561

1704

INSERT INTO t2 VALUES(3);

1562

1705

1563

1706

DROP TABLE t2;

1564

1707

}

1565

1708

# End of FOREIGN tests

1566

1709

1710

if ($test_transactions)

1711

{

1712

1713

# Test that checksum table uses a consistent read Bug #12669

1714

1715

connect (a,localhost,root,,);

1716

connect (b,localhost,root,,);

1717

connection a;

1718

eval create $temp table t1(a int not null) engine=$engine_type DEFAULT CHARSET=latin1;

1719

insert into t1 values (1),(2);

1720

set autocommit=0;

1721

checksum table t1;

1722

connection b;

1723

insert into t1 values(3);

1724

connection a;

1725

1726

# Here checksum should not see insert

1727

1728

checksum table t1;

1729

connection a;

1730

commit;

1731

checksum table t1;

1732

commit;

1733

drop table t1;

1734

1735

# autocommit = 1

1736

1737

connection a;

1738

eval create $temp table t1(a int not null) engine=$engine_type DEFAULT CHARSET=latin1;

1739

insert into t1 values (1),(2);

1740

set autocommit=1;

1741

checksum table t1;

1742

connection b;

1743

set autocommit=1;

1744

insert into t1 values(3);

1745

connection a;

1746

1747

# Here checksum sees insert

1748

1749

checksum table t1;

1750

drop table t1;

1751

1752

connection default;

1753

disconnect a;

1754

disconnect b;

1755

}

1756

1567

1757

# tests for bugs #9802 and #13778

1568

1758

1569

1759

if ($test_foreign_keys)

1573

1763

set foreign_key_checks=0;

1574

1764

eval create $temp table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = $engine_type;

1575

1765

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1576

--error ER_CANT_CREATE_TABLE

1766

-- error 1005

1577

1767

eval create $temp table t1(a char(10) primary key, b varchar(20)) engine = $engine_type;

1578

1768

set foreign_key_checks=1;

1579

1769

drop table t2;

1584

1774

set foreign_key_checks=0;

1585

1775

eval create $temp table t1(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=latin1;

1586

1776

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1587

--error ER_CANT_CREATE_TABLE

1777

-- error 1005

1588

1778

eval create $temp table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=utf8;

1589

1779

set foreign_key_checks=1;

1590

1780

drop table t1;

1594

1784

set foreign_key_checks=0;

1595

1785

eval create $temp table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type;

1596

1786

eval create $temp table t1(a varchar(10) primary key) engine = $engine_type;

1597

--error ER_ERROR_ON_RENAME, ER_ERROR_ON_RENAME

1787

-- error 1025,1025

1598

1788

alter table t1 modify column a int;

1599

1789

set foreign_key_checks=1;

1600

1790

drop table t2,t1;

1614

1804

eval create $temp table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=latin1;

1615

1805

eval create $temp table t3(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=utf8;

1616

1806

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1617

--error ER_ERROR_ON_RENAME

1807

-- error 1025

1618

1808

rename table t3 to t1;

1619

1809

set foreign_key_checks=1;

1620

1810

drop table t2,t3;

1628

1818

1629

1819

insert into t1 values(1);

1630

1820

insert into t3 values(1);

1631

--error ER_NO_REFERENCED_ROW_2

1821

-- error 1452

1632

1822

insert into t2 values(2);

1633

--error ER_NO_REFERENCED_ROW_2

1823

-- error 1452

1634

1824

insert into t4 values(2);

1635

1825

insert into t2 values(1);

1636

1826

insert into t4 values(1);

1637

--error ER_ROW_IS_REFERENCED_2

1827

-- error 1451

1638

1828

update t1 set a=2;

1639

--error ER_NO_REFERENCED_ROW_2

1829

-- error 1452

1640

1830

update t2 set a=2;

1641

--error ER_ROW_IS_REFERENCED_2

1831

-- error 1451

1642

1832

update t3 set a=2;

1643

--error ER_NO_REFERENCED_ROW_2

1833

-- error 1452

1644

1834

update t4 set a=2;

1645

--error ER_ROW_IS_REFERENCED_2

1835

-- error 1451

1646

1836

truncate t1;

1647

--error ER_ROW_IS_REFERENCED_2

1837

-- error 1451

1648

1838

truncate t3;

1649

1839

truncate t2;

1650

1840

truncate t4;

1684

1874

1685

1875

eval create $temp table t1 (s1 varbinary(2),primary key (s1)) engine=$engine_type;

1686

1876

eval create $temp table t2 (s1 binary(2),primary key (s1)) engine=$engine_type;

1687

eval create $temp table t3 (s1 varchar(2),primary key (s1)) engine=$engine_type;

1688

eval create $temp table t4 (s1 char(2),primary key (s1)) engine=$engine_type;

1877

eval create $temp table t3 (s1 varchar(2) binary,primary key (s1)) engine=$engine_type;

1878

eval create $temp table t4 (s1 char(2) binary,primary key (s1)) engine=$engine_type;

1689

1879

1690

1880

insert into t1 values (0x41),(0x4120),(0x4100);

1691

--error ER_DUP_ENTRY

1881

-- error ER_DUP_ENTRY

1692

1882

insert into t2 values (0x41),(0x4120),(0x4100);

1693

1883

insert into t2 values (0x41),(0x4120);

1694

--error ER_DUP_ENTRY

1884

-- error ER_DUP_ENTRY

1695

1885

insert into t3 values (0x41),(0x4120),(0x4100);

1696

1886

insert into t3 values (0x41),(0x4100);

1697

--error ER_DUP_ENTRY

1887

-- error ER_DUP_ENTRY

1698

1888

insert into t4 values (0x41),(0x4120),(0x4100);

1699

1889

insert into t4 values (0x41),(0x4100);

1700

1890

select hex(s1) from t1;

1710

1900

eval create $temp table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=$engine_type;

1711

1901

1712

1902

insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);

1713

--error ER_NO_REFERENCED_ROW_2

1903

-- error 1452

1714

1904

insert into t2 values(0x42);

1715

1905

insert into t2 values(0x41);

1716

1906

select hex(s1) from t2;

1717

1907

update t1 set s1=0x123456 where a=2;

1718

1908

select hex(s1) from t2;

1719

--error ER_ROW_IS_REFERENCED_2

1909

-- error 1451

1720

1910

update t1 set s1=0x12 where a=1;

1721

--error ER_ROW_IS_REFERENCED_2

1911

-- error 1451

1722

1912

update t1 set s1=0x12345678 where a=1;

1723

--error ER_ROW_IS_REFERENCED_2

1913

-- error 1451

1724

1914

update t1 set s1=0x123457 where a=1;

1725

1915

update t1 set s1=0x1220 where a=1;

1726

1916

select hex(s1) from t2;

1728

1918

select hex(s1) from t2;

1729

1919

update t1 set s1=0x4200 where a=1;

1730

1920

select hex(s1) from t2;

1731

--error ER_ROW_IS_REFERENCED_2

1921

-- error 1451

1732

1922

delete from t1 where a=1;

1733

1923

delete from t1 where a=2;

1734

1924

update t2 set s1=0x4120;

1735

--error ER_ROW_IS_REFERENCED_2

1925

-- error 1451

1736

1926

delete from t1;

1737

1927

delete from t1 where a!=3;

1738

1928

select a,hex(s1) from t1;

1740

1930

1741

1931

drop table t2,t1;

1742

1932

1743

eval create $temp table t1 (a int primary key,s1 varchar(2) not null unique) engine=$engine_type;

1744

eval create $temp table t2 (s1 char(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=$engine_type;

1933

eval create $temp table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=$engine_type;

1934

eval create $temp table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=$engine_type;

1745

1935

1746

1936

insert into t1 values(1,0x4100),(2,0x41);

1747

1937

insert into t2 values(0x41);

1751

1941

update t1 set s1=0x12 where a=2;

1752

1942

select hex(s1) from t2;

1753

1943

delete from t1 where a=1;

1754

--error ER_ROW_IS_REFERENCED_2

1944

-- error 1451

1755

1945

delete from t1 where a=2;

1756

1946

select a,hex(s1) from t1;

1757

1947

select hex(s1) from t2;

1853

2043

INSERT INTO t2 VALUES (1, 0, '');

1854

2044

INSERT INTO t2 VALUES (8, 1, '');

1855

2045

commit;

2046

DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)

2047

WHERE mm.id IS NULL;

1856

2048

select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)

1857

2049

where mm.id is null lock in share mode;

1858

2050

drop table t1,t2;

1909

2101

1910

2102

# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update

1911

2103

1912

--error ER_XXXLOCK_WAIT_TIMEOUTTTT

2104

--error 1205

1913

2105

select * from t1 where a = 2 and b = 2 for update;

1914

2106

1915

2107

# X-lock to record (1,1),(3,1),(5,1) should not be released in a update

1916

2108

1917

--error ER_LOCK_WAIT_TIMEOUT

2109

--error 1205

1918

2110

connection a;

1919

2111

commit;

1920

2112

connection b;

2054

2246

create $temp table t10(a int not null, b int, primary key(a)) select * from t2 for update;

2055

2247

2056

2248

connection b;

2057

--error ER_LOCK_WAIT_TIMEOUT

2249

--error 1205

2058

2250

reap;

2059

2251

2060

2252

connection c;

2061

--error ER_LOCK_WAIT_TIMEOUT

2253

--error 1205

2062

2254

reap;

2063

2255

2064

2256

connection d;

2065

--error ER_LOCK_WAIT_TIMEOUT

2257

--error 1205

2066

2258

reap;

2067

2259

2068

2260

connection e;

2069

--error ER_LOCK_WAIT_TIMEOUT

2261

--error 1205

2070

2262

reap;

2071

2263

2072

2264

connection f;

2073

--error ER_LOCK_WAIT_TIMEOUT

2265

--error 1205

2074

2266

reap;

2075

2267

2076

2268

connection g;

2077

--error ER_LOCK_WAIT_TIMEOUT

2269

--error 1205

2078

2270

reap;

2079

2271

2080

2272

connection h;

2081

--error ER_LOCK_WAIT_TIMEOUT

2273

--error 1205

2082

2274

reap;

2083

2275

2084

2276

connection i;

2085

--error ER_LOCK_WAIT_TIMEOUT

2277

--error 1205

2086

2278

reap;

2087

2279

2088

2280

connection j;

2089

--error ER_LOCK_WAIT_TIMEOUT

2281

--error 1205

2090

2282

reap;

2091

2283

2092

2284

connection a;

2110

2302

if (test_foreign_keys)

2111

2303

{

2112

2304

# bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID"

2113

--error ER_CANT_CREATE_TABLE

2305

--error 1005

2114

2306

eval create $temp table t1 (DB_ROW_ID int) engine=$engine_type;

2115

2307

2116

2308

2164

2356

eval create $temp table t1 ( a int ) ENGINE=$engine_type;

2165

2357

BEGIN;

2166

2358

INSERT INTO t1 VALUES (1);

2167

COMMIT;

2168

eval ALTER TABLE t1 ENGINE=$engine_type;

2359

OPTIMIZE TABLE t1;

2169

2360

DROP TABLE t1;

2170

2361

2171

2362

#######################################################################

Older »