path: root/storage/rocksdb/mysql-test/rocksdb/r/type_varchar.result

#
# A basic test whether endspace-aware variable length encoding 
# works when in PK
#
create table t1 ( pk varchar(10) primary key, col1 int not null) ;
insert into t1 values ('a', 1);
select * from t1;
pk	col1
a	1
drop table t1;
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (
v0 VARCHAR(0) ,
v1 VARCHAR(1) ,
v64 VARCHAR(64) ,
v65000 VARCHAR(65000) ,
PRIMARY KEY (v64)
) ENGINE=rocksdb;
SHOW COLUMNS IN t1;
Field	Type	Null	Key	Default	Extra
v0	varchar(0)	YES		NULL	
v1	varchar(1)	YES		NULL	
v64	varchar(64)	NO	PRI	NULL	
v65000	varchar(65000)	YES		NULL	
CREATE TABLE t2 (v VARCHAR(65532), PRIMARY KEY (v(255))) ENGINE=rocksdb;
SHOW COLUMNS IN t2;
Field	Type	Null	Key	Default	Extra
v	varchar(65532)	NO	PRI	NULL	
INSERT INTO t1 (v0,v1,v64,v65000) VALUES ('','','','');
INSERT INTO t1 (v0,v1,v64,v65000) VALUES ('','y','Once there, double check that an article doesn\'t already exist','Here is a list of recommended books on MariaDB and MySQL. We\'ve provided links to Amazon.com here for convenience, but they can be found at many other bookstores, both online and off.

  If you want to have your favorite MySQL / MariaDB book listed here, please leave a comment.
  For developers who want to code on MariaDB or MySQL

      * Understanding MySQL Internals by Sasha Pachev, former MySQL developer at MySQL AB.
            o This is the only book we know about that describes the internals of MariaDB / MySQL. A must have for anyone who wants to understand and develop on MariaDB!
            o Not all topics are covered and some parts are slightly outdated, but still the best book on this topic. 
      * MySQL 5.1 Plugin Development by Sergei Golubchik and Andrew Hutchings
            o A must read for anyone wanting to write a plugin for MariaDB, written by the Sergei who designed the plugin interface for MySQL and MariaDB! 

  For MariaDB / MySQL end users

      * MariaDB Crash Course by Ben Forta
            o First MariaDB book!
            o For people who want to learn SQL and the basics of MariaDB.
            o Now shipping. Purchase at Amazon.com or your favorite bookseller. 

      * SQL-99 Complete, Really by Peter Gulutzan & Trudy Pelzer.
            o Everything you wanted to know about the SQL 99 standard. Excellent reference book!
            o Free to read in the Knowledgebase! 

      * MySQL (4th Edition) by Paul DuBois
            o The \'default\' book to read if you wont to learn to use MySQL / MariaDB. 

      * MySQL Cookbook by Paul DuBois
            o A lot of examples of how to use MySQL. As with all of Paul\'s books, it\'s worth its weight in gold and even enjoyable reading for such a \'dry\' subject. 

      * High Performance MySQL, Second Edition, By Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz, Derek J. Balling, et al.
            o \"High Performance MySQL is the definitive guide to building fast, reliable systems with MySQL. Written by noted experts with years of real-world experience building very large systems, this book covers every aspect of MySQL performance in detail, and focuses on robustness, security, and data integrity. Learn advanced techniques in depth so you can bring out MySQL\'s full power.\" (From the book description at O\'Reilly) 

      * MySQL Admin Cookbook
            o A quick step-by-step guide for MySQL users and database administrators to tackle real-world challenges with MySQL configuration and administration 

      * MySQL 5.0 Certification Study Guide, By Paul DuBois, Stefan Hinz, Carsten Pedersen
            o This is the official guide to cover the passing of the two MySQL Certification examinations. It is valid till version 5.0 of the server, so while it misses all the features available in MySQL 5.1 and greater (including MariaDB 5.1 and greater), it provides a good basic understanding of MySQL for the end-user. ');
SELECT v0,v1,v64,v65000 FROM t1;
v0	v1	v64	v65000










			
	y	Once there, double check that an article doesn't already exist	Here is a list of recommended books on MariaDB and MySQL. We've provided links to Amazon.com here for convenience, but they can be found at many other bookstores, both online and off.
            o "High Performance MySQL is the definitive guide to building fast, reliable systems with MySQL. Written by noted experts with years of real-world experience building very large systems, this book covers every aspect of MySQL performance in detail, and focuses on robustness, security, and data integrity. Learn advanced techniques in depth so you can bring out MySQL's full power." (From the book description at O'Reilly) 
            o A lot of examples of how to use MySQL. As with all of Paul's books, it's worth its weight in gold and even enjoyable reading for such a 'dry' subject. 
            o A must read for anyone wanting to write a plugin for MariaDB, written by the Sergei who designed the plugin interface for MySQL and MariaDB! 
            o A quick step-by-step guide for MySQL users and database administrators to tackle real-world challenges with MySQL configuration and administration 
            o Everything you wanted to know about the SQL 99 standard. Excellent reference book!
            o First MariaDB book!
            o For people who want to learn SQL and the basics of MariaDB.
            o Free to read in the Knowledgebase! 
            o Not all topics are covered and some parts are slightly outdated, but still the best book on this topic. 
            o Now shipping. Purchase at Amazon.com or your favorite bookseller. 
            o The 'default' book to read if you wont to learn to use MySQL / MariaDB. 
            o This is the official guide to cover the passing of the two MySQL Certification examinations. It is valid till version 5.0 of the server, so while it misses all the features available in MySQL 5.1 and greater (including MariaDB 5.1 and greater), it provides a good basic understanding of MySQL for the end-user. 
            o This is the only book we know about that describes the internals of MariaDB / MySQL. A must have for anyone who wants to understand and develop on MariaDB!
      * High Performance MySQL, Second Edition, By Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz, Derek J. Balling, et al.
      * MariaDB Crash Course by Ben Forta
      * MySQL (4th Edition) by Paul DuBois
      * MySQL 5.0 Certification Study Guide, By Paul DuBois, Stefan Hinz, Carsten Pedersen
      * MySQL 5.1 Plugin Development by Sergei Golubchik and Andrew Hutchings
      * MySQL Admin Cookbook
      * MySQL Cookbook by Paul DuBois
      * SQL-99 Complete, Really by Peter Gulutzan & Trudy Pelzer.
      * Understanding MySQL Internals by Sasha Pachev, former MySQL developer at MySQL AB.
  For MariaDB / MySQL end users
  For developers who want to code on MariaDB or MySQL
  If you want to have your favorite MySQL / MariaDB book listed here, please leave a comment.
INSERT INTO t1 (v0,v1,v64,v65000) VALUES ('y', 'yy', REPEAT('c',65), REPEAT('abcdefghi ',6501));
Warnings:
Warning	1265	Data truncated for column 'v0' at row 1
Warning	1265	Data truncated for column 'v1' at row 1
Warning	1265	Data truncated for column 'v64' at row 1
Warning	1265	Data truncated for column 'v65000' at row 1
INSERT INTO t1 (v0,v1,v64,v65000) SELECT v65000, v65000, CONCAT('a',v65000), CONCAT(v65000,v1) FROM t1;
Warnings:
Warning	1265	Data truncated for column 'v0' at row 5
Warning	1265	Data truncated for column 'v1' at row 5
Warning	1265	Data truncated for column 'v64' at row 5
Warning	1265	Data truncated for column 'v65000' at row 5
Warning	1265	Data truncated for column 'v0' at row 6
Warning	1265	Data truncated for column 'v1' at row 6
Warning	1265	Data truncated for column 'v64' at row 6
SELECT v0, v1, v64, LENGTH(v65000) FROM t1;
v0	v1	v64	LENGTH(v65000)
			0
		a	0
	H	aHere is a list of recommended books on MariaDB and MySQL. We've	2966
	a	aabcdefghi abcdefghi abcdefghi abcdefghi abcdefghi abcdefghi abc	65000
	y	Once there, double check that an article doesn't already exist	2965
	y	cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc	65000
ALTER TABLE t1 ADD COLUMN v65536 VARCHAR(65536) ;
Warnings:
Note	1246	Converting column 'v65536' from VARCHAR to TEXT
SHOW COLUMNS IN t1;
Field	Type	Null	Key	Default	Extra
v0	varchar(0)	YES		NULL	
v1	varchar(1)	YES		NULL	
v64	varchar(64)	NO	PRI	NULL	
v65000	varchar(65000)	YES		NULL	
v65536	mediumtext	YES		NULL	
DROP TABLE t1, t2;
#
# Endspace-comparison tests: 
#
#
# Issue 257: Sort order for varchars is different between
#            MyISAM/InnoDB vs MyRocks
#
create table t1 (
pk varchar(64) CHARACTER SET latin1 COLLATE latin1_bin,
col1 varchar(64),
primary key (pk)
);
insert into t1 values ('a','a');
insert into t1 values ('a ', 'a-space');
ERROR 23000: Duplicate entry 'a ' for key 'PRIMARY'
insert into t1 values('b  ', 'b-2x-space');
insert into t1 values ('b', 'b');
ERROR 23000: Duplicate entry 'b' for key 'PRIMARY'
select pk, hex(pk), col1 from t1;
pk	hex(pk)	col1
a	61	a
b  	622020	b-2x-space
insert into t1 values ('a\t', 'a-tab');
insert into t1 values ('a \t', 'a-space-tab');
select pk, hex(pk), col1 from t1 order by pk;
pk	hex(pk)	col1
a		6109	a-tab
a 		612009	a-space-tab
a	61	a
b  	622020	b-2x-space
# Try longer values
insert into t1 values (concat('a', repeat(' ',10)), 'a-10-x-space');
ERROR 23000: Duplicate entry 'a          ' for key 'PRIMARY'
insert into t1 values (concat('c', repeat(' ',10)), 'c-10-x-space');
select * from t1;
pk	col1
a		a-tab
a 		a-space-tab
a	a
b  	b-2x-space
c          	c-10-x-space
drop table t1;
# Secondary index
create table t1 (
pk int not null primary key,
col1 varchar(64) CHARACTER SET latin1 COLLATE latin1_bin,
col2 varchar(64),
key (col1)
);
insert into t1 values (0, 'ab', 'a-b');
insert into t1 values (1, 'a ', 'a-space');
insert into t1 values (2, 'a',  'a');
insert into t1 values (3, 'a  \t', 'a-tab');
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
# Must show 'using index' for latin1_bin and utf8_bin:
explain
select col1, hex(col1) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	col1	67	NULL	#	Using index
select col1, hex(col1) from t1;
col1	hex(col1)
a  		61202009
a 	6120
a	61
ab	6162
# Must show 'using index' for latin1_bin and utf8_bin:
explain
select col1, hex(col1) from t1 force index(col1) where col1 < 'b';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	#	col1	col1	67	NULL	#	Using where; Using index
select col1, hex(col1) from t1 force index(col1) where col1 < 'b';
col1	hex(col1)
a  		61202009
a 	6120
a	61
ab	6162
delete from t1;
insert into t1 values(10, '', 'empty');
insert into t1 values(11, repeat(' ', 8), '8x-space');
insert into t1 values(12, repeat(' ', 16), '16x-space');
insert into t1 values(13, repeat(' ', 24), '24x-space');
insert into t1 values(14, concat(repeat(' ', 16),'a'), '16x-space-a');
insert into t1 values(21, repeat(' ', 9), '9x-space');
insert into t1 values(22, repeat(' ',17), '17x-space');
insert into t1 values(23, repeat(' ',18), '18x-space');
explain
select pk, col1, hex(col1), length(col1) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	#	NULL	col1	67	NULL	#	Using index
select pk, col1, hex(col1), length(col1) from t1;
pk	col1	hex(col1)	length(col1)
10			0
11	        	2020202020202020	8
12	                	20202020202020202020202020202020	16
13	                        	202020202020202020202020202020202020202020202020	24
21	         	202020202020202020	9
22	                 	2020202020202020202020202020202020	17
23	                  	202020202020202020202020202020202020	18
14	                a	2020202020202020202020202020202061	17
drop table t1;
create table t1 (pk int primary key, a varchar(512), key(a)) engine=rocksdb;
insert into t1 values (1, concat('a', repeat(' ', 300)));
insert into t1 values (2, concat('b', repeat(' ', 300)));
select pk,length(a) from t1 force index(a) where a < 'zz';
pk	length(a)
1	301
2	301
select pk,length(a),rtrim(a) from t1 force index(a) where a < 'zz';
pk	length(a)	rtrim(a)
1	301	a
2	301	b
select pk,length(a),rtrim(a) from t1 ignore index(a) where a < 'zz';
pk	length(a)	rtrim(a)
1	301	a
2	301	b
drop table t1;
#
# Issue 257: Sort order for varchars is different between
#            MyISAM/InnoDB vs MyRocks
#
create table t1 (
pk varchar(64) CHARACTER SET utf8 COLLATE utf8_bin,
col1 varchar(64),
primary key (pk)
);
insert into t1 values ('a','a');
insert into t1 values ('a ', 'a-space');
ERROR 23000: Duplicate entry 'a ' for key 'PRIMARY'
insert into t1 values('b  ', 'b-2x-space');
insert into t1 values ('b', 'b');
ERROR 23000: Duplicate entry 'b' for key 'PRIMARY'
select pk, hex(pk), col1 from t1;
pk	hex(pk)	col1
a	61	a
b  	622020	b-2x-space
insert into t1 values ('a\t', 'a-tab');
insert into t1 values ('a \t', 'a-space-tab');
select pk, hex(pk), col1 from t1 order by pk;
pk	hex(pk)	col1
a		6109	a-tab
a 		612009	a-space-tab
a	61	a
b  	622020	b-2x-space
# Try longer values
insert into t1 values (concat('a', repeat(' ',10)), 'a-10-x-space');
ERROR 23000: Duplicate entry 'a          ' for key 'PRIMARY'
insert into t1 values (concat('c', repeat(' ',10)), 'c-10-x-space');
select * from t1;
pk	col1
a		a-tab
a 		a-space-tab
a	a
b  	b-2x-space
c          	c-10-x-space
drop table t1;
# Secondary index
create table t1 (
pk int not null primary key,
col1 varchar(64) CHARACTER SET utf8 COLLATE utf8_bin,
col2 varchar(64),
key (col1)
);
insert into t1 values (0, 'ab', 'a-b');
insert into t1 values (1, 'a ', 'a-space');
insert into t1 values (2, 'a',  'a');
insert into t1 values (3, 'a  \t', 'a-tab');
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
# Must show 'using index' for latin1_bin and utf8_bin:
explain
select col1, hex(col1) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	col1	195	NULL	#	Using index
select col1, hex(col1) from t1;
col1	hex(col1)
a  		61202009
a 	6120
a	61
ab	6162
# Must show 'using index' for latin1_bin and utf8_bin:
explain
select col1, hex(col1) from t1 force index(col1) where col1 < 'b';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	#	col1	col1	195	NULL	#	Using where; Using index
select col1, hex(col1) from t1 force index(col1) where col1 < 'b';
col1	hex(col1)
a  		61202009
a 	6120
a	61
ab	6162
delete from t1;
insert into t1 values(10, '', 'empty');
insert into t1 values(11, repeat(' ', 8), '8x-space');
insert into t1 values(12, repeat(' ', 16), '16x-space');
insert into t1 values(13, repeat(' ', 24), '24x-space');
insert into t1 values(14, concat(repeat(' ', 16),'a'), '16x-space-a');
insert into t1 values(21, repeat(' ', 9), '9x-space');
insert into t1 values(22, repeat(' ',17), '17x-space');
insert into t1 values(23, repeat(' ',18), '18x-space');
explain
select pk, col1, hex(col1), length(col1) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	#	NULL	col1	195	NULL	#	Using index
select pk, col1, hex(col1), length(col1) from t1;
pk	col1	hex(col1)	length(col1)
10			0
11	        	2020202020202020	8
12	                	20202020202020202020202020202020	16
13	                        	202020202020202020202020202020202020202020202020	24
21	         	202020202020202020	9
22	                 	2020202020202020202020202020202020	17
23	                  	202020202020202020202020202020202020	18
14	                a	2020202020202020202020202020202061	17
drop table t1;
create table t1 (pk int primary key, a varchar(512), key(a)) engine=rocksdb;
insert into t1 values (1, concat('a', repeat(' ', 300)));
insert into t1 values (2, concat('b', repeat(' ', 300)));
select pk,length(a) from t1 force index(a) where a < 'zz';
pk	length(a)
1	301
2	301
select pk,length(a),rtrim(a) from t1 force index(a) where a < 'zz';
pk	length(a)	rtrim(a)
1	301	a
2	301	b
select pk,length(a),rtrim(a) from t1 ignore index(a) where a < 'zz';
pk	length(a)	rtrim(a)
1	301	a
2	301	b
drop table t1;
#
# Issue 257: Sort order for varchars is different between
#            MyISAM/InnoDB vs MyRocks
#
create table t1 (
pk varchar(64) CHARACTER SET ucs2 COLLATE ucs2_bin,
col1 varchar(64),
primary key (pk)
);
insert into t1 values ('a','a');
insert into t1 values ('a ', 'a-space');
ERROR 23000: Duplicate entry 'a ' for key 'PRIMARY'
insert into t1 values('b  ', 'b-2x-space');
insert into t1 values ('b', 'b');
ERROR 23000: Duplicate entry 'b' for key 'PRIMARY'
select pk, hex(pk), col1 from t1;
pk	hex(pk)	col1
a	0061	a
b  	006200200020	b-2x-space
insert into t1 values ('a\t', 'a-tab');
insert into t1 values ('a \t', 'a-space-tab');
select pk, hex(pk), col1 from t1 order by pk;
pk	hex(pk)	col1
a		00610009	a-tab
a 		006100200009	a-space-tab
a	0061	a
b  	006200200020	b-2x-space
# Try longer values
insert into t1 values (concat('a', repeat(' ',10)), 'a-10-x-space');
ERROR 23000: Duplicate entry 'a          ' for key 'PRIMARY'
insert into t1 values (concat('c', repeat(' ',10)), 'c-10-x-space');
select * from t1;
pk	col1
a		a-tab
a 		a-space-tab
a	a
b  	b-2x-space
c          	c-10-x-space
drop table t1;
# Secondary index
create table t1 (
pk int not null primary key,
col1 varchar(64) CHARACTER SET ucs2 COLLATE ucs2_bin,
col2 varchar(64),
key (col1)
);
insert into t1 values (0, 'ab', 'a-b');
insert into t1 values (1, 'a ', 'a-space');
insert into t1 values (2, 'a',  'a');
insert into t1 values (3, 'a  \t', 'a-tab');
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
# Must show 'using index' for latin1_bin and utf8_bin:
explain
select col1, hex(col1) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	
select col1, hex(col1) from t1;
col1	hex(col1)
ab	00610062
a 	00610020
a	0061
a  		0061002000200009
# Must show 'using index' for latin1_bin and utf8_bin:
explain
select col1, hex(col1) from t1 force index(col1) where col1 < 'b';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	#	col1	col1	131	NULL	#	Using where
select col1, hex(col1) from t1 force index(col1) where col1 < 'b';
col1	hex(col1)
a  		0061002000200009
a 	00610020
a	0061
ab	00610062
delete from t1;
insert into t1 values(10, '', 'empty');
insert into t1 values(11, repeat(' ', 8), '8x-space');
insert into t1 values(12, repeat(' ', 16), '16x-space');
insert into t1 values(13, repeat(' ', 24), '24x-space');
insert into t1 values(14, concat(repeat(' ', 16),'a'), '16x-space-a');
insert into t1 values(21, repeat(' ', 9), '9x-space');
insert into t1 values(22, repeat(' ',17), '17x-space');
insert into t1 values(23, repeat(' ',18), '18x-space');
explain
select pk, col1, hex(col1), length(col1) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	#	NULL	NULL	NULL	NULL	#	
select pk, col1, hex(col1), length(col1) from t1;
pk	col1	hex(col1)	length(col1)
10			0
11	        	00200020002000200020002000200020	16
12	                	0020002000200020002000200020002000200020002000200020002000200020	32
13	                        	002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020	48
14	                a	00200020002000200020002000200020002000200020002000200020002000200061	34
21	         	002000200020002000200020002000200020	18
22	                 	00200020002000200020002000200020002000200020002000200020002000200020	34
23	                  	002000200020002000200020002000200020002000200020002000200020002000200020	36
drop table t1;
create table t1 (pk int primary key, a varchar(512), key(a)) engine=rocksdb;
insert into t1 values (1, concat('a', repeat(' ', 300)));
insert into t1 values (2, concat('b', repeat(' ', 300)));
select pk,length(a) from t1 force index(a) where a < 'zz';
pk	length(a)
1	301
2	301
select pk,length(a),rtrim(a) from t1 force index(a) where a < 'zz';
pk	length(a)	rtrim(a)
1	301	a
2	301	b
select pk,length(a),rtrim(a) from t1 ignore index(a) where a < 'zz';
pk	length(a)	rtrim(a)
1	301	a
2	301	b
drop table t1;
#
# Issue 257: Sort order for varchars is different between
#            MyISAM/InnoDB vs MyRocks
#
create table t1 (
pk varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
col1 varchar(64),
primary key (pk)
);
insert into t1 values ('a','a');
insert into t1 values ('a ', 'a-space');
ERROR 23000: Duplicate entry 'a ' for key 'PRIMARY'
insert into t1 values('b  ', 'b-2x-space');
insert into t1 values ('b', 'b');
ERROR 23000: Duplicate entry 'b' for key 'PRIMARY'
select pk, hex(pk), col1 from t1;
pk	hex(pk)	col1
a	61	a
b  	622020	b-2x-space
insert into t1 values ('a\t', 'a-tab');
insert into t1 values ('a \t', 'a-space-tab');
select pk, hex(pk), col1 from t1 order by pk;
pk	hex(pk)	col1
a		6109	a-tab
a 		612009	a-space-tab
a	61	a
b  	622020	b-2x-space
# Try longer values
insert into t1 values (concat('a', repeat(' ',10)), 'a-10-x-space');
ERROR 23000: Duplicate entry 'a          ' for key 'PRIMARY'
insert into t1 values (concat('c', repeat(' ',10)), 'c-10-x-space');
select * from t1;
pk	col1
a		a-tab
a 		a-space-tab
a	a
b  	b-2x-space
c          	c-10-x-space
drop table t1;
# Secondary index
create table t1 (
pk int not null primary key,
col1 varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
col2 varchar(64),
key (col1)
);
insert into t1 values (0, 'ab', 'a-b');
insert into t1 values (1, 'a ', 'a-space');
insert into t1 values (2, 'a',  'a');
insert into t1 values (3, 'a  \t', 'a-tab');
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
# Must show 'using index' for latin1_bin and utf8_bin:
explain
select col1, hex(col1) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	
select col1, hex(col1) from t1;
col1	hex(col1)
ab	6162
a 	6120
a	61
a  		61202009
# Must show 'using index' for latin1_bin and utf8_bin:
explain
select col1, hex(col1) from t1 force index(col1) where col1 < 'b';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	#	col1	col1	259	NULL	#	Using where
select col1, hex(col1) from t1 force index(col1) where col1 < 'b';
col1	hex(col1)
a  		61202009
a 	6120
a	61
ab	6162
delete from t1;
insert into t1 values(10, '', 'empty');
insert into t1 values(11, repeat(' ', 8), '8x-space');
insert into t1 values(12, repeat(' ', 16), '16x-space');
insert into t1 values(13, repeat(' ', 24), '24x-space');
insert into t1 values(14, concat(repeat(' ', 16),'a'), '16x-space-a');
insert into t1 values(21, repeat(' ', 9), '9x-space');
insert into t1 values(22, repeat(' ',17), '17x-space');
insert into t1 values(23, repeat(' ',18), '18x-space');
explain
select pk, col1, hex(col1), length(col1) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	#	NULL	NULL	NULL	NULL	#	
select pk, col1, hex(col1), length(col1) from t1;
pk	col1	hex(col1)	length(col1)
10			0
11	        	2020202020202020	8
12	                	20202020202020202020202020202020	16
13	                        	202020202020202020202020202020202020202020202020	24
14	                a	2020202020202020202020202020202061	17
21	         	202020202020202020	9
22	                 	2020202020202020202020202020202020	17
23	                  	202020202020202020202020202020202020	18
drop table t1;
create table t1 (pk int primary key, a varchar(512), key(a)) engine=rocksdb;
insert into t1 values (1, concat('a', repeat(' ', 300)));
insert into t1 values (2, concat('b', repeat(' ', 300)));
select pk,length(a) from t1 force index(a) where a < 'zz';
pk	length(a)
1	301
2	301
select pk,length(a),rtrim(a) from t1 force index(a) where a < 'zz';
pk	length(a)	rtrim(a)
1	301	a
2	301	b
select pk,length(a),rtrim(a) from t1 ignore index(a) where a < 'zz';
pk	length(a)	rtrim(a)
1	301	a
2	301	b
drop table t1;
#
# Issue 257: Sort order for varchars is different between
#            MyISAM/InnoDB vs MyRocks
#
create table t1 (
pk varchar(64) CHARACTER SET utf16 COLLATE utf16_bin,
col1 varchar(64),
primary key (pk)
);
insert into t1 values ('a','a');
insert into t1 values ('a ', 'a-space');
ERROR 23000: Duplicate entry 'a ' for key 'PRIMARY'
insert into t1 values('b  ', 'b-2x-space');
insert into t1 values ('b', 'b');
ERROR 23000: Duplicate entry 'b' for key 'PRIMARY'
select pk, hex(pk), col1 from t1;
pk	hex(pk)	col1
a	0061	a
b  	006200200020	b-2x-space
insert into t1 values ('a\t', 'a-tab');
insert into t1 values ('a \t', 'a-space-tab');
select pk, hex(pk), col1 from t1 order by pk;
pk	hex(pk)	col1
a		00610009	a-tab
a 		006100200009	a-space-tab
a	0061	a
b  	006200200020	b-2x-space
# Try longer values
insert into t1 values (concat('a', repeat(' ',10)), 'a-10-x-space');
ERROR 23000: Duplicate entry 'a          ' for key 'PRIMARY'
insert into t1 values (concat('c', repeat(' ',10)), 'c-10-x-space');
select * from t1;
pk	col1
a		a-tab
a 		a-space-tab
a	a
b  	b-2x-space
c          	c-10-x-space
drop table t1;
# Secondary index
create table t1 (
pk int not null primary key,
col1 varchar(64) CHARACTER SET utf16 COLLATE utf16_bin,
col2 varchar(64),
key (col1)
);
insert into t1 values (0, 'ab', 'a-b');
insert into t1 values (1, 'a ', 'a-space');
insert into t1 values (2, 'a',  'a');
insert into t1 values (3, 'a  \t', 'a-tab');
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
# Must show 'using index' for latin1_bin and utf8_bin:
explain
select col1, hex(col1) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	
select col1, hex(col1) from t1;
col1	hex(col1)
ab	00610062
a 	00610020
a	0061
a  		0061002000200009
# Must show 'using index' for latin1_bin and utf8_bin:
explain
select col1, hex(col1) from t1 force index(col1) where col1 < 'b';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	#	col1	col1	259	NULL	#	Using where
select col1, hex(col1) from t1 force index(col1) where col1 < 'b';
col1	hex(col1)
a  		0061002000200009
a 	00610020
a	0061
ab	00610062
delete from t1;
insert into t1 values(10, '', 'empty');
insert into t1 values(11, repeat(' ', 8), '8x-space');
insert into t1 values(12, repeat(' ', 16), '16x-space');
insert into t1 values(13, repeat(' ', 24), '24x-space');
insert into t1 values(14, concat(repeat(' ', 16),'a'), '16x-space-a');
insert into t1 values(21, repeat(' ', 9), '9x-space');
insert into t1 values(22, repeat(' ',17), '17x-space');
insert into t1 values(23, repeat(' ',18), '18x-space');
explain
select pk, col1, hex(col1), length(col1) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	#	NULL	NULL	NULL	NULL	#	
select pk, col1, hex(col1), length(col1) from t1;
pk	col1	hex(col1)	length(col1)
10			0
11	        	00200020002000200020002000200020	16
12	                	0020002000200020002000200020002000200020002000200020002000200020	32
13	                        	002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020	48
14	                a	00200020002000200020002000200020002000200020002000200020002000200061	34
21	         	002000200020002000200020002000200020	18
22	                 	00200020002000200020002000200020002000200020002000200020002000200020	34
23	                  	002000200020002000200020002000200020002000200020002000200020002000200020	36
drop table t1;
create table t1 (pk int primary key, a varchar(512), key(a)) engine=rocksdb;
insert into t1 values (1, concat('a', repeat(' ', 300)));
insert into t1 values (2, concat('b', repeat(' ', 300)));
select pk,length(a) from t1 force index(a) where a < 'zz';
pk	length(a)
1	301
2	301
select pk,length(a),rtrim(a) from t1 force index(a) where a < 'zz';
pk	length(a)	rtrim(a)
1	301	a
2	301	b
select pk,length(a),rtrim(a) from t1 ignore index(a) where a < 'zz';
pk	length(a)	rtrim(a)
1	301	a
2	301	b
drop table t1;
create table t1 (
pk int primary key,
col1 varchar(10) collate utf8mb4_bin not null,
col2 varchar(20),
key(col1)
) engine=rocksdb;
insert into t1 values (1, 'ab','ab');
insert into t1 values (2, 'ab\0','ab0');
select pk, hex(col1), col2 from t1 force index(col1) order by col1;
pk	hex(col1)	col2
2	616200	ab0
1	6162	ab
select pk, hex(col1), col2 from t1 ignore index(col1) order by col1;
pk	hex(col1)	col2
2	616200	ab0
1	6162	ab
drop table t1;
create table t (id int primary key, email varchar(100), KEY email_i (email(30)));
insert into t values (1, 'abcabcabcabcabcabcabcabcabcabcabc  ');
explain select 'email_i' as index_name, count(*) AS count from t force index(email_i);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	#	
select 'email_i' as index_name, count(*) AS count from t force index(email_i);
index_name	count
email_i	1
drop table t;
set @save_rocksdb_checksums_pct = @@global.rocksdb_checksums_pct;
set @save_rocksdb_verify_row_debug_checksums = @@session.rocksdb_verify_row_debug_checksums;
set global rocksdb_checksums_pct = 100;
set session rocksdb_verify_row_debug_checksums = on;
create table t (id int primary key, email varchar(100), KEY email_i (email(30)));
insert into t values (1, 'a');
explain select 'email_i' as index_name, count(*) AS count from t force index(email_i);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	#	
select 'email_i' as index_name, count(*) AS count from t force index(email_i);
index_name	count
email_i	1
drop table t;
set global rocksdb_checksums_pct = @save_rocksdb_checksums_pct;
set session rocksdb_verify_row_debug_checksums = @save_rocksdb_verify_row_debug_checksums;
drop table if exists t;
Warnings:
Note	1051	Unknown table 'test.t'
create table t (h varchar(31) character set utf8 collate utf8_bin not null, i varchar(19) collate latin1_bin not null, primary key(i), key(h)) engine=rocksdb;
insert into t(i,h) values('a','b');
check table t;
Table	Op	Msg_type	Msg_text
test.t	check	status	OK
alter table t modify h varchar(31) character set cp1257 collate cp1257_bin not null;
check table t;
Table	Op	Msg_type	Msg_text
test.t	check	status	OK
drop table t;