~drizzle-trunk/drizzle/development

drop table if exists t1,t2,t3;

SET SQL_WARNINGS=1;

CREATE TABLE t1 (

auto int NOT NULL auto_increment,

string char(10) default "hello",

tiny int DEFAULT '0' NOT NULL ,

short int DEFAULT '1' NOT NULL ,

medium int DEFAULT '0' NOT NULL,

long_int int DEFAULT '0' NOT NULL,

longlong bigint DEFAULT '0' NOT NULL,

real_float float DEFAULT 0.0 NOT NULL,

real_double double,

utiny int DEFAULT '0' NOT NULL,

ushort int DEFAULT '00000' NOT NULL,

umedium int DEFAULT '0' NOT NULL,

ulong int DEFAULT '0' NOT NULL,

ulonglong bigint DEFAULT '0' NOT NULL,

time_stamp timestamp,

date_field date,	

date_time datetime,

blob_col blob,

tinyblob_col tinyblob,

mediumblob_col mediumblob  not null default '',

longblob_col longblob  not null default '',

options enum('one','two','tree') not null ,

flags enum('one','two','tree') not null default 'one',

PRIMARY KEY (auto),

KEY (utiny),

KEY (tiny),

KEY (short),

KEY any_name (medium),

KEY (longlong),

KEY (real_float),

KEY (ushort),

KEY (umedium),

KEY (ulong),

KEY (ulonglong,ulong),

KEY (options,flags)

);

show fields from t1;

Field	Type	Null	Default	Default_is_NULL	On_Update

auto	INTEGER	NO		NO	

string	VARCHAR	YES	hello	NO	

tiny	INTEGER	NO	0	NO	

short	INTEGER	NO	1	NO	

medium	INTEGER	NO	0	NO	

long_int	INTEGER	NO	0	NO	

longlong	BIGINT	NO	0	NO	

real_float	DOUBLE	NO	0.0	NO	

real_double	DOUBLE	YES		YES	

utiny	INTEGER	NO	0	NO	

ushort	INTEGER	NO	00000	NO	

umedium	INTEGER	NO	0	NO	

ulong	INTEGER	NO	0	NO	

ulonglong	BIGINT	NO	0	NO	

time_stamp	TIMESTAMP	YES		YES	

date_field	DATE	YES		YES	

date_time	DATETIME	YES		YES	

blob_col	BLOB	YES		YES	

tinyblob_col	BLOB	YES		YES	

mediumblob_col	BLOB	NO		NO	

longblob_col	BLOB	NO		NO	

options	ENUM	NO		NO	

flags	ENUM	NO	one	NO	

show keys from t1;

Table	Unique	Key_name	Seq_in_index	Column_name

t1	YES	PRIMARY	1	auto

t1	NO	utiny	1	utiny

t1	NO	tiny	1	tiny

t1	NO	short	1	short

t1	NO	any_name	1	medium

t1	NO	longlong	1	longlong

t1	NO	real_float	1	real_float

t1	NO	ushort	1	ushort

t1	NO	umedium	1	umedium

t1	NO	ulong	1	ulong

t1	NO	ulonglong	1	ulonglong

t1	NO	ulonglong	2	ulong

t1	NO	options	1	options

t1	NO	options	2	flags

CREATE UNIQUE INDEX test on t1 ( auto ) ;

CREATE INDEX test2 on t1 ( ulonglong,ulong) ;

CREATE INDEX test3 on t1 ( medium ) ;

DROP INDEX test ON t1;

insert into t1 values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,NULL,NULL,1,1,1,1,'one','one');

insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,2,2,'two','one');

insert into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','19970303101010','','','','3',3,3);

ERROR 22001: Data too long for column 'string' at row 1

insert into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,19970403090807,-1,-1,-1,'-1',-1,-1);

ERROR HY000: Received an invalid enum value '-1'.

insert into t1 values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,NULL,0,0,-4294967295,-4294967295,-4294967295,'-4294967295',0,"tree");

ERROR 22001: Data too long for column 'string' at row 1

insert into t1 values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,NULL,0,0,4294967295,4294967295,4294967295,'4294967295',0,0);

ERROR 22003: Out of range value for column 'tiny' at row 1

insert into t1 (tiny) values (1);

select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,utiny,ushort,umedium,ulong,ulonglong,mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000),date_field,date_time,blob_col,tinyblob_col,mediumblob_col,longblob_col from t1;

auto	string	tiny	short	medium	long_int	longlong	real_float	real_double	utiny	ushort	umedium	ulong	ulonglong	mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000)	date_field	date_time	blob_col	tinyblob_col	mediumblob_col	longblob_col

10	1	1	1	1	1	1	1	1	1	1	1	1	1	NULL	NULL	NULL	1	1	1	1

11	2	2	2	2	2	2	2	2	2	2	2	2	2	NULL	NULL	NULL	NULL	NULL	2	2

12	hello	1	1	0	0	0	0	NULL	0	0	0	0	0	NULL	NULL	NULL	NULL	NULL		

CREATE TABLE t2 (

auto int NOT NULL auto_increment,

string char(20),

mediumblob_col mediumblob not null,

new_field char(2),

PRIMARY KEY (auto)

);

INSERT INTO t2 (string,mediumblob_col) SELECT string,mediumblob_col from t1 where auto > 10;

select * from t2;

auto	string	mediumblob_col	new_field

1	2	2	NULL

2	hello		NULL

select distinct flags from t1;

flags

one

select flags from t1 where find_in_set("two",flags)>0;

flags

select flags from t1 where find_in_set("unknown",flags)>0;

flags

select options,flags from t1 where options="ONE" and flags="ONE";

options	flags

one	one

one	one

select options,flags from t1 where options="one" and flags="one";

options	flags

one	one

one	one

drop table t2;

create table t2 select * from t1;

update t2 set string="changed" where auto=16;

show columns from t1;

Field	Type	Null	Default	Default_is_NULL	On_Update

auto	INTEGER	NO		NO	

string	VARCHAR	YES	hello	NO	

tiny	INTEGER	NO	0	NO	

short	INTEGER	NO	1	NO	

medium	INTEGER	NO	0	NO	

long_int	INTEGER	NO	0	NO	

longlong	BIGINT	NO	0	NO	

real_float	DOUBLE	NO	0	NO	

real_double	DOUBLE	YES		YES	

utiny	INTEGER	NO	0	NO	

ushort	INTEGER	NO	0	NO	

umedium	INTEGER	NO	0	NO	

ulong	INTEGER	NO	0	NO	

ulonglong	BIGINT	NO	0	NO	

time_stamp	TIMESTAMP	YES		YES	

date_field	DATE	YES		YES	

date_time	DATETIME	YES		YES	

blob_col	BLOB	YES		YES	

tinyblob_col	BLOB	YES		YES	

mediumblob_col	BLOB	NO		NO	

longblob_col	BLOB	NO		NO	

options	ENUM	NO		NO	

flags	ENUM	NO	one	NO	

show columns from t2;

Field	Type	Null	Default	Default_is_NULL	On_Update

auto	INTEGER	NO	0	NO	

string	VARCHAR	YES	hello	NO	

tiny	INTEGER	NO	0	NO	

short	INTEGER	NO	1	NO	

medium	INTEGER	NO	0	NO	

long_int	INTEGER	NO	0	NO	

longlong	BIGINT	NO	0	NO	

real_float	DOUBLE	NO	0	NO	

real_double	DOUBLE	YES		YES	

utiny	INTEGER	NO	0	NO	

ushort	INTEGER	NO	0	NO	

umedium	INTEGER	NO	0	NO	

ulong	INTEGER	NO	0	NO	

ulonglong	BIGINT	NO	0	NO	

time_stamp	TIMESTAMP	YES		YES	

date_field	DATE	YES		YES	

date_time	DATETIME	YES		YES	

blob_col	BLOB	YES		YES	

tinyblob_col	BLOB	YES		YES	

mediumblob_col	BLOB	NO		NO	

longblob_col	BLOB	NO		NO	

options	ENUM	NO		NO	

flags	ENUM	NO	one	NO	

select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and (t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and (t1.longlong is not null or t2.longlong is not null)) or (t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or t2.umedium is not null)) or (t1.ulong<>t2.ulong and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong and (t1.ulonglong is not null or t2.ulonglong is not null)) or (t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or t2.date_field is not null)) or (t1.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and (t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or (t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null or t2.flags is not null)));

auto	auto

select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and not (t1.string<=>t2.string and t1.tiny<=>t2.tiny and t1.short<=>t2.short and t1.medium<=>t2.medium and t1.long_int<=>t2.long_int and t1.longlong<=>t2.longlong and t1.real_float<=>t2.real_float and t1.real_double<=>t2.real_double and t1.utiny<=>t2.utiny and t1.ushort<=>t2.ushort and t1.umedium<=>t2.umedium and t1.ulong<=>t2.ulong and t1.ulonglong<=>t2.ulonglong and t1.time_stamp<=>t2.time_stamp and t1.date_field<=>t2.date_field and t1.date_time<=>t2.date_time and t1.tinyblob_col<=>t2.tinyblob_col and t1.mediumblob_col<=>t2.mediumblob_col and t1.options<=>t2.options and t1.flags<=>t2.flags);

auto	auto

drop table t2;

create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, 'a' as t2, repeat('a',256) as t3, binary repeat('b',256) as t4, repeat('a',4096) as t5, binary repeat('b',4096) as t6, '' as t7, binary '' as t8 from t1;

show columns from t2;

Field	Type	Null	Default	Default_is_NULL	On_Update

auto	BIGINT	NO		NO	

t1	INTEGER	NO		NO	

t2	VARCHAR	NO		NO	

t3	VARCHAR	YES		YES	

t4	VARBINARY	YES		YES	

t5	TEXT	YES		YES	

t6	BLOB	YES		YES	

t7	VARCHAR	NO		NO	

t8	VARBINARY	YES		YES	

select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2;

t1	t2	length(t3)	length(t4)	length(t5)	length(t6)	t7	t8

1	a	256	256	4096	4096		

1	a	256	256	4096	4096		

1	a	256	256	4096	4096		

drop table t1,t2;

create table t1 (c int);

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

create table t2 select * from t1;

create table t3 select * from t1, t2;

ERROR 42S21: Duplicate column name 'c'

create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1 CROSS JOIN t2;

show columns from t3;

Field	Type	Null	Default	Default_is_NULL	On_Update

c1	INTEGER	YES		YES	

c2	INTEGER	YES		YES	

const	INTEGER	NO		NO	

drop table t1,t2,t3;

create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield));

drop table t1;

create table t1 ( id integer not null primary key );

create table t2 ( id integer not null primary key );

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

insert into t2 values (1);

select  t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );

id_A	id_B

1	1

2	NULL

select  t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);

id_A	id_B

1	1

2	NULL

create table t3 (id_A integer not null, id_B integer null  );

insert into t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );

select * from t3;

id_A	id_B

1	1

2	NULL

truncate table t3;

insert into t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);

select * from t3;

id_A	id_B

1	1

2	NULL

drop table t3;

create table t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );

select * from t3;

id_A	id_B

1	1

2	NULL

drop table t3;

create table t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);

select * from t3;

id_A	id_B

1	1

2	NULL

drop table t1,t2,t3;