mysql-test/main/type_blob.test

# This test can't be run with running server (--extern) as this uses
# load_file() on a file in the tree.
#
--source include/have_innodb.inc

#
# 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(250), d text(70000), e text(70000000));
show columns from t1;
# PS doesn't give errors on prepare yet
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
CREATE TABLE t2 (a char(255), b varbinary(70000), c varchar(70000000));
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
CREATE TABLE t4 (c varchar(65530) character set utf8 not null);
show columns from t2;
create table t3 (a long, b long byte);
show create TABLE t3;
show create TABLE t4;
drop table t1,t2,t3,t4;

#
# Check errors with blob
#

--error 1074
CREATE TABLE t1 (a char(257) default "hello");
--error 1074
CREATE TABLE t2 (a char(256));
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
CREATE TABLE t1 (a varchar(70000) default "hello");
SHOW CREATE TABLE t1;
CREATE TABLE t2 (a blob default "hello");
SHOW CREATE TABLE t2;

drop table t1,t2;

#
# test of full join with blob
#

create table t1 (nr int(5) 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, char and varbinary
#
create table t1 (t text,c char(10),b blob, d varbinary(10));
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";

lock tables t1 READ;
# We mask out the Privileges column because it differs for embedded server
--replace_column 8 #
show full fields from t1;
lock tables t1 WRITE;
--replace_column 8 #
show full fields from t1;
unlock tables;

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;
set tmp_memory_table_size=0; # force on-disk tmp table
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;
set tmp_memory_table_size=default;
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;

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

#
# Test of join with blobs and min
#

CREATE TABLE t1 (
  t1_id bigint(21) NOT NULL auto_increment,
  _field_72 varchar(128) DEFAULT '' NOT NULL,
  _field_95 varchar(32),
  _field_115 tinyint(4) DEFAULT '0' NOT NULL,
  _field_122 tinyint(4) DEFAULT '0' NOT NULL,
  _field_126 tinyint(4),
  _field_134 tinyint(4),
  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(21) DEFAULT '0' NOT NULL,
  seq_1_id bigint(21) 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(21) NOT NULL auto_increment,
  _field_131 varchar(128),
  _field_133 tinyint(4) DEFAULT '0' NOT NULL,
  _field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  _field_137 tinyint(4),
  _field_139 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  _field_140 blob,
  _field_142 tinyint(4) DEFAULT '0' NOT NULL,
  _field_145 tinyint(4) DEFAULT '0' NOT NULL,
  _field_148 tinyint(4) 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,'0000-00-00 00:00:00',0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0);
INSERT INTO t3 VALUES (2,'test job 2',0,'0000-00-00 00:00:00',0,'1999-02-26 21:08:04','',0,0,0);


CREATE TABLE t4 (
  seq_0_id bigint(21) DEFAULT '0' NOT NULL,
  seq_1_id bigint(21) 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(21) NOT NULL auto_increment,
  _field_149 tinyint(4),
  _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(21) DEFAULT '0' NOT NULL,
  seq_1_id bigint(21) 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(21) NOT NULL auto_increment,
  _field_143 tinyint(4),
  _field_165 varchar(32),
  _field_166 smallint(6) 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(11) 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 smallint 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;

#
# Bug when blob is updated
#

create table t1 (id integer auto_increment unique,imagem LONGBLOB not null default '');
insert into t1 (id) values (1);
# We have to clean up the path in the results for safe comparison
eval select
  charset(load_file('../../std_data/words.dat')),
  collation(load_file('../../std_data/words.dat')),
  coercibility(load_file('../../std_data/words.dat'));
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval explain extended select
  charset(load_file('$MYSQLTEST_VARDIR/std_data/words.dat')),
  collation(load_file('$MYSQLTEST_VARDIR/std_data/words.dat')),
  coercibility(load_file('$MYSQLTEST_VARDIR/std_data/words.dat'));
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval update t1 set imagem=load_file('$MYSQLTEST_VARDIR/std_data/words.dat') where id=1;
select if(imagem is null, "ERROR", "OK"),length(imagem) from t1 where id = 1;
drop table t1;
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval create table t1 select load_file('$MYSQLTEST_VARDIR/std_data/words.dat') l;
# We mask out the Privileges column because it differs for embedded server
--replace_column 8 #
show full fields from t1;
drop table t1;

#
# Test blob's with end space (Bug #1651)
# This is a bit changed since we now have true varchar
#

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), ('Honda'), ('Subaru'), ('Honda');
select * from t1 where txt='Chevy' or txt is NULL;
explain select * from t1 where txt='Chevy' or txt is NULL;
explain select * from t1 FORCE INDEX (`txt_index`) 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(11) 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);
show create table t1;
drop table t1;

#
# Test that blob's and varbinary 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 varbinary(5);
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');
alter table t1 modify a binary(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 '');
show create table t1;
insert into t1 values (default);
select * from t1;
drop table t1;
set @@sql_mode='TRADITIONAL';
create table t1 (a text default '');
show create table t1;
drop table t1;
set @@sql_mode='';

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

CREATE TABLE t (c TEXT CHARSET ASCII);
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;
# Bug#15776: 32-bit signed int used for length of blob
# """LONGBLOB:  A BLOB column with a maximum length of 4,294,967,295 or 4GB."""
#
# Conditions should be in this order:
# A size is not in the allowed bounds.
# If the type is char-ish AND size is within the max blob size:
#   raise ER_TOO_BIG_FIELDLENGTH  (suggest using BLOB)
# If size is too small:
#   raise ER_PARSE_ERROR
# raise ER_TOO_BIG_DISPLAYWIDTH

# BLOB and TEXT types
--disable_warnings
drop table if exists b15776;
--enable_warnings
create table b15776 (data blob(2147483647));
drop table b15776;
--error ER_PARSE_ERROR
create table b15776 (data blob(-1));
create table b15776 (data blob(2147483648));
drop table b15776;
create table b15776 (data blob(4294967294));
drop table b15776;
create table b15776 (data blob(4294967295));
drop table b15776;
--error ER_TOO_BIG_DISPLAYWIDTH
create table b15776 (data blob(4294967296));

CREATE TABLE b15776 (a blob(2147483647), b blob(2147483648), c blob(4294967295), a1 text(2147483647), b1 text(2147483648), c1 text(4294967295) );
show columns from b15776;
drop table b15776;

--error ER_TOO_BIG_DISPLAYWIDTH
CREATE TABLE b15776 (a blob(4294967296));
--error ER_TOO_BIG_DISPLAYWIDTH
CREATE TABLE b15776 (a text(4294967296));
--error ER_TOO_BIG_DISPLAYWIDTH
CREATE TABLE b15776 (a blob(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
--error ER_TOO_BIG_DISPLAYWIDTH
CREATE TABLE b15776 (a text(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));

# Int types
# "Another extension is supported by MySQL for optionally specifying the
# display width of integer data types in parentheses following the base keyword
# for the type (for example, INT(4)). This optional display width is used to
# display integer values having a width less than the width specified for the
# column by left-padding them with spaces."   § Numeric Types
CREATE TABLE b15776 (a int(0)); # 0 is special case, means default size
INSERT INTO b15776 values (NULL), (1), (42), (654);
SELECT * from b15776 ORDER BY a;
DROP TABLE b15776;
--error ER_PARSE_ERROR
CREATE TABLE b15776 (a int(-1));
CREATE TABLE b15776 (a int(255));
DROP TABLE b15776;
--error ER_TOO_BIG_DISPLAYWIDTH
CREATE TABLE b15776 (a int(256));
--error ER_PARSE_ERROR
CREATE TABLE b15776 (data blob(-1));

# Char types
# Recommend BLOB
--error ER_TOO_BIG_FIELDLENGTH
CREATE TABLE b15776 (a char(2147483647));
--error ER_TOO_BIG_FIELDLENGTH
CREATE TABLE b15776 (a char(2147483648));
--error ER_TOO_BIG_FIELDLENGTH
CREATE TABLE b15776 (a char(4294967295));
# Even BLOB won't hold
--error ER_TOO_BIG_FIELDLENGTH
CREATE TABLE b15776 (a char(4294967296));


# Other numeric-ish types
## For year, widths not "2" or "4" are silently rewritten to "4".  But
## When we complain about it, we say that the max is 255.  We may be
## talking about different things.  It's confusing.
--replace_result 4294967295 ? 0 ?
CREATE TABLE b15776 (a year(4294967295));
INSERT INTO b15776 VALUES (42);
SELECT * FROM b15776;
DROP TABLE b15776;
CREATE TABLE b15776 (a year(4294967296));
SHOW CREATE TABLE b15776;
DROP TABLE b15776;
CREATE TABLE b15776 (a year(0));  # 0 is special case, means default size
DROP TABLE b15776;
--error ER_PARSE_ERROR
CREATE TABLE b15776 (a year(-2));

## For timestamp, we silently rewrite widths to 14 or 19.
--error ER_TOO_BIG_PRECISION
CREATE TABLE b15776 (a timestamp(4294967294));
--error ER_TOO_BIG_PRECISION
CREATE TABLE b15776 (a timestamp(4294967295));
--error ER_TOO_BIG_PRECISION
CREATE TABLE b15776 (a timestamp(4294967296));
--error ER_PARSE_ERROR
CREATE TABLE b15776 (a timestamp(-1));
--error ER_PARSE_ERROR
CREATE TABLE b15776 (a timestamp(-2));

# We've already tested the case, but this should visually show that
# widths that are too large to be interpreted cause DISPLAYWIDTH errors.
--error ER_TOO_BIG_DISPLAYWIDTH
CREATE TABLE b15776 (a int(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
--error ER_TOO_BIG_FIELDLENGTH
CREATE TABLE b15776 (a char(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
CREATE TABLE b15776 (a year(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
SHOW CREATE TABLE b15776;
DROP TABLE b15776;

## Do not select, too much memory needed.
CREATE TABLE b15776 select cast(null as char(4294967295));
show columns from b15776;
drop table b15776;
CREATE TABLE b15776 select cast(null as nchar(4294967295));
show columns from b15776;
drop table b15776;
CREATE TABLE b15776 select cast(null as binary(4294967295));
show columns from b15776;
drop table b15776;

explain select cast(1 as char(4294967295));
explain select cast(1 as nchar(4294967295));
explain select cast(1 as binary(4294967295));

--error ER_TOO_BIG_DISPLAYWIDTH
explain select cast(1 as char(4294967296));
--error ER_TOO_BIG_DISPLAYWIDTH
explain select cast(1 as nchar(4294967296));
--error ER_TOO_BIG_DISPLAYWIDTH
explain select cast(1 as binary(4294967296));

--error ER_PARSE_ERROR
explain select cast(1 as decimal(-1));
explain select cast(1 as decimal(64, 30));
# It's not as important which errors are raised for these, since the
# limit is nowhere near 2**32.  We may fix these eventually to take
# 4294967295 and still reject it because it's greater than 64 or 30,
# but that's not a high priority and the parser needn't worry about
# such a weird case.
--error ER_TOO_BIG_SCALE,ER_PARSE_ERROR
explain select cast(1 as decimal(64, 999999999999999999999999999999));
--error ER_TOO_BIG_PRECISION,ER_PARSE_ERROR
explain select cast(1 as decimal(4294967296));
--error ER_TOO_BIG_PRECISION,ER_PARSE_ERROR
explain select cast(1 as decimal(999999999999999999999999999999999999));

explain select convert(1, char(4294967295));
--error ER_TOO_BIG_DISPLAYWIDTH
explain select convert(1, char(4294967296));
--error ER_TOO_BIG_DISPLAYWIDTH
explain select convert(1, char(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
explain select convert(1, nchar(4294967295));
--error ER_TOO_BIG_DISPLAYWIDTH
explain select convert(1, nchar(4294967296));
--error ER_TOO_BIG_DISPLAYWIDTH
explain select convert(1, nchar(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
explain select convert(1, binary(4294967295));
--error ER_TOO_BIG_DISPLAYWIDTH
explain select convert(1, binary(4294967296));
--error ER_TOO_BIG_DISPLAYWIDTH
explain select convert(1, binary(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));

--echo End of 5.0 tests

#
# Bug #33969: Updating a text field via a left join
#

CREATE TABLE t1(id INT NOT NULL);
CREATE TABLE t2(id INT NOT NULL, c TEXT NOT NULL);

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

UPDATE t2 SET c = REPEAT('1', 70000);
SELECT LENGTH(c) FROM t2;

UPDATE t1 LEFT JOIN t2 USING(id) SET t2.c = REPEAT('1', 70000) WHERE t1.id = 1;
SELECT LENGTH(c) FROM t2;

DROP TABLE t1, t2;

--echo # Bug #52160: crash and inconsistent results when grouping
--echo #             by a function and column

CREATE FUNCTION f1() RETURNS TINYBLOB RETURN 1;

CREATE TABLE t1(a CHAR(1));
INSERT INTO t1 VALUES ('0'), ('0');

SELECT COUNT(*) FROM t1 GROUP BY f1(), a;

DROP FUNCTION f1;
DROP TABLE t1;

--echo End of 5.1 tests

--echo #
--echo # Start of 5.5 tests
--echo #

#
# Problem when comparing blobs #778901
#

CREATE TABLE t1 ( f1 blob, f2 blob );
INSERT INTO t1 VALUES ('','');
SELECT f1,f2,"found row" FROM t1 WHERE f1 = f2 ;
DROP TABLE t1;

--echo #
--echo # MDEV-9319 ALTER from a bigger to a smaller blob type truncates too much data
--echo #
CREATE TABLE t1 (a MEDIUMBLOB);
INSERT INTO t1 VALUES (REPEAT(0x61,128000));
SELECT LENGTH(a) FROM t1;
ALTER TABLE t1 MODIFY a BLOB;
SELECT LENGTH(a) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a BLOB);
INSERT INTO t1 VALUES (REPEAT(0x61,65000));
SELECT LENGTH(a) FROM t1;
ALTER TABLE t1 MODIFY a TINYBLOB;
SELECT LENGTH(a) FROM t1;
DROP TABLE t1;

--echo #
--echo # End of 5.5 tests
--echo #


--echo #
--echo # Start of 10.2 test
--echo #

--echo #
--echo # MDEV-12809 Bad column type created for TEXT(1431655798) CHARACTER SET utf8
--echo #

CREATE TABLE t1 (a TEXT(1431655798) CHARACTER SET utf8);
SHOW CREATE TABLE t1;
DROP TABLE t1;

# ALTER SET DEFAULT
create table t1 (a int);
alter table t1 add column b blob, alter column b set default "foo";
show create table t1;
drop table t1;

--echo #
--echo # End of 10.2 test
--echo #


--echo #
--echo # Start of 10.4 test
--echo #

--echo #
--echo # MDEV-19317 TEXT column accepts too long literals as a default value
--echo #

EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 (a TINYTEXT DEFAULT ?)' USING REPEAT('a', 255);
INSERT INTO t1 VALUES ();
SELECT LENGTH(a), LENGTH(DEFAULT(a)) FROM t1;
DROP TABLE t1;

--error ER_INVALID_DEFAULT
EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 (a TINYTEXT DEFAULT ?)' USING REPEAT('a', 256);

--error ER_INVALID_DEFAULT
CREATE OR REPLACE TABLE t1 (a TINYTEXT DEFAULT 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');


EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 (a TEXT DEFAULT ?)' USING REPEAT('a', 256);
INSERT INTO t1 VALUES ();
SELECT LENGTH(a), LENGTH(DEFAULT(a)) FROM t1;
DROP TABLE t1;

CREATE OR REPLACE TABLE t1 (a TEXT DEFAULT 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
INSERT INTO t1 VALUES ();
SELECT LENGTH(a), LENGTH(DEFAULT(a)) FROM t1;
DROP TABLE t1;

--echo #
--echo # ASAN heap-use-after-free in my_hash_sort_bin or ER_KEY_NOT_FOUND
--echo # upon INSERT into table with long unique blob
--echo #

# Note that this test worked with myisam as it caches blobs differently than
# InnoDB

SET @save_sql_mode=@@sql_mode;
SET SQL_MODE='STRICT_ALL_TABLES';

CREATE TABLE t1 (a INT, b BLOB) ENGINE=innodb;
INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
CREATE TABLE t2 (c BIT, d BLOB, UNIQUE(d)) ENGINE=innodb;
--error ER_DATA_TOO_LONG
INSERT INTO t2 SELECT * FROM t1;
select * from t2;
DROP TABLE t1, t2;
SET @@sql_mode=@save_sql_mode;

--echo #
--echo # End of 10.4 test
--echo #