~drizzle-trunk/drizzle/development : contents of tests/r/information

~drizzle-trunk/drizzle/development : (revision 2200.2.15)

DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
create database mysqltest;
create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
create table test.t2(a int);
create table t3(a int, KEY a_data (a));
create table mysqltest.t4(a int);
create table t5 (id int auto_increment primary key);
insert into t5 values (10);
select table_name from data_dictionary.tables
where table_schema = "mysqltest" and table_name like "t%";
table_name
t1
t4
select * from data_dictionary.indexes where TABLE_SCHEMA = "mysqltest";
TABLE_SCHEMA	TABLE_NAME	INDEX_NAME	IS_USED_IN_PRIMARY	IS_UNIQUE	IS_NULLABLE	KEY_LENGTH	INDEX_TYPE	INDEX_COMMENT
mysqltest	t1	string_data	NO	NO	YES	120	UNKNOWN	NULL
show tables like 't%';
Tables_in_test (t%)
t2
t3
t5
show table status;
Session	Schema	Name	Type	Engine	Version	Rows	Avg_row_length	Table_size	Auto_increment
#	test	t5	STANDARD	InnoDB	#	#	#	#	#
show columns from t3 like "a%";
Field	Type	Null	Default	Default_is_NULL	On_Update
a	INTEGER	YES		YES	
select * from data_dictionary.columns where table_name="t1"
and column_name= "a";
TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	COLUMN_TYPE	ORDINAL_POSITION	COLUMN_DEFAULT	COLUMN_DEFAULT_IS_NULL	COLUMN_DEFAULT_UPDATE	IS_SIGNED	IS_AUTO_INCREMENT	IS_NULLABLE	IS_INDEXED	IS_USED_IN_PRIMARY	IS_UNIQUE	IS_MULTI	IS_FIRST_IN_MULTI	INDEXES_FOUND_IN	DATA_TYPE	DATA_ARCHETYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	ENUM_VALUES	COLLATION_NAME	COLUMN_COMMENT
mysqltest	t1	a	INTEGER	0	NULL	YES		NULL	NO	YES	NO	NO	NO	NO	NO	0	INTEGER	INTEGER	0	0	0	0	NULL		NULL
select table_name, column_name from data_dictionary.columns 
where table_schema = 'mysqltest' and table_name = 't1';
table_name	column_name
t1	a
t1	b
show columns from mysqltest.t1;
Field	Type	Null	Default	Default_is_NULL	On_Update
a	INTEGER	YES		YES	
b	VARCHAR	YES		YES	
drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
drop database mysqltest;
select column_type from data_dictionary.columns
where table_schema="data_dictionary" and table_name="COLUMNS" and
(column_name="character_set_name" or column_name="collation_name");
column_type
VARCHAR
select count(*) from data_dictionary.tables where 
table_schema="data_dictionary" and table_name="COLUMNS";
count(*)
#
select count(*) from data_dictionary.tables
where table_schema="mysql" and table_name="user";
count(*)
#
select table_schema, table_name, column_name from data_dictionary.columns where data_type = 'longtext';
table_schema	table_name	column_name
select table_name, column_name, data_type from data_dictionary.columns where data_type = 'datetime';
table_name	column_name	data_type
SELECT COUNT(*) FROM data_dictionary.tables A
WHERE NOT EXISTS 
(SELECT * FROM data_dictionary.columns B
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
AND A.TABLE_NAME = B.TABLE_NAME);
COUNT(*)
0
create table t1
( x_bigint BIGINT,
x_integer INTEGER,
x_int int,
x_decimal DECIMAL(5,3),
x_numeric NUMERIC(5,3),
x_real REAL,
x_float FLOAT,
x_double_precision DOUBLE PRECISION );
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
FROM data_dictionary.columns
WHERE TABLE_NAME= 't1';
COLUMN_NAME	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH
x_bigint	0	0
x_decimal	0	0
x_double_precision	0	0
x_float	0	0
x_int	0	0
x_integer	0	0
x_numeric	0	0
x_real	0	0
drop table t1;
SELECT table_schema, count(*) FROM data_dictionary.tables
WHERE table_name NOT LIKE 'ndb_%' AND 
table_name NOT LIKE 'falcon%'
GROUP BY TABLE_SCHEMA ORDER BY table_schema;
table_schema	count(*)
DATA_DICTIONARY	#
INFORMATION_SCHEMA	#
create table t1(f1 LONGBLOB, f2 LONGTEXT);
select column_name,data_type,CHARACTER_OCTET_LENGTH,
CHARACTER_MAXIMUM_LENGTH
from data_dictionary.columns
where table_name='t1';
column_name	data_type	CHARACTER_OCTET_LENGTH	CHARACTER_MAXIMUM_LENGTH
f1	BLOB	0	0
f2	TEXT	0	0
drop table t1;
create table t1(f1 int, f2 int, f3 BIGINT, f4 int,
f5 BIGINT, f6 int, f7 int);
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
from data_dictionary.columns
where table_name='t1';
column_name	NUMERIC_PRECISION	NUMERIC_SCALE
f1	0	0
f2	0	0
f3	0	0
f4	0	0
f5	0	0
f6	0	0
f7	0	0
drop table t1;
create table t1 (a int not null, b int);
select column_name, column_default from data_dictionary.columns
where table_schema='test' and table_name='t1';
column_name	column_default
a	NULL
b	NULL
use test;
show columns from t1;
Field	Type	Null	Default	Default_is_NULL	On_Update
a	INTEGER	NO		NO	
b	INTEGER	YES		YES	
drop table t1;
use test;
create table t1(id int);
insert into t1(id) values (1);
select 1 from (select 1 from test.t1) a;
1
1
use data_dictionary;
select 1 from (select 1 from test.t1) a;
1
1
use test;
drop table t1;
create table t1(f1 varbinary(32), f2 varbinary(64));
select character_maximum_length, character_octet_length
from data_dictionary.columns where table_name='t1';
character_maximum_length	character_octet_length
32	128
64	256
drop table t1;
select column_type, group_concat(table_schema, '.', table_name), count(*) as num
from data_dictionary.columns where
table_schema='data_dictionary' and
(column_type = 'varchar(7)' or column_type = 'varchar(20)'
 or column_type = 'varchar(27)')
group by column_type order by column_type, num;
column_type	group_concat(table_schema, '.', table_name)	num
create table t1(f1 char(1) not null, f2 char(9) not null);
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
data_dictionary.columns where table_schema='test' and table_name = 't1';
CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH
1	4
9	36
drop table t1;
set @a:= '.';
create table t1(f1 char(5));
create table t2(f1 char(5));
select concat(@a, table_name), @a, table_name
from data_dictionary.tables where table_schema = 'test';
concat(@a, table_name)	@a	table_name
.t1	.	t1
.t2	.	t2
drop table t1,t2;
SELECT t.table_name, c1.column_name
FROM data_dictionary.tables t
INNER JOIN
data_dictionary.columns c1
ON t.table_schema = c1.table_schema AND
t.table_name = c1.table_name
WHERE t.table_schema = 'data_dictionary' AND
c1.ordinal_position =
( SELECT COALESCE(MIN(c2.ordinal_position),1)
FROM data_dictionary.columns c2
WHERE c2.table_schema = t.table_schema AND
c2.table_name = t.table_name AND
c2.column_name LIKE '%SCHEMA%'
        )
AND t.table_name NOT LIKE 'falcon%'
  ORDER BY t.table_name, c1.column_name;
table_name	column_name
CATALOGS	CATALOG_CREATION_TIME
CHARACTER_SETS	DEFAULT_COLLATE_NAME
COLLATIONS	COLLATION_NAME
COLUMNS	TABLE_SCHEMA
CUMULATIVE_SQL_COMMANDS	COUNT_SELECT
CUMULATIVE_USER_STATS	BYTES_RECEIVED
CURRENT_SQL_COMMANDS	IP
ERRORS	ERROR_NAME
FOREIGN_KEYS	CONSTRAINT_SCHEMA
GLOBAL_STATEMENTS	VARIABLE_VALUE
GLOBAL_STATUS	VARIABLE_VALUE
GLOBAL_VARIABLES	VARIABLE_VALUE
INDEXES	TABLE_SCHEMA
INDEX_PARTS	TABLE_SCHEMA
INNODB_CMP	COMPRESS_OPS
INNODB_CMPMEM	PAGE_SIZE
INNODB_CMPMEM_RESET	PAGE_SIZE
INNODB_CMP_RESET	COMPRESS_OPS
INNODB_LOCKS	LOCK_TRX_ID
INNODB_LOCK_WAITS	REQUESTED_LOCK_ID
INNODB_REPLICATION_LOG	TRANSACTION_SEGMENT_ID
INNODB_STATUS	VARIABLE_VALUE
INNODB_SYS_COLUMNS	NAME
INNODB_SYS_FIELDS	NAME
INNODB_SYS_FOREIGN	FOR_NAME
INNODB_SYS_FOREIGN_COLS	FOR_COL_NAME
INNODB_SYS_INDEXES	NAME
INNODB_SYS_TABLES	NAME
INNODB_SYS_TABLESTATS	NAME
INNODB_TRX	TRX_STATE
MODULES	MODULE_VERSION
PLUGINS	PLUGIN_TYPE
PROCESSLIST	USERNAME
PROTOCOL_COUNTERS	COUNTER
REPLICATION_STREAMS	APPLIER
SCHEMAS	SCHEMA_NAME
SCOREBOARD_STATISTICS	NUMBER_OF_RANGE_LOCKS
SESSIONS	SESSION_SCHEMA
SESSION_STATEMENTS	VARIABLE_VALUE
SESSION_STATUS	VARIABLE_VALUE
SESSION_VARIABLES	VARIABLE_VALUE
SYS_REPLICATION_LOG	SEGID
TABLES	TABLE_SCHEMA
TABLE_CACHE	TABLE_SCHEMA
TABLE_CONSTRAINTS	CONSTRAINT_SCHEMA
TABLE_DEFINITION_CACHE	TABLE_SCHEMA
USER_DEFINED_BARRIERS	SESSION_ID
USER_DEFINED_LOCKS	SESSION_ID
USER_DEFINED_VARIABLES	VARIABLE_VALUE
SELECT t.table_name, c1.column_name
FROM data_dictionary.tables t
INNER JOIN
data_dictionary.columns c1
ON t.table_schema = c1.table_schema AND
t.table_name = c1.table_name
WHERE t.table_schema = 'data_dictionary' AND
c1.ordinal_position =
( SELECT COALESCE(MIN(c2.ordinal_position),1)
FROM data_dictionary.columns c2
WHERE c2.table_schema = 'data_dictionary' AND
c2.table_name = t.table_name AND
c2.column_name LIKE '%SCHEMA%'
        )
AND t.table_name NOT LIKE 'falcon%'
  ORDER BY t.table_name, c1.column_name;
table_name	column_name
CATALOGS	CATALOG_CREATION_TIME
CHARACTER_SETS	DEFAULT_COLLATE_NAME
COLLATIONS	COLLATION_NAME
COLUMNS	TABLE_SCHEMA
CUMULATIVE_SQL_COMMANDS	COUNT_SELECT
CUMULATIVE_USER_STATS	BYTES_RECEIVED
CURRENT_SQL_COMMANDS	IP
ERRORS	ERROR_NAME
FOREIGN_KEYS	CONSTRAINT_SCHEMA
GLOBAL_STATEMENTS	VARIABLE_VALUE
GLOBAL_STATUS	VARIABLE_VALUE
GLOBAL_VARIABLES	VARIABLE_VALUE
INDEXES	TABLE_SCHEMA
INDEX_PARTS	TABLE_SCHEMA
INNODB_CMP	COMPRESS_OPS
INNODB_CMPMEM	PAGE_SIZE
INNODB_CMPMEM_RESET	PAGE_SIZE
INNODB_CMP_RESET	COMPRESS_OPS
INNODB_LOCKS	LOCK_TRX_ID
INNODB_LOCK_WAITS	REQUESTED_LOCK_ID
INNODB_REPLICATION_LOG	TRANSACTION_SEGMENT_ID
INNODB_STATUS	VARIABLE_VALUE
INNODB_SYS_COLUMNS	NAME
INNODB_SYS_FIELDS	NAME
INNODB_SYS_FOREIGN	FOR_NAME
INNODB_SYS_FOREIGN_COLS	FOR_COL_NAME
INNODB_SYS_INDEXES	NAME
INNODB_SYS_TABLES	NAME
INNODB_SYS_TABLESTATS	NAME
INNODB_TRX	TRX_STATE
MODULES	MODULE_VERSION
PLUGINS	PLUGIN_TYPE
PROCESSLIST	USERNAME
PROTOCOL_COUNTERS	COUNTER
REPLICATION_STREAMS	APPLIER
SCHEMAS	SCHEMA_NAME
SCOREBOARD_STATISTICS	NUMBER_OF_RANGE_LOCKS
SESSIONS	SESSION_SCHEMA
SESSION_STATEMENTS	VARIABLE_VALUE
SESSION_STATUS	VARIABLE_VALUE
SESSION_VARIABLES	VARIABLE_VALUE
SYS_REPLICATION_LOG	SEGID
TABLES	TABLE_SCHEMA
TABLE_CACHE	TABLE_SCHEMA
TABLE_CONSTRAINTS	CONSTRAINT_SCHEMA
TABLE_DEFINITION_CACHE	TABLE_SCHEMA
USER_DEFINED_BARRIERS	SESSION_ID
USER_DEFINED_LOCKS	SESSION_ID
USER_DEFINED_VARIABLES	VARIABLE_VALUE
SELECT MAX(table_name) FROM data_dictionary.tables;
MAX(table_name)
VIEW_TABLE_USAGE
SELECT table_name from data_dictionary.tables
WHERE table_name=(SELECT MAX(table_name)
FROM data_dictionary.tables)
ORDER BY table_name;
table_name
VIEW_TABLE_USAGE
create table t1 (f1 int);
create table t2 (f1 int, f2 int);
drop table t1,t2;
select 1 as f1 from data_dictionary.tables  where "CHARACTER_SETS"=
(select cast(table_name as char)  from data_dictionary.tables WHERE TABLE_SCHEMA=schema()
AND TABLE_NAME = "CHARACTER_SETS"
 order by table_name limit 1) AND TABLE_SCHEMA=schema() limit 1;
f1
select t.table_name, group_concat(t.table_schema, '.', t.table_name),
count(*) as num1
from data_dictionary.tables t
inner join data_dictionary.columns c1
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
where t.table_schema = 'data_dictionary' AND
t.table_name not like 'falcon%' AND
c1.ordinal_position =
(select isnull(c2.column_type) -
isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
count(*) as num
from data_dictionary.columns c2 where
c2.table_schema='data_dictionary' and
(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
group by c2.column_type order by num limit 1)
group by t.table_name order by num1, t.table_name;
table_name	group_concat(t.table_schema, '.', t.table_name)	num1
alter database;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '' at line 1
alter database test;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '' at line 1
create table t1 (
f1 varchar(50),
f2 varchar(50) not null,
f3 varchar(50) default '',
f4 varchar(50) default NULL,
f5 bigint not null,
f6 bigint not null default 10,
f7 datetime not null,
f8 datetime default '2006-01-01'
);
show columns from t1;
Field	Type	Null	Default	Default_is_NULL	On_Update
f1	VARCHAR	YES		YES	
f2	VARCHAR	NO		NO	
f3	VARCHAR	YES		NO	
f4	VARCHAR	YES		YES	
f5	BIGINT	NO		NO	
f6	BIGINT	NO	10	NO	
f7	DATETIME	NO		NO	
f8	DATETIME	YES	2006-01-01 00:00:00.000000	NO	
drop table t1;
SET max_heap_table_size = DEFAULT;
USE test;
End of 5.0 tests.
SELECT SCHEMA_NAME FROM data_dictionary.schemas
WHERE SCHEMA_NAME ='data_dictionary';
SCHEMA_NAME
DATA_DICTIONARY
SELECT TABLE_COLLATION FROM data_dictionary.tables
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
TABLE_COLLATION
#
# Test that the query is visible to self and others.
#
SELECT info FROM data_dictionary.processlist WHERE id = CONNECTION_ID();
info
SELECT info FROM data_dictionary.processlist WHERE id = CONNECTION_ID()
SELECT info, command, db
FROM data_dictionary.processlist
WHERE id = CONNECTION_ID();
info	command	db
FROM data_dictionary.processlist
SELECT info, command, db
WHERE id = CONNECTION_ID()	Query	test

1657 by Brian Aker Add back result file that was lost in merge.	1	DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
	2	create database mysqltest;
	3	create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
	4	create table test.t2(a int);
	5	create table t3(a int, KEY a_data (a));
	6	create table mysqltest.t4(a int);
	7	create table t5 (id int auto_increment primary key);
	8	insert into t5 values (10);
	9	select table_name from data_dictionary.tables
	10	where table_schema = "mysqltest" and table_name like "t%";
	11	table_name
	12	t1
	13	t4
	14	select * from data_dictionary.indexes where TABLE_SCHEMA = "mysqltest";
	15	TABLE_SCHEMA TABLE_NAME INDEX_NAME IS_USED_IN_PRIMARY IS_UNIQUE IS_NULLABLE KEY_LENGTH INDEX_TYPE INDEX_COMMENT
1732.3.1 by Monty Taylor Subtract one - and then remove the extra binary character. This will keep	16	mysqltest t1 string_data NO NO YES 120 UNKNOWN NULL
1657 by Brian Aker Add back result file that was lost in merge.	17	show tables like 't%';
	18	Tables_in_test (t%)
	19	t2
	20	t3
	21	t5
	22	show table status;
	23	Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
	24	# test t5 STANDARD InnoDB # # # # #
	25	show columns from t3 like "a%";
	26	Field Type Null Default Default_is_NULL On_Update
1660 by Brian Aker MErge in change to do YES/NO like standard requires.	27	a INTEGER YES YES
1657 by Brian Aker Add back result file that was lost in merge.	28	select * from data_dictionary.columns where table_name="t1"
	29	and column_name= "a";
1999.4.11 by Brian Aker Fix issues with some columns incorrectly reporting NULL if they were of	30	TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_TYPE ORDINAL_POSITION COLUMN_DEFAULT COLUMN_DEFAULT_IS_NULL COLUMN_DEFAULT_UPDATE IS_SIGNED IS_AUTO_INCREMENT IS_NULLABLE IS_INDEXED IS_USED_IN_PRIMARY IS_UNIQUE IS_MULTI IS_FIRST_IN_MULTI INDEXES_FOUND_IN DATA_TYPE DATA_ARCHETYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE ENUM_VALUES COLLATION_NAME COLUMN_COMMENT
	31	mysqltest t1 a INTEGER 0 NULL YES NULL NO YES NO NO NO NO NO 0 INTEGER INTEGER 0 0 0 0 NULL NULL
1657 by Brian Aker Add back result file that was lost in merge.	32	select table_name, column_name from data_dictionary.columns
	33	where table_schema = 'mysqltest' and table_name = 't1';
	34	table_name column_name
	35	t1 a
	36	t1 b
	37	show columns from mysqltest.t1;
	38	Field Type Null Default Default_is_NULL On_Update
1660 by Brian Aker MErge in change to do YES/NO like standard requires.	39	a INTEGER YES YES
	40	b VARCHAR YES YES
1657 by Brian Aker Add back result file that was lost in merge.	41	drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
	42	drop database mysqltest;
	43	select column_type from data_dictionary.columns
	44	where table_schema="data_dictionary" and table_name="COLUMNS" and
	45	(column_name="character_set_name" or column_name="collation_name");
	46	column_type
	47	VARCHAR
	48	select count(*) from data_dictionary.tables where
	49	table_schema="data_dictionary" and table_name="COLUMNS";
	50	count(*)
	51	#
	52	select count(*) from data_dictionary.tables
	53	where table_schema="mysql" and table_name="user";
	54	count(*)
	55	#
	56	select table_schema, table_name, column_name from data_dictionary.columns where data_type = 'longtext';
	57	table_schema table_name column_name
	58	select table_name, column_name, data_type from data_dictionary.columns where data_type = 'datetime';
	59	table_name column_name data_type
	60	SELECT COUNT(*) FROM data_dictionary.tables A
	61	WHERE NOT EXISTS
	62	(SELECT * FROM data_dictionary.columns B
	63	WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
	64	AND A.TABLE_NAME = B.TABLE_NAME);
	65	COUNT(*)
	66	0
	67	create table t1
	68	( x_bigint BIGINT,
	69	x_integer INTEGER,
	70	x_int int,
	71	x_decimal DECIMAL(5,3),
	72	x_numeric NUMERIC(5,3),
	73	x_real REAL,
	74	x_float FLOAT,
	75	x_double_precision DOUBLE PRECISION );
	76	SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
	77	FROM data_dictionary.columns
	78	WHERE TABLE_NAME= 't1';
	79	COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH
	80	x_bigint 0 0
	81	x_decimal 0 0
	82	x_double_precision 0 0
	83	x_float 0 0
	84	x_int 0 0
	85	x_integer 0 0
	86	x_numeric 0 0
	87	x_real 0 0
	88	drop table t1;
	89	SELECT table_schema, count(*) FROM data_dictionary.tables
	90	WHERE table_name NOT LIKE 'ndb_%' AND
	91	table_name NOT LIKE 'falcon%'
	92	GROUP BY TABLE_SCHEMA ORDER BY table_schema;
	93	table_schema count(*)
1802.7.2 by Brian Aker 1) Fixed a couple of tests that were still relying on counts of tables in	94	DATA_DICTIONARY #
	95	INFORMATION_SCHEMA #
1657 by Brian Aker Add back result file that was lost in merge.	96	create table t1(f1 LONGBLOB, f2 LONGTEXT);
	97	select column_name,data_type,CHARACTER_OCTET_LENGTH,
	98	CHARACTER_MAXIMUM_LENGTH
	99	from data_dictionary.columns
	100	where table_name='t1';
	101	column_name data_type CHARACTER_OCTET_LENGTH CHARACTER_MAXIMUM_LENGTH
	102	f1 BLOB 0 0
1999.4.10 by Brian Aker This fixes the bug where we were not displaying the correct field type in	103	f2 TEXT 0 0
1657 by Brian Aker Add back result file that was lost in merge.	104	drop table t1;
	105	create table t1(f1 int, f2 int, f3 BIGINT, f4 int,
	106	f5 BIGINT, f6 int, f7 int);
	107	select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
	108	from data_dictionary.columns
	109	where table_name='t1';
	110	column_name NUMERIC_PRECISION NUMERIC_SCALE
	111	f1 0 0
	112	f2 0 0
	113	f3 0 0
	114	f4 0 0
	115	f5 0 0
	116	f6 0 0
	117	f7 0 0
	118	drop table t1;
	119	create table t1 (a int not null, b int);
	120	select column_name, column_default from data_dictionary.columns
	121	where table_schema='test' and table_name='t1';
	122	column_name column_default
	123	a NULL
	124	b NULL
	125	use test;
	126	show columns from t1;
	127	Field Type Null Default Default_is_NULL On_Update
1660 by Brian Aker MErge in change to do YES/NO like standard requires.	128	a INTEGER NO NO
	129	b INTEGER YES YES
1657 by Brian Aker Add back result file that was lost in merge.	130	drop table t1;
	131	use test;
	132	create table t1(id int);
	133	insert into t1(id) values (1);
	134	select 1 from (select 1 from test.t1) a;
	135	1
	136	1
	137	use data_dictionary;
	138	select 1 from (select 1 from test.t1) a;
	139	1
	140	1
	141	use test;
	142	drop table t1;
	143	create table t1(f1 varbinary(32), f2 varbinary(64));
	144	select character_maximum_length, character_octet_length
	145	from data_dictionary.columns where table_name='t1';
	146	character_maximum_length character_octet_length
	147	32 128
	148	64 256
	149	drop table t1;
	150	select column_type, group_concat(table_schema, '.', table_name), count(*) as num
	151	from data_dictionary.columns where
	152	table_schema='data_dictionary' and
	153	(column_type = 'varchar(7)' or column_type = 'varchar(20)'
	154	or column_type = 'varchar(27)')
	155	group by column_type order by column_type, num;
	156	column_type group_concat(table_schema, '.', table_name) num
	157	create table t1(f1 char(1) not null, f2 char(9) not null);
	158	select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
	159	data_dictionary.columns where table_schema='test' and table_name = 't1';
	160	CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH
	161	1 4
	162	9 36
	163	drop table t1;
	164	set @a:= '.';
	165	create table t1(f1 char(5));
	166	create table t2(f1 char(5));
	167	select concat(@a, table_name), @a, table_name
	168	from data_dictionary.tables where table_schema = 'test';
	169	concat(@a, table_name) @a table_name
	170	.t1 . t1
	171	.t2 . t2
	172	drop table t1,t2;
	173	SELECT t.table_name, c1.column_name
	174	FROM data_dictionary.tables t
	175	INNER JOIN
	176	data_dictionary.columns c1
	177	ON t.table_schema = c1.table_schema AND
	178	t.table_name = c1.table_name
	179	WHERE t.table_schema = 'data_dictionary' AND
	180	c1.ordinal_position =
	181	( SELECT COALESCE(MIN(c2.ordinal_position),1)
	182	FROM data_dictionary.columns c2
	183	WHERE c2.table_schema = t.table_schema AND
	184	c2.table_name = t.table_name AND
	185	c2.column_name LIKE '%SCHEMA%'
	186	)
	187	AND t.table_name NOT LIKE 'falcon%'
	188	ORDER BY t.table_name, c1.column_name;
	189	table_name column_name
2017.3.1 by Brian Aker Merge catalog with current trunk.	190	CATALOGS CATALOG_CREATION_TIME
1657 by Brian Aker Add back result file that was lost in merge.	191	CHARACTER_SETS DEFAULT_COLLATE_NAME
	192	COLLATIONS COLLATION_NAME
	193	COLUMNS TABLE_SCHEMA
	194	CUMULATIVE_SQL_COMMANDS COUNT_SELECT
	195	CUMULATIVE_USER_STATS BYTES_RECEIVED
	196	CURRENT_SQL_COMMANDS IP
1996.3.4 by Monty Taylor Update information_schema test with new default D_D table.	197	ERRORS ERROR_NAME
1810.6.10 by Andrew Hutchings Fix broken tets cases	198	FOREIGN_KEYS CONSTRAINT_SCHEMA
1657 by Brian Aker Add back result file that was lost in merge.	199	GLOBAL_STATEMENTS VARIABLE_VALUE
	200	GLOBAL_STATUS VARIABLE_VALUE
	201	GLOBAL_VARIABLES VARIABLE_VALUE
	202	INDEXES TABLE_SCHEMA
	203	INDEX_PARTS TABLE_SCHEMA
	204	INNODB_CMP COMPRESS_OPS
1819.7.144 by Stewart Smith update information_schema test result due to added INNODB system tables	205	INNODB_CMPMEM PAGE_SIZE
	206	INNODB_CMPMEM_RESET PAGE_SIZE
1657 by Brian Aker Add back result file that was lost in merge.	207	INNODB_CMP_RESET COMPRESS_OPS
	208	INNODB_LOCKS LOCK_TRX_ID
	209	INNODB_LOCK_WAITS REQUESTED_LOCK_ID
2132.3.16 by Andrew Hutchings Fix test cases	210	INNODB_REPLICATION_LOG TRANSACTION_SEGMENT_ID
1657 by Brian Aker Add back result file that was lost in merge.	211	INNODB_STATUS VARIABLE_VALUE
1819.7.144 by Stewart Smith update information_schema test result due to added INNODB system tables	212	INNODB_SYS_COLUMNS NAME
	213	INNODB_SYS_FIELDS NAME
	214	INNODB_SYS_FOREIGN FOR_NAME
	215	INNODB_SYS_FOREIGN_COLS FOR_COL_NAME
	216	INNODB_SYS_INDEXES NAME
	217	INNODB_SYS_TABLES NAME
	218	INNODB_SYS_TABLESTATS NAME
1657 by Brian Aker Add back result file that was lost in merge.	219	INNODB_TRX TRX_STATE
	220	MODULES MODULE_VERSION
	221	PLUGINS PLUGIN_TYPE
2029.1.26 by Brian Aker Merge in work for reserved words in SQL standard.	222	PROCESSLIST USERNAME
2131.7.4 by Andrew Hutchings Fix test cases	223	PROTOCOL_COUNTERS COUNTER
1657 by Brian Aker Add back result file that was lost in merge.	224	REPLICATION_STREAMS APPLIER
	225	SCHEMAS SCHEMA_NAME
1711.7.3 by Joseph Daly test fixes	226	SCOREBOARD_STATISTICS NUMBER_OF_RANGE_LOCKS
2191.1.5 by Brian Aker Update IS/DD results for sessions.	227	SESSIONS SESSION_SCHEMA
1657 by Brian Aker Add back result file that was lost in merge.	228	SESSION_STATEMENTS VARIABLE_VALUE
	229	SESSION_STATUS VARIABLE_VALUE
	230	SESSION_VARIABLES VARIABLE_VALUE
2132.3.16 by Andrew Hutchings Fix test cases	231	SYS_REPLICATION_LOG SEGID
1657 by Brian Aker Add back result file that was lost in merge.	232	TABLES TABLE_SCHEMA
	233	TABLE_CACHE TABLE_SCHEMA
	234	TABLE_CONSTRAINTS CONSTRAINT_SCHEMA
	235	TABLE_DEFINITION_CACHE TABLE_SCHEMA
1996.1.1 by Brian Aker Update name usage for user defined objects.	236	USER_DEFINED_BARRIERS SESSION_ID
	237	USER_DEFINED_LOCKS SESSION_ID
1927.2.1 by Brian Aker Merge up the tree.	238	USER_DEFINED_VARIABLES VARIABLE_VALUE
1657 by Brian Aker Add back result file that was lost in merge.	239	SELECT t.table_name, c1.column_name
	240	FROM data_dictionary.tables t
	241	INNER JOIN
	242	data_dictionary.columns c1
	243	ON t.table_schema = c1.table_schema AND
	244	t.table_name = c1.table_name
	245	WHERE t.table_schema = 'data_dictionary' AND
	246	c1.ordinal_position =
	247	( SELECT COALESCE(MIN(c2.ordinal_position),1)
	248	FROM data_dictionary.columns c2
	249	WHERE c2.table_schema = 'data_dictionary' AND
	250	c2.table_name = t.table_name AND
	251	c2.column_name LIKE '%SCHEMA%'
	252	)
	253	AND t.table_name NOT LIKE 'falcon%'
	254	ORDER BY t.table_name, c1.column_name;
	255	table_name column_name
2017.3.1 by Brian Aker Merge catalog with current trunk.	256	CATALOGS CATALOG_CREATION_TIME
1657 by Brian Aker Add back result file that was lost in merge.	257	CHARACTER_SETS DEFAULT_COLLATE_NAME
	258	COLLATIONS COLLATION_NAME
	259	COLUMNS TABLE_SCHEMA
	260	CUMULATIVE_SQL_COMMANDS COUNT_SELECT
	261	CUMULATIVE_USER_STATS BYTES_RECEIVED
	262	CURRENT_SQL_COMMANDS IP
1996.3.4 by Monty Taylor Update information_schema test with new default D_D table.	263	ERRORS ERROR_NAME
1810.6.10 by Andrew Hutchings Fix broken tets cases	264	FOREIGN_KEYS CONSTRAINT_SCHEMA
1657 by Brian Aker Add back result file that was lost in merge.	265	GLOBAL_STATEMENTS VARIABLE_VALUE
	266	GLOBAL_STATUS VARIABLE_VALUE
	267	GLOBAL_VARIABLES VARIABLE_VALUE
	268	INDEXES TABLE_SCHEMA
	269	INDEX_PARTS TABLE_SCHEMA
	270	INNODB_CMP COMPRESS_OPS
1819.7.144 by Stewart Smith update information_schema test result due to added INNODB system tables	271	INNODB_CMPMEM PAGE_SIZE
	272	INNODB_CMPMEM_RESET PAGE_SIZE
1657 by Brian Aker Add back result file that was lost in merge.	273	INNODB_CMP_RESET COMPRESS_OPS
	274	INNODB_LOCKS LOCK_TRX_ID
	275	INNODB_LOCK_WAITS REQUESTED_LOCK_ID
2132.3.16 by Andrew Hutchings Fix test cases	276	INNODB_REPLICATION_LOG TRANSACTION_SEGMENT_ID
1657 by Brian Aker Add back result file that was lost in merge.	277	INNODB_STATUS VARIABLE_VALUE
1819.7.144 by Stewart Smith update information_schema test result due to added INNODB system tables	278	INNODB_SYS_COLUMNS NAME
	279	INNODB_SYS_FIELDS NAME
	280	INNODB_SYS_FOREIGN FOR_NAME
	281	INNODB_SYS_FOREIGN_COLS FOR_COL_NAME
	282	INNODB_SYS_INDEXES NAME
	283	INNODB_SYS_TABLES NAME
	284	INNODB_SYS_TABLESTATS NAME
1657 by Brian Aker Add back result file that was lost in merge.	285	INNODB_TRX TRX_STATE
	286	MODULES MODULE_VERSION
	287	PLUGINS PLUGIN_TYPE
2029.1.26 by Brian Aker Merge in work for reserved words in SQL standard.	288	PROCESSLIST USERNAME
2131.7.4 by Andrew Hutchings Fix test cases	289	PROTOCOL_COUNTERS COUNTER
1657 by Brian Aker Add back result file that was lost in merge.	290	REPLICATION_STREAMS APPLIER
	291	SCHEMAS SCHEMA_NAME
1711.7.3 by Joseph Daly test fixes	292	SCOREBOARD_STATISTICS NUMBER_OF_RANGE_LOCKS
2191.1.5 by Brian Aker Update IS/DD results for sessions.	293	SESSIONS SESSION_SCHEMA
1657 by Brian Aker Add back result file that was lost in merge.	294	SESSION_STATEMENTS VARIABLE_VALUE
	295	SESSION_STATUS VARIABLE_VALUE
	296	SESSION_VARIABLES VARIABLE_VALUE
2132.3.16 by Andrew Hutchings Fix test cases	297	SYS_REPLICATION_LOG SEGID
1657 by Brian Aker Add back result file that was lost in merge.	298	TABLES TABLE_SCHEMA
	299	TABLE_CACHE TABLE_SCHEMA
	300	TABLE_CONSTRAINTS CONSTRAINT_SCHEMA
	301	TABLE_DEFINITION_CACHE TABLE_SCHEMA
1996.1.1 by Brian Aker Update name usage for user defined objects.	302	USER_DEFINED_BARRIERS SESSION_ID
	303	USER_DEFINED_LOCKS SESSION_ID
1927.2.1 by Brian Aker Merge up the tree.	304	USER_DEFINED_VARIABLES VARIABLE_VALUE
1657 by Brian Aker Add back result file that was lost in merge.	305	SELECT MAX(table_name) FROM data_dictionary.tables;
	306	MAX(table_name)
	307	VIEW_TABLE_USAGE
	308	SELECT table_name from data_dictionary.tables
	309	WHERE table_name=(SELECT MAX(table_name)
	310	FROM data_dictionary.tables)
	311	ORDER BY table_name;
	312	table_name
	313	VIEW_TABLE_USAGE
	314	create table t1 (f1 int);
	315	create table t2 (f1 int, f2 int);
	316	drop table t1,t2;
	317	select 1 as f1 from data_dictionary.tables where "CHARACTER_SETS"=
2017.3.1 by Brian Aker Merge catalog with current trunk.	318	(select cast(table_name as char) from data_dictionary.tables WHERE TABLE_SCHEMA=schema()
	319	AND TABLE_NAME = "CHARACTER_SETS"
	320	order by table_name limit 1) AND TABLE_SCHEMA=schema() limit 1;
1657 by Brian Aker Add back result file that was lost in merge.	321	f1
	322	select t.table_name, group_concat(t.table_schema, '.', t.table_name),
	323	count(*) as num1
	324	from data_dictionary.tables t
	325	inner join data_dictionary.columns c1
	326	on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
	327	where t.table_schema = 'data_dictionary' AND
	328	t.table_name not like 'falcon%' AND
	329	c1.ordinal_position =
	330	(select isnull(c2.column_type) -
	331	isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
	332	count(*) as num
	333	from data_dictionary.columns c2 where
	334	c2.table_schema='data_dictionary' and
	335	(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
	336	group by c2.column_type order by num limit 1)
	337	group by t.table_name order by num1, t.table_name;
	338	table_name group_concat(t.table_schema, '.', t.table_name) num1
	339	alter database;
	340	ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '' at line 1
	341	alter database test;
	342	ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '' at line 1
	343	create table t1 (
	344	f1 varchar(50),
	345	f2 varchar(50) not null,
	346	f3 varchar(50) default '',
	347	f4 varchar(50) default NULL,
	348	f5 bigint not null,
	349	f6 bigint not null default 10,
	350	f7 datetime not null,
	351	f8 datetime default '2006-01-01'
	352	);
	353	show columns from t1;
	354	Field Type Null Default Default_is_NULL On_Update
1660 by Brian Aker MErge in change to do YES/NO like standard requires.	355	f1 VARCHAR YES YES
	356	f2 VARCHAR NO NO
	357	f3 VARCHAR YES NO
	358	f4 VARCHAR YES YES
	359	f5 BIGINT NO NO
	360	f6 BIGINT NO 10 NO
	361	f7 DATETIME NO NO
2088.8.11 by Brian Aker Fix additional output, swaps for the valus.	362	f8 DATETIME YES 2006-01-01 00:00:00.000000 NO
1657 by Brian Aker Add back result file that was lost in merge.	363	drop table t1;
	364	SET max_heap_table_size = DEFAULT;
	365	USE test;
	366	End of 5.0 tests.
	367	SELECT SCHEMA_NAME FROM data_dictionary.schemas
	368	WHERE SCHEMA_NAME ='data_dictionary';
	369	SCHEMA_NAME
2114.3.3 by Brian Aker Fix for show schemas to user generator correctly for output.	370	DATA_DICTIONARY
1657 by Brian Aker Add back result file that was lost in merge.	371	SELECT TABLE_COLLATION FROM data_dictionary.tables
	372	WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
	373	TABLE_COLLATION
	374	#
	375	# Test that the query is visible to self and others.
	376	#
	377	SELECT info FROM data_dictionary.processlist WHERE id = CONNECTION_ID();
	378	info
	379	SELECT info FROM data_dictionary.processlist WHERE id = CONNECTION_ID()
	380	SELECT info, command, db
	381	FROM data_dictionary.processlist
	382	WHERE id = CONNECTION_ID();
	383	info command db
	384	FROM data_dictionary.processlist
	385	SELECT info, command, db
	386	WHERE id = CONNECTION_ID() Query test