~drizzle-trunk/drizzle/development

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

339

342

drop table t1;

340

CREATE TEMPORARY TABLE t1 (id int NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) ENGINE=MyISAM;

343

CREATE TABLE t1 (id int NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) ENGINE=MyISAM;

341

344

INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf');

342

345

DROP TABLE t1;

343

346

CREATE TABLE t1 (

353

356

NULL

354

357

NULL

355

358

DROP TABLE t1;

356

CREATE TEMPORARY TABLE t1 (title text) ENGINE=MyISAM;

359

CREATE TABLE t1 (title text) ENGINE=MyISAM;

357

360

INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education');

358

361

INSERT INTO t1 VALUES ('House passes the CAREERS bill');

359

362

SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1;

480

483

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used

481

484

Warnings:

482

485

Note 1003 select collation(conv(130,16,10)) AS `collation(conv(130,16,10))`

486

explain extended select coercibility(conv(130,16,10));

487

id select_type table type possible_keys key key_len ref rows filtered Extra

488

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used

489

Warnings:

490

Note 1003 select coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`

483

491

explain extended select length('\n\t\r\b\0\_\%\\');

484

492

id select_type table type possible_keys key key_len ref rows filtered Extra

485

493

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used

583

591

select trim(trailing NULL from 'xyz') as "must_be_null";

584

592

must_be_null

585

593

NULL

586

CREATE TEMPORARY TABLE t1 (

594

CREATE TABLE t1 (

587

595

id int NOT NULL auto_increment,

588

596

a bigint default NULL,

589

597

PRIMARY KEY (id)

706

714

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

707

715

select lpad(i, 7, ' ') as t from t1;

708

716

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

709

def t 8 7 7 Y 128 31 63

717

def t 9 7 7 Y 128 31 63

710

718

711

719

1000000

712

720

713

721

select rpad(i, 7, ' ') as t from t1;

714

722

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

715

def t 8 7 7 Y 128 31 63

723

def t 9 7 7 Y 128 31 63

716

724

717

725

1000000

718

726

719

727

drop table t1;

720

select load_file(DRIZZLETEST_BAD_FILE);

721

ERROR HY000: The file 'DRIZZLETEST_BAD_FILE' must be in the schema directory or be readable by all

728

select load_file("lkjlkj");

729

load_file("lkjlkj")

730

NULL

731

select ifnull(load_file("lkjlkj"),"it's null");

732

ifnull(load_file("lkjlkj"),"it's null")

733

it's null

722

734

CREATE TABLE t1 (a varchar(10));

723

735

INSERT INTO t1 VALUES ('abc'), ('xyz');

724

736

SELECT a, CONCAT(a,' ',a) AS c FROM t1

762

774

Warnings:

763

775

Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both 'y' from `test`.`t1`.`s`) > 'ab')

764

776

DROP TABLE t1;

765

create TEMPORARY table t1 (a bigint not null)engine=myisam;

777

create table t1 (a bigint not null)engine=myisam;

766

778

insert into t1 set a = 1024*1024*1024*4;

767

779

drop table t1;

768

create TEMPORARY table t1 (a char(36) not null)engine=myisam;

780

create table t1 (a char(36) not null)engine=myisam;

769

781

insert ignore into t1 set a = ' ';

770

782

insert ignore into t1 set a = ' ';

771

783

select * from t1 order by (oct(a));

798

810

20.06

799

811

select conv("18383815659218730760",10,10) + 0;

800

812

conv("18383815659218730760",10,10) + 0

801

1.83838156592187e19

813

1.838381565921873e19

802

814

select "18383815659218730760" + 0;

803

815

"18383815659218730760" + 0

804

1.83838156592187e19

816

1.838381565921873e19

805

817

CREATE TABLE t1 (code varchar(10));

806

818

INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13');

807

819

SELECT ASCII(code), code FROM t1 WHERE code='A12';

833

845

WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00');

834

846

id select_type table type possible_keys key key_len ref rows filtered Extra

835

847

1 SIMPLE t2 const PRIMARY PRIMARY 42 const 1 100.00 Using index

836

1 SIMPLE t1 ref code code 43 const 4 100.00 Using where; Using index

848

1 SIMPLE t1 ref code code 43 const 2 100.00 Using where; Using index

837

849

Warnings:

838

Note 1003 select `test`.`t1`.`code` AS `code`,'a12' AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = 'a12') and <cache>(('a12' = 'a12')) and ((length(`test`.`t1`.`code`) = 5) or <cache>(('a12' < 'a00'))))

850

Note 1003 select `test`.`t1`.`code` AS `code`,'a12' AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = 'a12') and ('a12' = 'a12') and ((length(`test`.`t1`.`code`) = 5) or ('a12' < 'a00')))

839

851

DROP TABLE t1,t2;

840

852

select benchmark(-1, 1);

841

853

benchmark(-1, 1)

1374

1386

repeat('hello', 4294967295)

1375

1387

NULL

1376

1388

Warnings:

1377

Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated

1389

Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated

1378

1390

select repeat('hello', -4294967296);

1379

1391

repeat('hello', -4294967296)

1380

1392

1382

1394

repeat('hello', 4294967296)

1383

1395

NULL

1384

1396

Warnings:

1385

Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated

1397

Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated

1386

1398

select repeat('hello', -4294967297);

1387

1399

repeat('hello', -4294967297)

1388

1400

1390

1402

repeat('hello', 4294967297)

1391

1403

NULL

1392

1404

Warnings:

1393

Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated

1405

Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated

1394

1406

select repeat('hello', -18446744073709551615);

1395

1407

repeat('hello', -18446744073709551615)

1396

1408

1401

1413

repeat('hello', 18446744073709551615)

1402

1414

NULL

1403

1415

Warnings:

1404

Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated

1416

Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated

1405

1417

select repeat('hello', -18446744073709551616);

1406

1418

repeat('hello', -18446744073709551616)

1407

1419

1414

1426

Warnings:

1415

1427

Error 1292 Truncated incorrect DECIMAL value: ''

1416

1428

Error 1292 Truncated incorrect DECIMAL value: ''

1417

Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated

1429

Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated

1418

1430

select repeat('hello', -18446744073709551617);

1419

1431

repeat('hello', -18446744073709551617)

1420

1432

1427

1439

Warnings:

1428

1440

Error 1292 Truncated incorrect DECIMAL value: ''

1429

1441

Error 1292 Truncated incorrect DECIMAL value: ''

1430

Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated

1442

Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated

1431

1443

select space(-1);

1432

1444

space(-1)

1433

1445

1438

1450

space(4294967295)

1439

1451

NULL

1440

1452

Warnings:

1441

Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated

1453

Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated

1442

1454

select space(-4294967296);

1443

1455

space(-4294967296)

1444

1456

1446

1458

space(4294967296)

1447

1459

NULL

1448

1460

Warnings:

1449

Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated

1461

Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated

1450

1462

select space(-4294967297);

1451

1463

space(-4294967297)

1452

1464

1454

1466

space(4294967297)

1455

1467

NULL

1456

1468

Warnings:

1457

Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated

1469

Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated

1458

1470

select space(-18446744073709551615);

1459

1471

space(-18446744073709551615)

1460

1472

1465

1477

space(18446744073709551615)

1466

1478

NULL

1467

1479

Warnings:

1468

Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated

1480

Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated

1469

1481

select space(-18446744073709551616);

1470

1482

space(-18446744073709551616)

1471

1483

1478

1490

Warnings:

1479

1491

Error 1292 Truncated incorrect DECIMAL value: ''

1480

1492

Error 1292 Truncated incorrect DECIMAL value: ''

1481

Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated

1493

Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated

1482

1494

select space(-18446744073709551617);

1483

1495

space(-18446744073709551617)

1484

1496

1491

1503

Warnings:

1492

1504

Error 1292 Truncated incorrect DECIMAL value: ''

1493

1505

Error 1292 Truncated incorrect DECIMAL value: ''

1494

Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated

1506

Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated

1495

1507

select rpad('hello', -1, '1');

1496

1508

rpad('hello', -1, '1')

1497

1509

NULL

1502

1514

rpad('hello', 4294967295, '1')

1503

1515

NULL

1504

1516

Warnings:

1505

Warning 1301 Result of rpad() was larger than max_allowed_packet (67108864) - truncated

1517

Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated

1506

1518

select rpad('hello', -4294967296, '1');

1507

1519

rpad('hello', -4294967296, '1')

1508

1520

NULL

1510

1522

rpad('hello', 4294967296, '1')

1511

1523

NULL

1512

1524

Warnings:

1513

Warning 1301 Result of rpad() was larger than max_allowed_packet (67108864) - truncated

1525

Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated

1514

1526

select rpad('hello', -4294967297, '1');

1515

1527

rpad('hello', -4294967297, '1')

1516

1528

NULL

1518

1530

rpad('hello', 4294967297, '1')

1519

1531

NULL

1520

1532

Warnings:

1521

Warning 1301 Result of rpad() was larger than max_allowed_packet (67108864) - truncated

1533

Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated

1522

1534

select rpad('hello', -18446744073709551615, '1');

1523

1535

rpad('hello', -18446744073709551615, '1')

1524

1536

NULL

1529

1541

rpad('hello', 18446744073709551615, '1')

1530

1542

NULL

1531

1543

Warnings:

1532

Warning 1301 Result of rpad() was larger than max_allowed_packet (67108864) - truncated

1544

Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated

1533

1545

select rpad('hello', -18446744073709551616, '1');

1534

1546

rpad('hello', -18446744073709551616, '1')

1535

1547

NULL

1542

1554

Warnings:

1543

1555

Error 1292 Truncated incorrect DECIMAL value: ''

1544

1556

Error 1292 Truncated incorrect DECIMAL value: ''

1545

Warning 1301 Result of rpad() was larger than max_allowed_packet (67108864) - truncated

1557

Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated

1546

1558

select rpad('hello', -18446744073709551617, '1');

1547

1559

rpad('hello', -18446744073709551617, '1')

1548

1560

NULL

1555

1567

Warnings:

1556

1568

Error 1292 Truncated incorrect DECIMAL value: ''

1557

1569

Error 1292 Truncated incorrect DECIMAL value: ''

1558

Warning 1301 Result of rpad() was larger than max_allowed_packet (67108864) - truncated

1570

Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated

1559

1571

select lpad('hello', -1, '1');

1560

1572

lpad('hello', -1, '1')

1561

1573

NULL

1566

1578

lpad('hello', 4294967295, '1')

1567

1579

NULL

1568

1580

Warnings:

1569

Warning 1301 Result of lpad() was larger than max_allowed_packet (67108864) - truncated

1581

Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated

1570

1582

select lpad('hello', -4294967296, '1');

1571

1583

lpad('hello', -4294967296, '1')

1572

1584

NULL

1574

1586

lpad('hello', 4294967296, '1')

1575

1587

NULL

1576

1588

Warnings:

1577

Warning 1301 Result of lpad() was larger than max_allowed_packet (67108864) - truncated

1589

Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated

1578

1590

select lpad('hello', -4294967297, '1');

1579

1591

lpad('hello', -4294967297, '1')

1580

1592

NULL

1582

1594

lpad('hello', 4294967297, '1')

1583

1595

NULL

1584

1596

Warnings:

1585

Warning 1301 Result of lpad() was larger than max_allowed_packet (67108864) - truncated

1597

Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated

1586

1598

select lpad('hello', -18446744073709551615, '1');

1587

1599

lpad('hello', -18446744073709551615, '1')

1588

1600

NULL

1593

1605

lpad('hello', 18446744073709551615, '1')

1594

1606

NULL

1595

1607

Warnings:

1596

Warning 1301 Result of lpad() was larger than max_allowed_packet (67108864) - truncated

1608

Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated

1597

1609

select lpad('hello', -18446744073709551616, '1');

1598

1610

lpad('hello', -18446744073709551616, '1')

1599

1611

NULL

1606

1618

Warnings:

1607

1619

Error 1292 Truncated incorrect DECIMAL value: ''

1608

1620

Error 1292 Truncated incorrect DECIMAL value: ''

1609

Warning 1301 Result of lpad() was larger than max_allowed_packet (67108864) - truncated

1621

Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated

1610

1622

select lpad('hello', -18446744073709551617, '1');

1611

1623

lpad('hello', -18446744073709551617, '1')

1612

1624

NULL

1619

1631

Warnings:

1620

1632

Error 1292 Truncated incorrect DECIMAL value: ''

1621

1633

Error 1292 Truncated incorrect DECIMAL value: ''

1622

Warning 1301 Result of lpad() was larger than max_allowed_packet (67108864) - truncated

1634

Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated

1623

1635

SELECT CHAR(0xff,0x8f);

1624

1636

CHAR(0xff,0x8f)

1625

1637

��

Older »