~drizzle-trunk/drizzle/development

create table t1 ( RID int not null default '0', IID int not null default '0', nada varchar(50) not null,NAME varchar(50) not null,PHONE varchar(50) not null);

852

insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777');

1027

show create table t1;

1028

Table Create Table

1029

t1 CREATE TABLE `t1` (

1030

`'test' collate utf8_bin` varchar(4) COLLATE utf8_bin DEFAULT NULL

1030

`'test' collate utf8_bin` varchar(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL

1031

) ENGINE=DEFAULT

1032

select count(*) from t1;

1033

count(*)

1040

show create table t1;

1041

Table Create Table

1042

t1 CREATE TABLE `t1` (

1043

`test` varchar(4) COLLATE utf8_bin DEFAULT NULL

1043

`test` varchar(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL

1044

) ENGINE=DEFAULT

1045

select count(*) from t1;

1046

count(*)

1053

show create table t1;

1054

Table Create Table

1055

t1 CREATE TABLE `t1` (

1056

`test` varchar(4) COLLATE utf8_bin DEFAULT NULL

1056

`test` varchar(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL

1057

) ENGINE=DEFAULT

1058

select count(*) from t1;

1059

count(*)

1081

show create table t1;

1082

Table Create Table

1083

t1 CREATE TABLE `t1` (

1084

`a` varchar(1) COLLATE utf8_swedish_ci DEFAULT NULL

1084

`a` varchar(1) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL

1085

) ENGINE=DEFAULT

1086

drop table t1;

1087

create table t1 as

1091

show create table t1;

1092

Table Create Table

1093

t1 CREATE TABLE `t1` (

1094

`a` varchar(1) COLLATE utf8_spanish_ci DEFAULT NULL

1094

`a` varchar(1) CHARACTER SET utf8 COLLATE utf8_spanish_ci DEFAULT NULL

1095

) ENGINE=DEFAULT

1096

drop table t1;

1097

drop table t2;

1102

select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a

1103

order by f2, a1;

1104

show columns from t2;

1105

Field Type Null Default Default_is_NULL On_Update

1106

f2 DATE TRUE TRUE

1107

a1 INTEGER TRUE TRUE

1105

Field Type Null Key Default Extra

1106

f2 date YES NULL

1107

a1 int YES NULL

1108

drop table t1, t2;

1109

create table t1 (f1 int);

1110

create table t2 (f1 int, f2 int ,f3 date);

1128

)

1129

order by sdate;

1130

show columns from t4;

1131

Field Type Null Default Default_is_NULL On_Update

1132

sdate DATE TRUE TRUE

1131

Field Type Null Key Default Extra

1132

sdate date YES NULL

1133

drop table t1, t2, t3, t4;

1134

create table t1 (a int not null, b char (10) not null);

1135

insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');

1203

t2 CREATE TABLE `t2` (

1204

`a` varchar(5) DEFAULT NULL

1205

) ENGINE=DEFAULT

1206

select row_format from data_dictionary.TABLES where table_schema="test" and table_name="t2";

1206

select row_format from information_schema.TABLES where table_schema="test" and table_name="t2";

1207

row_format

1208

DEFAULT

1208

Compact

1209

alter table t2 ROW_FORMAT=fixed;

1210

Warnings:

1211

Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

1209

1212

show create table t2;

1210

1213

Table Create Table

1211

1214

t2 CREATE TABLE `t2` (

1212

1215

`a` varchar(5) DEFAULT NULL

1213

) ENGINE=DEFAULT

1216

) ENGINE=DEFAULT ROW_FORMAT=FIXED

1214

1217

drop table t1,t2;

1215

1218

CREATE TABLE t1 (a mediumtext);

1216

1219

CREATE TABLE t2 (b varchar(20));

1438

1441

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

1439

1442

CREATE TABLE t2 SELECT * FROM (SELECT NULL) a UNION SELECT a FROM t1;

1440

1443

DESC t2;

1441

Field Type Null Default Default_is_NULL On_Update

1442

NULL INTEGER TRUE TRUE

1444

Field Type Null Key Default Extra

1445

NULL int YES NULL

1443

1446

CREATE TABLE t3 SELECT a FROM t1 UNION SELECT * FROM (SELECT NULL) a;

1444

1447

DESC t3;

1445

Field Type Null Default Default_is_NULL On_Update

1446

a INTEGER TRUE TRUE

1448

Field Type Null Key Default Extra

1449

a int YES NULL

1447

1450

CREATE TABLE t4 SELECT NULL;

1448

1451

DESC t4;

1449

Field Type Null Default Default_is_NULL On_Update

1450

NULL VARCHAR TRUE TRUE

1452

Field Type Null Key Default Extra

1453

NULL varbinary(0) YES NULL

1451

1454

CREATE TABLE t5 SELECT NULL UNION SELECT NULL;

1452

1455

DESC t5;

1453

Field Type Null Default Default_is_NULL On_Update

1454

NULL VARCHAR TRUE TRUE

1456

Field Type Null Key Default Extra

1457

NULL varbinary(0) YES NULL

1455

1458

CREATE TABLE t6

1456

1459

SELECT * FROM (SELECT * FROM (SELECT NULL)a) b UNION SELECT a FROM t1;

1457

1460

DESC t6;

1458

Field Type Null Default Default_is_NULL On_Update

1459

NULL INTEGER TRUE TRUE

1461

Field Type Null Key Default Extra

1462

NULL int YES NULL

1460

1463

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

1461

1464

End of 5.0 tests

Older »