~drizzle-trunk/drizzle/development : revision 186

18

col6 col1 col3 fourth col4 col4_5 col5 col7 col8

19

1 2 3 4 5 PENDING 0000-00-00 00:00:00

20

drop table t1;

21

create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL);

21

create table t1 (bandID INT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL);

22

insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12);

23

alter table t1 add column new_col int, order by payoutid,bandid;

24

select * from t1;

52

ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null;

53

SHOW FULL COLUMNS FROM t1;

54

Field Type Collation Null Key Default Extra Privileges Comment

55

GROUP_ID int(10) unsigned NULL NO PRI 0 #

56

LANG_ID smallint(5) unsigned NULL NO PRI 0 #

55

GROUP_ID int(10) unsigned NULL NO PRI NULL #

56

LANG_ID smallint(5) unsigned NULL NO PRI NULL #

57

NAME char(80) latin1_swedish_ci NO MUL NULL #

58

DROP TABLE t1;

59

create table t1 (n int);

298

t1 0 a 2 b A 300 NULL NULL YES BTREE

299

t1 1 b 1 b A 100 NULL NULL YES BTREE

300

drop table t1;

301

CREATE TABLE t1 (i int(10), index(i) );

302

ALTER TABLE t1 DISABLE KEYS;

303

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

304

ALTER TABLE t1 ENABLE KEYS;

305

drop table t1;

306

301

CREATE TABLE t1 (

307

302

Host varchar(16) binary NOT NULL default '',

308

303

User varchar(16) binary NOT NULL default '',

397

392

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

398

393

t1 MyISAM 10 Fixed 1 37 X X X X X X X X latin1_swedish_ci NULL

399

394

drop table t1;

400

create table t1 (a int not null, b int not null, c int not null, d int not null, e int not null, f int not null, g int not null, h int not null,i int not null, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;

395

create table t1 (a int not null default 0, b int not null default 0, c int not null default 0, d int not null default 0, e int not null default 0, f int not null default 0, g int not null default 0, h int not null default 0,i int not null default 0, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;

401

396

insert into t1 (a) values(1);

402

Warnings:

403

Warning 1364 Field 'b' doesn't have a default value

404

Warning 1364 Field 'c' doesn't have a default value

405

Warning 1364 Field 'd' doesn't have a default value

406

Warning 1364 Field 'e' doesn't have a default value

407

Warning 1364 Field 'f' doesn't have a default value

408

Warning 1364 Field 'g' doesn't have a default value

409

Warning 1364 Field 'h' doesn't have a default value

410

Warning 1364 Field 'i' doesn't have a default value

411

397

show table status like 't1';

412

398

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

413

399

t1 MyISAM 10 Fixed 1 37 X X X X X X X X latin1_swedish_ci NULL

453

439

delete from t1;

454

440

show create table t1;

455

441

Table Create Table

456

t1 CREATE TABLE `t1` (

457

`a` char(10) CHARACTER SET koi8r DEFAULT NULL

442

t1 CREATE TABLE "t1" (

443

"a" char(10) CHARACTER SET koi8r

458

444

) ENGINE=MyISAM DEFAULT CHARSET=latin1

459

445

alter table t1 DEFAULT CHARACTER SET latin1;

460

446

show create table t1;

461

447

Table Create Table

462

t1 CREATE TABLE `t1` (

463

`a` char(10) CHARACTER SET koi8r DEFAULT NULL

448

t1 CREATE TABLE "t1" (

449

"a" char(10) CHARACTER SET koi8r

464

450

) ENGINE=MyISAM DEFAULT CHARSET=latin1

465

451

alter table t1 CONVERT TO CHARACTER SET latin1;

466

452

show create table t1;

467

453

Table Create Table

468

t1 CREATE TABLE `t1` (

469

`a` char(10) DEFAULT NULL

454

t1 CREATE TABLE "t1" (

455

"a" char(10)

470

456

) ENGINE=MyISAM DEFAULT CHARSET=latin1

471

457

alter table t1 DEFAULT CHARACTER SET cp1251;

472

458

show create table t1;

473

459

Table Create Table

474

t1 CREATE TABLE `t1` (

475

`a` char(10) CHARACTER SET latin1 DEFAULT NULL

460

t1 CREATE TABLE "t1" (

461

"a" char(10) CHARACTER SET latin1

476

462

) ENGINE=MyISAM DEFAULT CHARSET=cp1251

477

463

drop table t1;

478

464

create table t1 (myblob longblob,mytext longtext)

479

465

default charset latin1 collate latin1_general_cs;

480

466

show create table t1;

481

467

Table Create Table

482

t1 CREATE TABLE `t1` (

483

`myblob` longblob,

484

`mytext` longtext COLLATE latin1_general_cs

468

t1 CREATE TABLE "t1" (

469

"myblob" longblob,

470

"mytext" longtext COLLATE latin1_general_cs

485

471

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs

486

472

alter table t1 character set latin2;

487

473

show create table t1;

488

474

Table Create Table

489

t1 CREATE TABLE `t1` (

490

`myblob` longblob,

491

`mytext` longtext CHARACTER SET latin1 COLLATE latin1_general_cs

475

t1 CREATE TABLE "t1" (

476

"myblob" longblob,

477

"mytext" longtext CHARACTER SET latin1 COLLATE latin1_general_cs

492

478

) ENGINE=MyISAM DEFAULT CHARSET=latin2

493

479

drop table t1;

494

480

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

495

481

ALTER TABLE t1 DROP PRIMARY KEY;

496

482

SHOW CREATE TABLE t1;

497

483

Table Create Table

498

t1 CREATE TABLE `t1` (

499

`a` int(11) NOT NULL,

500

`b` int(11) DEFAULT NULL,

501

UNIQUE KEY `b` (`b`)

484

t1 CREATE TABLE "t1" (

485

"a" int(11) NOT NULL,

486

"b" int(11),

487

UNIQUE KEY "b" ("b")

502

488

) ENGINE=MyISAM DEFAULT CHARSET=latin1

503

489

ALTER TABLE t1 DROP PRIMARY KEY;

504

490

ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists

667

653

CREATE INDEX i1 ON t1(a);

668

654

SHOW CREATE TABLE t1;

669

655

Table Create Table

670

t1 CREATE TABLE `t1` (

671

`a` int(11) DEFAULT NULL,

672

KEY `i1` (`a`)

656

t1 CREATE TABLE "t1" (

657

"a" int(11),

658

KEY "i1" ("a")

673

659

) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED

674

660

DROP INDEX i1 ON t1;

675

661

SHOW CREATE TABLE t1;

676

662

Table Create Table

677

t1 CREATE TABLE `t1` (

678

`a` int(11) DEFAULT NULL

663

t1 CREATE TABLE "t1" (

664

"a" int(11)

679

665

) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED

680

666

DROP TABLE t1;

681

667

DROP TABLE IF EXISTS bug24219;

797

783

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(section + 12)' at line 1

798

784

alter table table_24562 order by length(title);

799

785

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(title)' at line 1

800

alter table table_24562 order by (select 12 from dual);

801

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select 12 from dual)' at line 1

802

786

alter table table_24562 order by no_such_col;

803

787

ERROR 42S22: Unknown column 'no_such_col' in 'order clause'

804

788

drop table table_24562;

806

790

alter table t1 alter column mycol set default 0;

807

791

desc t1;

808

792

Field Type Null Key Default Extra

809

mycol int(10) NO 0

793

mycol int(10) NO NULL

810

794

drop table t1;

811

795

create table t1(id int(8) primary key auto_increment) engine=heap;

812

796

insert into t1 values (null);

832

816

50

833

817

51

834

818

drop table t1;

835

set @orig_sql_mode = @@sql_mode;

836

set sql_mode="no_zero_date";

837

create table t1(f1 int);

838

alter table t1 add column f2 datetime not null, add column f21 date not null;

839

insert into t1 values(1,'2000-01-01','2000-01-01');

840

alter table t1 add column f3 datetime not null;

841

ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'f3' at row 1

842

alter table t1 add column f3 date not null;

843

ERROR 22007: Incorrect date value: '0000-00-00' for column 'f3' at row 1

844

alter table t1 add column f4 datetime not null default '2002-02-02',

845

add column f41 date not null;

846

ERROR 22007: Incorrect date value: '0000-00-00' for column 'f41' at row 1

847

alter table t1 add column f4 datetime not null default '2002-02-02',

848

add column f41 date not null default '2002-02-02';

849

select * from t1;

850

f1 f2 f21 f4 f41

851

1 2000-01-01 00:00:00 2000-01-01 2002-02-02 00:00:00 2002-02-02

852

drop table t1;

853

set sql_mode= @orig_sql_mode;

854

819

create table t1 (v varchar(32));

855

820

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

856

821

select * from t1;

910

875

engine=myisam default charset=latin1;

911

876

alter table t1 change t t text;

912

877

drop table t1;

913

CREATE TABLE t1 (a varchar(500));

914

ALTER TABLE t1 ADD b GEOMETRY NOT NULL, ADD SPATIAL INDEX(b);

915

SHOW CREATE TABLE t1;

916

Table Create Table

917

t1 CREATE TABLE `t1` (

918

`a` varchar(500) DEFAULT NULL,

919

`b` geometry NOT NULL,

920

SPATIAL KEY `b` (`b`)

921

) ENGINE=MyISAM DEFAULT CHARSET=latin1

922

ALTER TABLE t1 ADD KEY(b(50));

923

SHOW CREATE TABLE t1;

924

Table Create Table

925

t1 CREATE TABLE `t1` (

926

`a` varchar(500) DEFAULT NULL,

927

`b` geometry NOT NULL,

928

SPATIAL KEY `b` (`b`),

929

KEY `b_2` (`b`(50))

930

) ENGINE=MyISAM DEFAULT CHARSET=latin1

931

ALTER TABLE t1 ADD c POINT;

932

SHOW CREATE TABLE t1;

933

Table Create Table

934

t1 CREATE TABLE `t1` (

935

`a` varchar(500) DEFAULT NULL,

936

`b` geometry NOT NULL,

937

`c` point DEFAULT NULL,

938

SPATIAL KEY `b` (`b`),

939

KEY `b_2` (`b`(50))

940

) ENGINE=MyISAM DEFAULT CHARSET=latin1

941

CREATE TABLE t2 (a INT, KEY (a(20)));

942

ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

943

ALTER TABLE t1 ADD d INT;

944

ALTER TABLE t1 ADD KEY (d(20));

945

ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

946

ALTER TABLE t1 ADD e GEOMETRY NOT NULL, ADD SPATIAL KEY (e(30));

947

ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

948

DROP TABLE t1;

949

878

CREATE TABLE t1 (s CHAR(8) BINARY);

950

879

INSERT INTO t1 VALUES ('test');

951

880

SELECT LENGTH(s) FROM t1;

1051

980

ERROR 42S01: Table 'tt+2' already exists

1052

981

SHOW CREATE TABLE `tt+1`;

1053

982

Table Create Table

1054

tt+1 CREATE TEMPORARY TABLE `tt+1` (

1055

`c1` int(11) DEFAULT NULL

983

tt+1 CREATE TEMPORARY TABLE "tt+1" (

984

"c1" int(11)

1056

985

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1057

986

SHOW CREATE TABLE `tt+2`;

1058

987

Table Create Table

1059

tt+2 CREATE TEMPORARY TABLE `tt+2` (

1060

`c1` int(11) DEFAULT NULL

988

tt+2 CREATE TEMPORARY TABLE "tt+2" (

989

"c1" int(11)

1061

990

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1062

991

DROP TABLE `tt+1`, `tt+2`;

1063

CREATE TABLE `#sql1` (c1 INT);

1064

CREATE TABLE `@0023sql2` (c1 INT);

1065

SHOW TABLES;

1066

Tables_in_test

1067

#sql1

1068

@0023sql2

1069

RENAME TABLE `#sql1` TO `@0023sql1`;

1070

RENAME TABLE `@0023sql2` TO `#sql2`;

1071

SHOW TABLES;

1072

Tables_in_test

1073

#sql2

1074

@0023sql1

1075

ALTER TABLE `@0023sql1` RENAME `#sql-1`;

1076

ALTER TABLE `#sql2` RENAME `@0023sql-2`;

1077

SHOW TABLES;

1078

Tables_in_test

1079

#sql-1

1080

@0023sql-2

1081

INSERT INTO `#sql-1` VALUES (1);

1082

INSERT INTO `@0023sql-2` VALUES (2);

1083

DROP TABLE `#sql-1`, `@0023sql-2`;

1084

992

CREATE TEMPORARY TABLE `#sql1` (c1 INT);

1085

993

CREATE TEMPORARY TABLE `@0023sql2` (c1 INT);

1086

994

SHOW TABLES;

1093

1001

INSERT INTO `@0023sql1` VALUES (2);

1094

1002

SHOW CREATE TABLE `#sql2`;

1095

1003

Table Create Table

1096

#sql2 CREATE TEMPORARY TABLE `#sql2` (

1097

`c1` int(11) DEFAULT NULL

1004

#sql2 CREATE TEMPORARY TABLE "#sql2" (

1005

"c1" int(11)

1098

1006

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1099

1007

SHOW CREATE TABLE `@0023sql1`;

1100

1008

Table Create Table

1101

@0023sql1 CREATE TEMPORARY TABLE `@0023sql1` (

1102

`c1` int(11) DEFAULT NULL

1009

@0023sql1 CREATE TEMPORARY TABLE "@0023sql1" (

1010

"c1" int(11)

1103

1011

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1104

1012

DROP TABLE `#sql2`, `@0023sql1`;

1105

1013

DROP TABLE IF EXISTS t1;