~drizzle-trunk/drizzle/development

INSERT INTO t2 VALUES (1,000001,00,'Omaha','teethe','neat','') , (2,011401,37,'breaking','dreaded','Steinberg','W') , (3,011402,37,'Romans','scholastics','jarring','') , (4,011403,37,'intercepted','audiology','tinily','');

1331

SELECT * FROM t2;

1332

1333

1334

# For bug #12836

1335

# Delete was allowing all rows to be removed

1336

--error 1031

1337

DELETE FROM t2;

1338

SELECT * FROM t2;

1339

INSERT INTO t2 VALUES (2,011401,37,'breaking','dreaded','Steinberg','W');

1340

INSERT INTO t2 VALUES (3,011402,37,'Romans','scholastics','jarring','');

1341

INSERT INTO t2 VALUES (4,011403,37,'intercepted','audiology','tinily','');

1342

SELECT * FROM t2;

1343

--error 1031

1344

TRUNCATE TABLE t2;

1345

SELECT * FROM t2;

1346

1347

# Adding support for CHECK table

1348

CHECK TABLE t2;

1349

SELECT * FROM t2;

1350

1351

1352

# Test INSERT DELAYED and wait until the table has one more record

1353

SELECT COUNT(auto) FROM t2;

1354

INSERT DELAYED INTO t2 VALUES (99999,011403,37,'the','delayed','insert','');

1355

1356

# Insert another record since in Archive delayed values are only

1357

# guaranteed to materialize based on either:

1358

# 1) A new row showing up from a normal insert

1359

# 2) A flush table has occurred.

1360

INSERT INTO t2 VALUES (100000,000001,00,'after','delayed','insert','');

1361

1362

# Wait for the delayed insert to appear

1363

while (`SELECT COUNT(auto)!=1215 FROM t2`)

1364

{

1365

sleep 0.1;

1366

}

1367

SELECT COUNT(auto) FROM t2;

1368

1369

# Adding test for ALTER TABLE

1370

ALTER TABLE t2 DROP COLUMN fld6;

1371

SHOW CREATE TABLE t2;

1372

SELECT * FROM t2 WHERE auto != 100000;

1373

1374

1375

# Adding tests for autoincrement

1376

# First the simple stuff

1377

1378

CREATE TABLE `t5` (

1379

`a` int NOT NULL auto_increment,

1380

b char(12),

1381

PRIMARY KEY (`a`)

1382

) DEFAULT CHARSET=latin1;

1383

1384

INSERT INTO t5 VALUES (NULL, "foo");

1385

INSERT INTO t5 VALUES (NULL, "foo");

1386

INSERT INTO t5 VALUES (NULL, "foo");

1387

INSERT INTO t5 VALUES (NULL, "foo");

1388

INSERT INTO t5 VALUES (NULL, "foo");

1389

INSERT INTO t5 VALUES (32, "foo");

1390

--error 1022

1391

INSERT INTO t5 VALUES (23, "foo");

1392

INSERT INTO t5 VALUES (NULL, "foo");

1393

INSERT INTO t5 VALUES (NULL, "foo");

1394

--error 1022

1395

INSERT INTO t5 VALUES (3, "foo");

1396

INSERT INTO t5 VALUES (NULL, "foo");

1397

SELECT * FROM t5;

1398

1399

SELECT * FROM t5 WHERE a=3;

1400

1401

DROP TABLE t5;

1402

1403

CREATE TABLE `t5` (

1404

`a` int NOT NULL auto_increment,

1405

b char(12),

1406

KEY (`a`)

1407

) DEFAULT CHARSET=latin1 AUTO_INCREMENT=5;

1408

1409

INSERT INTO t5 VALUES (NULL, "foo");

1410

INSERT INTO t5 VALUES (NULL, "foo");

1411

INSERT INTO t5 VALUES (NULL, "foo");

1412

INSERT INTO t5 VALUES (NULL, "foo");

1413

INSERT INTO t5 VALUES (NULL, "foo");

1414

INSERT INTO t5 VALUES (32, "foo");

1415

INSERT INTO t5 VALUES (23, "foo");

1416

INSERT INTO t5 VALUES (NULL, "foo");

1417

INSERT INTO t5 VALUES (NULL, "foo");

1418

INSERT INTO t5 VALUES (3, "foo");

1419

INSERT INTO t5 VALUES (NULL, "foo");

1420

SELECT * FROM t5;

1421

OPTIMIZE TABLE t5;

1422

SELECT * FROM t5;

1423

1424

SELECT * FROM t5 WHERE a=32;

1425

SELECT * FROM t5 WHERE a=3;

1426

1427

DROP TABLE t5;

1428

1429

CREATE TABLE `t5` (

1430

`a` int NOT NULL auto_increment,

1431

b blob(12),

1432

KEY (`a`)

1433

) DEFAULT CHARSET=latin1;

1434

1435

INSERT INTO t5 VALUES (NULL, "foo");

1436

INSERT INTO t5 VALUES (NULL, "We the people");

1437

INSERT INTO t5 VALUES (NULL, "in order to form a more pefect union");

1438

INSERT INTO t5 VALUES (NULL, "establish justice");

1439

INSERT INTO t5 VALUES (NULL, "foo grok ");

1440

INSERT INTO t5 VALUES (32, "ensure domestic tranquility");

1441

INSERT INTO t5 VALUES (23, "provide for the common defense");

1442

INSERT INTO t5 VALUES (NULL, "fo fooo");

1443

INSERT INTO t5 VALUES (NULL, "foo just naother bit of text");

1444

INSERT INTO t5 VALUES (3, "foo this is mine to think about");

1445

INSERT INTO t5 VALUES (NULL, "promote the general welfare");

1446

SELECT * FROM t5;

1447

SELECT b FROM t5;

1448

SELECT b FROM t5 WHERE a =3;

1449

SELECT b FROM t5 WHERE a IN (32, 23, 5);

1450

1451

1452

#More blob tests

1453

1454

DROP TABLE t5;

1455

1456

CREATE TABLE `t5` (

1457

`a` int NOT NULL auto_increment,

1458

b blob(12),

1459

c blob(12),

1460

KEY (`a`)

1461

) DEFAULT CHARSET=latin1;

1462

1463

INSERT INTO t5 VALUES (NULL, "foo", "grok this!");

1464

INSERT INTO t5 VALUES (NULL, "We the people", NULL);

1465

INSERT INTO t5 VALUES (NULL, "in order to form a more peefect union", "secure the blessing of liberty");

1466

INSERT INTO t5 VALUES (NULL, "establish justice", "to ourselves and");

1467

INSERT INTO t5 VALUES (32, "ensure domestic tranquility", NULL);

1468

INSERT INTO t5 VALUES (23, "provide for the common defense", "posterity");

1469

INSERT INTO t5 VALUES (NULL, "promote the general welfare", "do ordain");

1470

SELECT * FROM t5;

1471

SELECT b FROM t5;

1472

SELECT b FROM t5 WHERE a =3;

1473

SELECT b FROM t5 WHERE a IN (32, 23, 5);

1474

SELECT c FROM t5;

1475

SELECT c FROM t5 WHERE a =3;

1476

SELECT c FROM t5 WHERE a IN (32, 23, 5);

1477

1478

# Adding this in case someone tries to add fast ALTER TABLE and doesn't tes

1479

# it.

1480

# Some additional tests for new, faster ALTER TABLE. Note that most of the

1481

# whole ALTER TABLE code is being tested all around the test suite already.

1482

1483

1484

DROP TABLE t1;

1485

CREATE TABLE t1 (v varchar(32)) ;

1486

insert into t1 values ('def'),('abc'),('hij'),('3r4f');

1487

select * from t1;

1488

# Fast alter, no copy performed

1489

ALTER TABLE t1 change v v2 varchar(32);

1490

select * from t1;

1491

# Fast alter, no copy performed

1492

ALTER TABLE t1 change v2 v varchar(64);

1493

select * from t1;

1494

# Regular ALTER TABLE

1495

ALTER TABLE t1 add i int auto_increment not null primary key first;

1496

select * from t1;

1497

1498

# Testing cleared row key

1499

DROP TABLE t5;

1500

1501

CREATE TABLE `t5` (

1502

`a` int NOT NULL auto_increment,

1503

b varchar(250),

1504

c varchar(800),

1505

KEY (`a`)

1506

) DEFAULT CHARSET=latin1;

1507

1508

INSERT INTO t5 VALUES (NULL, "foo", "grok this!");

1509

INSERT INTO t5 VALUES (NULL, "We the people", NULL);

1510

INSERT INTO t5 VALUES (NULL, "in order to form a more peefect union", "secure the blessing of liberty");

1511

INSERT INTO t5 VALUES (NULL, "establish justice", "to ourselves and");

1512

INSERT INTO t5 VALUES (32, "ensure domestic tranquility", NULL);

1513

INSERT INTO t5 VALUES (23, "provide for the common defense", "posterity");

1514

INSERT INTO t5 VALUES (NULL, "promote the general welfare", "do ordain");

1515

INSERT INTO t5 VALUES (NULL, "abcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabc", "do ordain");

1516

1517

SELECT * FROM t5;

1518

1519

CREATE TABLE `t6` (

1520

`a` int NOT NULL auto_increment,

1521

b blob(12),

1522

c int,

1523

KEY (`a`)

1524

) DEFAULT CHARSET=latin1;

1525

SELECT * FROM t6;

1526

INSERT INTO t6 VALUES (NULL, "foo", NULL);

1527

INSERT INTO t6 VALUES (NULL, "We the people", 5);

1528

INSERT INTO t6 VALUES (NULL, "in order to form a more pefect union", 9);

1529

INSERT INTO t6 VALUES (NULL, "establish justice", NULL);

1530

INSERT INTO t6 VALUES (NULL, NULL, NULL);

1531

INSERT INTO t6 VALUES (32, "ensure domestic tranquility", NULL);

1532

INSERT INTO t6 VALUES (23, "provide for the common defense", 30);

1533

INSERT INTO t6 VALUES (NULL, "fo fooo", 70);

1534

INSERT INTO t6 VALUES (NULL, NULL, 98);

1535

INSERT INTO t6 VALUES (NULL, "promote the general welfare", 50);

1536

SELECT * FROM t6;

1537

SELECT * FROM t6 ORDER BY a;

1538

SELECT * FROM t6 ORDER BY a DESC;

1539

1540

SHOW CREATE TABLE t6;

1541

1542

1543

1544

# Cleanup, test is over

1545

1546

1547

1548

--disable_warnings

1549

DROP TABLE t1, t2, t4, t5, t6;

1550

--enable_warnings

1551

1552

1553

# BUG#26138 - REPAIR TABLE with option USE_FRM erases all records in ARCHIVE

1554

# table

1555

1556

create table t1 (i int) engine=archive;

1557

insert into t1 values (1);

1558

repair table t1 use_frm;

1559

select * from t1;

1560

drop table t1;

1561

1562

1563

# BUG#29207 - archive table reported as corrupt by check table

1564

1565

create table t1(a longblob) engine=archive;

1566

insert into t1 set a='';

1567

insert into t1 set a='a';

1568

check table t1 extended;

1569

drop table t1;

1570

1571

1572

# BUG#31036 - Using order by with archive table crashes server

1573

1574

1575

CREATE TABLE t1(a VARCHAR(510)) ENGINE = ARCHIVE;

1576

1577

let $bug31036=41;

1578

--disable_query_log

1579

while($bug31036)

1580

{

1581

INSERT INTO t1(a) VALUES (REPEAT('a', 510));

1582

dec $bug31036;

1583

}

1584

--enable_query_log

1585

INSERT INTO t1(a) VALUES ('');

1586

1587

--disable_result_log

1588

SELECT * FROM t1 ORDER BY a;

1589

--enable_result_log

1590

1591

DROP TABLE t1;

1592

1593

1594

# BUG#31833 - ORDER BY leads to wrong result when ARCHIVE, BLOB and table

1595

# cache is full

1596

1597

CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b BLOB, KEY(a)) ENGINE=archive;

1598

INSERT INTO t1 VALUES (NULL, NULL),(NULL, NULL);

1599

FLUSH TABLE t1;

1600

SELECT * FROM t1 ORDER BY a;

Older »