summaryrefslogtreecommitdiff
path: root/mysql-test/main/type_blob.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/type_blob.result')
-rw-r--r--mysql-test/main/type_blob.result1065
1 files changed, 1065 insertions, 0 deletions
diff --git a/mysql-test/main/type_blob.result b/mysql-test/main/type_blob.result
new file mode 100644
index 00000000000..569ba65df3f
--- /dev/null
+++ b/mysql-test/main/type_blob.result
@@ -0,0 +1,1065 @@
+drop table if exists t1,t2,t3,t4,t5,t6,t7;
+CREATE TABLE t1 (a blob, b text, c blob(250), d text(70000), e text(70000000));
+show columns from t1;
+Field Type Null Key Default Extra
+a blob YES NULL
+b text YES NULL
+c tinyblob YES NULL
+d mediumtext YES NULL
+e longtext YES NULL
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
+CREATE TABLE t2 (a char(255), b varbinary(70000), c varchar(70000000));
+Warnings:
+Note 1246 Converting column 'b' from VARBINARY to BLOB
+Note 1246 Converting column 'c' from VARCHAR to TEXT
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
+CREATE TABLE t4 (c varchar(65530) character set utf8 not null);
+Warnings:
+Note 1246 Converting column 'c' from VARCHAR to TEXT
+show columns from t2;
+Field Type Null Key Default Extra
+a char(255) YES NULL
+b mediumblob YES NULL
+c longtext YES NULL
+create table t3 (a long, b long byte);
+show create TABLE t3;
+Table Create Table
+t3 CREATE TABLE `t3` (
+ `a` mediumtext DEFAULT NULL,
+ `b` mediumblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+show create TABLE t4;
+Table Create Table
+t4 CREATE TABLE `t4` (
+ `c` mediumtext CHARACTER SET utf8 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1,t2,t3,t4;
+CREATE TABLE t1 (a char(257) default "hello");
+ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead
+CREATE TABLE t2 (a char(256));
+ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
+CREATE TABLE t1 (a varchar(70000) default "hello");
+Warnings:
+Note 1246 Converting column 'a' from VARCHAR to TEXT
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` mediumtext DEFAULT 'hello'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CREATE TABLE t2 (a blob default "hello");
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` blob DEFAULT 'hello'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1,t2;
+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();
+last_insert_id()
+3
+select * from t1,t1 as t2;
+nr b str nr b str
+1 a A 1 a A
+2 bbb BBB 1 a A
+3 ccc CCC 1 a A
+1 a A 2 bbb BBB
+2 bbb BBB 2 bbb BBB
+3 ccc CCC 2 bbb BBB
+1 a A 3 ccc CCC
+2 bbb BBB 3 ccc CCC
+3 ccc CCC 3 ccc CCC
+drop table t1;
+create table t1 (a text);
+insert into t1 values ('where');
+update t1 set a='Where';
+select * from t1;
+a
+Where
+drop table t1;
+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;
+show full fields from t1;
+Field Type Collation Null Key Default Extra Privileges Comment
+t text latin1_swedish_ci YES NULL #
+c char(10) latin1_swedish_ci YES NULL #
+b blob NULL YES NULL #
+d varbinary(10) NULL YES NULL #
+lock tables t1 WRITE;
+show full fields from t1;
+Field Type Collation Null Key Default Extra Privileges Comment
+t text latin1_swedish_ci YES NULL #
+c char(10) latin1_swedish_ci YES NULL #
+b blob NULL YES NULL #
+d varbinary(10) NULL YES NULL #
+unlock tables;
+select t from t1 where t like "hello";
+t
+hello
+HELLO
+select c from t1 where c like "hello";
+c
+hello
+HELLO
+select b from t1 where b like "hello";
+b
+hello
+select d from t1 where d like "hello";
+d
+hello
+select c from t1 having c like "hello";
+c
+hello
+HELLO
+select d from t1 having d like "hello";
+d
+hello
+select t from t1 where t like "%HELLO%";
+t
+hello
+HELLO
+HELLO MY
+select c from t1 where c like "%HELLO%";
+c
+hello
+HELLO
+HELLO MY
+select b from t1 where b like "%HELLO%";
+b
+HELLO
+HELLO MY
+select d from t1 where d like "%HELLO%";
+d
+HELLO
+HELLO MY
+select c from t1 having c like "%HELLO%";
+c
+hello
+HELLO
+HELLO MY
+select d from t1 having d like "%HELLO%";
+d
+HELLO
+HELLO MY
+select d from t1 having d like "%HE%LLO%";
+d
+HELLO
+HELLO MY
+select t from t1 order by t;
+t
+NULL
+NULL
+
+1
+a
+hello
+HELLO
+HELLO MY
+select c from t1 order by c;
+c
+NULL
+NULL
+
+
+a
+hello
+HELLO
+HELLO MY
+select b from t1 order by b;
+b
+NULL
+NULL
+NULL
+
+HELLO
+HELLO MY
+a
+hello
+select d from t1 order by d;
+d
+NULL
+NULL
+
+1
+HELLO
+HELLO MY
+a
+hello
+select distinct t from t1;
+t
+NULL
+
+hello
+HELLO MY
+a
+1
+select distinct b from t1;
+b
+NULL
+
+hello
+HELLO
+HELLO MY
+a
+select distinct t from t1 order by t;
+t
+NULL
+
+1
+a
+hello
+HELLO MY
+select distinct b from t1 order by b;
+b
+NULL
+
+HELLO
+HELLO MY
+a
+hello
+select t from t1 group by t;
+t
+NULL
+
+1
+a
+hello
+HELLO MY
+select b from t1 group by b;
+b
+NULL
+
+HELLO
+HELLO MY
+a
+hello
+set big_tables=1;
+select distinct t from t1;
+t
+NULL
+
+hello
+HELLO MY
+a
+1
+select distinct b from t1;
+b
+NULL
+
+hello
+HELLO
+HELLO MY
+a
+select distinct t from t1 order by t;
+t
+NULL
+
+1
+a
+hello
+HELLO MY
+select distinct b from t1 order by b;
+b
+NULL
+
+HELLO
+HELLO MY
+a
+hello
+select distinct c from t1;
+c
+NULL
+
+hello
+HELLO MY
+a
+select distinct d from t1;
+d
+NULL
+
+hello
+HELLO
+HELLO MY
+a
+1
+select distinct c from t1 order by c;
+c
+NULL
+
+a
+hello
+HELLO MY
+select distinct d from t1 order by d;
+d
+NULL
+
+1
+HELLO
+HELLO MY
+a
+hello
+select c from t1 group by c;
+c
+NULL
+
+a
+hello
+HELLO MY
+select d from t1 group by d;
+d
+NULL
+
+1
+HELLO
+HELLO MY
+a
+hello
+set big_tables=0;
+select distinct * from t1;
+t c b d
+NULL NULL NULL NULL
+
+hello hello hello hello
+HELLO HELLO HELLO HELLO
+HELLO MY HELLO MY HELLO MY HELLO MY
+a a a a
+1 NULL 1
+select t,count(*) from t1 group by t;
+t count(*)
+NULL 2
+ 1
+1 1
+a 1
+hello 2
+HELLO MY 1
+select b,count(*) from t1 group by b;
+b count(*)
+NULL 3
+ 1
+HELLO 1
+HELLO MY 1
+a 1
+hello 1
+select c,count(*) from t1 group by c;
+c count(*)
+NULL 2
+ 2
+a 1
+hello 2
+HELLO MY 1
+select d,count(*) from t1 group by d;
+d count(*)
+NULL 2
+ 1
+1 1
+HELLO 1
+HELLO MY 1
+a 1
+hello 1
+drop table t1;
+create table t1 (a text, unique (a(2100)));
+ERROR 42000: Specified key was too long; max key length is 1000 bytes
+create table t1 (a text, key (a(2100)));
+Warnings:
+Note 1071 Specified key was too long; max key length is 1000 bytes
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` text DEFAULT NULL,
+ KEY `a` (`a`(1000))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+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;
+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_id
+test^M
+job^M
+1 1 test job 1 0000-00-00 00:00:00 1999-02-25 22:43:32 0 High admin 0 tomato test^M
+job^M
+1 1
+drop table t1,t2,t3,t4,t5,t6,t7;
+create table t1 (a blob);
+insert into t1 values ("empty"),("");
+select a,reverse(a) from t1;
+a reverse(a)
+empty ytpme
+
+drop table t1;
+create table t1 (a blob, key (a(10)));
+insert into t1 values ("bye"),("hello"),("hello"),("hello word");
+select * from t1 where a like "hello%";
+a
+hello
+hello
+hello word
+drop table t1;
+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';
+count(*)
+2
+drop table t1;
+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';
+foobar boggle
+fish 10
+select foobar, boggle from t1 where foobar = 'fish' and boggle = 10;
+foobar boggle
+fish 10
+drop table t1;
+create table t1 (id integer auto_increment unique,imagem LONGBLOB not null default '');
+insert into t1 (id) values (1);
+select
+charset(load_file('../../std_data/words.dat')),
+collation(load_file('../../std_data/words.dat')),
+coercibility(load_file('../../std_data/words.dat'));
+charset(load_file('../../std_data/words.dat')) collation(load_file('../../std_data/words.dat')) coercibility(load_file('../../std_data/words.dat'))
+binary binary 4
+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'));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select charset(load_file('MYSQLTEST_VARDIR/std_data/words.dat')) AS `charset(load_file('MYSQLTEST_VARDIR/std_data/words.dat'))`,collation(load_file('MYSQLTEST_VARDIR/std_data/words.dat')) AS `collation(load_file('MYSQLTEST_VARDIR/std_data/words.dat'))`,coercibility(load_file('MYSQLTEST_VARDIR/std_data/words.dat')) AS `coercibility(load_file('MYSQLTEST_VARDIR/std_data/words.dat'))`
+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;
+if(imagem is null, "ERROR", "OK") length(imagem)
+OK 581
+drop table t1;
+create table t1 select load_file('MYSQLTEST_VARDIR/std_data/words.dat') l;
+show full fields from t1;
+Field Type Collation Null Key Default Extra Privileges Comment
+l longblob NULL YES NULL #
+drop table t1;
+create table t1 (id integer primary key auto_increment, txt text not null, unique index txt_index (txt (20)));
+insert into t1 (txt) values ('Chevy'), ('Chevy ');
+ERROR 23000: Duplicate entry 'Chevy ' for key 'txt_index'
+insert into t1 (txt) values ('Chevy'), ('CHEVY');
+ERROR 23000: Duplicate entry 'Chevy' for key 'txt_index'
+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';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt='Chevy ';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt='Chevy ' or txt='Chevy';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt='Chevy' or txt='Chevy ';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where id='1' or id='2';
+id txt
+1 Chevy
+2 Chevy
+insert into t1 (txt) values('Ford');
+select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford';
+id txt
+1 Chevy
+2 Chevy
+3 Ford
+select * from t1 where txt='Chevy' or txt='Chevy ';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt in ('Chevy ','Chevy');
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt in ('Chevy');
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt between 'Chevy' and 'Chevy';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy ';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt between 'Chevy' and 'Chevy ';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt < 'Chevy ';
+id txt
+select * from t1 where txt <= 'Chevy';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt > 'Chevy';
+id txt
+3 Ford
+select * from t1 where txt >= 'Chevy';
+id txt
+1 Chevy
+2 Chevy
+3 Ford
+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;
+id txt
+1 Chevy
+2 Chevy
+3 NULL
+explain select * from t1 where txt='Chevy' or txt is NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where
+select * from t1 where txt='Chevy ';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt='Chevy ' or txt='Chevy';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt='Chevy' or txt='Chevy ';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where id='1' or id='2';
+id txt
+1 Chevy
+2 Chevy
+insert into t1 (txt) values('Ford');
+select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford';
+id txt
+1 Chevy
+2 Chevy
+4 Ford
+select * from t1 where txt='Chevy' or txt='Chevy ';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt in ('Chevy ','Chevy');
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt in ('Chevy');
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt between 'Chevy' and 'Chevy';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy ';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt between 'Chevy' and 'Chevy ';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt < 'Chevy ';
+id txt
+select * from t1 where txt < 'Chevy ' or txt is NULL;
+id txt
+3 NULL
+select * from t1 where txt <= 'Chevy';
+id txt
+1 Chevy
+2 Chevy
+select * from t1 where txt > 'Chevy';
+id txt
+4 Ford
+select * from t1 where txt >= 'Chevy';
+id txt
+1 Chevy
+2 Chevy
+4 Ford
+alter table t1 modify column txt blob;
+explain select * from t1 where txt='Chevy' or txt is NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where
+select * from t1 where txt='Chevy' or txt is NULL;
+id txt
+1 Chevy
+3 NULL
+explain select * from t1 where txt='Chevy' or txt is NULL order by txt;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where; Using filesort
+select * from t1 where txt='Chevy' or txt is NULL order by txt;
+id txt
+3 NULL
+1 Chevy
+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 = '';
+max(i)
+4
+drop table t1;
+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;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ `c` tinyblob NOT NULL,
+ `d` int(11) NOT NULL,
+ `e` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`,`b`,`c`(255),`d`),
+ KEY `a` (`a`,`b`,`d`,`e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+CREATE table t1 (a blob);
+insert into t1 values ('b'),('a\0'),('a'),('a '),('aa'),(NULL);
+select hex(a) from t1 order by a;
+hex(a)
+NULL
+61
+6100
+6120
+6161
+62
+select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0');
+b
+NULL
+6100
+610000
+612000
+616100
+6200
+alter table t1 modify a varbinary(5);
+select hex(a) from t1 order by a;
+hex(a)
+NULL
+61
+6100
+6120
+6161
+62
+select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0');
+b
+NULL
+6100
+610000
+612000
+616100
+6200
+alter table t1 modify a char(5);
+select hex(a) from t1 order by a;
+hex(a)
+NULL
+6100
+61
+61
+6161
+62
+select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0');
+b
+NULL
+610000
+6100
+6100
+616100
+6200
+alter table t1 modify a binary(5);
+select hex(a) from t1 order by a;
+hex(a)
+NULL
+6100000000
+6100000000
+6100000000
+6161000000
+6200000000
+select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0');
+b
+NULL
+610000000000
+610000000000
+610000000000
+616100000000
+620000000000
+drop table t1;
+create table t1 (a text default '');
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` text DEFAULT ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values (default);
+select * from t1;
+a
+
+drop table t1;
+set @@sql_mode='TRADITIONAL';
+create table t1 (a text default '');
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` text DEFAULT ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+set @@sql_mode='';
+CREATE TABLE t (c TEXT CHARSET ASCII);
+INSERT INTO t (c) VALUES (REPEAT('1',65537));
+Warnings:
+Warning 1265 Data truncated for column 'c' at row 1
+INSERT INTO t (c) VALUES (REPEAT('2',65536));
+Warnings:
+Warning 1265 Data truncated for column 'c' at row 1
+INSERT INTO t (c) VALUES (REPEAT('3',65535));
+SELECT LENGTH(c), CHAR_LENGTH(c) FROM t;
+LENGTH(c) CHAR_LENGTH(c)
+65535 65535
+65535 65535
+65535 65535
+DROP TABLE t;
+drop table if exists b15776;
+create table b15776 (data blob(2147483647));
+drop table b15776;
+create table b15776 (data blob(-1));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1))' at line 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;
+create table b15776 (data blob(4294967296));
+ERROR 42000: Display width out of range for 'data' (max = 4294967295)
+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;
+Field Type Null Key Default Extra
+a longblob YES NULL
+b longblob YES NULL
+c longblob YES NULL
+a1 longtext YES NULL
+b1 longtext YES NULL
+c1 longtext YES NULL
+drop table b15776;
+CREATE TABLE b15776 (a blob(4294967296));
+ERROR 42000: Display width out of range for 'a' (max = 4294967295)
+CREATE TABLE b15776 (a text(4294967296));
+ERROR 42000: Display width out of range for 'a' (max = 4294967295)
+CREATE TABLE b15776 (a blob(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
+ERROR 42000: Display width out of range for 'a' (max = 4294967295)
+CREATE TABLE b15776 (a text(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
+ERROR 42000: Display width out of range for 'a' (max = 4294967295)
+CREATE TABLE b15776 (a int(0));
+INSERT INTO b15776 values (NULL), (1), (42), (654);
+SELECT * from b15776 ORDER BY a;
+a
+NULL
+1
+42
+654
+DROP TABLE b15776;
+CREATE TABLE b15776 (a int(-1));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1))' at line 1
+CREATE TABLE b15776 (a int(255));
+DROP TABLE b15776;
+CREATE TABLE b15776 (a int(256));
+ERROR 42000: Display width out of range for 'a' (max = 255)
+CREATE TABLE b15776 (data blob(-1));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1))' at line 1
+CREATE TABLE b15776 (a char(2147483647));
+ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead
+CREATE TABLE b15776 (a char(2147483648));
+ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead
+CREATE TABLE b15776 (a char(4294967295));
+ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead
+CREATE TABLE b15776 (a char(4294967296));
+ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead
+CREATE TABLE b15776 (a year(?));
+Warnings:
+Note 1287 'YEAR(?)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+INSERT INTO b15776 VALUES (42);
+SELECT * FROM b15776;
+a
+2042
+DROP TABLE b15776;
+CREATE TABLE b15776 (a year(4294967296));
+SHOW CREATE TABLE b15776;
+Table Create Table
+b15776 CREATE TABLE `b15776` (
+ `a` year(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE b15776;
+CREATE TABLE b15776 (a year(0));
+Warnings:
+Note 1287 'YEAR(0)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+DROP TABLE b15776;
+CREATE TABLE b15776 (a year(-2));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-2))' at line 1
+CREATE TABLE b15776 (a timestamp(4294967294));
+ERROR 42000: Too big precision 4294967294 specified for 'a'. Maximum is 6
+CREATE TABLE b15776 (a timestamp(4294967295));
+ERROR 42000: Too big precision 4294967295 specified for 'a'. Maximum is 6
+CREATE TABLE b15776 (a timestamp(4294967296));
+ERROR 42000: Too big precision 4294967296 specified for 'a'. Maximum is 6
+CREATE TABLE b15776 (a timestamp(-1));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1))' at line 1
+CREATE TABLE b15776 (a timestamp(-2));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-2))' at line 1
+CREATE TABLE b15776 (a int(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
+ERROR 42000: Display width out of range for 'a' (max = 255)
+CREATE TABLE b15776 (a char(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
+ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead
+CREATE TABLE b15776 (a year(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
+SHOW CREATE TABLE b15776;
+Table Create Table
+b15776 CREATE TABLE `b15776` (
+ `a` year(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE b15776;
+CREATE TABLE b15776 select cast(null as char(4294967295));
+show columns from b15776;
+Field Type Null Key Default Extra
+cast(null as char(4294967295)) char(0) YES NULL
+drop table b15776;
+CREATE TABLE b15776 select cast(null as nchar(4294967295));
+show columns from b15776;
+Field Type Null Key Default Extra
+cast(null as nchar(4294967295)) char(0) YES NULL
+drop table b15776;
+CREATE TABLE b15776 select cast(null as binary(4294967295));
+show columns from b15776;
+Field Type Null Key Default Extra
+cast(null as binary(4294967295)) binary(0) YES NULL
+drop table b15776;
+explain select cast(1 as char(4294967295));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+explain select cast(1 as nchar(4294967295));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+explain select cast(1 as binary(4294967295));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+explain select cast(1 as char(4294967296));
+ERROR 42000: Display width out of range for '1' (max = 4294967295)
+explain select cast(1 as nchar(4294967296));
+ERROR 42000: Display width out of range for '1' (max = 4294967295)
+explain select cast(1 as binary(4294967296));
+ERROR 42000: Display width out of range for '1' (max = 4294967295)
+explain select cast(1 as decimal(-1));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1))' at line 1
+explain select cast(1 as decimal(64, 30));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+explain select cast(1 as decimal(64, 999999999999999999999999999999));
+Got one of the listed errors
+explain select cast(1 as decimal(4294967296));
+Got one of the listed errors
+explain select cast(1 as decimal(999999999999999999999999999999999999));
+Got one of the listed errors
+explain select convert(1, char(4294967295));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+explain select convert(1, char(4294967296));
+ERROR 42000: Display width out of range for '1' (max = 4294967295)
+explain select convert(1, char(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
+ERROR 42000: Display width out of range for '1' (max = 4294967295)
+explain select convert(1, nchar(4294967295));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+explain select convert(1, nchar(4294967296));
+ERROR 42000: Display width out of range for '1' (max = 4294967295)
+explain select convert(1, nchar(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
+ERROR 42000: Display width out of range for '1' (max = 4294967295)
+explain select convert(1, binary(4294967295));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+explain select convert(1, binary(4294967296));
+ERROR 42000: Display width out of range for '1' (max = 4294967295)
+explain select convert(1, binary(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
+ERROR 42000: Display width out of range for '1' (max = 4294967295)
+End of 5.0 tests
+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);
+Warnings:
+Warning 1265 Data truncated for column 'c' at row 1
+SELECT LENGTH(c) FROM t2;
+LENGTH(c)
+65535
+UPDATE t1 LEFT JOIN t2 USING(id) SET t2.c = REPEAT('1', 70000) WHERE t1.id = 1;
+Warnings:
+Warning 1265 Data truncated for column 'c' at row 1
+SELECT LENGTH(c) FROM t2;
+LENGTH(c)
+65535
+DROP TABLE t1, t2;
+# Bug #52160: crash and inconsistent results when grouping
+# 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;
+COUNT(*)
+2
+DROP FUNCTION f1;
+DROP TABLE t1;
+End of 5.1 tests
+#
+# Start of 5.5 tests
+#
+CREATE TABLE t1 ( f1 blob, f2 blob );
+INSERT INTO t1 VALUES ('','');
+SELECT f1,f2,"found row" FROM t1 WHERE f1 = f2 ;
+f1 f2 found row
+ found row
+DROP TABLE t1;
+#
+# MDEV-9319 ALTER from a bigger to a smaller blob type truncates too much data
+#
+CREATE TABLE t1 (a MEDIUMBLOB);
+INSERT INTO t1 VALUES (REPEAT(0x61,128000));
+SELECT LENGTH(a) FROM t1;
+LENGTH(a)
+128000
+ALTER TABLE t1 MODIFY a BLOB;
+Warnings:
+Warning 1265 Data truncated for column 'a' at row 1
+SELECT LENGTH(a) FROM t1;
+LENGTH(a)
+65535
+DROP TABLE t1;
+CREATE TABLE t1 (a BLOB);
+INSERT INTO t1 VALUES (REPEAT(0x61,65000));
+SELECT LENGTH(a) FROM t1;
+LENGTH(a)
+65000
+ALTER TABLE t1 MODIFY a TINYBLOB;
+Warnings:
+Warning 1265 Data truncated for column 'a' at row 1
+SELECT LENGTH(a) FROM t1;
+LENGTH(a)
+255
+DROP TABLE t1;
+#
+# End of 5.5 tests
+#
View Lorry Controller Status