~drizzle-trunk/drizzle/development

# This test can't be run with running server (--extern) as this uses

# load_file() on a file in the tree.

#

#

# Basic cleanup

#

--disable_warnings

drop table if exists t1,t2,t3,t4,t5,t6,t7;

--enable_warnings

#

# Check syntax for creating BLOB/TEXT

#

CREATE TABLE t1 (a blob, b text, c blob, d text, e text);

show columns from t1;

# PS doesn't give errors on prepare yet

CREATE TABLE t2 (a varchar(255), b blob, c blob);

CREATE TABLE t4 (c varchar(16383) not null);

show columns from t2;

create table t3 (a int, b int);

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show create TABLE t3;

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show create TABLE t4;

drop table t1,t2,t3,t4;

#

# Check errors with blob

#

--error 1101

CREATE TABLE t1 (a blob default "hello");

CREATE TABLE t2 (a varchar(256));

drop table t2;

--error 1074

CREATE TABLE t1 (a varchar(70000) default "hello");

--error 1101

CREATE TABLE t2 (a blob default "hello");

# Safety to be able to continue with other tests if above fails

--disable_warnings

drop table if exists t1,t2;

--enable_warnings

#

# test of full join with blob

#

create table t1 (nr int not null auto_increment,b blob,str char(10), primary key (nr));

insert into t1 values (null,"a","A");

insert into t1 values (null,"bbb","BBB");

insert into t1 values (null,"ccc","CCC");

select last_insert_id();

select * from t1,t1 as t2;

drop table t1;

#

# Test of changing TEXT column

#

create table t1 (a text);

insert into t1 values ('where');

update t1 set a='Where'; 

select * from t1;

drop table t1;

#

# test of blob, text, and char

#

create table t1 (t text,c varchar(10),b blob, d blob);

insert into t1 values (NULL,NULL,NULL,NULL);

insert into t1 values ("","","","");

insert into t1 values ("hello","hello","hello","hello");

insert into t1 values ("HELLO","HELLO","HELLO","HELLO");

insert into t1 values ("HELLO MY","HELLO MY","HELLO MY","HELLO MY");

insert into t1 values ("a","a","a","a");

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

insert into t1 values (NULL,NULL,NULL,NULL);

update t1 set c="",b=null where c="1";

select t from t1 where t like "hello";

select c from t1 where c like "hello";

select b from t1 where b like "hello";

select d from t1 where d like "hello";

select c from t1 having c like "hello";

select d from t1 having d like "hello";

select t from t1 where t like "%HELLO%";

select c from t1 where c like "%HELLO%";

select b from t1 where b like "%HELLO%";

select d from t1 where d like "%HELLO%";

select c from t1 having c like "%HELLO%";

select d from t1 having d like "%HELLO%";

select d from t1 having d like "%HE%LLO%";

select t from t1 order by t;

select c from t1 order by c;

select b from t1 order by b;

select d from t1 order by d;

select distinct t from t1;

select distinct b from t1;

select distinct t from t1 order by t;

select distinct b from t1 order by b;

select t from t1 group by t;

select b from t1 group by b;

select distinct t from t1;

select distinct b from t1;

select distinct t from t1 order by t;

select distinct b from t1 order by b;

select distinct c from t1;

select distinct d from t1;

select distinct c from t1 order by c;

select distinct d from t1 order by d;

select c from t1 group by c;

select d from t1 group by d;

select distinct * from t1;

select t,count(*) from t1 group by t;

select b,count(*) from t1 group by b;

select c,count(*) from t1 group by c;

select d,count(*) from t1 group by d;

drop table t1;

-- error 1071

create table t1 (a text, unique (a(2100))); # should give an error

create table t1 (a text, key (a(2100)));    # key is auto-truncated

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show create table t1;

drop table t1;

#

# Test of join with blobs and min

#

CREATE TABLE t1 (

  t1_id bigint NOT NULL auto_increment,

  _field_72 varchar(128) DEFAULT '' NOT NULL,

  _field_95 varchar(32),

  _field_115 int DEFAULT '0' NOT NULL,

  _field_122 int DEFAULT '0' NOT NULL,

  _field_126 int,

  _field_134 int,

  PRIMARY KEY (t1_id),

  UNIQUE _field_72 (_field_72),

  KEY _field_115 (_field_115),

  KEY _field_122 (_field_122)

);

INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL);

INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL);

INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL);

CREATE TABLE t2 (

  seq_0_id bigint DEFAULT '0' NOT NULL,

  seq_1_id bigint DEFAULT '0' NOT NULL,

  PRIMARY KEY (seq_0_id,seq_1_id)

);

INSERT INTO t2 VALUES (1,1);

INSERT INTO t2 VALUES (2,1);

INSERT INTO t2 VALUES (2,2);

CREATE TABLE t3 (

  t3_id bigint NOT NULL auto_increment,

  _field_131 varchar(128),

  _field_133 int DEFAULT '0' NOT NULL,

  _field_135 datetime,

  _field_137 int,

  _field_139 datetime,

  _field_140 blob,

  _field_142 int DEFAULT '0' NOT NULL,

  _field_145 int DEFAULT '0' NOT NULL,

  _field_148 int DEFAULT '0' NOT NULL,

  PRIMARY KEY (t3_id),

  KEY _field_133 (_field_133),

  KEY _field_135 (_field_135),

  KEY _field_139 (_field_139),

  KEY _field_142 (_field_142),

  KEY _field_145 (_field_145),

  KEY _field_148 (_field_148)

);

INSERT INTO t3 VALUES (1,'test job 1',0,NULL,0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0);

INSERT INTO t3 VALUES (2,'test job 2',0,NULL,0,'1999-02-26 21:08:04','',0,0,0);

CREATE TABLE t4 (

  seq_0_id bigint DEFAULT '0' NOT NULL,

  seq_1_id bigint DEFAULT '0' NOT NULL,

  PRIMARY KEY (seq_0_id,seq_1_id)

);

INSERT INTO t4 VALUES (1,1);

INSERT INTO t4 VALUES (2,1);

CREATE TABLE t5 (

  t5_id bigint NOT NULL auto_increment,

  _field_149 int,

  _field_156 varchar(128) DEFAULT '' NOT NULL,

  _field_157 varchar(128) DEFAULT '' NOT NULL,

  _field_158 varchar(128) DEFAULT '' NOT NULL,

  _field_159 varchar(128) DEFAULT '' NOT NULL,

  _field_160 varchar(128) DEFAULT '' NOT NULL,

  _field_161 varchar(128) DEFAULT '' NOT NULL,

  PRIMARY KEY (t5_id),

  KEY _field_156 (_field_156),

  KEY _field_157 (_field_157),

  KEY _field_158 (_field_158),

  KEY _field_159 (_field_159),

  KEY _field_160 (_field_160),

  KEY _field_161 (_field_161)

);

INSERT INTO t5 VALUES (1,0,'tomato','','','','','');

INSERT INTO t5 VALUES (2,0,'cilantro','','','','','');

CREATE TABLE t6 (

  seq_0_id bigint DEFAULT '0' NOT NULL,

  seq_1_id bigint DEFAULT '0' NOT NULL,

  PRIMARY KEY (seq_0_id,seq_1_id)

);

INSERT INTO t6 VALUES (1,1);

INSERT INTO t6 VALUES (1,2);

INSERT INTO t6 VALUES (2,2);

CREATE TABLE t7 (

  t7_id bigint NOT NULL auto_increment,

  _field_143 int,

  _field_165 varchar(32),

  _field_166 int DEFAULT '0' NOT NULL,

  PRIMARY KEY (t7_id),

  KEY _field_166 (_field_166)

);

INSERT INTO t7 VALUES (1,0,'High',1);

INSERT INTO t7 VALUES (2,0,'Medium',2);

INSERT INTO t7 VALUES (3,0,'Low',3);

select replace(t3._field_140, "\r","^M"),t3_id,min(t3._field_131), min(t3._field_135), min(t3._field_139), min(t3._field_137), min(link_alias_142._field_165), min(link_alias_133._field_72), min(t3._field_145), min(link_alias_148._field_156), replace(min(t3._field_140), "\r","^M"),t3.t3_id from t3 left join t4 on t4.seq_0_id = t3.t3_id left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id left join t6 on t6.seq_0_id = t3.t3_id left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id left join t2 on t2.seq_0_id = t3.t3_id left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id where t3.t3_id in (1) group by t3.t3_id order by link_alias_142._field_166, _field_139, link_alias_133._field_72, _field_135, link_alias_148._field_156;

drop table t1,t2,t3,t4,t5,t6,t7;

#

# Test of reverse with empty blob

#

create table t1 (a blob);

insert into t1 values ("empty"),("");

select a,reverse(a) from t1;

drop table t1;

#

# Test of BLOB:s with NULL keys.

#

create table t1 (a blob, key (a(10)));

insert into t1 values ("bye"),("hello"),("hello"),("hello word");

select * from t1 where a like "hello%";

drop table t1;

#

# Test of found bug in group on text key

#

CREATE TABLE t1 (

       f1 int DEFAULT '0' NOT NULL,

       f2 varchar(16) DEFAULT '' NOT NULL,

       f5 text,

       KEY index_name (f1,f2,f5(16))

    );

INSERT INTO t1 VALUES (0,'traktor','1111111111111');

INSERT INTO t1 VALUES (1,'traktor','1111111111111111111111111');

select count(*) from t1 where f2='traktor';

drop table t1;

#

# Test of found bug when blob is first key part

#

create table t1 (foobar tinyblob not null, boggle int not null, key (foobar(32), boggle));

insert into t1 values ('fish', 10),('bear', 20);

select foobar, boggle from t1 where foobar = 'fish';

select foobar, boggle from t1 where foobar = 'fish' and boggle = 10;

drop table t1;

#

# Test blob's with end space (Bug #1651)

# This is a bit changed since we now have true varchar

#

#FIXME: The following test needs to be fixed for Drizzle

#create table t1 (id integer primary key auto_increment, txt text not null, unique index txt_index (txt (20)));

#--error ER_DUP_ENTRY

#insert into t1 (txt) values ('Chevy'), ('Chevy ');

#--error ER_DUP_ENTRY

#insert into t1 (txt) values ('Chevy'), ('CHEVY');

#alter table t1 drop index txt_index, add index txt_index (txt(20));

#insert into t1 (txt) values ('Chevy ');

#select * from t1 where txt='Chevy';

#select * from t1 where txt='Chevy ';

#select * from t1 where txt='Chevy ' or txt='Chevy';

#select * from t1 where txt='Chevy' or txt='Chevy ';

#select * from t1 where id='1' or id='2';

#insert into t1 (txt) values('Ford');

#select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford';

#select * from t1 where txt='Chevy' or txt='Chevy ';

#select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy';

#select * from t1 where txt in ('Chevy ','Chevy');

#select * from t1 where txt in ('Chevy');

#select * from t1 where txt between 'Chevy' and 'Chevy';

#select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy ';

#select * from t1 where txt between 'Chevy' and 'Chevy ';

#select * from t1 where txt < 'Chevy ';

#select * from t1 where txt <= 'Chevy';

#select * from t1 where txt > 'Chevy';

#select * from t1 where txt >= 'Chevy';

#drop table t1;

create table t1 (id integer primary key auto_increment, txt text, index txt_index (txt (20)));

insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL);

select * from t1 where txt='Chevy' or txt is NULL;

explain select * from t1 where txt='Chevy' or txt is NULL;

select * from t1 where txt='Chevy ';

select * from t1 where txt='Chevy ' or txt='Chevy';

select * from t1 where txt='Chevy' or txt='Chevy ';

select * from t1 where id='1' or id='2';

insert into t1 (txt) values('Ford');

select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford';

select * from t1 where txt='Chevy' or txt='Chevy ';

select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy';

select * from t1 where txt in ('Chevy ','Chevy');

select * from t1 where txt in ('Chevy');

select * from t1 where txt between 'Chevy' and 'Chevy';

select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy ';

select * from t1 where txt between 'Chevy' and 'Chevy ';

select * from t1 where txt < 'Chevy ';

select * from t1 where txt < 'Chevy ' or txt is NULL;

select * from t1 where txt <= 'Chevy';

select * from t1 where txt > 'Chevy';

select * from t1 where txt >= 'Chevy';

alter table t1 modify column txt blob;

explain select * from t1 where txt='Chevy' or txt is NULL;

select * from t1 where txt='Chevy' or txt is NULL;

explain select * from t1 where txt='Chevy' or txt is NULL order by txt;

select * from t1 where txt='Chevy' or txt is NULL order by txt;

drop table t1;

CREATE TABLE t1 ( i int NOT NULL default '0',    c text NOT NULL, d varchar(1) NOT NULL DEFAULT ' ', PRIMARY KEY  (i), KEY (c(1),d));

INSERT t1 (i, c) VALUES (1,''),(2,''),(3,'asdfh'),(4,'');

select max(i) from t1 where c = '';

drop table t1;

# End of 4.1 tests

#

# Bug#11657: Creation of secondary index fails

#

create table t1 (a int, b int, c tinyblob, d int, e int);

alter table t1 add primary key (a,b,c(255),d);

alter table t1 add key (a,b,d,e);

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show create table t1;

drop table t1;

#

# Test that blobs are sorted according to length

#

CREATE table t1 (a blob);

insert into t1 values ('b'),('a\0'),('a'),('a '),('aa'),(NULL);

select hex(a) from t1 order by a;

select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0');

alter table t1 modify a blob;

select hex(a) from t1 order by a;

select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0');

alter table t1 modify a char(5);

select hex(a) from t1 order by a;

select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0');

drop table t1;

#

# Bug #19489: Inconsistent support for DEFAULT in TEXT columns

#

create table t1 (a text default '');

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show create table t1;

insert into t1 values (default);

select * from t1;

drop table t1;

create table t1 (a text default '');

drop table t1;

#

# Bug #32282: TEXT silently truncates when value is exactly 65536 bytes

#

CREATE TABLE t (c TEXT);

INSERT INTO t (c) VALUES (REPEAT('1',65537));

INSERT INTO t (c) VALUES (REPEAT('2',65536));

INSERT INTO t (c) VALUES (REPEAT('3',65535));

SELECT LENGTH(c), CHAR_LENGTH(c) FROM t;

DROP TABLE t;

--echo End of 5.0 tests