~drizzle-trunk/drizzle/development

CREATE TABLE t1 (privatemessageid int NOT NULL auto_increment, folderid int NOT NULL default '0', userid int NOT NULL default '0', touserid int NOT NULL default '0', fromuserid int NOT NULL default '0', title varchar(250) NOT NULL default '', message mediumtext NOT NULL, dateline int NOT NULL default '0', showsignature int NOT NULL default '0', iconid int NOT NULL default '0', messageread int NOT NULL default '0', readtime int NOT NULL default '0', receipt int NOT NULL default '0', deleteprompt int NOT NULL default '0', multiplerecipients int NOT NULL default '0', PRIMARY KEY (privatemessageid), KEY userid (userid));

416

INSERT INTO t1 VALUES (128,0,33,33,8,':D','',996121863,1,0,2,996122850,2,0,0);

417

CREATE TABLE t2 (userid int NOT NULL auto_increment, usergroupid int NOT NULL default '0', username varchar(50) NOT NULL default '', password varchar(50) NOT NULL default '', email varchar(50) NOT NULL default '', styleid int NOT NULL default '0', parentemail varchar(50) NOT NULL default '', coppauser int NOT NULL default '0', homepage varchar(100) NOT NULL default '', icq varchar(20) NOT NULL default '', aim varchar(20) NOT NULL default '', yahoo varchar(20) NOT NULL default '', signature mediumtext NOT NULL, adminemail int NOT NULL default '0', showemail int NOT NULL default '0', invisible int NOT NULL default '0', usertitle varchar(250) NOT NULL default '', customtitle int NOT NULL default '0', joindate int NOT NULL default '0', cookieuser int NOT NULL default '0', daysprune int NOT NULL default '0', lastvisit int NOT NULL default '0', lastactivity int NOT NULL default '0', lastpost int NOT NULL default '0', posts int NOT NULL default '0', timezoneoffset varchar(4) NOT NULL default '', emailnotification int NOT NULL default '0', buddylist mediumtext NOT NULL, ignorelist mediumtext NOT NULL, pmfolders mediumtext NOT NULL, receivepm int NOT NULL default '0', emailonpm int NOT NULL default '0', pmpopup int NOT NULL default '0', avatarid int NOT NULL default '0', avatarrevision int NOT NULL default '0', options int NOT NULL default '15', birthday date NULL, maxposts int NOT NULL default '-1', startofweek int NOT NULL default '1', ipaddress varchar(20) NOT NULL default '', referrerid int NOT NULL default '0', nosessionhash int NOT NULL default '0', autorefresh int NOT NULL default '-1', messagepopup int NOT NULL default '0', inforum int NOT NULL default '0', ratenum int NOT NULL default '0', ratetotal int NOT NULL default '0', allowrate int NOT NULL default '1', PRIMARY KEY (userid), KEY usergroupid (usergroupid), KEY username (username), KEY inforum (inforum));

418

INSERT INTO t2 VALUES (33,6,'Kevin','0','kevin@stileproject.com',1,'',0,'http://www.stileproject.com','','','','',1,1,0,'Administrator',0,996120694,1,-1,1030996168,1031027028,1030599436,36,'-6',0,'','','',1,0,1,0,0,15,NULL,-1,1,'64.0.0.0',0,1,-1,0,0,4,19,1);

419

SELECT DISTINCT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t2.userid = t1.touserid);

420

privatemessageid folderid userid touserid fromuserid title message dateline showsignature iconid messageread readtime receipt deleteprompt multiplerecipients userid usergroupid username password email styleid parentemail coppauser homepage icq aim yahoo signature adminemail showemail invisible usertitle customtitle joindate cookieuser daysprune lastvisit lastactivity lastpost posts timezoneoffset emailnotification buddylist ignorelist pmfolders receivepm emailonpm pmpopup avatarid avatarrevision options birthday maxposts startofweek ipaddress referrerid nosessionhash autorefresh messagepopup inforum ratenum ratetotal allowrate

421

128 0 33 33 8 :D 996121863 1 0 2 996122850 2 0 0 33 6 Kevin 0 kevin@stileproject.com 1 0 http://www.stileproject.com 1 1 0 Administrator 0 996120694 1 -1 1030996168 1031027028 1030599436 36 -6 0 1 0 1 0 0 15 NULL -1 1 64.0.0.0 0 1 -1 0 0 4 19 1

422

DROP TABLE t1,t2;

423

CREATE TABLE t1 (a int primary key, b int, c int);

424

INSERT t1 VALUES (1,2,3);

425

CREATE TABLE t2 (a int primary key, b int, c int);

426

INSERT t2 VALUES (3,4,5);

427

SELECT DISTINCT t1.a, t2.b FROM t1, t2 WHERE t1.a=1 ORDER BY t2.c;

428

a b

429

1 4

430

DROP TABLE t1,t2;

431

CREATE table t1 ( `id` int NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`)) AUTO_INCREMENT=3 ;

432

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

433

INSERT INTO t1 VALUES (3, 'aaaaa');

434

INSERT INTO t1 VALUES (2, 'eeeeeee');

435

select distinct left(name,1) as name from t1;

436

name

437

438

439

drop table t1;

440

CREATE TABLE t1 (

441

ID int NOT NULL auto_increment,

442

NAME varchar(75) DEFAULT '' NOT NULL,

443

LINK_ID int DEFAULT '0' NOT NULL,

444

PRIMARY KEY (ID),

445

KEY NAME (NAME),

446

KEY LINK_ID (LINK_ID)

447

);

448

INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0);

449

INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (2,'Jack',0);

450

INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (3,'Bill',0);

451

CREATE TABLE t2 (

452

ID int NOT NULL auto_increment,

453

NAME varchar(150) DEFAULT '' NOT NULL,

454

PRIMARY KEY (ID),

455

KEY NAME (NAME)

456

);

457

SELECT DISTINCT

458

t2.id AS key_link_id,

459

t2.name AS link

460

FROM t1

461

LEFT JOIN t2 ON t1.link_id=t2.id

462

GROUP BY t1.id

463

ORDER BY link;

464

key_link_id link

465

NULL NULL

466

drop table t1,t2;

467

CREATE TABLE t1 (

468

html varchar(5) default NULL,

469

rin int default '0',

470

rout int default '0'

471

);

472

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

473

SELECT DISTINCT html,SUM(rout)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin;

474

html prod

475

1 0.0000

476

drop table t1;

477

CREATE TABLE t1 (a int);

478

INSERT INTO t1 VALUES (1),(2),(3),(4),(5);

479

SELECT DISTINCT a, 1 FROM t1;

480

a 1

481

1 1

482

2 1

483

3 1

484

4 1

485

5 1

486

SELECT DISTINCT 1, a FROM t1;

487

1 a

488

1 1

489

1 2

490

1 3

491

1 4

492

1 5

493

CREATE TABLE t2 (a int, b int);

494

INSERT INTO t2 VALUES (1,1),(2,2),(2,3),(2,4),(3,5);

495

SELECT DISTINCT a, b, 2 FROM t2;

496

a b 2

497

1 1 2

498

2 2 2

499

2 3 2

500

2 4 2

501

3 5 2

502

SELECT DISTINCT 2, a, b FROM t2;

503

2 a b

504

2 1 1

505

2 2 2

506

2 2 3

507

2 2 4

508

2 3 5

509

SELECT DISTINCT a, 2, b FROM t2;

510

a 2 b

511

1 2 1

512

2 2 2

513

2 2 3

514

2 2 4

515

3 2 5

516

DROP TABLE t1,t2;

517

CREATE TABLE t1(a INT PRIMARY KEY, b INT);

518

INSERT INTO t1 VALUES (1,1), (2,1), (3,1);

519

EXPLAIN SELECT DISTINCT a FROM t1;

520

id select_type table type possible_keys key key_len ref rows Extra

521

1 SIMPLE t1 ALL NULL NULL NULL NULL 3

522

EXPLAIN SELECT DISTINCT a,b FROM t1;

523

id select_type table type possible_keys key key_len ref rows Extra

524

1 SIMPLE t1 ALL NULL NULL NULL NULL 3

525

EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2;

526

id select_type table type possible_keys key key_len ref rows Extra

527

1 SIMPLE t1_1 ALL NULL NULL NULL NULL 3 Using temporary

528

1 SIMPLE t1_2 ALL NULL NULL NULL NULL 3 Distinct; Using join buffer

529

EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2

530

WHERE t1_1.a = t1_2.a;

531

id select_type table type possible_keys key key_len ref rows Extra

532

1 SIMPLE t1_1 ALL PRIMARY NULL NULL NULL 3 Using temporary

533

1 SIMPLE t1_2 eq_ref PRIMARY PRIMARY 4 test.t1_1.a 1 Distinct

534

EXPLAIN SELECT a FROM t1 GROUP BY a;

535

id select_type table type possible_keys key key_len ref rows Extra

536

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort

537

EXPLAIN SELECT a,b FROM t1 GROUP BY a,b;

538

id select_type table type possible_keys key key_len ref rows Extra

539

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort

540

EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b;

541

id select_type table type possible_keys key key_len ref rows Extra

542

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort

543

CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT,

544

PRIMARY KEY (a,b));

545

INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);

546

EXPLAIN SELECT DISTINCT a FROM t2;

547

id select_type table type possible_keys key key_len ref rows Extra

548

1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using temporary

549

EXPLAIN SELECT DISTINCT a,a FROM t2;

550

id select_type table type possible_keys key key_len ref rows Extra

551

1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using temporary

552

EXPLAIN SELECT DISTINCT b,a FROM t2;

553

id select_type table type possible_keys key key_len ref rows Extra

554

1 SIMPLE t2 ALL NULL NULL NULL NULL 3

555

EXPLAIN SELECT DISTINCT a,c FROM t2;

556

id select_type table type possible_keys key key_len ref rows Extra

557

1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using temporary

558

EXPLAIN SELECT DISTINCT c,a,b FROM t2;

559

id select_type table type possible_keys key key_len ref rows Extra

560

1 SIMPLE t2 ALL NULL NULL NULL NULL 3

561

EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;

562

id select_type table type possible_keys key key_len ref rows Extra

563

1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort

564

CREATE UNIQUE INDEX c_b_unq ON t2 (c,b);

565

EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;

566

id select_type table type possible_keys key key_len ref rows Extra

567

1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using filesort

568

DROP TABLE t1,t2;

569

create table t1 (id int, dsc varchar(50));

570

insert into t1 values (1, "line number one"), (2, "line number two"), (3, "line number three");

571

select distinct id, IFNULL(dsc, '-') from t1;

572

id IFNULL(dsc, '-')

573

1 line number one

574

2 line number two

575

3 line number three

576

drop table t1;

577

CREATE TABLE t1 (a int primary key, b int);

578

INSERT INTO t1 (a,b) values (1,1), (2,3), (3,2);

579

explain SELECT DISTINCT a, b FROM t1 ORDER BY b;

580

id select_type table type possible_keys key key_len ref rows Extra

581

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort

582

SELECT DISTINCT a, b FROM t1 ORDER BY b;

583

a b

584

1 1

585

3 2

586

2 3

587

DROP TABLE t1;

588

CREATE TABLE t1 (

589

ID int NOT NULL auto_increment,

590

x varchar(20) default NULL,

591

y decimal(10,0) default NULL,

592

PRIMARY KEY (ID),

593

KEY (y)

594

);

595

INSERT INTO t1 VALUES

596

(1,'ba','-1'),

597

(2,'ba','1150'),

598

(306,'ba','-1'),

599

(307,'ba','1150'),

600

(611,'ba','-1'),

601

(612,'ba','1150');

602

select count(distinct x,y) from t1;

603

count(distinct x,y)

604

605

select count(distinct concat(x,y)) from t1;

606

count(distinct concat(x,y))

607

608

drop table t1;

609

CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b));

610

INSERT INTO t1 VALUES (1, 101);

611

INSERT INTO t1 SELECT a + 1, a + 101 FROM t1;

612

INSERT INTO t1 SELECT a + 2, a + 102 FROM t1;

613

INSERT INTO t1 SELECT a + 4, a + 104 FROM t1;

614

INSERT INTO t1 SELECT a + 8, a + 108 FROM t1;

615

EXPLAIN SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a;

616

id select_type table type possible_keys key key_len ref rows Extra

617

1 SIMPLE t1 ALL NULL NULL NULL NULL 16 Using where; Using temporary; Using filesort

618

SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a;

619

a a

620

DROP TABLE t1;

621

CREATE TABLE t1 (a INT, UNIQUE (a));

622

INSERT INTO t1 VALUES (4),(null),(2),(1),(null),(3);

623

EXPLAIN SELECT DISTINCT a FROM t1;

624

id select_type table type possible_keys key key_len ref rows Extra

625

1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary

626

SELECT DISTINCT a FROM t1;

627

628

629

NULL

630

631

632

633

EXPLAIN SELECT a FROM t1 GROUP BY a;

634

id select_type table type possible_keys key key_len ref rows Extra

635

1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort

636

SELECT a FROM t1 GROUP BY a;

637

638

NULL

639

640

641

642

643

DROP TABLE t1;

644

CREATE TABLE t1 (a INT, b INT);

645

INSERT INTO t1 VALUES(1,1),(1,2),(1,3);

646

SELECT DISTINCT a, b FROM t1;

647

a b

648

1 1

649

1 2

650

1 3

651

SELECT DISTINCT a, a, b FROM t1;

652

a a b

653

1 1 1

654

1 1 2

655

1 1 3

656

DROP TABLE t1;

657

End of 5.0 tests

658

CREATE TABLE t1(a INT, b INT, c INT, d INT DEFAULT 0, e INT DEFAULT 0,

659

PRIMARY KEY(a,b,c,d,e),

660

KEY(a,b,d,c)

661

);

662

INSERT INTO t1(a, b, c) VALUES (1, 1, 1),

663

(1, 1, 2),

664

(1, 1, 3),

665

(1, 2, 1),

666

(1, 2, 2),

667

(1, 2, 3);

668

EXPLAIN SELECT DISTINCT a, b, d, c FROM t1;

669

id select_type table type possible_keys key key_len ref rows Extra

670

1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary

671

SELECT DISTINCT a, b, d, c FROM t1;

672

a b d c

673

1 1 0 1

674

1 1 0 2

675

1 1 0 3

676

1 2 0 1

677

1 2 0 2

678

1 2 0 3

679

DROP TABLE t1;

680

End of 5.1 tests

Older »